Follow Contextures Blog - Excel Tips and Tutorials on Feedspot

Continue with Google
Continue with Facebook


Someone sent me a workbook in which a simple VLOOKUP formula was returning #N/A errors, instead of the correct results. The product numbers looked the same, but Excel didn't match them in the lookup. Can you solve this VLOOKUP formula error mystery?

VLOOKUP Formula Error

To show you the problem, here's a screenshot of the lookup table, and the VLOOKUP formula. The formula in cell D5 says that the blue cell (A2) and the green cell (D2) are equal.

However, there's a VLOOKUP formula error in cell E2. Instead of returning the product name, "Blue shirt", the result is #N/A.

Get the VLOOKUP Sample File

If you love an Excel challenge, click here to download the sample file, and see if you can fix the problem - it's a tricky one!

  • If you're not sure where to start looking for the problem, there are VLOOKUP troubleshooting tips on my site.
  • The sample file also has a sheet that shows my troubleshooting steps, and another sheet shows my formula that fixes the problem.


The troubleshooting steps for the VLOOKUP formula mystery start in the next section.

  • Come back and read the rest of the article later, if you want to solve the mystery on your own.
  • Or, keep reading, if you want to see how I found the problem, and fixed the formula
VLOOKUP Troubleshooting Part 1

Usually, this type of VLOOKUP error occurs when one value is a real number, and the other is text number. See the details for that type of Number/Text problem on my website.

To test the numbers in the sample file, I used the  ISTEXT and ISNUMBER functions. The screen show below shows the results of that test, for the values in A2 and D2.

Both cells contain text, not real numbers, so a Number/Text issue isn't causing the errors in this workbook.

VLOOKUP Troubleshooting Part 2

Another common cause for VLOOKUP errors is extra characters in one of the cells – usually extra space characters.

Using the LEN function, I checked the length of the string in each cell.

There are 7 characters in A2, and only 5 characters in cell D2

The TRIM function will remove leading,  trailing, and duplicate spaces.

  • In cell D16, I used TRIM on the value in A2.
  • Then, I checked the length of the trimmed string.

There was no change, so the extra characters are NOT normal space characters.

VLOOKUP Troubleshooting Part 3

The next step is to figure out what those extra characters are.

  • We know there are 7 characters in cell A2, so I listed the numbers 1-7 on a worksheet.
  • In the next column, the MID function extracts one character at each position.

The numbers from cell A2 appear in positions 2-6, and there are hidden characters at the start and end of the string.

In the next column, I used the CODE function, to see what each character number is. Some characters, such as non-printing characters 0-30, can be removed with the CLEAN function. Let's see if our hidden characters are one of those.

No, Excel says that cells D2 and D8 contain character #63.

There isn't a non-printing character with code 63 though, so what is happening?

I typed 63 in cell D10, and used the CHAR function to see the character with that code number. Hmmm…it's a question mark.

So those hidden characters are not really questions marks, but Excel is confused, and returns that code number anyway.  (I found that clue on the Mr. Excel forum).

  • CODE and CHAR use the basic ANSI character set in Windows, which has a maximum code number of 255.
  • The hidden characters are probably from a different character set, and have a code number greater than 255

Whatever they are, we can't use CLEAN to remove those hidden characters.

VLOOKUP Troubleshooting Part 4

To fix the formula, we don't need to know what those hidden characters are. However, I was curious to find out if they were both the same character, or two different characters.

  • To test that, I used SUBSTITUTE, to replace any instances of the first hidden character, with an empty string. That reduces the string by 1 character
  • When I used SUBSTITUTE for each of the characters, both hidden characters were removed from the string.

If you really need to identify one of these strange characters, use the AscW function in a macro, or in the Immediate Window. This bit of code will show the character number for the first character in the active cell.

Sub GetCode()
  Debug.Print AscW(ActiveCell.Value)
End Sub

With that code, I learned that the hidden characters in this file are characters 8237 (cell C5) and 8236 (cell D5)

Solution to VLOOKUP Formula Error

To fix the problem, I set up a "hidden character extraction range".

  • In cell H2, a sample code was pasted from the lookup table
  • In cell I2, a LEFT formula pulls out the first character:  =LEFT(H2,1)
  • In cell J2, a RIGHT formula pulls out the last character:  =RIGHT(H2,1)

NOTE: You could put this information on a hidden sheet. I left them on the same sheet as the VLOOKUP, so it's easier to see how they're used.

Then, combine those hidden characters with the product number in the VLOOKUP formula in cell E2, to get the product name:

=VLOOKUP(I2 & D2 & J2, $A$2:$B$6,2,0)

Thanks to Mohit Kejriwal for sending this question

Get the Sample Workbook

