Excel Insights E-Book

• Herausgeber: Packt Publishing
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 410

Veröffentlichungsjahr: 2024

Beschreibung

Dive into the world of advanced Excel techniques designed to elevate your data analysis skills. Start with mastering custom number formatting, efficient data entry, and powerful formulas like INDEX MATCH. Explore Excel's evolving features, including dynamic arrays and new data types, ensuring you stay at the forefront of the latest tools.
The course then guides you through creating impactful charts for presentations and advanced filtering techniques. You’ll also discover the transformative power of Power Query, allowing you to manipulate and combine data with ease. With chapters on financial modeling and creative Excel model development, you’ll learn to solve complex problems and develop innovative solutions.
Finally, the course introduces you to VBA, teaching you how to automate tasks and create custom worksheet functions, equipping you with the skills to enhance your workflows. By the end of the course, you’ll have a robust understanding of Excel's advanced features, empowering you to handle any data challenge with confidence and creativity.

Leseprobe

Excel Insights

© 2020 by Jon Acampora, Liam Bastick, Leila Gharani, Michael Girvin, Roger Govier, Frederic le Guen, Mathieu Guindon, Tim Heng, Wyn Hopkins, Ian Huitson, Bill Jelen, Tony de Jonker, Gašper Kamenšek, John MacDougall, Dave Paradi, Jon Peltier, Jan Karel Pieterse, Ken Puls, Oz du Soleil, Hervé Thiriez, Mynda Treacy, Henk Vlootman, and Charles Williams.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without permission from the publisher.

All terms in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books.

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

Printed in USA by Hess Print Solutions.

First Printing: January 2020.

Authors: Jon Acampora, Liam Bastick, Leila Gharani, Michael Girvin, Roger Govier, Frederic le Guen, Mathieu Guindon, Tim Heng, Wyn Hopkins, Ian Huitson, Bill Jelen, Tony de Jonker, Gašper Kamenšek, John MacDougall, Dave Paradi, Jon Peltier, Jan Karel Pieterse, Ken Puls, Oz du Soleil, Hervé Thiriez, Mynda Treacy, Henk Vlootman, and Charles Williams.

Lead Editors: Liam Bastick and Ingeborg Hawighorst

Technical Editors: Liam Bastick, Tim Heng, Bill Jelen, Jan Karel Pieterse and Charles Williams.

Indexer: Nellie Jay.

Compositor: Jill Cabot.

Cover Design: Shannon Travise.

Screen Reader Captions: Jon Acampora, Liam Bastick, Leila Gharani, Roger Govier, Frederic le Guen, Mathieu Guindon, Wyn Hopkins, Ian Huitson, Bill Jelen, Gašper Kamenšek, John MacDougall, Dave Paradi, Jon Peltier, Jan Karel Pieterse, Ken Puls, Oz du Soleil, Mynda Treacy, Henk Vlootman, and Charles Williams.

Published by: Holy Macro! Books, PO Box 541731, Merritt Island FL 32954.

Distributed by Independent Publishers Group, Chicago, IL.

ISBN 978-1-61547-067-9 Print, 978-1-61547-153-9 Digital.

Library of Congress Control Number: 2019952976.

Excel examples used in this book can be downloaded from: https://mrx.cl/mvpfiles.

Table of Contents

Smart Uses of Custom Number Formatting

Ctrl + Enter

Auto-Magically Master INDEX MATCH (and Other Formulas)

Relative Named Ranges – When Named Ranges Go Walkabout

An Introduction to Excel’s New Data Types

A Look to the Future – Dynamic Arrays

XLOOKUP Debuts in Excel

Why the Love / Hate for Pie Charts?

Intermediate Charting in Excel

Creating Charts for Presentations

Advanced Filter

Power Query: Manipulate Your Data Like a Pro

Combine All Files in a Folder

Power Query M Code Approximate Match Lookup Formula

The Power Behind the Boringest Sentence in Excel

Understanding Context in Power Pivot

Thinking Through the Modelling of a Seating Chart

Financial Modelling

Creative Excel Model Development

An Introduction to Simulation in Excel

Staying out of Trouble

Make Your Own VBA Worksheet Functions

An Overview of Modern VBA Best Practices

About the MVPs

Index

Smart Uses of Custom Number Formatting

by Leila Gharani

Number formatting is ideal for manipulating data in presentations without actually “touching” the source data. Most people normally use it to show numbers with a thousand separator or as percentage, or even to show green for positive and red for negative values, but they hardly go beyond that.

Common Number formatting options.

The image below shows how you can extend the usual number formatting options to create impressive reports. You can even use dynamic thresholds to control the formatting.

Custom number formatting to emphasize deviations.

I will take you through the necessary steps to create reports like these. It’s easy. The catch is, you first need to understand the rule behind custom number formatting. Once you do, it’ll open up your eyes to all the opportunities to improve the presentation of your reports.

Rule Behind Custom Number Formatting

To access custom number formatting, right-click on any cell, click ‘Format Cells…’or use the shortcut key Ctrl + 1. In the window that appears, in the Number tab, go to Custom.

The Custom option allows you to create your own customized formats. You can choose how to display positive and negative numbers, as well as how you’d like zero values and text entries to be formatted.

