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!
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.
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
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)
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.
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 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.
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.
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.
'// Dana DeLouis
'// Backup AutoCorrect to Worksheet
'2018-01-30 added line to insert new sheet
ACE = Application.AutoCorrect.ReplacementList
Range(Cells(1), Cells(UBound(ACE), 2)) = ACE
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.
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
'// Dana DeLouis
'// Add AutoCorrect entries.
'// Column A -> Wrong Word
'// Column B -> Correct Word
Dim rng As Range
For Each rng In Columns(1) _
.AddReplacement rng, rng.Offset(0, 1)
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.
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.
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")
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.
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.
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.
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.
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.
LEN(A2) – Counts the number of characters in cell A2
SUBSTITUTE(A2,”,”,””) – Replace each comma with an empty string
LEN(SUBSTITUTE(A2,”,”,””) – Count characters in cell A2, with all the commas are removed
Subtract amount 2 from amount 1, to get the number of commas
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:
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
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.
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.
NOTE: I had time to go through some of the Advanced Excel training material, and my review is below.
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!
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.
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.
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.
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.
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.
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.
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!
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!
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.
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 – 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.
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.
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!
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.
The first character of a name must be one of the following characters:
Remaining characters in the name can be
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.
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.