To see all the details on the VLOOKUP Formula error problem and fix, download the sample file.

The zipped file is in xlsx format, and does not contain any macros.


The post Excel VLOOKUP Formula Error Mystery appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

With Excel's data validation, you can show a drop down list of items in a cell. You can even create "dependent" drop downs. For example, select a region, and see only the customers in that region. See how to show a warning in Excel drop down list, if the source data is not set up correctly.

Dependent Drop Down Lists

In this example, select a region name in column B. Then, when you click the drop down arrow in column C, the list just shows the customers in that region.

There are a couple of benefits to dependent drop down lists:

  • It's easier to pick a customer from a short list, instead of the full list
  • It encourages valid customer entries. (Data validation isn't bulletproof – there are ways to get around it)

Get the sample file, and see the complete setup instructions on my website.

The Lists

There are two lists used in this dependent data validation technique.

  • The Region drop down is based on the Regions list.
  • The Customer drop down shows the customers for the selected region, from the Region/Customer table

Customer Drop Down

The dependent drop down for Customer  uses OFFSET, MATCH and COUNTIF to find the customers for the selected region.

  • =OFFSET(RegionStart, MATCH(B2,RegionColumn,0)-1, 1, COUNTIF(RegionColumn,B2),1)

It finds the first instance of the region name, and gets customers from the next column, based on a count of the region name. In this screen shot, East is in the 8th row, and the six customers from that region would appear in the drop down.

See my website, for more details on how the formula works.

Sort By Region

For the OFFSET formula to work correctly, the lookup table MUST be sorted by the Region column. If the list is sorted by customer name, the East region list would show the wrong set of 6 names.

Check if the Regions Are Sorted

In the original version of this technique, you had to remember to sort the list by region, after making any changes to the lookup list. There wasn't a warning system to alert you to problems.

To help avoid errors, I've created a new sample file, and it has formulas to check if the region names are in A-Z order.

There's a new column (SortCheck) in the lookup table, with a formula to check the order.

  • =IF(A3="",0,--(A3<A2))

If an item is out of order, there is a 1 in the row above it. The "East" in A5 is less than the "West" in A4, so cell C4 returns a 1, instead of a zero.

Get the Total Number

In a cell named SortCheck, a formula calculates the total for that SortCheck column.

  • =SUM(tblRegCust[SortCheck])

Another named cell, SortMsg, contains a typed error message that will be used in the data validation.

Show Warning in Excel Drop Down

To show a warning in Excel drop down when necessary, I changed the Customer data validation formula slightly. The IF function looks at the total, and shows the SortMsg range, if the total is greater than zero.

  • =IF(SortCheck>0, SortMsg, OFFSET(RegionStart,MATCH(B2,RegionColumn,0)-1, 1, COUNTIF(RegionColumn,B2),1))

You'll have to fix the list, before you can choose a customer name.

It's easy to overlook a message that's in a worksheet cell, but this error message is hard to ignore!

Other Dependent Methods

For other ways to create dependent drop down lists, go to the following pages on my Contextures site:

Get the Sample File

Get the Warning in Excel Drop Down sample file, and see the complete instructions on my website.



The post Show Warning in Excel Drop Down appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

To save time, create AutoCorrect list entries for words, phrases, and even symbols that you type frequently. Then, type a short code, and Excel automatically changes it to the full text. See how to create an entry, then print a list of all your entries, and copy them to a different computer, using the AutoCorrect macros below.

Create Excel AutoCorrect Entries

The programs in Microsoft Office share a common list of AutoCorrect entries. Any entries that you create or change in Excel, will also be available in Word, Outlook, and PowerPoint.

To see the AutoCorrect list:

  • On the Excel Ribbon, click the File tab, and then click Options
  • Click the Proofing category, and then click the AutoCorrect Options button.

To add a text entry:

  • In the Replace box, type a short code that will be easy to remember.
  • In the With box, type the full word or phrase that will replace the short code, after you type it.

For example, I entered the short code: .dd

When I type that on a worksheet, it will be replaced by my full name: Debra Dalgleish

Create AutoCorrect Symbols

In the AutoCorrect Options window, there is a tab for Math AutoCorrect. Unfortunately, those shortcuts don't work in Excel.

However, you can copy the symbols from that tab, and create regular AutoCorrect entries for them.

This animated screen shot shows the steps - type a short code, copy and paste a symbol from the Math tab, then use the short code on your worksheet, any time you need it.

Print AutoCorrect List Entries

If you'd like to see everything that's in your AutoCorrect list, run this macro. It was written long ago by Dana DeLouis, and he shared it in the Excel newsgroups.

I added a line to insert a new sheet, so you don't accidentally overwrite anything. The macro lists the short codes (Replace) items in column A, and the full text (With) in column B.

Copy this macro, and paste it into a regular code module.

Sub AutoCorrectEntries_Display()
'// Dana DeLouis
'// Backup AutoCorrect to Worksheet
'2018-01-30 added line to insert new sheet
   Dim ACE
   ACE = Application.AutoCorrect.ReplacementList
   Range(Cells(1), Cells(UBound(ACE), 2)) = ACE
End Sub
Edit the AutoCorrect List Entries

After the AutoCorrect list is created on a worksheet, you can manually edit it, before you import the list to a different computer.

For example,

  • add new codes and replacement text
  • modify the replacement text for personal entries that you previously created
  • delete any personal entries that won't be needed on the other computer

Or, just leave the list as is, then save and close the workbook.

Copy AutoCorrect List Entries

After you create your list of AutoCorrect entries, use the following macro (also by Dana DeLouis)  to add those entries on a different computer.

  • Open the workbook with the list,
  • Activate the sheet that has the exported list of AutoCorrect items.
  • Run the macro below, to add those items to the new computer's list.

What the macro does:

  • adds any new items
  • overwrites any matching codes on the new computer
  • does NOT remove any existing entries that are not in the imported list

Copy this macro, and paste it into a regular code module.

Sub AutoCorrectEntries_Add()
'// Dana DeLouis
'// Add AutoCorrect entries.
'// Column A -> Wrong   Word
'// Column B -> Correct Word
   Dim rng As Range
   With Application.AutoCorrect
      For Each rng In Columns(1) _
      .SpecialCells(xlConstants, _
         .AddReplacement rng, rng.Offset(0, 1)
   End With
End Sub



The post Create and Copy AutoCorrect List Items in Excel appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Last week, I ran into problems counting Excel data with COUNTIF, and it's Twitter's fault! Why did they do that? The COUNTA function can cause problems too, when it counts cells that look empty. Let's see how to fix both of those issues.

Check for Duplicates

So, how did Twitter break my COUNTIF formula?

Every Thursday, I collect tweets for my weekly Excel Twitter post. The tweets are pasted into an Excel file, and a COUNTIF formula checks for duplicate content.


The result should be 1, unless there are duplicates

Last Thursday, after years without problems, one row returned a #VALUE! error, instead of a number.

Twitter Limits

Recently, Twitter changed from a 140 character limit, to a 280 character limit. My workbook has a formula that checks the length of each tweet, and that one was 267 characters – the longest tweet that I've ever pasted into the workbook!

And yes, I have conditional formatting that highlights any tweet over 140 characters. Don't judge me.

COUNTIF Character Limit

Unfortunately, Microsoft didn't raise its character limits, when Twitter increased theirs. That's what caused the error – COUNTIF/COUNTIFS can only check strings up to 255 characters. Other functions have the same limit.

Only 255 Characters

Here's another sample to show the problem. The 255 length works in row 4, but there is an error in row 5, which has one extra character – an X at the end.

To get a correct count, I used an old, reliable function --SUMPRODUCT, instead of COUNTIF. And since I was improving the formula, I created named ranges too, and wrapped it with an IF function.


Confusing Workaround

Later, I checked Microsoft's COUNTIF page, and it says you can get around the 255 limit, by joining two long strings with the concatenate operator (&). That suggestion did NOT work for me though - maybe I'm missing something:

  • =COUNTIF(A2:A5,"long string"&"another long string")

Can you get that Microsoft formula to work?

Click here to download my sample file, and see more COUNTIF examples on my website.

COUNTA Counts Empty Cells

In other Excel count functions news, I've done a major update on my blog post about problems counting Excel data when cells look empty, but they aren't.

In the screen shot below, data was copied from an Access database, and pasted into Excel. The COUNTA formula in cell C2 is counting those "blank" cells, even though they look empty.

Other Causes

It's not just data from Access that creates these strange "blank" cells. They're also created if you convert formulas to values, and some of the formulas returned an empty string ("").

This screen shot shows that type of formula, and when pasted as values, the empty string cells are counted.

See Hidden Contents

In the update, I added a tip that lets you see something in those "blank" cells.

  • On the Excel Ribbon, click the File tab
  • At the left, click Option
  • In the Category list, click Advanced
  • Scroll down to the end of the Advanced options, and look for the Lotus Compatibility section
  • Add a check mark to Transition Navigation Keys

After you turn this option on, click on a cell that looks blank, and check the Formula Bar. You should see an apostrophe there.

Remember to turn this option off later, when you've finished the troubleshooting.

Fix the Problem

My original solution was to use Find and Replace. The blanks were replaces with $$$$, and then the $$$$ were replaced with nothing. There are instructions to manually do those steps, and there's a macro too.

The update has 2 new solutions, thanks to the people who posted their suggestions in the comments section.

How would you fix the problem?



The post Problems Counting Excel Data COUNTIF COUNTA appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

If you try to group pivot table items in Excel, you might get an error message that says, "Cannot group that selection." For older versions of Excel, if you had a problem grouping pivot table items, it was usually caused by blank cells, or text in number/date fields. For Excel 2013 and later, there's another thing that can prevent you from grouping.

Problem Grouping Pivot Table

Here's a screen shot of the "Cannot group that selection." error message that appears. The message doesn't give you any clues as to why you can't group the items. It's up to you to do the detective work.

Data Model

We'll look at the traditional reasons for this grouping problem in the next section. But first, here's the newer issue, that might affect you, if you're using Excel 2013 or later.

  • When you create a pivot table, there's a check box to "Add this data to the Data Model".
  • If you checked that box, you won't be able to group any items in the pivot table.

When the source data is added to the data model,  you end up with an OLAP-based Power Pivot, instead of a traditional pivot table, and the grouping feature is not available.

Is It OLAP-Based?

A quick way to tell if your pivot table is OLAP-based is to check the Ribbon:

Select any cell in the pivot table

  • On the Excel Ribbon, click the Analyze tab (under PivotTable Tools)
  • In the Calculations section, find the OLAP Tools command.
    • If it's dimmed out, your pivot table is the traditional type
    • If the command is active, your pivot table is OLAP-based

And if you check the Fields, Items, & Sets drop down, some of the features will be dimmed out, for OLAP-based pivot tables.  For example, you can't create a calculated field or calculated item.

Fix the Grouping Problem

I haven't found any way to change the pivot cache from an OLAP-based source (data model), to a data source that isn't in the data model. So, if you need grouping, create a new pivot table from the source data, and do NOT check the box to add the data to the Data Model.

NOTE: You can keep the OLAP-based pivot table too, and have two pivot tables based on the same data, using different pivot caches.

Blank Cells or Text

If your pivot table is the traditional type (not in the data model), grouping problems are usually caused by invalid data in the field that you're trying to group.

If you're trying to group dates or numbers, the grouping problem usually occurs when the field contains records with one of these items:

  • a blank cell in a date/number field, or
  • a text entry in a date/number field.

To fix the problem

  • For blank cells, fill in the date/number (use a dummy date/number if necessary).
  • If there is text in the date/number field, remove it.
  • If numbers are being recognized as text, use one of the techniques to change text to real numbers.

Then refresh the pivot table, and try grouping the items again.

Previous Grouping

If you don't have blank cells or text in the date column, there may be a grouped field left over from a previous time that you grouped the data.

  1. Check the field list, to see if there's a second copy of the date field, e.g. Date2.
  2. If there is, add it to the row area, and ungroup it.
  3. Then, you should be able to group the date field again.
Pivot Table Grouping

Learn more about pivot table grouping, and get a workbook with sample file that you can use for testing. Go to the How to Group Pivot Table Data page on my Contextures website.



The post Problem Grouping Pivot Table Items appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Do you use Excel’s SUBSTITUTE function very often? It’s a handy way to count items in a cell, when they’re separated by commas or spaces. The examples below show different ways to use this function – have you tried the technique in the last example?

Count Items in a Cell

In the first example, we’ll count the items in a cell, when they are separated by commas. The items are in cell A2, and this formula is entered in cell B2:


This formula uses LEN and SUBSTITUTE to count the items.

  1. LEN(A2) – Counts the number of characters in cell A2
  2. SUBSTITUTE(A2,”,”,””) – Replace each comma with an empty string
  3. LEN(SUBSTITUTE(A2,”,”,””) – Count characters in cell A2, with all the commas are removed
  4. Subtract amount 2 from amount 1, to get the number of commas
  5. Add 1, to get the number of items
TRIM the Spaces

If you’re using space characters to separate the items in a cell, be sure to use the TRIM function, to clean up any leading, trailing, or extra spaces.

Without TRIM, the item count could be incorrect. Here is an example without TRIM:

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))+1

The counts in rows 4 and 5 are too high, because of extra spaces.

Here’s the revised formula to count items in a cell with space character separators:


The counts with this formula, in column B, are correct, and the incorrect counts (with the old formula), are in column C, for comparison

Check for Minimum Count

Instead of just counting the items in the cell, you can change the end of the formula, and check for a minimum item count.

In this example, a street name is entered in column A.

  • There should be at least 3 items entered in the cell, such “123 Main Street”.
  • If there are 3 or more items, there will be 2 or more spaces.

Change the end of the formula – instead of adding 1, to get the word count, check that the number of spaces is 2 or more


The results are TRUE or FALSE, and you could use a formula like this as a custom rule for  data validation in a cell.

Check Minimum Count Version 2

Another way to check for a minimum word count is with this shorter formula – thanks to UniMord for suggesting it.

Instead of checking the cell contents with LEN, the formula replaces the 2nd space character (if there is one), and then compares with the original value.

=TRIM(A2)<>SUBSTITUTE(TRIM(A2),” “,””,2)

How It Works

This formula uses 2 as the 3rd argument in SUBSTITUTE, to specify which space character (instance number) to replace.

The second space is removed, and you can see the results of that step in column C(it’s there as a demo only).

Usually, we omit the 3rd argument, so ALL instances of the text are replaced. But in this case, it’s helpful to just replace the 2nd space character, as our test.

  • We want a minimum of 3 items, so there should be at least 2 space characters.
  • If we replace the 2nd space, the result is different from the original value
  • So, if the original is not equal to the revised value, there are 3 or more items.
Download the Sample File

To download the Count Items in a Cell sample file, go to the Excel Files page on my Contextures website. In the Functions section, look for FN0053 – Check Item Count with SUBSTITUTE

There are more SUBSTITUTE examples on my Contextures Blog., with a workbook that you can download, to try the examples from that article.


The post Count Items in a Cell with SUBSTITUTE appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

To see specific data in an Excel Table, you can select an item from the drop down filter in a column heading. Someone asked me if there was a way to scroll through filter items, instead of opening the filter list each time. The technique described below uses a pivot table, which could be hidden on a different sheet, and a spin button, to go up or down in the list of filter items.

Scroll Through Filter Items Demo

This animated screen shot shows how the scrolling technique works. Use the Spin Button to scroll through filter items.

  • Click the Up button, to filter by the next product in the list
  • Click the Down button, to filter by the previous product in the list
  • When you reach the beginning or end of the list, the next selection is “All”

Why Use Spin Buttons?

I wrote the original code for this technique long ago, to scroll through filter items in a pivot table report filter. Slicers hadn’t been invented yet, and the spin button was a quick way to filter a pivot table.

Now you can use Slicers to filter a pivot table or Excel table, but they take up a lot of space on a worksheet. A Spin Button is a compact way to go through a list of items, in alphabetical order.

In this screen shot, you can see the size of the Spin Button, compared to a Slicer for the Product field.

How to Set Up the Spin Buttons

First, I added an ActiveX Spin Button on the worksheet – there are detailed instructions on the Report Filter Macros page of my website.

NOTE: In Excel for Mac, ActiveX controls are not available. You would have to use the Spin Button from Form Controls, and create code to work with that.

Next, I added code to the Spin Button – right-click on it, and click View Code.

Select the SpinUp and SpinDown procedures, and add two macro names in each procedure.

The macros will be added to the workbook shortly. These macros change the selected item in a pivot table’s report filter, and then change the selected item in the Excel Table’s Product column filter.

Create a Pivot Table

Next, create a pivot table, based on the Excel table that you want to filter. You can put the pivot table on the same worksheet, or on a different sheet. In the sample file, the pivot table is on the same sheet, so it’s easier to see how the technique works.

The only field in the pivot table is Product, in the Report Filter area. In the screen shot below, you can see all the items in the Product field.

Add the Pivot Table Macros

Next, you’ll add two macros – PivotPageUp and PivotPageDown. The code is in the sample file (on the modPivot module), and on the Report Filter Macros page of my website. Store this code in a regular code module.

  • The code gets the current item number, then adds or subtracts 1, to get the new item number.
  • It shows that item, or shows “All”, if the previous item was at the beginning or end of the list.

NOTE: Adjust the macros, if your pivot table is not on the same sheet as the Excel Table.

Add the Change Filter Macro

The final macro is named ChangeFilter, and it  is stored on a regular code module. The code is in the sample file, on the modFilter module.

  • This macro gets the name of the current page in the pivot table’s Report Filter.
  • It selects that item in the Excel Table’s Product column, or clears the filter, if “All” is selected.
Download the Sample File

To see how the macros scroll through filter items in an Excel Table, download the sample file from my website. In the Download section on the Report Filter Macros page, look for the download named Pivot Spinner Table Filter.

The zipped file is in xlsm format, and contains macros. To test the code, enable macros when you open the file.


The post Scroll Through Filter Items in Excel Table appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

It’s only a few days until Christmas, so it’s time to give yourself a gift! Take advantage of the slow time at work, and start the new year with advanced Excel training, to upgrade your skills. I’ve selected a bundle of 3 courses from the Excel with Business website, and you can get lifetime access to all 3 courses at a deeply discounted price.

Advanced Excel Training

Here are the 3 courses that I selected — these courses are for intermediate and advanced Excel users only. They have many courses for beginners too, but I knew that you wouldn’t be interested in those.

  • Advanced Excel
  • Introduction to Data Science
  • Business Analysis

Click here to see the Excel Master bundle details.

NOTE: I had time to go through some of the Advanced Excel training material, and my review is below.

Lifetime Access

Usually, you would get one year of access to the training, but you’ll get lifetime access to the 3 courses, if you get this bundle. So, you can do all three courses now, or space out your training – you won’t have to worry about time running out.

Later, if you want to review some of the material, just log in again, and refresh your memory. It will be a great resource when you’re starting new Excel projects at work!

The Company

Before I agreed to let you know about their courses, I looked into the Excel with Business company. How long had they been around, and what is their reputation? Are their courses popular, and what do the students have to say about them?

The company started in 2009, which is a long time, especially in Internet years! The founders wanted to create Excel training that was business-focused, rather than just explaining how the features work.

That was a good decision, because their first course had over 100K users! Since then, they’ve added more courses, and their library has more than 50 courses, covering Excel, other Microsoft applications, and business training.

The courses get great reviews too – you can see the ratings and student comments about Excel with Business, at the bottom of the course pages.

The Instructors

It’s important to have highly-qualified instructors too, and here’s some info on the experts leading the advanced Excel training courses that I picked for our Excel Master bundle.

  • Advanced Excel is led by Simon Hurst, whose name I recognized instantly. He’s a Chartered Accountant in the UK, who has led Excel training for more than 20 years, and I’ve read many of his online articles. Simon has a talent for explaining complex topics in a clear and simple way.
  • For Business Analysis, Harold Graycar is the instructor. He has degrees in engineering and computer science, and an extensive business background. He has used Excel since it was first launched, and has developed several Excel modelling tools for financial  and other business applications.
  • The Data Science course is led by Dr. Chris Littlewood, who is one of the founders of Excel with Business. He has degrees in physics and maths from Oxford and Cambridge, and many years of business experience in analysis and strategy development. He developed the filtering algorithm that lets you customize your training material – I’ll tell you more about that in the course review, below.
Advanced Excel Course

I also wanted to see the courses, before recommending them to you. I’ve had time to go through some of the Advanced Excel course, and here’s what I liked about it, and a couple of things that I’d do differently.

Course Dashboard

When you first log in to the Advanced Excel course, you’ll see a dashboard. It has a list of topics, and a bar chart over the section titles gives you a quick view of how long each section takes, and how much you have completed.

Course Filter

At the top right, there is a "Take Filter" button, and that helps you customize the course material.

Click the button, and "The Filter" guides you through a few questions. That personalizes the training, by filtering out the content that you already know, or don’t need to know. For example, you can choose the level of knowledge that you need – anything from "know just the basics" to "be a complete master".

You could start with the basics, then come back later, to get to the "master" level. That’s an advantage of the lifetime access that you’ll get with this bundle.

Skills Test

For the next step in the filter, there is a 15-minute skills test, with 15 compulsory questions and 15 optional questions. Don’t worry about taking the test – it’s designed to see where your skills are now, and to customize your course material.

But, if you really don’t like tests, you can click the "I’m a beginner, skip the test" button.

I went through the 15 compulsory questions, and then it showed my score. A couple of the questions were a bit confusing, like the first one, that listed "All the Above" as the second option. Hmmm.

But, it was interesting to take the test, and to see my results. At the end, a grid was displayed, showing the lessons that have been filtered out of my advanced Excel training plan.

Start the Course

After you complete the filter, you can begin the lessons in the course. There is a Start Course button at the top right, so click that to get started.

Once you’re in the course content, it’s easy to go to any other module. At the left, there is a grid that represents all the section modules.

  • Point to any box in the grid, to see its title
  • Click on any box, to go to that module.

The grid is coloured coded too, which makes it even easier to navigate:

  • Grey boxes are filtered out
  • White boxes are to be done
  • Checked boxes are completed
  • Green box is your current module

This is great, if you’re like me, and you like to jump around a bit, rather than going step by step through the lessons.

Course Content

I went through a few of the Advanced Excel modules, to review the content, and the presentation style. The course is divided into 6 sections. Each section has several units, and there are multiple modules within each unit.

The last module in each Unit is an Exercise, where you can download an Excel file, to review the skills that you learned.

For example, here is Section 4 – Spreadsheet Impact. It has 4 units, and the unit on Sparklines has 3 modules.

Course Modules

Each lesson module has 3 parts:

  • The modules starts with a Concept statement, to help you understand what will be included in the module.
  • Next, there is a Method section, with written descriptions and screen shots, to explain the module’s topic. In the Method section, there were usually videos too, that demonstrated the techniques.
  • Finally, there is a Knowledge Check section at the end, with one or more questions about the material.

In the Exercise module, there is an Action section, where you can download the sample file. Below that, there are 3 boxes, where you can mark the unit complete.

  • This was useful
  • I knew this already
  • This was not relevant

Your selections must be stored, as part of the filtering algorithm.

Advanced Excel Review

After reviewing the Advanced Excel course, I can highly recommend it. The course is a great investment, especially with the lifetime access, and discounted price that you’ll get in this Excel Master Course Bundle.

After completing the Advanced Excel course, and doing all the lessons and practice exercises, you’ll be ready to tackle new challenges at work, or for your personal Excel projects.

Here are some of the pros and cons that I found, and a warning!

  • Excellent course content and instructor
  • Filter tool removes unnecessary content from your course plan
  • Modules have written instructions, screen shots, videos, and quizzes, so there is something to suit many different learning styles.
  • Wide range of advanced Excel topics
  • Examples show how to apply the techniques in your business projects
  • Sample files help you review the material from each Unit
  • There were a few spots where I wished for more details, but it’s understandable that not every topic can be given extensive coverage
  • The grid popups don’t include section or unit titles, just module names, such as "Introduction". For ambiguous titles, you can point to adjacent blocks to see their names.
  • The Filter removes unnecessary material, but some of that might be of interest to you. Take a quick look at the filtered out modules, to see what’s covered in them
  • Section and Unit links appear at the top of each module, but disappear when you scroll down – it would help to have those locked in view
  • The Start Course button took me to Section 5, instead of Section 2, where I expected to start. That was confusing, but the grid made it easy to select an earlier module
  • There were short pauses in some videos, that could have been edited out
  • As the course title indicates, this is advanced Excel training. Unless you already have intermediate Excel skills, the course could be overwhelming. Start with the Basic Excel course or Intermediate Excel course instead, and then get advanced Excel training later.
  • It’s not magic! Be sure to download the sample files too, and do the reviews, to reinforce what you’ve learned. The material is designed to take you to the advanced level in Excel, so you’ll have to do some work to achieve that goal – you won’t magically become an expert, if you just skim through the lessons.
Get the Contextures Excel Master Bundle

To learn more about the Excel Master bundle, or to sign up for the courses, click this link, or click the picture below. Remember, you will have Lifetime Access to all 3 courses, and the price is deeply discounted.

Get 2018 off to a great start, with a new set of Excel skills!


The post Advanced Excel Training Master Bundle appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Here is an end-of-the-year Excel Roundup, with articles that I’ve read recently. To get weekly links and articles, sign up for my weekly Excel newsletter. Merry Christmas, and happy holidays, and come back for a new blog post in January!

Excel Data

Power BI – For everything that you wanted to know about Power BI, download a free copy of Reza Rad’s book, Power BI From Rookie to Rockstar. Or, read the book online — scroll down about halfway on that page, to see the table of contents.

Clean Data – Apparently, messy data is the biggest headache for machine learning and data science. That article is based on Kaggle’s interesting survey of 16K data scientists.

Dashboards – Tableau shared 7 tips and tricks for building dashboards, and they’re great tips for Excel dashboards too. And for dashboard charts, read these 5 Data Viz tips from Jorge Camoes.

Excel Master: Start the new year with an upgrade to your Excel skills. I’ve selected a bundle of courses from the Excel With Business website, with a great discount on the price. Get 3 courses – Advanced Excel, Introduction to Data Science and Business Analysis — at one low price, for lifetime access. This offer is for intermediate and advanced Excel users only! Click here to see the bundle details.

Excel Complaints

Excel Complaints – A recent WSJ article reported that some CFOs want their employees to stop using Excel. This Bloomberg article says some complaints are valid, but others are misguided.

Number Problems: If you hate the way that Excel messes up some number when you import them,  vote for this fix request on UserVoice. It has almost 1200 votes, and the last response I see from Microsoft was 2 years ago.

Table Problems: Jeff Weir also has an Excel complaint – problems with Excel tables on protected sheets. There is a UserVoice request to fix that too, and Jeff hopes that you’ll add your vote there.


Advanced Formulas: Ben Collins is offering a free course in Google Sheets Advanced Formulas. Many of the functions have a twin in Excel, such as VLOOKUP, INDEX and MATCH. You’ll also see functions that Excel doesn’t have, like QUERY and FILTER. I don’t know how long Ben’s course will be free, so if you’re interested, sign up soon.


Formatting: Chandoo shares five conditional formatting tips, from simple highlighting, to complex formula rules – get the sample workbook too. If you want to learn more, there are conditional formatting examples on my website.

Excel Fonts – If you want to choose the best fonts for an Excel report, Kevin Lehrbass has a free workbook that lets you compare several fonts at once. (Level – All)

Excel Fun

Excel Art – Tatsuo Horiuchi creates amazing art in Excel, as you can see in the video at that link. There’s more Excel art on the Datalabs site, created from charts. And Andy Pope has a free tool for converting a normal BMP image into an Excel scatter chart.

Excel Games – If you enjoy games, take a look at this Excel Sudoku Solver. Also, Andy Pope has a few Excel games on his website, and lots of other great stuff, like this Chart Exporter.

Excel Christmas Tree – And finally, a bit of holiday fun. Download the sample file from my website, and you can decorate an Excel Christmas tree, using a spin button. There are NO macros in the file – just conditional formatting, formulas and named ranges. Merry Christmas!


Excel Roundup 20171214

The post Excel Roundup 20171214 appeared first on Contextures Blog.

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

As Juliet famously said to Romeo, “What’s in a name?” And she was talking about rows (misspelled as “rose”), so maybe Juliet was using a spreadsheet at the time. There are special rules for Excel names, but you might be surprised to see what is allowed.

Microsoft Rules for Excel Names

In Excel, you can create names that refer to cells, or to a constant value, or a formula. After you create Excel names, you can use them in formulas, or quickly go to a named range.

There are rules for Excel Names, and here’s what Microsoft says is allowed. It seems clear, but a few of the rules aren’t as ironclad as they look:

  • The first character of a name must be one of the following characters:
    • letter
    • underscore (_)
    • backslash (\).
  • Remaining characters in the name can be
    • letters
    • numbers
    • periods
    • underscore characters
  • The following are not allowed:
    • Space characters are not allowed as part of a name.
    • Names can’t look like cell addresses, such as A$35 or R2D2
    • C, c, R, r — can’t be used as names — Excel uses them as selection shortcuts
  • Names are not case sensitive. For example, North and NORTH are treated as the same name.
How To Name Cells

It’s easy to name a range of cells – here’s what I usually do (there’s a video at the end of this article too):

  • Select the cells that you want to name
  • Type a valid one-word name for those cells, in the Name Box at the left of the Formula Bar.
  • Press Enter, to complete the name. If you forget that step, the name doesn’t stick.

Later, you can use those names in formulas, or for navigation.

Quick Names

Another easy way to create names is based on text that you’re already entered on the worksheet. For example, type “Months”, then the month names in the 12 cells below that.

  • First, select the heading cell, and the cells that you want to name.
  • Then, on Excel’s Formulas tab, click the Create From Selection command.
  • Check the box to tell Excel where your headings are (top, left, bottom or right), and click OK
  • Excel names the ranges with valid names, based on your headings

Since Excel creates the names in this method, you don’t have to worry about what’s valid.

NOTE: You can see the step in the video at the end of this article.

Beyond the Basic Rules for Excel Names

Even though Microsoft’s rules for Excel names say that you must use only letters, numbers, periods, underscores and backslashes, other characters are allowed. It seems that “letters” has a broad interpretation.

I learned about “beyond the basics” technique from Peter B., who sent me a workbook in which he used Unicode text in his Excel names (shown below). I didn’t know that was possible. Thanks, Peter!

My Test of the Rules

Inspired by Peter’s examples, I did a few simple name tests, using characters from the “Alt and Number Keypad” set. For example, I typed an “a” in the Name Box, then Alt+1, Alt+30 and Alt+31. That means “a happy faces goes up and down”, in case you were wondering.

In the screen shot below, you can see that name in the Name Box drop down list, along with a few other unconventional names that I created.

Excel used the characters from cell D2, when I created a name using the “Create From Selection technique on that range.

However, when I created a couple of names in the Name Manager, the special characters show up correctly there, but appear as question marks in the Name Box drop down list. You can see a couple of those in the screen shot above.

Testing the Rules for Excel Names

After looking at Peter’s examples, and doing a few experiments, I did some searching, so see what documentation there might be online, for these anomalies. What is allowed, and what isn’t?

An link in an old Excel newsgroup post led me to a treasure trove of information on rules for Excel names – Martin Trummer’s GitHub project on excel-names. Martin has done an in-depth study of what’s allowed, beyond the basic letters and numbers.

Go to Martin’s project page, and you’ll see his written examples, and there is an Excel file to download. Thanks to Martin, for doing this research!

Video: Name a Range of Cells

Watch this short video to see how to name a group of cells, go to that named group of cells, and use the name in a formula.

Name a Range of Cells in Excel - YouTube

Video: Create an Excel Name from Selection

To quickly name individual cells, or individual ranges, you can use heading cell text as the names. Watch this video to see the steps, and go to the Excel Names page on my website for more details and videos.

Quickly Name Selected Cells in Excel - YouTube



The post What Are the Rules for Excel Names? appeared first on Contextures Blog.

Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview