interactive diagram

Contents

High-quality visualization of a large amount of information is almost always a non-trivial task, because displaying all the data often leads to diagram overload, confusion and, as a result, to incorrect perception and conclusions.

Here, for example, data on exchange rates for several months:

Building a graph over the entire table, as you can easily figure out, is not the best idea. A beautiful solution in such a situation could be the creation of an interactive diagram, which the user can adjust for himself and the situation. Namely:

move forward-backward in the future-past along the time axis
zoom in and out of individual areas of the chart for a detailed study of the details of the chart
enable/disable the display of individual currencies to choose from

It might look something like this:

Like? Then let’s go…

Step 1. Create an additional table for the chart

In most cases, a simple but powerful technique is used to implement the interactivity of a chart – the chart is built not according to the original one, but according to a separate, specially created table with formulas that displays only the necessary data. In our case, the initial data will be transferred to this additional table only for those currencies that the user has selected using the checkboxes:

In Excel 2007/2010, you can apply the command to the created ranges Format as a table (Size as Table) from the tab Home (Home):

This will give us the following benefits:

Any formulas in such tables are automatically translated to the entire column – no need to “drag” them manually to the end of the table
When adding new rows to the table in the future (new dates and rates), the table sizes increase automatically, including the adjustment of ranges in charts, links to this table in other formulas, etc.
The table quickly gets beautiful formatting (interlaced fill, etc.)
Each table gets its own name (in our case, Table1 and Table2), which can then be used in formulas.

You can read more about the benefits of using such Tables here.

Step 2. Add checkboxes for currencies

In Excel 2007/2010, this requires displaying the tab developer (developer), and in Excel 2003 and later, the toolbar Forms (Forms). For this:

In Excel 2003: select from the menu View – Toolbars – Forms (View – Toolbars – Forms)
In Excel 2007: click the button Office – Options Excel – Show Developer tab on Ribbon (Office Button – Excel options – Show Developer Tab in the Ribbon)
In Excel 2010: File – Options – Ribbon settings – enable the Developer flag (File – Options – Customize Ribbon – developer)

On the toolbar or tab that appears developer (developer) drop down list Insert (Insert) choose a tool Checkbox (Checkbox) and draw two checkboxes to enable/disable each of the currencies:

The text of the checkboxes can be changed by right-clicking on them and selecting the command Change text (Edit text).

Now we will bind our checkboxes to any cells to determine whether the checkbox is enabled or not (in our example, these are two yellow cells at the top of the additional table). To do this, right-click on each added checkbox in turn and select the command Object Format (Size Control), and then in the window that opens, set Cell communication (Cell link).

Our goal is to have each checkbox linked to the corresponding yellow box above the currency column. When you enable a checkbox in a linked cell, it will display TRUE (TRUE), when turned off – LYING (FALSE). This will allow, in the future, to check related cells using formulas and display in an additional table either the rate value from the source table for plotting, or #N/A (#N/A)so that the graph is not built.

Step 3. Translating data into an additional table

Now let’s fill the additional table with a formula that will translate the original data from the main table if the corresponding currency flag is enabled and the associated cell contains the word TRUE (TRUE):

Note that when using the command Format as a table (Size as Table) in the first step, the formula has to use the table name and the column name. In the case of a normal range, the formula will be more familiar:

