Microsoft Office/Working with Formulas
Select the cell that the formula result is going to be displayed in. The formula can be constructed in the formula bar. Always need to put the = sign before a formula. That is how Excel recognizes what you are entering as a formula.
| Sign | Operation | Example |
|---|---|---|
| + | Addition | =A1+B1+C1+D1 |
| - | Subtraction | =A1-A2 |
| * | Multiplication | =C4*C5 |
| / | Division | =C4/D4 |
| (…) | Combination | =A1*(B1+C1) |
Click on the green checkmark to enter the formula, the red x to cancel the formula.
Autofill
[edit | edit source]Autofill helps you fill in formulas quickly once you have constructed one in a cell. In order to Autofill, select the cell with the formula. Place your cursor so the small black cross appears in the lower right corner of the cell (+). Once that cursor is visible, simply drag your formula down the column (or across the row as the case may be). Autofill will change the cell references accordingly.
Note that cell references can also change automatically when you copy and paste a formula using the clipboard, unless you use an absolute cell reference (see below).
E.g.: If the formula in A3 is =A1+A2, when you drag that formula over to B3 then the formula becomes =B1+B2.
Cell References
[edit | edit source]- Relative Cell References: cell references that change when the formula is autofilled into different cells, as in the example above.
- Absolute Cell References: if you don't want a certain part of your cell reference to change when you copy the formula to a new cell, you need to put a $ in front of the row and/or column part of the reference (see example below).
E.g.: If the formula in A3 is =$A$1+A2, when you drag that formula over to B3 the formula becomes =$A$1+B2.
To quickly add the dollar symbols use the F4 key, which toggles four ways. First press gives both row and column absolute, $A$1, second press gives row absolute, A$1, third press gives column absolute, $A1 and finally fourth press returns to fully relative, A1.
AutoSum
[edit | edit source]The AutoSum button on the standard toolbar allows you to quickly insert the SUM function. Select the cell where you want to put the total and then click on the AutoSum button ( ). Excel will insert the SUM function and take a guess as to what cell range you’d like to sum. Check to make sure the cell range is correct, then press enter to accept the function.
Functions
[edit | edit source]
Excel has created hundreds of functions that prevent you from having to write out complex or repetitive formulas yourself. The cells that you want to perform the function on are either listed, separated by commas, or included in a range of numbers, indicated by a colon in between the first number in the range and the last number in the range.
Functions can be inserted by using the Insert menu, by clicking on the Paste Function icon on the Standard toolbar, or by typing = in the formula bar and choosing the function from the drop down list on the left.
Functions can also be 'nested'; that is, inserted into larger functions, by using the appropriate amount of brackets.
=AVERAGE(SUM(B2:F2), SUM(B3:F3))
The Function Wizard
[edit | edit source]You can quickly generate valid functions using the function wizard. To open it, click the function wizard button in the formula toolbar ( ). It provides a description of the function you select (in this case AVERAGE), a space for you to enter in the range or numbers (in Number1) and previews the Formula Result. You can select a range from your sheet rather than typing it in manually by clicking on the little button with the red arrow to the right of the Value box.
AutoCalculate
[edit | edit source]Excel will automatically perform calculation on a set of cells that you select and display the results on the status bar. The default calculation is the SUM function, but you can change the calculation by right-clicking (Win) or Command + clicking (Mac) on the AutoCalculate result.