12 Excel Skills Every Data Analyst Needs (With Examples)

The Excel skills a data analyst needs are the ones that turn a messy export into a clear answer: cleaning text and dates, lookups with XLOOKUP or INDEX/MATCH, conditional sums with SUMIFS and COUNTIFS, PivotTables, Power Query for repeatable cleaning, and simple charts. Add IF logic, tables, conditional formatting and basic data validation and you have the toolkit most analyst interviews in India test. You don't need macros or VBA to get hired.

I'm Gulab Chand Tejwani. I teach data analysis and Power BI at TechNet Consultancy in Vallabh Vidyanagar, Anand, and online, and I'm a Microsoft Certified: Power BI Data Analyst Associate and a Microsoft Certified Trainer. Every batch I teach starts with Excel, even for people who say they "already know it". Most of them know how to type into it. Fewer know how to analyse with it.

Here are the twelve skills I make sure every learner can do without searching online.

1. Turning a range into an Excel Table

Select your data and press Ctrl+T. That's it, and it changes everything afterwards. Tables expand automatically when you add rows, formulas fill down on their own, and you get readable references like =SUM(Sales[Amount]) instead of =SUM(D2:D4381).

When I open a learner's file and see a raw range with formulas pointing at row 4381, I know the report will break the day someone adds row 4382.

2. Cleaning text: TRIM, CLEAN, PROPER and Text to Columns

Data exported from billing software or a website form is usually dirty. Trailing spaces make "Anand " and "Anand" look like two cities. Names come in as "RAMESH PATEL", "ramesh patel" and "Ramesh Patel".

Useful functions:

• TRIM removes extra spaces
• CLEAN removes non-printing characters
• PROPER, UPPER, LOWER fix case
• Text to Columns (Data tab) splits "Patel, Ramesh" into two columns
• TEXTSPLIT and TEXTBEFORE/TEXTAFTER do the same with formulas in Microsoft 365

A small test I use: give someone a list of 500 city names with spelling and spacing problems and ask how many unique cities there are. The answer tells me a lot.

3. Fixing dates stored as text

This one causes more wrong reports than any formula error. A date that is actually text sorts alphabetically, so "01-12-2025" ends up next to "01-01-2026", and month totals come out wrong.

Check with =ISNUMBER(A2). A real date returns TRUE. To fix, use Text to Columns with the right date format (DMY for most Indian exports), or DATEVALUE where the format is consistent. Then set a proper date format so you can see what you have.

4. Lookups: XLOOKUP, VLOOKUP and INDEX/MATCH

You will join data in Excel constantly: product names onto sales lines, region onto customers, target onto actuals.

XLOOKUP is the one to learn first if you have Microsoft 365 or Excel 2021 or later:

=XLOOKUP(B2, Products[ProductID], Products[ProductName], "Not found")

It looks left or right, it has a built-in "not found" value, and it doesn't break when someone inserts a column. Still learn VLOOKUP and INDEX/MATCH, because you will inherit old files that use them, and some interviewers ask about the difference.

Interview tip: be ready to explain why VLOOKUP with the last argument missing can return the wrong match. (It defaults to approximate match, which expects sorted data.)

	XLOOKUP	VLOOKUP	INDEX/MATCH
Available in	Microsoft 365, Excel 2021+	All versions	All versions
Looks left	Yes	No	Yes
Default match	Exact	Approximate (risky)	Set by MATCH argument
Breaks if a column is inserted	No	Often	No
Not-found value built in	Yes	No, wrap in IFERROR	No, wrap in IFERROR

5. SUMIFS, COUNTIFS and AVERAGEIFS

These answer most "how much" and "how many" questions in one line:

=SUMIFS(Sales[Amount], Sales[Region], "Gujarat", Sales[Month], "Oct-2026")

Learn to point the criteria at cells instead of typing them in, so one formula can fill a whole summary grid. That small habit makes your workbook reusable.

6. IF, IFS and logical tests

Analysts bucket things all the time: high or low value customers, on time or late deliveries, pass or fail. IF, IFS, AND, OR and IFERROR cover nearly all of it.

Keep nesting shallow. If your formula has seven IFs inside each other, a small lookup table with XLOOKUP is easier to read and easier to change.

7. PivotTables

If you only master one thing from this list, make it PivotTables. They summarise thousands of rows by any combination of fields in seconds, and they are the closest thing in Excel to the way Power BI and SQL GROUP BY think.

Practise:

• Summarising by two fields (region by month)
• Changing "Sum of" to count, average or % of total
• Grouping dates into months and quarters
• Adding a slicer
• Refreshing after the source data changes

Most interview tasks I've seen for fresher analyst roles include a PivotTable somewhere.

A worked PivotTable example

Say you have a sales table with Date, Region, Product and Amount, about 4,000 rows. The manager asks: "Which region grew the most from Q1 to Q2?"

1. Click anywhere in the table and insert a PivotTable on a new sheet.
2. Put Region in Rows and Date in Columns. Right-click a date and group by Quarters (and Years, if the data spans more than one).
3. Put Amount in Values. Check it says "Sum of Amount", not "Count of Amount". If it says Count, some amounts are probably stored as text.
4. Add a calculated difference in the next column, or show values as "% Difference From" the previous quarter.
5. Sort by that difference.

That's five minutes of work. The same question in raw formulas takes much longer and is easier to get wrong.

8. Charts that people can read

Analysts get judged by their charts, fairly or not. A few rules I repeat in every batch:

• Use a column or bar chart to compare categories, and a line chart for change over time
• Avoid 3D and pie charts with more than four slices
• Sort bars from largest to smallest unless there's a natural order
• Write a title that states the finding: "Online sales overtook store sales in August" says far more than "Sales by Channel"

9. Conditional formatting

Use it to make exceptions jump out: overdue invoices, stock below reorder level, negative growth. Data bars and colour scales work well in summary tables. Don't paint the whole sheet; if everything is highlighted, nothing is.

10. Data validation and drop-downs

If other people type into your workbook, add drop-down lists (Data, Data Validation, List) so "Anand", "ANAND" and "Anad" don't all appear. It's a small thing that saves hours of cleaning later, and it shows an interviewer you think about data quality at the source.

11. Power Query

Power Query is built into Excel (Data, Get & Transform) and it's the same engine you will use in Power BI. It records your cleaning steps (remove columns, split, change type, filter, merge, unpivot) so next month you just click Refresh instead of redoing everything.

Two Power Query moves worth learning early:

• Combining all the files in a folder, for example twelve monthly sales exports, into one table
• Unpivoting, which turns a wide sheet with one column per month into a tall table that PivotTables and Power BI can use

Once learners see this, they rarely go back. It's also the most direct bridge to Power BI, which comes next in the data analyst roadmap.

12. Dynamic array functions: FILTER, SORT, UNIQUE

In Microsoft 365, functions like UNIQUE, SORT and FILTER return whole lists that spill into neighbouring cells:

=SORT(UNIQUE(Sales[City]))
=FILTER(Sales, Sales[Amount] > 50000, "None")

They replace a lot of copy-paste work, and they think the way SQL does: give me these rows, in this order, without duplicates. If you learn them now, SQL will feel familiar sooner.

Which keyboard shortcuts save the most time?

You don't need fifty shortcuts. These are the ones I see save the most time in class, on Windows:

• Ctrl+T turns a range into a Table.
• Ctrl+Shift+L turns filters on or off.
• Ctrl+Arrow jumps to the edge of the data; add Shift to select as you go.
• Alt+N+V inserts a PivotTable (Excel's ribbon key sequence).
• F4 repeats the last action, or cycles absolute references while editing a formula.
• Ctrl+; enters today's date as a real date.
• Ctrl+1 opens Format Cells, which is the fastest way to check whether a "date" is really a date.

Learn three this week and three next week. Trying all of them at once doesn't stick.

What about VBA and macros?

They're useful in some jobs, especially in finance teams with old processes. But for a data analyst role in 2026, I'd rather you spent that time on Power Query and SQL. Power Query handles most of the repetitive cleaning that people once wrote macros for, and it's easier for the next person to understand.

How do interviewers test Excel for analyst roles?

In my experience, and from what learners tell me after their interviews, the common pattern is a short practical task: here's a file, clean it, answer three questions, and make one chart. Sometimes it's timed. Sometimes they watch you share your screen.

They are looking for:

• Whether you check the data before you trust it (duplicates, blanks, text dates)
• Whether you use lookups and SUMIFS correctly
• Whether you reach for a PivotTable when it's the obvious tool
• Whether your final answer is clear to someone who didn't do the work

Speed comes from practice. Correctness comes from checking.

How do I practise these skills?

Pick a public dataset, such as one from India's Open Government Data platform (data.gov.in), or Microsoft's free Financial Sample workbook from the Power BI documentation. Then give yourself a question, like "which state had the highest growth last year", and answer it start to finish using as many of the twelve skills as make sense. Write down every problem you hit.

If you want structured practice with feedback, our data analysis training spends its opening module on exactly this, before moving into SQL and Power BI. We teach it in the Vallabh Vidyanagar classroom and live online; see training programs in Anand for local details or the post on choosing a data analysis course in Anand if you're comparing options.

FAQ

Is Excel enough to get a data analyst job?

Excel alone can get you some MIS or reporting roles, but most data analyst listings also ask for SQL and a BI tool such as Power BI. Treat Excel as the foundation, not the whole house.

Which Excel version should I learn on?

Microsoft 365 if you can, because it has XLOOKUP, dynamic arrays and the newest Power Query features. If you only have Excel 2016 or 2019, learn VLOOKUP and INDEX/MATCH well and you can switch to XLOOKUP later.

Should I learn VLOOKUP or XLOOKUP?

Learn XLOOKUP first if your version has it, because it's simpler and safer. Learn VLOOKUP too, because older workbooks use it and interviewers sometimes ask about it.

How long does it take to learn Excel for data analysis?

With 8 to 10 hours a week of practice, most learners get comfortable with these twelve skills in three to five weeks. Mastery takes longer and comes from using them on real data.

Is Power Query part of Excel or Power BI?

Both. Power Query is built into Excel (Get & Transform) and into Power BI Desktop. The interface and the M language behind it are almost the same, so what you learn in one carries over to the other.