OmniDocHow to select and modify data sources for Excel charts
There is no need to redraw the Excel chart that has been created when data needs to be added or subtracted or the order needs to be adjusted. This article explains how to reselect the data range, add and sort series in the chart, and how the chart will change after the data source is changed.
Key points of this article
- To modify chart data, select the chart first, and then set the adjustment range through the data source. There is no need to re-insert the chart.
- When adding a series, you must specify the series name and value range separately. It is recommended to select the header cell together.
- Moving the series up or down only changes the legend and display order, but does not change the arrangement of the original table.
- After converting the data range into a table, the newly added rows are usually automatically included in the chart, eliminating repeated adjustments.
- Check the display of ranges, series names, hidden data, and empty cells on a case-by-case basis after data source changes.
After making an Excel chart, the data often needs to be changed: one more month is added, one product is missing, the order of the series is changed, or some rows are temporarily hidden, but the chart changes accordingly. Many people subconsciously delete and redraw. In fact, in most cases, just modifying the data source behind the chart is enough.
This article is written for people who often make reports, weekly reports or papers with pictures. After reading, you can know: how to select data in excel charts, how to add, delete and sort series, and what will happen to the chart after the table data changes. The entire process uses Excel's built-in charting function and does not require additional software.
How to select data in excel chart? One sentence conclusion
For existing charts, the safest approach is to first select the chart, open the data source settings, and directly select or modify the referenced cell range; if you just want to extend the range by a few lines, dragging the border is more intuitive. The table below provides recommended practices for common situations.
| your situation | Recommended practices | reason |
|---|---|---|
| I just want the chart to include a few more rows of data | Drag the control points at the four corners of the data area | Colored borders display the range simultaneously, making changes intuitive. |
| To add a series, such as a new product | Add a series in the data source settings, specifying the name and value respectively | Able to precisely control the cells corresponding to each series |
| To adjust the legend order or series order | Move series up or down in data source settings | Only the display order is changed, the original table is not changed |
| The rows and columns of the chart are in the wrong direction | Use the switch column function | No need to rearrange the table |
| Data will be added frequently | First convert the data area into a table, and then make a chart based on the table | New rows usually automatically enter the chart range |
Before you start: Confirm these points first
Taking a minute to judge before taking action can avoid making the chart more messy.
- Confirm the data range referenced by the chart. When the chart is selected, the data area is usually marked with a colored border. Note which rows and columns it covers. This is the basis for all subsequent modifications. Data outside the frame will not appear on the graph.
- Distinguish between classification axes and series. The category of the horizontal axis (such as month, region) and each line and each group of columns (such as a certain product) are two different things. By adding categories as series, the graph will be misaligned and the legend will become meaningless.
- Check whether the table header is included in the reference. The series name is usually taken from the header cell. If the table header is not selected, the legend will show a default name like "Series 1", which looks like something is wrong with the data.
- If the data comes from PDF, convert it into a table first. The desktop client's PDF editor supports exporting PDF to Excel format. After exporting, you need to check the numbers one by one before using them to make charts to avoid bringing recognition or typesetting errors into the chart.
Steps to add and sort series in Excel chart
The following steps apply to common Excel versions. The specific button names may differ slightly depending on the version and language, but the core actions are the same.
- Select the chart. Click the blank area of the chart to display the colored border of the data area, indicating that editing has started.
- Right-click to open Data Source Settings. Right-click on the chart and find the command related to selecting data (usually labeled "Select Data" in the Chinese version) to open the Data Source dialog.
- Check out the existing series. Check whether the name and numeric range of each series in the legend item list correspond to the correct cell; if there are any errors, correct them here.
- Add new collections. Click Add, point the series name to the corresponding header cell, then check the value in this column. Once confirmed, the new series will immediately appear in the chart.
- Remove unwanted series. After selecting a series, click delete; it will only be removed from the chart, while the original data in the table will still be retained.
- Adjust the series order. Select a series and use the up or down button; the stacking order in the legend and diagram will change in sync.
- Modify the horizontal axis category. In the horizontal (categorical) axis tab settings, rebox the cell containing the month, name, or other categories, and the horizontal axis text will update accordingly.
- Confirm and check the results. Click OK to return to the chart, check each series one by one to see if the series is complete and if the order matches expectations, then save the file.
After completion, check each item one by one
After making changes to the chart, don't just glance at it and stop there. The following items are prone to problems, and that's exactly where charts tend to "deteriorate" after data source changes.
| Inspection items | How to see it | What should you do if there's a problem? |
|---|---|---|
| Whether the data coverage is complete | Whether the colored border covers all the necessary rows and columns | Drag the corner to complete, or rebox in the data source settings |
| Check if the series name is correct | Check whether the legend text matches the header | Modify the header cell referenced by the name, or check the reference address |
| Whether new data is included in the chart | Add a row at the end of the data area to see if the chart has updated | No update notes that the scope is not extended, manually expanding the citation, or switching to tables |
| Hide the data of rows and columns | After hiding a row, check if the corresponding points or bars on the chart have disappeared | By default, hidden data is not displayed; When you want to display, check the box to show data for hidden rows and columns in the data source hidden and empty cell settings |
| Display method for empty cells | Leave one space blank and check whether the image is disconnected, marked as zero, or connected | Choose the method that matches the meaning between hidden and empty cell settings |
About inserting cells: inserting rows or columns in the middle of data areas usually expands the chart; If inserted outside the area close to the end, the chart may not automatically include new content. Therefore, after insertion, you should review the borders and graphics as shown in the table above, and do not assume they are updated.
📝 Summary
To edit data in an Excel chart, the key is to select the chart first, then adjust the range, series, and order in the data source settings, trying not to redraw the chart. Converting data areas to tables, inserting them, and checking borders makes subsequent additions and removals much easier.
❓ Frequently Asked Questions (FAQ)
1. How do you sort charts in Excel?
In the data source settings, select the series and use move up and down to adjust the order; this changes the legend and stacking order. If you want to sort values by size, you should first sort the source data in the table, and the chart will update accordingly.
2. How do you add data in an Excel chart without affecting the original chart?
Just add a new series in the data source settings; the scope and style of other series will not change. If you only want to add a row at the end of the table, after converting the data area into a table, the chart will automatically include the new data.
3. Can charts created in Excel 2016 still have data modified in the new version?
Yes. After creating and saving in the common xlsx format in Excel 2016, you can still edit the data source in newer versions. There may be slight differences in individual fonts or layouts; just check each item after opening it.
4. How to check if the numbers shown in the chart do not match the table?
First, open the data source settings and check whether the referenced worksheet name and cell address point to the current table. If the chart is copied from another file, the reference may still point to the original file and need to be changed to the current table range.
5. If the numbers in the chart differ from the original data, which one should you trust?
The original data in the table is the standard; the chart is merely a graphical representation of the data. If the data comes from PDF or scans, the numbers obtained through OCR or export must be compared item by item with the original file, and the machine-recognized results need to be manually verified.
