Calculating Percentages of a Total in Excel

You can calculate percentages in Excel using basic multiplication and division. I’ll show you how to use this to make quick work of calculating a column of percentages.

In the example below, column B shows travel expenses for the year, and a yearly total (using the SUM function). In column C, we want to know the percentage that each month contributed to the total. Think of a pie chart: the total in B18 is the whole pie, and the percentages in column C will be the slices.

Percentage Formula 1_expenses

In C5, this formula divides the January amount by the total:
=B5/B18

But I won’t enter it, yet! In order to save time and minimize data entry, I want to auto-fill the formula down to row 18. This will change the numerators (January through December), but leave the denominator fixed on B18 because I want to show each month divided by the same total.

To do this, I’ll make the denominator an absolute reference by clicking it and pressing the F4 key on the keyboard. That makes the formula look like this:

=B5/$B$18

Tip: $B tells Excel to not rewrite the columns when I fill, and $18 tells Excel not to rewrite the rows. In this instance, it doesn’t matter if I have a dollar sign before the B or not, since I’m going to fill straight down the column, and it wouldn’t change, anyway.

Now I’ll enter the formula, but the result is a decimal:
Percentage Formula 1_decimal

Instead of showing this long decimal, I want a percentage and I want to round it off after two digits. This takes just one click:

Select the long decimal in C5, then click the Percent Style button Percentage Formula 1_percent style on the Home tab of the Ribbon bar.

Tip: To increase or decrease the number of decimal places showing, click the Increase Decimal or Decrease Decimal button on the Ribbon bar.

This gives me the percent that I want:
Percentage Formula 1_13

Now I’ll auto-fill the formula down to row 18. When you click any cell, do you notice the small dot in the lower-right corner?
Percentage Formula 1_dot

When you roll the mouse over the dot, the mouse pointer becomes a crosshair.
Percentage Formula 1_crosshair

Now I’ll drag the crosshair downwards:
Percentage Formula 1_drag

Now the entire column is filled with percentages (I deleted the zero in C17).
Percentage Formula 1_completed

If you’d like to examine this worksheet, download Percentage Formula 1_travel expenses.xlsx. You can test yourself by deleting all the numbers down column C and trying it on your own.

Local Seminars Related to this Topic:

Related Advanced Excel Articles

Categories

Monthly Archives

