Automatically Resize an Excel Chart to Match Populated Data
Question details
Resize an Excel chart automatically so its category axis includes only populated records rather than all possible rows in a fixed source range.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A report source can contain up to 100,000 rows, while most reports contain only 2,000 to 6,000 records.
- Observed behavior
- The chart reserves space for all 100,000 source rows even when formulas return NA() for unused rows.
Confirm which column always contains a value for each valid record, such as an ID or date column. Use that column to identify populated rows when creating a dynamic source range or Excel Table.
Convert the source range to an Excel Table for an automatically expanding chart
Use this method when you want charts to grow and shrink with added or removed records without maintaining formulas or fixed row limits.
Excel Tables automatically resize as records are added or removed. When the chart series uses Table columns, the chart source follows the actual populated Table rows instead of a preallocated 100,000-row range.
Click any cell in the current data range, including the header row and the populated records you want to chart.
Go to Insert > Table, confirm the selected range, select My table has headers if applicable, and click OK.
To create a chart, select the relevant Table columns and go to Insert > Recommended Charts or Insert > Charts. For an existing chart, right-click it and choose Select Data, then set each series to the appropriate Table column.
Enter a new record directly below the last Table row or delete unneeded Table rows. The Table and its linked chart update to use the current records.
Use a FILTER spilled range as the Excel 365 chart source
Use this method when you need a formula-driven chart source that excludes blank records from a large preallocated range.
Build a Dynamic Chart Source in WPS Spreadsheets
WPS Spreadsheets lets you organize report data in a table and create charts from the populated table columns. As report rows are added or removed, the chart can follow the active data range instead of displaying unused categories from a large fixed range.
- 1. Open the report workbook: Open the XLSX file in WPS Spreadsheets and select the columns that contain the chart categories and values.
- 2. Format the data as a table: Use Insert > Table, confirm the populated range, and enable headers when your first row contains field names.
- 3. Insert a chart from the table: Select the Table columns, then choose Insert > Chart and select the chart type required for the report.
- 4. Check the chart data range: Click the chart and use the chart data options to confirm that the series references the Table columns rather than a fixed range extending to row 100000.
- 5. Update report records: Add records in the row directly below the Table or remove rows that are no longer needed; then verify the chart reflects the current Table rows.
Frequently Asked Questions
Why does my Excel chart still show empty space after I use NA() in unused rows?
NA() prevents Excel from plotting those values, but a chart based on a fixed range such as A2:A100000 can still retain all category positions. Change the chart source to an Excel Table column or a dynamic spilled range.
How do I make a chart automatically include new rows in Excel?
Convert the source data to an Excel Table through Insert > Table, then create the chart from the Table columns. New rows entered immediately below the Table become part of the Table and can be included in the chart.
Can I use FILTER to create a dynamic chart range in Excel 365?
Yes. Enter a formula such as =FILTER(A2:B100000,A2:A100000<>"") in an empty area, then use the spilled result, for example D2#, as the chart source through Chart Design > Select Data.
What column should I use in the FILTER condition for a dynamic chart?
Use a column that is always filled for valid records, such as a record ID, transaction date, or product name. For example, use A2:A100000<>"" when column A identifies every valid row.
