Here is an example of a dynamic chart that changes based on the selected cell. Move up or down to see more data points for a category, move sideways to switch between categories. Done with a SelectionChange macro and dynamic ranges. Show You may know about dynamic charts and how to construct them. You may even have done a few yourself. Most often, dynamic charts involve some kind of parameter selection and one or more dynamic ranges that feed the chart series to automatically display something like the last few rows of a data set. In this article, you can read how to construct a dynamic chart that changes its data when you move the cell selection in the underlying data table. Let’s start out with some data. We have daily readings for several categories of fruit:  Daily data for several categories The goal is to create a chart that shows the values for one fruit category over a number of days. Something like this: Creating a chart like this is fairly simple. Select the data, column A for the X axis, one of the fruit columns for the Y axis, create a column chart and do some basic formatting to get rid of the Excel defaults, especially the ugly dark grey background in Excel 2003. Now the goal is that if the user wants to view another fruit category, there should be no drop-down boxes or check boxes to select. Simply using the arrow keys to move around the data table should change the chart display. OK. Here’s how it’s done. We need:
Fixed named ranges: In the attached sample file range names have been assigned to these cells: L2 = cCol — the active cell’s column number L3 = cRow — the active cell’s row number L4 = cRows — a count of the populated rows in the data table. Leave L3 and L3 empty. They will be filled by the macro created in the next step. In L4, we need to count how many rows of data we have. This will be based on column K, so the formula is: =COUNTA(K:K) A macro to record the current cell: In order to fill column K with the data for the series to be charted, we need to determine which column currently hosts the active cell, i.e. the cell we clicked last or moved to with the arrow keys. This can be done by creating a small macro in the sheet module of the active worksheet. Hit Alt-F11 to open the Visual Basic Editor, find the VBA project for the workbook and double-click Sheet1 in the left hand navigation pane. Then enter this code into the code window: Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'only change the cCol value if selected cell is in data range If Target.Column > 2 And Target.Column < 10 Then Range("cCol").Value = Target.Column End If Range("cRow").Value = Target.Row End SubThe macro assesses the active cell whenever the cell selection changes, i.e. whenever you move the cell highlight with the enter or arrow keys. If the active cell is between columns 2 and 9 (that’s where our fruit categories are) then the column number will be written into the cell called cCol (we defined L2 with that name above. Also, the current row number will be written into a cell called cRow (which we assigned to L3 above). Helper Column: Leaving column J empty we use column K as a helper column to hold the values of the data series that we want to chart. The macro we’ve just entered provides us with the column index of the active column. This value can now be used to pull the data of that column into column K. The formula for K1 is: This formula looks at the range from A1 to I1 and returns the nth cell in the range, where n is the value in the cell cCol. If cCol holds a 4, then the 4th cell in the range will be returned, i.e. cell D1. The formula needs to be copied down all rows of the data table. Now, whenever the active cell changes to a different column, the data in column K will recalculate and show the data of the column where the active cell is. We can now easily construct a chart based on column K and see the chart change whenever a different column is selected. The added twist now is to also influence how many data points, i.e. rows of data, are displayed in the chart. For this, we use Two dynamic ranges: The goal is to chart only the values from the current cell and below, so we need to define a chart range that does not include the whole of column K. In Excel 2003 click Insert – Name – Define. In Excel 2007 and later click Define Name on the Formulas ribbon. Enter the name “DateRange” (without the quotes) into the Name field and the following formula into the Refers To field Define a second range name as ChartRange with the formula Let’s look at the ChartRange formula: the first INDEX function looks at the whole of column K and returns the row specified by the value in cRow. The second INDEX function looks at the whole of column K and returns the last populated cell in that column (from the named range cRows in L4 defined above). So, if our active cell is in G26, column K will have the values of column G, and the ChartRange will be K26:K33 in the attached data sample. The DateRange is calculated with the same principle to provide the correct X axis labels. Putting it all together – As a last step, we now need to define the source data for the chart and replace any hard-coded ranges with the range names. One way is to click the chart series and edit the formula in the formula bar to be: Another way is to edit the source data (Excel 2003: click the chart, then click Chart – Data source – Series tab. For Excel 2007: click the Design Tab in the Chart Tools, then Select Data – click the series – click Edit) and enter the “=Sheet1!DateRange” as the X axis definition and “=Sheet1!ChartRange” in the data values field. Make sure that the series name is defined as “=Sheet1!$K$1”, since that cell automatically updates with the name of the data series currently selected. If this was all a bit over your head, don’t worry. Just download the attached file and let your fingers do some walking. You can copy and paste your own data into the worksheet. If you insert or delete columns, you need to adjust the macro to specify which columns hold your categories. Adding or deleting rows won’t affect the performance, since the data range is calculated automatically. The attached file was created in Excel 2010 and has been saved in Excel 2003 compatible format. chart by selected cell.xls
Home ➜ Excel Charts ➜ Dynamic Chart Range I have a strong reason for you to use a dynamic chart range. It happens sometimes that you create a chart and at the time when you update it you have to change its range manually. Even when you delete some data, you have to change its range. Maybe it looks like that changing a chart range is no big deal. But what, when you have to update data frequently? You do need a dynamic chart range. Are you sure, I need a Dynamic Chart Range?Yes, 100%. Alright, let me show you something. Below, you have a chart with the month-wise amount and when you add the amount for Jun, chart values are the same, there is no change. Now the thing is, you have to update the chart range manually to include Jun in the chart. So what do you think, using a dynamic chart range is a time-saver? Using Data Table for Dynamic Chart RangeIf you are using the 2007 version of excel or above then using a data table instead of a normal range is the best way. All you have to do, convert your normal range into a table (use shortcut key Ctrl + T) and then use that table to create a chart. Now, whenever you add data to your table it will automatically update the chart as well. In the above chart, when I have added the amount for Jun, the chart gets updated automatically. The only thing that leads you to use the next method is when you delete data from a table, your chart will not get updated. The solution to this problem is when you want to remove data from the chart just delete that cell by using the delete option. Using Dynamic Named RangeUsing a dynamic named range for a chart is a bit tricky but it’s a one-time setup. Once you do that, it’s super easy to manage it. So, I have split the entire process into two steps. Creating a Dynamic Named Range for Dynamic ChartTo create a dynamic named range we can use OFFSET Function. Quick Intro to Offset: It can return a range’s reference which is a specified number of rows and columns from a cell or range of cells. We have the following data to create a named range. In column A we have months and amounts in column B. And, we have to create dynamic named ranges for both of the columns so that when you update data your chart will update automatically. Download this file to follow along. Here are the steps.
At this point, we have two named ranges, “month” & “amount”. Now, let me tell you how it works. In the above formulas, I have used the count function to count the total number of cells with a value. Then I have used that count value as a height in offset to refer to a range. In the month range, we have used A2 as starting point for offset and counting the total number of cells having in column B with counta (-1 to exclude heading) which gives reference to A2:A7. Changing Source Data for the Chart to the Dynamic Named RangeNow, we have to change source data to named ranges we have just created. Oh, I am sorry I forget to tell you to create a chart, please insert a line chart. Here are the further steps.
All is done. Congratulations, now your chart has a dynamic range. Sample FileLast WordsUsing a dynamic chart range is a super time saver & it will save you a lot of effort. You don’t have to change your data range again and again. Every time when you update your data your chart is instantly updated. More Charting Tips and Tutorials |
Excel chart data range based on cell value
zusammenhängende Posts
Urheberrechte © © 2024 frojeostern Inc.
