Excel Skills Every Data Analyst Needs

The Excel skills for data analyst roles cover the whole analysis: cleaning messy data, joining tables with XLOOKUP, pulling subsets with FILTER, summarizing with COUNTIFS, SUMIFS and pivot tables, and checking the result. This guide is for people applying to analyst, reporting or business intelligence roles who want to know which skills to sharpen and how each one shows up in a case study.

Which Excel skills for data analyst roles matter most#

Analysts are judged on whether their answer is right and whether someone else can trust it. So the most useful skills are the ones that keep an analysis correct as data changes.

Analyst interviews often test these stages with a case study: a dataset and a few business questions, such as "Which area has the best-value listings?" or "What share of patients were readmitted?" The questions rarely name a function. You choose the tool, build the answer from cell references so it updates when an input changes, and sometimes explain what the number means. That last part matters: an average of ratios is not the same as a ratio of totals, and a good analyst says which one they used and why.

Cleaning data before you analyze it#

Most analysis errors start with dirty data, so cleaning comes first. Common problems and fixes:

• Extra spaces stop lookups matching. =TRIM(A2) removes spaces at the start and end, and turns double spaces in the middle into single ones.
• Inconsistent case, like "east" and "East". COUNTIFS ignores case, but reports look messy; =PROPER(A2) tidies names.
• Numbers stored as text are skipped by SUM. If =COUNT(C2:C100) is lower than =COUNTA(C2:C100), some values are text. =VALUE(C2) converts one.
• Dates as text won't sort or subtract properly. Rebuild them with DATE(year, month, day).

Joining tables with XLOOKUP#

XLOOKUP finds a value in one column and returns the matching value from another, in any direction. With SKUs in B2:B9 and product names in A2:A9:

=XLOOKUP(G2,B2:B9,A2:A9,"Not found")

It searches for the SKU in G2 and returns the product name, even though names are to the left. The 4th argument shows "Not found" instead of #N/A. XLOOKUP needs Excel 2021 or Microsoft 365; in older versions use =INDEX(A2:A9,MATCH(G2,B2:B9,0)). See the complete XLOOKUP guide, the XLOOKUP function reference and how to look up to the left in Excel.

Pulling subsets with FILTER, UNIQUE and SORT#

Dynamic array functions return several results that spill into the empty cells below, and they update when the data changes. They need Excel 2021 or Microsoft 365. With reps in A, regions in B and amounts in C:

=FILTER(A2:C8,(B2:B8="East")*(C2:C8>=10000))

This returns three rows: Ava, Olivia and Lucas. Each test makes a column of TRUE and FALSE, and multiplying them keeps a row only when both are TRUE. Use + instead of * for "either". AND doesn't work here because it returns one value for the whole column. Add a third argument such as "None" to show text when nothing matches; otherwise FILTER returns #CALC!.

=SORT(UNIQUE(B2:B8)) lists each region once, A to Z: East, South, West. The FILTER function guide covers more conditions.

Summarizing with COUNTIFS, SUMIFS and pivot tables#

Analysts summarize two ways, and good ones know when to use each.

• Pivot tables are fastest for exploring. Drag Region to Rows and Amount to Values, then add a second row field, a report filter or Show Values As > % of Grand Total. Remember to refresh after the data changes, and to extend the source range if new rows fall outside it.
• Formulas are best for a fixed, always-current number on a dashboard: =SUMIFS(C2:C8,B2:B8,"East",C2:C8,">=10000") returns 41400 and never needs a refresh.

A neat pattern combines both: =SORT(UNIQUE(B2:B8)) for the labels, then =SUMIFS($C$2:$C$8,$B$2:$B$8,E2) next to each label and filled down. That gives a pivot-style summary that updates on its own.

Checking an analysis before you share it#

Checking is part of the job, not an extra. Build at least one check into every summary:

• Totals reconcile: =SUM(F2:F4)=SUM(C2:C8) is TRUE when a by-region summary adds back to the data.
• Counts reconcile: the counts per group should add up to =COUNTA(A2:A8).
• Look at the outliers: sort by the value column and read the top and bottom rows. Odd values are often data errors.

How Excel fits with other analyst tools#

Excel is one tool among several for analysts. Job ads for analyst roles often also list SQL for pulling data, and some list a BI or visualization tool. The thinking you practise in Excel, such as conditions, joins, grouping and checking, carries over directly. On a resume, name the specific Excel skills you can show; the guide to showing Excel skills on your resume honestly explains how. Analyst skills also overlap with Excel for sales and operations and Excel for HR and recruiting.

Practise it#

Join tables in XLOOKUP, pull matching rows in FILTER: pull matching rows with a formula, and go beyond the basics in Pivot tables 2 with nested rows, report filters, % of total and SUMIFS. The exercise below is the FILTER example from this guide.