Working With Excel Named Ranges In Power Query

One of the more recent additions to Power Query is the ability to access data from named ranges in the Excel worksheet rather than an Excel table. I’ve got used to formatting data as tables in Excel because that’s what Power Pivot needs to import data directly from the worksheet, but if you are working with Power Query and a pre-existing workbook then trying to reformat data as tables can be a pain. Also, if you just want to import a single value, for example as a parameter to a query, a table seems like overkill.

(Incidentally, if you’re wondering what a named range is in Excel, there are tons of good introductions to the subject on the internet like this one. You can do loads of cool stuff with them.)

Consider the following Excel worksheet:


There are three named ranges here: FirstRange, SecondRange, ThirdRange, and the values in the cells show which range the cells are in. FirstRange consists of two cells in two columns; SecondRange consists of three cells in a single row; and ThirdRange is consists of three, non-contiguous cells. (You can also use this trick to display the names of all contiguous ranges in an Excel workbook, but alas it does seem to work for non-contiguous ranges).

At the moment, the Power Query ribbon doesn’t make it obvious that you can use named ranges as data sources. However you can see all the tables and cells in a worksheet, and even return that list from a query, by creating a blank query and using the expression

= Excel.CurrentWorkbook()


Once you’ve done this you can see all the ranges (and also any tables) in the workbook, and click on the table link next to the name to see the data. For example, clicking on FirstRange shows the following table in a new step in the query editor:


The expression to get at this table in a single step is:

= Excel.CurrentWorkbook(){[Name="FirstRange"]}[Content]

The range SecondRange in my example is equally straightforward to reference, and you can see its contents by using the expression

= Excel.CurrentWorkbook(){[Name="SecondRange"]}[Content]


Unfortunately ThirdRange, which is not contiguous, is a problem: I can only get the first cell in the range. So the expression

= Excel.CurrentWorkbook(){[Name="ThirdRange"]}[Content]

Returns just this table:


It would be nice if we could get a list containing the cell values, rather than a table, for ranges like this…

Last thing to mention is that if you do want the value in a cell, rather than a table, you just need to right-click inside the cell in the Query Editor and select Drill Down:


This returns the value (in this case the text “Third Range Cell 1”) in the cell you clicked on:


This is a much more useful value to return than a table containing a single row/column, if you intend to use a value from a single cell in a named range as a parameter to another query.

You can download the sample workbook for this post here.

25 thoughts on “Working With Excel Named Ranges In Power Query

    • Bob, thanks for the info.
      There is something strange and I’am not sure what’s the problem.
      Could you try with this file:
      It has a VeryHid sheet named Baza, and a table T_Baza on it.

      Also it is .xls file, but i think thet the format is not the problem.

      • Hmm… it’s annoying to not be able to respond to my own comment.

        If you’re using Excel.Workbook, then the format matters. If you’re using Excel.CurrentWorkbook, then the version of Excel could matter. That’s because we use the Excel Automation API to read values for Excel.CurrentWorkbook, but either the ACE OLEDB provider or the OpenXML libraries to read the workbook for Excel.Workbook. So… which are you doing?

      • Than the format does not matter?

        I used this M code:
        Source = Excel.Workbook(Web.Contents(“”)),
        Lookup = Source{[Name=”T_Baza”]}[Data]

        and got an error:
        Expression.Error: The key did not match any rows in the table.

        But it looks it’s just me :(

      • Actually, it *is* the format that’s the problem. With .xls files, we’re limited to what the ACE OLEDB driver can tell us about the contents of the file. With OpenXML, we have a lot more leeway to extract the things we’re interested in.

      • Regardless of the location of the file (local or web) I can only see the sheets and named ranges from visible sheets.

      • No, it’s not just you. I was trying with a local workbook as Chris had shown. When I look across the web as you have just shown, I get the same error.

        Can you access any of the tables in that workbook?

  1. I didn’t even know that named ranges could be made of non-contiguous cells! I suppose they are not used often – and, in any case, I don’t know how you should import them (just as a table of one column with one row per cell?).
    That said, it’s amazing how Power Query make it simple to do operations that required many different approaches in other tools. For example, importing Excel in Power Pivot directly does not support tables at all, only named ranges and entire worksheets can be selected!
    Long life to Power Query!

  2. Oops. I thought we were filtering out non-contiguous ranges. I hope no one takes a dependency on their presence and/or current values as I suspect one of these needs to change.

    Can someone make a good case for why these should be usable from PQ (other than “just because” :)?

    • Oh, I see — Excel.Workbook filters these out but Excel.CurrentWorkbook does not. Hmm… we’ll have to try to normalize these.

      • To be honest, whilst I knew that you could, I cannot recall ever using a non-contiguous named range in Excel. I use non-contiguous ranges in VBA frequently, but then by setting a range object. I guess it’s one of those ‘good to know in case you ever need it’ things. Good to be able to use named ranges in PQ, but I cannot see why PQ should support non-contiguous named ranges, I would rather see PP being able to access tables in an Excel workbook.

  3. I tried using this post today and found something interesting. I used Excel.CurrentWorkbook() and could not find a named range that I know is there. So I tried a Power Query from a new, separate workbook back to my original file, and the range name is available to query from PQ.

    After playing around with this, I found that Excel.CurrentWorkbook() does not recognize a named range when it intersects with a table.

    Has anyone experienced this yet? (Excel 2010, July ’14 release of PQ)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s