Articles

How to Create Interactive Charts in Excel – Tutorial

Pinterest LinkedIn Tumblr

Excel Charts are a fun way to visualize your data. But normal charts restrict the user to select what they wish to see. However, interactive charts in Excel enables you to change the visual according to the choice of the user. This means you only have to build the report once and then the user can effortlessly create their own view instead of you having to create multiple permutations of the same report.

interactive charts in excel

Where can it be used?

Many times while presenting data, you might encounter a situation wherein, there is a lot of data to be presented. On the contrary, the user would want to examine the detailing one by one and not as a whole. A solution would be enabling the user to communicate and pick the desired information within the chart. Interactive charts in Excel perform the same.

For example, let’s say you are the product manager of a company. You are looking at historical data of order quantity for various products. Making a chart with the entire data can result in a chaotic experience for the user. Moreover, it would make it hard to interpret. But with the help of an interactive chart, you can choose a particular product and analyze its performance and trend.

Steps to create interactive charts

Let’s say you own an automobile shop, and you want to know the sale value of each product at once and not all in one chart. With the help of the following data let us try to create interactive charts to understand the sales value of each product.

excel data to understand interactive charts

Step 1 Copy data in a new spreadsheet, avoid working on the source data.

Data

Step 2 – Select the entire data excepting the total section, and create a line chart. To select a line chart
Manually – Go to the insert tab and select the line chart.
Shortcut Key Alt + N + N
The result will be as below.

line chart data result

The chart looks incredibly hard to study and understand as there are many lines. It is hard to focus on one single product and causes a chaotic experience. But with the help on interactive charts, we will be able to highlight or show the specific line’s information. This will be easier for the user to understand the data. Let’s continue

Step 3 Instead of having different colours for each line, we make all the lines to a dull grey coloured line. The reason behind this is while selecting a specific product, the respective line would be highlighted making it easier to comprehend.
1. Double click on the chart, which enables the “Format data series” to the right side of the screen.
2. Then select a single line, go to the colour option and select a colour, in this case, I am selecting a grey colour.
By doing this the colour of a line will be changed as shown in the picture.

formating data series

Tip: Under step 3 you changed the colour of a single line. Now to change the colour for all the lines, it can be time-consuming. In Excel, we have many shortcut keys. There is a shortcut key which redoes the last action “F4“. We can make use of it. So once done you can then use “F4” key. Below is the result.

highlighted in line chart

Step 4 Let’s reduce the thickness of the line to make it look better for visualization.
1. Double click on the chart, which enables the “Format data series” to the right side of the screen.
2. Then select a single line, go to the width option and change the width to 0.25 pt.
3. Then select each line and press “F4” to repeat the last formatting to the current selection.
By doing this the width of a line will be changed as shown in the image below.

less thick lines in chart

Step 5 – Here, we need a selection mechanism that helps to highlight a particular product. To do so we make use of a pivot table.
1. To create a pivot table, select entire data and go to Insert >> Pivot table or press Alt + N + V
2. Then drag and drop product line to the rows field as we require only the products.

selecting data for pivot table
Pivot table fields box

Step 6 – In this step, we add a slicer. Why slicer? A slicer allows a user to filter data in an interactive manner. Here, we want to highlight a particular line so adding a slicer helps to do that.
1. So, go to insert and add Slicer. Select Product Line.

inserting slicer

3. Copy the slicer and paste it near to the line chart.

pasting slicer near to the line chart

But in this step, when you select any product from the slicer the changes will affect only the pivot. As of now, there will be no changes in the chart. Therefore to make it work first we will link the pivot and then use a formula which will be linked to the chart.

Step 7 – In an empty cell nearby link the cell to the item in the pivot table. Simply type = and select the cell within the pivot table. Now when you filter based on the slicer the pivot table is affected and so is the linked cell.

linking cell to pivot table
cell linked to pivot table

 

Step 8 Extract the periodical sales data of the selected product using the cell which was recently linked to the pivot table. Here we use the VLOOKUP Function.
Formula: =VLOOKUP($B$16,$B$3:$J$10,2,)
The following picture extracts the data of 01-Oct-2019, copy the formula to the right side, and change the column number to get the data of other months.

extracting the data by date

After dragging and changing the column headers the data would look similar to the below image.

result after extracting the data

Step 9 Now is the part where we link the above-ascertained values to the chart. Copy the above data and paste it into the chart. It automatically highlights the selected product line as shown in the picture.

linking the ascertained values to the chart

Step 10 The final step is to modify your chart. Add a Chart title, remove legends, add data labels to the endpoint of the line. These are minor tweaks done to make your chart look attractive.
The final output looks as follows

interactive charts final output

Benefits of Interactive Charts in Excel

  • It enables the user to interact with the data provided.
  • A large amount of information can be presented in an easy way to analyze and understand the format.
  • Fits to present accurate data as expected by the user.
  • An interactive chart is also used to make a comparison of trends.
  • It helps to focus on one thing at a time.
  • No need to prepare static charts with different time zones.

Limitations of Interactive Charts in Excel

  • Sometimes people skip core messages.
  • It could reveal the data in an oversimplified way.
  • It is insufficient to add more colours to differentiate categories.

Conclusion

Interactive charts can help the user to focus on one thing at a time. The process to prepare could be lengthy, but the result is worth it. It also acts as a boon for marketing purposes and for tracking views of social network sites, as it provides an eloquent look for the data derived, it is a bane for enterprises which expects an elite representation of data using standard formatting styles. Nevertheless, interactive charts remove the need to create multiple charts.

To know more on interactive charts click here

Check out our Youtube channel for more videos.

Related Posts

Write A Comment