Hands up if you’ve ever managed to lose a day or two trying to format an Excel spreadsheet!
Don’t worry, it happens to the best of us! Spreadsheets are an invaluable business tool when used correctly, however it’s easy for them to become large and out of control as they grow. Formatting formulas is a classic example of where even the simplest of spreadsheets can become a time consuming task – and this is something that really shouldn’t take any time at all.
Quite often, you will use a formula in a cell that then needs to be applied to a whole row or column, however, the formula is based upon cell references (e.g. A1 + B1) and therefore needs to alter slightly to reflect the relevant locations and cells to apply the formula on.
One way of doing this might be to manually apply the formula to every cell that needs it. This is doable if you have only a few rows of data – but what if you have hundreds, or even thousands of rows?? Then it becomes more of a challenge. The good news is there is a simple solution – the fill handle.
How to fill formulas in Microsoft Excel
Take a look at this simple spreadsheet calculating tickets booked for an event. We want to use a simple sum formula to work out how many tickets each member has ordered, and then how many member and non member tickets are required.
Lets start by inputting our sum formula into cell E2 as shown here. Press ‘Enter’
Then click on cell E2 again to make it the active cell.
If you hover with you mouse over the bottom right hand corner of the cell (there is a small black dot there) you will see that the pointer will change to a small black cross ( + ) when you have it in the right position over the fill handle. At this point when you see the black cross, click and hold down the left mouse button. Next, keeping the mouse button pressed down, drag the fill handle down to fill the cells that you want to copy the formula into.
When you release the mouse button, the cells will be automatically be populated with the new formulae. You will see that the cell references in the formulae have automatically changed. You can then also do this in exactly the same way for formula across the bottom row of ‘totals’. In the screenshot here you can see that after using the fill handle, you get a small ‘actions’ menu pop up – here you can choose if you wanted to fill the formula across, or alternatively you can simply copy the formatting instead which is another time saving trick for formatting your spreadsheets easily.