Excel is still the first tool most data analysts open. Before you learn Power BI or SQL, a few Excel formulas and habits will save you hours on every dataset. This guide covers the ones that come up in almost every analyst role.
The formulas to learn first
- SUMIFS, COUNTIFS and AVERAGEIFS: totals and counts with one or more conditions, for example sales by region and month.
- XLOOKUP (or VLOOKUP and INDEX/MATCH in older versions): bring a value from one table into another using a shared key.
- IF, IFS and IFERROR: label rows, handle missing values and hide error messages in reports.
- TEXT, LEFT, RIGHT and TRIM: clean names, codes and dates that were typed in by hand.
- DATE functions: EOMONTH and the year and month functions, so reports group by the right period.
Habits that matter more than any single formula
- Turn every raw dataset into an Excel Table (Ctrl+T) so formulas grow with the data.
- Keep the raw data on one sheet and never edit it. Do the cleaning on a separate sheet.
- Name your key ranges, so a formula reads like a sentence.
- Check totals against the source once, before you send a report.
Pivot tables: the fastest way to answer a question
A pivot table can summarise thousands of rows in seconds. Start with one question, such as “which product made the most revenue last quarter?”, then drag the fields that answer it. Add a slicer when someone needs to filter the report themselves.
Where Excel stops and SQL starts
Excel works well up to a few hundred thousand rows. When data lives in a database, or when you need to join many tables, SQL is the better tool. Learning Excel first makes SQL easier, because both are about filtering, grouping and joining tables.
Practise with a small project
Pick a public sales or survey dataset, clean it, and build a one-page summary with three charts and two pivot tables. Write one paragraph that explains what the data shows. That paragraph is what you will talk about in an interview.
Next step: see how Excel fits into the full course on our data analytics course page, or book a free demo class. A counsellor will share the fee and the next batch dates.

Data Analysis for Small Business Owners in Bangalore - ScholarLogic
[…] these skills in the data analytics course at BTM Layout, or read Excel formulas every data analyst should know. Book a free demo class, and ask a counsellor for batch […]
Data Validation in Excel: Keeping Entries Clean - ScholarLogic
[…] new errors but does not fix old ones. Clean the existing data first, then apply the rules. Read Excel formulas every data analyst should know next, or book a free demo class. Ask a counsellor for the current course […]