Percentages How do I work with these? ( Image Source: FreeImages.com by Umut Kemal)
Percentages? How do I work with these? (Image Source: FreeImages.com by Umut Kemal)

Working out percentages of the whole in Excel may sound terribly simple to some of you but I come across this problem all the time in my role of floor walking. Trying to figure out how to calculate a percentage of the whole is a little daunting to some so here we have a simple spreadsheet that we will work on.

Percent1

N.B. This Tip is written in Office 2016 so images of tools or ribbons may differ from your version. The features still work the same.

So here we have a list with one calculation at the bottom of each column giving us the total of each column. We would like to know what percentage each record is in comparison to the totals at the bottom.

Start by replicating the titles on the side and top by using the multiple copy then paste. I have elected to paste in cell A11.

In cell B12 type in the following = B5/$B$9

The dollar signs are there to anchor the cell B9 and this is called Absolute Referencing. A topic I that has been covered in a previous tip.

Fill this down the page to line 16 where you should get a result of 100% if this is not the case then there is something wrong in your calculation. I suggest that you start again, as I find that trying to decipher what is wrong in a formula takes much more time than starting again. For some reason it always works out the second time round.

Percent2

The result is this showing the formulae. Percent3

Fill in the rest of the calculation in the table and you have your results.

If on the other hand you require the percentage of the entire table, then make sure your formula is selecting the grand total cell and absolute reference that one. Percent4

Resulting in this. Percent5

LEAVE A REPLY

Please enter your comment!
Please enter your name here