Excel Percentages and Differences

Usually the formulas you need for percentages and differences are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore.

Excel Percentages and Differences

Excel Percentages and Differences

Percentages and Differences

Some of the most common calculations on Excel worksheets are Percentages and Differences. Percentages: Show one number as a percentage of another, or a percentage of the Grand total. We have to do these formulas constantly. Differences are just as common. Budget vs Actual, Plan vs Achieved, Current Year vs Previous Year etc. These formulas are the meat and drink of Excel worksheets.

Usually the formulas you need are quite straightforward: divisions for percentages and a minus sign to take one value from another. But there are pitfalls for the unwary which we shall explore. Read on…

Comparison

This is all about showing one number as a percentage of another number. In the example, we need to show Costs as a percentage of Revenue. In your formula, divide the smaller number by the larger number and then copy it down the column.

In the example, the formula’s really easy, =B2/C2

Costs v Revenue
Show Costs as a percentage of Revenue

The formula is correct but the results are shown as decimals, Excel cells typically have a General number format. We need to show those results in column D as percentages so that they are easier for our audience to understand. To format the percentage cells either use the Percent Style control on the Home tab or right-click and choose Format Cells, then apply the Percentage category in the Number tab.

Most people find percentages much easier to comprehend than decimals.

Percent format
Percentage format

Percentage formats multiply the cell value by 100 and displays the result with a percent symbol.

Formatted results
The finished formulas

As a Percentage of the Total

This is a very popular calculation, showing a number as a percentage of the whole. Again, it works on the principle that people can interpret percentages far easier than they can raw numbers.

Formulas going wrong
Errors in our formulas

We’ve divided the Revenue for the first item by the grand total and it’s worked fine for the first calculation. But the formulas go haywire when copied down.

It’s obvious what the problem is when we show our formulas. The divisor, B10—the Grand Total, needs to be constant in our formulas. Time for dollar signs!

Normal cell references like B10 are relative. This means they change when they are copied down or across. To fix a cell reference in a formula, you need an absolute reference, like this $B$10.

Show Formulas
Show the Formulas in the cells
Dollar Signs
Fix the reference to the Grand Total

You don’t have to show the formulas in the cells but it helps you see what you’re doing. Click the Show Formulas control on the Formulas tab.

To enter the dollar signs, either type them in or click the B10 reference in your formula and then press the F4 key. (Try Command+T if you’re on a Mac)

When you’ve fixed the cell reference in the first formula you can copy it down. Click Show Formulas again and then apply the percentage number format to your results.

finished-formulas
The final results!

Percentage Change, the Power of One

Usually, it’s considered bad practice to include constants (fixed numbers) in your formulas but there are a few exceptions when we’re using that magic number, Number One! In this first calculation we need to show the percentage change between two numbers, namely the percentage increase or decrease between Actual and Budget.

Percentage Change
Percentage change, the long way round

Here’s the simple and obvious method. Calculate the difference between Actual and Budget (B2C2 in the formula) and then divide the result by the Budget (C2 in the formula) Of course, we need to have brackets around the minus calculation as otherwise the division would be calculated first.

The formula works but it is too complicated as we have two references to C2 and that can’t be right.

And, indeed, it isn’t necessary to refer to the same cell twice but we’ll need a bit of magic mathematics to simplify the formula and force out the duplicated reference.

The original formula was =(B2C2)/C2, which we can reduce to: =B2/C2-1

Can you see what I did there? If you can’t follow it, have a look at the algebra of the formula in the following section.

The power of One
Percentage change, short version!
original formula

Here’s the algebra of the original formula. Actual minus Budget to calculate the difference. And then all divided by the Budget to calculate the percentage.

reduce formula

Firstly expand the formula: Actual divided by Budget minus Budget divided by Budget. This gives exactly the same result as the original.

simplify formula

And finally, reduce the formula. A number divided by itself is always one. So, we replace Budget divided by Budget with one. Much simpler and far more efficient.

Percentage Increase, the Power of One

Here’s another calculation where it’s all too easy to create a formula which is easy to understand but too complicated. In the example we have Prices in column B, a Percentage increase or decrease in column C and we need to calculate the new price in column D.

