Excel: Out With the Old, In With the New

Overview

The vast majority of heavy Excel users are still working in it like it’s 20 years ago.  If you haven’t noticed, a LOT has changed since then!  From structuring data, building formulas, and even automation options, there are several new approaches that will help you save time, reduce errors, and keep up with best practices.

In this article, we’ll look at ten new approaches you should be using right now, as well as the old approaches they replace.  I’ll also share a few custom tools that have helped me apply these new approaches with shortcuts to save even more time.  Make sure to use the box below to download the example workbook showing all these approaches in action!

The 10 Approaches

#1 – Old: Nested IF Functions | New: IFS Function

The IF function is one of the workhorses of powerful Excel workbooks.  The IF function lets you embed all kinds of logic all throughout your workbook.  For the longest time, to cover several different scenarios, you had to nest the IF statements, making them tricky to read, tough to write, and ultimately, quite error prone.

The newer IFS function makes it much easier to read, write, and troubleshoot.  Check out the screenshot that contrasts them below.  It may not seem like a huge difference, but eliminating all the extra parentheses makes it much easier to use.  Another more modern approach is using the SWITCH function – see the example workbook for the comparable example to the IFS statement.

Here’s a screenshot of IFS function in action.

#2 – Old: VLOOKUP Function | New: XLOOKUP Function

Back in the day, if you could quickly write a formula using the VLOOKUP function, you were a fairly advanced Excel user.  But VLOOKUP has limitations.  It’s also now considered table stakes Excel knowledge.  These days, while you can still use VLOOKUP, the newer XLOOKUP function is much more recommended.