Proper use of custom number formatting requires you to follow a special syntax which is comprised of four parts (when all four parts are specified):

Positive number; Negative number; Zero; Text

Each segment is separated by the semicolon (;) symbol, known as a delimiter.

Excel doesn’t require all four parts though. If your formatting includes only two parts, Excel assumes the first part of the formatting to be for positive values and zeros while the second part will be used to format negative values. If you specify only one format segment, i.e. the semi-colon is entirely left out from the syntax, then Excel ignores positive and negative but applies the same formatting to all four parts.

In the ‘Custom Format Cells’ dialog box, you have over 30 different number formats available:

Custom number formatting options that come with every workbook.

If you scroll through these, you come across symbols like #, 0, ?, [Red], etc. The below list explains the uses of the most common symbols:

0(zero) is a fixed placeholder for values. It is commonly used to define the number of decimal places.#(pound / hash) is a digital placeholder as in 0. The difference is # doesn’t force trailing zero values. For example, if you have ## as a custom number format and the value in the cell is 2, you will see a 2. Whereas, if you have 00 as the custom number format, you will see 02 shown in the cell instead. A common format for the thousand separator is #,##0. This means commas are displayed for every three (3) digits., (comma)displays the thousand separator. Note that this can be different depending upon your regional settings. For example, if your regional settings are German, you’ll need the “.” Instead of the “,”.. (period)displays decimal digits. As with the thousand separator, this is dependent upon your regional settings. Again, for the German setting, you’ll need the “,” instead of “.” for decimal places._ (underscore) adds a space the size of whatever the next character is, so “_)” would make a space the same size as a closing bracket. This is typically used so the value input is not stuck to the cell border. This aligns positive numbers correctly below negative values in parentheses.* (asterisk) repeats the next character until the width of the cell is filled. For example, you’d like the currency symbol on the left-hand side of the cell, followed by blank space and then the number. The wider the column width, the wider the blank space in between the dollar symbol and the number.@ (‘at sign’) is a placeholder for text.[Red] changes the color of the values to red. Excel custom number formatting has a certain number of color codes available which can be used here. In English, the names recognized are [Black], [White], [Red], [Green], [Blue], [Yellow], [Magenta] and [Cyan]. For example, the standard [Green] is quite bright for reports. [Color 10] can be used for a darker green. They all need to be entered with square brackets. There are 57 colors available, [Color 0] (the one everyone always forgets about!) through [Color 56]. You can find the link to the complete list of color index and values in the downloadable workbook.

Hiding Zero Values in Reports

A practical use of custom number formatting is to hide zero values in reports. Follow these steps to add a custom number format:

Select the data range to be formattedRight-click / format cells or use shortcut Ctrl + 1Click on category ‘Custom’ and under ‘Type’ write the custom number format string: ‘#,##0;-#,##0;’Click OK

Custom number formatting to hide zero values.

Zero values are now shown as empty cells, because the third argument (after the second delimiter) is blank.

Remember custom number formatting is like applying make-up. It changes the visible appearance but not the actual content.

If you click on one of the newly formatted empty cells and look at the formula bar, you will see a zero value in there.

Zero values disappear (but they are still there).

Showing Values in Millions

Let’s assume your task is to show the following monthly sales data in millions. Instead of 30,000,000, you’d like to show it as 30 M:

Monthly sales data.

One option is to add an extra calculation step by dividing the data by 1,000,000 and adding an “M” to this with the following formula:

=C5/1000000 & " M"

Please don’t do this!

Showing the data in millions by adding text to the value is something you should avoid!

Changing formatting using this approach converts each cell value into a text cell because text added to a number converts the final value to a text string. Excel automatically left-aligns these because it doesn’t recognize them as numbers. Your values might “look” like millions, but the actual values in the cells are not numbers. They are text. This means you can’t perform any mathematical operations on them.

Formatting the data as millions is the more efficient and practical approach. To do this, highlight the raw data and go to custom number formatting. Apply this format:

0,," M";-0,," M";-

Custom number formatting to show data as millions followed by the “M” abbreviation.

“0,,” says to Excel, “display this number in millions”. One comma represents thousands and two commas denote millions. The second “M” adds text to the formatted number, but it remains a number. Notice the text is inside quotation marks and there is a space before it. This way the “M” doesn’t stick to the number.

The part after the first semi-colon is identical to the positive number format but displays negative values with the minus sign. The “-“ after the second semi-colon replaces zero values with a dash sign. Text formatting in this example has been ignored. Only positive, negative and zero values have been modified.

Without looking at the values in the formula bar, one would think the cell value to be “30 M”.

We see 30 M in the cell, but the actual value is “30000000”.

The actual value in the cell is “30000000”. You can continue to perform mathematical operations on this value, and it will behave accordingly.

Creating a chart on this data is also neater and easier to read than the one created on the original data set, which uses a standard number formatting with a thousand separator and zero decimal places.

Custom number formatting for the chart axis looks neater and better organized.

Showing Deviations as Symbols

Let’s take this a step further and disguise values as symbols. This way, you can show deviations with up and down arrows or any other symbol of your choice.

You might have done this in the past with conditional formatting. After going through this section, I think you will find the custom number formatting method much easier to apply. I personally prefer custom number formatting over conditional formatting whenever I can, for the following reasons:

