• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel CHOOSE Function Toggle

You are here: Home / Excel Formulas / Excel CHOOSE Function Toggle
CHOOSE Function
May 3, 2016 by Mynda Treacy

At first glance the CHOOSE function isn’t very exciting and typically you have to team it up with other functions to get the party started. Fair enough I suppose, after all, the more the merrier.

In this tutorial we’re going to use choose to toggle through different aggregation methods (AVERAGE, MAX, MIN, SUM) as seen here:

Excel CHOOSE Function trick

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

CHOOSE Function

But first a quick rundown for those not familiar with CHOOSE:

The syntax is:

CHOOSE(index_num, value1, [value2], [value3],….)

It returns a value from a list based on the index_num argument.

A simple example:

=CHOOSE(2, "Functions","PivotTables","Macros","Tables")

Would return PivotTables as it’s the second value in the list.

Whereas

=CHOOSE(4, "Functions","PivotTables","Macros","Tables")

Would return Tables because the index_num argument is 4, and Tables is the 4th value.

One of the unique features of CHOOSE is that the value arguments can be:

  • Numbers
  • Cell references
  • Defined names
  • Formulas
  • Functions
  • Text (as in the above examples)

CHOOSE Party

That’ a long list of options and provides a huge range of applications (and opportunities to party...sorry, I couldn't help myself ;-)).

There are a few moving parts to this technique:

CHOOSE Function steps

Is your head in a spin? Let me explain them:

  1. CHOOSE's index_num argument comes from a Table called agg that maps the aggregation type to the index number:

    CHOOSE Function aggregation table

  2. Slicers (available in Excel 2010 onwards) provide the interactivity that enables the user to toggle through the different aggregation methods:

    CHOOSE Function slicer

  3. The Slicer filters a mini PivotTable (mini being small and with the Grand Total line removed, as opposed to some special breed of PivotTable):

    CHOOSE Function pivottable

  4. Our data is formatted in an Excel Table called ProgramTbl2 (see below) and this means our formula will be using Structured References to reference the table.

    CHOOSE Function source data

  5. And our CHOOSE formula in cell I9 references cell L13 of the PivotTable to find which aggregation method was selected:
=CHOOSE(L13, AVERAGE(ProgramTbl2[Viewers]), MAX(ProgramTbl2[Viewers]), MIN(ProgramTbl2[Viewers]), SUM(ProgramTbl2[Viewers]),)

Bonus: in cell H9 there is a dynamic text label which also uses CHOOSE to display which aggregation method has been chosen:

=CHOOSE(L13,"Average","Maximum","Minimum","Total")&" Viewers:"

Excel 2007 Method

For those of you still using Excel 2007 you don’t have the luxury of Slicers but you can achieve the same results using Form Control Radio Buttons:

Excel 2007 CHOOSE Function trick

  1. To insert radio buttons you first need to enable the Developer tab in your Ribbon.
  2. After selecting the Radio Button from the Insert drop down on the Developer tab simply left click and drag the mouse to draw it on your workbook. Right-click it to edit the text.

    insert radio button

  3. Make sure you put them in a Group box (Form Control) so Excel knows they’re all part of a group and numbers them consecutively. Click and drag to draw the Group Box on your worksheet just like you do with the Radio Buttons.

    insert group box

Tip: the whole of each Radio Button must be inside the bounds of the Group Box.

  1. Set the Cell Link cell for each Radio Button. Right-click the Radio Button > Format Control > Control tab and choose a cell for the Cell Link (mine is in cell E7):

    format form control cell link

The Cell Link is a cell anywhere in your workbook which captures the number of the selected radio button. All 4 of your radio buttons should use the same cell as this populates the index_num argument in your CHOOSE formula.

Note: you’d normally put your Cell Link cell out of sight. I put beside the radio buttons so you can see all the moving parts together.

Tip: you can copy and paste the first Radio Button and it will remember the cell link so you only have to set the Cell Link once.

  1. Link your index_num argument to the Cell Link cell.
  2. =CHOOSE(E7,AVERAGE(ProgramTbl[Viewers]),MAX(ProgramTbl[Viewers]),MIN(ProgramTbl[Viewers]),SUM(ProgramTbl[Viewers]))

Toggle away!

Uses for this technique

  • Headline figures in your Dashboard reports
  • A quick way to summarise your data in different ways
  • Use it with named ranges to return different regions/product group summaries

More CHOOSE Examples

  • A fairly common way to use CHOOSE is to force VLOOKUP to look to the left. Although I prefer INDEX & MATCH for this.
  • Mike Alexander shows us a clever way to use CHOOSE to convert a date into a fiscal quarter.

Totals in Excel Tables

Excel Tables also enable you to choose the aggregation method by clicking on the down arrow in the Total cell:

Excel Table Total Row

It’s a nice touch, however there are two things I don’t like about this:

  1. The ‘Total’ label in the first column doesn’t change to tell you what aggregation method is in use. You can choose Standard Deviation and ‘Total’ still says ‘Total’.
  2. It’s at the bottom of the Table which can be a long way away.
CHOOSE Function

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:maximize Excel across multiple screensMaximize Excel Across Multiple Screens
Next Post:Interactive Excel ChartsInteractive Excel Charts

Reader Interactions

Comments

  1. Ru

    August 12, 2016 at 2:05 pm

    I know this is a Choose exercise but another solution is to do it simpler and yet more versatile.
    Enter the following.

    C26 Analysis D26 Viewer Stats
    C27 Average D27 =AVERAGE(ProgramTbl[Viewers])
    C28 Maximum D28 =MAX(ProgramTbl[Viewers])
    C29 Minimum D29 =MIN(ProgramTbl[Viewers])
    C30 Total D30 =SUM(ProgramTbl[Viewers])

    Now convert this to a table and any or all of the Statistics can be selected or visible.
    The table can be dragged elsewhere, say above the ProgramTbl table as Jonathan likes, or even to another sheet.
    Regards,
    Ru

    Reply
  2. Jonathan Lettington

    May 4, 2016 at 9:29 pm

    Regards your comments about “there are (two) things I don’t like about thi ……The total’ still says ‘Total’. It’s at the bottom of the Table which can be a long way away.”

    Whenever I insert a table I always activate the Totals row. It might in Row 1217 for all I know.
    I then insert a row above my table headers In Row1 so now I have a clean row of white space immediately above my Table Headers.
    I then click in a column heading cell in Row1, (example Cell G1), type an = and then click in the cell containing the Totals value at the bottom of the table column.(example Cell G1217) This now shows the Total value at the bottom AND the top of the table. Repeat for all other totals that you want to see at the top above your headings. I find because they are dynamic they are fantastically more convenient than having to continually trawl to the base of the table.

    Reply
    • Mynda Treacy

      May 4, 2016 at 10:02 pm

      Thanks for sharing your approach, Jonathan.

      Reply
  3. r

    May 4, 2016 at 1:50 am

    ciao Mynda!
    I quickly looked at your file … and I immediately thought of a different solution …
    chenge pivot table value in:
    Aggregation Number
    Average 1
    Maximum 4
    Minimum 5
    Total 9

    then in I9 =SUBTOTAL(L13,ProgramTbl2[Viewers])
    ummm … end in H9 i think we can write =K13&” Viewers:”

    I take this opportunity for a big CIAO to you
    🙂
    regards
    r

    Reply
    • Mynda Treacy

      May 4, 2016 at 8:47 am

      CIAO to you too, Roberto. Lovely to hear from you.

      Thanks for your idea. I also thought about using AGGREGATE but that wouldn’t demonstrate CHOOSE 😉 nor would it work in Excel 2007 with radio buttons.

      Mynda

      Reply
  4. Paul S.

    May 4, 2016 at 1:40 am

    First of all, I want to say thank you for all the wonderful tips you put out!

    I like the thought behind this tip. I noticed one thing that a person has to watch out for. Since by design, slicers allow a person to make one or multiple choices, a person may do that in another scenario with different data where it would be appropriate to make several choices at once, but using this slicer method only one of them will show.

    So, visually, if two choices are made via the slicer – it is fine if one looks strictly at the heading, but if one is thinking of what choices he made or visually looks at the slicer (and not the heading), he will be getting the wrong impression of what the data is representing.

    Reply
    • Mynda Treacy

      May 4, 2016 at 8:48 am

      Good point, Paul. Thankfully in Excel 2016 you can set a Slicer to only use single selection, but in earlier versions you’d have to write error handling into your formula to tell them they can’t pick multiple items.

      Reply
      • Bryon T. Smedley

        May 26, 2016 at 12:17 am

        I’m running Office 2016 and dabbled with the Multi-Select option in the slicers. This just seems to be a way to allow a user to select multiple items without knowing about CTRL or SHIFT as selection controls. The user can still select multiple items via the keyboard.

        Below is a macro that will detect the selection of multiple items in a slicer and reject all of them except for one. Paste this into the code window of the sheet that contains the slicer.

        I used the name of the table and slicer that Mynda used in her example file:

        Pivot Table Name: PivotTable1
        Slicer Name: Aggregation

        In the immortal words of the Sirius Cybernetics Corporation:

        “Share and Enjoy!”

        =============================================

        Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

        Const CONTROL_PIVOT As String = “PivotTable1”
        Const CONTROL_FIELD As String = “Aggregation”

        Dim pi As PivotItem
        Dim itemFound As Boolean

        On Error GoTo wp_exit

        Application.EnableEvents = False

        If Target.Name = CONTROL_PIVOT Then

        With Target.PivotFields(CONTROL_FIELD)

        For Each pi In .PivotItems

        If pi.Visible Then

        If itemFound Then

        pi.Visible = False

        Else

        itemFound = True

        End If

        End If

        Next pi

        End With

        End If

        wp_exit:

        Application.EnableEvents = True

        End Sub

        Reply
        • Mynda Treacy

          May 26, 2016 at 9:19 am

          Brilliant! Thanks for sharing, Bryon 🙂

          Mynda

          Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.