Use Power Query and Excel At The Same Time
Why Can’t I Use Excel While In The Power Query Editor?
For some insane reason, Microsoft decided it was necessary to shut off access to Excel while a user is working in the Power Query Editor. Often times it is vital to be able to reference your Excel files while building out your query rules and it is a hassle to have to close down the entire Power Query Editor just to view your Excel file.
I can remember first learning how to use Power Query and instantly thinking there was a bug with it because I could not go into my Excel file and reference it for the automation steps I was creating. Being accustomed to the Visual Basic Editor user experience, I could not comprehend why the Power Query Editor interaction with opened Excel files wouldn’t be similar.
Upon further research, I found that Microsoft did this by design and didn’t have plans to modify this (you can complain directly to Microsoft here as I did).
Luckily, there are a few different hacks to get around this annoyance. Let’s look at our options so you can pick the proper one for your needs and start interacting with the Power Query Editor and Excel at the same time!
Option #1: Open A Cloud Version
While the Power Query Editor locks up your entire Desktop version of Excel, it doesn’t impact your ability to open up the same file in Excel Online.
To do this, simply ensure your file is saved out in OneDrive or SharePoint. Then, simply open it up in your web browser or on Microsoft Teams.
This will allow you to make edits or view your Excel spreadsheet using the online version while you make Power Query edits on the desktop version.
Option #2: Use A Second Excel Instance
Another option you can pursue if you don’t want to deal with Excel online is to open a second instance of Microsoft Excel on the desktop. This will allow you to open up a copy of the same workbook (keyword “COPY”) or other Excel files that are not opened in your current/first instance of Excel.
In order to create a second instance of Microsoft Excel, you need to do the following:
- Right-Click on your Excel desktop icon
- Right-Click on the Excel menu item
- Select Run As Administrator
- Click Yes if a Windows warning dialog pops up
Once you have opened up a second instance of the Excel Application, you can proceed to open up Excel files using the Open dialog menu.
You can verify that you have successfully opened two instances of Excel by viewing the two instances in your Task Manager window.
You can view your Task Manager by doing the following:
- Use keyboard shortcut: Ctrl + Alt + Delete
- Select Task Manager from the list of option
In the Processes tab of the Task Manager, you should see two individual Microsoft Excel icons within the Apps section.
As you can see below, both the Power Query Editor and a copy of the same exact Excel file are accessible simultaneously. This allows you to reference your Excel file without the need to close the query editor.
I Hope This Helped!
Hopefully, I was able to explain how you view/interact with both the Power Query Editor and your Excel files at the same time. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.