Switch chart between monthly and quarterly

0027 Switch chart between monthly and quarterly - Featured Image

In this post, I want to answer a question asked by a reader.  The question was from Nick:

“I have a chart on a separate chart sheet, where I’d like to add a developer object (button, option box or checkbox) to control whether data is shown quarterly or monthly. Any idea how to do that?”

In this post, we’re going to answer that very question.

Watch the video:

Watch the video on YouTube

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0027 Switch chart between monthly and quarterly.zip

Scenario

Here is our scenario.  As shown in the screenshot below, there is monthly and quarterly data.

Monthly & Quarterly Scenario

From this, we want to create a chart on a chart sheet, on which we can easily switch between displaying monthly or quarterly values.

Final Chart

Creating the chart

Let’s start by creating a chart and formatting it.

Select the cells containing the monthly data, then click Insert > Clustered Column Chart.
Insert Clustered Column Chart

Format the chart however you wish.  In this example, I have:

  • Increased the Gap Width to 50%
  • Changed the fill color to dark gray
  • Removed the major gridlines
  • Set the vertical axis minimum to always start at zero
  • Removed the vertical axis
  • Added data labels
  • Positioned the data labels to be Inside End
  • Formated the data labels to be white, bold, size 11.

The formatted chart now looks like this:

Example chart - first view

Move the chart to a separate sheet

Charts can either be on the face of a worksheet or as a separate sheet by itself.  Switching between them is simple enough.

Right-click on the chart, select Move Chart… from the menu.

Right-click move chart

Select New sheet and give the chart sheet a name.  I’ve gone with the default value of Chart1.  Click OK.

Move chart to chart sheet

The chart now moves to a separate sheet by itself.

Create the drop-down list

Next, let’s create a drop-down list, which we will use to switch between the monthly and quarterly options.

Enter the values Monthly and Quarterly in a list on the worksheet (I’ve used cells G3 & G4 on Sheet1)

Values for drop-down list

Display the Developer ribbon

Right-click on a blank part of the ribbon and select Customize the Ribbon… from the menuCustomize Ribbon to display Developer Ribbon

From the Excel Options window click Customize Ribbon, enable the Developer option then click OK.
Display Developer Ribbon in Excel Options

Select the chart sheet, then click Developer > Insert > Combo Box (Form Control).  While I’ve used a Combo Box, any object with a cell link will work.

Insert form control from Developer menu

The mouse changes to a small cross.  Click and drag the mouse on the chart in the position where you wish to position the drop-down box.  We can easily move and resize it later if it’s not quite right.

Right-click on the drop-down, select Format Control…

Right-click drop down format control

For the input range, use the list we created a few moments ago.

For the cell link, select any worksheet cell. I’ve used cell H3 on Sheet1.

Enter values into Format Control box

The cell chosen as the cell link displays the selected position within the drop-down.  If Monthly is selected in the drop-down, cell H3 displays 1, if Quarterly were selected, it would disoplay 2.

The drop-down box can now switch between the options in the list; it won’t change the chart yet, as it’s not currently linked.

Create dynamic named ranges

The key to the switching functionality is using a named range as the source for the chart.  So, let’s create some named ranges.

Click Formulas > Name Manager.

Ribbon Formula - Name Manager

In the Name Manager dialog box, click New.

Name Manager - New

Give the named range a name (I’m using ChartSource in this example).

In the Refers To box, we are going to enter a formula.  There are many formulas that we can use to create a dynamic range. For this scenario, we are going to use CHOOSE.

The arguments of the CHOOSE function are:

=CHOOSE(Index_num, Value1, Value2, [Value3]...)
  • Index_num = Specifies which value argument to select
  • Value1 = The value to return if the Index_num is 1
  • Value2 = The value to return if the Index_num is 2
  • Value3 = The value to return if the Index_num is 3… etc.

CHOOSE can handle up to 254 values; in our example, we are only going to use 2.

  • Value1 will be the monthly values
  • Value2 will be the quarterly values

The Index_num will reference the cell link from the drop-down box. Therefore, when the drop-down changes, CHOOSE returns either the monthly or quarterly data.

=CHOOSE(Sheet1!$H$3,Sheet1!$B$3:$B$14,Sheet1!$E$3:$E$6)

Named range with Choose Formula inside

Click OK to close the New Name box.

Repeat the same steps to create a named range for the labels.

Named range for chart labels

We should now have two named ranges set-up.  One for the chart data and the other for the chart labels.

Named ranges created in name manager

Click Close to close the Name Manager dialog box.

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Used named ranges in the chart

Next, let’s use the named ranges in the chart.  Right-click on the chart and click Select Data… from the menu.

Right click chart then select data

Click the Edit button in the Legend Entries section.

Edit chart data

The Edit Series dialog box opens.  In the series values field, replace the range with the named range.  We need to retain the sheet name at the start of the named range.  If the sheet name has a space in it, then it must have single quotes around the name, followed by a ! (e.g., =‘My Sheet’!ChartSource).  However, if the sheet name is without spaces, then it doesn’t need single quotes (e.g., =Sheet1!ChartSource, as can be seen in our example below).

Use ChartSource Named Range as Source for Chart

Click OK to close the Edit Series window.

Repeat again for the chart labels.

Apply ChartLables named range

Click OK to close the Select Data Source dialog box.

Now we can test the drop-down.  It should be possible to switch between the monthly and quarterly options.

Switching chart

If your chart formatting changes when switching between the charts (as shown above), don’t worry, we can fix that.

Change chart series formatting

Start with the formatted chart selected.

Click File > Options.  In the Excel Options window, select Advanced, scroll down to the chart section, and untick the Properties follow chart data point for current workbook option.  Click OK to close the options window.

Uncheck the Properties follow chart data points for current workbook

Now, try the chart drop-down again; the formatting should remain.

Create a dynamic chart title

Currently, the title of the chart just says Value.  Let’s make that more dynamic too.  In cell I3 of Sheet1, enter the following formula:

=INDEX(G3:G4,H3) & " Stock"

This will display Monthly Stock or Quarterly Stock depending on the drop-down selection.

To add this to the chart title, click on the heading, enter the = symbol into the formula bar, then click on the cell containing the title we created.

Include Dynamic Title for Chart

Conclusion

There we have it; a chart sheet where the user can select between a quarterly or monthly option.

Final Chart

There are lots of charting techniques that use the named range methodology.  While we’ve used CHOOSE to switch the chart source, INDEX, OFFSET and XLOOKUP are also good alternative options for applying this methodology.

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

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