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

Excel UNIQUE Function

You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy. See how to sort the results automatically when the data changes…

Excel UNIQUE Function

Excel UNIQUE function

List Unique Entries

You can use the Excel UNIQUE function to extract a list of unique or distinct items from a range of Excel cell values. It’s really easy.

Entering the UNIQUE function

Just type “=u” into a cell and you’ll see a list of Excel functions pop up. Double-click UNIQUE in the list to start your formula. Enter the cell range that contains your values. Your formula should look something like this:

=UNIQUE(B4:B16)

Press ENTER to see the results.

Make sure that you leave enough empty space under the formula to show the results. Otherwise you might get an error.

As the extraction is done with a formula that means it’s dynamic. You only have to do it once. If you ever change the entries in the original list then the extraction of your unique values automatically updates to reflect the changes.

Results of UNIQUE formula

Sorting the Results

Using the SORT function

Oh, you wanted the results in alphabetical order did you?

The UNIQUE function can’t do that, but the SORT function can. All you have to do is wrap one function inside the other.

Double-click your formula so that you can do the required edit. Type SORT and an open bracket at the start then a closed bracket at the end.

Your formula should now look something like this:

=SORT(UNIQUE(B4:B16))

Press ENTER and your formula now returns the entries in alphabetical order. A-Z order is the default for the SORT function.

Results in A-Z order

Unique or Distinct?

Some people get very angry when you say things like “almost unique” or “very unique” because unique, properly, means “one of a kind”. It’s an absolute idea. The Excel UNIQUE function returns a list of everything in the list but excluding any repetitions. It should really be called DISTINCT, but they’ve gone for a more popular definition of unique.

Optional arguments for UNIQUE

You can change this if you want to.

The function has a few optional arguments. You need the third one:

TRUE- Return items that appear exactly once

FALSE- Return every distinct item

Change your formula so that it reads as follows:

=UNIQUE(B4:B16,,TRUE)

Two commas means you’re giving the third argument. TRUE to return items that appear exactly once.

And here’s the result. There’s only one item. Corunna only appears once in the list. It’s one of a kind.

In fact, it’s unique!

One of a kind
Excel course

The UNIQUE 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

Excel Roman Numerals

You can use Excel’s ROMAN function in a formula whenever you need to express normal, Arabic numbers as Roman numerals. And you can use the ARABIC function to convert them back the other way…

Excel Roman Numerals

Excel Roman Numerals

Excel Roman Numerals

You can use Excel’s ROMAN function in a formula whenever you need to express normal, Arabic numbers as Roman numerals. 

Excel ROMAN function

Just type “=r” into a cell and you will see a list of Excel functions pop up, then double-click ROMAN in the list to start your formula. If you like using the keyboard then type-in “=ro” and press the TAB key.

Enter the reference of the cell containing the number you want converted, in this case it’s B3. Then press ENTER and you’re done. Excel’s ROMAN function converts positive numbers up to a maximum of 3999. The optional Form argument gives you a choice of up to four degrees of simplification of the classic Roman form. For example, the value of 2500 can not be simplified and is expressed as roman MMD. However, 2499 is MMCDXCIX in the classic form but MMID in the simplified form. 

Enter your formula as =ROMAN(B3,FALSE) for the simplified form or use a number from 1 to 3 for the less simplified forms, i.e. =ROMAN(B3,2) gives you MMXDIX. Just enter the formula as =ROMAN(B3) if it can’t be simplified.

I’m glad we don’t have to use roman numerals in our Excel worksheets, it’s no wonder that Maximus was always in such a bad mood.

My name is Maximus Decimus Meridius. Father to a murdered son, husband to a murdered wife. And I will have my vengeance, in this life or the next.

Maximus

Excel’s ARABIC function

Excel ARABIC function

If you’ve already got Roman numerals and you want to convert them to normal, Arabic numbers, then use the ARABIC function.

Excel course

To learn more about the world of Excel functions, come along on our dedicated Excel Functions Course.

Call 020 7920 9500 now for details.

Related Posts

Lorem Ipsum for Word and PowerPoint

Generating random text or Lorem Ipsum for Word and PowerPoint. Do you ever need filler or placeholder text for Word documents or PowerPoint text boxes? It’s quite handy when you want to plan the design and layout of your document…

Lorem Ipsum for Word and PowerPoint

Random text lorem ipsum

Generating Random Text for Word and PowerPoint

Generating random text or Lorem Ipsum for Word and PowerPoint. Do you ever need filler or placeholder text for Word documents or PowerPoint text boxes? It’s quite handy when you want to plan the design and layout of your document and check out different fonts and formatting options etc. Being an expert typist, I always used to type in double-encrypted blah, blah nonsense like this, “jkhj kefhkfh kjhjkhf hfshf jhkjhs” until I discovered random text functions. However, my typing speed has suffered, I was quite impressive typing “jkhj kefhkfh kjhjkhf” but now I’m back in the land of hunt and peck.

Random Text

shortcut for random text

To generate some random text, click on your document or on a PowerPoint text box, type in =RAND( ) and then press the ENTER key. Don’t forget the equals sign at the start and both the brackets at the end. You’ll get some paragraphs of random text. To control the number of paragraphs, enter a number inside the brackets. So, =RAND(5) gives you five paragraphs.

Lorem Ipsum

shortcut for lorem ipsum

If you have a classical bent then you may prefer some Latin, in which case type in =LOREM( ) and you get paragraphs of lorem ipsum.

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Lovely!

PowerPoint Course

To see how to create great presentations, come along on our PowerPoint Introduction Course.

Call 020 7920 9500 for full details.

Related Posts

Cluttered PowerPoint Slides

