How To Bold Subtotals in Excel Pivot Table? (4 Easy Methods)

Microsoft Excel is one of the most powerful tools for data analysis, and pivot tables are a central feature that allows users to easily summarize, analyze, and present data. One frequent request by users is how to bold subtotals in a pivot table for better readability and emphasis. In this article, we’ll walk you through the steps to achieve that.

What Are Subtotals in Excel Pivot Tables?

Subtotals in Excel pivot tables summarize data across categories. For example, if you have sales data from different regions, Excel can automatically generate subtotals for each region’s sales, which makes the data easier to analyze.

By default, Excel does not bold subtotals, but doing so can make your data easier to read, especially in large tables. We’ll explain several methods to achieve bolding, including using built-in pivot table styles, manually formatting cells, and using VBA (Visual Basic for Applications).

Why Bold Subtotals in Pivot Tables?

Before we dive into the technical details, let’s understand the advantages of bolding subtotals in a pivot table:

• Improved readability: Bolding subtotals helps users easily distinguish summary data from detailed data.
• Enhanced presentation: Bolding key figures, like subtotals, makes your pivot table visually appealing and professional.
• Focus on key insights: When sharing reports, bold subtotals draw attention to important figures.

Step-by-Step Guide: How to Bold Subtotals in an Excel Pivot Table

1. Format Subtotals with Pivot Table Styles

Excel provides several built-in pivot table styles that can automatically bold subtotals. This method is quick and does not require manual formatting. Here’s how you can do it:

1. Select the pivot table.
2. In the ribbon, go to the Design tab.
3. In the PivotTable Styles group, click the dropdown arrow to see the available styles.
4. Hover over different styles to see the live preview. Look for a style that has bold subtotals.
5. Once you find a suitable style, click to apply it to your pivot table.

Many built-in pivot table styles include bold formatting for subtotals. For example, the Medium and Dark styles often bold subtotal rows.

2. Select All Subtotals at Once and Bold Them

This is the most reliable manual method, because the formatting stays with the subtotals when the pivot table is refreshed or new items are added:

1. Click in the pivot table and go to PivotTable Analyze > Actions > Select, and make sure Enable Selection is on.
2. Point at the left edge of any subtotal row (for example “North Total”) until the pointer becomes a small black arrow, then click. Excel selects every subtotal row of that field.
3. Press Ctrl + B (or click Bold on the Home tab).

Also keep Preserve cell formatting on update ticked in PivotTable Options > Layout & Format. If you format individual cells without Enable Selection, the bold can disappear when the layout changes.

3. Format Subtotals Using Conditional Formatting

Another approach is to use Conditional Formatting to automatically bold subtotal rows. This method is particularly useful if your pivot table is dynamic and you expect the number of subtotal rows to change frequently.

1. Select your entire pivot table.
2. In the ribbon, go to the Home tab and click on Conditional Formatting.
3. Choose New Rule.
4. In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
5. Enter the following formula (assuming subtotals are in Column A):

=ISNUMBER(SEARCH("Total",$A1))

6. Click on Format, and in the Font tab, select Bold.
7. Click OK to apply the rule.

This will automatically bold any row that contains the word “Total”, which typically indicates a subtotal row.

4. Use VBA to Bold Subtotals in Excel Pivot Table

If you work with large datasets or multiple pivot tables, you may want to automate the bolding of subtotals using VBA. This is a more advanced approach but can save time in the long run.

Here’s a VBA macro that bolds the subtotal rows of every row field in the first pivot table on the active sheet:

Sub BoldSubtotals()
    Dim pt As PivotTable
    Dim pf As PivotField

    Set pt = ActiveSheet.PivotTables(1)

    For Each pf In pt.RowFields
        On Error Resume Next   ' skip fields without subtotals (e.g. the innermost field)
        pt.PivotSelect "'" & pf.Name & "'[All;Total]", xlDataAndLabel, True
        If Err.Number = 0 Then Selection.Font.Bold = True
        Err.Clear
        On Error GoTo 0
    Next pf
End Sub

Steps to Run the VBA Script:

1. Press Alt + F11 to open the VBA editor.
2. In the VBA editor, click Insert > Module.
3. Copy and paste the code above into the module.
4. Press F5 to run the code.

