Power Query Intellisense and Highlighting in Excel

I'm super excited that Power Query Intellisense and Highlighting features are finally here in Excel!  It's been a long time coming, with these features debuting in Power BI Desktop months ago.

Where do Intellisense and Highlighting Show up?

Let's look at all three places that Intellisense and Highlighting are now exposed in Excel's Power Query.

Intellisense and Highlighting in the Formula Bar

The first thing you should notice about the image below is that Excel now adds colour highlighting to the text in the formula bar.  "Text" values are shown in red, keywords in blue, and data types in green:

Intellisense and Highlighting in Excel's Power Query Formula Bar

In addition, you can see Intellisense showing in the picture above as well.  In this case the cursor is immediately after the opening parenthesis character.  Unlike in the past, we now get a nice syntax popup box that tells us what needs to come next in this function.

Intellisense in Custom Columns

This is the place where I really wanted to see this happen.  Here's a look at Intellisense working live as I started to build a column using a Text function:

Intellisense in Excel's Custom Column Dialog

Pretty sweet, as this actually starts to display the options.

(Now, to be fair, in my build of Excel, this feature only seems to consistently work if I rename the column first, then tab into the formula builder.  If I just click straight into the formula, bypassing the tab order, it doesn't seem to kick in.)

Syntax Highlighting in Custom Columns

How about syntax highlighting?  The formula here is only meant for demo purposes, but you get the idea of how it can look:

Syntax Highlighting in Excel's Custom Column Dialog

Blue keywords for if, then, else.  Hard coded values (like 3) show up in green like data types, and text is showing in red.

Intellisense and Highlighting in the Advanced Editor

And finally, Intellisense and highlighting work in the Advanced Editor as well.  Here's a look at the syntax highlighting:

Syntax Highlighting in Excel's Advanced Editor

And here's what happens when we start manually adding a new step, which kicks in the Intellisense in the Advanced Editor window:

Intellisense in Excel's Advanced Editor

How do you get these features?

First, you'll need to be on Office 365.  Sorry Excel 2016 and 2019, but my understanding is that these new features are only coming to subscription users.  (That's another reason that - in my opinion - you should never buy another 4 digit numbered release of Excel ever again.)

If you are on subscription, you get them with your regular updates.  This feature set hit the Insider build channel last month in version 1907.  It's currently also been deployed to those on the Monthly Targeted channel running version 1907, build 11901.20080 or newer.

If you're not on the Monthly Targeted channel, you'll need some patience, as it's coming.  Just keep checking those updates!

11 thoughts on “Power Query Intellisense and Highlighting in Excel

  1. @Ken what is also exciting is Dynamic array formulas are not part of the Monthly Channel update cycle

  2. True enough Sam! With regards to the channel, I believe dynamic arrays are being flighted slowly into the main builds now. Not sure when they'll be turned on for everyone, as Microsoft want to make sure they don't break things.

  3. "That's another reason that - in my opinion - you should never buy another 4 digit numbered release of Excel ever again."
    I think that is what MS wants but what do you do with clients that are simply not going to move to 365? Suddenly having to go back to working without PQ or DA is like losing a limb and the thought that the versions are unlikely to come together for three or more years fills me with horror.

  4. The intellisense in Excel is so slow to respond it is unusable for me. Has anyone else experienced this? Is there a way to improve it?

  5. Hi Kim,

    Unsure on this one. Can you provide some more details about your build of Excel, and maybe what kind of processor and RAM you have?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.