Excel is still the first tool most data analysts open every morning. Before you learn Power BI, SQL or Python, a solid set of Excel skills will save you hours on every dataset and help you answer the practical questions that come up in analyst interviews. This guide brings together everything a beginner needs in one place: the order to learn tools in, cleaning data, the formulas that matter, lookups, pivot tables, simple dashboards, conditional formatting, data validation and Power Query.
Work through it from top to bottom, or jump to the section you need.
Contents
- 1. Excel, SQL or Power BI: what to learn first
- 2. Data cleaning in Excel: a step-by-step checklist
- 3. Text functions: TRIM, LEFT, RIGHT, MID and TEXTSPLIT
- 4. The formulas every data analyst should know
- 5. IF, IFS and nested conditions
- 6. XLOOKUP vs VLOOKUP
- 7. Pivot tables and the mistakes to avoid
- 8. Pivot charts and slicers: a mini dashboard
- 9. Conditional formatting
- 10. Data validation
- 11. Power Query basics
- 12. Practice questions for a sales dashboard
- 13. A practice project for your resume
1. Excel, SQL or Power BI: what to learn first
Excel, SQL and Power BI appear in almost every data analyst job description, and freshers often ask which one to start with. The short answer: you will need all three, but the order matters.
Start with Excel if
- you have never worked with data before;
- you want to see the result of every step immediately on screen;
- your target roles mention reporting in Excel.
Start with SQL if
- you are already comfortable with Excel formulas and pivot tables;
- your target roles mention databases or large datasets.
Why they work well together
Excel teaches filtering, grouping and lookups visually. SQL does the same things in code on much larger data: filtering becomes WHERE, a pivot table becomes GROUP BY and a lookup becomes a JOIN. Power BI then turns that data into reports and dashboards other people can use. Power BI now includes Copilot, which can help build report pages and visuals, but you still need to understand the data to check its results, which is why Excel and SQL come first.
A simple order for beginners
- Excel: cleaning data, formulas and summaries.
- SQL: reading, filtering, grouping and joining tables.
- Power BI: data models, measures and dashboards.
- Python: step by step, once the basics feel easy.
A practical plan is to spend a few weeks on Excel cleaning and pivot tables, then answer the same questions with SQL on the same kind of data. Comparing your Excel and SQL answers is an excellent way to check both.
2. Data cleaning in Excel: a step-by-step checklist
Most datasets arrive with problems: extra spaces, mixed date formats, duplicates and blanks. Cleaning them before you analyse saves you from wrong totals later.
- Keep a copy of the raw data. Never clean the original sheet. Copy it to a new sheet and work there, so you can always check against the source.
- Remove extra spaces and fix case. Use TRIM to remove extra spaces and PROPER or UPPER to make text consistent. Otherwise “Bangalore ” and “bangalore” count as two different cities.
- Fix dates and numbers stored as text. Check that dates sort correctly and numbers add up. Use Text to Columns, DATEVALUE or VALUE to convert them.
- Remove duplicates carefully. Use Remove Duplicates only after deciding which columns define a real duplicate. Two orders from the same customer on the same day may both be genuine.
- Decide what to do with blanks. Leave, fill or remove each blank, and write your decision in a notes sheet.
- Check the totals. Compare the row count and the main total with the raw sheet, and explain any difference.
To find duplicates before deleting anything, add a duplicate-values conditional formatting rule, or count how often the fields that should identify each record appear with COUNTIFS.
3. Text functions: TRIM, LEFT, RIGHT, MID and TEXTSPLIT
Exported data is rarely clean. Names have extra spaces, codes are joined together and dates arrive as text. Excel’s text functions fix most of this without manual editing, and they are a common part of data analyst tests.
Cleaning
=TRIM(A2)removes extra spaces, including double spaces between words.=CLEAN(A2)removes non-printing characters that come from some systems.=PROPER(A2), UPPER and LOWER fix capitalisation. Combine them:=PROPER(TRIM(A2))tidies a list of names in one step.
Extracting parts
=LEFT(A2, 3): the first three characters, such as a city code.=RIGHT(A2, 4): the last four, such as a year.=MID(A2, 5, 2): two characters starting at position 5.=LEN(A2): the length, useful for spotting phone numbers with missing digits.
Finding a position
=FIND("@", A2) returns where the @ sits in an email address. To get the domain: =MID(A2, FIND("@", A2) + 1, 100). Use SEARCH instead of FIND when upper and lower case should be treated the same.
Splitting and joining
In Microsoft 365, =TEXTSPLIT(A2, ",") splits “Python,SQL,Excel” into three cells. TEXTBEFORE and TEXTAFTER are even simpler: =TEXTAFTER(A2, "@") gives the email domain. In older versions, use Data, then Text to Columns. To join cells, =TEXTJOIN(", ", TRUE, B2:D2) combines them with a separator and skips blanks, and the & operator joins simple pieces: =A2 & " - " & B2.
Numbers stored as text
Values that look like numbers but are left-aligned are often text. =VALUE(A2) converts them, and =TEXT(B2, "dd-mmm-yyyy") formats a number or date as text for labels.
4. The formulas every data analyst should know
- 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. See section 6.
- IF, IFS and IFERROR: label rows, handle missing values and hide error messages in reports. See section 5.
- TEXT, LEFT, RIGHT and TRIM: clean names, codes and dates that were typed in by hand.
- Date functions such as EOMONTH, YEAR and MONTH, so reports group by the right period.
Absolute and relative references
When you copy a formula, Excel shifts its cell references. Use an absolute reference such as $B$2 when a copied formula must always point to the same cell, for example a single tax rate or target value.
Habits that matter more than any single formula
- Turn every raw dataset into an Excel Table (Ctrl + T) so formulas and pivots 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 any report.
Excel works well up to a few hundred thousand rows. When data lives in a database, or you need to join many tables, SQL is the better tool, and learning Excel first makes SQL much easier.
5. IF, IFS and nested conditions
A large part of analysis is labelling data: high or low value orders, late or on-time deliveries, pass or fail. Excel’s conditional functions do this, and choosing the right one keeps formulas short and readable.
IF: one condition, two results
=IF(C2>=50000, "High value", "Standard"). The pattern is IF(test, value if true, value if false). Text results need quotes; numbers do not.
AND and OR inside IF
=IF(AND(C2>=50000, D2="Bangalore"), "Priority", "Normal"). AND needs every condition to be true; OR needs at least one. They let you keep a single IF instead of two.
Nested IF vs IFS
For several bands, older files nest IFs: =IF(B2>=90,"A",IF(B2>=75,"B",IF(B2>=60,"C","D"))). IFS, available in Excel 2019 and Microsoft 365, is easier to read: =IFS(B2>=90,"A", B2>=75,"B", B2>=60,"C", TRUE,"D"). Conditions are checked in order, so start with the highest band. The final TRUE acts as “everything else”.
When to use a lookup table instead
If you have more than four or five bands, or the thresholds change often, put them in a small table (minimum score and grade) and use XLOOKUP with an approximate match: =XLOOKUP(B2, Bands[Min], Bands[Grade], , -1). Changing a threshold then means editing one cell rather than every formula.
Common mistakes
- Numbers stored as text, so comparisons silently fail. Check with ISNUMBER.
- Overlapping or wrongly ordered conditions in IFS.
- Missing a final “else”, which produces #N/A in IFS.
- Hard-coding thresholds inside hundreds of formulas.
6. XLOOKUP vs VLOOKUP
Almost every analyst job involves matching data from two tables, such as order IDs in one sheet and customer names in another. The classic tool is VLOOKUP; newer versions of Excel also have XLOOKUP. Both work, but they behave differently, and knowing why saves hours of debugging.
How VLOOKUP works
VLOOKUP looks for a value in the first column of a range and returns a value from a column to the right, chosen by number. For example, =VLOOKUP(A2, Customers!A:D, 3, FALSE) finds the ID in A2 and returns the third column of the customer table. It has three common problems:
- It cannot look left. The lookup column must be the first column of the range.
- The column number breaks easily. If someone inserts a column in the source table, “3” now points to the wrong data, with no warning.
- The default match is approximate. If you forget the last argument (FALSE), VLOOKUP may return a wrong value instead of an error.
How XLOOKUP works
XLOOKUP takes the lookup column and the return column separately: =XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found"). This fixes all three problems: the return column can be on either side, inserting columns does not break it, and exact match is the default, with an optional message when nothing is found.
Which should you learn?
Learn both. Use XLOOKUP for new work when your version has it (Microsoft 365 and Excel 2021 onwards), but be able to read VLOOKUP, because many company files still use it and interviewers often ask about it.
Quick practice task
- Create an Orders sheet with Order ID and Customer ID.
- Create a Customers sheet with Customer ID, Name and City.
- Bring the customer name into Orders with VLOOKUP, then with XLOOKUP.
- Insert a new column in the Customers sheet and see which formula still returns the right name.
7. Pivot tables and the mistakes to avoid
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 in the fields that answer it. Small mistakes, however, can give totals that look right and are wrong. Watch for these:
- Not refreshing: a pivot table does not update when the data changes until you refresh it.
- A fixed source range: new rows are left out. Use an Excel Table as the source so it grows automatically.
- Numbers stored as text: they are counted, not summed.
- Blank rows or merged cells in the source: they break grouping.
- Count instead of Sum: check the value field setting.
- Dates not grouping as expected: check that the date column contains real dates, not text.
A quick check: compare the pivot table’s grand total with a simple SUM of the source column. If they differ, find out why before you share the report.
8. Pivot charts and slicers: a mini dashboard
Add a pivot chart and a few slicers to a pivot table and it becomes an interactive mini dashboard that a manager can click through without touching a formula. It is a skill that comes up in many data analyst interviews.
- Start from a clean table. Convert your data to an Excel Table (Ctrl + T) so the pivot always includes the latest rows after a refresh.
- Build the pivot table. A typical sales example: Region in Rows, Month in Columns and Sales in Values. Check that values use Sum, not Count.
- Add a pivot chart. With the pivot selected, choose Insert, then PivotChart. Use a clustered column chart to compare regions or a line chart for trends over months. The chart is linked to the pivot and updates with it.
- Add slicers. Choose Insert, then Slicer, and pick fields such as Product Category or Year. Add a Timeline for date fields to filter by month or quarter with a slider.
- Connect one slicer to several pivots. Right-click a slicer, open Report Connections and tick every pivot that should respond. Now one click filters the whole sheet.
Tips that make it look professional
- Remove chart clutter: field buttons, gridlines and unnecessary legends.
- Give each chart a title that states the insight, not just the field names.
- Format numbers (thousands, currency) in the pivot rather than in the chart.
- Use Data, then Refresh All, before sharing.
9. Conditional formatting
Conditional formatting changes how a cell looks when its value meets a rule. It lets you spot late deliveries, low stock or a sales dip without reading every row. Three rules to start with:
- Highlight cells greater than a value: for example, orders above 10,000 rupees.
- Colour scales: shade a column from low to high so trends show at a glance.
- Formula rules: for example, highlight a whole row when its due date has passed.
A caution: too many colours hide the message. Use one colour for the problem you want people to act on, and keep the rest plain.
10. Data validation
Data validation stops wrong entries before they reach your analysis. A date column with text in it, or a product name with a typo, can quietly break a pivot table.
How to set it up
- Select the cells you want to control.
- Open Data, then Data Validation.
- Choose a rule: a list, a whole number, a date range or a custom formula.
- Add an input message and an error alert so the next person knows what to enter.
Useful rules for analysts
- A drop-down list of allowed categories, such as North, South, East and West.
- A date rule that accepts only dates after a set start date.
- A custom rule that stops duplicate order IDs, for example
=COUNTIF($A:$A, A2)=1.
Validation prevents new errors but does not fix old ones. Clean the existing data first (see section 2), then apply the rules.
11. Power Query basics
Power Query is the data-cleaning tool built into both Excel and Power BI. It records each cleaning step, so the same steps run again automatically when the data is refreshed. That makes it the natural next step once you have cleaned the same kind of file by hand a few times.
The steps you will use first
- Remove duplicate rows.
- Change column types, for example text to date.
- Filter out blank or test rows.
- Split or merge columns.
- Append two tables with the same columns, or merge two tables on a shared key.
Rename each step in the Applied Steps panel, such as “Remove test orders”, so anyone can follow what happened to the data. As practice, take a messy CSV file and clean it in Power Query until it loads without errors, then explain each step in one sentence.
12. Practice questions for a sales dashboard
These questions come up when you build a beginner sales dashboard with Excel, SQL and Power BI. Try to answer each one before reading the answer.
- How do I check for duplicates in Excel? Use a duplicate-values rule, or count occurrences of the fields that should identify each record. Confirm what a duplicate means before removing rows.
- What is the difference between WHERE and HAVING in SQL? WHERE filters rows before grouping. HAVING filters grouped results, such as customers whose total purchases exceed a threshold.
- What is the difference between INNER JOIN and LEFT JOIN? An INNER JOIN keeps only matching rows. A LEFT JOIN also keeps unmatched rows from the left table, with blanks for the right table’s fields.
- How does COUNT differ from COUNT DISTINCT? COUNT(column) counts non-blank values, including repeats. COUNT(DISTINCT column) counts unique values.
- What is the difference between a Power BI measure and a calculated column? A measure is calculated in the current report filter context. A calculated column computes a value for each row, which you can use in categories and filters.
- What is a star schema? A table of events, such as sales, at the centre, connected to descriptive tables such as Date, Product and Customer.
- How can I verify the totals in a dashboard? Compare it with a separate pivot table or query for the same dates and filters, and check for duplicates, missing records and rounding differences.
13. A practice project for your resume
Pick a public sales or survey dataset and work through this guide on it:
- Clean the data on a separate sheet using the checklist in section 2.
- Add helper columns with IF, XLOOKUP and text functions.
- Build two pivot tables and three pivot charts, connected to shared slicers.
- Highlight the one problem that matters with conditional formatting.
- Write one paragraph explaining what the data shows and what you would recommend.
That final paragraph is what you will talk about in an interview. For more questions across Excel, SQL and Power BI, see the Data Analytics questions and answers, and for what changed recently in Excel, read Copilot in Excel: what data analytics learners should know.
Learn Excel, SQL and Power BI with ScholarLogic
Excel, SQL and Power BI are the core of the 5-month Data Analytics course in Bangalore at ScholarLogic, BTM Layout. Freshers and graduates from any stream can join, and Python is taught step by step after the basics. You can attend a free demo class first; a counsellor will share the fee and the next batch dates. To book, call 78923 93639.

Power BI and Dashboards for Data Analysts: A Complete Beginner’s Guide to DAX, Data Models and Design - ScholarLogic
[…] you are new to data, start with our Excel guide and SQL guide first. Power BI is much easier when you already think in tables, filters and […]
Statistics and Core Skills for Data Analysts: From p-values to Data Quality and Interviews - ScholarLogic
[…] pairs with our tool guides: Excel, SQL and Power BI, and with our machine learning guide if you are heading towards data […]
IT Career Guide for Freshers: Resumes, LinkedIn, Portfolio Projects, Interviews and Gen AI - ScholarLogic
[…] the technical skills themselves, see our track guides: Excel, SQL, Power BI, statistics, machine learning, Python, Django, Core Java, Spring Boot, AWS, DevOps […]