Part I: MS Excel: Sort and manage data in a jiffy
We have already dealt with some of the basic functions of Microsoft Excel, an application of the MS Office suite.
In this part, we look at the various kinds of formatting capabilities Excel provides.
All the major formatting for cells in Excel can be done on the 'Format Cell' window, which can be accessed by clicking 'Format -> cells' (the formatting would apply to the cell selected just before accessing this window). On this window, you will find tabs like 'Number', 'Alignment', 'Font', 'Border', 'Patterns', and 'Protection'.
Number
This is for formatting a cell containing numbers. The default is 'General' which means no specific formatting. On the numbers tab, you can specify things like the number of decimal places to display and the default for currency, which is '$'(you can set it to 'INR' for Rupee). To do so, select 'Currency' from 'Category' list and from the 'Symbol' dropdown, select 'INR'.
Similarly, formatting for 'Time', 'Date', 'Fraction' and other categories can be selected.
Another category, 'Custom', is a very useful option for selecting any of the predefined formatting from the 'Type' list. You can then modify it as per your requirement. This customised formatting would apply to the selected cell.
For more details on custom formatting, go to Excel 'Help' by pressing 'F1" and search for custom formatting. From the results, select 'Number Format Codes'.
Alignment
Using this tab, you can specify the alignment of the cell -- the horizontal and vertical alignment parameters, and the angles at which the text should appear. You can also select the text direction (if you want the next character typed in the cell to appear on the left or right of the earlier character) or merge two or more cells (combining 2 or more cells).
Font
Besides size, style and colour of the font, you can also specify the effects through this tab.
Border
This tab allows you to specify the border you want to apply to a cell. If you want the border to be visible in a printout, you need to specify the border. The light grey coloured grids visible are not the cell's border, they will not show up when you print the page.
Patterns
From this tab you can specify how the cell should be shaded and with which colour. You can select from the various available textures or choose a simple colour without any pattern or shading.
Protection
Select 'Lock' or 'Hide' to hide or lock a cell. However, this would have no effect until you protect the workbook, which can be done by clicking 'Tools -> Protection'.
Conditional Formatting
Conditional formatting is one of the features that make MS Excel an application builder and not just a simple spreadsheet tool.
Depending upon the value of the cell, you can change the formatting. You can change the formatting of the cell based on its value or based on the value of some other cell or some calculations.
Practice run
Before discussing the formatting capabilities, let's create a small worksheet in Excel and put some data to try out the different formatting features
~Open Excel and in the first column (column A) enter all the subjects studied in Class 10 and 12.
~In column B and C enter the marks obtained in Class 11 and 12 respectively in these subjects.
~Now select the cells you want to for conditional formatting. We have selected B2, B3, B4, B5, B6, C2, C3, C4, C5 and C6.
~Now click 'Format -> Conditional Formatting' and change the value of condition1 from 'Cell value is' to 'Formula Is'. In the textbox on the left, type '=$B3>$C3' (do not use the quote signs). This means the formatting will apply only to B and C columns when they satisfy the condition that the value in column C is more than the value of column B of the same row.
~Now to choose the colour, say orange, for the text satisfying the condition click on 'Format' and select the styles/colour for the text.
~To add another condition, click on the 'Add' button on the lower part of the window.
~For the second condition, apply a condition based on the value, for example, let's apply a condition to highlight values less than 60. Let the condition dropdown value remain 'Cell Value Is' and from the next drop down, select 'Less than' and enter the value '60' in the textbox. Select the formatting you wish to apply by clicking the 'Format' button.
~You can add up to three conditions for each selection. Now click 'OK'.
~Now your table will have coloured text wherever the cell meets the condition.
~These values are dynamic (if you change any value in the formatted cell, which do not fulfil the pre-defined conditions set by you, then the text will again change colour, in this case to black).
Part I: MS Excel: Sort and manage data in a jiffy
Tomorrow: Excel in formulas
More from rediff