Yes—but not as a PivotChart: Excel does not offer an XY scatter chart as a PivotChart type. Create a regular scatter chart from two numeric PivotTable results, preferably by linking them into a small helper table, then select that table and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter.
Why a scatter chart needs a workaround
A PivotTable is designed to summarize and rearrange data. A PivotChart is tied to that summary and changes when you filter or reorganize it. However, Excel’s PivotChart options do not include XY scatter, bubble, or stock charts. That is why choosing a chart directly from the PivotTable may not give you the scatter option you want.
The workaround is to chart the PivotTable’s results as an ordinary chart. A standard scatter chart uses one numeric field for horizontal positions and another numeric field for vertical positions. For example, each point could represent a product, with average selling price on the x-axis and average monthly units sold on the y-axis. The PivotTable supplies the summarized values; the regular chart plots them.
This distinction matters when the PivotTable changes. A normal chart is not a PivotChart, so it does not automatically inherit PivotTable-specific behavior such as its field buttons. It can still reflect changes to cells it references, but its plotted range may need attention if the summary grows or shrinks.
Check that the data suits an XY scatter plot
Before building the chart, decide what one point should represent and identify a meaningful numeric measure for each axis. Scatter charts are useful for exploring relationships between two quantities, such as advertising spend and sales, test score and study hours, or product price and units sold. They show whether values tend to move together, spread out, or include unusual points.
A text label such as a product name is useful for identifying points, but it is not a numeric x-value. Excel scatter charts position points on a numeric horizontal scale. If your PivotTable has categories in rows and only one numeric measure, a column or bar chart is usually a clearer choice. Do not turn category names into arbitrary numbers just to make a scatter chart; the resulting horizontal distances would imply a numeric meaning that the categories do not have.
Also check what the PivotTable is calculating. Sum, average, count, and other summaries answer different questions. If a product has a different number of observations from another product, a sum may reflect volume rather than typical performance. Choose the summary that fits the question, and make the axis titles explicit so readers know what the plotted values mean.
Prepare the PivotTable summaries
In Excel for Microsoft 365 on Windows, select the source data and choose Insert > PivotTable. Confirm the table or range, choose where to place the PivotTable, and select OK. Arrange the fields so each row represents one point in the eventual chart—for example, one product, store, month, or customer segment. Put the numeric measures you need in Values.
To change how a value is summarized, open its field menu in the Values area and choose Value Field Settings. Depending on the source and field, you can select options such as Sum, Count, or Average. Make sure both chart measures are summarized at the same level of detail. Plotting average price by product against total sales by month, for instance, would mix unlike groups and make the relationship hard to interpret.
Keep the PivotTable layout simple. Put the two numeric measures in adjacent value columns and the point identifier in a row-label column. Turn off subtotals and grand totals if they would otherwise become extra plotted points: on the PivotTable Design tab, use Subtotals > Do Not Show Subtotals and Grand Totals > Off for Rows and Columns. A grand total is usually not another observation, so including it can distort the chart’s scale.
Build a helper table and create the chart
A helper table gives the regular chart a clean, predictable range. Put clear headings in two columns, such as Average price and Average units, then link each row to the corresponding PivotTable values. You can also copy the PivotTable values and paste them into a separate area as values; that creates a snapshot that will not update when the PivotTable refreshes.
For example, suppose the PivotTable has product names in A5:A9, average prices in B5:B9, and average units in C5:C9. In a helper table, put the x-axis heading in E4 and the y-axis heading in F4. With those PivotTable locations, enter =B5 in E5; if B5 contains 12.5, the helper cell returns 12.5. With the same layout, enter =C5 in F5; if C5 contains 40, the helper cell returns 40. Fill both formulas down for the remaining products, keeping each x-value paired with its corresponding y-value. These are ordinary cell references, so the displayed results follow changes to the referenced cells.
Select the two helper columns, including their headings, and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. If Excel assigns the wrong ranges, right-click the chart, choose Select Data, select the series, and choose Edit. Set Series X values to the numeric x-measure cells and Series Y values to the matching numeric y-measure cells; exclude headings and text labels from both ranges. Add axis titles from Chart Design > Add Chart Element > Axis Titles.
If the chart has several series, each series needs its own paired x and y ranges. For a single series, a separate helper column containing product names can make points easier to identify, but labels are optional. You can add data labels and, where available, use values from cells to show those names. Keep labels selective when points overlap, since labeling every point can make a chart difficult to read.
Keep the chart useful after a refresh
When you refresh a PivotTable, its values may change, and new categories may appear or disappear. Formulas that point to existing value cells will reflect changed values, but they do not automatically guarantee that the helper table has a correctly paired row for every new PivotTable item. If the PivotTable expands, you may need to add helper rows and extend the chart’s series ranges. If it contracts, check for old helper rows that still contain values and could remain plotted.
After refreshing, check the first and last plotted rows, the x-y pairings, and whether a subtotal or grand total has slipped into the selected range. For recurring reports, keep the helper table beside the PivotTable and use a consistent layout. If the summary’s shape changes frequently, consider rebuilding or checking the chart range as part of the refresh routine rather than assuming a regular chart will resize exactly as a PivotChart would.
There are two practical update styles. Linked formulas keep the chart connected to the current summary, but depend on stable cell positions and a well-maintained helper range. Pasting values gives you a fixed report that will not change unexpectedly, but you must paste fresh results after each update. Choose based on whether the chart should update with the PivotTable or document a particular reporting period.
Format and interpret the result carefully
Use axis titles that include units, such as Average price in dollars and Average units per month. Set number formats to match the measures, and avoid adding a trendline unless it helps answer the question. A trendline describes the displayed observations; it does not establish that one measure causes the other. A few extreme values can also affect the apparent pattern, so inspect unusual points and confirm they are valid data rather than summary or range-selection errors.
Scatter charts treat the x-axis as a value scale, so the distances between x-values have meaning. If points overlap, try smaller markers, some transparency, or data labels for only selected observations. If the point names matter more than numeric spacing, a category chart may communicate the comparison more honestly. Likewise, when the goal is to show changes over ordered dates, a line chart may be more natural than a scatter chart, especially if the dates represent a continuous time sequence.
Common problems and fixes
- Scatter is unavailable for a PivotChart. Use a regular chart from a helper range; XY scatter is not a supported PivotChart type.
- The chart shows row numbers or labels as values. Edit the series through Select Data and assign only numeric cells to X values and Y values.
- There is one dot or an unexpected series. Check that each numeric measure is in the intended range and that the two ranges contain the same number of corresponding observations.
- The chart includes totals or subtotals. Exclude those cells from the helper table or turn off totals in the PivotTable layout.
- New PivotTable items do not appear. Add matching helper rows and extend the ordinary chart’s series ranges, then verify the pairing.
- All points sit on top of one another. Confirm that the source values are genuinely different and that the chart is using the intended measures, not repeated labels or a single summary value.
Alternatives when a scatter chart is not the best fit
If you need a chart that responds directly to PivotTable filters and slicers, use a supported PivotChart type such as a column, bar, or line chart. It will remain linked to the PivotTable, though it will not provide true numeric x-axis spacing. For a scatter plot, keep the ordinary chart and use helper cells that reflect the filtered summary, accepting that the chart range may need maintenance.
For a one-time analysis, copying the summarized values to a clean range and creating a regular scatter chart is often the simplest route. For a repeatable report, linked helper cells are more convenient, provided you check the chart after refreshes. If the source data itself contains the two numeric measures at the individual-record level, you may not need a PivotTable at all: a regular scatter chart from those records can show the raw relationship, while a PivotTable-based chart instead shows a relationship among aggregated groups.
Frequently Asked Questions
Can you make a scatter plot directly from a PivotTable?
Not as a PivotChart. You can create a standard XY scatter chart using numeric PivotTable results, preferably arranged in a helper table.
Why is scatter missing from the PivotChart menu?
Excel does not support XY scatter as a PivotChart type. Insert a regular scatter chart from a worksheet range instead.
Can a scatter plot use text categories on the x-axis?
Not as category positions in the way a column chart does. An XY chart needs numeric x-values. Use a category chart for text categories, or choose a meaningful numeric measure for the scatter chart’s horizontal axis.
Will the scatter chart update when I refresh the PivotTable?
It can update when its linked helper cells change, but the chart is an ordinary chart. New or removed PivotTable rows may require you to update the helper table and chart ranges.
Should I include the PivotTable grand total?
Usually not. A grand total is an aggregate of the observations, not another observation of the same kind, and can distort the chart. Exclude it from the plotted range.
Related
- Excel Protect Sheet: How to Change a Password
- Excel Protect Sheet: How to Edit Protected Cells Easily
- Excel Protect Sheet but Allow Grouping: What Works
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Creating an Org Chart in Excel: Steps and Options ExplainedTo create an org chart in Excel for Microsoft 365 on Windows, choose Insert > SmartArt > Hierarchy…
- Black Bullseye Chart in Excel: How to Create OneA black bullseye chart is usually a custom target graphic made from concentric circles; it is not a…
- Stacked Bar Chart Excel Gantt: Build a Task TimelineYes. In Excel, a Gantt chart can be made from a stacked bar chart by plotting each task’s…
- Statistical Graphs and Charts: Types, Uses, and ChoicesStatistical graphs and charts are visual displays of data that help people see patterns, compare values, and communicate…
- Excel Box Plot X-Axis: Categories, Labels, and FixesIn an Excel box plot, the x-axis normally shows the categories or data groups being compared; the y-axis…
- Excel Pie Chart: Show Percentages ClearlyYes. In Excel for Microsoft 365 on Windows, select the pie chart, then choose Chart Design > Add…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.