Change Functions With Excel Drop Down List

Last year, I shared a technique for selecting a function name from a drop down list, and that changed the formulas in a summary row on the worksheet.

Select Function Name

For example, from the drop down list in cell C2:

• Choose the MAX function, to see the highest amounts in a lists of sales orders.
• Next, choose the SUM function, to see the total amount in the lists of orders
• Or, choose the COUNT function, to get the total count

This technique runs on formulas, not macros, and is a great way to show different information in a small amount of worksheet space.

It could be perfect for an interactive Excel dashboard, where people can choose the information they want to see.

Watch the Video

I’ve finally made a video that shows how to create this changeable summary, and create a drop down list of functions.

You can watch the video here, and for written instructions, and the sample file download, visit this page: Change Excel Function With Subtotals

As I show in the video, to alternate between functions, such as SUM, AVERAGE or MAX in a summary row, use the Subtotal function in your formulas.

Then, add a drop down list of functions on the worksheet, using the Excel data validation feature. Next, select the function you want, to see those results in the summary.

This technique does not require macros — the formulas create the changing summaries.

________________