Part I: MS Excel: Sort data in a jiffy
Part II: Formatting in Excel
After tackling the basic and formatting capabilities of Microsoft Excel, we come to formulas, which are an integral part of the worksheets, and without which an electronic spreadsheet wouldn't be of much use.
The advantage of MS Excel over paper versions of spreadsheets is that the 'formula' function takes the values from specified cells and performs mathematical operations on them. For example, you can make a formula add, subtract, divide, multiply, calculate averages and perform many other functions.
How to create a formula
Let's create a formula to calculate the total of a list of various items as well as updating the total whenever a new item is added.
~In column A, specify names of various items.
~In column B, specify the number of each type of item.
~We want to find the total of all the items combined and display this result in Cell B5.
~All formulas begin with an equal sign, so click on cell 'B5', type '=' and then enter the formula. Excel will treat all cells containing '=' as a formula.
~To find the total of the cells B1, B2, B3 and B4, type the formula in cell B5 as =B1+B2+B3+B4. This formula can be typed directly in the cell or into the formula bar (the cell needs to be selected before typing anything in the formula bar). Formulas are not case sensitive.
~On pressing the 'Enter' key or on another cell, the sum of the values in cell B1 to B4 will be visible in cell B5.
~If you make changes to any value in cells B1 to B4, the changes will be reflected in the sum being displayed in Cell B5. The total can also be calculated by typing the formula in cell B5 as =SUM(B1:B4).
~It is not necessary for the cells to be in a continuation for performing any calculation. You can also calculate sum of cells, for example B1, C4, and C8. The formula to calculate the sum in this case would be =B1+C4+C8.
~To calculate the average of the values in cells B1 through B4, you need to add and then divide the total by the number of values added together: =(B1+B2+B3+B4)/4.
Points to note:
In a formula you may use the operators '+', '-', '*', '/'.
Below is an example of a few valid formulas:
=(F1+F4)/((E2+E3)-F1*F3))
=(F1+F4)/((E2+E3)-F1*3))
Inbuilt functions
You can create formulas from inbuilt functions in MS Excel. As discussed, the formula '=B1+B2+B3+B4' can be represented as a function '=Sum (B1.B4)'. This function asks Excel to include all the cells from B1 to B4 for calculating the total.
To try finding out the functions that Excel provides, click on the cell where you want to apply, then click 'Insert -> Function'. From the 'Select a category' dropdown option, select 'All'.
To know the details of any function, click on the function name in the 'Select a function' box. All the details about the usability of the function will be displayed just below the box. Select the desired function and click 'OK'. Follow the steps and create the formula.
Tips and Tricks
~Quick help
To get quick help on any menu item, press 'Shift' and 'F1' key simultaneously and click on the menu item for which you need help. For example, to get help on 'Auto Formatting', press the 'Shift' and 'F1' key simultaneously. The cursor will turn to an arrow and question mark sign. Now click 'Format -> AutoFormat'.
Similarly, you can try this for any of the icons in MS Excel.
~Add hidden text to formulas
To make it easier to recall a formula, you can add a hidden note. For example, the formula '=((B1*12)+(C20*4)+D20)-(E3*12))' would not make sense to you. To easily understand what the formula is all about, you can add notes.
For example, '=((B1*12)+(C20*4)+D20)-(E3*12)) + N ((monthly salary*12)+(quarterly Bonus*4)+(yearly bonus)-(Monthly loan EMI*12))'
Here, the value of the formula 'N ( .)' is considered as 0. Adding '0' to any result will not affect the outcome.
~Mark cells with formula
If you have quite a few formulas on your worksheet and want to identify these cells at a glance, you can assign a colour that would be seen on all the cells which have formula value.
To assign a colour to all the cells with formula, Click 'Edit -> Go to-> Special'. Now select 'Formula' and click 'OK'. The opened window will close. Now click 'Format -> Cells' and select the 'Patterns' tab and select colour and click 'OK'. All the cells with formula will be highlighted.
(Note: This applies only to the cells that have formula before performing this action. New formulae added to this worksheet will not be highlighted)
~Generate random numbers
Sometimes you may need to fill a particular column in the worksheet with random numbers. The function '=rand()' allows you can to generate random numbers. In the first cell of a column, type in '=rand ()'. You will get a number in between 0 and 1.
Similarly to get a random number in between 1 and 100, type '=rand()*100'. After entering the 'Rand' function in one cell, you can apply the same function across other cells in the same column. Just click the cell with the formula, move your mouse pointer over the lower-right corner of the cell until it turns into a black plus sign, and drag it horizontally across the cells you wish to fill.
~Results without formula
If you copy-paste the results of a formula to a blank cell, you would see the value being copied on the cell. But in the background, MS Excel would be copy-pasting the formula to this cell, so any changes made to the value of the cells being used in this calculation would also affect the value of the copied cell.
To copy just the resultant value (not the formula but the static result) of some complex calculation to a blank cell, you need to follow the regular copy and paste feature and then click the arrow next to the 'Paste Option' and click 'Values Only'.
~To clear all worksheet formatting
To quickly clear all the formatting in a spreadsheet, click on any cell in the spreadsheet and press 'Control' key and 'A' key simultaneously. This will select the entire spreadsheet. Now click 'Edit -> Clear -> Formats'.
~Combine text from different cells
While MS Excel provides unlimited options to play around with numerals, there are limited options to do so with text. Using the '&' key, you can consolidate the text information in the same way that the '+' symbol adds up numbers. Blank space can be added by giving a space between quotes.
For example, in Cell 'A1' type 'REDIFF' (without quotes) and in cell 'A2' type 'MAIL' (also without quotes). Now in Cell 'A3' type in the formula as "=A1&" "&A2".
The result would be REDIFF MAIL.
~Date format
If you wish to display the date as 30-01-2007 instead of 30/01/2007 in MS Excel, then click on the 'Start button and select 'Control Panel'. Now double click on the 'Regional' and 'Language option', click on 'Customise' and go to the 'date' tab. From the 'Date Separator' select '-' instead of '/'. Click 'OK' and you are done.
Part I: MS Excel: Sort data in a jiffy
Part II: Formatting in Excel
More from rediff