9 Excel formulas everyone should know
- 13 sep 2021
If you’re like most Excel users, you’re only using a tiny proportion of what the software can offer. And there’s a good chance you’re missing out on time-saving formulas and functions (ready-made formulas) that could save you hassle and make your calculations more reliable.
Our list of handy formulas has something for everyone, from basic arithmetic through to conditional statements and tidying up text.
- Start by selecting the cell where you want the answer to show. Then type = into the formula bar across the top of the screen or choose a function (more later). To tell Excel which cells to use for the formula, click and drag with the mouse to select a series of cells and put the range that appears in brackets, or click on individual cells one at a time.
The answer will pop up when you hit enter.
- To copy a formula into all the cells in a row or column, select the cell containing the formula, right click or control C to copy, click and drag with the mouse to select all the cells where you want the formula to go, enter.
1 - Basic arithmetic
To add, subtract, multiply or divide, use the 4 basic ‘operators’.
The formulas are the same as normal sums, but with the = sign first.
2 - Totalling rows and columns using the AutoSum function
The formula for adding up all the numbers in a row or column is
=SUM (first cell address in row or column:last cell address in row or column)
= SUM (E14:E222)
But there’s no need to type out the formula or even select the cells. From either the Home or Formulas tab, choose the cell at the end of the row or column you want to add up, and click AutoSum. You’ll see the formula appear in the formula bar. Click enter.
3 - Adding up numbers in individual cells
To use SUM to add up numbers not in a single row or column, you’ll need to type the formula into the formula bar – for example, =SUM(A2,A9,C13). Rather than typing the cell addresses, though, just click on each cell one at a time to insert the address, adding a comma between each.
4 - Using the AVERAGE, MODE and MEDIAN functions
Remember school math lessons and the three types of average?
Mean: all the numbers added together and divided by how many numbers there are.
Mode: the most frequently-occurring value
Median: the value in the middle of the data set.
The Excel formula for calculating the mean average of a series of numbers is
=AVERAGE (first cell address in row or column:last cell address in row or column).
No need to type anything in or select all the cells. In the Formulas tab, click the cell at the end of the row or column you want to average, then click on the drop-down arrow by the AutoSum button and choose AVERAGE.
The mode and median functions aren’t in the AutoSum drop down. The easiest way to use them is to type =MODE or =MEDIAN into the formula bar and select the group of numbers you want to average (adding brackets at the start and end).
5 - Identifying where a certain condition is met – the IF function.
Say you want to know which values in column B are greater than 100. Select cell C1, and type =IF (B1>100,”yes”,”no”). You’ll see a Yes appear in C1 if the value in B1 is higher than 100, a No if it’s lower. Copy the formula into all the cells where you need it.
Even better, it’s easy to add a calculation to find out the total number of instances where the condition is met.
Choose a cell. For the example above (for a range of cells C1 to C20), type in the formula =COUNTIFS(C1:C20,”true”). You can add multiple ranges and criteria; just separate each with a comma and put quote marks around text.
6 - Calculating percentages
To find out what percentage a value in one cell is of another is easy.
Say the value in cell A1 is 20 and the value in cell B1 is 15 and you want to know what 15 is as a percentage of 20. Select cell C1, and type the formula =B1/A1. Then go to the home tab and click the % button in the number section.
7 - Tidying up text
Text that’s cut and pasted into Excel can often have untidy extra spaces. To fix this, simply click in a new cell alongside, type the formula =TRIM, and add the relevant cell selection in brackets. You’ll get a clean and shiny new list.
8 - Joining text from different cells
A common spreadsheet requirement is to join text in different cells – for example, bringing first name and last name into a single full name column. Fortunately, there’s a formula for this.
To combine a first name in cell A1 (Anna) and a last name in cell B1 (Mason), the formula =B1&”,”&A1 will give you Mason, Anna. Alternatively, the formula A1&” “&B1 will return Anna Mason.
9 - Creating a series of dates
If you spend far too long typing out date series, help is at hand.
To autofill a column with consecutive daily dates, type in the first date of the series. Click on the little green square on the bottom right hand corner and drag it down the column. The dates will appear by magic.
For a weekly, monthly or annual series, type in the first two dates, and click and drag the green square.