Calculate Percentage of a Number in Excel [4 Cases Explained]

We all know how important it is to know to calculate the percentage for our daily work life. Usage of percentage is almost everywhere, you can’t avoid it. I know some of you may find the percentage a bit disturbing because we can not always remember the basic rule of percentage all the time. The good news is Excel has a percentage feature and some easy formulas to calculate the percentages. Here, I am going to explain 4 cases to calculate the percentage of a number in Excel.

Get Percentage of a Number in Excel.xlsx

Table of Contents

Basics of Percentage

In the simplest words, the Percentage means parts per 100. 100 is considered the total number when we calculate the percentage. It has no unit and a symbol like ‘%’ is used to express the percentage.

Percentage = (Part/Total number) x 100

To give you an example, 25% means, 25% =25/100 = 1/4

And if, you have 200 books and so far you read 25 books from there. So you have finished reading 12.5% of your books: =(25/200)*100 =12.5%

Now to show you some scenarios of percentages I have a dataset here with some information about a clothing shop. It has a list of different types of products and the number of products and amount of sold products. We are going to find out the percentage of sold products.

Calculate Percentage of a Number in Excel by Applying a Formula

To calculate the percentage of a number in Excel, you can either write down the formula of the percentage or use the Percentage option of Excel:

=C2/B2

In this formula, C2 is the number of sold products, so it is part of the whole portion. That’s why we will divide the C2 by B2. Here B2 is the number of the full amount. Always keep the small number first and divide that with the bigger number.

But using the percentage command will be the fastest way to do that:

Select an empty cell (cell D2).
Type the following formula: =C2/B2
Hit ENTER.
This will give you the result of the fraction.

Select the Home tab.
Inside the Number group, click on the ‘%’ symbol (or press CTRL+SHIFT+%).
Now, double-click on the Fill Handle ‘+’ icon.

This turns the results into percentages.

Note: This method shows the integer values only. But if you want to increase the decimals, and keep the numbers after the point, you have to follow a few extra steps. To increase the decimal:

Select the Home tab.
From the Number group, click on the drop-down icon.

Select the Percentage option from the drop-down list.

Now, the percentage has two numbers after the point.
Copy the formula down to the range of D2:D9 using the Fill Handle.

Here is the final result.

Note: You can also increase or decrease the decimals by using the decimal commands.

Calculate the Percentage of a Number in Excel with Total Amount in a Different Cell

Now, for instance, the dataset has a common total value of all products, unlike the previous scenario.

The formula to calculate the percentage of numbers in Excel with the total amount in a different cell:

=B2/$B$10

In the formula above, B2 is the number of T-shirts. We want the B2 to be changed in the formula so we have not applied any cell reference here. But we want the Total to be fixed in the formula. So we have used the absolute cell reference in B10 and turned it into $B$10.

This time the method will be different. To do that, follow these steps:

Select a cell.
Write this formula: =B2/$B$10
Note: Press F4 after typing B10 to apply absolute cell reference.

Press ENTER.
Click on the Home tab.
Click on the ‘%’ symbol (or press CTRL+SHIFT+%).

Copy down the formula using the Fill Handle.

Case 1: Calculate the Percentage of Various Numbers in Excel

You can calculate the percentage of a number in Excel. Here is the formula below:

=(B2*A2)

This formula means cell A2 has the Percentage(%). B2 has a value of which I want to find the 12%. The asterisk mark (*) works here as a multiplier.

Now, go through the steps below:

Select an empty cell.

Write the formula in: =(B2*A2)
Press ENTER.
Double-click on the Fill Handle ‘+’ icon.

This way you can calculate the percentage of a number.

Case 2: Calculate a Decrease of Percentage in Excel

If you want to decrease a percentage from the total value, there’s a formula for that. For instance, each month you want to invest a percentage of the amount from the monthly revenue. In that case, you have to minus the percentage of investment from the revenue.

The formula below,

