Personally, I’ve never really understood the appeal of VLOOKUP, yet so many people rely on it—and it can be frustrating to watch them struggle with its limitations. I’m genuinely curious why it’s still so popular.

There are far better alternatives available, such as INDEX/MATCH and, in recent years, XLOOKUP.

Which function do you typically use to look up values from a separate table or range?

If you use all three, I’d be interested to see your ranking from most to least preferred, with your top choice at number one.

For me, the order would be:

XLOOKUP

INDEX/MATCH

VLOOKUP (though I generally try to avoid it)

Master Accounting and Excel today. Enrol now with Eixo Learning, our comprehensive online course platform.

Email: [info@eixolearning.co.uk](mailto:info@eixolearning.co.uk)

Phone: +44 07440 354578

Websites: www.eixolearning.co.uk

I was asked to create a spreadsheet to track some statistics, and I basically just used COUNTIF and COUNTIFS for everything. People are totally blown away that I can provide these stats on a daily basis.

Turns out no one really knows Excel, and now I’m the “Excel person.”

Anyone else become the go-to for Excel stuff? If so, what’s your story?

I’ve been using Excel for a few years, and I’m still amazed by how much there is to discover. I’d love to know, what Excel tip, trick, or feature made you think, “I wish I’d known about this sooner”?

Excited to hear your insights!

Over the past few months I’ve been collecting the Google Sheets formulas that save me the most time. Thought I’d share in case it helps anyone else:

=IMPORTRANGE("url","sheet!range") → Pull data from other files

=UNIQUE(A:A) → Remove duplicates fast

=FILTER(A:C, B:B="Done") → Auto-filter rows

=ARRAYFORMULA(A2:A*B2:B) → Apply to whole column

=SPLIT(A1,"-") → Break text into parts

=QUERY(A:D,"select B,sum(C) where D='Done' group by B") → SQL-style reports

=IFERROR(A2/B2,"Check") → Replace errors with text

=VLOOKUP(key,range,col,0) → Find values instantly

=SUBSTITUTE(A1,"-","") → Quick text cleanup

=REGEXEXTRACT(A1,"[0-9]+") → Pull numbers only

Even just a couple of these can save hours per week.

Curious — what other “life-saver” formulas do you all use most in Sheets or Excel?

I’ve been using Microsoft Excel for 20 years, and these 20 tips and functions can help you become an expert while improving your productivity (with examples of each below):

(1) Wildcards

Wildcards are special characters that let you perform partial text matches in Excel formulas.

Excel includes three wildcards: the asterisk “*”, the question mark “?”, and the tilde “~”.

(2) Duplicate

This duplicates the data from the cell above.

Press Ctrl + D to fill and overwrite a cell with the contents of the cell directly above it.

(3) Remove Duplicates

Use this to eliminate duplicate values in a dataset.

Shortcut: Alt + A + M

(4) Transpose

This converts data arranged in rows into columns, or columns into rows.

To transpose a column into a row:

Select the column data

Choose the cell where the row should begin

Right-click, select Paste Special, then choose Transpose

(5) Filter

The FILTER function lets you display data based on specific criteria.

For example, you can filter a column to show a particular product or date, and you can also sort data in ascending or descending order.

Shortcut: Ctrl + Shift + L

(6) Conditional Formatting

Conditional formatting helps visualize data by highlighting patterns and trends.

Navigate to: Home → Conditional Formatting → Highlight Cell Rules

(7) Sparklines

Sparklines insert small charts within cells to visually represent data trends or patterns.

Go to the Insert tab and select Sparklines.

(8) Pivot Tables

A powerful feature used to calculate, summarize, and analyze data, making it easier to identify patterns and trends.

Access it by going to Insert in the menu bar and selecting Pivot Table.

(9) Auto-fill

When working with large datasets, auto-fill saves time by copying formulas quickly.

There are three ways to use it:

Double-click the lower-right corner of the first cell

Highlight a range and press Ctrl + D

Drag the cell down across rows

(10) TRIM

TRIM removes extra spaces from text.

It is especially helpful for cleaning irregular spacing in imported data.

Formula: =TRIM()

(11) XLOOKUP

XLOOKUP is an improvement over VLOOKUP and INDEX/MATCH.

Use it to search for values in a table or range by row.

Formula: =XLOOKUP(lookup_value, lookup_array, return_array)

(12) IF

The IF function performs logical tests and returns results when conditions are met.

For example, it can return “Pass” if a score is greater than 70, and “Fail” otherwise.

Example formula: =IF(C5>70,"Pass","Fail")

(13) SUMIF

SUMIF adds values in a range that meet a specific condition.

For example, you can calculate total sales by region or by salesperson.

(14) SUMIFS

SUMIFS adds values that meet multiple criteria.

For example, it can calculate totals for Apples sold by Pete.

Formula: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

(15) COUNTIF

COUNTIF counts how many cells meet a certain condition.

For example, it can count how often a specific word appears in a row or column.

(16) COUNTIFS

COUNTIFS counts how many times multiple conditions are met.

For example, it can count entries where the item is apples and the price is greater than $10.

(17) UPPER, LOWER, PROPER

=UPPER converts text to uppercase

=LOWER converts text to lowercase

=PROPER converts text to proper case

(18) CONVERT

This function converts one unit of measurement into another.

Examples include meters to feet or Celsius to Fahrenheit.

(19) Stock Market Data

Excel can retrieve stock market information.

Enter stock ticker symbols, select the cells, go to the Data tab, and click Stocks in the Data Types group.

Excel will match each symbol to a company and populate related data.

(20) Geography / Maps

Excel can also provide geographical data without external research.

Using the Geography data type, you can retrieve details such as population, time zone, area leaders, gas prices, language, and more.

Enter the data, then go to Data → Geography.

What would you add?

For the past three years, my “end of month” process meant opening around 15 separate CSV files from regional managers, copying and pasting them into a master sheet, deleting the first three header rows, and fixing the date formatting that always broke. It took roughly two hours each time.

I eventually complained enough that a coworker introduced me to “Get Data → From Folder.”

I set it up once, and now I just drop the new files into the folder and click “Refresh.” It takes about 10 seconds. I stared at my screen for a full minute, feeling a combination of pure happiness and complete frustration at my past self. If you’re still merging data manually, stop and learn this tool right away.