Course contents · Excel Basics

Proper data sets and Excel Tables

Open the practice in the trainer →

Sorting, filtering, D functions, PivotTables, Power Query and Power Pivot only work properly when the raw data is stored in a proper data set.

Rules of a proper data set

  1. Field names (headers) in the first row, each one unique.
  2. Records in rows, one record per row.
  3. No blank rows or columns inside the data; empty cells around the table.
  4. One data type per column (numbers, dates, text).
  5. No totals and no merged cells inside the data set.

Cleaning data

Raw data often needs cleaning: remove unwanted characters, add needed ones, split and join values. Useful tools: TRIM, LEFT, RIGHT, MID, LEN, UPPER and the & operator.

The Excel Table feature (Ctrl+T)

Turns a data set into a dynamic table: formulas and formatting extend to new rows automatically, filter buttons and a total row appear, and PivotTables and charts based on it pick up new data.

Tasks in the trainer

  1. In G2 count the records (non-empty cells in the Employee column B2:B7).
  2. In G3 total the expenses in the “Stationery” category.
  3. Clean the names: in E2:E7 remove extra spaces and convert to upper case.
  4. In G4 count the records in the “Transport” category.
  5. In G5 find the largest expense amount.

Solve the tasks in the trainer →

Check yourself

  1. Where do field names go in a proper data set?
  2. Which shortcut creates an Excel Table?
  3. What breaks the proper data set rules?