Create a List of Sheet Names using Power Query... With 2 ways Navigation

When you have a workbook with lots of worksheets it's very useful to create an Index of All Sheet Names and be able to Navigate to any of these worksheets and back to the Index.
There are 3 methods for doing this:
Either by Using Functions
Or By using a VBA code
Or By Using Power Query

In this tutorial I am using Power Query

You can download the Start file and follow along by clicking on the link here below:

To watch the tutorial in which I use Functions Click on the Link:

To watch the tutorial in which I use a VBA Code Click on the Link:

If you find value in this tutorial give it a thumb up, and hit the “Subscribe” button to be notified when new videos are posted

[zuhairalmutawa]

Excellent tutorial, Thank you Mr. Nabil.

[Samu-gzqj]

Finally, I landed a right place to learn excel tricks! Thank you❤❤❤

[manishpanwar]

No one cud explain this before in such manner. Wonderful 🤝🤝

[vincysigma]

Waw..Very nice trick. This helped me a lot.. Thank you Sir.

[k.b.jayakumar]

Hats off Sir. Excellent presentation with very informative content. Thank you very much indeed!

[KhalilYasser]

That's awesome Nabil. In my opinion this is better than using formulas or VBA. Easy and quick steps.
Best Regards

[erikguzik]

ok, well I have Office 365, not sure if there have been changes or not, I don't use PQ that often and not familiar with all the ins and outs of it... When I try to do the first step, go to PQ, you now select Get Data, From File, From XL workbook. I select the file name..."Index of Sheet Names with Power Query.xlsx". Then Press IMPORT. I get an error saying "The process cannot access the file {file name} because it is being used by another process" it's like it won't work on open workbooks

[rajiviyer]

Thanks a lot for the wonderful explanation Nabil. I have 4 questions:
1) Does Power Query not work on .xlsb extension?
2) How to ensure the Index created with file names update if I add or delete sheets? It's not happening in my file even after I refresh the query.
3) How to ensure the order of the sheet names changes in the Index if I move around the sheets in the workbook?
4) Does this not work if I try to get data from workbook from a file saved in OneDrive? I get the error that the file is already in use.

[Luciano_mp]

Power Query much easier and practical, thanks for all methods.

[subhasisg]

Hi, Thanks for such a beautiful tutorial. I am in an additional need. Actually I have a workbook of 50 sheets. In each sheet I want to add a column at the beginning which is the name of the worksheet. Say Sheet 1(named IP1) is having details for an IP Address. In column A I want to add the IP Address (IP1) for all the rows in the sheet. Similarly in sheet IP2, I want to populate the A column with IP2. This way I want to do for all the Sheets with their respective sheet names. Note that Column A is currently not empty. Can you help?

[ahmedkhalifa]

أشكرك كثيرا على المعلومات الفيمة والممتعة في الوقت نفسه، الطريقة التي أعجبتني وأفضلها هي استعمال الصيغ ، ختى وإن كانت ظريقة Power Query أفضل وأسرع ،إلا أنه تبقى عندي طريقة الصيغ مفضلة عندي أكرر شكري والسلام

[patrickschardt]

I’m becoming a huge fan of power query. Normally I like formulas, but you have to use the macro function get workbook. That means I had to save the file as a macro enabled workbook. IT doesn’t always like that

[reng]

Hi Mr. Nabil I have a question for you that i have not been able to solved yet in Power Query do yuo have an email where to write it?

[SamehRSameh]

Plz record whole tutorial about power query, power pivot & power Bi

[ctdivers]

Yes Hats off. Looking forward to more excellent presentation and content. Power Query is much easier.
My tabs are dates in text format and I need to convert them into date format. How can I do that? 121020 need to convert to 12/10/20.

[LotfyKozman]

Thanks so much for using the magic PQ.
Please check your LinkedIn inbox.

[alexzz]

It works without single Name]]&"!A1", [@[Sheet Name]])