How to Calculate and Format Percentages in Excel

Often, there are two types of percentages that one needs to calculate in Excel.

  • 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.

Calculate and Format Percentages in Excel

In this tutorial, you’ll learn:

  • How to Calculate Percentage as a Proportion in Excel.
  • How to Calculate Percentage Change in Excel.
  • How to Format Cells with Percentages in Excel.

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 the 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:

Calculate Percentages in Excel - Sales Coverage

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 formula to calculate the percentage in Excel:

=Effectively Reached/Total Prospective Customers

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 percentage change in costs on a monthly/quarterly basis.

Here is simple example of using Percentage change:

Calculate Percentages in Excel - Growth percentage

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 formula in cell D2 is (C2-B2)/B2

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:

  1. Using the Keyboard Shortcut.
  2. Using the Ribbon.
  3. Using the Number Format Cells Dialog Box.
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.

Calculate Percentages in Excel - Ribbon

Note that this applies the percentage format with no decimal places. So if you use it on 0.22587, it will make it 23%.

Calculate Excel Percentages in Excel - Using Ribbon Demo

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.

Calculate Excel Percentages in Excel - Increase Decimal

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).Calculate Excel Percentages in Excel - dialog launcher
  • In the Format Cells dialog box, within the Number tab, select Percentage in the Category list.Calculate Excel Percentages in Excel - Format Cell Percentage
  • 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.Calculate Excel Percentages in Excel - change decimal
  • Click OK.

You May Also Like the Following Excel Tutorials:

>