---

Article-Tutorial Created : 3rd March 2007

Created By : Tayo K. Solagbade

MS Excel Version : 2000

Downloads: 1.8MB Zipped Folder containing Tutorial Video(.avi) & Finished Workbook (Click here to watch the video demonstration and/or request your FREE download).

---

Description/Purpose:

In this tutorial I demonstrate how you can quickly create a chart(Clustered Column Bar) whose plotted data can be changed quickly and easily with a single mouse click(in a drop menu) so that it plots corresponding data for any ONE of five different years - WITHOUT USING VISUAL BASIC CODE.

Tools And Functions Used:

a. Combo box from the form toolbar

b. Index/Match formulas

c. Transpose function

d. Range Names

e. Clustered column bar chart type

Data To Be Worked On:

The data is contained in a table titled "Monthly Sales Of Bottled Water" showing sales figures for five years(1995 to 1999) on a row by row monthly basis in adjacent columns.

a. Cells E5 to E16 hold the names of the different months from Jan to Dec.

b. Cells F4 to J4 hold the Years from 1995 in cell F4 to 1999 in cell J4(running horizontally across the top of the table).

c. Cells F5 to F16 hold sales figures for 1995 from Jan to Dec, row by row. Cells G5 to G16 hold sales figures for 1996 from Jan to Dec, row by row and the arrangement continues until cells J5 to J16 which hold sales figures for 1999 from Jan to Dec, row by row.

Click the image below to see a larger picture of the data table to be worked on. Note that(as is obvious in the video tutorial) at the start of the demonstration ONLY the data table exists - the combo box drop menu control, the chart and the data in cells B5 to B16 are only added DURING the course of the demonstration.

Action Steps:

(Watch the video provided for this tutorial, as you read the steps below. Press Alt+Enter key to watch in "Full Screen mode", which is much clearer when you're using Windows media player)

1. I start by clicking "Help > About Microsoft Excel" on the main menu bar to show that I am working in MS Excel 2000 - so virtually anyone who tries the stuff they see in this tutorial should have no problems getting the same results.

2. Next I right-click on the main menu bar and click "Forms" on the shortcut menu that appears to bring up the "Forms toolbar".

3. On the forms tool bar I click the icon for the "combo box drop menu" control and drag between cells a2 and b2 with my left mouse button pressed down, then release it once I'm satisfied with the resulting size of the drop menu that appears.

4. Next, I highlight cells E5 to E16(containing the names of the months) and name that range as "nfMonthsList" using the 'Range Name" box/field to the left of the formula bar.

Note: I do this to make it easier later on to use that set of cells in a formula. Range names are often more convenient for use in formulas than cell addresses - though I have found certain situations when they become a hindrance. See other tutorials to come.

5. I also name the portion of the sales data table containing the actual sales figures(i.e. range F5 to J16) with the name "dbase" for the same reason given above.

6. I need to be able to use the combo box drop menu I earlier created to choose different years (from 1995 to 1999). I need to arrange them vertically so that I can assign the vertical range containing them to the "Input Range" of the combo box control. To get this vertical arrangement from the horizontal one provided in the table, I use the "Transpose" function in an array formula as follows:

a. I mouse-select(i.e. "highlight") a vertical range of blank cells (C12 to C16) corresponding to the number of cells I want to transpose - in this case five(5): 1995 to 1999(i.e. range F4 to J4) .

b. Then I type "=Transpose(" and click the first of the five horizontally arranged years and drag horizontally to include the last in cell "J4".

c. Next (and this is one you do not see in the video), I press on my keyboard the "Shift+Ctrl+Enter" keys simultaneously to automatically insert the array formula's braces around the Transpose function into the highlighted cells(C12 to C16).

Note: If you do this right, you will find the horizontal range of values will appear in the vertical range you selected. Looking inside the cells, you will notice the function has { } braces around it, but when you press F2 (function key at top left end of your keyboard) or double click to edit it, the braces disappear. If this does not happen then you've got it wrong.

7. Now that I have my vertically arranged sequence of years, I name the range of cells(C12 to C16) with "nfYearsList" - again for ease of reference at a later time.

8. Now I want to make the list of years appear in the combo box drop menu control I created earlier on in step 3. above. To do this I right-click on it and select "Formal Control".

9. In the dialog box that appears I then type "nfYearsList' in the "Input Range" field to make the drop menu show the list of years when it ic licked.

10. I then click the range selector image to the right of the entry field for "Cell Link" to enable me click on cell "C2" in the worksheet to specify it as the cell where the "position"(in the list of years) of the year chosen will appear. This will correspond with the column number for that year in the "Dbase" field earlier named.

11 Next I click/select cell B5. I type the following Index/Match formula "=INDEX(dbase,MATCH($E5,nfMonthsList,0),$C$2)". Notice how I click on "E5" in the formula text, and press F4 key repeatedly to toggle the dollar($) sign addressing on cell E5 from absolute to mixed till I see that the column letter is made absolute(i.e. has a dollar sign before it) while the row number is kept relative(no dollar sign before it).

Note: This helps later on when I have to copy the formula down from the cell for Jan up to Dec(i.e. B5 to B16). Note that cell C2's cell addressing in that formula is made absolute(dollar signs in front of both the column letter and row number). This is necessary because ALL the formulas - from jan to dec - must refer to it to determine the current year's column number.

12. Once I get the relative and absolute addressing of the cell references in the first formula (for January) right in cell B5, I then copy the formula down to the other 11 rows: B6 to B16). Excel automatically causes the text "E5" to change to "E6" from January to February and so on in the formula. Note also how the formula returns the sales figure for bottled water in the corresponding row it is in, which matches the column number for the year chosen.

