Using Table Styles and Total Row Calculations0%

Using Table Styles and Total Row Calculations

Beginner12 min read•Updated: Jul 11, 2026

Using Table Styles and Total Row Calculations

Excel Tables combine visual appeal with analytical functionality. Built-in Table Styles provide polished, accessible color themes with a single click, while the Total Row delivers instant summary statistics powered by the intelligent 'SUBTOTAL' function.

1. Customizing Table Styles & Layout Options

When your active cell is within a Table, navigate to the Table Design ribbon tab to customize visual presentation:

Table Style Options (Checkboxes):

Header Row: Toggles visibility of column titles.
Total Row: Inserts or hides the summary row at the bottom.
Banded Rows: Shades alternating rows with contrasting tones to guide the eye across wide tables.
First Column: Emphasizes the leftmost column (frequently row labels or IDs) with bold text.
Last Column: Emphasizes the rightmost column (often row totals).
Banded Columns: Shades alternating columns (useful when rows are not banded).
Filter Button: Shows or hides the dropdown filter arrows on header cells.

1Table Styles Gallery:
2- Light Styles : Minimalist borders and soft pastel fills.
3- Medium Styles: High-contrast corporate palettes (Navy, Teal, Slate, Forest).
4- Dark Styles  : Bold headers with saturated accent bands.

2. Enabling and Configuring the Total Row

The Total Row provides instant mathematical summaries without manually typing formulas:

1
In the Table Design tab, check the box for Total Row (shortcut: Ctrl + Shift + T).
2
A new row labeled Total appears at the bottom of the table.
3
Click any cell in the Total Row; a drop-down arrow appears.
4
Select the desired aggregation function from the menu:

None: Leaves the cell blank.
Average: Calculates the arithmetic mean.
Count: Counts all entries ('COUNTA').
Count Numbers: Counts numeric entries only ('COUNT').
Max / Min: Finds the highest or lowest value.
Sum: Computes the total sum.
StdDev / Var: Computes standard deviation or variance.

3. Why the Total Row Uses SUBTOTAL Instead of SUM

When you inspect the formula generated by the Total Row, you will see:

1=SUBTOTAL(109, [Revenue])

Why doesn't it use '=SUM([Revenue])'?

Function code 109 specifies that hidden filtered rows must be excluded from the sum.
When you filter the table (e.g., viewing only the 'East Region'), the Total Row automatically recalculates to reflect only the visible filtered records!
If it used standard 'SUM', the total would stubbornly display the grand total of all records, regardless of active filters.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the keyboard shortcut to toggle the Total Row on and off in an active Excel Table?

Question 2

Which function does Excel use in the Total Row to automatically exclude rows hidden by filters?

Question 3

In the formula '=SUBTOTAL(109, [Sales])', what does the number 109 signify?

Question 4

How can you change a Total Row calculation from 'Sum' to 'Average'?

Question 5

What visual effect does checking 'Banded Rows' apply to an Excel Table?

Next Lesson

Highlighting Cells Based on Rules

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Structured References in Table Formulas	Highlighting Cells Based on Rules

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Highlighting Cells Based on Rules

Using Table Styles and Total Row Calculations

Beginner12 min read•Updated: Jul 11, 2026

Study Materials Cheatsheet

Using Table Styles and Total Row Calculations

1. Customizing Table Styles & Layout Options

When your active cell is within a Table, navigate to the Table Design ribbon tab to customize visual presentation:

Table Style Options (Checkboxes):

Header Row: Toggles visibility of column titles.
Total Row: Inserts or hides the summary row at the bottom.
Banded Rows: Shades alternating rows with contrasting tones to guide the eye across wide tables.
First Column: Emphasizes the leftmost column (frequently row labels or IDs) with bold text.
Last Column: Emphasizes the rightmost column (often row totals).
Banded Columns: Shades alternating columns (useful when rows are not banded).
Filter Button: Shows or hides the dropdown filter arrows on header cells.

1Table Styles Gallery:
2- Light Styles : Minimalist borders and soft pastel fills.
3- Medium Styles: High-contrast corporate palettes (Navy, Teal, Slate, Forest).
4- Dark Styles  : Bold headers with saturated accent bands.

2. Enabling and Configuring the Total Row

The Total Row provides instant mathematical summaries without manually typing formulas:

1
In the Table Design tab, check the box for Total Row (shortcut: Ctrl + Shift + T).
2
A new row labeled Total appears at the bottom of the table.
3
Click any cell in the Total Row; a drop-down arrow appears.
4
Select the desired aggregation function from the menu:

None: Leaves the cell blank.
Average: Calculates the arithmetic mean.
Count: Counts all entries ('COUNTA').
Count Numbers: Counts numeric entries only ('COUNT').
Max / Min: Finds the highest or lowest value.
Sum: Computes the total sum.
StdDev / Var: Computes standard deviation or variance.

3. Why the Total Row Uses SUBTOTAL Instead of SUM

When you inspect the formula generated by the Total Row, you will see:

1=SUBTOTAL(109, [Revenue])

Why doesn't it use '=SUM([Revenue])'?

Function code 109 specifies that hidden filtered rows must be excluded from the sum.
When you filter the table (e.g., viewing only the 'East Region'), the Total Row automatically recalculates to reflect only the visible filtered records!
If it used standard 'SUM', the total would stubbornly display the grand total of all records, regardless of active filters.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What is the keyboard shortcut to toggle the Total Row on and off in an active Excel Table?

Question 2

Which function does Excel use in the Total Row to automatically exclude rows hidden by filters?

Question 3

In the formula '=SUBTOTAL(109, [Sales])', what does the number 109 signify?

Question 4

How can you change a Total Row calculation from 'Sum' to 'Average'?

Question 5

What visual effect does checking 'Banded Rows' apply to an Excel Table?

Next Lesson

Highlighting Cells Based on Rules

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Structured References in Table Formulas	Highlighting Cells Based on Rules

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next