Skip to content

14 exercises · 3 datasets

Excel practice, with real-looking data

Download a dataset, open it in Excel or Google Sheets, and work through the exercises in order. Try each one before opening the solution. All data is fictional.

Last reviewed

Datasets

  1. 01

    Add a net revenue column

    Easy · sales.csv

    Add 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).

  2. 02

    Total revenue by region

    Easy · sales.csv

    Build 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.

  3. 03

    Monthly revenue trend

    Medium · sales.csv

    Show 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 =MAXIFS or sort descending to find the top month.

  4. 04

    Top sales rep in each region

    Hard · sales.csv

    For 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 use SORTBY + TAKE on a filtered list.

  5. 05

    Category share of revenue

    Medium · sales.csv

    Calculate each category's share of total net revenue, formatted as a percentage.

    • Percentages
    • Absolute references
    Solution

    =SUMIFS(Table1[net_revenue],Table1[category],A2)/SUM(Table1[net_revenue]), formatted as %. The denominator must stay fixed — a structured reference or $ anchors handle this.

  6. 06

    Clean inconsistent email addresses

    Easy · employees.csv

    Some 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.

  7. 07

    Employee tenure in years

    Easy · employees.csv

    Calculate 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.

  8. 08

    Look up each employee's manager name

    Medium · employees.csv

    Add 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")).

  9. 09

    Count and handle missing ratings

    Easy · employees.csv

    How 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. 10

    Assign salary bands

    Medium · employees.csv

    Band 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. 11

    Resolution time in hours

    Medium · support_tickets.csv

    Compute 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 with DATEVALUE + TIMEVALUE or Power Query first.

  12. 12

    Flag SLA breaches

    Medium · support_tickets.csv

    SLA 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. 13

    CSAT by agent and channel

    Medium · support_tickets.csv

    Build 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. 14

    One-page support dashboard

    Hard · support_tickets.csv

    Create 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.