Friday, December 20, 2013

The Spreadsheet Tools

Typing and formatting data is all well and good but the purpose of a spreadsheet is to process your data. In this section we will introduce you to some powerful features found in Excel. 

Simple Formulas 
One way to process a set of numbers in your worksheet is to use a formula. Simply click on the cell in which you want the result to be displayed, then type in your formula. It should begin with the equals sign (=) and may contain cell references, numerical constants, or pre-defined Excel functions. 

For example, to add the contents of cells A2, A3, A4 and A5 and put the result in A6 you would type the following formula into cell A6 

=A2+A3+A4+A5 

Whilst you are typing it in the cell displays the actual formula, but as soon as you press the Enter key the contents of the cell change to show the result of the operation. 

When building formulas you use the following symbols 

=A1+A2 add contents of cell A1 to contents of cell A2 
=A1-A2 subtract contents of cell A2 from contents of cell A1 
=A1*A2 multiply contents of cell A1 with contents of cell A2 
=A1/A2 divide contents of cell A1 by contents of cell A2 

All formulas and functions in a worksheet process the contents of specified cells. If you decide to change the contents of a significant cell, then all formulas that refer to 
that cell respond immediately and automatically update their displayed result. This makes the spreadsheet an incredibly powerful tool, indispensable for answering ‘What would happen if...’ type questions. 

Summing Numbers 
To add a large range of numbers you can use the Excel SUM function as follows: 

=SUM(A2:A5) 

However, rather than typing this in, you can go to the Formulas tab, then in the Function Library group, click the AutoSum button. Excel will place a dotted line round the numbers that it thinks you want to add. If Excel’s guess is correct you can then simply click the AutoSum button again, or press the Enter key to confirm the range and accept the formula. If Excel guesses wrongly, however, you can simply select the correct range with the mouse and press the Enter key. When you are confident with AutoSum you can speed up the process by double-clicking the button. 

In addition, every time you select an area of numbers, Excel will display the sum in the status bar at the bottom of the Excel screen. 

Formulas/Formulas
=150*.05   --Multiplies 150 by 0.05. This formula   uses only values and isn’t all that useful   because it always returns the same result.   You may as well just enter the value 7.5 into   the cell.
=A1+A2    Adds the values in cells A1 and A2.
=Income–Expenses
          Subtracts the value in the cell named Expenses   from          the value in the cell named Income.
=SUM(A1:A12)
           Adds the values in the range A1:A12.
=A1=C12   Compares cell A1 with cell C12. If they are identical,        the formula returns TRUE; otherwise it returns FALSE.

Operators Used in Formulas

Operator Precedence in Excel Formulas

Data-Fill 
Entering data into a worksheet can be a laborious process. Excel can help you with this by making intelligent guesses as to what you intend to type in. So if you type the word ‘Monday’ in cell B2, for example, Excel can automatically insert Tuesday to 
Friday into cells B3-B6. The process by which Excel does this is called data-fill. 

To use data-fill you first need to select a few cells at the beginning of a sequence. On the corner of the border of the selected cells you will notice a little box. This is called a data-fill handle. If you position the cursor above this it turns from white to black. 


To extend the selected sequence you should click and hold down on the data-fill handle, then drag the border over the adjacent cells. When you release the mouse button, all the selected cells will be filled in according to the sequence. 

A more powerful incarnation of this feature is the automatic generation of formulae. If you have, for example, five columns of numbers and you want to sum each column, you can simply AutoSum the first column, then use data-fill to drag the formula across the other four columns. If column one has the sum of cells A1 to A6, then column two would automatically perform the sum of B1 to B6 etc. 


No comments:

Post a Comment