I can choose any symbol (I’m not a big fan of the existing conditional formatting symbols, although it is possible to customize these too, admittedly)There is no duplicating of the formatting rules, which is a common issue with conditional formattingCustom number formatting is faster to implementCustom number formatting is not volatile (i.e. it recalculates every time you do anything in Excel) unlike conditional formatting. This makes calculations faster and spreadsheets more stableIt’s less resource intensive in general, as compared to conditional formatting when used on large data sets

As a first step, you need to select the symbols. Click on an empty cell. Go to the Insert tab on the ribbon and select Symbol. Select the symbol you’d like to use in your report and click on Insert. Choose more symbols if you like. This inserts them in the active cell. For this example, I will use the up and down arrows found under Arial in the subset ‘Geometric Shapes’.

Insert a symbol of your choice in an empty cell.

Remember: to use custom number formatting, you need numbers in the cells.

In the following example, we can calculate the change from Actual to ‘Previous Year’ (PY) as follows:

=C7–D7

Calculate the deviation by deducting Previous Year (PY) values from Actual values.

Now that you have numbers, you can disguise them as you’d like. Copy the arrow symbols you previously inserted. Highlight the values in the deviation column (column E), right- click, ‘Format Cells’, go toCustom and apply the following formatting:

▲;▼;

The values in the deviation column are shown as up and down arrows. Remember the first argument before the semi-colon is how positive numbers should be formatted. These are now shown as an up arrow. Negative numbers are formatted as a down arrow, and zero values are hidden.

Values disguised as symbols.

Now let’s add some color to the symbols. With the values selected, go back to custom number formatting (press Ctrl +1) and edit the arguments to:

[Green]▲;[Red]▼;

Press OK and the arrows now have colors. Personally, I find the standard green too bright. My preference is [Color 10] for a darker green or [Color 43] for a lighter version. The below image uses [Color 10] for the up arrows.

[Color 10] is used for the up arrows.

The custom number formatting box is language sensitive. If you’re using Excel in another language, translate “color” to your language. The index numbers are identical in all versions.

Up and Down Arrows with Percentage Deviation

What if you wanted to show the percentage value together with the symbol in the same cell? No problem. First calculate the percentages by (Actual – PY)/PY, or alternatively Actual/PY -1.

Highlight the values and go to custom number formatting. Type the following format:

[Color 10]0%▲;[Red] -0%▼;

Your result will show both the cell value formatted as a percentage, followed by the symbol.

Custom number formatting to show percentage values and symbols in the same cell.

If you’d like to align the symbols to the right-hand side of the cell and the values to the left-hand side, you can take advantage of the asterisk sign. The asterisk sign repeats the next character to fill up the cell. If the next character is a space, it will repeat the space. This ensures the breathing space inside the cell, is proportional to the width of the cell. Now let’s update the custom number formatting to be:

[Color 10] 0%* ▲;[Red] -0%* ▼;

Symbols are aligned to the right-hand side and numbers to the left-hand side.

This aligns the symbols under one another. I find the report easier to read this way.

Conditional Number Formatting Using the Custom Number Format Option

Up until now, I shared the major rule behind custom number formatting: first comes positive numbers, then negative values, then zero, then text.

What many people don’t know is that custom number formatting also allows for conditions. It has more limitations than Excel’s conditional formatting feature, but it can be utilized to create a high, low and other case scenario. To define conditions, you need to include them in square brackets [ ], similar to colors. The syntax then works as:

{format1}[Condition 1];{format2}[Condition 2];{format3}

This works like a nested IF formula: if the first condition holds, do the first number format; if the second condition holds (but not the first) do the second format; otherwise go with the third format.

Let’s take the example above, but this time add conditions based on absolute differences.

If the difference between Actual and PY is more than one hundred, I’d like to have a green up arrow. If the difference is less than zero, a red down arrow and for values between zero and one hundred, I’d like an orange rectangle.

Update the custom number formatting arguments to be:

[Color 10][>100]▲;[Red][<0]▼;[Color 45]▬

Left-align the values in the E column to get the symbols closer to the data.

Conditional formats based on fixed thresholds.

This generally works fine. However, if you define the second condition to be <-100 instead of <0, you will see a minus sign beside the symbol for the values between 0 and -100.

A better way around this is to use the technique shown below.

Dynamic Thresholds with Custom Number Formatting

This time, in addition to defining conditions for the threshold, I’d like to input the threshold value in a cell. Unfortunately, you don’t have the option to apply cell references in the Custom Number Format dialog box.

A way around this to take advantage of the first rule of custom number formatting. The fact that I can format positive, negative, zero and text values in different ways gives me four conditions I can control. These conditions are pre-defined based on the number and type (text or number) of the value entered in the cell. Let’s use this to our advantage.

This time, I’d like to add a 5% threshold. A green arrow should appear for the rows where the difference is above 5%. Any value that’s below -5%, should appear as a red arrow. Other values should be invisible.

I’ve placed the threshold value in a cell (cell E22 in the image below) and formatted it as percentage. I’ve also calculated the percentage difference between Actual and PY in column E. In column F, l would like to use symbols for the cells where the deviation is greater than the threshold.

