Creating Dynamic Charts Using the OFFSET Function and Named Ranges in Excel 2007 and Excel 2010

Updated on October 13, 2016

How to Create a Dynamic Chart in Excel 2007 and Excel 2010 That Updates Automatically as Data Is Added or Removed

Hi and welcome to my second hub on using the OFFSET function. In today’s hub, I am going to investigate how to create dynamic charts in Excel using OFFSET as well as Defined Names to create named ranges.

What this will allow you to do is to create charts from your data which will update automatically when you add or remove data. With a static chart, when you add data, you have to manually update your chart so that it displays the new data. A dynamic chart does this for you automatically.

An example of a Dynamic Chart created using a named range in Excel 2007 or Excel 2010.
An example of a Dynamic Chart created using a named range in Excel 2007 or Excel 2010. | Source

In my first hub on the OFFSET function, I investigated how to use it with the COUNTA function to create dynamic data ranges so you could, for example, use it with SUM to add up invoices, sales or web traffic data. Should you delete or add data to the dynamic range your formula will be automatically recalculated to reflect the new data. This hub can be found here.

Data used to create a defined named range in Excel 2007 and Excel 2010.
Data used to create a defined named range in Excel 2007 and Excel 2010. | Source

Creating Named Ranges in Excel 2007 and Excel 2010

The first step is to define named ranges from our data shown to the right:

The named ranges will then be used to define the data that we will use to create the dynamic charts.

We need to create two named ranges, one for the data itself and another for the chart labels. Firstly, we will create the range for the data that will appear in our chart:




  • Select the Define Name button on the Formulas tab in the Defined Names group
  • Define a Name (without spaces) you can see I used DailyVisits

Creating a Defined Name range in Excel 2007 and Excel 2010.
Creating a Defined Name range in Excel 2007 and Excel 2010. | Source
  • In the Refers to box enter the following OFFSET formula

=OFFSET(DynamicCharts!$B$2,1,0,COUNTA(DynamicCharts!$B:$B),1)

The above formula creates a dynamic range starting at cell B2. It counts down the same number of rows as the number of rows found to be containing data by COUNTA.

So, if COUNTA finds 23 cells containing data, the range will start at B2 and end at B25.

COUNTA is dynamic so if data is added or removed, it automatically updates which changes the range. The OFFSET function is explained in far more detail in my first hub on OFFSET (the link can be found in the Introduction).

Note: When making changes to the Refers to box, do not use the arrow keys to move around this dialogue box or it will add cell references to the formula. Move by clicking the mouse instead.

Note: to edit your named ranges once they are created, use the Name Manager button to the left of the Define Name button you used to create it.

  • Next, we need to repeat the above process, this time using Label for the Name and the formula below in the Refers to box to create our dynamic range for the chart labels.

