Excel & Power BI Documentation
The data analyst toolkit — formulas, lookups, pivot tables, Power Query, data modelling, DAX and dashboards.
Excel and Power BI are the daily tools of data analysts. Excel is for cleaning and analysing data, and Power BI turns it into interactive dashboards. This page covers the formulas, pivot tables, Power Query, DAX and dashboard skills that interviews ask about.
1. Introduction
| Tool | Best for |
|---|---|
| Excel | Quick analysis, cleaning, one-off reports |
| Power Query | Repeatable data cleaning (in both tools) |
| Power BI | Interactive dashboards and shared reports |
| SQL | Pulling data from databases |
Typical analyst workflow: get data → clean it → model it → analyse → visualise → share the insight.
2. Excel Basics
| Shortcut | Does |
|---|---|
Ctrl + T |
Make a table |
Ctrl + Shift + L |
Toggle filters |
Ctrl + Arrow |
Jump to the edge of data |
Ctrl + Shift + Arrow |
Select to the edge |
F4 |
Toggle $ references |
Alt + = |
AutoSum |
Ctrl + ; |
Today's date |
Cell references:
| Type | Example | On copy |
|---|---|---|
| Relative | A1 |
Row and column change |
| Absolute | $A$1 |
Nothing changes |
| Mixed | $A1, A$1 |
One part stays fixed |
Use Tables (Ctrl + T): formulas fill down by themselves and new rows are included.
3. Essential Formulas
=SUM(B2:B10) Add
=AVERAGE(B2:B10) Mean
=COUNT(B2:B10) Count numbers
=COUNTA(A2:A10) Count non-empty
=MAX(B2:B10) =MIN(B2:B10) Largest, smallest
=ROUND(B2, 2) Round
=IF(B2>=40, "Pass", "Fail") Condition
=IFS(B2>=75,"A", B2>=50,"B", TRUE,"C")
=AND(B2>0, C2>0) =OR(...) Combine conditions
=IFERROR(B2/C2, 0) Replace errors
Conditional totals:
=COUNTIF(C2:C100, "Delhi")
=SUMIF(C2:C100, "Delhi", D2:D100)
=SUMIFS(D2:D100, C2:C100, "Delhi", E2:E100, ">1000")
=AVERAGEIFS(D2:D100, C2:C100, "Delhi")
4. Lookups
=VLOOKUP(A2, Prices!A:C, 3, FALSE)
=XLOOKUP(A2, Prices!A:A, Prices!C:C, "Not found")
=INDEX(C2:C100, MATCH(A2, A2:A100, 0))
| VLOOKUP | XLOOKUP | INDEX + MATCH | |
|---|---|---|---|
| Look left | No | Yes | Yes |
| Column insert safe | No | Yes | Yes |
| Default if missing | No | Yes | No |
| Old Excel | Yes | No | Yes |
Common mistake: leaving out
FALSEin VLOOKUP gives an approximate match and wrong answers.
5. Text and Dates
=TRIM(A2) Remove extra spaces
=UPPER(A2) =PROPER(A2) Change case
=LEFT(A2, 3) =RIGHT(A2, 2) =MID(A2, 2, 4)
=LEN(A2)
=TEXTJOIN(", ", TRUE, A2:C2)
=SUBSTITUTE(A2, "-", "")
=TODAY() =NOW()
=YEAR(A2) =MONTH(A2)
=DATEDIF(A2, TODAY(), "Y")
=TEXT(A2, "dd-mmm-yyyy")
Dynamic arrays (Excel 365) spill results into neighbouring cells:
=UNIQUE(C2:C100)
=SORT(A2:B100, 2, -1)
=FILTER(A2:D100, C2:C100="Delhi")
6. Pivot Tables
Pivot tables summarise thousands of rows in seconds, without formulas.
- Click inside your data → Insert → PivotTable.
- Drag fields into Rows, Columns, Values and Filters.
- Right-click a value → change Sum to Count or Average, or Show Values As → % of Grand Total.
- Add Slicers for clickable filters.
- Right-click → Refresh when the data changes.
Example: Region in Rows, Month in Columns, Sales in Values gives sales by region and month.
7. Cleaning Data and Charts
Cleaning: Remove Duplicates, Text to Columns, Flash Fill (Ctrl + E), Find & Replace, TRIM, Data Validation drop-downs.
Conditional formatting highlights values: colour scales, top 10, and duplicates.
| Show | Chart |
|---|---|
| Comparison | Bar / column |
| Trend over time | Line |
| Part of a whole (few parts) | Pie or doughnut |
| Relationship | Scatter |
| Distribution | Histogram |
8. Power Query
Power Query records your cleaning steps so that they re-run on new data. Data → Get Data in Excel, or Transform Data in Power BI.
Common steps: remove columns, change types, filter rows, split columns, trim, replace values, unpivot, Merge (join) and Append (stack).
| Merge | Append | |
|---|---|---|
| Combines | Columns, by a key | Rows |
| SQL equivalent | JOIN | UNION |
9. Power BI and Data Modelling
Workflow: Get Data → Transform (Power Query) → Model → Report → Publish to Service.
| Part | Does |
|---|---|
| Desktop | Free app for building reports |
| Service | Website for sharing and refresh |
| Report | Pages of visuals |
| Dashboard | One page of pinned visuals (Service) |
Star schema is the recommended model:
DimDate
|
DimProduct — FactSales — DimCustomer
|
DimRegion
- Fact table: events with numbers (sales rows).
- Dimension tables: descriptions (product, date, customer).
- Use one-to-many relationships from dimensions to facts, with a single filter direction.
- Add a proper date table for time calculations.
10. DAX Essentials
DAX is the formula language of Power BI.
Total Sales = SUM(Sales[Amount])
Orders = DISTINCTCOUNT(Sales[OrderID])
Delhi Sales = CALCULATE([Total Sales], Customers[City] = "Delhi")
Profit % = DIVIDE([Total Profit], [Total Sales], 0)
Sales YTD = TOTALYTD([Total Sales], 'Date'[Date])
Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Line Total = SUMX(Sales, Sales[Qty] * Sales[Price])
| Measure | Calculated column | |
|---|---|---|
| Runs | When the visual shows | At refresh, stored per row |
| Uses | Filter context | Row context |
| Good for | Totals, ratios | Categories to slice by |
CALCULATE is the most important function: it changes the filter context. DIVIDE avoids divide-by-zero errors.
11. Dashboards That Work
- Put the key numbers (KPIs) at the top left, where people look first.
- Use few colours, and one chart per question.
- Add slicers for date and region.
- Title charts with the insight: "Sales fell 12% in Q3", not "Sales by quarter".
- Use Row-Level Security so each manager sees only their region.
- Set a scheduled refresh in the Service.
12. Practice Problems
- Clean a messy customer list: trim, remove duplicates, fix dates.
- Use
XLOOKUPto add prices to an orders sheet, andSUMIFSfor totals by city. - Build a pivot table of sales by region and month, with slicers.
- Build a Power BI sales dashboard on a star schema.
- Write DAX for Total Sales, YTD, last year and growth %.
13. Interview Questions
VLOOKUP vs XLOOKUP? XLOOKUP can look left, defaults to exact match and has a not-found value.
What is a pivot table? A tool that summarises data by dragging fields into rows, columns and values.
COUNT vs COUNTA? COUNT counts numbers; COUNTA counts non-empty cells.
Absolute vs relative reference? $A$1 stays fixed when copied; A1 shifts.
Measure vs calculated column? A measure calculates in the current filter context; a column is stored per row.
What does CALCULATE do? It evaluates an expression with changed filters.
Star vs snowflake schema? Star has flat dimensions; snowflake splits dimensions into more tables. Star is simpler and faster in Power BI.
Merge vs append? Merge joins columns; append stacks rows.
Import vs DirectQuery? Import stores a copy (fast); DirectQuery queries the source live.
What is Row-Level Security? Limiting which rows each user can see.
14. Cheat Sheet & References
Excel formulas
=SUMIFS(sum, range1, crit1, range2, crit2)
=COUNTIF(range, crit)
=XLOOKUP(key, look_in, return, "None")
=INDEX(col, MATCH(key, keys, 0))
=IFERROR(x, 0)
=TEXT(date, "dd-mmm-yyyy")
=FILTER(data, cond) =UNIQUE(col) =SORT(data, col, -1)
Shortcuts
Ctrl+T table · Ctrl+Shift+L filter · F4 lock ref
Alt+= AutoSum · Ctrl+E Flash Fill · Ctrl+; today
DAX
Total = SUM(T[Amount])
Filtered = CALCULATE([Total], T[City] = "Delhi")
Ratio = DIVIDE([A], [B], 0)
YTD = TOTALYTD([Total], 'Date'[Date])
LastYear = CALCULATE([Total], SAMEPERIODLASTYEAR('Date'[Date]))