Search

# 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!

This Tutorial Covers:

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

`=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:

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.

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!

You May Also Like the Following Excel Tutorials:

FREE EXCEL BOOK

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

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.

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 E-BOOK

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