How to use Microsoft Excel formulas

Using formulas is the quickest and easiest way to sort data and do simple sums in Microsoft Excel. Paolo Lenotti of Excel with Business is on hand to show you how

Microsoft Excel’s power lies in its functions and  formulas, which allow all sorts of data analysis, from simple sums to standard deviations.

Let’s say we need to calculate our sales values by multiplying the quantity of each product sold by the price charged per item. Much of the effectiveness of a spreadsheet comes from the ability to perform calculations by referring to the contents of other cells, so that those calculations are updated automatically when the cell contents change. We will calculate our Net sales in this way.

In the example below, we select cell D4 and press = on the keyboard to indicate to Excel that we are starting to type a formula rather than just text or a number. We want to multiply the contents of B4 by the contents of C4, so, after pressing = we click on cell B4 – Excel will automatically enter the reference to B4 in our formula. We then press the * on the keyboard to perform the multiplication and click on cell C4.

Multiplication of the contents of two cells

Note the use of colour coding in our formula: the B4 is shown in blue and cell B4 has a blue border and light blue fill.

We now have our first formula and we need to copy it to the other rows in our table. At the bottom right-hand corner of our selected cell there is a small, solid square. This is the Fill Handle. If we position our cursor over this, it will change from a ‘hollow’ cross to a solid black cross. We then drag this down to the bottom of our table to fill the cells with a copy of the contents of D4.

Select the bottom right corner of the cell and drag down

The Fill Handle automatically fills the other cells in the column

You will see that Excel has not only copied our formula from D4 down to D12, but the formula has also adapted to refer to the correct figures on each row. This is because Excel references are, by default, ‘relative’. This means that when we get to cell D12, we are still referring to the cell 2 columns to the left and on the same row, which is now B12.

AAT members have free access to this and many other Excel tips through their MyAAT account. Excel with Business‘s online course is designed to teach the features of Microsoft Excel that are useful to AAT members, saving time and offering a tailored learning experience.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.