IMPORTANT NOTE: The logic applied here in using the Index/Match formula to extract data from a table is something I have used - in different variations(sometimes in conjunction with SUMPRODUCT formulas) - to produce quick/convenient reporting interfaces covering one to five years of data for clients. So simple, yet so powerful!

I encourage you to try your hands at doing this on your own. Once you know how to use it, you'll be amazed just how easily you'll be able to automate a lot of your routine data handling and report generation needs. I will in future tutorials elaborate on how to use some of the concepts/tools mentioned in this tutorial.

Download the finished workbook and study the changes that occur as you choose different years from the combo box drop menu.

Note: The Index/Match formula returns the cell value on the row it is used in that corresponds to the column number in cell C2 at any point in time. I exploited the last argument in the formula by making it variable: instead of entering a number directly into it, I entered a cell reference which I cause to change by choosing a year(1995 to 1999) from the combo drop menu. For instance 1999 is in column no. 1 of the range "Dbase" and 1999 in column no. 5. That's why when you click each year, those corresponding numbers appear(1 and 5) in cell C2.

13. Finally, I highlight the new vertical range(cells D5 to D16) of values generated by choosing a year from the drop menu. Then I click on the shortcut chart-plotting icon(on the MS Excel Standard Toolbar) to bring up the Chart Wizard dialog. I click "Next" to accept the default "Clustered Column Bar Chart" type.

14. Next I click the "Series" tab that appears at the top of the Chart Wizard Step 2 of 4 dialog box and click on the "range selection" icon for the "Category(X) labels entry field. I then use the mouse to highlight the range of cells from Jan to Dec, to make them appear on the X-axis of the chart.

15. I click "Next" again and click finish to add the resultant chart to the worksheet. In the remaining seconds of the video, I re-size the chart and then begin using combo box drop down menu control to change the years and show the bars in the chart changing as the data for the years changes.

Comments: What I have done in this tutorial is to show you just ONE possible way to achieve this kind of result. There are other methods and variations that I know can be used AND some that I may not even be familiar with. I offer my ideas simply for you to consider in making up your mind. You data handling and report generation needs will help you determine what approach is most suitable for you.

IMPORTANT NOTE: Normally, you would have been able use the form below to email a copy of the tutorial video(and a video of my most recent Excel software) to yourself.

Unfortunately, due to repeated incidences of abuse by spammers/hackers, I have had to remove the file mailer script that works with the form.

To get the listed downloads, you will therefore have to fill/submit THIS web form(click the preceeding link) to request them.

 

XLH Tutorial No. 1 Video Demo(Plot A Graph Using Dynamic Data)
XLH Demo Video For Tayo Solagbade's GAM1 Software(GAM=General Accounts Manager)
E-Mail:
Name:
Comments:

*Do NOT click the "Email Checked File(s) To Me" button as it will not work.