Insert and Configure Total Rows

Why Total Rows matter on MO-210

After you create a table, Domain 3 often asks you to insert a Total Row and set specific summary calculations. Typical project language includes "Display the Total Row and show the sum of the Amount column", "In the Total Row, calculate the average of UnitPrice", or "Show a count of entries in the OrderID column."

This is not the same as typing =SUM(D2:D50) below a plain range. Graders look for a real table Total Row with the correct function selected in the total cell. A hand-built SUM one row below a range may look correct to you and still fail the task.

Turning the Total Row on

1. Click any cell in the table.
2. Open Table Design.
3. In Table Style Options, check Total Row.

Excel adds a row labeled Total (or a localized equivalent) at the bottom of the table. By default, Excel often places a Sum in the rightmost numeric column and leaves other total cells blank or set to None—behavior can vary with column types, so always open the dropdown on each column the project names.

You can also right-click the table and use table commands that include Total Row on many builds. Prefer Table Design so you can see all style options at once.

Using the total-cell dropdown

Click a cell in the Total Row (not the "Total" label cell unless that is where Excel placed a function). A dropdown arrow appears. Open it to choose:

Select the function named in the project. If the project says average, do not leave Sum. If it says count of orders, decide whether Count or Count Numbers fits; for ID/text columns, Count is usually correct because Order IDs may be stored as text.

Structured references and SUBTOTAL

When you pick Sum from the dropdown on a column named Amount, Excel typically inserts a formula similar to:

=SUBTOTAL(109,[Amount])

Key points for the exam:

• Structured references use the column name in brackets ([Amount]), not D2:D100.
• SUBTOTAL with function numbers in the 101–111 set ignores rows hidden by filters, so totals update sensibly when you filter the table.
• Function number 109 corresponds to SUM; other numbers map to AVERAGE, COUNT, and so on. You do not need to memorize every number for MO-210 if you use the dropdown, but you should recognize that a total formula is a SUBTOTAL structured reference, not a plain =SUM(D:D).

If you type a normal =SUM([Amount]) yourself, it may calculate, but filtered rows are still included—unlike the dropdown’s SUBTOTAL approach. Prefer the dropdown unless the project explicitly requires a different formula.

Multiple columns, one Total Row

One Total Row serves the whole table. Configure each needed column separately:

• Amount → Sum
• UnitPrice → Average
• SKU → Count

Leave unrelated columns on None so you do not invent summaries the project did not ask for. Extra totals are rarely penalized for appearance alone, but wrong functions in the wrong columns are.

Total Row versus worksheet totals below the table

When the table grows (new row typed in the last data row), the Total Row stays at the bottom and structured references expand. That automatic expansion is part of why MOS prefers Total Rows over static ranges.

Turning Total Row off and on again

Clearing Total Row removes the summary row. Checking it again brings a Total Row back; you may need to reselect aggregates if the project previously set custom choices. If a multi-step project says to remove totals later, use the check box—do not delete the row with Home → Delete in a way that damages table structure.

Worked exam-style scenario

Table Sales has columns Region, Rep, Units, Revenue. Instructions: "Display the Total Row. Show the sum of Revenue and the average of Units."

Steps:

1. Click inside Sales → Table Design → check Total Row.
2. In the Revenue total cell, open the dropdown → Sum.
3. In the Units total cell, open the dropdown → Average.
4. Leave Region and Rep as None (or whatever default text total Excel left—change to None if a Sum appeared incorrectly on a text column).

Confirm formulas show structured references to [Revenue] and [Units].

Suggested practice sequence

1. Build a table with mixed text and number columns.
2. Enable Total Row; set Sum, Average, and Count on three different columns.
3. Filter the table to one region and watch SUBTOTAL results change.
4. Disable Total Row, then re-enable and restore the required functions.

Those four drills cover insert, configure, filter interaction, and toggle recovery for Total Row tasks on MO-210.