It’s easy to create dynamic charts in Excel that resize if you use a Table. Excel 2007, Excel 2010, and Excel 2013 have a Table command that few know about. This command identifies a database or list with headers and data as being a range of related cells. Once you identify the database or list as a Table, Excel treats it as a single entity. If you create a chart from a range of data in a Table, the chart will expand or contract as you add or remove data.
Click here to download the Excel tutorial example file.
Click here to learn the two ways to create dynamic charts. Which is best for you?
To create a Table begin with a range having text or date labels along the top or left side. The top left cell is blank.
1. Click inside the data.
2. On the Insert tab, in the Tables group, click Table. The labels and data will assume the default Table format. You can easily select a new Table format later.
3. Create a chart by selecting within the Table and pressing Alt+F1 to create the default chart on the worksheet. From the menu choose the Insert tab, in the Charts group select the type of chart you want.
The new chart understands that it is linked with the Table and any data added or deleted to the Table will be reflected in the chart.
If you have already created a chart from a range of cells you can turn the existing chart data into a Table,
1. Click inside the data.
2. On the Insert tab, in the Tables group, click Table.
Creating the Table retroactively still links the data and chart so the chart resizes as data changes.
Automatically Expanding or Contracting the Chart by Adding or Deleting Data
Once the chart is based on a Table you can add new data to extend an existing data series or add a new data series just by typing the new data adjacent to the existing data. Do not leave spaces between the Table and new data.
Resizing or Removing the Table
To resize a Table, click in the Table and in the Table Tools Design tab, in the Properties group, click resize table and enter the new Table range.
You can also resize the Table and remove the right most columns by clicking inside the Table and dragging the handle at the lower right corner.
To remove the Table and return the data to normal cell references, click inside the Table, then in Table Tools Design, in the Tools group, select Convert to Range.
Sorting or Hiding Data in an Excel Chart with a Table
When the data range is an Excel Table it automatically includes the AutoFilter arrows to the right of each header label. Sort data by clicking the AutoFilter arrow in the header you want to sort on and select Sort A to Z or Sort Z to A. Hide data by deselecting the checkbox for the data you want to hide.
Normally Excel will not allow multiple AutoFilters on the same worksheet, however, Excel does allow multiple Tables on a worksheet and each can have its own AutoFilter.
Formatting the Table Range
Apply predesigned formats and even alternating row shadings by clicking inside the table, then from the Table Tools Design tab, select the table formatting you want applied.