PivotTable reports: Format sales reports

As the saying goes, "a picture is worth a thousand words." You can tell your manager about your sales numbers, but a clear report or chart can do the job even better. With Microsoft Office Excel 2003, you can add eye-catching formatting to your reports and charts in minutes.

In this article, you'll learn tips for using formatting to make your data stand out. You'll use data from the PivotTable Report - Formatting sample datasheet to learn how to:

  • Format a Microsoft PivotTable® report automatically.
  • Create a Microsoft PivotChart® report, name it, and name the category (x) axis and value (y) axis.
  • Change the font in a chart.
  • Change the range of values shown in a chart.
  • Optimize a chart for printing in black and white.

What's the best way to present your report or chart?

Consider these factors as you decide how to present your report or chart:

  • Size of chart and distance from audience A chart that looks fine on your super-size desktop monitor may be hard to read on your manager's laptop—and it may be totally indecipherable from the back of the room in a presentation. A simple, clearly labeled, high-contrast chart is your safest bet.
  • Resolution Chart elements such as shading don't always look the same projected on a screen or printed at a low resolution. If possible, preview your chart the way your audience will see it.
  • Audience needs and level of understanding Think about what your audience knows already and what they need to know, and then plan and label your chart so there is no chance for confusion. Some audiences can understand a more complex chart; when in doubt, keep it simple.
  • Color or black-and-white output Optimize your chart colors and contrast levels based on how the chart will be viewed or printed.

Format a PivotTable report automatically

This simple PivotTable report shows actual sales versus projected sales for four quarters. Say you want to send this report along to your manager, but you realize that it looks unpolished and hard to read. Worse, it's not even obvious that you beat your sales goals every quarter. You want to use formatting to emphasize the differences between projected sales and actual sales.

PivotTable report

Let's make this report not only look more professional, but show off your sales results better.

  1. On the PivotTable toolbar, click the Format Report button.

PivotTable toolbar and Format Report button

  1. The AutoFormat dialog box opens, which gives you a host of format options. Select Report 1 for this example, and then click OK.

AutoFormat dialog box

Pretty as a picture!

Note that you don't have to do anything to rearrange the Sales and Projected columns. When you choose Report 1, Excel rearranges them for you. With the Sales and Projected numbers in separate columns and the Grand Total in bold, your sales results are hard to miss. Your PivotTable report is formatted professionally— easy to read, and ready to go.

Now, you can copy and paste the report directly into an e-mail message and send it to your manager.

Formatted PivotTable report

Create a chart with the Chart Wizard

Now you want to make a chart out of your PivotTable report and format it to help emphasize sales results.

  1. On the PivotTable toolbar, click the Chart Wizard button Button image.

The PivotChart Wizard automatically creates a chart based on default settings.

Chart with Sum of Projected and Sum of Sales columns stacked vertically

  1. By default, the Sum of Sales and Sum of Projected columns are stacked on top of one another. It's hard to compare projected and actual sales this way, so you'll change the chart to show the columns side by side. While you're at it, you'll name the chart, the category (x) axis, and the value (y) axis.


  1. Click the Chart Wizard button Button image again, choose the first Chart sub-type (highlighted in black), and then click Next.

Chart Wizard - Step 1 of 4 - Chart Type

  1. On the Titles tab, do the following:
    1. For Chart title, type Actual vs. Projected Sales FYE 12/31/05.
    2. For Category (X) axis, type Quarters.
    3. For Value (Y) axis, type Sales (in millions).
    4. Click Finish.

Chart Wizard

Don't worry about the proportions in this chart preview—just make sure the title and axes show the names you want.

  1. To insert a line break in the title, click between Sales and FYE, delete the space, and then press CTRL+ENTER.

Comparable, clear — and useful

Now it's easy to compare projected and actual sales in side-by-side columns. And the category (x) axis and value (y) axis names make it clear what is being compared.PivotChart report with sales data

Change the font in your chart

Speaking of labels, you may want to make the chart text more readable. You can format any fonts on your chart.

  1. Click the chart text (for example, Quarters), or select the individual characters you want to format.

 Note   If a chart title or text box is linked to a worksheet cell, you can change the formatting of all characters in the title or text box at the same time, but you cannot change individual characters.

To change the formatting of all of the text in the chart at the same time, click the blank area between the border of the chart and the plot area to select the chart area (chart area: The entire chart and all its elements.).

  1. On the Formatting toolbar, click a button for the format you want to use. For example, if you want to make your text bold, click Bold on the toolbar.


To see other font options, such as superscript and subscript:

  1. Click the text you want to change.
  2. On the Format menu, click the command for the chart item you want to format— for example, Selected Axis Title or Selected Object. The name of the command depends on the chart item selected.
  3. On the Font tab, select the options you want to use.

The following example shows the axis descriptions changed to 12 point, dark blue, and bold, and the title of the chart changed to 20 point, dark blue, and bold.

PivotChart sales report

Change the range of values shown in a chart

By default, the chart shows values from zero to a maximum major unit higher than the highest value in the chart. To make the improvements in sales figures stand out, you can change the range of values you show in your chart.

  1. Right-click a value on the Y axis, and then click Format Axis.
  2. Click the Scale tab.
  3. In the Minimum box, type 1.6.
  4. In the Maximum box, type 2.6, and then click OK.

By showing a smaller range of values, you emphasize the differences between projected and actual sales, and how sales have increased over time.

PivotChart with modified range

Optimize a chart for printing in black and white

Colors that look great on your screen may not show much contrast when you print them out in black and white to give to your sales manager. Before you print a chart, you can quickly optimize it for black-and-white printing. For example, this chart was created in color, but lacks contrast in black and white.

PivotChart Report print preview

You don't need to change each of the bar colors to make them stand out better. To optimize the chart for printing in black and white:

  1. On the File menu, click Print Preview.
  2. Click Setup.
  3. On the Chart tab, select Print in black and white, and then click OK.

The chart is automatically shown in higher contrast for black and white printing. You can see how it looks in black and white in the preview, but the colors still display in normal view.

PivotChart Report optimized for black and white printing

Formatting isn't just window dressing for your reports and charts. Good planning and the best formatting for your presentation can make a difference in how your sales results are understood, perceived—and remembered.

Applies to:
Excel 2003