I can use the IF function to assign three different types of flags to the cells. For the cases where the percentage change is greater than the threshold, I’d like a 1. If the percentage change is lower than the negative value of the threshold, I’d like a -1. If it lies in between, I’d like a zero (0). Therefore, I can use the following formula:

=IF(E24<-$E$22,-1,IF(E24>$E$22,1,0))

This way each cell has either a value of 1, -1 or 0.

Using the IF function to get three types of values (0,1 and -1).

Next step is to apply custom number formatting. This time I will use a square symbol (also available in Symbols, under Arial / Geometric Shapes).

Insert the symbol in an empty cell. Copy the symbol, then highlight the data in column F, go to custom number formatting and apply the following format:

[Color 43]■;[Color 53]■;

I picked Color 43 for a lighter green and Color 53 for a darker red. Zero values will be hidden.

Custom number formatting with dynamic thresholds.

Now if I change the threshold to 15%, my report automatically updates as shown below.

Symbols are allocated based on the threshold specified.

That’s how easy it is to “connect” custom number formatting to a cell value. You can of course expand on this. For example, instead of inputting the threshold manually, you could define it with a formula. You could calculate the average deviation and use this as a basis for the IF formulas.

With this method, you can also have a fourth condition.

Let’s assume my conditions are as follows:

>10%: dark green symbol5-10%: light green symbol2-5%: yellow symbol<2%: red symbol

Notice all the conditions are based on positive values. That’s okay, because I will use a formula to get the appropriate flags.

Setting up the flags for custom number formatting with four conditions.

The formula I’ve used for column F is:

=IF(E10>=$D$4,1,IF(AND(E10>=$D$5,E10<$E$5),-1,IF(AND(E10>=$D$6, E10<$E$6),0,"t")))

Value 1 is assigned for the first condition, -1 to the second, 0 to the third and “t” for the fourth condition. I will custom number format column F values as follows:

[Color 10]■;[Green]■;[Yellow]■;[Red]■

That’s it! My report is complete.

Custom number formatting with four conditions.

There are different functions you can use here depending on your knowledge and experience. For example, if you’re comfortable with VLOOKUP, you could use the following formula:

=VLOOKUP(E30,$D$24:$F$27,3)

The formula is shorter but if you use this method, you must organize your condition table correctly. The lower boundaries need to be sorted in ascending order otherwise the approximate match of VLOOKUP will return errors or wrong results.

Adjust the conditions depending on the data and your requirements.

VLOOKUP function as an alternative to the nested IF function

Once you have the flags in place, apply the number formatting specified above.

Get Creative with Custom Number Formatting

I hope the seven examples in this chapter have given you some ideas for your own reports. Custom number formatting can be a bit scary to work with if you don’t understand the syntax. Now that you do, there’s nothing stopping you. Take advantage of this simple but powerful feature to optimize your reports.

Ctrl + Enter

by Gašper Kamenšek

This chapter will introduce you to Ctrl + Enter and then through three real-life scenarios show the added value of using it. So, without further ado, let’s start with something really simple. We want to put today’s date in cell A1 and format it so that we can see the name of the day next to the date.

Here is a step by step progression:

select cell A1press Ctrl + semi-colon (;) to get today’s datepress Ctrl + Enterpress Ctrl + 1 to activate ‘Format Cells’ dialogchoose Custom format and type DDDD, MM/DD/YYYY

The key here is the third step. Did you notice how pressing Ctrl + Enter instead of pressing Enter or Tab has confirmed the cell entry, but in a way that still left A1 the active cell so that we could jump straight to formatting? Pretty impressing right? Let’s take it a step further.

Now we want to put today’s date into the first 5,000 cells of column A. The key takeaway in this example will be that we can use Ctrl + Enter to enter the same entry into multiple cells at once. Here are the necessary steps:

enter A1:A5000 into the Name Box and press Enter (do not click anywhere after this step!)

The Name Box.

use Ctrl + semicolon (;) to get today’s datepress Ctrl + Enter, and you should see today’s date in all 5,000 cells

Now we know that with Ctrl + Enter we can enter the same entry into multiple cells at once. For our next scenario, we are going to use Ctrl + Enter together with another Excel gem. This one is called ‘Go To Special’. For this scenario, we start with this sample data:

Sample data with blank cells.

As you can see, we have a few blank cells. All we want to do is to put a zero (0) into those blank cells. Here is a step-by-step breakdown:

select the entire table (Ctrl + A)press F5 (or Ctrl + G) and press ‘Special…’

The ‘Go To Special’ window.

choose Blanks and press OK

The ‘Go To Special’ dialog box.

now that you have only blank cells selected, type zero (0) and press Ctrl + Enter

‘Go To Special’ and Ctrl + Enter is one of the strongest combinations of two different Excel “tools” coming together, to empower you to do great things. Just think about using the ‘Visible cells only’ to add a certain comment to rows derived by filters…

This brings us to our final scenario. This one will show you that Ctrl + Enter is even more versatile than you thought. For this scenario, we will start with a file that has its cell formats pre-designed.

Example with pre-designed cell formats.