24 Comments

  • Marshall Olchowy says:

    Thank you for a clear,simple and practical instruction. Not always easy to find.

  • Cris says:

    You literally just helped me figure something out that has been driving me crazy all day. All I was doing was dividing the wrong cells…mind…blown. I also agree with Marshall. Most tutorials try too hard. This is exactly what I needed. Thank you.

  • saumyaa says:

    HOW TO FIND A PERCENTGE WHEN 3 VALUE’S TOTAL IS GIVEN ……………..BY THE WAY YOU JUST GAVE THE SIMPLEST EXPLANATION 🙂

  • Eva says:

    so easy. I love it.

  • subash says:

    got it thanks

  • Millie says:

    Wow, that’s the simplest solution ever I have been given to solve a problem. I have pulled many hairs from my head trying to calculate percentage. You just made it easy like banana. Thanks

  • Albert says:

    thank you. simple explanations always work best.

  • Man Darino says:

    Not to be redundant but really clear and easy. Thanks a bunch!

  • smn says:

    PLEASE ADVISE PERCENTAGE CALCULATION FOR THE FOLLOWING FIGURE:

    28,665 – 11,466 = 17,199 ( HOW CALCULATE THE PERCENTAGE % )

  • Cheryl says:

    Hello ~ I am trying to figure out how to break down a sum total into 3 payments. This is for a construction contract form. *For example: Total amount customer owes is $12,000. Payments due are as follows: 30% due at contract signing, 50% due at start of work and 20% due at completion of work. What is the formula for figuring these out? Any feedback would be appreciated. thank you!

  • Helen says:

    Thank you! The directions were clear and very simple to follow. I was successful my first time using these directions. I wish all websites explained the how-to’s this clearly!

  • Darlene says:

    Hi, I’m trying to create a budget sheet where each item should be a specific percentage of my total income. Ideally I’d like the spreadsheet to calculate what I SHOULD be spending in each area based on my income (i.e. the 12 jars method: http://www.getrichslowly.org/blog/2009/07/08/learning-to-budget-with-the-jars-system/), but if the actual cost is higher, I’d like it to also recalculate what I need to be earning (and adjust the other figures) based on what I’m spending.

    Is such a thing possible?

  • Stephen says:

    Hello,

    I need to work out and show the total percentage of work done in a project.
    I have 4 stages each need to achieve 100%. I need to show during the progress reports, percentage of each stage plus the total % across all 4 stages as well as change from last reporting period.
    Looking at your examples I can see ways of doing bits of this but not all.

    Many Thanks for your help

    • Excel Tips and Tricks from Pryor.com says:

      Using Excel as a project manager is thoroughly do-able and it’s a large subject. If you open a New file in Excel and type “Project management” into the Templates Search box, you’ll get quite a selection.

  • Tom says:

    Is there a function I could use to get a percentage range back?

    For example, I’d like to know what the range of 10-15 percent of the total money in another cell. So if a certain cell has a total of $100, I’d like to get the range of $10-$15 back in the cell with the formula in it.

    • Excel Tips and Tricks from Pryor.com says:

      Getting a “percentage range” is well within Excel’s capacity but it does imply a cell range as an answer – not a single cell.
      To get $10-$15 as an answer we’ll need two cells to hold it. (a range with two cells)
      (see below)

      The first cell (C1) will be the target amount times ten percent. ($100 * 10%)
      The second cell (D1) will be the target amount times fifteen percent. ($100 * 15%)
      Reply
      Note “Fifteen percent” is mathematically stated 0.15
      If you’d like that range displayed as a single cell, you can concatenate the answers in C1 and D1 as a text. (using the ampersand or CONCATENATE function)
      (see below)
      Reply2
      F1 won’t be calculable, itself, or useful in any other calculations, only C1 and D1 will be.

      Excel will, in all circumstances, deal with a range as more than one cell.

  • yordanos asheber says:

    thank you very much , it makes my work easy ,and simple

  • Chris says:

    Thanks…I know it’s a simple formula but this helped me a lot

  • aj says:

    Cell B1 has an amount of $5785.00, how do I get cell B2 to reflect a 10% discount total (ex. $5206.50) the discounted amount?

    • Excel Tips and Tricks from Pryor.com says:

      To get this solution, we’ll be executing two steps at once.
      1) Get 10% of B1
      2) Subtract that amount from B1

      The math to solve this is “B1 minus ten percent of B1”.
      That’s written out as =B1-(B1*0.1)
      reply

      It might be clearer to a user, other than the author to place the 10% in its own cell.
      reply

      This alternate approach makes it clear to first-time users of your file that the discount is 10% and displays exactly how much is discounted.

  • Maria says:

    Hi, I need to provide a spread sheet for my boss to show percentage of his total revenue and percentage of total expenses on a monthly basis, I am a little lost…… help!

    thank you in advance for your assistance in this matter

    • Excel Tips and Tricks from Pryor.com says:

      Excel has a wide variety of functions to make complex calculations but we won’t need them for this kind of problem.
      To determine “What percentage X is of Y.” We only need to divide X by Y.

      1/100 is 0.01 (Decimal notation for 1%)
      50/100 is 0.50 (Decimal notation for 50%)

      If total revenue is $100,000 and total expenses are $25,000 we get 25000/100000 as 0.25.
      If we format 0.25 as a percentage (below) we’ll get 25%
      reply

Leave a Reply

Your email address will not be published. Required fields are marked *