Calculating the percentage as a proportion of a specified value (for example, if you eat 4 out of 5 mangoes, what percentage of mangoes have you eaten). Calculating the percentage change (YoY or MoM). This is usually used in sales reporting where the manager would want to know what’s the sales growth Year on Year, or Quarter on Quarter.
In this tutorial, I will show you the formula to calculate percentages in Excel as well as to format the cell so that the numbers show up as percentages (and not decimals). So let’s get started!
Calculating Percentage as a Proportion
Examples of this would be to find sales coverage or project completion status. For example, your sales manager may want to know what percentage of the total prospective customers can be reached effectively in a region. This is known as sales coverage. Based on this, he/she can work on the sales coverage model and/or channels to reach more customers. Here is a sales coverage example for three regions:
Looking at the example above, it would be instantly clear that the coverage is lowest in Region C, where the manager may plan some new initiatives or campaigns. Here is the Exxcel formula to calculate the percentage in Excel: Within Excel, you can enter =B3/B2 to calculate the percentage for Region A. Note that this would give a value in General/Number format and not in the percentage format. To make it look like a percentage, you need to apply the percentage format (shown later in this tutorial).
Calculating Percentage Change in Excel
Percentage change is widely used and monitored in various areas of business. Analysts usually talk about a company’s revenue growth or Profit growth in percentage. Companies often track the percentage change in costs on a monthly/quarterly basis. Here is a simple example of using Percentage change:
Column D has the YoY percentage change value for Revenue, Cost, and Profit. It can be easily deduced that the revenue growth was healthy but costs grew at a staggering 86.1% and lead to a decline in profits. Here is how to calculate percentage changes in Excel: Revenue Percentage Change = (2016 Revenue – 2015 Revenue)/2015 Revenue In this example, the percentage formula in cell D2 is Again, note that this would give a value in General/Number format and not in the percentage format. To make it look like a percentage, you need to apply the percentage format (shown later in this tutorial). So let’s see how to apply the percentage formatting in Excel.
Applying Percentage Formatting in Excel
There are various ways you can apply the percentage formatting in Excel:
Method 1 – Using the Keyboard Shortcut
To apply the percentage format to a cell or a range of cells:
Select the cell(s). Use the keyboard shortcut – Control + Shift + % (hold the Control and Shift keys and then press the % key).
Note that this applies the percentage format with no decimal places. So if you use it on 0.22587, it will make it 23%.
Method 2 – Applying Percentage Format Using the Ribbon
To apply percentage format to a cell (or a range of cells):
Select the cell (or the range of cells). Go to Home –> Number –> Percent Style.
Note that this applies the percentage format with no decimal places. So if you use it on 0.22587, it will make it 23%.
If you want to have the percentage value to have decimals as well, you can use the increase decimal icon right next to the percentage icon in the ribbon.
Method 3 – Applying Percentage Format Using Format Cells Dialog Box
Using the Format Cells Dialog box gives a lot of flexibility to the user when it comes to formatting. Here is how to apply percentage format using it:
Select the cell (range of cells). Go to Home –> Number and click on the dialog launcher (a tilted arrow icon at the bottom right of the group). This will open the Format Cell dialog box. You can also use the keyboard shortcut – Control + 1 (hold the control key and press 1).
In the Format Cells dialog box, within the Number tab, select Percentage in the Category list. If you want to change the number of decimal places to be displayed, use the Decimal places option on the right. For example, if you want to display the value with 2 decimal places, make it 2. Click OK.
So these are the methods you can use to calculate percentages in Excel with a formula. I also covered some methods you can use to format the number to show it as a percentage. I hope you found this tutorial useful!
How to Calculate Weighted Average in Excel. How to Calculate Square Root in Excel How to Calculate CAGR in Excel. Calculating Standard Deviation in Excel. Excel Data Entry Tips Clean Data in Excel Spreadsheets How to Find Range in Excel Calculate Percentage Change in Excel (% Increase/Decrease Formula)
