(Remember the naming conflict I mentioned? Excel uses the column heading (cell B1) to name the series.) In the list on the left, select Smith and then click Edit.From the resulting submenu, choose Select Data.We need to change that reference to the dynamic ranges we just created, as follows: That's because the chart, by default, references a specific data range, DynamicChart1:A1:E3. If you enter new data, the chart won't yet reflect it. Next, insert a column chart, as you did before. Don't use the same labels for both your spreadsheet headings and your dynamic range names.
Because the chart defaults will use the label headings in each column for each series name, you can't use those labels to name the dynamic ranges. The series headings in row 1 are also names. Using the name, alone, will confuse Excel. You might be wondering why I added the Series label to each range name. If you skip rows or columns, this technique won't work as expected. It's important to remember that you must enter new data in a contiguous manner. The first formula (for the labels in column A) doesn't have this component. The addition of the -1 component eliminates the heading cell from the count. The second set of references refers to the entire column, enabling the formula to accommodate all values in the column, not just a specific range. That's because there's a row of headings in row 1. Notice that first range reference starts with row 2.
It relies on dynamic ranges that update automatically, similar to the way the table does, but only with a little help from you. When either is the case, there's a more complex formula method. Furthermore, this feature isn't available in pre-ribbon versions of Office. You won't always want to turn your data range into a table. Now, update the chart by adding values for March and watch the chart update automatically.
In the Charts group, choose the first 2-D column chart in the Chart dropdown.To illustrate, create a quick column chart as follows: Click OK and Excel will format the data range as a table.Īny chart you build on the table will be dynamic.If the table does not have headers, be sure to uncheck the My Table Has Headers option. Excel will display the selected range, which you can change.To do so, simply select the data range and do the following: LEARN MORE: Office 365 Consumer pricing and features The table methodįirst, we'll use the table feature, available in Excel 20-you'll be amazed at how simple it is. Fortunately, the process is easy to implement in Excel 20 if you're willing to use the table feature. By doing so, the chart will automatically reflect changes and additions to the source data. The key is to define the chart's source data as a dynamic range.
If you want to advance beyond your ordinary spreadsheet skills, creating dynamic charts is a good place to begin that journey. The steps are very similar to the following tutorial by Susan Harkins. Editor's note: In the video, Brandon Vigliarolo uses Microsoft Office 365 and walks through the steps of building dynamic charts in Excel.