Lesson 2.11 – Pivot Table Formatting in Excel

Lesson Overview

Formatting Pivot Tables improves readability, presentation, and clarity of data. Excel provides multiple options to customize the look and feel without altering the underlying data.

1. Apply a Pivot Table Style

• Steps:

  1. Select any cell in the Pivot Table.

  2. Go to PivotTable Analyze → Design → PivotTable Styles.

  3. Choose a built-in style (Light, Medium, Dark) or create a custom style.

Tips:

• Use alternating row colors for better visibility.

• Highlight total rows for clarity.

2. Format Numbers

• Format numeric values to display currency, percentage, or decimal places.

• Steps:

  1. Right-click a value in the Pivot Table → Number Format

  2. Choose desired format (Currency, Number, Percentage, Date, etc.)

  3. Click OK

Example: Show sales in ₹ currency with two decimal places.

3. Adjust Column Width & Row Height

• Auto-adjust column width: Double-click the right edge of the column header.

• Manually adjust row height for better spacing.

4. Change Report Layout

• Go to Design → Report Layout

• Options:

  1. Compact Form: Default, saves space, all fields in one column

  2. Outline Form: Shows fields in separate columns, easier to read

  3. Tabular Form: Each field in its own column, best for printing

5. Show/Hide Subtotals and Grand Totals

• Subtotals: Design → Subtotals → Show All / Do Not Show

• Grand Totals: Design → Grand Totals → On for Rows, Columns, Both, or Off

6. Apply Conditional Formatting

• Highlight key data with colors or data bars.

• Steps:

  1. Select Pivot Table values → Home → Conditional Formatting

  2. Choose formatting type: Color Scales, Data Bars, Icon Sets

Example: Highlight top 10 sales values in green.

7. Expand/Collapse Groups

• Click + or – symbols on grouped rows to show or hide details

• Useful for drilling down into specific categories

8. Tips for Effective Formatting

• Keep formatting consistent for reports

• Avoid excessive colors which may distract

• Use Bold for headers and totals

• Freeze top row or first column if needed for large tables

9. Real-Life Examples

• Sales report: Highlight top-performing products

• HR data: Emphasize departments with highest employee count

• Finance report: Format amounts in currency and highlight negative values

Practice Questions

1. How do you apply a built-in Pivot Table style?

2. Which number formats are useful for financial data?

3. What is the difference between Compact, Outline, and Tabular layouts?

4. How do you show or hide subtotals and grand totals?

5. Give an example where conditional formatting improves Pivot Table readability.