14 exercises · 3 datasets
Excel practice, with real-looking data
Last reviewed
Datasets
Sales orders
180 rows · order_id, order_date, region, city, sales_rep, product_id, product, category, unit_price, quantity, discount_pct, channel
sales.csvEmployees
80 rows · employee_id, first_name, last_name, email, department, join_date, base_salary, performance_rating, manager_id, location
employees.csvSupport tickets
150 rows · ticket_id, created_at, resolved_at, channel, priority, category, agent, csat
support_tickets.csv
- 01
Add a net revenue column
Easy · sales.csvAdd a column net_revenue = unit_price × quantity, after applying discount_pct.
- Formulas
- Absolute references
Hint
Discount is a percentage stored as a whole number (e.g. 10 means 10%).
Solution
In the first data row (assuming unit_price in I, quantity in J, discount_pct in K):
=I2*J2*(1-K2/100)Convert the range to a Table (Ctrl+T) so the formula fills down automatically as
=[@unit_price]*[@quantity]*(1-[@discount_pct]/100). - 02
Total revenue by region
Easy · sales.csvBuild a small summary table listing each region and its total net_revenue.
- SUMIFS
Solution
List the regions in a column (or use
=UNIQUE(Table1[region])), then:=SUMIFS(Table1[net_revenue], Table1[region], A2)A PivotTable with region in Rows and net_revenue in Values gives the same result.
- 03
Monthly revenue trend
Medium · sales.csvShow net revenue by month for the year and chart it. Which month was highest?
- Dates
- PivotTables
- Charts
Hint
Group dates by month in a PivotTable, or derive a month column first.
Solution
Add
month = TEXT([@order_date],"yyyy-mm"), then a PivotTable with month in Rows and Sum of net_revenue in Values. Insert a line chart. Use=MAXIFSor sort descending to find the top month. - 04
Top sales rep in each region
Hard · sales.csvFor each region, find the sales rep with the highest net revenue.
- Dynamic arrays
- SUMIFS
- XLOOKUP
Hint
Build a region × rep revenue grid first.
Solution
Create a grid with regions down and reps across using
=SUMIFS(Table1[net_revenue],Table1[region],$A2,Table1[sales_rep],B$1). Then per region:=XLOOKUP(MAX(B2:G2),B2:G2,$B$1:$G$1). In Microsoft 365 you can also useSORTBY+TAKEon a filtered list. - 06
Clean inconsistent email addresses
Easy · employees.csvSome emails have stray spaces and capital letters. Create a clean, lower-case email column.
- Text functions
- Data cleaning
Solution
=LOWER(TRIM([@email])). Then copy and Paste Special › Values if you need to replace the original column. - 07
Employee tenure in years
Easy · employees.csvCalculate each employee's completed years of service as of 31 March 2026.
- Dates
- DATEDIF
Solution
=DATEDIF([@join_date], DATE(2026,3,31), "y"). Put the as-of date in a separate cell and reference it, so the report can be refreshed. - 08
Look up each employee's manager name
Medium · employees.csvAdd manager_name by looking up manager_id in the same table. Leave it blank for people without a manager.
- XLOOKUP
- Self-lookup
Solution
=IF([@manager_id]="", "", XLOOKUP([@manager_id], [employee_id], [first_name]&" "&[last_name], "Not found")). - 09
Count and handle missing ratings
Easy · employees.csvHow many employees have no performance_rating? What is the average rating by department, ignoring blanks?
- COUNTBLANK
- AVERAGEIFS
Solution
=COUNTBLANK(Table2[performance_rating]). Average by department:=AVERAGEIFS(Table2[performance_rating], Table2[department], A2)— AVERAGEIFS ignores blank cells automatically. - 10
Assign salary bands
Medium · employees.csvBand base_salary: under ₹8L = Band 1, ₹8L–₹15L = Band 2, ₹15L–₹22L = Band 3, above = Band 4.
- IFS
- XLOOKUP approximate match
Solution
=IFS([@base_salary]<800000,"Band 1",[@base_salary]<1500000,"Band 2",[@base_salary]<2200000,"Band 3",TRUE,"Band 4"). A cleaner, maintainable option is a band table with lower bounds and=XLOOKUP([@base_salary], bands[min], bands[band], , -1). - 11
Resolution time in hours
Medium · support_tickets.csvCompute hours to resolve each ticket. Unresolved tickets should show "Open".
- Date-time arithmetic
- IF
Hint
Excel stores date-times as days; multiply by 24.
Solution
=IF([@resolved_at]="","Open",([@resolved_at]-[@created_at])*24). If the columns import as text, convert them withDATEVALUE+TIMEVALUEor Power Query first. - 12
Flag SLA breaches
Medium · support_tickets.csvSLA targets: Urgent 4h, High 12h, Medium 24h, Low 48h. Flag resolved tickets that breached their SLA, and give the breach rate by priority.
- Logical functions
- Lookup tables
Solution
Create an SLA table, then
=IF(ISNUMBER([@hours]), IF([@hours] > XLOOKUP([@priority], sla[priority], sla[hours]), "Breached", "Met"), "Open"). Breach rate:=COUNTIFS(T[priority],A2,T[sla],"Breached")/COUNTIFS(T[priority],A2,T[sla],"<>Open"). - 13
CSAT by agent and channel
Medium · support_tickets.csvBuild a PivotTable of average csat by agent (rows) and channel (columns), and highlight the lowest cells.
- PivotTables
- Conditional formatting
Solution
Insert › PivotTable: agent in Rows, channel in Columns, Average of csat in Values. Apply a colour scale via Conditional Formatting. Note in your summary how many responses each average is based on — small counts make averages unreliable.
- 14
One-page support dashboard
Hard · support_tickets.csvCreate a one-page dashboard with: tickets by week, median resolution hours, SLA breach rate and CSAT, with slicers for channel and priority.
- Dashboard design
- Slicers
- KPIs
Hint
Median needs a formula or Power Pivot measure — PivotTables don't offer it by default.
Solution
Build PivotTables on a hidden sheet, connect slicers to all of them (Slicer › Report Connections), and place charts and KPI cells on the dashboard sheet. For median:
=MEDIAN(FILTER(T[hours], ISNUMBER(T[hours]))). Label every KPI with its definition.