Excel at 40 Zine!

Excel is 40 this year, and I decided to mark the occasion by creating a zine.

Have you already got the zine?

You may have come directly to this page because you’ve got a copy in your hands. Hello and welcome! I’m an Excel and Power Query trainer and consultant, and I’m glad you’ve found my zine (and now my website). You can skip straight to the next section.

If you haven’t got a copy, then here’s a bit more information about it and how you can get one. It’s pictured above, it has ten “did you know” facts about Excel including killer features from the early days as well as some of the later formulas.

It’s designed to print on a sheet of A4 paper, and then as long as you fold it in the right way, you get a zine. So if you want a copy for yourself, just let me know and I’ll send you a PDF to print.

How did I choose which Excel tips to include?

This is not supposed to be a “top ten things you must know in Excel” type list. Instead, I tried to select a range of things so that there would be something for all levels of Excel user.

I was also somewhat limited by what I could fit into six pages that are the eighth of the size of a sheet of A4!

So much as I’d love to share with you (for example) how to conditionally format a graph, a zine isn’t the place for this.

However, most of the tips regularly feature in my training courses.

More information about the specific tips

1. Emojis

Yes, you can insert Emojis into your spreadsheets and formulas. Just because you can, doesn’t mean you should.

Actually, that’s not fair. You see, there are lots of “businessy” emojis, like checkmarks. ✔️

And it’s a lot easier to find them in the Emoji menu than searching through the “Insert character” menu used to be!

2. 3-dimensional formulas

This is some old functionality that I stopped using and have recently restarted. Here’s a more detailed post about how it works.

I had stopped using them when I discovered Power Query, which I generally prefer as a tool for combining data.

However, since Microsoft started releasing dynamic array formulas, I’ve come back to these bit, because you can use them within formulas like VSTACK to bring data together from multiple sheets.

You do have to be very careful with where your sheets are physically located in the workbooks, which is why I usually like to include a blank START and END sheet with the data for combining in between.

3. Double click that fill handle!

The “fill handle” is another piece of old functionality. The ability to drag a formula across or down was one of the early features that made Excel such a useful tool. However, a surprising number of people don’t know that if you double click on it, it fills the formula as far down as you have data.

Bonus tip; using a Table will mean your formula automatically copies down without any clicking at all.

4. New Text formulas

I find myself including bits about the new text formulas – TEXTBEFORE, TEXTAFTER, TEXTSPLIT and sometimes TEXTJOIN – in virtually all of my courses. They’re dead easy for beginners to understand, and more experienced users often haven’t come across them because they’re comparatively new.

If you haven’t come across them, here’s a nice introduction which features quite a fiddly scenario.

5. Focus Cell

Hopefully you’ll all have this by now – it started being released in 2024 so has rolled out to most editions. I like to add it to my Quick Access Toolbar so I can toggle it on and off.

6. Bar charts with conditional formatting

The feature in question is the “Data bars” part of Conditional Formatting.

Note that in the example pictured, there are a couple of steps to presenting it like this.

Firstly, you have to copy the data into another column. Then you apply the data bars. Then you go into Conditional Formatting > Manage Rules > Edit Rule and select “Show Bar Only” option.

7. UNIQUE

I love UNIQUE! Along with FILTER, it’s one of the first dynamic array formulas I teach people, and I use it loads in my consultancy solutions too.

It’s simple to operate, but opens up lots of possibilities. For example, as shown in this example, the list will automatically update when your data changes. You can also feed a list generated by UNIQUE into a data validation list.

So you can create a dropdown that will automatically update when new data is added.

8. ALT + =

There are loads and loads of keyboard shortcuts. It’s definitely worth learning a few, but don’t try to learn them all at once. Add one or two into your workflow each week, and focus on the things you do the most.

I reckon loads of us use Autosum a lot, and therefore this one will be fairly high up your list!

9. XLOOKUP

XLOOKUP can do so many things! If you haven’t yet made the switch from VLOOKUP, please do, you will not regret it.

Here’s a very comprehensive video showing how it works and then many ways you can extend it.

10. Power Query

It’s a bit of a cheat to include something so huge as a “quick tip” but as I keep meeting Excel enthusiasts who don’t know about it, I want to raise its profile in anyway I can.

I learned Power Query in 2017 and my reaction was – WHERE HAS THIS BEEN ALL MY LIFE???

I used it all the time, whether it’s for quick data cleansing jobs or creating management accounts models or automating processes such as calculating irrecoverable VAT adjustments. It’s not “advanced” – you can achieve a lot of things by pressing buttons, and it’s easy to correct mistakes if you don’t get it right first time.

If this sounds interesting, I run a half day Introduction to Power Query course in small groups online.

Enjoyed my Excel at 40 Zine? Stay in touch!

As I mentioned before, I’m an Excel and Power Query trainer. I specialise in creating bespoke in-person training for teams that is memorable and fun. I also do financial modelling and consultancy, often for small organisations and charities. You can contact me here.

You can also keep in touch via my quarterly newsletter, where I round up the latest developments in Excel and share articles and videos I’ve enjoyed watching. You can also connect with me on LinkedIn.