PathTailor
FRSign inBuild my path
PathTailor
Build my pathSign inLire en français

Putting Your Data in a Table

First lesson of the Excel: PivotTables course, free to read: about 15 minutes, 3 exercises and 3 quiz questions. This is the catalogue's base lesson; in the student space, it is rewritten for your profile.

Objectives

  • Prepare a data source that a PivotTable can use
  • Turn a range into a structured table and name it
  • Use calculated columns, the total row and structured references

List-format dataStructured tableStructured references

The list format, the condition for everything else

A PivotTable (tableau croisé dynamique, TCD) only summarizes a list of data. If the list is badly built, the PivotTable will be wrong or impossible to create. A good source follows the list format:

  • a single header row, with unique, non-empty names;
  • one row per record (a sale, an invoice, a ledger entry);
  • one column per piece of information (Date, Region, Salesperson, Category, Quantity, Price);
  • no empty row or column, no merged cell;
  • no subtotal or total in the data: the PivotTable will calculate them;
  • a single type per column: real dates, real numbers.

A frequent flaw is the “presentation” table, with months in columns (January, February, March…). It reads well but summarizes poorly. The list format, with a Date column, is what you must feed the PivotTable.

Turning the range into a table

Click in the data, then Insert > Table (shortcut Ctrl+L in the French version of Excel, Ctrl+T in the English version). Check that the My table has headers box is ticked. The range becomes a structured table: banded rows, filter arrows, and a Table Design tab in the ribbon.

First action: give the table a name, in Table Design > Table Name, for example Ventes (no spaces). This name will be used in formulas and as the PivotTable’s source.

Decisive advantages:

  • the table expands automatically when you type a row right below it or a column right next to it;
  • a PivotTable built on Ventes will take new rows into account at the next refresh, without changing its source;
  • the headers stay visible in place of the column letters when you scroll.

Calculated columns and structured references

Add the header Montant in G1: the column joins the table. In G2, type = then click E2, *, then F2. Excel writes:

=[@Quantite]*[@PrixUnitaire]

This is a structured reference: [@Quantite] means “the value of the Quantite column on this row”. On confirmation, the formula is automatically copied down the whole column: it is a calculated column. It reads like a sentence and does not depend on where the cells are located.

Outside the table, you designate a whole column with TableName[Column]:

=SOMME(Ventes[Montant])                               -> 22820
=SOMME.SI.ENS(Ventes[Montant];Ventes[Region];"Nord")  -> 10680

These formulas stay correct as the table grows: no more need to change G2:G21 to G2:G22.

The total row

Tick Table Design > Total Row. A row appears below the table; each cell offers a list (Sum, Average, Count, Max…). It uses SOUS.TOTAL (SUBTOTAL): it therefore follows the table’s filters. Untick it before creating a PivotTable if you prefer, but it is not counted in the PivotTable’s data.

Checking before crossing

Before moving on to the PivotTable, check: dates are right-aligned (real dates), quantities and prices too (real numbers), no label varies (Nord and nord would give two distinct rows in the PivotTable). A filter on each column lets you spot badly entered values at a glance.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

  1. TaskDifficulty 12 hints

    Create the Ventes table

    Create the file C:\Formation\ventes_s1.csv with Notepad, paste in the content below, save it, then open it in Excel by double-clicking it. The data occupies the range A1:F21. If everything lands in column A, use Data > Text to Columns with the semicolon as the delimiter. Then save the workbook as ventes_s1.xlsx.

    Date;Region;Commercial;Categorie;Quantite;PrixUnitaire
06/01/2026;Nord;Martin;Logiciel;4;300
12/01/2026;Sud;Bernard;Formation;2;450
19/01/2026;Est;Petit;Materiel;15;80
27/01/2026;Nord;Leroy;Maintenance;5;120
03/02/2026;Sud;Moreau;Logiciel;3;300
10/02/2026;Est;Petit;Formation;1;450
17/02/2026;Nord;Martin;Materiel;20;80
24/02/2026;Sud;Bernard;Maintenance;8;120
04/03/2026;Nord;Leroy;Logiciel;6;300
11/03/2026;Est;Petit;Logiciel;2;300
18/03/2026;Sud;Moreau;Formation;3;450
25/03/2026;Nord;Martin;Maintenance;4;120
07/04/2026;Est;Petit;Materiel;10;80
14/04/2026;Sud;Bernard;Logiciel;5;300
21/04/2026;Nord;Leroy;Formation;2;450
05/05/2026;Sud;Moreau;Materiel;12;80
12/05/2026;Nord;Martin;Logiciel;7;300
19/05/2026;Est;Petit;Maintenance;6;120
09/06/2026;Sud;Bernard;Formation;4;450
23/06/2026;Nord;Leroy;Materiel;25;80
    1. Turn the range into a table with headers and name it Ventes.
    2. In G1, add the Montant column calculated by =[@Quantite]*[@PrixUnitaire].
    3. Turn on the total row and display the sum of Montant and of Quantite.
    4. Below the table, enter the row 30/06/2026;Est;Petit;Logiciel;1;300 (one value per column) and observe the total row.
    5. Delete this test row (right-click > Delete > Table Rows), turn off the total row and save as ventes_s1.xlsx.
    • The table is named Ventes (Table Design > Table Name)
    • G2 contains =[@Quantite]*[@PrixUnitaire] and the formula propagated down to G21
    • The total row displays 22820 for Montant and 144 for Quantite
    • After adding the test row, that sale's Montant displays 300 and the total 23120, with no manual copying

  2. TaskDifficulty 22 hints

    Formulas with structured references

    Start from the ventes_s1.xlsx workbook prepared in lesson 1: a table named Ventes (A1:G21) with the calculated column Montant (total 22 820). If you do not have it, redo exercise 1 of lesson 1.

    In a free area to the right of the table (for example I1:J4):

    1. In I1, enter Total and in J1, calculate the sum of the Montant column with a structured reference.
    2. In I2, enter Nord and in J2, calculate the total amounts of the Nord region with SOMME.SI.ENS and structured references.
    3. In I3, enter Ventes Logiciel and in J3, count the sales of the Logiciel category.
    4. In I4, enter Prix moyen pondéré and in J4, divide the sum of the amounts by the sum of the quantities.
    • No formula contains an address like G2:G21: only Ventes[...]
    • J1 displays 22820 and J2 displays 10680
    • J3 displays 6
    • J4 displays 158,47 (with two decimals)

  3. Multiple choiceDifficulty 11 hint

    A well-built source

    Which of these flaws prevents grouping dates by month in a PivotTable?

    • The table has banded rows
    • Some dates are entered as text
    • The table is named Ventes
    • The Montant column is calculated

End-of-lesson quiz

  1. Which shortcut creates a table in the French version of Excel?

    • Ctrl+L
    • Ctrl+Maj+L
    • Alt+F5

  2. What does [@Quantite] mean in a table formula?

    • The sum of the Quantite column
    • The value of the Quantite column on the same row
    • The first cell of the column

  3. Why not leave subtotals in the source data?

    • They slow Excel down
    • The PivotTable would add them to the detail rows and double the amounts
    • They prevent saving

The rest of the course

  1. Creating and Organizing a PivotTable20 min
  2. Calculated Fields and Grouping20 min
  3. Slicers and Timelines15 min
  4. PivotCharts15 min
Build my pathSee the full syllabus

Excel: PivotTables · Intermediate · 1 h 25 min