Basic formulae, such as summing the contents of mutiple cells, form the backbone of how spreadsheets work. They let you quickly produce facts and figures from the data on the page, making all the calculations automatically. Here we’ll show you how get started with basic formulae and tap the power at the heart of Excel. See also:
How to create PivotTables in Excel How to delete duplicates in Excel How to add, delete, lock, format, merge and split cells in Excel How to import Excel data to Google sheets (and vice versa)
How to create basic formulae in Excel: What can you actually do?
The formulae you can create in Excel consist of the basic mathematical processes – addition, subtraction, multiplication, and division. These can be applied to whole columns, a specific range of cells, or even individual ones anywhere on the spreadsheet. There are two main methods for creating formulae – manual or automatic – and we’ll take a brief look at both in this tutorial. Formulae are non-destructive, meaning they won’t alter the data in your spreadsheet at all and can be deleted without causing any damage, so you’re safe to experiment with them as much as you want.
How to create basic formulae in Excel: Creating automatic formulae
A common task when working with spreadsheets is the need to calculate the total amount of a column of figures. Thankfully this is very easy in Excel due to the AutoSum feature. To do this you’ll need to click on an empty cell below the last entry in a column and keep the mouse button held down. Then move up to highlight the range of cells you want to use in the calculation. (Note: this can also be achieved by selecting the empty cell and then using Shift and the arrow buttons on your keyboard).
When you have the relevant cells selected go up to the top right corner of the Home menu (the strip at the top of the page that has the various formatting options) and look for the ∑ symbol. Depending on the version of Excel you’re running it might also have AutoSum written next to it.
Click on this and you should see the empty cell under the highlighted column is now filled with the calculated total. You’ll also notice the in the bar at the top of the worksheet there is a formula showing you how the calculation was made – in this case =SUM(D2:D14).
To delete the formula simply highlight the original cell that now shows the total, being careful to ensure that the rest of the column isn’t highlighted, and press delete on your keyboard. You can repeat the process using various different automatic formulae found in the drop down menu indicated by the little arrow next to the AutoSum symbol. These include options for the Average figure in a column, Minimum and Maximum amounts, plus a few more advanced calculations found in the More Functions section.
How to create basic formulae in Excel: Creating manual formulae
If you want to put together a different calculation then the manual route is the way to go. To enter a formula you’ll first need to select an empty cell where you want your calculation to appear. Then in the Formula bar at the top of the worksheet you can type in the formula itself. The structure of formulae is always the same, no matter which calculation you are doing. Each one has to start with the = symbol so that Excel knows that you’re entering a calculation rather than just data. The equals sign is then followed by the cell locations (indicating which cells you want to include in the formula) and either the +,-,/, or * symbols for add, subtract, divide, and multiply. For example in our spreadsheet we want to calculate how much we need to pay B.Wayne for his services. There are two entries for him which are found in cells D3 (£25,000) and D13 (£13,500). So to calculate his total amount we need to enter the following formula: =D3+D13
Once we’ve typed this in we press enter and the total amount of £38,500 is displayed in the cell. Say we want to include the amounts owed to B.Wayne’s co-worker D.Grayson in the calculations? We could either add two more cells to the existing formula, making it =D3+D10+D13+D14 or if we wanted to keep things a little neater we could group B.Wayne and D.Grayson’s entries separately like this; =(D3+D13) + (D10+D14) Working out the average amount that was paid out to both men across their two jobs is quite easy. At the start of the formula we type AVERAGE, then place commas between each cell number. The formula looks like this: =AVERAGE(D3,D13,D10,D14) Which gives us the answer £13,461.50
Of course you can add, subtract, divide, and multiply any figures just as you would in a normal mathematical equation. So if we wanted to see the difference in pay between B.Wayne and D.Grayson we would enter this formula: =(D3+D13)-(D10+D14) And discover that Mr Wayne costs £23,154 more to employ than his young ward.
Alternatively we could also project the costs of using B.Wayne for the same jobs each month over a four month period by using the following formula where we multiply his total by four: =(D3+D13)*4
Armed with these basic formulae you’ll now be able to automate the calculations on your spreadsheets and harness the data within. There are far more complex variants that can be used, but they’ll need a whole other feature to cover in any depth. For the meantime practice using these everyday tools and you could end up saving plenty of time, and maybe even a bit of money. Martyn has been involved with tech ever since the arrival of his ZX Spectrum back in the early 80s. He covers iOS, Android, Windows and macOS, writing tutorials, buying guides and reviews for Macworld and its sister site Tech Advisor.
