How to create re-sorting chart in Excel

Excel 365
When creating comparison charts, two different approaches are used:
- comparative analysis of some parameters of one key element - the favorite one (product, brand, etc.) with the parameters of competitive elements (products, brands, etc.),
- comparison of the parameters of a certain list of elements without favorites.

One of the most common tips when creating comparison charts without favorites is to sort the data by some key parameter.

In Excel, you can easily create a chart in which you can switch the option by which to sort the proposed elements of the comparison list:

Dynamically reordering chart in Excel 365

To create an interactive chart, add one of the controls: Option Buttons (radio buttons), Check Boxes, or Combo Box (drop-down list). See other techniques for creating interactive charts.

To create a re-ordering interactive chart with option buttons, do the following:

   1.   Prepare the data that will be shown in the chart

Add the new data to select one of the parameters.

For example, enter in the cell F2 the number 1:

Dynamically reordering chart data in Excel 365

   1.2.   To the next cells, add the following formula:

= SORTBY (<table>, CHOOSE (<index>, <first item>, <second item> ...), [<sort order>])

The SORTBY (<array>, <by array>, [<sort order>], <by array2>, [<sort order2>] ...) function returns the <array>, sorted by the <by array> (and other <by array2>, <by array3>, etc. one by one if specified) in the <sort order>:

  • 1 - ascending (by default),
  • -1 - descending.

The CHOOSE (<index>, <first item>, <second item> ...) function returns the value from the list (<first item>, <second item>, etc.) specified by the first parameter <index>.

In this example:

= SORTBY (B2:D9, CHOOSE ($F$2, C3:C9, D3:D9))

So, you will see the sorted array by the chosen index:

Chart data in Excel 365

Note: You can define names instead of using this formula on the spreadsheet.

   2.   Create a chart

Create a simple bar chart or any other chart you prefer (see more about different charts in Excel) using the calculated data (or defined names).

For example:

Simple chart in Excel 365

   3.   Add the option buttons to the chart

   3.1.   On the Developer tab (see Show the Developer tab), in the Controls group, click the Insert drop-down list and then choose Option Button (Form Controls):

Controls, Option button in Excel 365

Note: You can also add to the Quick Access Toolbar (see how to add commands to the Quick Access Toolbar):

  • The entire Insert controls dropdown list:
    Insert dropdown list in Quick Access Toolbar Excel 365
  • The Option Button control.

Add as many option buttons as the number of series selected by these option buttons and place them where you want.

In this example, just two option buttons were used:

Options buttons for simple chart in Excel 365

   3.2.   Format Option buttons:

To create a more effective view, place the Option buttons under Legend points and remove buttons names:

   3.2.1.   Double-click on every Option button and change the label - remove it.

   3.2.2.   Right-click on each Option button and choose Format Control... in the popup menu:

Format Control in Excel 365

   3.2.3.   In the Format Control dialog box, on the Control tab, choose the appropriate cell in the Cell link field (in this example, F2):

Format Control Option button in Excel 365

The trick to using Option buttons is to assign the same cell to all the Option buttons in a particular group. Initially, the cell value is assigned to the first element of the option group. Next, all added Option buttons will automatically use and change the value of this cell.

Note: To change the sizes of the control or move it, right-click on the control to select it, then click it again to close the popup menu.

Please, disable AdBlock and reload the page to continue

Today, 30% of our visitors use Ad-Block to block ads.We understand your pain with ads, but without ads, we won't be able to provide you with free content soon. If you need our content for work or study, please support our efforts and disable AdBlock for our site. As you will see, we have a lot of helpful information to share.