03Feb2015

Dynamic Charts in Excel

Dynamic charts in excel are useful when we add new data to our existing data tables. For example, lets take 1st quarter sales data for Laptops, Mobiles and Tablets as shown in the image below. A chart for such a data table is generally static i.e. the chart does not reflect new data which gets added to the existing data table. This blog will help you to create a dynamic chart which reflects new data points as and when new data gets added to the data table. To create dynamic charts we use offset function.

dynamic charts in excel

 

Step 1: Define a Named Range

Step 1a:

To define a name range, select ‘Define Name’  in ‘Defined Names’ section in ‘Formulas’ tab in Excel ribbon.

dynamic charts in excel 1

 

Step 1b:

A new window for defining a new named range will pop up. Here, we define the new name for our data ranges.

dynamic charts in excel 3

 

For Months, we enter Name as ‘months’ and put the following formula

=OFFSET(‘Total Sales Data’!$A$1,1,0,COUNTA(‘Total Sales Data’!$A:$A)-1,1)”

Here, we use offset function. Offset function has five arguments:

1) ‘Total Sales Data’!$A$1: This is the reference cell. We are using A1 as starting reference because ‘Months’ column starts from this cell (refer to our sample data image).

2) ‘1’: This is the # rows from reference cell from which, our named range starts ($A$1 is our reference cell, since number of rows from reference cell is 1, our range ‘months’ starts from row # 2).

3) ‘0’: This is the # columns from reference cell from which, our named range starts (since reference cell is in column A and our range ‘ months’ is also in column A, the value is 0)

4) ‘COUNTA(‘Total Sales Data’!$A:$A)-1′: We use ‘COUNTA’ function to find the height (# used rows) of the range. We subtract 1 from the total range as we are using headers in our data table.

5) ‘1’: We end the offset function with 1 as width. Since our named range is only one column, width is 1.

 

Similarly, we define named ranges for Laptops, Mobiles and Tablets as shown in the image below.

dynamic charts in excel 4

dynamic charts in excel 6

dynamic charts in excel 7

 

Step 2: Create a Chart

After defining named ranges, we select the data range and click on ‘Insert’ & then select the chart in ‘Charts’ area (Clustered bar chart in this example) as shown in the image below.

dynamic charts in excel 8

 

dynamic charts in excel 9

 

Step 3: Inserting Named Ranges into the chart (Dynamic chart in Excel)

To insert named ranges, we right click on chart and select  ‘select data’ option as shown in the image below.

dynamic charts in excel 10

 

To make ‘months’ dynamic:

We click on Edit button to make changes for the month as shown in the image below.

dynamic charts in excel 11

 

Now, we get a pop up window (Axis Labels). Here instead of fixed range, we change it into named range (months).

dynamic charts in excel 12

 

To make ‘Laptops’ range dynamic:

Step 3a:

We click on Laptops

Step 3b:

Now, we click on Edit as shown in the image below.

dynamic charts in excel 13

 

Now, we change the ‘series values’ (range) to our named range laptops

dynamic charts in excel 14

 

Similarly, we edit the data range for mobiles and tablets.

dynamic charts in excel gif

 

Steps above would help us in creating dynamic charts in excel. If you still have any questions, feel free to contact us here.

  • 3 Feb, 2015
  • Excel for Commerce
  • 0 Comments
  • Dynamic Charts in Excel, Excel Consultant, Excel Expert,

Categories

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>