Sunday, March 15, 2020

Home Excel tips Making Interactive Charts in Excel [How To]

Making Interactive Charts in Excel [How To]

March 15, 2020 Excel tips,

Making Interactive Charts in Excel [How To]

Graphs and charts are amazing tools to understand the information in the blink of an eye. It not only saves you from reading at all levels. That is why charts and Excel is one combination not to miss when you are learning Microsoft Excel application.

But sometimes even the charts are so laborious that you wish that some of the lines and numbers must be taken out to simplify and make it easy to digest. This is where interactive charts enters the scene.

Interactive means that it let you communicate with the information displayed i.e. you can toggle on/off some information, increase/decrease the amount of information and so much more. This not only make charts easy to understand but also very interesting! After all, humans love to interact

Understanding the concept

As stated earlier, interactive charts involve toggling information on/off or show/hide to make things easy for audience. For example you have sales data of four regions of five years. Chart will look something like this:

But what if we have the ability to show/hide sales from certain areas? For example displaying the report of only one area.

Excel charts are made up of underlying data. And if any change is made to it, charts immediately incorporate it. Therefore, if we want to hide lets say the sales of East region then we simply have to take that row out and chart will show only the sales of other three regions. We can do this by simply deleting the row or hiding it as the following animation shows:

But deleting or hiding the row is not an efficient way to do it as deleting is destructible and hiding requires manual effort every time. So we need to dig some better ways.

There are many ways to get this job done. And the method you choose depends on how the information needs to be presented and what is possible in that case. I will be starting with the simplest way and then moving to advanced and better methods.

Method 1: Learning “Hide and Seek”

Lets get straight into it and I will explain it at the end as it will be much easier for you to understand what is happening.

Open up worksheet named Simple and follow these steps:

Step 1: Select the data and go to cell C12 and paste the data. This way we will have a duplicate data just below the original one.

Step 2: Select the figures in the data you just pasted and hit delete key! Yes you read it right

Step 3: Put this formula in cell D13:

=IF($B3=”show”,D3,””)

Step 4: Click+hold+drag the fill handle right to populate the cells with the formula and finally double clicking the fill handle to populate cells downward as well.

Step 5: At the moment nothing must be visible. But if you type Show in the cells (from B13 to B16) then data of that row will appear. For now have “Show” written in all four rows from cell B3 through B6.

Step 6: Select the data with the years and region names. Go to Insert > Line chart > Select anyone (I went for the first).

So now we have a chart that is based on the data we duplicated earlier. To see the magic happen just select cell B15 and hit delete key! TADA! Sales chart for North region has disappeared. Go on and delete another one

Bonus Tip: Making it better

Instead of designing the solution in a way that you need to write “Show” to enable the row, you can tune the formula to accept anything. Writing 4 alphabets slows it down. So in cell D13 put this formula:

=IF($B3<>””,D3,””)

This formula is simply checking that if cell B13 is not empty then get the contents of cell D3 otherwise leave it empty. Populate other cells with the same formula using fill handle.

With this formula in place just write anything in cell B13 and it will get it working. Let it be 1 or X or Show. Following animation will help you understand:

Caution

This formula will still work even if you get into cell and hit space bar once. To the eyes there is nothing in the cell however for Excel having a space is a character and so data will still show up.

Method 2: Using data validation drop-down

With basics understood. Now we can add more advanced features to make it even more interesting. Until now we have learned how to show or hide the chart information using the content in a specific cell.

But as input is manual in previous technique, it is error prone and can cause our setup to fail. To avoid this we can use data validation lists.

With data validation lists, the user is limited to options available in the drop down, therefore, the probable effect of human error is significantly reduced. So lets learn how to get the same thing working with data validation in the game.

Step 1: Click worksheet named Data validation

Step 2: You already have the two similar sets of data as we saw earlier.

Step 3: Go to cell B3 and click Data tab in the ribbon and then click data validation button.

Step 4: Under Allow drop down select list and in Source input bar put this:

Show, Hide

Step 5: Drag the fill handle to apply the same data validation on the cells up to cell B6.

Step 6: Go to cell D12 and put this formula:

=IF($B3=”Show”,D3,””)

