How to Format a Pivot Table in Google Sheets

A raw pivot table is powerful, but let's be honest - it isn't always pretty. When you need to share your findings with a manager, a client, or your team, a messy report can obscure the very insights you're trying to highlight. This guide will walk you through the essential formatting techniques in Google Sheets to transform your pivot tables from a raw data crunch into a polished, professional report that's easy to understand and act on.

First, a Quick Refresher: Creating Your Pivot Table

Before you can format a pivot table, you need one to work with. Let's start with a sample dataset of fictional product sales. Imagine you have a sheet with columns for Date, Region, Product, Units Sold, and Revenue.

Here’s a lightning-fast refresher on creating the pivot table we'll be formatting:

1. Click anywhere inside your data range.

2. Go to the main menu and select Insert > Pivot table.

3. Google Sheets will suggest a new sheet for your pivot table. Click Create.

4. The Pivot Table Editor pane will appear on the right. Let's build a simple view:

   • Under Rows, click "Add" and select Region.

   • Under Columns, click "Add" and select Product.

   • Under Values, click "Add" and select Revenue. This will automatically set it to SUM of Revenue.

You now have a functional pivot table showing total revenue for each product, broken down by region. It's useful, but the jumble of unformatted numbers and plain text could be much clearer.

Customize Number Formats for Clarity

The most crucial formatting step is ensuring your numbers are readable. Raw numbers like "18456.75" are hard to interpret at a glance. Let’s clean them up.

Applying Currency Formatting

If your values represent money, they should look like it. Displaying the right currency symbol and using thousand separators makes a huge difference in readability.

1. Select the entire range of numerical data within your pivot table, including the "Grand Totals." Make sure not to select the text headers or row labels.

2. Go to the main menu and click Format > Number > Currency.

Instantly, your numbers are transformed. 18456.75 becomes $18,456.75, which is immediately recognizable as currency.

Adjusting Decimal Places

Sometimes, two decimal places are more detail than you need. For high-level reports, rounding to the nearest dollar often provides a cleaner look.

• With your numbers still selected, look at the toolbar at the top of your sheet. You will find two icons:

  • Decrease decimal places (.0 <,-): Click this twice to remove the cents.

  • Increase decimal places (.00 ->): Click this to add them back if needed.

This simple adjustment can make your table feel less cluttered and draw the eye to the most important parts of the number.

Formatting Percentages or Other Numbers

If your pivot table summarizes values like percentages (e.g., "% of Grand Total"), you can format these just as easily:

• Select the data you want to display as a percentage.

• In the menu, go to Format > Number > Percent.

• Just like with currencies, you can use the toolbar icons to adjust the number of decimal points.

Improve Legibility with Themes and Colors

A wall of black text on a white background is hard on the eyes. Adding color helps guide the viewer through the data and makes the report feel more professional.

Apply a Sitewide Theme

Themes are the fastest way to get a coordinated, professional look. A theme applies a consistent set of fonts, colors, and styling rules to your entire spreadsheet, including your pivot table.

1. Click anywhere in your pivot table.

2. Go to Format > Theme. A sidebar with theme options will appear.

3. Click on different themes like "Simple," "Modern," or "Elegant" to preview them. Your pivot table will update instantly.

4. Once you find one you like, just close the Theme sidebar. The change is saved automatically.

This single action often takes care of font choices, header background colors, and more, giving your table a major facelift in seconds.

Use Alternating Colors for Readability

Banding your rows with alternating colors makes it much easier to scan long tables without losing your place. This feature is a game-changer for readability.

1. Select your entire pivot table, including headers and totals.

2. From the menu, go to Format > Alternating colors.

3. A sidebar will appear, automatically applying a default style. From here, you can:

   • Choose a different pre-made style from the list.

   • Customize the Header, Color 1, and Color 2 to match your company's branding or your personal preference.

Use Conditional Formatting to Highlight Key Insights

Formatting isn't just about making things pretty, it's about telling a story with your data. Conditional formatting is one of the most powerful tools for this, allowing you to automatically change a cell's appearance based on its value.

