How to Show Only One Series in an Excel Chart Data Table
Question details
The user wants to display only one specific data series in an Excel chart's data table while keeping multiple series visible in the chart itself.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Customizing a chart layout to display specific numeric values in a data table without altering the visual representation of the multi-series chart.
- Observed behavior
- Excel automatically displays all source series associated with the chart in the data table, with no built-in option to selectively hide a series from the table while retaining it on the chart.
Since Excel links the chart data table directly to the visible chart series, determine exactly which data series you need displayed in your final table before applying these manual workarounds.
Create a Custom Linked Table Beneath the Chart
Because the built-in data table cannot be selectively filtered, manually building a table in the worksheet cells beneath the chart gives you complete control over which series are displayed.
This method involves disabling the default chart data table and using the worksheet cells directly below the chart to mimic the look of a data table.
Click on your existing chart. Click the '+' (Chart Elements) icon that appears next to it, and uncheck the 'Data Table' option to remove the default table.
Hold down the 'Alt' key on your keyboard while clicking and dragging the edges of your chart. This will snap the chart boundaries perfectly to the edges of the worksheet cells.
In the row of cells immediately below the chart, type or use formulas (e.g., '=B2') to link the data for the single series you want to display.
Highlight the cells containing your new custom data table. Go to the 'Home' tab and apply borders and alignment so it visually seamlessly integrates with the chart above it.
Use a Secondary Chart to Generate the Data Table
You can create a simplified, transparent duplicate chart that contains only the single series you want to display, and use its data table instead.
Create Professional Charts and Spreadsheets with WPS Office
If you are looking for a highly customizable and cost-effective solution for data visualization, WPS Office provides a powerful, free alternative to Microsoft Office. It offers a familiar interface, allowing you to easily build complex charts and manage your spreadsheet data without expensive subscriptions.
Frequently Asked Questions
Can I hide a row in the chart data table without deleting the series from the chart?
No, Excel's built-in chart data table strictly mirrors the series displayed in the chart. If a series is actively plotted and visible on the chart, it will inevitably appear in the attached data table.
Does hiding a column in my spreadsheet remove it from the chart data table?
Yes. By default, if you hide the source data column or row in your worksheet, that data will be hidden from the visual chart and consequently disappear from the chart's data table. However, this hides the series entirely, not just from the table.
Is there a VBA macro to hide specific series in an Excel data table?
VBA cannot override the native behavior of the chart data table element itself. The most effective VBA solution involves writing a script to automatically read your desired series and generate a custom cell-based table underneath the chart.
