Microsoft Excel – Calculating Percentages of Totals

You may have to work out the sales in each store as a percentage of the total sales over all stores.

Open a workbook with figures that need percentages and click in the cell where you want your answer.

In the example shown, you want to calculate what percentage the 2006 Turnover in Store 1 was of Total 2006 figures. You need to divide H5, the 2006 Store 1 turnover, by cell G10, the total turnover of all five stores. (To calculate what percentage 5 is of 25 you would divide 5 by 25, which is 0.2, and then multiply it by 100, giving an answer of 20%.)

Type an equals sign =, then type in the cell reference of the first cell to be used in the calculation, in this case G5. Alternatively, after typing in the equals sign, you could click in G5, or navigate to G5 using the cursor keys. Now type a division sign (/) followed by the cell reference of the total turnover, G10. Type a percent sign (%) to instruct Excel that this is a percentage calculation. Finally, press the enter key, or click the tick by the left of the formula bar, to finish the formula.

What about formatting the result???

This cell has to be set as an absolute cell reference so that when you have calculated the result for Store 1, you can use the fill handle to work out the percentage turnover for the other stores. Type G10 or click in the cell G10. Now hit the F4 function key on your keyboard.

You will see that Excel has put a dollar sign in front of the G and of the 10. This has nothing to do with currency, but is Excel’s way of indicating that the cell is an absolute cell reference. You can type in the dollar signs yourself instead of hitting the F4 key if you prefer. Finish by typing in a % sign, the click the tick on the Formula bar.

You can now use the fill handle to replicate the formula in the other cells. A good way of checking that your formula is right is to replicate the formula in the Total cell – it should read 100.

If you are using Microsoft Excel 2013 or Microsoft Excel 2016, you can use the Quick Analysis tool to work out percentages of totals without having to think about absolute cell references.

To use the Quick Analysis button:

  1. Select the data you want to analyse. The Quick Analysis button will appear at the bottom right of the range. Click on it or press Ctrl + Q.
  2. Select the category that you want to apply in this case TOTALS.

Quick Analysis

3.  From within the category, point at an option to see a preview of that analysis choice, in  this case % Total.

4.   To select an analysis choice, click on it and Excel will apply it to the data set.

Find out more about our Excel training London, UK or our Excel training Wiltshire, Dorset, Hampshire.

Find out more about our Excel training Sydney, Australia.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 (or 150 Aussie dollars)for every time someone says "If only I'd known that." ....
This entry was posted in Microsoft Excel, Microsoft Excel 2003, Microsoft Excel 2007, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s