Tag Archives: dropdown

cascading drop-down

Excel Cascading drop-down (no VBA!)

1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.75 out of 5)
Loading...

Cascading drop-down are a very useful feature in Excel making it much easier to categorize your records. Say you have a list of records you want to associate with categories and subcategories. Normally you would start by assigning a category to each record and then have a problem with matching a subcategory.

What is a cascading drop-down?

Cascading drop-downs are drop-downs that filter their values based on the selection made on another drop-down (higher in the hierarchy). You can have any amount of cascading drop-downs e.g. drill-down from store department, through the products lane, down till the product family and finally the product SKU.
See the example below:
cascading dropdown
What to play with a online example? Telerik controls have a nice mock-up Cascading DropDownList for this.

How to make a cascading drop-down

Now down to business. Let’s go through the simple steps of creating a 1 level cascading drop-down consisting of 2 drop-downs. Simply repeat these steps to create additional cascading drop-downs levels.

Prepare source data for the drop-downs

I prepared a simple data set consisting of countries and associated cities.

Cascading dropdown: Source worksheet
Cascading dropdown: Source worksheet

Notice that the countries are repeated for each city. This is because we need to map out each city with an individual country for what we are about to do in the next step. If you need to add an additional level for the cascading drop-down you should repeat this approach.

Create a named range for each category

Create a named range for each country by selecting all cities within the country. Repeat this for all remaining countries. In case you need to add new subcategories (cities) to your cascading drop-down insert rows in the middle of a country section. This will automatically extend the named range.

Now for the the important part!!! Remember to name your countries using the exact name of the country. In case there are spaces in the names (which are not allowed in named ranges) replace them with “_”. This post handles such cases equally.

Cascading drop-down: Creating the named ranges
Cascading drop-down: Creating the named ranges

Create a unique list of unrepeating items for the first drop-down

You can either create this list manually or to just listing the countries manually for the first drop-down. I personally prefer to have the list created automatically based on the first column of countries in the examples above – use the formula below for that:

It’s best to use the same worksheet as the source tables and use the formula below to extract a unique repeating list of countries. This is an Array Formula so remember to hit CTRL+SHIFT+ENTER when editing the formula (first line only). Then simply drag/copy it down to get all countries.

Cascading drop-down: First drop-down
Cascading drop-down: First drop-down

Array Formula for unique list of unrepeating countries
=IFNA(INDEX(OFFSET($A$1;1;0;COUNTA($A:$A)-1);MATCH(0;COUNTIF(E$1:$E1;OFFSET($A$1;1;0;COUNTA($A:$A)-1));0));"")

Create a named range for the first drop-down

Assuming your list of countries is in column E of the same worksheet as shown above feel free to use the formula below for the named range – it will update the list automatically whenever you add new countries. Alternatively simply select the whole list of countries – but remember to update the named range manually when adding new countries.

=OFFSET(Dictionary!$E$1;1;0;COUNTA(Dictionary!$E:$E)-1)

This is an example using the formula above:

Cascading drop-down: Named range for list of countries
Cascading drop-down: Named range for list of countries

Create the cascading drop-down based on the named ranges using Data Validation

You are almost there! Now go to the worksheet where you want to define your cascading drop-down. And add Data Validation for the first and second cell as shown below:

First drop-down
Go to Data Validation and define the list based on the named range we created in Step 4:

Cascading drop-down: First drop-down
Cascading drop-down: First drop-down

Second drop-down
Now let’s repeat the same exercise and again for the second cell we need to add Data Validation. This time we will use the INDIRECT function to dynamically associate the correct named range of cities based on the country selected in the first cell.

Assuming the first drop-down is defined in cell A2. Set the following formula for your Data Validation:

=INDIRECT(SUBSTITUTE($A2;" ";"_"))

See example below:

Cascading drop-down: Second drop-down
Cascading drop-down: Second drop-down

That’s it this is the final result:
Cascading drop-down: Result
Cascading drop-down: Result

Download the example

Feel free to download the example used above:

Follow me on Twitter or Facebook for more Tips!

dynamic excel charts

Use Form Controls to make Dynamic Excel Charts (no VBA)

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

Dynamic Excel charts are so much appealing to users and more pleasant in interaction. Fortunately Excel has PivotCharts and Slicers. But what if you want to have more control over your charts and use other controls such as Dropdowns, ScrollBars and Radiobuttons? Today I will show you how to easily use Form Controls to add interactivity to your Excel Charts without any VBA nor PivotTables/PivotCharts.

Form Controls

What are Form Controls? Form Controls can be accessed from the Developer ribbon in Excel (File->Options->Customize Ribbon). They are simple controls that can be dragged-and-dropped onto your Worksheets and easily configured.

Excel Form Controls
Excel Form Controls

