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.
Download Practice Workbook
Related Articles
- How to Make Dynamic Charts in Excel
- How to Create a Dynamic Chart in Excel Using VBA
- How to Create Min Max and Average Chart in Excel
<< Go Back to Dynamic Excel Charts | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