This script will bold all subtotals in the first pivot table on the active worksheet. You can modify the script to handle multiple pivot tables or customize it further based on your needs.

Customizing Subtotal Formatting in Excel Pivot Tables

Aside from bolding, you may want to apply additional formatting to your subtotals. Excel allows you to customize:

• Font style (italic, underline)
• Font color
• Background color for subtotal rows

Here’s how you can do it:

Steps:

1. Select the subtotal rows.
2. Right-click and choose Format Cells.
3. In the Font tab, choose a new color or style for the subtotal text.
4. In the Fill tab, apply a background color.

You can repeat this process to further enhance the visual appearance of your pivot table and ensure that subtotals stand out. To further enhance the visual appearance of your pivot table, you can also consider adjusting the font size, style, and color to make the subtotals stand out. Additionally, you can experiment with different formatting options such as bolding or italicizing the subtotal values to make them more noticeable. In addition, removing subtotals in excel pivot table can help simplify the table’s appearance and make it easier for viewers to interpret the data.

Using the PivotTable Options for More Control

If you’re looking for more control over how subtotals are displayed, PivotTable Options provide additional settings that can be useful.

1. Click on your pivot table.
2. Go to the PivotTable Analyze tab and select Options.
3. In the PivotTable Options dialog box, navigate to the Layout & Format tab.
4. Make sure Preserve cell formatting on update is ticked, so your bold subtotals survive refreshes.
5. To move subtotals above or below each group, use Design > Subtotals > Show all Subtotals at Top (or Bottom) of Group.

This feature allows for more control over how the pivot table is laid out and how subtotals are positioned and formatted.

Example of Bolding Subtotals in Pivot Table (Table Example)

Here’s an example of how bolding subtotals can improve the appearance and readability of a pivot table:

In the table above, you can see how bolding the subtotal rows helps to visually differentiate between detailed and summary data.

Common Issues When Bolding Subtotals in Pivot Tables

While bolding subtotals in Excel pivot tables is usually straightforward, you might encounter a few challenges:

• Dynamic Pivot Tables: If your pivot table is updated regularly, manually bolded subtotals may lose formatting. Using pivot table styles or conditional formatting can help prevent this issue.
• Multiple Levels of Subtotals: If you have several levels of subtotals (e.g., region, product), Excel may not automatically bold all levels. You can manually bold each level or adjust your pivot table design to accommodate multiple subtotal formats.
• VBA Errors: If using VBA, ensure that the correct pivot table is referenced in your code. It’s important to debug your script for any specific worksheet or field names.

Final Thoughts

Bolding subtotals in an Excel pivot table significantly enhances the presentation and readability of your data. Whether you choose to use built-in styles, manually format subtotals, or leverage VBA for automation, there are multiple ways to achieve the desired result.

Remember to experiment with different formatting options to find the one that best fits your report. With the ability to bold, color, and emphasize subtotals, your pivot tables will not only look more professional but also make data analysis more effective.

FAQs

How do I bold subtotals in an Excel Pivot Table?

To bold subtotals in an Excel Pivot Table, turn on PivotTable Analyze > Select > Enable Selection, click the left edge of a subtotal row to select all subtotals of that field, and press Ctrl + B. You can also use a PivotTable style that bolds subtotals, or conditional formatting.

Can I use VBA to bold subtotals in an Excel Pivot Table?

Yes, you can use VBA to automate the process of bolding subtotals. You can write a VBA script that loops through the pivot table fields and bolds the subtotal rows.

Why should I bold subtotals in a Pivot Table?

Bolding subtotals improves readability, highlights key data summaries, and enhances the presentation of your pivot table by making important figures stand out.

What is the easiest way to bold subtotals in a Pivot Table?

The easiest way to bold subtotals is by using Excel’s built-in PivotTable styles. You can choose a style that automatically bolds subtotals for you.

Will bolding subtotals stay when I update the Pivot Table?

Yes, if you select the subtotals with Enable Selection before bolding them and keep Preserve cell formatting on update ticked in PivotTable Options. Formatting applied to individual cells may be lost when the layout changes.

Can I customize subtotal formatting in Pivot Tables beyond bolding?

Yes, you can customize subtotal formatting by changing the font color, background color, or applying other text styles like italic or underline in addition to bolding.