3 Excel formulas you’re still wasting too much time on

Over the years, Excel has improved consistently, yet many people remain tethered to legacy methods that require manual updates and complex workarounds, often because they have grown accustomed to using them. If you find yourself constantly re-filtering data, manually deleting duplicates, or wrestling with rigid lookup tables, you are likely relying on tools that have already been replaced by more efficient, dynamic alternatives.

By moving away from these older approaches, you can automate your Excel workflows and reduce the common errors that arise when tasks rely too heavily on manual input.

Related VLOOKUP Stop fighting column limits Screenshot by Yasir Mahmood

If you've been using Excel for a while, you've almost certainly used VLOOKUP. For decades, it served as the go-to formula for pulling data from one table into another, and for a long time, it was impressive. But VLOOKUP comes with several quirks that can easily mess up your spreadsheet.

The first issue is structural: your lookup value must always live in the leftmost column of your selected range. That might sound manageable until you need to find an employee's name based on an ID in cell A2 from a table in range B2:E7. VLOOKUP cannot perform that search because the ID (the lookup value) isn’t in the table's first column.

Then there's the column index number. Every time you use VLOOKUP, you have to manually count which column position holds the data you want. If you add or remove a column from your table, that number becomes incorrect, your formula breaks, and you end up debugging something that was working perfectly yesterday. On top of that, VLOOKUP defaults to an approximate match unless you remember to add FALSE at the end of your formula.

The modern fix is XLOOKUP. Rather than referencing an entire table or range and counting columns, XLOOKUP allows you to point directly to the column you're searching through (lookup_array) and the column or columns you want to return (return_array):

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

It separates the lookup array from the return array, so the result can appear to the left or right of the search column. If you want to find an employee's name and department at the same time, a single XLOOKUP formula can handle both, eliminating the need to write two separate lookups. XLOOKUP also defaults to an exact match, so one of the most common VLOOKUP mistakes is eliminated from the start.

Remove Duplicates Why repeat the same cleanup every day when one formula can handle it? Screenshot by Yasir Mahmood

Duplicate entries are a headache in any dataset, whether they're duplicate customer records or repeated order IDs; redundant entries can throw off your totals. Excel's built-in Remove Duplicates button has been the standard fix for years, and while it works, it permanently deletes rows from your spreadsheet.

That means every time your data changes—when you add new entries or someone pastes in a fresh export—you have to run the entire process again from scratch. You select your range, go to the Data tab -> Data Tools -> Remove Duplicates, pick your columns, and click OK, all over again. And because the tool deletes data, there is no easy way to reverse the action if you later realize you removed something you still needed.

The UNIQUE function offers a better fix:

=UNIQUE(array, [by_col], [exactly_once])

Instead of deleting anything, UNIQUE reads your source data and generates a live, automatically updated list of distinct values in a separate location. When a new name gets added to your original list, the UNIQUE output refreshes automatically. When a duplicate disappears from the source, the list adjusts to reflect the change. Nothing gets deleted, nothing needs to be re-run, and your clean list stays current without extra effort.

AutoFilter Skip the copy-paste routine Screenshot by Ada

AutoFilter is an incredibly useful tool for narrowing your data to a specific subset, whether that means viewing only East Region sales or focusing on pending invoices. However, it becomes less helpful when you need to share that filtered view, place it into a report, or keep it updated as your data grows.

The problem is that AutoFilter only hides rows; it doesn’t actually move or extract data. So, if you want a separate, report-ready list of all "Office Supply" transactions from the Asia region, you end up filtering the data, selecting the visible rows, copying them, switching to another sheet, and pasting. Every time your underlying data changes, you have to repeat the entire process from the beginning.

The FILTER function handles this automatically, which is why I use this function for everything now. You write a single formula, specify the data to evaluate and the conditions to apply, and it produces a live table that matches your criteria:

=FILTER(array, include, [if_empty])

When a new transaction appears in your source data, it shows up in the filtered output immediately. You can also layer conditions with ease by combining AND logic, OR logic, or a mix of both within the same formula.

For instance, to generate a live list of all "Vegetables" sales in the "Asia" region, you could use the following formula:

=FILTER(A2:N100, (C2:C100="Vegetables") * (A2:A100="Asia"), "No results found")

This formula creates a fresh table that updates automatically the moment you add a new sale to your main dataset.

Related How to Use the FILTER Function in Excel

Looking to streamline your data analysis in Excel? Learn how to effectively use the FILTER function to simplify your data and improve productivity.

Modern formulas mean fewer mistakes and less maintenance

Moving from manual tools to dynamic array functions like XLOOKUP, UNIQUE, and FILTER lets you build spreadsheets that maintain themselves rather than requiring your input every time something changes. Every hour you currently spend re-running filters, re-cleaning lists, and re-fixing broken lookups is time you could redirect toward meaningful analysis.

Excel has already done the hard work of building better tools. You only need to start using them.

View Original Article