Instructions for creating interactive charts in Excel with INDEX function

Excel makes it easy to create clear, concise charts from your raw data.However, sometimes the graphs create static and not interesting.So the following article will introduce a simple method to help you turn static Excel charts into dynamic charts.

A simple drop-down menu (drop-down menu) can transform a simple Excel chart into an illustration image capable of presenting a variety of information and it's easy to set up.Here's how to use the INDEX function and the basic drop-down menu to create interactive charts in Excel.

Create data

First thing to do is collect data.The image illustrated below is data about the time that appears on the screen of Star Wars movie characters.

Instructions for creating interactive charts in Excel with INDEX function Picture 1

You just need to follow the steps similar to the following example with your metrics.Sort data by row, there is no space between them.When completing the chart, you can switch the position between C-3PO and Darth Vader and the chart will update with the correct data.

Next, copy and paste the title row below the data table.

Instructions for creating interactive charts in Excel with INDEX function Picture 2

Now, in the third row from the title you just pasted, enter the word "Data" into a cell and enter a placeholder number in the box to the right next to it.From now on they will be placeholders as a basis for the drop-down menu.

Instructions for creating interactive charts in Excel with INDEX function Picture 3

When all is in place, we will join these elements with the INDEX function.Enter the following function formula on the two cells above theDataset:

 = INDEX ( $B $10 : $I $13 , $C $18 ,0 ) 

If you are new to using INDEX, let's analyze it.$ B $ 10: $ I $ 13 is the entire data that you want the formula to access, $ C $ 18 points to the cell that defines the display data (the number we put next to the Datasetcell).End the function with a column reference, but here we have specified the specific cell so the fill is 0.

The specifics of the function will vary depending on your data, so the image below shows the different components in the corresponding function on excel so you can do the same job.

Instructions for creating interactive charts in Excel with INDEX function Picture 4

Only the arrow in the corner of the cell contains the function formula, you will see the arrow turn into a plus sign, then drag it out across the row.

Instructions for creating interactive charts in Excel with INDEX function Picture 5

You will get the same results for the remaining cells as shown above.Now, you can manually change the number in the box to the right of theDataSet to use the INDEX formula. For example, enter 1in the box, the function will display data related to C-3PO.

Instructions for creating interactive charts in Excel with INDEX function Picture 6

Create a chart

Now the next step will be a little easier.We will choose the data we have after using the INDEX function - NOT the data that we enter manually - turn it into a chart.

To turn data into a chart, select Insert> Recommended Charts in the menu bar.

Instructions for creating interactive charts in Excel with INDEX function Picture 7

You can use any type of chart that best suits your data.In this example, the column chart seems most reasonable.

When the selection is complete, the graph showing the data will appear, test that everything is working by changing the number next to theDataSet.If the chart changes according to the data set you choose, then you have succeeded.

Instructions for creating interactive charts in Excel with INDEX function Picture 8

Add a drop down menu

Now, make this animated graph a bit more "friendly".Select theDeveloper tab , click the Insert drop-down menu, in the Controls menu select Combo Box (Form Control).

Instructions for creating interactive charts in Excel with INDEX function Picture 9

Create a drop-down menu wherever you want to place it but must be on the spreadsheet.In this example, the drop-down menu is located below the chart.

Instructions for creating interactive charts in Excel with INDEX function Picture 10

Right-click on the object and selectForm Control . Select the object for the data set in the Input rangefield.

Instructions for creating interactive charts in Excel with INDEX function Picture 11

The Cell link field needs to match the cell containing the number indicating the selected data set.

Instructions for creating interactive charts in Excel with INDEX function Picture 12

ClickOKand check the drop down menu.You will be able to select the data set by name and the chart will be updated automatically.

Instructions for creating interactive charts in Excel with INDEX function Picture 13

Once you're done, you can tweak the "look" to increase your spreadsheet's aesthetics, clean up some data to make the chart more compact.

For example, if you want to display all charts on the same page, copy and paste the chart, drop-down menu into a new worksheet.Then right-click on the drop-down menu and selectFormat Control.

Instructions for creating interactive charts in Excel with INDEX function Picture 14

All you need to do is addSheet1! in front of the previously selected cell. This will tell Excel to find cells on a different sheet than the chart and drop-down menu. Click OKand we have a dynamic chart on a new, more neat and neat sheet.

Instructions for creating interactive charts in Excel with INDEX function Picture 15

So you can give yourself an interactive chart on Excel with INDEX function.Hope you find this article helpful and share it with the people around you.

5 ★ | 1 Vote

May be interested

  • Instructions for creating superscript, subscript in ExcelInstructions for creating superscript, subscript in Excel
    the article i will guide you to write the superscript and subscript in excel.
  • How to draw flow charts in ExcelHow to draw flow charts in Excel
    in this article, readers will learn how to set up a flowchart environment and create an excellent flow diagram in excel. some links for readers to download free microsoft excel flow charts will be placed at the end of the article.
  • How to draw charts and graphs in Excel simply and quicklyHow to draw charts and graphs in Excel simply and quickly
    charts in excel help visualize data and support easy data analysis. you can create bar charts, pie charts, and line charts with just a few simple steps.
  • Instructions for creating charts in Excel 2007 or 2010Instructions for creating charts in Excel 2007 or 2010
    the chart is a very effective way of displaying data in computational or statistical programs, especially microsoft excel. in the tutorial below, we will cover the basic operations to create a chart from the data table in excel 2007 or 2010 version ...
  • How to create 2 Excel charts on the same imageHow to create 2 Excel charts on the same image
    the combination of 2 charts on the same excel image helps users easily show the data.
  • How to draw a pie chart in Excel 2016How to draw a pie chart in Excel 2016
    pie charts are the best way to show data, making your reports and summary sheets more scientific and logical, in today's article, tipsmake will help you write how to draw pie charts in excel. 2016 with quite simple steps.
  • Index (INDEX) in SQLIndex (INDEX) in SQL
    sql index (index) is a special lookup table that database search engines can use to quickly increase the time and performance of data retrieval.
  • Instructions for creating charts on PowerPointInstructions for creating charts on PowerPoint
    drawing a graph of powerpoint presentations is a basic operation. you can select different chart types for powerpoint presentations such as column charts, line charts, pie charts, bar charts, area charts, scattering charts.
  • MATCH function in Excel, usage and examplesMATCH function in Excel, usage and examples
    the match function in excel helps determine the position of a value in a list or range of cells. this is a useful tool when working with large data sets and can be combined with the index function to retrieve more precise information.
  • How to use the WRAPROWS function in ExcelHow to use the WRAPROWS function in Excel
    having trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.