It can look left and right, or up and down.  It can spill results natively (see new approach #3 below for spilling formulas!).  It lets you handle unmatched results directly.  And for a lot of people, the formula syntax is just easier to understand.

See below for a neat trick I’ve been using to quickly convert legacy VLOOKUP-based formulas to XLOOKUP!

Here’s a screenshot that compares the syntax of the older VLOOKUP function with the newer XLOOKUP function.

#3 – Old: Manual Data Refreshing  | New: Using Power Query

Updating source data is one of the most painful time-consuming and error-prone parts of most Excel-based processes.  Experts frequently cite that it accounts for 70-80% of the effort it takes to complete repetitive Excel processes!  Traditionally, teams would go find the data source, run a report, download it, copy and paste the data, remove/add some columns or rows, and all sorts of other enhancement steps.

In 2013, Microsoft released an add-in with the first version of Power Query, and in 2016 it was embedded in the Data > Get & Transform section of the ribbon menu.  It allows users to get data from a variety of sources (Excel, CSV, PDF, images, websites, databases), apply a variety of enhancement steps, and load data directly to Excel tables.  It is extremely flexible and provides a robust step-by-step audit trail.

Here’s a screenshot that compares the traditional steps required to refresh a data source with the Power Query 1-step refresh approach, along with an example data set.

#4 – Old: Manually-Filled Formulas  | New: Automatically-Spilling Formulas

Formulas are the backbone of Excel.  And yet they are the cause of most Excel errors.  One of the reasons is because people often forget to update them as data sources change in size.  Traditionally, that meant filling formulas to account for new rows, returning formula results one cell at a time.

In late 2019, Microsoft released the first batch of dynamic array functions (aka DAFs) that can automatically spill results across multiple cells down, right, or both.  A huge benefit of this is that you’re not just automating the calculations, you’re also automating the structure of your Excel files.  Calculations are automatically expanded or contracted to accomodate data source updates.  They also allow you to build some amazingly powerful calculations that you otherwise just couldn’t do with formulas.

Learn more about DAFs here or grab a template with examples of all of them in a comprehensive guide here.

Here’s a screenshot that compares the traditional formula approach (one-cell at a time) with the newer DAF spilling approach.

#5 – Old: User-Defined Functions with VBA  | New: Custom Functions with LAMBDA

For decades, the only way for Excel users to make custom functions was to use VBA, which required saving as a macro-enabled file.  While there are still reasons you might want to use user-defined functions (aka UDFs), in most cases, you can now create the same custom function without VBA using the unique LAMBDA function.

LAMBDA lets you build your own custom functions – complete with required/optional parameters – without needing VBA or macros.  This helps simplify and share complex formulas while making them easy to maintain in one place.  To define your own custom function using LAMBDA, go to the Formulas ribbon tab and click the Name Manager.  Give it a name, a definition that starts with “=LAMBDA(” and then supply the parameters and calculations.

Key tip: Keep a sharable library of your LAMBDA custom functions that you can reuse across your workbooks (see the macros section below for how I do this super-quickly!).

Learn more about the LAMBDA function here, or grab a LAMBDA library template with 80+ custom functions you can use here.

Here’s a screenshot that compares the traditional UDF custom function approach (VBA) with the newer LAMBDA custom function approach.

#6 – Old: Individual Cell Formats  | New: Cell Styles

Chances are you spend more time formatting cells than you’d like, both when you initially set them up and then later when you make updates.  Excel offers several formatting options – fonts, colors, borders, number formats, and beyond – and each can take clicking several tiny buttons.  Instead of setting formats one type at a time, consider using cell styles.

Cell styles offer three significant benefits over standard formats.  First, they allow you to set several formats at one time.  Second, if you make changes the the underlying style formats, they are reflected all throughout your workbook.  Compare that to updating one sheet and one cell at a time!  And third, you can even name them, effectively saving your favorites, making them clear and consistent.

To use cell styles, simply go to the Home ribbon tab and click the Cell Styles button to expose the native and custom cell styles available.  Hover over one of them to see a preview applied to any cells you’ve selected.

Learn more about cell styles here.

Below is a screenshot example showing some common native cell styles.  You can’t tell by the screenshot whether standard formats or cell styles are used, but you can in the example workbook.

#7 – Old: Unstructured Ranges | New: Tables

Behind most great Excel files is source data, structured in a tabular format.  A tabular structure makes it easy to apply formulas, filter/sort, apply PivotTables, etc.  A tabular structure means columns that represent fields or attributes, and rows that represent records or transactions, just like a database table.  While you can use a tabular structure without a formal table, tables are an underused gem.

Tables offer several unique benefits.  First, they offer a more contextual referencing approach, using the table and column names instead of the column letter/row number approach.  Second, the underlying table range automatically expands to accommodate new rows.  Third, any formulas throughout the table can automatically fill to the new rows.  And lastly, formatting can be automatically applied to different table parts – headers, totals, odd/even rows/columns, etc.

To insert a table or convert a range to a table, go to the Insert ribbon menu and click click table.  You’ll also want to rename your table to something that makes sense (most experts start with “tbl” at the beginning).

Below is a screenshot example that compares an unformatted, unstructured range with a table (with default styling).  Remember that tables are much more powerful than how they look!

#8 – Old: Text/Font-Based Indicators | New: Checkboxes (Visual Indicators)

For the longest time, Excel users have leveraged several approaches to represent values of True/False, Yes/No, On/Off, etc.  I’ve seen symbols and creative fonts, form controls, and even inserted images.  They may look okay, but they don’t play nicely with formulas and they tend to break when there are workbook updates.

The Microsoft team introduced in-cell checkboxes in 2024, and they’ve quickly become a really popular feature.  They are simple, easy to use (insert and check/uncheck), they can be formatted, and the underlying value represents TRUE or FALSE, making them very formula-friendly.

To insert one or more checkboxes, simply select the target cells and go to the Insert ribbon menu, and click Checkbox.  If desired, format them just like you would with other cell values (typically it’s color and alignment).

Below is a screenshot that shows examples of the symbol/font-based approach and modern checkboxes.

#9 – Old: VBA Macros | New: Office Scripts

For over three decades, VBA (Visual Basic for Applications) has been the automation workhorse for Excel.  If there is a painful, manual process that flows through Excel, VBA can probably automate it.  It’s still a really valuable programming language and for many cases, it’s the only way to automate processes.  However, it’s a dated language and it’s desktop-specific.

Microsoft initially released Office Scripts for the web version of Excel in 2020 as a modern way to offer automation and add-ins that would be cross-platform friendly and be capable of running in the cloud.  While there are some capability gaps between the two languages, Office Scripts can tackle most of the common use cases that VBA has traditionally handled.  There are still scenarios where using both of them within the same workbook/process is still perfectly acceptable.

Below is a screenshot example comparing the code for VBA with Office Scripts for a basic automation task: inserting and renaming a handful of sheets.

#10 – Old: Google Searching/Manual Effort | New: Copilot Natural Language Searching/Automation

I first learned some of the deep Excel features starting in 2002, and as you can imagine, I spent a lot of time searching on Google for the right formula to use or the exact VBA code syntax I needed to use.  I’ve spent countless hours building out workbooks, applying formulas, writing complex formulas, and coding macros.  That was valuable learning.  However, we’re now in the AI age, and we can do all of those things so much faster.

My go-to AI tool for several months has been Copilot for a few reasons – it’s included with my Office 365 plan, it’s built right into Excel (and other Office apps!), and if anyone knows how to deeply embed AI into the core architecture of Excel, it’s the Microsoft team (don’t bet against them!).

There are several key uses cases for which Copilot has dramatically sped up my work:

• Better understanding functions and tools
• Building items like custom functions and macros
• Building out the starting structure of your workbook
• Analyzing data for trends and insights
• Finding potential errors or other issues

Depending on your Excel/Office license, you may or may not have access to Copilot directly within Excel.  You can also use other AI tools like Claude, ChatGPT, Gemini, etc. by installing add-ins or by providing the Excel file as an attachment and asking for insights and analysis.

Below is a screenshot example that contrasts the traditional steps that most users took before Copilot was available, and the modern approach when integrating Copilot into your workflows.

Macros that help

To make some of these new tools even easier to use, I’ve built several shortcuts and other tools to use them quickly and easily.  Below are just some of the many tools available in the XLEV8 Excel Add-in.  Check these out in the support pages referenced below, or see them in action in the video below:

• Toggle Lookup – this will toggle the lookup formula parts between VLOOKUP > XLOOKUP > INDEX/MATCH.  This helps you learn the syntax between them as well as quickly update older files to take a newer approach!
• Search Formulas – this custom search box allows you to save and search through your favorite formulas using an auto-complete search box, saving a lot of time in reusing formulas.
• Search Lambda Formulas – this custom search box lets you search for and inject LAMBDA functions from your central library with just a couple of keystrokes.
• Global Search – this custom search box also lets you search for and inject LAMBDA functions from your central library with just a couple of keystrokes.  It also surfaces other items – recent files, sheets, named ranges, macros, and keyboard shortcuts.
• Cell Style Picker – this helps you quickly apply your favorite cell styles in a shortcut-driven picklist.  For even more automation, leverage the Bulk Edit Cell Styles macro to see all your styles in an Excel sheet where you can add/delete/update/rename them.
• Insert Picker – this helps you quickly insert items like checkboxes and tables in a shortcut-driven picklist.

Video

Summary

If you haven’t changed your approach with Excel for several years, you’re missing out on some powerful, time-saving features.  Aside from saving time, you’ll add valuable controls that reduce the risk of errors, and really, they’re just more fun to work with.  These are the tools and approaches that are being taught to beginners and college students – they are worth learning to be a great collaborator.

To modernize your Excel skills and take advantage of some of these new approaches and tools, make sure to download the example workbook using the box above.  There are plenty of items you can learn from or easily copy and paste to your key workbooks.

What modern approaches are you taking that others should know about?  Let us know in the comments below!