Creating and Editing charts is an important feature of Excel. It helps us visualize our datasets with ease instead of going through intimidating data for comparisons. Creating dynamic charts and editing chart data is a vital part of any visualization and comparison process. This article will focus on how to edit chart data in Excel and in doing so, to change the chart automatically in the process.
How to Create a Chart Based on Data in Excel
First, you need a dataset to portray charts from in Excel. We are using the following dataset for demonstration.
If you want to change the chart Data Range automatically with the modifications of rows/columns, it is a good idea to change the dataset to a table. This way, you won’t need to manually update the chart every time you insert or delete a new row/ column.
To convert the dataset to an Excel table follow these steps.
Steps to Create a Table:
- First, select the whole dataset.
- Second, go to the Insert tab from your ribbon and click on Table from the Table group.
- Next, a Create Table box will open up. Make sure the My table has headers option is checked. Then click on OK.
As a result, the dataset will now be converted into an Excel table.
After some modifications, the table will look something like this.
This is our final table which will serve as our chart data.
To create a chart out of it follow these steps.
Steps to Generate a Chart:
- First, select a cell in the table.
- Then go to the Insert tab from your ribbon and under the Charts group, select Recommended Charts.
- Consequently, the Insert Chart box will open up. Now, select the chart type you want from this box. Here, we have selected Clustered Column under the Recommended Charts tab for demonstration. Finally, click on OK.
As a result, a column chart or bar chart will appear on the spreadsheet.
After some modifications, we have decided to go with this style.
All the methods/ examples will work regardless of the chart type if you have converted the dataset into a table and then select the chart type.
Up to this point, we have just selected a chart for a table and generated a chart from it. Now we will focus on editing the chart data- how to edit particular values, columns, and rows in Excel. Follow along to learn more or select the one you want from the table at the top of the article.
1. Modifying Excel Chart Data
In our first example, we will see how to modify the chart by editing chart data within it. Let’s say the price/unit of the first product in our table has gone down, from $22 to $10. Changing the value now will automatically update the chart.
To change the value, select cell D5 and rewrite the value to 10.
At this point, you can see the change in the chart instantly.
Compare this graph with the one in the previous section. You will notice the height of the first column shortened.
Changing any values, that are present in the graph will change the chart. Changing the ID of the third product to let’s say “Product 3” will automatically change the chart too.
Change the cell value of cell B7 to “Product 3”.
Instantly, the value of the third entry will change accordingly.
Read More: How to Change Data Source in Excel Chart
2. Editing Chart Data by Adding New Values in Excel
To add new values such as columns/rows, first, you need to make some modifications so that the table automatically adds new rows/ columns in the table. And if you do that the newly added values will automatically be included in the table and in turn, will change the chart accordingly.
- First, go to the Excel Options by clicking on the File tab and then Options from the backstage view.
- Next, in the Excel Options box, go to the Proofing tab and select Autocorrect Options in it.
- Now make sure the options shown in the figure are selected in the AutoFormat As You Type tab of the Autocorrect box.
- Finally, click on OK in both the boxes.
- Now Excel will automatically add new rows/columns to the table as you keep typing to adjacent cells.
- To add a new value to a chart, simply add a new column to the table. For that, simply inserting values besides the table would be enough. Type out the header in cell E5.
- And then press Enter.
- A column will be automatically created with the formats applied in the table.
- Now complete the column.
Instantly, the new values will be added to the chart according to the chart data in Excel.
Read More: How to Change Chart Data Range in Excel
3. Removing Values from Chart Data
To remove values from the chart you merely need to remove a column from the chart data. To remove a column from a table follow these steps.
- First, select the column you want to delete.
- Then right-click on the column. As a result, a context menu will appear.
- First, select Delete from it. And then select Table Columns.
Consecutively, the column will be deleted.
And the chart will automatically lose the values.
Read More: How to Edit Data Table in Excel Chart
4. Adding New Rows to Chart Data
To add new rows to chart data, make sure you have enabled the options to automatically add new rows/columns to the table described in the second example. Once you have done that, just write a new product id in cell B9 and press Enter.
A new row will automatically appear at the end of the table.
Now fill up the values in the row.
You will notice the chart will automatically have a new column at the end of it.
5. Removing Rows from Excel Chart Data
In a similar manner, you can remove rows to remove columns in the chart. To delete rows from chart data in Excel, follow these steps.
- First, select the rows you want to remove. We have selected the range B8:E8 for this purpose.
- Then right-click on the row.
- Next select Delete from the context menu. And then select Table Rows.
This will remove the row from the table.
And remove a set of columns from the chart that represented the particular row.
Read More: How to Skip Data Points in an Excel Graph
Download Practice Workbook
You can download the workbook used to demonstrate all of the methods and examples below. Download and practice yourself while you go through the article.
These were all the examples of different edit you can perform in chart data in Excel. Hope you found this guide helpful and informative. If you have any questions or suggestions let us know below.