Getting everything ready for you…

Loading courses, problems and your progress. This takes just a moment.

0s

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 FALSE in 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.

  1. Click inside your data → Insert → PivotTable.
  2. Drag fields into Rows, Columns, Values and Filters.
  3. Right-click a value → change Sum to Count or Average, or Show Values As → % of Grand Total.
  4. Add Slicers for clickable filters.
  5. 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

  1. Clean a messy customer list: trim, remove duplicates, fix dates.
  2. Use XLOOKUP to add prices to an orders sheet, and SUMIFS for totals by city.
  3. Build a pivot table of sales by region and month, with slicers.
  4. Build a Power BI sales dashboard on a star schema.
  5. 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]))

References