Excel: Create Scatter Charts from Pivot Tables

Do you need to visualize data from a pivot table in Excel, but find yourself overwhelmed by the numerous chart types? A scatter chart might be just what you're looking for. Scatter charts, also known as X-Y charts, are perfect for displaying the relationship between two sets of data. They're especially useful when you want to compare data points or identify trends. Let's dive into how to create an Excel scatter chart from a pivot table.

Before we begin, ensure your pivot table is set up correctly. It should have at least two columns of numerical data that you want to compare. For this guide, let's assume you have a pivot table with 'Sales' and 'Profit' data from different regions.

Preparing Your Pivot Table

Before creating a scatter chart, ensure your pivot table is set up correctly. It should have at least two columns of numerical data that you want to compare. For this guide, let's assume you have a pivot table with 'Sales' and 'Profit' data from different regions.

To prepare your pivot table, follow these steps:

• Select any cell in your pivot table.
• Go to the 'Design' tab under 'PivotTable Tools'.
• Click on 'Select Data' in the 'Tools' group.
• In the 'Select Data Source' dialog box, ensure the correct data range is selected.
• Click 'OK'.

Adding Fields to Axes

Now that your pivot table is set up, it's time to add fields to the axes of your scatter chart. Here's how:

1. Select any cell in your pivot table.
2. Go to the 'Insert' tab under 'Home'.
3. Click on 'Scatter' in the 'Charts' group. Choose the scatter chart type that suits your data best (e.g., Scatter with Smooth Lines and Markers).
4. In the 'Create Scatter Chart' dialog box, under 'Horizontal (Category) Axis', click the 'Add' button and select the field you want on the x-axis (e.g., 'Region').
5. Under 'Vertical (Series) Axis', click the 'Add' button and select the field you want on the y-axis (e.g., 'Sales').
6. Click 'OK'.

Formatting Your Scatter Chart

Your scatter chart is now created, but it might not be visually appealing yet. Let's format it to make it more engaging:

1. Click on any data point in your chart to select it.
2. Go to the 'Format Selection' tab under 'Chart Tools'.
3. In the 'Format Selection' group, click on 'Shape Fill' to change the color of your data points.
4. Click on 'Shape Outline' to change the color of the lines connecting your data points.
5. To add a title to your chart, click on the 'Layout' tab under 'Chart Tools'. In the 'Labels' group, click on 'Chart Title' and enter your title.

Updating Your Scatter Chart

One of the benefits of creating a scatter chart from a pivot table is that it updates automatically when you refresh or modify your pivot table. Here's how to refresh your chart:

Simply select any cell in your pivot table and click the 'Refresh' button in the 'Data' tab under 'Home'. Your scatter chart will update to reflect the new data.

Filtering Your Scatter Chart

You can also filter your scatter chart to display only the data you're interested in. Here's how:

1. Click on any data point in your chart to select it.
2. Go to the 'Format Selection' tab under 'Chart Tools'.
3. In the 'Format Selection' group, click on 'Show Data Labels' to display the data labels for your chart.
4. Click on the dropdown arrow in the data label of the data point you want to filter. Uncheck the other data points to filter your chart.

And there you have it! You've successfully created, formatted, and filtered a scatter chart from a pivot table in Excel. This powerful combination of tools can help you gain valuable insights from your data. Happy charting!