How to create dynamic charts in Excel?

In Excel, if you have created multiple charts based on your range data series, and you want to make the charts look beautiful and clean. To do this, you can create the dynamic interactive charts in your worksheet, when you select one option, your corresponding chart will be showed as following screenshots. Here, I will introduce two types of interactive charts: Interactive charts using Drop down menu and Interactive charts using Option buttons. Interactive charts using Drop down menu doc-interactive-charts2 Interactive charts using Option buttons doc-interactive-charts23 Create dynamic interactive charts using Drop down menu Create dynamic interactive charts using Option buttons read less

Users will appreciate a chart that updates right before their eyes. In Excel 2007 and 2010 it's as easy as creating a table. In earlier versions, you'll need the formula method. If you want to advance beyond your ordinary spreadsheet skills, creating dynamic charts is a good place to begin that journey. The key is to define the chart's source data as a dynamic range. By doing so, the chart will automatically reflect changes and additions to the source data. Fortunately, the process is easy to implement in Excel 2007 and 2010 if you're willing to use the table feature. If not, there's a more complex method. We'll explore both. 1) The table method First, we'll use the table feature, available in Excel 2007 and 2010-you'll be amazed at how simple it is. The first step is to create the table. To do so, simply select the data range and do the following: Click the Insert tab. In the Tables group, click Table. Excel will display the selected range, which you can change. If the table does not have headers, be sure to uncheck the My Table Has Headers option. Click OK and Excel will format the data range as a table. Any chart you build on the table will be dynamic. To illustrate, create a quick column chart as follows: Select the table. Click the Insert tab. In the Charts group, choose the first 2-D column chart in the Chart dropdown. Now, update the chart by adding values for March and watch the chart update automatically. The dynamic formula method You won't always want to turn your data range into a table. Furthermore, this feature isn't available in pre-ribbon versions of Office. When either is the case, there's a more complex formula method. It relies on dynamic ranges that update automatically, similar to the way the table does, but only with a little help from you. Using our earlier sheet, you'll need five dynamic ranges: one for each series and one for the labels. Instructions for creating the dynamic range for the labels in column A follow. Then, use these instructions to create a dynamic label for columns B through E. To create the dynamic range for column A, do the following: Click the Formulas tab. Click the Define Names option in the Defined Names group. Enter a name for the dynamic range, MonthLabels. Choose the current sheet. In this case, that's DynamicChart1. You can use the worksheet, if you like. In general, it's best to limit ranges to the sheet, unless you intend to utilize them at the workbook level. Enter the following formula: =OFFSET(DynamicChart1!$A$2,0,0,COUNTA(DynamicChart1!$A:$A)) Click OK. Now, repeat the above instructions, creating a dynamic range for each series using the following range names and formulas: SmithSeries: =OFFSET(DynamicChart1!$B$2,0,0,COUNTA(DynamicChart1!$B:$B)-1) JonesSeries: =OFFSET(DynamicChart1!$C$2,0,0,COUNTA(DynamicChart1!$C:$C)-1) MichaelsSeries: =OFFSET(DynamicChart1!$D$2,0,0,COUNTA(DynamicChart1!$D:$D)-1) HancockSeries: =OFFSET(DynamicChart1!$E$2,0,0,COUNTA(DynamicChart1!$E:$E)-1) Notice that first range reference starts with row 2. That's because there's a row of headings in row 1. The second set of references refers to the entire column, enabling the formula to accommodate all values in the column, not just a specific range. The addition of the -1 component eliminates the heading cell from the count. The first formula (for the labels in column A) doesn't have this component. It's important to remember that you must enter new data in a contiguous manner. If you skip rows or columns, this technique won't work as expected. You might be wondering why I added the Series label to each range name. Using the name, alone, will confuse Excel. The series headings in row 1 are also names. Because the chart defaults will use the label headings in each column for each series name, you can't use those labels to name the dynamic ranges. Don't use the same labels for both your spreadsheet headings and your dynamic range names. Next, insert a column chart, as you did before. If you enter new data, the chart won't yet reflect it. That's because the chart, by default, references a specific data range, DynamicChart1:A1:E3. We need to change that reference to the dynamic ranges we just created, as follows: In the chart, right-click any column. From the resulting submenu, choose Select Data. In the list on the left, select Smith and then click Edit. (Remember the naming conflict I mentioned? Excel uses the column heading (cell B1) to name the series.) In the resulting dialog, enter a reference to Smith's dynamic range in the Series Values control. In this case, that's =DynamicChart1!SmithSeries. Click OK. Repeat the above process to update the remaining series to reflect their dynamic ranges: DynamicChart1!JonesSeries; DynamicChart1!MichaelsSeries; and DynamicChart1!HancockSeries. Next, update the chart's axis labels (column A), as follows: In the Select Data Source dialog, click January (in the list to the right). Then, click Edit. In the resulting dialog, reference the axis label's dynamic range, DynamicChart1!MonthLabels. Click OK. You don't have to update February; Excel does that for you. Now, start entering data for March and watch the chart automatically update! Just remember, you must enter data contiguously; you can't skip rows or columns. This formula method is more complex than the table method. Be careful naming the dynamic ranges and updating the series references. It's easy to enter typos. If the chart doesn't update, check the range references. read less

Dynamic chart can be created using index formula Step 1) Create a dropdown using Developer -> Form control -> Combobox Step 2) Get the selected value in different cell. To do this right click on combobox, Select input range as dropdown value, provide cell link as cell where you would like to put the selected dropdown index value. Step 3) using index formula INDEX(array,dropdown index value) here array is complete table, for dropdown index value K4 INDEX(C5:C16,$K$4) Step 4) Drag the same formula to all the cell horizontally. Step 5) Select result value and draw chart. read less