Five things I learned at the Global Excel Summit 2022

Last week I “went” to the Global Excel Summit. This is a three day event featuring many of the superstars of Excel, eg Leila Gharani, Chandoo, Wyn Hopkins, Oz du Soleil etc etc. It was virtual this year, but as I’d never been before I didn’t know what to expect anyway.

I learned way more than five things, of course, but here are my biggest takeaways.

1. I need to learn a lot more about Power Automate

As I’ve written on many occasions, Power Query is a complete gamechanger for everyone who uses Excel for more than a few hours a week. With Power Query, finance teams can automate lots of routine data cleansing, analysis and reporting tasks. Power Automate could be the rocket that takes this to the next level again, bringing the ability to design and or customise workflows outside of Excel. Eg: receive these attachments, save them into this Sharepoint file, run this Power Query on them to combine them, email the resulting report to everyone.

Potentially revolutionary. Potentially dangerous in the wrong hands. Certainly something that needs to be on all Finance Directors’ radars.

2. Tabular format in pivot tables

This is perhaps a small thing but is certainly very useful for me. If you are pivoting data and have a category and a sub category, Excel defaults to indenting the sub category under the category, like this:

This has always annoyed me and sometimes puts me off using a pivot table. For a start, if you want to use the data for another process, it doesn’t lend itself that well to it because both category and subcategory are in the same column. Also, I always get confused that the category 1 sub total is above the line items, not below them.

At the Global Excel Summit I learned that not only is there an alternative format (Tabular), but you can actually default your workbooks to use it by going to File / Options/Data.

There are other things you can change here too, for example Excel’s infuriating automatic grouping of dates in pivot tables.

The bigger point here is that if something frustrates you in Excel, have a poke around / quick google as perhaps it isn’t as set in stone as you might think.

(This was from Bill Jelen/MrExcel, who had many more pivot table tricks)

3. Maybe Power Pivot doesn’t suck, after all

Confession time; I’ve never really got on with Power Pivot. I think it’s probably because (a) it seems to crash on me when I try to use it (b) my data sets don’t tend to be that huge, so I can get round not using it with Power Query and ordinary pivot tables.

However, I saw a couple of sessions which made me think about this some more, and made me realise that on occasions I’m really pushing PQ to do something that would be more efficient in Power Pivot. The bigger learning point is that you don’t have to be an expert in everything but it’s very useful to know the key uses and selling points of different apps. You may not need them for what you’re currently doing, but it may be just the tool for a future role.

4. I like virtual conferences and I hope they stick around

There are obvious logistical, financial and carbon footprint advantages to attending a conference from your home. One of the great things about the Global Excel Summit was how Global it felt, with delegates from all over the world.

I also found it easier to concentrate on a lot of technical subject matter, because I was able to knit, stretch and/or fidgit without feeling self-conscious, and occasionally pause the stream to get a cuppa. (The sessions were generally organised to be 45 minutes long every hour, so there were breaks built in)

I even found networking OK – people had the option to fill in profiles and link to their social media profiles so it was fairly easy to get chatting to people in a channel of your choice. Similarly, if you didn’t want to talk to someone it was very easy to ignore them.

5. Real experts are always learning

There were so many “wow, I didn’t know you could do that” moments. What was lovely was that lots of these comments came from other presenters. It’s reassuring to know that people I learn from are still learning new things, and aren’t afraid to admit that.

Things I didn’t learn

Practical uses of LET and LAMBDA for finance teams. I know everyone is really excited about them but I thought it was interesting that people kept defaulting to the Pythagorus theorum example of constructing a LAMBDA, which is on the Microsoft support page. I’m sure it’s supercool and useful for some people but at the moment I’m struggling to think of when it might solve a common finance team problem. Still, I will continue to Watch This Space.