All we need to do is to calculate the desired prices in range D7:G11. If Ctrl + Enter did not exist this would be a venture full of pasting only formulas, copying without formats and other special ways excel offers for copying formulas. However, with Ctrl + Enter, it’s just three simple steps:

select the range D7:G11write the formula =$C7*B$3

Example having typed in the formula.

press Ctrl + Enter

The example completed.

Et voila, we have all the formulas copied, and not a single format has been overwritten.

Auto-Magically Master INDEX MATCH (and Other Formulas)

by Wyn Hopkins

INDEX MATCH isn’t easy. After you learn the magic technique in this chapter it’s simple!

What Does INDEX MATCH Do?

Imagine a company that supplies parts for various vehicles, and we have a price list like the one below.

INDEX MATCH is perfect for finding information in a table based on a reference.

The INDEX MATCH formula combination allows you to look up a product code and return a corresponding value from any other column. I’ll explain how this works shortly. The result is time saved from manually searching / filtering and greater accuracy as searching manually can lead to mistakes.

If we had 10 different products, we could just copy the formula 10 times:

The formula =INDEX(PartsList!D:D, MATCH(D2, PartsList!C:C,0)) is not easy to write.

While the result is great, the formula itself is very unfriendly to write. Firstly, it’s not one but two functions INDEX and MATCH. Secondly, you have to remember the order to use them which isn’t easy for occasional users. This is often why VLOOKUP is a preferred alternative to INDEX MATCH, but we’ll address that later in the chapter.

A Bit of Background - How Does INDEX MATCH Work?

Before we jump into the nice and simple solution, let’s understand how INDEX MATCH works. If you don’t care, then just skip this section; it’s OK, I won’t tell.

We need to break INDEX MATCH down into its 2 parts. The MATCH part is used to find which row contains a matching item

=MATCH("C4-562",C:C,0)

MATCH example.

For example, looking at the screenshot above, if we choose to find a MATCH for C4-562 we will get the answer five (5) as this is the fifth row of the selected range.

Note: the final argument of zero (0) is required to look for an exact match.

INDEX is then used to return the nth item from a column:

=INDEX(D:D,5)

Identifying the product code is in the fifth row.

This formula would result in the word “Bumper” because it is the fifth item in the list.

Finally, you replace the typed in 5 with the MATCH Formula to give

=INDEX(D:D,MATCH("C4-562",C:C,0))

This is a tricky formula to write from memory.

AutoCorrect

If you skipped or didn’t really understand the last section, don’t worry. The title of this chapter is Auto-Magically Master INDEX MATCH, so let’s get on with the magic part. The first part of this requires AutoCorrect.

To introduce AutoCorrect, we’ll discover how typing “,,”(two commas) can AutoCorrect to a bullet point. You can set up AutoCorrect to do all sorts of magical things – that’s a hint as to what’s coming…

Insert a bullet point into Excel using the Symbol icon (on the Insert tab of the ribbon)

Click on the Insert Ribbon and then click the Symbol icon.

Pick Arial font and subset Geometric Shapes (it’s near the bottom of the list)

Choose something that looks like a bullet point, e.g. under Arial, Geometric Shapes.

Double-click on the bullet point and click Close. You should now have a bullet point in one of your cells. Don’t press Enter just yet

Highlight your bullet point (by double clicking on it) and press Ctrl + C to copy it

Important: Don’t copy the entire cell, just the bullet point itself within the cell.

After copying press Escape and then check it’s worked by pasting the bullet point into another cell. We are now ready to paste this bullet point into Excel’s AutoCorrect library.

Click on File followed by Options (or press Alt + F + T) then Proofing and finally AutoCorrect Options…

Click File then Options (or press Alt + F + T) then choose Proofing, AutoCorrect Options…

You now can type “,,” (two commas) in the ‘Replace:’ box and then click in the ‘With:’ box and press Ctrl + V to paste the bullet point in there as pictured:

Using two commas to create a bullet point.

Click Add followed by OK and then OK again.

Now in any cell simply type “,,” followed by a space and your commas auto-magically correct to a bullet point. Excellent! If you happen to not want AutoCorrect to occur, you can always press Ctrl + Z or click Undo.

As you can see from the previous screenshot you could use “$$” to automatically create a “£” or a “€” symbol. Also, any long name you type regularly could be abbreviated here.

Note: If you’ve ever been caught out by typing (c) and it autocorrecting to © then you can delete that in this AutoCorrect Options… window too.

Note: If you have a recent version of Excel and a numeric keypad on your computer, the Excel team made it easier to insert a bullet by using Alt+7 on numeric keypad.

With Great Power Comes Great Responsibility

I’m not suggesting you are the sort of person who likes to play practical jokes on colleagues and family members, but if someone does leave their computer unlocked you could, and I’m not saying should, quickly use File -> Options -> Proofing -> AutoCorrect… to change the word “the” to “idiot”.

Warning: This AutoCorrect dictionary is shared across ALL OFFICE PRODUCTS so this change will affect Word, Outlook, and Power Point etc.

Pre-build Your INDEX MATCH Formula Using AutoCorrect

Finally, here’s the technique to quickly master INDEX MATCH and make it easy to use, even easier than its close rival VLOOKUP. Type the following formula into your AutoCorrect Options window, or copy it and then paste it from the downloadable sample file:

=INDEX(Step3ResultColumn,MATCH(Step1LookupCell,Step2LookupColumn,0))

Remember, it’s File -> Options -> Proofing -> AutoCorrect Options…

Getting AutoCorrect to create your INDEX MATCH formula template.

In the replace box type “iii” or something of your choosing that won’t trigger AutoCorrect accidentally.

Warning: If you use the letter “i” just twice then when you write the word “skiing” the two letters will be replaced by your entire formula!

Using Your Pre-built Formula

Now we have our formula built we can try it out in our earlier example. To do this, type “iii” into the cell F2 on the sheet named ‘Using the AutoCorrect trick’ then press Space:

Using your pre-built formula.

Then double click on the phrase ‘Step1LookupCell’ so that it highlights:

Highlighting the first argument of MATCH.

Follow this by clicking on the cell you want to look up a value for. In this case, it is Cell E2:

Replacing the first argument of MATCH.

Next, double-click on the phrase ‘Step2LookupColumn’ to highlight it.

Double-click on the formula element ‘Step2LookupColumn’.

Go to the column labelled ‘Product Code’ in the ‘Parts List’ sheet and click on column C to select the entire column.

Click on column C to select the entire column.

Then in your formula bar, double-click on ‘Step3ResultColumn’ to highlight it.

Double-click on ‘Step3ResultColumn’.

Finally, select whichever column you’d like to return, in this case click on column A, to return the corresponding ‘Vehicle Group’.

Click on column A to select the entire column.

Press Enter to complete the process. Your answer should be ‘Car’. Try and repeat this process for the next item in the list.

1. To do this, in cell F3 type “iii” followed by a space:

Beginning again in cell F3.

Then do the following steps:

Double-click ‘Step1LookupCell’ and then click on the cell containing the code you are looking upDouble-click ‘Step2LookupColumn’ and go to the ‘Parts List’ sheet and select column CDouble-click ‘Step3ResultColumn’ and select column A from the ‘Parts List’ table

Success!

You can now simply copy the formula down for the rest of the items in the list.

Improvements, Tips and Tables

It’s best practice not to highlight entire columns as it can impact performance on more complex spreadsheets as you are referencing 1 million rows with your MATCH and your INDEX. One option would be just to highlight the used ranges so your formula would change from

=INDEX(PartsList!A:A,MATCH(E2,PartsList!C:C,0))

to

=INDEX(PartsList!$A$2:$A$31,MATCH(E2,PartsList!$C$2:$C$31,0))

That is a really ugly looking formula.

Note: The $ signs are there to lock the references in place as you copy your formula down.

Also, if new data is added to the bottom of your ‘Parts List’ table, then your formula will need to be manually changed to pick up the new data. This is not ideal. The best solution is to turn your Parts List into a Table. An Excel Table is a “thing” and is so useful in many ways. INDEX MATCH and Tables work together beautifully:

It makes your formulas more “readable”As you add more data your formulas will automatically pick up the data

To turn your ‘Parts List’ into a Table, simple click in the ‘Parts List’ and press Ctrl + T followed by clicking OK (the checkbox, ‘My table has headers’, should be ticked).

Converting the ‘Parts List’ data into a Table.

Then you should give your Table a name such as tblParts.

Renaming the Table.

Now you can try out our “iii” AutoCorrect Magic trick as before. Go to cell H2 of the page you were adding formula to then type “iii” followed by pressing Space. Then, double-click ‘Step1LookupCell’ and then click on cell E2.

Editing ‘Step1LookupCell’ and replacing it with cell E2.

Double-click ‘Step2LookupColumn’ and go to the ‘Parts List’ sheet and click on any cell in the tblParts ‘Product Code’ column then press Ctrl + Space to highlight the column.

Clicking in a cell and pressing Ctrl + Space highlights an entire column.

Double-click ‘Step3ResultColumn’ and click in any cell in the ‘Product Name’ column then press Ctrl + Space.

Completing the formula.

The formula reads as follows

=INDEX(tblParts[Product Name],MATCH(E2,tblParts[Product Code],0))

Now press Enter and your result should be ‘Petrol Cap’. This formula is much more meaningful than the alternative

=INDEX(PartsList!$M$2:$M$31, MATCH(E2,PartsList!$C$2:$C$31,0))

If you would rather bring back ‘Selling Price’ just backspace out the word ‘Product Name’ and select ‘Product Selling Price’ instead:

It’s easy to change your mind!

Advantages over VLOOKUP

Those of you that know VLOOKUP may not immediately see the benefit of using INDEX MATCH since the formulas produce the same result. However, INDEX MATCH has three key advantages.

INDEX MATCH can look to the left to return results, e.g. we could not return ‘Vehicle Group’ or ‘Vehicle Section’ data using VLOOKUP as they are to the left of ‘Product Code’

INDEX MATCH is more versatile than VLOOKUP.

INDEX MATCH doesn’t return the wrong answer if you insert or remove columns, whereas VLOOKUP may give the wrong answer when the data set it’s referencing has columns inserted or removedYou don’t have to count columns with INDEX MATCH, you just pick the two you want to use. With VLOOKUP you manually count across the number of columns and type that number in as the column you want to return.