Step 7: Drag the fill handle to the right to column H and then double click the fill handle to populate it across the relevant range.

Select Show from all the drop down menus so that we can make the chart. Once all the data is visible, select it with the headers both on top and left. By the way I am talking about the second range of data that we just created using formula. Go to Insert > Line chart and pic anyone. I selected the first one. This will give you a basic line chart.

Now you can easily show or hide a certain region’s sale on graph using show/hide drop down. Following animation shows the whole process and also how it works once done:

Excel Form Controls

As the name suggests, form controls are meant to give user convenience while interacting in Excel environment. These controls provide different types of buttons, scroll bars, boxes and spinners etc. Today we are going to use some of these controls to pimp up our charts.

Method 3: Using check box

The magnet readers know that we have already seen check boxes in action when we learnt how to create edgy to-do lists.

Today we will have similar use but by connecting it to a chart.

Step 1: To use form controls you need developer’s tab which is not enabled by default. Right click on Home tab and select customize ribbon

Step 2: A big dialogue box will appear. On the list from the right find Developer tab and tick the check box with it. Click OK.

Now that you have the Developer’s tab you are all set to add form controls.

Concept is still the same. We will be working on a duplicate range which will be connected with original data via an intermediary switch which in this case will be a check-box.

Step 3: Go to cell B3 and click Developer’s tab > Insert drop down > Check boxes. This will not insert the check box by itself rather you will have to draw it.

Step 4: Draw the check box within cell B3. Don’t worry if it is not exactly in the center as we can adjust it later.

Step 5: Right click on the added check box and hit Edit text. Remove any text to have just the check box.

Step 6: Select cell B3 and drag the fill handle down to cell B6 to add check boxes to other cells as well.

Step 7: Now to the tedious task. We have to connect each check box to the same cell where they are residing. We cannot get these check boxes to work until we connect them to a source where they can give output. In other words, we can connect check boxes only indirectly and not directly with other elements in Excel except cells. Following animation shows how to get it done:

Step 8: Once all the cells are connected, if you check/uncheck the box you will see the connected cell showing TRUE/FALSE. And now using this output we can get to work.

Step 9: Go to cell D13 and put this formula:

=IF($B3=TRUE,D3,””)

Step 10: Drag the fill handle to column H and then double click the fill handle to paste the formula to the whole range.

Step 11: Make sure all the check boxes are ticked. Select the data in range C12:H16 and insert the line chart using the same process as we have discussed earlier i.e. Insert > Line chart > Chart 1 under 2-D charts.

Step 12: To make the data clean select cells B3 through B6 and hit Ctrl 1 to open custom number dialogue box. From the list on the left select custom and under Type input field punch in the following and click OK.

This will hide the unnecessary TRUE/FALSE argument from being displayed and make it look clean. Go on and test the graph by toggling check boxes

Method 4: Using Combo box

Combo box is much like data validation drop down in which user selects just one option. Therefore one combo box means just one area’s chart will be displayed at a time. So lets see how to get this control to work.

Step 1: Open the worksheet named Combo box that has the same data. This time however arrangement is a little different but don’t worry.

Step 2: Click Developer’s tab and then click Insert drop-down button and click combo box control.

Step 3: Draw the combo box as big as you like. I kept it sleek occupying exactly one row.

Step 4: Right click on the combo box and click format control. In the dialogue box go to control tab. Click the select range button of input range box and select cells C3:C6. This will pull the name of regions as list in the combo box.

Step 5: In cell link put $C$9 as value. We have selected cell C9 where combo box will give output. Now if you switch between options in the combo box the value in the cell C9 will change automatically to show which number of option is selected. For example if East is selected then cell C9 will report 1 and if South is selected it will report 4 as it is the fourth option.

Following animation walks you through all the steps:

Using these values in cell C9 now we can work out an interactive chart. But before that we need to have one thing done.

Step 6: Go to cell C10 and put this formula:

=INDEX(C3:C6,C9)

Simple yet very effective. INDEX formula is given an array or group of items in cells C6:C9 (basically four cells C6, C7, C8 and C9) and then asked to pick the item using the number reported in cell C9. So if C9 has 1 it will select first item in the group which is C6 (East). If C9 has 3 then it will select the third item in the group which is C8 (North).