Skip to content
Reading settings
ExcelBeginner~20 min practiceLesson 5 of 34

Design a table that analysis can trust

Turn a month-by-month matrix into a table you can filter and extend.

Content reviewed · How we check lessons

Loading your progress…

In this lesson

Create one row per team and month, add a key and compare the new table with the original totals.

Example

Make room for March without another month column

A team report has Jan and Feb columns: Support has 10 and 12 tickets; Sales has 8 and 9. You need to filter by team and month and add March later. Put each team-month combination on its own row.

How it works

The row grain tells you what one record represents. Here, one row means one team in one month, and Team plus Month identifies that row.

Keep the record table separate from its report layout. Use one header per field, consistent types and no subtotal rows inside the source. Build month summaries from those records.

Steps

  1. On RawMatrix, enter Team, Jan and Feb with the two team rows. Keep this sheet as the original so you can compare the reshaped data with it.

  2. On Records, enter Team, Month and Tickets in A1:C1. Fill rows 2–5 with Support Jan 10, Support Feb 12, Sales Jan 8 and Sales Feb 9.

  3. Read each row as a team-month record. Use the same month labels throughout and enter Tickets as numbers, rather than text such as twelve tickets.

  4. Select A1:C5 and choose Insert > Table. Confirm My table has headers. Name the table TicketRecords under Table Design where that option is available.

  5. Add a Key column and enter =[@Team]&"|"&[@Month]. Check the four keys: Support|Jan and Sales|Feb should each appear once.

  6. Enter the formula below outside the table and check the total. Keep this summary outside the record rows so it cannot be counted as another observation.

    =SUM(TicketRecords[Tickets])

    English function names. Formula settings →

Try it yourself

  1. Append Support, Mar, 11 and Sales, Mar, 10 as new records. Check that the table extends and the Key formulas populate for the two added rows.
  2. Create a separate small report showing month totals. State whether the row grain in the report is the same as the row grain in TicketRecords, and explain why those tables should remain separate.
Show expected result
  • The original four records total 39 tickets. After adding March, six records total 60 and all six Team|Month keys are distinct.
  • Monthly totals are Jan 18, Feb 21 and Mar 21; a monthly report row combines two team records rather than representing a single source observation.

If something goes wrong

Excel detects only part of the record area.
Remove blank separator rows within the source, then check the table range. Use borders or table styles to group records visually.
Two rows share a key but have different ticket counts.
Check whether one row is a revision or whether the key needs another field. Keep both rows until you resolve their meaning against the original report.

Quick check

Try these questions after the exercise. Check the result in your app before marking the lesson complete.

1. What does one row of TicketRecords represent?
2. What is the total after adding March values 11 and 10?
3. Where should a grand-total row normally go for this source design?

Choose an answer for all 3 questions to check your understanding.

Optional: try it with Copilot

If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.

Review TicketRecords. What does one row represent? Check the headers, field types and Team|Month key before suggesting changes.

More from Microsoft

These Microsoft guides cover the tools used in this lesson.

Loading your progress…