Pivot Table Issues

How to Display Individual Data Points in an Excel PivotTable

Maira Mehtab Oct 9, 2026 869 views

Question details

The user wants to display multiple, individual data points (such as thermometer readings) in a dashboard without the data being aggregated or grouped.

How to Display Individual Data Points in an Excel PivotTable

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating auto-updating dashboard charts based on monthly source data where each individual reading needs to be visualized separately.
Observed behavior: PivotTables and PivotCharts automatically summarize value fields (e.g., Sum or Count), making it impossible to view raw, unsummarized data points directly through standard Value Field Settings.

Before you start

Before you begin, ensure your source data is formatted as an Excel Table (Ctrl+T) so that any new data added later will automatically flow into your charts and PivotTables.

Solution 1Recommended

Add a Unique Row Identifier to the Source Data

Force the PivotTable to display individual records by assigning a unique row number to every entry, preventing the PivotTable from grouping them.

Because PivotTables are fundamentally designed to aggregate data, they will always group identical row labels together. By introducing a completely unique value for every single row, you force the PivotTable to list every item individually.

Insert a new column

Go to your raw source data table, insert a new column, and name it 'Row ID' or 'Reading Number'.

Number the rows sequentially

Fill this new column with sequential numbers (1, 2, 3...) so every single data point has a distinct, unique identifier.

Update the PivotTable

Right-click your PivotTable and select 'Refresh' to load the new column. Drag the 'Row ID' field into the 'Rows' area of the PivotTable Field List.

View individual points

Because each Row ID is unique, the PivotTable will no longer sum or group the readings together. Your connected PivotChart will now display every individual data point.

Add a Unique Row Identifier to the Source Data

Chart Axis Appearance: The unique row numbers will appear on your PivotChart's axis. You can format or hide this axis depending on the visual design of your dashboard.

Solution 2

Use Standard Charts and Slicers Instead of PivotCharts

Bypass the aggregation problem entirely by creating a regular chart directly from your raw data table and filtering it.

WPS Spreadsheet Solution

Analyze and Chart Unsummarized Data Effortlessly in WPS Office

WPS Spreadsheet provides powerful data visualization tools, including standard charts, tables, and PivotTables. You can easily build dynamic dashboards to display individual data points without unwanted aggregation, ensuring your data is presented exactly how you need it.

1. Import your data: Open your existing dataset or dashboard file directly in WPS Spreadsheet.
2. Insert a unique ID: Add a sequential 'Row Number' column to your data table to keep every record distinct.
3. Create a PivotTable: Navigate to the Insert tab, click PivotTable, and drag your new unique ID into the Rows field.
4. Generate the chart: Insert a PivotChart to visualize your fully unsummarized, individual data points accurately.

Fully compatible with Microsoft Excel (.xlsx) files, PivotTables, and charts.Create dynamic, unsummarized dashboards with advanced Slicers and filtering tools.Lightweight software with a familiar, easy-to-navigate interface for quick data analysis.Free to use for everyday data visualization and spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Is there a 'Display as values' setting in PivotTables to turn off aggregation?

No, PivotTables are fundamentally designed to aggregate data (Sum, Count, Average, etc.). There is no built-in toggle in the Value Field Settings to completely disable summarization. You must bypass this by giving each row a unique identifier or using a standard chart instead.

Why does my PivotChart group all my daily readings into one single bar?

This happens because PivotTables automatically group identical row labels together and aggregate their corresponding values. To separate them into individual bars, you must add a column with unique values (such as a highly specific timestamp or a sequence number) to the Rows area of your PivotTable.

Can I use Slicers on regular charts instead of PivotCharts?

Yes. If your source data is formatted as a Table (using Ctrl+T), you can insert Slicers directly from the Table Design tab. These Slicers will filter the table's rows, and any standard chart linked to that table will update automatically to reflect the filtered data.

Excel & Data Problems