**How To Work Out Percentages in Excel **

## tax percentages, percentage change, percentage increase, percentage of total….

Whenever I’m delivering an introductory Excel course, one of the core topics I cover is percentages. It’s a basic skill that crops up every day in the business world and is crucial for any Excel user to master. However. the mere mention of the word percentage is often met with users shrinking back in their seats with school day memories of maths coming back to haunt them.

Luckily, you don’t have to fear because if you are using Excel you don’t have to be a mathematical expert and furthermore, with a bit of practise you’ll be churning out percentage formulas in a jiffy!

A technique to make this process even easier is to use **named ranges** to reference changing cells. Those of you who are familiar with absolute references in formulas will find this useful because it means you don’t have to press F4 to make a cell absolute or type in the $$. So here goes…. the first one I get asked about constantly is how to work out the percentage of a value.

## 1. How to Calculate the Percentage of a Value e.g Tax Percentages

A great example of this which most people can relate to of course is GST. How do we calculate the GST on a value?

## Name the Changing Cells to Make Referencing Easy

First we are going to name the cell containing the GST rate . To do this click on the cell (B10 in the example below) and then click into the name box shown in the red circle below. The name box displays the active cell reference. Over type this with a name like GST (spaces are not allowed) and then press Enter.

## Create the Formula to Calculate 10% GST

- Click into cell C2 and type =
- Click on cell B2
- Type * GST
- Press Enter
- Drag the bottom right corner of cell C2 (where the small green square is) down to cell C4 to copy the formula down the column
- Click in cell D2 to calculate the line total
- Type =
- Click cell B2
- Type +
- Click cell C2
- Press Enter
- Drag the bottom right corner of cell D2 (where the small green square is) down to cell D4 to copy the formula down the column

In Excel the finished sheet would look something like this:-

## 2. Calculate the Percentage of a Total

In this example I have a list of monthly expenses and a total at the bottom of my list. I want to see each of the individual item prices expressed as a percentage of the overall total.

- I set up the spreadsheet and use AutoSum to total the costs in column B
- Next up I want to name the Total cell so I click cell B7
- Click in the Name Box and type Total and press Enter
- Click cell C2
- Type =
- Click B2
- Type /
- Type Total
- Press Enter
- The formula =B2/Total divides the mortgage cost by the total
- Click C2 and drag the bottom right corner of the cell to copy the formula down to cell C6
- The last step is to display the answers as percentages
- Ensure cells C2 to C6 are selected and click the % icon on the Home tab on the ribbon to display % shown above

## 3. Calculate Percentage Change Between Sales this Quarter and Last Quarter

If you need to calculate the percentage change between two values in Excel, you can use a formula that divides the change itself by the “old” value. A common example would be to calculate the percentage change between sales last quarter and sales this quarter.

How the formula works

*(new value – old value) / new value*

In the example, the active cell contains this formula:

=(C2-B2)/C2

- Set up the sheet and click into cell D2 to calculate the first % change
- Type =
- Type (
- Click C2
- Type –
- Click C2
- Type )
- Type /
- Click C2
- Press Enter
- Drag the bottom right corner of Cell D2 down to cell D7 to copy the formula
- The last step is to display the answers as percentages
- Ensure cells D2 to D7 are selected and click the % icon on the Home tab on the ribbon to display %

## 4. Increase Existing Prices by 15% Without Using a Formula

A question I get asked a lot is how to change an existing price list without having to insert a formula column, just simply change what’s already there. Here’s an easy way to do it using Copy and Paste Special.

- Using my previous example, I now want to add 15% to the values in column B.
- First of all I type 1.15 into cell E2, this will add 15% on, to add 20% on you would need to type 1.2 and so on
- Click cell E2 and click Copy or press Ctrl C
- Select the values you want to increase by 15% in column B
- Click the drop down button beneath the Paste button just underneath the File menu tab
- Choose Paste Special
- Choose Multiply
- NOTE: you may have to reformat the cells to currency as pasting also copies formats by default
- Delete the contents of cell E2

## Reverse Calculate Percentages

Reverse calculating can be a useful thing to do in Excel. For example, consider the spreadsheet below, all of the items were marked down by 20% for a huge sale but I need to work out what the original price was before it was marked down. We can calculate this in Excel by dividing the sale price by 1 minus the discount rate. See the formula in the top line of the screenshot below.

- Name cell B10 as discount
- Click in cell C2
- Type =
- Click on cell B2
- Type /(1-discount)
- Press Enter

**Author**: Anita Lund owner of Trainers Direct, Microsoft Certified Instructor teaching Excel courses in Sydney

## Recent Comments