The BIG advantage VLOOKUP used to have was that it was easier to write than INDEX MATCH – not anymore! Now that you know how to Auto-Magically generate your INDEX MATCH formula it’s a simple case of “iii” and a few double clicks.

If I haven’t convinced you of the benefits of INDEX MATCH, then setup a shortcut for VLOOKUP using “vvv” instead:

=VLOOKUP(Step1LookupCell,Step2TableOfData,Step3TypeColumnNo,FALSE)

Relative Named Ranges – When Named Ranges Go Walkabout

by Mynda Treacy

Named Ranges is a vast topic that includes some simple techniques that we all can – and should – use to make our spreadsheets easier to build and maintain. There are also some more advanced techniques, like relative named ranges, which are good to know. They are especially useful for that occasion when you inherit a workbook from an Excel super user who thinks you’ll have no hope deciphering their file.

Relative References

A relative named range returns a result that is relative to the cell in which you use it.

To understand this, let’s take a moment to revisit a concept that every Excel user should know very well and that is the way a relative cell reference automatically updates as it’s copied from one cell to the next.

For example, cell B7 in the image below contains a SUM formula that uses relative cell references B2:B6:

Formulas containing relative cell references.

When we copy the formula in cell B7 (shown in the image above) across to cells C7, D7 and E7 it automatically adjusts the column reference relative to its new location, i.e. copying the formula in B7 across one column to cell C7 will result in =SUM(C2:C6), and so on.

Note: if I were to copy the formula down a row the row references would also adjust as they are relative to both rows and columns.

Relative named ranges work in the same way, and we can use them to replace the individual SUM formulas in row 7 of the example above.

Example

For example, in the image below, row 7 contains the formula =Total_Sales. The Edit Name dialog box shows that the name Total_Sales, which is displayed for the active cell, B7, is referring to the formula:

=SUM(Sheet2!B2:B6)

Total_Sales Named Formula.

Total_Sales is, in essence, a named formula, and I’ll refer to it as such going forward.

Warning: Don’t try to use spaces (“ “) in a range name. They are not accepted, as space is actually the intersect operator in Excel (e.g. trying to use ‘Total Sales’ would make excel seek the intersection of a range name called ‘Total’ with one called ‘Sales’). The use of underscores is a common convention in separating words in range name definitions.

Also notice that the named formulas in cells B7, C7, D7 and E7 are all the same:

=Total_Sales

However, if I edit the name while the cursor is set in cell C7 you can see in the image below that the named formula, Total_Sales, is now referring to cells C2:C6:

Relative Named Range in cell C7.

Likewise, if you edit the names while cells D or E are selected, you’ll find they reference D2:D6 and E2:E6 respectively. In other words, the named formula Total_Sales will always sum the five cells immediately above the cell in which you place it. It does this because the cell references in the ‘Refers to’ field are relative.

Scope of Relative Named Ranges

The named formula, Total_Sales, has the scope of the workbook, meaning I can use it on any sheet, however the ‘Refers to’ specifies that it will always sum cells on Sheet2:

Relative range references Sheet2 in ‘Refers to’ field.

For example, if I enter =Total_Sales in cell B7 on Sheet1 it will sum cells B2:B6 on Sheet2.

If I want to use this named formula relative to any sheet, I can change the ‘Refers to’ to:

=SUM(!C2:C6)

Omitting the sheet name and leaving the exclamation mark in front of the cell references results in a dynamic sheet reference. Therefore, while the named formula will have the scope of the workbook, it will refer to the active sheet.

For example, if I now enter =Total_Sales in cell B7 on Sheet1 it will sum cells B2:B6 on Sheet1.

In other words, I have a truly relative named formula, i.e. relative to both the cells and the sheet.

Warning: This use of an exclamation mark in named ranges has been known to cause Excel to crash and can create problems when used with VBA (e.g. creating an action like Application.CalculateFull), so use it with caution. That said, I’ve never experienced any problems, so it may be resolved in more recent versions of Excel (or I’m more careful these days!).

Creating Relative Named Ranges

Location, location, location. It’s the cliché that should be front of mind when creating relative named ranges.

When you create a relative named range, you should first select the cell that you want the range to be relative to. For example, to create the Total_Sales named formula, I first selected cell B7. Then, from the Formulas tab > Define Name:

Select the cell that you want the range relative to before defining the name.

This will open the New Name dialog box where you can give your named range or formula a name (no spaces allowed, see earlier). Then, select the scope and enter the cell reference or formula in the Refers to field:

New Name dialog box.

Other Uses for Relative Named Ranges

So far, the example we’ve looked at is a relative named formula, but you can also create a relative named range. For example, with cell B7 selected we can name the cells B2:B6 Product_Sales:

Create a relative named range.

We may then use the relative named range in a SUM formula (or any other formula):

Using relative named ranges in formulas.

Relative Dynamic Named Ranges

Dynamic named ranges are a staple for the intermediate / advanced Excel user. They allow us to return a range that adapts to ever changing data or criteria. For example, we might use a dynamic named range as the source for a PivotTable.

Using dynamic named ranges as the source of a PivotTable.

As new rows are added to our source Data_Rng, the dynamic named range also increases to include the new data, thus eliminating the need for us to update the PivotTable source cell references.