=OFFSET(DynamicCharts!$A$2,1,0,COUNTA(DynamicCharts!A$3:$A$33),1

Creating a second Defined Name range in Excel 2007 and Excel 2010.
Creating a second Defined Name range in Excel 2007 and Excel 2010. | Source

Creating a Dynamic Chart in Excel 2007 and Excel 2010

Now we have the named ranges, we need to create the chart:

  • Select the Column button in the Charts group on the Insert tab

This will create a completely blank chart

  • With the chart selected, click on the Select Data button in the Data group on the Design tab under Chart Tools
  • Change the Chart data Range so that the name you gave your named range above for the data (I used DailyVisits) is entered after the name of the tab as shown below

So in my example, I changed:

=DynamicCharts!$F$7

to

=DynamicCharts!DailyVisits

Updating the Data Source so that it uses a range defined by a formula using OFFSET and COUNTA to change a chart to a Dynamic Chart in Excel 2007 and Excel 2010.
Updating the Data Source so that it uses a range defined by a formula using OFFSET and COUNTA to change a chart to a Dynamic Chart in Excel 2007 and Excel 2010. | Source

The chart will now look like this:

Dynamic Chart created using Named Ranges in Excel 2007 or Excel 2010.
Dynamic Chart created using Named Ranges in Excel 2007 or Excel 2010. | Source

To add our dynamic labels:

  • Select the chart again
  • Click on the Select Data button in the Data group on the Design tab under Chart Tools
  • Under Horizontal (Category) Axis Labels, click Edit
  • In the Axis label range, enter in the name of your named range for your labels (I chose Labels for mine)

Configuring Dynamic labels for use in a Dynamic Chart in Excel 2007 or Excel 2010.
Configuring Dynamic labels for use in a Dynamic Chart in Excel 2007 or Excel 2010. | Source
  • Click OK twice and Excel will now have added your labels to the chart

Finally, we need to ensure that the chart is using the named ranges. To do this:

  • Ensure that the chart is once again selected
  • Click on the data series
  • On the formula bar, you will see a formula beginning with =SERIES

The formula in my chart is:

=SERIES(,'Analytics Overview.xlsm'!Labels,DynamicCharts!$B$3:$B$39,1)

Editing the data series used by a Dynamic Chart to ensure it is using the named ranges in Excel 2007 or Excel 2010.
Editing the data series used by a Dynamic Chart to ensure it is using the named ranges in Excel 2007 or Excel 2010. | Source

Before we edit this formula, let’s look at its syntax:

The formula is made up of four parts

  • The title of the chart (this is optional)
  • The location of the labels for the horizontal axis
  • The location of the data for the series
  • The order that the series appear on the chart (1 for first, 2 for second etc)

Components of the formula used to convert a chart to a Dynamic Chart in Excel 2007 and Excel 2010.
Components of the formula used to convert a chart to a Dynamic Chart in Excel 2007 and Excel 2010. | Source

There are three parts of the formula that you should edit:

  • The first is to add the chart title should you wish to from the data header (if you click to the left of the first comma in the formula and then select the cell that contains your data header (which is B2 in my example) Excel will add it automatically for you) so that first part of the formula becomes:

=SERIES(DynamicCharts!$B$2,

  • The second part is the part that contains the dynamic labels. These will be updated automatically should the labels in your spreadsheet change.

'Analytics Overview.xlsm'!Labels,

  • The third part is to ensure that the chart updates when you add or remove data dynamically. To do this:

Change the last part of the formula $B$3:$B$39 to your named data range for the data (mine is DailyVisits)

DynamicCharts!$B$3:$B$39,

The formula becomes:

=SERIES(DynamicCharts!$B$2,'Analytics Overview.xlsm'!Labels,DynamicCharts!DailyVisits,1)

If you would like to learn more about creating and configuring charts and graphs in Excel 2007 and Excel 2010, I have a hub that covers this in greater detail. The hub can be found here.

Now we have the chart completed, one final thing to note when entering in data. If you enter in data without the corresponding labels in the adjacent row, Excel will not add them to your chart. It will only display the new data once the labels are added. If you add in a number of labels with no data, Excel will display the first label without data but will not add any more labels without data to the chart.

Conclusion

As our lives become more and more busy and the amount of information we work with increases, it is always very useful from both a time saving and error reduction standpoint to automate as much as we possibly can and the OFFSET function is very useful indeed in allowing us to automate both formulas and charts in Excel. Particularly for spreadsheets that are often updated, automating the updating of your charts is very handy indeed.

The OFFSET function in Excel allows you to create dynamic ranges and dynamic charts which update automatically when data is added or removed within the range. In today’s hub, we created named ranges and used them with the OFFSET function to create dynamic charts.

Many thanks for reading and I hope that you are now enjoying your dynamically updating charts and spending your new free time on something more enjoyable. Please leave any comments you may have below.

Questions & Answers

    © 2013 Robbie C Wilson

    Comments

      0 of 8192 characters used
      Post Comment

      • profile image

        Douglas 

        10 months ago

        Excelent post, thanks for sharing helps a lot!

      • profile image

        Clinton 

        15 months ago

        Thanks so much for this article. It has really helped me.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)