David Benaim is an Excel & PowerPoint vlogger, trainer and comedian. He recently shared his top Hacks for Working with Spreadsheets with the Apolitical network.

When most think of an advanced Excel user, they consider VLOOKUPs, PivotTables, Text To Columns etc. but all of those have actually been replaced with enhanced features this decade, and most Excel users have no idea. Plus of course we couldn’t be in 2026 without talking AI. There is so much innovation happening in Excel,so follow me as I take time away from preparing my Edinburgh Fringe Excel stand-up comedy show as we embark on a listicle in written and video format.

1. XLOOKUP

Never learned VLOOKUP – don’t. Use =VLOOKUP regularly? Stop. It’s useful but heavily flawed. VLOOKUP has been the third most used function in Excel for 40 years, but in 2020, XLOOKUP launched with the inputs =XLOOKUP(Value to lookup, column to look up in, column with value to return, [value if not found OPTIONAL], [two rarely used OPTIONAL inputs]).

Excel tips 1

VLOOKUP veterans will know the pain of the formula breaking when you insert new columns or how it can never look backwards. XLOOKUP fixes those and many other issues. This video compares the two.

2. =TRANSLATE via AI

If you work with spreadsheets in different languages, =TRANSLATE(cell with text to translate) can convert to the default language on your computer. Because it’s in a formula, you can drag it down to have it work across thousands of lines in seconds. The translate function has two other inputs, the second I recommend leaving blank, but the final optional one is useful =TRANSLATE(text to translate, ,[OPTIONAL target language]). More in this video.

Translate excel

3. Currency conversion live

Type a currency pair in a cell e.g. EUR/USD or GBP/CAD and click on the Data Tab > Data Types > Currencies. This will then show you the current rate updated every 5 minutes. Click on it then the + next to Price will output the price into a cell. More here.

4. Table of Contents

Click View > Navigation Pane to see all worksheets on the right, you can even drill down to Tables, PivotTables or images in each worksheet. Watch it here.

Table of contents excelable of contents excel

5. Formulas can now return multiple cells

This game changing enhancement dropped alongside a slew of new functions including =UNIQUE(cell range) which returns only the unique values, =SORT and =FILTER which do similar things to the actions on the Data tab but via output formulas. When you click a cell with the formula output, blue outline appears around the output range, but you can only edit the formula from the top leftmost cell. Check it out here.

Formula excel

6. Split first & last name with =TEXTSPLIT

A really useful function can split Mary Smith into a cell for Mary and another for Smith using =TEXTSPLIT(Cell, " "), space is the separator or delimiter and must be contained within quotation marks " ", just like any text in an Excel formula. As the result is shown in multiple cells with a blue outline when clicked on, this is a dynamic array as explained in the previous tip. Watch here.

7. =GROUPBY

PivotTables are useful for analysis, but they don’t update automatically and some users prefer formula-based solutions. =GROUPBY(Row field, Values field, SUM) will sum up the values, broken down by the category in the “Row fields.” The resulting blue outlined table is also a dynamic array. Find a demo here.

Groupby excel

8. Checkboxes

Click the Insert tab and you’ll see an option for checkboxes. That’s a quick one, and here's the video.

Cehckboxes excel

9. Copilot can edit your Excel

Click the Rainbow logo on the bottom right to launch Micosoft’s chat AI interface. More in the video here. My favourite prompts are:

  • “Clean this data”: Copilot will clean spelling errors, numbers stored as text, inconsistencies (e.g. a column with Male, Female, F will change to Male, Female, Female) and many more things.
  • “Create a dashboard”: Copilot will create a one summary with tables & charts.
  • “Look up the top 10 movies of the decade with release date, director, box office revenue etc.”

Note - you can also get Chat GPT and Claude add-ins inside your Excel for similar experiences if you prefer not to use Copilot.

Copilot excel

10. Python in Excel

Type =PY in the formula bar to launch Python mode, where you can write code which can return more advanced data visualisations, data scraping etc. Copilot or another AI tool can be useful to generate the code. Video here.

Python excel

That takes us to the end of the Excel-lent list. If you like to laugh about spreadsheets, I’ll be doing my Excel Comedy & Mathem-antics show at Edinburgh Fringe from 6-30 August plus a handful of other shows in Gothenburg, Tallinn & Dundee across September, and plans for Australia in 2027 Q1. Follow me on socials or see more show information here.