OmniDocHow to write percentage and proportion formulas in Excel
Explain clearly how to use formulas to calculate proportions, increases and decreases, and 1:x ratios in Excel, including denominator locking, cell formatting, and handling of common incorrect values, with a checklist attached.
Key points of this article
- Use "part ÷ whole" for the proportion, lock the denominator with $, and then set the cell format to percentage. The value will still be a decimal, and subsequent calculations will not be affected.
- Use "(new value - old value) ÷ old value" to calculate the increase or decrease. If the result is negative, it means a decrease. If the old value is zero, use IFERROR.
- 1:x ratio uses "ratio" to get x. When text format is required, use the TEXT function to control the number of decimal places.
- The numbers look normal but cannot be calculated. They are mostly in text format and can be converted using the VALUE function or columnarization.
- After finishing, check whether the total is consistent with the whole, whether there are any error values, and whether the denominator reference is correct.
When calculating proportion, growth rate, or the ratio of two numbers in an Excel spreadsheet, many people will first use a calculator to calculate it and then fill it in manually. In fact, it can be completed with one formula, and the results will be automatically updated after the original data is changed. This article talks about how to use formulas in Excel to calculate percentages, covering proportions, increases and decreases, percentages of a certain number, and ratios in the form of 1:x.
The real trouble is usually not with the formula itself, but with the result being displayed as 0.25, #DIV/0! appearing, or numbers in text format not participating in the calculation. The following will first give the conclusion and comparison table, and then talk about the operating steps and inspection methods.
How to calculate the percentage using formula? One sentence conclusion
First answer in one sentence: Enter the formula "part ÷ whole" in a blank cell, and the result will be a decimal. Then set the number format of the cell to percentage, and it will be displayed as a percentage. The value in the cell is still a decimal, and subsequent calculations are not affected.
| your situation | Recommended practices | reason |
|---|---|---|
| Find the proportion of an item to the total | =B2/$B$10, set the cell to percentage | When dividing to get a decimal, the percentage format only changes the display, not the value. |
| Find the percentage of a certain number | =B2*15%, written directly in the formula | Excel will calculate 15% as 0.15 |
| Find the growth rate or increase or decrease | =(C2-B2)/B2, set as percentage | Divide the difference between the old and new values by the base. A positive result indicates growth, and a negative result indicates decline. |
| Find the ratio of two numbers | =C2/B2, keep the numerical format | The ratio is the basis for subsequent writing of the 1:x ratio |
| Need to be displayed as "1:x" text | ="1:"&TEXT(C2/B2,"0.00") | TEXT controls the number of reserved digits, but the result is text and cannot be included in the calculation. |
Before you start: Confirm these points first
- Are the numbers real numbers: Numbers are aligned to the left, with a small green triangle in the upper left corner of the cell, which is usually text. Text cannot directly participate in division, and a #VALUE! error will occur. You can use the VALUE function to convert, or use the column sort function in data tools to convert the entire column into numbers.
- Where to put the overall value: The proportion formula must refer to the total cell. If the total is B10, write it as $B$10, and add a dollar sign to lock it so that the denominator will not move when you copy it down.
- Can the denominator be zero: When performing division on blank rows or rows with unfilled data, #DIV/0! will appear, which needs to be processed with IF or IFERROR in advance to prevent error values from spreading to the summary results.
- Determine the caliber first: The algorithms for proportion (part ÷ whole), growth rate ((new-old) ÷ old), and ratio (A:B) are different. If you choose the wrong formula, the result will be meaningless no matter how standardized it is.
- Only change the display of decimal places: After setting the percentage, it is recommended to keep the number of decimal places uniformly according to the reporting needs. Do not round in advance during calculation, otherwise the sum of the items may not add up to the total.
Steps to calculate the percentage of each item to the total in Excel
- Write the project name in column A and the corresponding amount in column B. For example, the data is located in rows B2 to B9.
- Enter =SUM(B2:B9) in B10 to get the total, which is the denominator of all subsequent proportions.
- Enter =B2/$B$10 in C2 and press Enter to get a decimal, such as 0.25.
- Select C2, open the cell format settings, select Percent in the Number category, and set the number of decimal places.
- Drag the fill handle in the lower right corner of C2 and copy the formula down to C9. Each row will reference the same total cell.
- Enter =SUM(C2:C9) in C10 and confirm that the result is consistent with the whole, that is, the total is displayed as the proportion of all.
- If you only want to calculate the percentage of a certain number, directly enter =B2*15% in the blank cell and press Enter. There is no need to set the format.
Check each item after completion
| Check items | How to see | What should you do if there's a problem? |
|---|---|---|
| Total percentage | Whether C10 matches the overall is displayed as the overall proportion | If the discrepancy is large, check whether a certain line is missing or if the denominator reference is misaligned |
| Is the denominator locked? | Click C5 to see if the denominator in the formula is still $B$10 | If not locked, add a dollar sign and refill it |
| Error value | Are there #DIV/0!, #VALUE! | When the denominator is zero, use IFERROR; for text numbers, first convert them to VALUE |
| Values and display | Is the edit bar 0.25? The cell shows 25%. | This is normal; the value is a decimal, so don't manually multiply by 100 |
| Digital format | Whether the numbers involved in the calculation are aligned to the right | The numbers on the left are mostly text, converted to numbers using columns or VALUE |
How to write a proportional formula in Excel? 1: x and the fluctuation algorithm
To calculate the ratio of two numbers, the key is to first determine which one is the baseline. For example, if column A is 200 and column B is 500, and you want to express it as "A : B = 1 : x," then x is the result of B divided by A.
- Enter =C2/B2 in the cell to get 2.5, meaning that when A is 1, B corresponds to a value of 2.5, i.e., 1 : 2.5.
- To display as text, enter ="1:"&TEXT(C2/B2,"0.00"), resulting in 1:2.50. The second parameter of TEXT determines the number of decimal places to be retained.
- If both numbers are integers, to find the ratio of coprime integers, you can use the GCD function to find the common divisor of the two numbers and remove them, for example: =B2/GCD(B2,C2)&":"&C2/GCD(B2,C2).
The price change is written as = (C2 - B2) / B2. A positive result indicates growth, while a negative result indicates a decline. When the old value B2 is 0, division cannot be performed; it can be written as =IFERROR((C2-B2)/B2,""), leaving the empty value blank. Also, pay attention to distinguishing between two concepts: going from 10% to 15% is a 5 percentage point increase, while a growth rate is 50%.
📊 Core Comparison
| Method | Formula example | Result form | Suitable for the scene |
|---|---|---|---|
| Partial ÷ overall plus percentage format | =B2/$B$10 | Showing 25%, value 0.25 | Proportion statistics, results still need to be calculated |
| Multiply directly by the percentage | =B2*15% | Numerical values | Find the percentage of a number |
| Ratio | =C2/B2 | Numerical values | Calculate x in 1:x |
| TEXT splicing | ="1:"&TEXT(C2/B2,"0.00") | Text | Only for display and no longer involved in calculations |
| Growth rate | =(C2-B2)/B2 | percentage | Percentage change, year-on-year or month-on-month |
❓ Frequently Asked Questions (FAQ)
1. What does it mean when the cell shows 0 after entering the formula?
Usually, cells are set to integer format, and decimals are rounded to 0. Change the format to percentage or regular, and the results will display correctly.
2. Why does the cell change to time or date after entering 1:3?
Excel recognizes entries with colons as time. If you want to keep the original format, you can first set the cell to text format before entering, or use the TEXT concatenation formula from earlier to generate scale text.
3. If there is no total row in the table, how do you calculate the proportions?
You can directly use SUM in the formula to find the denominator, for example, =B2/SUM($B$2:$B$9), without starting a new sum row. However, each cell is counted repeatedly, making it difficult to check if the formula is long. If there is a lot of data, it is recommended to set up a separate total cell.
📝 Summary
The core of Excel percentage calculation is to first determine the numerator and denominator, then divide to get the decimal and display it as a percentage using cell formatting. Proportion, price change, and 1:x ratio each have fixed notations. Denominator locking, error value handling, and text numeric conversion are the three common issues. After completion, check the checklist once more; the results will be more reliable.
