# How to Create Dynamic Chart with Multiple Series in Excel

### Method 1 – Insert Table to Create Dynamic Chart with Multiple Series

Steps:

• Select the complete dataset.
• Go to the Insert tab and select Table.

• From the Create Table dialog box, press OK.

• The dataset will be converted into a table.
• Select the table.
• Move on to the Insert tab and select Insert → Charts → 2-D Column → Clustered Column.

• After that, a 2-D Column chart will appear and this is the Dynamic Chart.

• Add a new row to the table. Let’s say, I will add Olivia’s securing marks in Physics and Chemistry as 91 and 77.

• The chart with multiple series updates the data automatically.
• Say the chart is a dynamic one.

### Method 2 – Create Dynamic Chart Through Named Range in Excel

#### STEP 1: Define Named Range

• Make the defined name and the dynamic formula for every column.
• In the Formulas tab, go to Defined Names → Defined Name.

• A New Name dialog box will appear in front of you. In that dialog box, type Name in the Name typing box.
• Select the current worksheet named Dynamic Ranged Name from the Scope drop-down box.
• Type the below formulas in the Refers to typing box and press OK.

`=OFFSET(\$B\$5,0,0,COUNTA(\$B:\$B)-1)`

Formula Breakdown:

• In the above formula, the COUNTA function counts all the non-empty cells from the entire column data of column B and here it returns 8.
• The OFFSET function returns a range of cells from the specified rows and columns.
• The reference value as B5 and height as the output provided by the COUNTA function.
• It returns the value of the non-empty cells of column B.
• Repeat the above-described steps for column C. The formula for the Physics column will be as below.

`=OFFSET(\$C\$5,0,0,COUNTA(\$C:\$C)-1)`

• Insert the below formula for the Chemistry column to perform a similar process.

`=OFFSET(\$D\$5,0,0,COUNTA(\$D:\$D)-1)`

#### STEP 2: Creating Dynamic Chart in Excel

• Draw a 2-D Stacked Bar chart from the Insert tab following the similar process mentioned in the 1st ,we selected the full dataset B4:D10 for the chart.

• Press right-click on any column of your chart. Instantly, the Context Menu pops up.
• Select the Select Data option from that window.

• A Select Data Source dialog box will appear in front of you. From the Select Data Source dialog box, select Physics.
• Select the Edit option under the Legend Entries (Series).

• A window named Edit Series pops up. From the Edit Series dialog box, type =’Dynamic Named Range’!Physics in the Series values typing box.
• Press OK.

• From the Edit Series dialog box, type =’Dynamic Named Range’!Chemistry in the Series values typing box.
• Press OK.

• Select the Edit button under the Horizontal (Category) Axis Labels option.

• From the Axis Labels dialog box, type =’Dynamic Named Range’!Name in the Axis label range box and press OK.

• Press OK.

#### Final Output

• Add a new row to the table. Let’s say, I will add Martin’s securing marks in Physics and Chemistry as 81 and 75.

• The chart with multiple series updates the data automatically.
• See the below chart to understand clearly.

## Things to Remember

• First of all, if there is no value in the referenced cell, the #N/A error occurs in Excel.
• Then, you can press Ctrl + T simultaneously on your keyboard to create a table.
• Next, you should not leave any blank cells in the Named Range.
• Afterward, make sure to follow the naming convention when entering the Series values.

## Related Articles

<< Go Back to Dynamic Excel Charts | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan

Mehedi Hasan, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a profound passion for research and innovation, he actively engages with Excel. In his capacity, Mehedi not only adeptly tackles intricate challenges but also showcases enthusiasm and expertise in navigating tough situations with finesse, underscoring his unwavering dedication to consistently delivering exceptional and high-quality content. He... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF