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.

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:

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

Here is a 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 percentage 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.

Also read: How to Square a Number 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 shortcutControl + 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.

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!

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

Leave a Comment

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster