One of the most useful benefits Excel provides is a straight-forward way of calculating data. In the below example I will be demonstrating how to create a margin formula which will be recalculated on each row.
By default formula calculations are set to automatic, however sometimes they can be switched off. To double-check they are turned on, in the tool strip click “Formulas”, “Calculation”, “Calculation Options” and click “Automatic”.
Click on the first blank cell of the column you would like to populate with your formula.
Press = to change the cell to formula mode. When the cell is in formula mode if you click on another cell it will populate your active cell with that address.
You can also type your formula if it is more convenient =C2-D2
After you are satisfied with your formula hit enter and your first cell will be populated with the resultant value.
To copy this formula into the rest of the column, click on the bottom right-hand corner of your new formula cell and drag down to populate the column. Alternatively if you know that you want to populate the whole column, double-click on the right-hand corner and excel will populate all of the cells available until Column D has a blank space (which in the above example is D8).
If you now click on one of the populated cells you can see that the formula has changed with the cell index. D4 has the formula “=C4-D4”.
After you are happy with how the data is displayed and are confident that it will stay static (e.g. you know that the sales for LargeData will stay at 5645) it is good practice to remove the formula from the cells. At the moment if the Sales or Cost data is modified the Margin data will be too, which may be beneficial if your data is going to change.
Click the E column to highlight it and press Ctrl and C to copy the data to the clipboard.
Right-click in the first cell of the column and click “paste special.
Change from the default option “All” to “Values”. Now only the numerical value will remain.