What makes Form Controls special? They are configurable and can be linked to cells in your Excel Workbook. Look below for an example of how the ScrollBar properties look like. Notice especially the Cell Link property which will output the current value of the Form Control (in this case the scroll value). This opens a whole lot of different possibilities to use Form Controls in calculations/charts etc.
Example Form Control Properties
Example Form Control Properties

Dynamic Excel Charts with a ScrollBar

Let’s consider a first example with a ScrollBar.
On the left there is a simple table with 3 columns:

  • Company – name of a company
  • No. of employees – number of employees in the company
  • Total – the number to be presented in the chart cut off by the ScrollBar (only showing values above)

Notice that the Total formula take the value from the ScrollBar to set the current cut-off. By scrolling the ScrollBar notice that the formulas will automatically recalculate and the chart will refresh to show only values above the cut-off. Neat huh?

Excel Dynamic Chart with ScrollBar
Excel Dynamic Chart with ScrollBar

I tend to usually overlay the Form Controls over the chart itself so it is more obvious for the user that the chart is dynamic.

For Excel 2013 users – you can also use timeline scrollbars natively in PivotTables/Charts (available on the Analyze ribbon tab).

Dynamic Excel Charts with a Dropdown

Let’s now consider a different example using a Dropdown form control.
Again on the left side notice the data source table. I have simply split the employee column into two sections: IT employees and non-IT employees.

We will also create a dropdown with 3 options:

  • IT – sum only IT employees
  • Non-IT – sum only non-IT employees
  • All – sum all employees

Having these three categories we can link them to the Dropdown control so they appear when click on the bottom arrow. Simple right? Now let’s link the H2 cell with the Dropdown control value and we can now see the changes in the selection of the Dropdown. Notice that the Total cell formula with show different value depending on the selection in the Dropdown. Easy right?

Example Dropdown Form Control
Example Dropdown Form Control

Again I would recommend overlaying the chart with the Dropdown to make it seem like a natural part of the Excel Chart.

Dynamic Excel Charts with a Radiobuttons

Radiobuttons work similarly as Dropdowns with the difference that you have to group them using the GroupBox control to achieve mutual exclusion. See the example below:

Example FormControls Radiobutton
Example FormControls Radiobutton

Next steps

Excel Google Charts Tool – Learn how to add cool Google Charts to your Excel Workbook

excel dynamic named range

Creating a dynamic named range in Excel

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

When making complex Excel solutions is often the case that you will need to manage many different lists of certain values e.g. for dropdowns, formulas etc. It is a real pain to manage lists that change very often. The clean solution is to define a dynamic named range which will adapt to the list of parameters within a certain column.

Defining a dynamic named range

Create a list of items

Some Excel Range list
Some Excel Range list
Create your list of items. It is often best to keep your lists on a separate Worksheet and each column topped with a header explaining what each list contains. Watch out for BLANK as they will not be supported by the dynamic named range.

Open the Excel Name Manager

Go to the FORMULAS ribbon and open the Name Manager within the Defined Names group.

Create a New Named Range

Hit the New.. button to create a new Named Range. Next provide the name for your Excel dynamic named range.

Create a new Excel Named Range
Create a new Excel Named Range

Remember that Excel range names cannot contain spaces and need to start with a letter character

Provide the dynamic named range formula

Provide the formula for your dynamic named range. Assuming your worksheet name is NameOfWorksheet the formula should look like this:

=OFFSET(NameOfWorksheet!$A$1;1;0;COUNTA(NameOfWorksheet!$A:$A)-1)

See an example below:

Dynamic Named Range
Dynamic Named Range

How does the dynamic named range formula work?

How does it work? Well the OFFSET function takes 5 arguments:

  1. the reference cell
  2. the offset number of rows to move
  3. the offset number of columns to move
  4. how many rows of data to return (optional)
  5. how many columns of data to return (optional)

See now that what our dynamic named range formula does is:

  • move 1 cell down from the “List of names” cell to the first cell of the list
  • return a range of unempty rows – for as many rows as there are un-empty cells in the entire column minus 1 (minus the first cell of the column)

Therefore to sum up, the formula returns a range of all unempty cells within the given column offset by 1 row (fro the header). The definition of the Named Range is a formula hence will recalculate automatically.

The Named Range formula recalculates based on the calculation settings. If you turn of Automatic Calculation be aware that the Named Range will need to be recalculated manual or else it might show an outdated range if you add/remove rows

Making an Excel dropdown with a dynamic populated list

Let’s now make a common use the list of names which we defined to create an Excel dropdown. This way the dropdown will only be populated with the items defined in the dynamic named range list.

Dynamic Excel Dropdown
Dynamic Excel Dropdown

Good practice to using dynamic named ranges

My personal experience is that lists should be in hidden worksheets (sometimes good to make it even “very” hidden) in which each column will represent a certain list of values which can be reference by a certain dynamic named range. This will make it easy for you to manage your lists and not worry about the number of their items increasing or decreasing.