How to Calculate Percentages in Excel

Download ebook

Are you struggling to calculate percentage every now and then? Do you need to find a calculator or mobile phone every time when it comes to the calculation of percentages? Here’s a simple solution. You can calculate percentage in Excel. It is a very basic task in Excel.

Eventually to find percentage of the total data the learner should know the formula to calculate percentage in Excel. In this blog, we will learn the general formula to be used in Excel sheets which can easily help calculate the percentage of the lengthy data.

1. How to Calculate Percentage in Excel

Let us learn how to calculate the percentage in Excel using formulas with some examples additionally.Generally, the formula we use to calculate is same as in the mathematics i.e = part of work completed/total work to be completed x 100

Simultaneously, this formula can be used in Excel with nuance.

Example: Let’s understand improve our understanding through this example by simply calculating the percentage of travelling expenses for the year 2022.

1.Firstly, we have to calculate the total amount of travelling expenses for 12 months by adding all. In Excel the formula for adding is SUM(B4:B15)

2. Then the next step is to press the enter key after entering the formula

3. Now, calculate the simple percentage i.e. individually and then the percentage of the first month. As with reference to the image Column C will have a simple percentage and Column D will have the result of the percentage.

For the month of January, select the cell of expenses B4 dividing it by the total amount of expense which is 31200 as in cell B16 multiplied by 100.

Again after pressing the enter key, we get the January contribution to that total month. However, it is not in percentage form.

4. Now, use the following formula to find the percentage of a total and see how it is different from the simple percentage.Click on the enter key. As result, we can see the contribution of the month of January expenses from 12 months total (Total percentage)

Presently, both the values in Column C and Column D are in decimals rather than percentage. We can change it to percentage after we calculate the percentage for all 12 months.

2. Making the denominator absolute

So when we are willing to fix the denominator to calculate the percentage in Excel we have to use a simple formula. Additionally, we have to change the formula by adding $ in the denominator row and column as well.

So when we insert $ before cell formula it will fix the percentage.

Here, $B indicates not to rewrite column and $16 to rows consecutively. The $ sign makes the value absolute. This one is the most crucial step. So, do not skip it even by mistake.We will get the equivalent result after making the percentage formula absolute. Now, make the percentage of total formula denominator too.

Thereafter we can see the plus sign that appears on the right downside of the cell. We can alternatively calculate the percentage in Excel by dragging it till the last month. Once you drag, the result will be the calculation of all the travelling expenses for 12 months.

3. Changing the decimals to percentage

Currently, we can see in the image that all the percentages in Excel are in decimals for all the months. We can change it to decimal once in all. It can be done by simply selecting Columns C and D. Thereafter click on the home button in which the Percent style appears (%).

Once you complete the steps as mentioned above you will get the result i.e. you will get all the amount of Column C and Column D in percentage.

4. Increase or decrease decimal values in percentage

If you want to increase or decrease decimal values in Excel as a percentage, you can utilize the “Increase Decimal” and “Decrease Decimal” buttons in conjunction with percentage formatting. Here’s how you can do it:

Increase or decrease decimal values:

Select the cell or range of cells containing the decimal values you want to modify.

On the Ribbon, go to the “Home” tab.

In the “Number” group, you’ll find the “Increase Decimal” (with a “+” sign) and “Decrease Decimal” (with a “-” sign) buttons.

Click the “Increase Decimal” button to increase the number of decimal places or click the “Decrease Decimal” button to decrease the number of decimal places.

Apply percentage formatting:

Select the cell or range of cells with the modified decimal values.

Right-click and choose “Format Cells” from the context menu.

In the Format Cells dialog box, go to the “Number” tab.

In the “Category” list, select “Percentage.”

Specify the desired number of decimal places for the percentage if needed.

Click “OK” to apply the formatting changes.

By following these steps, you can increase or decrease decimal values as a percentage and format them accordingly in Excel.

Quick steps to calculate percentage in Excel:

Also check the video

Here’s how you can apply this formula to calculate percentages in Excel: Determine the value you want to calculate the percentage of. Let’s assume the value is in cell A1.

Identify the total or the reference value you want to use as the denominator. For example, if the total is in cell B1, use that cell as the denominator in the formula. In an empty cell, enter the formula =(A1/B1)*100. This formula divides the value by the total and then multiplies the result by 100 to get the percentage. Press Enter to calculate the percentage. You can then copy the formula to other cells if you have additional values to calculate percentages for.

Remember to adjust the cell references (A1 and B1) based on your specific data arrangement.

Conclusion:

• The percentage is always proportion per 100.
• In case the learner gets the error while calculating the percentage, it can eventually remove using the IFERROR function.
• By dividing the numerator and denominator and multiplying its result by 100 equal to percentage.
• In Excel, the users will always get the percentage in decimal. Thereafter, it has to be changed in percentage using % in the home tab-Number.
• Simultaneously, the user can also use a shortcut command to change data into percentage by Ctrl+Shift+%
• Besides this, the user can also observe increase and decrease decimals to see percentage in the appropriate way.

About Henry Harvin:

Henry Harvin is an unparallel institute when it comes to training and certifications. It has the Winner of the Top Corporate Training Award, Game-Based Learning Company of the Under 40 Business World Award.It provides Training + Projects + Internship + Certification + Placement + E-Learning + Masterclass + Gold Membership (all in one box).The learner can opt for the courses like Advanced Excel Training Course.

Recommended blogs

• What is COUNT in Excel, and how do I use the COUNTIF function
• Top 25 Microsoft Excel Formulas you should know
• Top 15 MS Excel Formulas One Must Know

Download ebook