The simplest way to calculate a percentage in Excel

To find a percentage in Excel, you divide the part by the whole, then multiply by 100. In a spreadsheet, you write this as a formula: =A1/B1*100, where A1 is the part and B1 is the whole. Excel will show you the result as a number.

If you want the result to display with a percent sign (like 25% instead of 25), select the cell with your answer, then click the percent button (%) in the toolbar. This formats the number as a percentage without changing the calculation itself.

Most people find it easier to skip the *100 step and let Excel's percentage format do the work. Type =A1/B1, press Enter, then click the % button. Excel will show 0.25 as 25% automatically.

Key Takeaways

  • The percentage formula is the part divided by the whole: =A1/B1, then format as percentage.
  • You can multiply by 100 in the formula (=A1/B1*100) or use Excel's percentage button in the toolbar to format the result.
  • Percentage format shows 0.25 as 25%, but the underlying number stays the same.
  • When calculating what percentage one number is of another, always put the smaller number (the part) in the numerator and the larger number (the whole) in the denominator.
  • You can copy a percentage formula down a column to calculate percentages for multiple rows at once.

Setting up your data before you calculate

Before you write any formula, arrange your numbers so the part is in one column and the whole is in another. For example, if you want to know what percentage of your budget went to rent, put the rent amount in column A and the total budget in column B.

Label your columns clearly so you remember which is which. A header row that says "Rent Paid" and "Total Budget" takes two seconds and saves confusion later. This is especially important if you are building a spreadsheet someone else will read.

Make sure both numbers are in the same units. If one is in dollars and the other is in cents, or one is in thousands and the other is not, your percentage will be wrong. Convert them first, then calculate.

Using the percentage formula on multiple rows

If you have a list of items and want to find what percentage each one represents, write the formula once, then copy it down. Put your first part in A2 and your whole in B2. In cell C2, type =A2/B2 and press Enter.

Now click on C2 to select it. You will see a small square in the bottom right corner of the cell. Click and drag that square down to the last row with data. Excel will copy the formula and automatically adjust the row numbers, so C3 becomes =A3/B3, C4 becomes =A4/B4, and so on.

After you release the mouse, select the entire column C (or just the cells with formulas), then click the % button in the toolbar. All your results will display as percentages at once.

Finding what percentage increase or decrease occurred

To calculate how much something changed as a percentage, use this formula: =(New Value - Old Value) / Old Value. If a price went from $50 to $75, you would calculate (75-50)/50, which gives 0.5 or 50% increase.

The key is always dividing by the original number, not the new one. If you divide by the new number instead, you will get the wrong answer. The old value is your "whole" in this case, because it is what you are measuring change against.

If the result is negative, that means a decrease. A formula that returns -0.2 means a 20% decrease. Format it as a percentage and Excel will show -20%, which makes the direction of change obvious.

Calculating the total when you know the percentage

Sometimes you know the percentage and one number, and you need to find the other. If something is 25% of 200, and you want to know what that something is, use =Percentage * Whole. In Excel, if the percentage is in A1 and the whole is in B1, type =A1*B1.

Make sure your percentage is in decimal form (0.25, not 25) for this to work. If you have 25 in the cell, divide it by 100 first: =A1/100*B1. Or type it as 0.25 directly in the formula.

If you need to find the whole when you know the part and the percentage, rearrange the formula: =Part / Percentage. If 50 is 25% of something, you would calculate 50/0.25 to get 200.

Avoiding common mistakes with percentages

The most common error is putting the numbers in the wrong order. Remember: part goes on top, whole goes on bottom. If you calculate 200/50 instead of 50/200, you will get 4 or 400% instead of 0.25 or 25%.

Another mistake is forgetting to format as percentage after you calculate. Your formula might be correct, but if the cell shows 0.25 instead of 25%, anyone reading the spreadsheet will misunderstand the result. Always format the final answer.

Watch out for dividing by zero. If your "whole" column has a zero in it, Excel will show #DIV/0! in that row. You can prevent this by using an IF statement: =IF(B2=0,0,A2/B2). This tells Excel to show 0 if the whole is zero, instead of an error.

Using percentage formulas in real spreadsheets

In a budget spreadsheet, you might calculate what percentage of your income goes to each expense. Put your expenses in column A, your total income in column B, and use =A2/B2 for each row. This shows you at a glance where your money goes.

In a sales spreadsheet, you might track what percentage of your goal each person reached. If the goal is 100 sales and someone made 75, the formula =75/100 shows they reached 75% of target. Copy this formula down for your whole team.

In a grade spreadsheet, you can calculate what percentage of points a student earned. If an assignment was worth 50 points and they earned 45, the formula =45/50 shows 90%. This works the same way whether you are tracking one student or a hundred.

Frequently Asked Questions

How do I show percentages with decimal places in Excel?

Right-click the cell with your percentage, select Format Cells, choose Percentage, and change the decimal places number. If you want 25.5% instead of 25%, set decimal places to 1. The default is usually 2 decimal places.

What does it mean when my percentage is over 100%?

A percentage over 100% means the part is larger than the whole. If you earned 150 out of 100 possible points on a test, that is 150%, which is correct. This happens with bonus points, overfilled containers, or when comparing a larger number to a smaller one.

Can I calculate percentage of percentage in Excel?

Yes. If you want 25% of 50%, multiply them: =0.25*0.50, which gives 0.125 or 12.5%. This is useful when you have nested percentages, like a discount on top of a discount, or a percentage of a percentage-based allocation.

How do I make a percentage formula that always uses the same cell?

Use a dollar sign before the column and row you want to lock. If your total is always in B2 and you want to copy the formula down, type =A2/$B$2. When you copy this down, A2 changes to A3, A4, and so on, but B2 stays B2.

What if my numbers are in different columns and I want one percentage for all of them?

Use the SUM function to add them up first. If your parts are in A2 through A10 and your whole is in B2 through B10, type =SUM(A2:A10)/SUM(B2:B10). This adds all the parts, divides by all the wholes, and gives you one overall percentage.