See, and play with the Excel data when PowerQuery editor is open

If you have started using PowerQuery (and you must), you may have come across the issue of not being able to look at the Excel worksheet while in the PowerQuery Editor or to look at two different PowerQuery’s at the same time. Below we show you how to work with the Excel data when PowerQuery editor is open.

YouTube Work with Excel while PowerQuery is open

Watch, like, subscribe

See 2 separate PowerQuery’s at the same time

We find that sometimes we just want to look at a piece of data, or check the spelling of a field name (upper/ lower case becomes important). Perhaps we need to copy some code from a different PowerQuery and want to see it in context without having NotePad as the inbetween step. So how can you see, and play with the Excel data when PowerQuery editor is open as shown below.

play with the Excel data when PowerQuery editor is open

Open a second instance of MS Excel

Opening a second instance of Excel is normally a bad idea, done accidentally. It causes confusion and does stupid things like not allowing you to link spreadsheets and copy formulas across  workbooks.

But in this case it is exactly what we want. We need Excel to open up and not know that PowerQuery is being used. In many cases the file we want to see is the exact file we are working with in the other instance of Excel. All we want to do is look at it or perhaps copy some code across, no changes!

It is important to note that this is a little dangerous so make sure you open it as read only and accept that you may get some warning messages (as the file is open the other instance of Excel may complain that it can’t access a file).

In previous versions of Excel, you could just click on the Excel icon and it would open the 2nd instance (which is why it happened accidentally). But with the new way Excel 2013, Excel 2016, and Office365 handles windows you need to do it slightly different.

2nd Instance of Excel in Excel 2013, 2016 and Office 365

The easiest way we have found is to open Excel while holding the ALT key down. The key is to hold the ALT key down until you get the message  “Do you want to start a new instance of Excel?”.

So the steps are:

  1. Hold your ALT key down, right click on your Excel icon (1 below), and
  2. click on Excel 2016 (2 below) (still holding the ALT key down and wait)
  3. Only when you see the message “Do you want to start a new instance of Excel?” (as shown below) do you let go of the ALT key and click Yes

A new instance of Excel will open up and the Excels’ will ignore each other. As mentioned previously this has some dangers and can be a bit confusing (we use 2 monitors to clearly separate what we are looking at) but it is easier than opening, closing, copy pasting and trying to remember exact syntax because PowerQuery is VERY particular about fields used.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Power Query Training Course

Power Query close and load to greyed out

Why to learn PowerQuery NOT PowerBI