Percentage increase formula
Original New price formula-too long!

Here’s my first attempt at the formula, it has the original price (B2 in the formula) plus the amount of the increase or decrease (B2*C2 in the formula):

=B2+B2*C2

Easy to understand but inefficient as there are two references to B2.

Once again, the formula needs to be simplified, it now becomes the original price (B2 in the formula) multiplied by one plus the amount of the increase or decrease:

=B2*(1+C2)

See the reduction algebra below.

Percent increase simple version
New price formula simplified
Percent increase formula

The first attempt was the original price plus the amount of the percentage increase to calculate the new price.

Percentage increase simple

To restate a value you just multiply it by one. Only, in this case, you increase the multiplier by the amount of the percentage increase. Dead easy, but just not obvious.

That’s all for now. I hope I managed to give you a few ideas on calculating Excel percentages and differences. Best of luck with all your Excel formulas!

Excel course

To get the best out of Excel formulas you’ll need to have a good, working knowledge of Excel functions. Come along on our dedicated Excel functions course and discover what Excel formulas can do for you.

Call 020 7920 9500 now for details.

Related Posts

Excel FILTER Function

I think the Excel FILTER function does the filter job better than AutoFilter. It’s a live formula and an extraction, you don’t have to filter your data in place. There’s no need for that clunky Advanced Filter…

Excel FILTER Function

Excel Filter function

Excel FILTER Function

I really like Excel’s AutoFilter but I think the Excel FILTER function does the job better because it’s a live formula. And it’s an extraction, you don’t have to filter your data in place. This means it’s easy to have your results wherever you want them without having to bother with the Advanced Filter.

It’s a fairly new function so, as usual, the first thing is to check out whether you have it or not. Just click on an Excel cell, type-in “=FI” and see if you can spot FILTER in the pop-up list.

Here’s the data I want to filter, it’s a simple Excel list with 6 columns, A to F, with headings entered into the top row.

Dataset for FILTER

Basic Excel Filter Formula

And here’s the basic filter formula. I want to filter the list to just show the rows where I have “Agency” in column C.

=FILTER(A:F,C:C=”Agency”)

Basic filter

Sorting the Results

You can sort the results, if you want to, by wrapping the SORT function around the FILTER function, like this:

=SORT(FILTER(A:F,C:C=”Agency”))

Basic Sort

And that sorts the results in alpha order on the first column. But you can change this by entering extra information into the sort. In this example, I’m sorting the results by the 6th column (that’s my last column) with the highest number listed first. So, that’s minus 1 for descending order. Here’s the formula:

=SORT(FILTER(A:F,C:C=”Agency”),6,-1)

Sort by the last column

Filtering the Filter

Here’s the original filter formula again. You’ll see that the formula gives you all the columns from your data. You don’t always want that. To control the results you need to filter your filter.

=FILTER(A:F,C:C=”Agency”)

Basic filter

To do this put your original filter inside another filter and use an array of values to declare which columns to display in the results. The array is a set of zeros or ones inside a pair of braces (squiggly brackets). A one means show the column, a zero hides the column. Here’s my formula; out of the 6 columns, I’m showing only the second, third and sixth columns:

=FILTER(FILTER(A:F,C:C=”Agency”),{0,1,1,0,0,1})

Filter the Filter

More Complex Excel Filter Formulas

You can create complex filters by multiplying different expressions in your filter. Don’t worry about the internal logic, just copy and paste an example and adapt it to your own needs. I always do!

In this example, include everything where you have “Agency” in column C and the value in column E is 1000 or more:

=FILTER(A:F,(C:C=”Agency”)*(E:E>=1000))

Agency and Quantity filters

In this one, include everything where the value in column E is between 250 and 1000:

=FILTER(A:F,(E:E>=250)*(E:E<=1000))

Between 250 and 1000 filter

In this one, include everything where the value in column E is between 250 and 1000, but not where the value in column C is “Agency”:

=FILTER(A:F,(E:E>=250)*(E:E<=1000)*NOT(C:C=”Agency”))

Not include filter