=IF(F$1,B4,#N/A)

Pay attention to the partial fixing of the link to the yellow cell (F $ 1), because it should move to the right, but it shouldn’t move down when copying the formula over the entire range.

Now, when the checkboxes are turned on or off, our additional table is filled either with data from the original table, or with an artificially created #N/A error that does not give a line on the chart.

Step 4. Create scrollbars for the time axis and zoom

Now let’s add scrollbars to the Excel sheet, with the help of which the user can easily shift the graph along the time axis and change the scale of its increase.

Scroll bar (scroll bar) we take in the same place as the checkboxes – on the toolbar Forms (Forms) or on the tab developer (developer):

We draw two stripes one after the other on the sheet in any suitable place – for shifting in time and scale:

Each scrollbar must be associated with its own cell (blue and green cells in the figure), where the numerical value of the slider position will be displayed. We will then use it to determine the scale and shift. To do this, right-click on the drawn stripe and select the command from the context menu Object Format (Size control). In the window that opens, you can set the associated cell and the minimum-maximum within which the slider will walk:

Thus, after doing all of the above, you should have two scroll bars, when you move the sliders along which the values in the related cells should change in the range from 1 to 307.

Step 5: Create a Dynamic Named Range

In order to display data on the chart only for a certain time interval, we will create a named range that will refer only to the necessary cells in the additional table. This range will be characterized by two parameters:

Indented from the beginning of the table down by a given number of lines, i.e. indented past-future timeline (blue cell)
The number of cells in height, i.e. scale (green cell)

We will later use this named range as the source data for plotting the chart.

To create such a range, we will use the function DISPOSAL (OFFSET) from category References and arrays (Lookup and Reference) – this function can create a link to a range of a given size in a given place on a sheet and has the following arguments:

A certain starting cell is taken as a reference point, then an offset relative to it is set by a given number of rows down and columns to the right. The last two arguments of this function are the height and width of the range we need. So, for example, if we wanted to have a reference to a range of data with rates for 5 days, starting from January 4, then we could use our OFFSET function with the following arguments:

=СМЕЩ(A3;4;1;5;2)

The trick is that the constants in this formula can be replaced with references to cells with variable content – in our case, the blue and green cells. You can do this by creating a dynamic named range with the function DISPOSAL (OFFSET). For this:

In Excel 2007/2010, click Name Manager (Name Manager) tab formula (Formulas)
In Excel 2003 and later, select from the menu Insert – Name – Assign (Insert – Name – Define)

To create a new named range, click the button Create (Create) and enter the name of the range and a link to the cells in the window that opens.

First, let’s create two simple static named ranges with names like Shift and Zoom, which will refer to the blue and green cells respectively:

Now a little more difficult – create a range with the name euros, which will be referenced using the function DISPOSAL (OFFSET) on the euro exchange rate data for the selected time period, using the Shift and Zoom ranges just created earlier and cell E3 as a starting point:

Please note that the name of the current sheet is used before the name of the range – this narrows the scope of the named range, i.e. makes it available within the current sheet, rather than the entire workbook. We need this to build a chart in the future. In newer versions of Excel, you can use a drop-down list to create a local sheet name Area.

Named range is created in the same way dollars for data on the dollar exchange rate:

And completes the picture range labels, indicating the x-axis labels, i.e. dates for the selected segment:

The overall picture should look something like this:

Step 6. Building a chart

Let’s select several rows in the upper part of the auxiliary table, for example, the range E3:G10 and build a diagram of the type Timetable (Line). To do this, in Excel 2007/2010 you need to go to the tab Insert (Insert) and in a group Diagram (Chart) choose type Timetable (Line), and in older versions select from the menu Insert – Diagram (Insert – Chart). If you select one of the lines in the created diagram, then the function will be visible in the formula bar ROW (SERIES)serving the selected data series:

This function sets the data and label ranges for the selected chart series. Our task is to replace the static ranges in its arguments with the dynamic ranges we created earlier. This can be done directly in the formula bar by changing

=ROW(Sheet1!$F$3;Sheet1!$ E $ 4: $ E $ 10;Sheet1!$F$4:$F$10; 1)

=ROW(Sheet1!$F$3;Sheet1!labels;Sheet1!euros; 1)

Having performed this procedure sequentially for the data series of the dollar and the euro, we will get what we were striving for – the chart will be built according to the dynamic ranges of Dollars and Euros, and the labels for the X-axis will be taken from the same dynamic range Labels. When you change the position of the sliders, the ranges will change and, as a result, the chart will change. When you turn the checkboxes on or off, only those currencies that we need are displayed.

Thus, we have a fully interactive chart where we can display exactly the piece of data that we need for analysis.

Smart Spreadsheets Excel 2007/2010