A formula in a spreadsheet is similar to an expression in Maths. An example of an expression might be: ‘r=2+2’.
In Excel the result of the expression or formula is placed in the current cell so we do not need the variable name 'r' at the front. If we enter ‘=2+2’ into a cell in Excel we get the answer ‘4’ as the cell contents.
Entering '=2+2' is not much more useful than entering '4' unless we want to use Excel as a mere calculator - it does, after all, have more significant digits than most calculators. Rather more useful is the facility to include cell references in formulae which allow expressions to be entered such as '=A1+2' or '=B2*2'. The contents of the cells A1 and B2 can be changed, which will produce a different result in the cells that contain the expressions.
For example, if A1 contains 2 and B1 contains '=A1+2' then B1 will display the result 4. If the contents of A1 are changed to 3 then B1 will automatically update to display 5. This is a powerful feature of spreadsheets and allows us to construct complex mathematical models.
We can use formulae in spreadsheets to perform a wide range of tasks.
Add two numbers.
Multiply two numbers - multiplication table.
Add two numbers and multiply by a third.
Subtract two numbers and divide by a third.
To generate the sequence 1, 2, 4, 8, 16… we can enter 1 in a starting cell and then use a formula to double it in the subsequent cells. We select the first cell with a formula and use its copy marker to apply it to subsequent cells. You might think that A3 would contain an exact copy of the cell above but it actually contains '=A2*2'. Excel automatically adds 1 to all cell references in formulae when they are copied into cells underneath. If the copying is done horizontally Excel increases the column letter, so '=A1*2' becomes '=B1*2', '=C1*2', etc.
To display the formulae you have created choose Tools/Options and the View tab. Click on the Formulas box in the Windows Options section.
To display grid lines and row and column headings choose File/Page Setup and the Sheet tab. Click on the options you require. Choose Print Preview to check that the settings have been applied.
An Excel spreadsheet generally recalculates itself every time a cell changes. For very large spreadsheets this may take a long time so recalculation can be turned off. To turn off automatic recalculation choose Tools/Options/Calculation and tick the Manual option. Now use F9 to recalculate the spreadsheet. You may find this useful when using the RAND() function so that you can control when new values are calculated.
Some formulas require brackets or parentheses to ensure correct operator precedence in an expression. For example, 2+2x2=6 but (2+2)x2=8. The order of precedence for operators is the same as for maths on paper or a calculator - BIDMAS. The 'i' is for Index or power, for example 2*2^2=8 while (2*2)^2=16. In the first of these 2^2=4 is done first, 2*4=8. In the second example 2*2=4 is done first, 4^2=16.
These techniques can be useful for checking that you have entered your formulas correctly.
When you have entered a formula you can trace the cells that feed into it and the cells that depend on it. Bring up the Formula Auditing toolbar (Tools/Formula Auditing/Show Formula Auditing Toolbar) and use the various options on it to trace dependents and precedents.
The precedents of a cell are the cells that are referred to in a formula. If you enter two columns of numbers and sum them in a third column then you can trace the precedents of cells in the third column.
The dependents of a cell are the cells that are referred to in a formula. If you click on a cell in the first two columns (plain numbers in this example) you can show its dependents, that is cells that include a formula that refers to that cell.
To check the values generated by a formula choose Tools/Formula Auditing/Evaluate Formula. Each part of a formula is underlined, clicking on the Evaluate button will convert this to a value so you can trace the values and the result.
Watch windows are found in many high level programming environments, including VBA. To track what is going on in various cells on a worksheet you can use the Watch Window. Choose Tools/Formula Auditing/Show Watch Window. Click the Add Watch button in the Watch Window and then click the cell you want to track. This is useful for cells on a large worksheet or in a workbook with more than one worksheet.
Use the Back Button to Return to the previous page