Smart Dep. Drop-Down Lists in Excel:
Expandable & Exclude Blank Cells

In situations where contents of a dropdown list are frequently changing, it is helpful to have a dynamic dropdown list that can automatically expand, as well as exclude any blank cells in the cell references.

This example uses a sample data of App names for three divisions: Productivity, Games, and Utility.

Excel drop down list dependent

This is our data set:

Start Date will be on the X-axis and Tasks on the Y-axis

The difference between Start and End date is going to define the length of the task, i.e, the length of the bars.

This will be different to the # of working days.

Why?

Because # of working days excludes weekends and holidays.

What type of chart should I use?

One of my favourite techniques is error bars, and conveniently, you can use error bars to show the number of Working Days.

And because they’re going to be horizontal, you can only have horizontal error bars in chart types where there are numbers on the X-axis.

So which chart type is that?

Scatter plot.

With the scatter plot, there are numerical values on the X as well as the Y-axis, and that way  horizontal error bars can be used in the chart.

When using a scatter plot, there needs to be numbers on the Y. You can’t put text on the Y axis.

So, instead of the text, these tasks need to be numbered.

The full difference between the end date and the start date also needs to be calculated.

Video and Workbook

Feel free to Download the Workbook HERE.

Free Excel Download

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

GET 75% OFF THE COURSE
Free Ebook

Leave A Comment

Share This