Named ranges error when importing from Excel
When trying to import an Excel sheet with contacts into Outlook I get the error;
"An error has occurred in the Microsoft Excel translator while getting the contents of a file system.
The Microsoft Excel file <filename> has no named ranges. Use Microsoft Excel to name the range of the data you want to import."
How do I do that?
A “Named Range” is actually nothing more than a selection of information in an Excel sheet which is grouped together. This allows you to work more easily with that set of information as a whole.
Still sounds confusing? Don’t worry, let’s create a named range first;
- Open the Excel sheet in Excel.
- Select all the cells that contain information that you want to import into Outlook.
Warning! Do not press on the column headers to select an entire column at once as you will also select all the empty cells underneath it and thus ending up with a lot of empty contact in Outlook. - This selection will be the Named Range;
- Excel 2003 and previous
Insert-> Name-> Define… - Excel 2007, Excel 2010, Excel 2013 and Excel 2016
Tab Formulas-> section: Defined Names-> button Define Name
- Excel 2003 and previous
- A new dialog will open where you can specify a name for your range.
Here type; contacts - Save and close the Excel sheet.
- Start the import process again from within Outlook and you won’t get the “named ranges” error.
Workaround and Outlook 2013 and Outlook 2016
Instead of creating a named range for the xls-file, you can also save your Excel sheet as a csv-file and import that into Outlook. No named ranges are required then.
This is also the way to go for Outlook 2013 and Outlook 2016 since these versions of Outlook no longer support importing from xls-files but do support csv-files.
More information
You might wonder why you need to do this and how this can benefit you in your work.
You might have noticed already that on the final screen of the import process (the one with the button Finish) it reads;
Import “contacts” into folder: Contacts
As you can see, here was your Named Range used. When you created additional named ranges into your Excel sheet with different sets of information, then on that screen you could select which named ranges you wanted to import. This allows you to use a single Excel sheet to maintain multiple sets of contact information such as on additional sheets or additional smaller selections (such as per company) within your large set of data.
Note: There are of course a lot more other uses to Named Ranges in Excel itself. You can find some more info and tips about them here.