Do you ever have to deal with those cluttered PowerPoint slides? You know; the ones crowded with layers of overlapping shapes and text boxes—and you need to change the one in the middle of the pile…

Cluttered PowerPoint Slides

Cluttered PowerPoint Slides

Cluttered PowerPoint Slides

Do you ever have to deal with those cluttered PowerPoint slides? You know; the ones crowded with layers of overlapping shapes and text boxes—and you need to change the one in the middle of the pile! You can’t see what you’re doing on these busy slides. Some people move all the other objects out of the way so that they can see the one they want. Then they make a few changes and then move them all back again in to, hopefully, their original positions.

There has to be a better way. You can use the keyboard shortcuts Tab and Shift+Tab to cycle forwards and backwards through the stack of objects until you can see that the selection handles are around the one you want. Or, you can press ALT+F10 and turn on the…

Selection Pane

ALT F10 Selection Pane

There’s a control to turn this on and off on the Home tab but it’s easily missed. The Selection Pane lists all the objects on your slide and you use it to identify and work on any object. Just click the Hide All button and then start clicking the eyes to the right to show selected objects, you’ll soon find the one you want. Make your changes, click the Show All button and you’re done. It’s so easy when you can see what you’re doing.

PowerPoint Selection Pane

PowerPoint uses its own fiendish naming scheme for all your objects. Name like Rectangle 5, TextBox 4 etc make perfect sense until you have to animate them and then you have real problems trying to identify specific objects. All you have to do is rename them according to their appearance or purpose and you’ll have a much easier time creating animations.

An object named something like “Black Heart” or “Red Star” is a lot easier to deal with than “Heart 4”. To rename an object, click the current name in the pane and replace the text.

There’s nothing wrong with having cluttered PowerPoint slides—you just need to know how to work with them easily.

PowerPoint Course

To see how to create great presentations, come along on our PowerPoint Introduction Course.

Call 020 7920 9500 for full details.

Related Posts

Resizing Keynote Text Boxes

I must say that I quite like Keynote, and prefer it to PowerPoint. But if there’s one thing that gets my goat, it’s those pesky text boxes! You can’t seem to resize them properly. What you need is an 8-handled text box…

Resizing Keynote Text Boxes

Resizing Keynote Text Boxes

Keynote Text Boxes

Mouse Tips are all about the little things in life that make all the difference. Today we’re looking at resizing Keynote text boxes. I must say that I quite like Keynote, and prefer it to PowerPoint. But if there’s one thing that gets my goat, it’s those pesky text boxes!

Every time you insert a text box or copy and paste some text onto a Keynote slide you get a 2-handled text box which adjusts itself automatically to fit the text that it contains. Fine! But you can’t resize it. Only by filling it with placeholder text. And I can’t really be bothered to do that.

This is frustrating because you often need to change the dimensions of a text box. So that you can visualise how it relates to the other slide elements. What you need is an 8-handled text box.

Changing a 2-handled Text Box to an 8-handled Text Box

Keynote 2 handled text box

Here’s my 2-handled text box and it’s driving me mad! Some of the slide layouts have sizeable 8-handled text boxes. Why can’t I have one too? Now, you can copy and paste the actual text box off one of these slides. But then you’ll have to copy your text into it…

Don’t despair. It’s not obvious but it’s not difficult. You need to change your 2-handled text box into an 8-handled one. Keep the text box selected and go into the main application menu bar. You need Format, Shapes and Lines, then Reset Text and Object Handles. Now you have an 8-handled text box.

Keynote reset text
Keynote 8 handled text box

Easy-peasy isn’t it? I have to confess that I hardly ever use the Keynote main menu. Like most people, I just use the shortcuts and get most things done by right-clicking.

The Keynote 8-handled text box is a magnificent thing. You can size it anyhow you like. And there’s far more formatting options compared to the other one. You can have drop-caps, text in columns, all sorts! Happiness is an 8-handled text box.

Keynote happy text box
Keynote Presentations

If you want to get the best out of Keynote and stop doing everything the long way round, come along on our Keynote training course.

Call 020 7920 9500 for full details.

Related Posts

Non-Breaking Spaces

Text in browsers and documents usually wraps onto the next line at the first available space or hyphen. Which is fine most of the time, but not always. What if I have text where I need to keep the words together…

Non-Breaking Spaces

Non Breaking Spaces

Non-Breaking Space

This is the first in our occasional series of Mouse Tips. These are the little things in life that make all the difference. Today the topic is non-breaking spaces. Hold your breath!

Text in browsers, documents and emails usually wraps onto the next line at the first available space or hyphen. Which is fine by me. Most of the time, but not always. What do I do if I have text where I need to keep the words together? Like addresses or proper names. Here’s my address: 27 High St, EC1.

I want all of those words to stay together. I don’t want the text to break onto a new line after the “27”. And then have the “High St, EC1” on the next line. Text will always wrap at the first space. Personal names are another bugbear. The name, “Mrs Edna Bloggs” should all read as consecutive words, otherwise it looks silly. You can’t have “Mrs” at the end of one line, and then “Edna Bloggs” at the start of the next line. Numbers and units are another example where the text needs to stay together. 24 mpg should always read as “24 mpg” otherwise it has no meaning.

You often end up re-typing the text so that it reads properly. No, this is where you need a non breaking space.

Keyboard shortcut

A normal space is the spacebar. The non breaking space is CTRL+SHIFT+Spacebar. Hold down CTRL and SHIFT together and then press the spacebar. It’s a bit of a pain BUT that text stays together. You’ll have to do this for every space in an address.

If you’re on a Mac, try OPTION+Spacebar.

word-document

If you want to see other typographical tricks, come along on our Word Introduction Course.

Related Posts