Multiplication gives you a logical AND condition. If you want a logical OR, then use the plus sign. For example, this formula means include everything where the value in column C is either “Agency” or “In Store”:

=FILTER(A:F,(C:C=”Agency”)+(C:C=”In Store”))

Excel FILTER OR Condition

That’s all for now, best of luck with your filters!

Excel course

The FILTER function is just one of the new type of Excel functions, Dynamic Array Functions that were released with Excel 365. To learn more about these revolutionary functions, come along on our dedicated Excel Dynamic Array Functions Course.

Call 020 7920 9500 now for details.

Related Posts

Microsoft Power Platform Fundamentals

Audience Profile

Candidates for this Microsoft Power Platform Fundamentals exam aspire to improve productivity by understanding the capabilities of the Microsoft Power Platform, automating basic business processes with Power Automate, performing basic data analysis with Power BI, acting more effectively by creating simple Power Apps experiences, and creating powerful chatbots by using Power Virtual Agents.

Microsoft Power Platform Fundamentals

Microsoft 365 Fundamentals

Audience Profile

This Microsoft 365 Fundamentals exam is designed for candidates looking to demonstrate foundational-level knowledge of Software as a Service (SaaS) solutions to facilitate productivity on-site, at home, or a combination of both. Candidates may have knowledge of cloud-based solutions or may be new to Microsoft 365. Candidates can use this exam to prepare for other Microsoft certifications, but it is not a prerequisite for any of the related certifications.

Microsoft 365 Fundamentals

Microsoft Dynamics 365 Fundamentals ERP

Audience Profile

This Microsoft Dynamics 365 Fundamentals ERP exam covers the features and capabilities of Dynamics 365 finance and operations apps.

Candidates of this exam should have general technical knowledge. They should also have a fundamental understanding of financial principles and business operations.

Microsoft Dynamics 365 Fundamentals ERP

Microsoft Dynamics 365 Fundamentals CRM

Audience Profile

This Microsoft Dynamics 365 Fundamentals CRM exam covers the customer engagement features and capabilities of Dynamics 365 apps. Candidates of this exam should have a fundamental understanding of customer engagement principles, business operations, and general computer skills.

Microsoft Dynamics 365 Fundamentals CRM

Microsoft Azure Data Fundamentals

Audience Profile

  • Candidates for this Microsoft Azure Data Fundamentals exam should have foundational knowledge of core data concepts and how they are implemented using Microsoft Azure data services.
  • This exam is intended for candidates beginning to work with data in the cloud.
  • Candidates should be familiar with the concepts of relational and non-relational data, and different types of data workloads such as transactional or analytical.
  • Azure Data Fundamentals can be used to prepare for other Azure role-based certifications like Azure Database Administrator Associate or Azure Data Engineer Associate, but it is not a prerequisite for any of them.
Microsoft Azure Data Fundamentals

Microsoft Azure AI Fundamentals

Audience Profile

Candidates for this exam should have foundational knowledge of machine learning (ML) and artificial intelligence (AI) concepts and related Microsoft Azure services.

This exam is an opportunity to demonstrate knowledge of common ML and AI workloads and how to implement them on Azure.

This exam is intended for candidates with both technical and non-technical backgrounds. Data science and software engineering experience are not required; however, some general programming knowledge or experience would be beneficial.

Azure AI Fundamentals can be used to prepare for other Azure role-based certifications like Azure Data Scientist Associate or Azure AI Engineer Associate, but it is not a prerequisite for any of them.

Microsoft Azure AI Fundamentals

Microsoft Azure Fundamentals

EXAM DESIGN Audience Profile

Candidates for this Microsoft Azure Fundamentals exam are technology professionals who want to demonstrate foundational knowledge of cloud concepts and Microsoft Azure.

Candidates can describe Microsoft Azure Fundamentals architectural components and Azure services such as compute, networking, and storage. Candidates can also describe features and tools to secure, govern, and administer Azure.

Candidates for this Microsoft Azure Fundamentals exam have skills and experience working with an information technology area, such as infrastructure management, database management, or software development.

Microsoft Azure Fundamentals