Maybe you’re using a PivotTable as the source data for a regular chart. You may use a dynamic named range for the chart source allowing it to automatically pick up changes in the PivotTable size.

Using dynamic named ranges as a chart source.

However, typically these dynamic named ranges aren’t relative.

An area where relative dynamic named ranges will come in handy though, is for Sparklines. In the image below, I’ve inserted a group of Sparklines in column A and you can see the Data Range is hard coded C2:I9:

Fixed Sparkline ranges.

This means that when new data is added for future months in column J onward, we’ll have to edit the Sparkline Data range and update it MANUALLY! That ‘M’ word is enough to make an advanced Excel user queasy.

Now ideally, we’d use a dynamic named range for the Sparkline data range, but you can’t enter a dynamic named range for a group of Sparklines, only for individual Sparklines. I don’t fancy creating seven separate dynamic named ranges, one for each row. That’s way too much work.

Luckily, we can create one dynamic named range that is relative to the cell it’s in and use that for each individual Sparkline (it’s a lot quicker to copy and paste seven Sparklines). With the Sparklines removed, I’ll start with cell A2 selected and then go to the Formulas tab > Define Name.

I’ll call my relative dynamic named range sparkline_rng, and use an INDEX MATCH formula like so:

=$C2:INDEX($C$2:$O$9,MATCH($B2,$B$2:$B$9,0),COUNTA($C$1:$O$1))

In English the formula reads

Start the range in cell C2find the last cell in the range C2:O9 using INDEXfind the row INDEX by MATCHing the value in cell B2 to the cells in B2:B9find the column that INDEX should use by COUNTAing the columns that contain text in the range C1:O1 to find the last column containing values

Tip: The MATCH component of the formula above could be replaced with any other function that will return the row number argument for INDEX, e.g. COUNTA($B$2:$B2) or ROWS($B$2:$B2) will also work.

Define the Sparkline dynamic named range making sure to select the cell that will contain the first Sparkline.

I can check the formula is evaluating correctly by inspecting it in the Name Manager (CTRL+F3).

For example, in the image below, you can see I’ve selected cell A3 and in the Name Manager I’ve selected the sparkline_rng. To see the marching ants around the cells returned by the formula, I simply click anywhere in the ‘Refers to’ field:

Check the dynamic relative named range formula is evaluating correctly.

Now that I know my named range is working correctly, I can insert the Sparklines. Simply enter the first one and in the Create Sparklines dialog box enter the relative dynamic named range in the Data Range field:

Create the first Sparkline.

Then copy and paste the Sparklines one at a time, so they remain ungrouped:

Copy and paste the Sparkline to the remaining rows.

A big thanks to Christopher Mangels for the Sparkline example.

Limitations

Relative named ranges cannot be used in hyperlinks because cell A1 is always the hyperlink anchor for a defined named:

Hyperlinks will not work with relative named ranges.

Using absolute named ranges with Hyperlinks isn’t an issue, but for relative named ranges it essentially renders them absolute, or always relative to A1 when used with hyperlinks.

Get the Workbook

The workbook for this chapter (from https://mrx.cl/mvpfiles ) contains the examples covered and further tutorials on topics covered including:

Relative and Absolute ReferencesAbsolute Named RangesINDEX and MATCH formulasDynamic Named RangesSparklinesCreating Regular Charts from PivotTables

An Introduction to Excel’s New Data Types

by John MacDougall

Since the start of Excel, one cell could only contain one piece of data. With Excel’s new rich data type feature, this is no longer the case. We now have multiple data fields inside one cell!

When you think of data types you might be thinking about text, numbers, dates or Boolean values, but these new data types are quite different. These are really connections to an online data source that provides more information about the data.

At present there are two data types available in Excel; Stocks and Geography.

For example, with the new Stock data type our cell might display the name of a company, but will also contain information like the current share price, trading volume, market capitalization, employee head count, or year of incorporation for that company.

The connections to the additional data are live, which is especially relevant for the stock data. Since data like the share price is constantly changing, you can always get the latest data by refreshing the connection.

Caution: The stock data is provided by a third party. Data is delayed, and the delay depends on which stock exchange the data is from. Information about the level of delay for each stock exchange can be found here: mrx.cl/aboutsources

The data connection for the Geography data type is also live and can be refreshed, but these values should change very infrequently.

Converting Data

Whichever data type you want to use, converting cells into a data type is the same process. As you might expect, the new data types have been placed in the Data tab of the Excel ribbon in a section called ‘Data Types’.

At present the two available data types fit nicely into the space. However, you can click on the lower right area to expand this space, which seems to suggest more data types are on their way!

Data Types in the Data tab.

You will need to select the range of cells with your text data and go to the Data tab and click on either the Stock or Geography data type. This will convert the plain text into what is known as a rich data type.

Warning: You can’t convert cells containing formulas to data types.

You can easily tell when a cell contains a data type as there will be a small icon on the left inside the cell. The federal hall icon indicates a Stock data type and the map icon indicates a Geography data type.

Convert to Stock data type.

The right click menu has a contextual option which is only available when the right-click is performed on a cell or range containing data types. This is where you’ll find most commands relating to data types.

Right-click menu Data Type options.