=B2*(1-C2)

In the formula above, B2*1 means B2 which is the amount of revenue. And B2*C2 returns the value of the 12th portion of $25,000. So when subtracting B2*C2 from B2*1 returns the value of change.

To calculate a decrease of percentage in Excel, follow these steps:

Select an empty cell.
Write this formula: =B2*(1-C2)

Press ENTER.
Double-click on the Fill Handle icon.

Fill Handle has copied the formula down to D2:D10.

Case 3: Calculate the Percentage of a Number in Excel with Scattered Parts of a Total

If you are working with a lengthy dataset with repeated product names but different values, you can still find the percentage of a particular repeated product.

We are going to use the SUMIF function for this method.

The formula below with the SUMIF function is to calculate the percentage of a number in Excel with scattered parts of a total:

=SUMIF(A2:A11,"Top",B2:B11)/$B$12

The formula says, range A2:A11, is selected to find the product name “Top”. Then the function will sum up the number of Top from range B2 to B11. Then the sum-up value will be divided by B12 (total value).

To calculate the percentage of a number in Excel with scattered parts of a total, follow these steps:

Select a separate cell (D3).
Write the formula in: =SUMIF(A2:A11,”Top”,B2:B11)/$B$12

Press ENTER.
Select the ‘%’ symbol from the Number group.

You will get the result in percent (%) after that.

Case 4: Calculate an Increase of Percentage in Excel

This method is similar to calculating a decrease in percentage. You just need to modify the formula a bit.

To calculate an increase of percentage in Excel, follow these steps:

Select cell D2.

Type the following formula: =B2*(1+C2)
Press ENTER.
Click on the Fill Handle icon.

Here is the final outcome of the formula.

Conclusion

I have tried to show you the most used formulas in Excel. But there are many other formulas for percentages. I hope you have found what you are looking for in my article. Let us know how you like this blog. Thank you.

Frequently Asked Questions

How to do calculations in Excel?

To perform calculations in Excel:

Click on the cell where you want the result of your calculation.
Type an equal sign (=) in the selected cell. This signals Excel that you are entering a formula.

Input the mathematical expression or use a function. For example, to add numbers in cells A1 and B1, type =A1+B1
Press the ENTER key to execute the formula. The result will appear in the selected cell.
Alternatively, you can use the formula bar to directly input and edit formulas.

For more complex calculations or functions, explore Excel’s extensive library of built-in functions, accessible through the Formulas tab. Familiarize yourself with common functions like SUM, AVERAGE, and IF for various calculations.

Why is Excel not calculating?

Excel may not be calculated due to various reasons. Here are common issues and solutions:

Automatic Calculation Disabled: Check if automatic calculation is disabled. Go to the Formulas tab, click on Calculation Options, and ensure Automatic is selected.
Circular References: Circular references, where a formula refers to its cell, can cause calculation issues. Identify and resolve any circular references in the workbook.

Errors in Formulas: Check for errors in your formulas. Invalid references, mismatched parentheses, or incorrect functions can prevent calculations.
Data Types: Ensure that data types are consistent. Excel may have trouble with calculations if data types (text, numbers) are mixed.
Calculation Options: In the Formulas tab, go to Calculation Options and make sure Automatic is selected. If set to Manual, formulas won’t update until you manually recalculate.

Large Data Sets: Large datasets can slow down calculations. Consider optimizing your workbook, using filtering, or breaking down complex formulas.
Protected Worksheets: If the worksheet is protected, certain cells or ranges may be locked, preventing calculations. Unprotect the sheet if necessary.
Add-Ins or Macros: Third-party add-ins or macros might interfere with Excel’s calculations. Disable them and see if the issue persists.

Corrupted Workbook: The workbook itself may be corrupted. Create a new workbook and try rebuilding your formulas.

By addressing these potential issues, you can troubleshoot and resolve Excel calculation problems.

Related Articles

Rate this post