• IMPORTANT NOTICE: The Excelguru Help Forums will be permanently shut down on Friday, June 26, 2026.

    With AI taking a more prevalent role in answering user questions, the traffic to the Excelguru Help Forums has seen a sharp decrease in traffic over the past couple of years. We do not see this trend changing anytime in the near future. As such, continuing to run the forums is just no longer feasible, so we have made the difficult decision to close them down at the end of the month.

    We appreciate everyone who joined our forums to ask and answer questions over the past decade – and in particular – want to say a huge THANK YOU to the moderators and administrators who volunteered their time and expertise on a daily basis. You made the community what it was.

    NOTE: NEW USER ACCOUNTS CAN NO LONGER BE CREATED.

Named range not visible in the import wizard

M

mikeger90

New member
Joined
Mar 20, 2026
Messages
4
Reaction score
0
Points
1
Excel Version(s)
2024
Hello,


I perform the following steps in the source file:

1. I create a formatted (Excel) table from a cell range.

2. I define a named range based on this table (Formulas → Define Name).



After that, I perform the following steps in the target file:

1. I open Power Pivot and choose “From Other Sources” → “Excel File”.

2. I select the source file.

3. In the import wizard, the named range is not shown as a view.



If I define a named range for a regular cell range, it works (view is visible in the import wizard), but not when the named range refers to an Excel table.

So I have two questions:

a) Why is the named range not displayed as a view?

b) Does this mean it is simply not possible? What is the advantage for Power Pivot of formatting the source range as a table? In many articles, this is described as a major benefit, but I honestly cannot see it.



Best regards,

Mike
 
I think this is a "bug" in excel. However, if you select what you believe is the correct table even though there is no table name and select preview you can determine if this is the correct table/range to bring into the data model.
 
You’re absolutely right about that; however, the Power Pivot import wizard seems to display only named ranges and entire worksheets, not ranges formatted as tables.
 
Power Pivot import wizard seems to display only named ranges and entire worksheets, not ranges formatted as tables.
Click to expand...
this appears to be a true statement. I would urge you to raise this issue with Microsoft as they are the only ones who can change how Excel and PP work.
 
After carrying out step 2 on the source workbook, do you then save that source file before opening Power Pivot? I'm pretty sure it only looks at the Excel workbook which won't have the new named range until it has been saved.
 
Hello p45cal,

yes I save the file, before I start Power Pivot/the import wizard.
 
The Power Pivot import method does not recognise dynamic named ranges (that includes both Tables and regular ranges defined dynamically). The benefit to using tables is really when the data is in the same workbook.
 
I should also add that it is generally better, in my opinion, to bring data in using power query, then load it into the data model from there.
 
You must log in or register to reply here.
Back
Top