Let's use it to highlight your best-performing regions and products.

Highlight Top Performers with Color Scales

A color scale applies a gradient of colors to a range of cells, effectively creating a "heat map." This instantly shows highs and lows without the viewer needing to read a single number.

1. Select the numerical data in your pivot table (e.g., all the SUM of Revenue values).

2. Click Format > Conditional formatting to open the sidebar.

3. Under the "Format rules" section, click on the "Color scale" tab.

4. A default green color scale will be applied. Now you can customize it:

   • Preview: You can see a live preview of how the scale will look.

   • Minpoint, Midpoint, Maxpoint: You can set specific colors for your lowest, middle, and highest values. A common choice is red for the minimum, yellow for the midpoint, and green for the maximum, creating an intuitive RAG (Red/Amber/Green) status view.

5. Click Done. Now, right inside your table, the cells with the highest revenue will be shaded one color (e.g., dark green) and the lowest will be another (e.g., light green or red), instantly drawing attention to the extremes.

Setting Rules to Flag Specific Numbers

You can also create rules to format specific cells that meet your criteria - for example, flagging all revenue figures below a certain threshold.

1. Select the same range of revenue data.

2. Go back to Format > Conditional formatting but stay on the "Single color" tab.

3. Under "Format rules," use the dropdown to set your condition. For example:

   • Choose "Is less than" in the Format cells if dropdown.

   • Enter a value, like 10000.

4. Under "Formatting style," choose how you want these cells to look. You can make the text bold, turn the background red, or choose any combination.

5. Click Done. Google Sheets will now automatically flag any product-region combo that brought in less than $10,000, alerting you to underperforming areas.

Final Touches for a Polished Report

With the major formatting work done, a few final adjustments to layout and headers will complete your professional-looking report.

Rename Your Headers

By default, your pivot table might have clunky headers like "SUM of Revenue." Let's fix that.

• Simply click on the header cell inside the pivot table (e.g., the cell that says "SUM of Revenue") and type a clearer, more concise name like "Total Revenue." This doesn't change the calculation, only the label.

• You can also edit headers directly in the Pivot Table Editor. Just find the analyzed value under the "Values" section and type your new name there.

Adjust Alignment and Resizing

Clean alignment gives a report a more structured feel. Generally, text labels look best left-aligned, while numbers should be right-aligned to make them easier to compare.

• Alignment: Select the cells you wish to align. In the toolbar, you'll find icons for horizontal alignment (left, center, right) and vertical alignment (top, middle, bottom).

• Column Sizing: You can resize columns to fit your data perfectly. Hover your cursor over the line between column letters (e.g., between B and C) until a double-sided arrow appears. You can then:

  • Click and drag to manually resize the column.

  • Double-click to auto-size the column to fit the widest content within it.

Wrapping Headers

Long headers like "Q4 2024 Projected Sales Performance" can stretch a column and mess up your entire layout. Enable text wrapping so the header text neatly flows onto multiple lines.

1. First, highlight the cells that contain the headers you want to fix.

2. Click Format > Wrapping in the main menu.

3. Finally, click Wrap in the dropdown and just like that, your headers will be formatted for better visual clarity.

Final Thoughts

By applying number formatting, using stylesheets and alignment, cleaning up headers, and leveraging conditional formulas for emphasis, you can transform a sterile grid of numbers into a clear, compelling report. These simple steps don't just organize your analysis - they elevate it, making it easier for others to quickly absorb your insights and make better decisions.

As you build more reports, this process of connecting data and creating clean visuals can become repetitive. When we found ourselves spending hours each week pulling data and manually formatting pivot tables, we knew there had to be a better way. With Graphed, we can connect directly to data sources like Google Analytics, Shopify, or Salesforce and use natural language to create reports on the fly. Asking "show me revenue by product and region for last quarter" instantly gives us a live-updating, perfectly formatted dashboard, replacing frustrating busywork with actionable insights in seconds.