Introduction
Calculate Percentage in Excel :-The term “per cent” comes from the Latin per centum, meaning “by the hundred.” A percentage is a way to show a part of something out of 100. You can calculate it by dividing the part (numerator) by the total (denominator) and multiplying by 100.
The basic formula for percentage is:
(Part/Whole) * 100 = Percentage
For example, if you have 20 apples and give 5 to your friends, you can calculate the percentage like this: (5/20) * 100 = 25%.
In Excel, calculating percentages is easier because Excel helps you with some of the steps.
Microsoft Excel is a great tool for working with numbers and doing math. One common task is calculating percentages. Whether you’re analyzing sales, budgeting, or tracking grades, knowing how to calculate percentages in Excel is a must. In this guide, we’ll go over the key formulas to help you master percentage calculations in Excel.”
Now, let me show you a few easy formulas how to calculate percentage in Excel, like percentage increases and how to find the percentage of a total.
Method 1:- Calculate Percentage in Excel Using Excel ribbon
- Formula: The basic formula is Part/Total = Percentage
- No need to multiply by 100 because Excel does that when you format the result as a percentage.
Example:
Let’s say you have “Ordered items” in column B and “Delivered items” in column C.
To find the percentage of delivered items, follow these steps:
In cell D2, type =C2/B2 and press Enter.
- Copy the formula down for the rest of the rows.
- Select column D and click the Percent Style button (Home tab > Number group) to show percentages instead of decimals.
- If needed, increase decimal places for more accuracy.
- And that’s it! Easy as that 😄
Method 2:- Shortcut Method to Calculate Percentage in Excel
Example:-
- To find the percentage of delivered items, follow these steps:
In cell D2, type =C2/B2 and press Enter.
- Select the cell(s) with the calculation, then press Ctrl + Shift + % to convert the result to percentage format.
- Select the cell with the percentage format.
- Hover over the bottom-right corner of the selected cell until you see the fill handle (a small square).
- Click and drag down or across to apply the percentage format to other cells.
Method 3. Calculating Total Percentage using Sum Function
To calculate the total percentage of delivered items follow these steps:
- Use the Sum formula
=SUM(B2:B12)in a cell (e.g., B13) to get the total ordered items. - Use the Sum formula
=SUM(C2:C12)in another cell (e.g., C13) to get the total delivered items.
In another cell (e.g., D13), use the formula to calculate the percentage:-
“=Total Delivered/Total Ordered x 100”
- Example formula in Excel: =C13/B13∗100
- Press Enter
- You will get the total percentage 🙂
Method 4:- Calculate Percentage in Excel Using IFERROR Function to ignore error
- Normally, to calculate the percentage of delivered items, you would use the formula:
=(Delivered / Ordered) * 100
- Since you have cells with “out of stock” text or missing data, you can wrap the formula with IFERROR Function to avoid
#VALUE!errors.
Here’s how you can adjust the formula using IFERROR: = IFERROR((Delivered / Ordered) * 100, “”)
This will return a blank cell (“”) when there’s an error, like “out of stock.”
- Type the Formula in Cell D2 =IFERROR(C2/B2*100,”-“)
Now press Enter and Drag the formula down to apply it to the other rows. This will automatically handle any errors due to text like “out of stock” and return a blank value instead of #VALUE!.
FAQs
- What is the basic formula for calculating a percentage in Excel?
The basic formula is: =part/total. For example, if you have 25 apples out of 100, the formula would be =25/100, which gives you 0.25 or 25%
- How do I format a decimal as a percentage in Excel?
- To format a decimal as a percentage, select the cell, click the “%” button in the “Number” group on the “Home” tab, or press Ctrl + Shift + %. This will multiply the decimal by 100 and display it as a percentage.
- How can I calculate a percentage of a total in Excel?
To calculate a percentage of a total, use the formula: =part/total. For example, to find what percentage 30 is of 120, the formula is =30/120, which equals 0.25 or 25%.
