The three ways to write a percentage formula

Excel calculates percentages the same way you do by hand: divide the part by the whole, then multiply by 100. The difference is that Excel can do this when ready across hundreds of rows. You write the formula once, and it repeats.

The most common formula is =A1/B1*100, where A1 is the part and B1 is the whole. If A1 contains 25 and B1 contains 200, the result is 12.5. You can also write =A1/B1 and format the cell as a percentage — Excel will multiply by 100 for you automatically. A third option is =(A1/B1)*100 with parentheses, which does the same thing but makes the order of operations clearer to anyone reading your spreadsheet later.

Key Takeaways

  • A percentage formula divides the part by the whole: write =A1/B1*100 or =A1/B1 and format as percentage.
  • Use the dollar sign ($) to lock a cell reference when you copy a formula down: =$B$1 stays fixed while A1 changes to A2, A3, and so on.
  • Format cells as percentage by right-clicking, choosing Format Cells, and selecting Percentage from the Category list.
  • Copy a formula across or down by clicking the cell, then dragging the small square in the bottom-right corner to fill adjacent cells.

Setting up your data before you write the formula

Before you type anything, arrange your spreadsheet so the part is in one column and the whole is in another. For example, if you are tracking how many people attended each event out of how many were invited, put the attendance numbers in column A and the invitation numbers in column B. Leave the first row for headers — "Attendance" and "Invited" — so you remember what each column holds.

Start your data in row 2. This leaves room to write your formula in row 2 as well, then copy it down to all the other rows. If your data starts in row 1, you will overwrite your numbers when you paste the formula.

Writing and copying your first percentage formula

Click on the cell where you want the percentage to appear — usually column C, row 2. Type =A2/B2*100 and press Enter. Excel calculates the result when ready.

Now copy this formula down to every row that has data. Click on cell C2 again. Look at the bottom-right corner of the cell — you will see a small square. Click and drag that square down to the last row with data. Excel automatically adjusts the formula for each row: C2 stays =A2/B2*100, C3 becomes =A3/B3*100, and so on. This is called a relative reference, and it is what makes spreadsheets powerful.

Using absolute references when you need a fixed number

Sometimes you want to divide every number in a column by the same total. For example, you might have sales by region in column A and the company's total sales in cell B1. You want to calculate what percentage each region represents of the total.

If you write =A2/B1*100 and copy it down, the formula will change to =A3/B2*100, =A4/B3*100, and so on — which is wrong. You need B1 to stay fixed while A changes. Add dollar signs: =A2/$B$1*100. The dollar signs lock that cell. When you copy the formula down, A2 becomes A3, A4, and so on, but $B$1 stays $B$1. This is called an absolute reference.

Formatting cells to show the percentage symbol

If you use the formula =A2/B2*100, your result will be a number like 12.5, and you have to remember it means 12.5 percent. If you use =A2/B2 instead, Excel gives you 0.125, which is harder to read. The clearest approach is to write =A2/B2 and then format the cell as a percentage.

Select the cells with your results. Right-click and choose Format Cells. In the window that opens, click the Number tab if it is not already selected. In the Category list on the left, click Percentage. You will see options for how many decimal places to show — usually 0 or 2 is fine. Click OK. Excel multiplies your decimal by 100 and adds the % symbol, so 0.125 becomes 12.5% or 12.50% depending on your choice.

Calculating what percentage one number is of another across different sheets

If your part and whole are on different sheets, the formula works the same way but includes the sheet name. If the part is in cell A2 on Sheet1 and the whole is in cell B1 on Sheet2, write =Sheet1!A2/Sheet2!B1*100. The exclamation mark tells Excel to look on a different sheet. You can also click the cell instead of typing the name — Excel will write the reference for you.

This is useful when you have raw data on one sheet and a summary on another. You can calculate percentages on the summary sheet without moving or copying your original numbers.

Avoiding common mistakes with percentage formulas

The most common error is forgetting to lock the denominator with dollar signs when you copy a formula down. You write =A2/B1*100, copy it to C3, and suddenly the formula becomes =A3/B2*100. Check your formula in a few cells by clicking them and looking at the formula bar at the top — it shows exactly what Excel is calculating.

Another mistake is dividing by zero. If a cell in your denominator column is empty or contains 0, Excel will show #DIV/0! as the result. You can prevent this by writing a formula that checks first: =IF(B2=0,0,A2/B2*100). This says "if B2 is zero, show 0; otherwise, calculate the percentage." This keeps your spreadsheet from looking broken when you have incomplete data.

Frequently Asked Questions

What is the difference between =A1/B1*100 and =A1/B1 formatted as percentage?

Both give the same result, but they work differently. The first formula multiplies by 100 inside the calculation, so the cell contains the number 12.5. The second formula keeps the decimal (0.125) and the percentage format displays it as 12.5%. For most spreadsheets, the second method is cleaner because the actual value stays as a decimal, which makes it easier to use in other calculations later.

Can I calculate percentages if my data has blank cells?

A blank cell in the numerator (top) will usually show 0%, which is often fine. A blank cell in the denominator (bottom) will show #DIV/0! error. Use the IF formula mentioned above to handle this: =IF(B2=0,0,A2/B2*100). This prevents errors and makes your spreadsheet look intentional rather than broken.

How do I calculate the percentage change from one number to another?

Percentage change uses a different formula: =(New Value - Old Value) / Old Value * 100. If sales were 100 last month and 120 this month, the formula is =(120-100)/100*100, which equals 20%. If the result is negative, the value decreased.

What if I want to show percentages with no decimal places?

Right-click the cells, choose Format Cells, select Percentage, and change the decimal places field from 2 to 0. Click OK. Now 12.5% will display as 13% (rounded). If you want to see the exact value without rounding, keep at least one decimal place.

Can I use percentage formulas in conditional formatting or charts?

Yes. You can write a percentage formula in a helper column, then use those results in a chart or as the basis for conditional formatting (which highlights cells based on their values). You can also write the percentage formula directly in some chart labels, depending on which spreadsheet program you use.