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

Sharing is caring!

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. Manually Bold Subtotals

If you prefer a more customized approach or if your pivot table style does not bold subtotals, you can manually format the subtotal rows.

1. Click anywhere inside the pivot table.
2. In the PivotTable Analyze tab, click on Field Settings for the field where subtotals appear.
3. In the Subtotals & Filters section, ensure subtotals are turned on.
4. Now, locate the subtotal rows in your pivot table.
5. Select the subtotal row(s), right-click, and choose Format Cells.
6. In the Font tab, check the Bold option and click OK.

This will bold the subtotal rows while keeping the rest of the pivot table unchanged.

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 simple VBA script to bold all subtotal rows in your pivot table:

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

    ' Reference the pivot table
    Set pt = ActiveSheet.PivotTables(1)

    ' Loop through each pivot field
    For Each pf In pt.RowFields
        ' Loop through each pivot item
        For Each pi In pf.PivotItems
            ' If it's a subtotal, apply bold formatting
            If pi.RecordCount > 1 Then
                pi.DataRange.Font.Bold = True
            End If
        Next pi
    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. Check the box labeled Subtotal Filter Page Items to ensure subtotals appear where needed.
5. You can also adjust the display of subtotals to appear either above or below data.

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, you can either use built-in PivotTable styles, manually format the subtotal rows, or apply conditional formatting for dynamic tables.

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?

If you manually bold the subtotals, the formatting may reset when the pivot table updates. To avoid this, use PivotTable styles or conditional formatting, which will maintain the bolding even when the table is refreshed.

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.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.