Excel Formulas Every IT Person Should Know

Nobody in IT sets out to become an Excel person. Then you export a list of users from Active Directory, another from your asset system, and someone asks which machines have no owner, and suddenly you are one.

This is not a general Excel tutorial. It is the specific set of formulas that come up over and over when you are reconciling exports, cleaning up messy CSVs and answering questions about your estate. Twelve of them, with the traps that catch people out.

1. XLOOKUP. The one that replaces VLOOKUP

If your Excel has it, use it. It takes a lookup value, the column to search, and the column to return, in that order. It does not care about column position, it does not need a column index number, and it has a built-in not-found result.

=XLOOKUP(A2, Assets!B:B, Assets!D:D, "Not found")

The fourth argument is the part people skip and then regret. Without it you get #N/A scattered through the sheet, which then breaks every count and sum downstream.

2. COUNTIF. What is in this list but not that one

This is the workhorse of reconciliation. Put your first export in one sheet, the second in another, and ask how many times each value appears in the other list. Zero means it is missing.

=COUNTIF(SecondList!A:A, A2)

Filter on the result being 0 and you have your answer in about thirty seconds. It is faster and easier to explain than a lookup, because the output is a number rather than an error value.

3. TRIM and CLEAN. Fix the invisible problem

When a lookup that should obviously match returns nothing, trailing whitespace is the culprit far more often than anything else. Exports from web consoles are particularly bad for this, and non-printing characters sneak in from PDF copies.

=TRIM(CLEAN(A2))

Run it on both sides before you conclude the data is wrong. It has saved more time than any other formula on this list.

4. TEXTBEFORE and TEXTAFTER, for distinguished names

Active Directory exports are full of strings like CN=Jane Smith,OU=Finance,DC=corp,DC=local. Getting the OU out used to mean nested MID and FIND. Now it is one function.

=TEXTAFTER(TEXTBEFORE(A2, ",DC="), "OU=")

=TEXTBEFORE(A2, "@")     Email address to username
=TEXTAFTER(A2, "@")      Email address to domain

5. TEXTSPLIT, one cell into many columns

Give it a delimiter and it spills across columns automatically. Ideal for splitting a comma-separated group membership field or a semicolon-delimited list of installed software.

=TEXTSPLIT(A2, ",")

6. UNIQUE and SORT. Deduplicate without a pivot table

Two functions that removed a whole category of manual work. UNIQUE spills a deduplicated list, SORT orders it, and both update automatically when the source changes.

=SORT(UNIQUE(A2:A5000))

7. FILTER. A query language hiding in a spreadsheet

This is the one that makes Excel genuinely useful for estate reporting. Return every row that meets a condition, as a live spilled range.

=FILTER(A2:E5000, (D2:D5000="Windows 10")*(E2:E5000<>"Retired"), "None")

Multiply conditions together for AND, add them for OR. It reads oddly the first time and then becomes second nature.

8. IFERROR, but use it deliberately

Wrapping everything in IFERROR is tempting and slightly dangerous, because it hides genuine mistakes as well as expected ones. Use it where an error is a legitimate outcome. A lookup that may not match, and not as a blanket.

=IFERROR(XLOOKUP(A2, Assets!B:B, Assets!D:D), "Unassigned")

9. TEXT and DATEVALUE. The date column that will not sort

Exports frequently produce dates that Excel treats as text, which is why the column sorts alphabetically and every date filter looks broken. Convert once, properly, rather than fighting it row by row.

=DATEVALUE(LEFT(A2, 10))
=TEXT(A2, "yyyy-mm-dd")

Sorting yyyy-mm-dd as text still gives correct chronological order, which is handy when you have to hand the file to a system that only accepts strings.

10. CONCAT and TEXTJOIN. Build commands from data

Turning a spreadsheet of machine names into a list of PowerShell commands is a five-second job and saves a lot of typing.

=TEXTJOIN(",", TRUE, A2:A50)

="Remove-ADGroupMember -Identity " & B2 & " -Members " & A2 & " -Confirm:$false"

Always eyeball the generated commands before running them, and never generate anything destructive this way without a -WhatIf pass first.

11. SUMPRODUCT. Counting with more than one condition

Still the most flexible way to count rows meeting several criteria, and it works in every version of Excel including ones too old for COUNTIFS.

=SUMPRODUCT((B2:B5000="Finance")*(C2:C5000="Windows 11"))

12. LEN. Find the row that breaks the import

When a bulk import fails on one record, comparing string lengths finds the outlier fast. A username field that should be under 20 characters and contains 74 is your problem row.

=LEN(TRIM(A2))

Which of these you actually need

Glossary

Frequently asked questions

Do I still need VLOOKUP?

Only if you are on an older version of Excel or sharing files with people who are. XLOOKUP is better in every way, but it will show as an error for anyone on Excel 2019 or earlier.

Why does my lookup return Not found when the value is clearly there?

Nine times out of ten it is trailing whitespace or a non-printing character. Wrap both sides in TRIM(CLEAN(...)) and try again. The remaining cases are usually a number stored as text on one side.

Is it safe to reference whole columns like A:A?

On modern Excel, yes. The old advice to avoid it dates from a time when it forced a full recalculation of a million rows. Today the calculation engine only evaluates the used range.

How do I stop a spilled formula overwriting my data?

It will not. It refuses to spill and shows a #SPILL! error instead. Clear the cells in the way and it resolves itself.

Can I do all of this in Power Query instead?

For anything you will repeat, yes, and you probably should. Formulas are quicker for a one-off answer; Power Query is better when the same messy export lands every month.

Disclosure: this post may contain affiliate links. If you buy through one of them, we may earn a small commission at no extra cost to you. We only recommend products we’ve tested or genuinely rate.