Excel Tip – Pick From a List (on the fly!)

It’s the beginning of June, another Bank Holiday is here, but the weather is… well… miserable might be a good word to describe it! Don’t despair though, because it’s also time for a new ‘Keep it Simple‘ tip. Yaaaay! I’ve got a fabulous Excel tip for you that will help chase away the bad weather blues (there’s a song in that somewhere…)

You can view the video or follow the (brief) step-by-step shown underneath.

Enjoy and remember: Keep it Simple!

Today I’ve got a really lovely Excel tip tip that will allow us to pick from a list – on the fly! And what I mean by that, is that we DON’T need to create the drop-down list beforehand!

Cynical head: ‘Ok, what’s the catch’?

The only catch is that it is a text based feature that doesn’t work with figures.

Cynical head: ‘I think I can live with that, but show me more and I’ll let you know what I think’.

Ok, let’s take a look.

  • In column A we can see that the same Courses are being repeated, over and over, but there is no drop-down list to choose from.

Image of repeated names of courses

  • We also have repeat information in columns B and D, but if I click on any cell in those columns, again, we can see that there’s no drop-down.

Image showing 2 more columns with repeat course levels and locations

Now watch this:

Step 1 – Watch the Magic Happen

  • I’ll click on to cell A13 and press ALT + DOWN-ARROW on the keyboard – and Voilà!

Drop-down menu appears in cell A13

  • It looks up and finds each unique value in the column and presents it to me, as a drop-down!

Cynical head: Ok, I’m impressed but stop with the French – not cool.

  • I’ll DOWN-ARROW to select ‘Excel’ – then press Enter!

Step 2 – Do It Again!

  • I’ll TAB across – then Alt + DOWN-ARROW again to select – and Enter!

Drop-down menu shown in the B column

Step 3 – Watch It Not Working

  • Remember, it doesn’t work with figures, so when I TAB – then Alt + DOWN-ARROW

Drop-down under a a column of figures shows blank

  • We can see there’s nothing there.

Step 4 – One More Time, Pleaaaase!

  • I’ll move to the last one – Alt + DOWN-ARROW

Selecting from a drop-down menu in the D column

  • I’ll UP-ARROW to select this time – and Enter!


Thank you and I’ll be back with another useful tip – next month!


  • Handy tip, tks

    VJ Reply
  • Simple but effective

    Kev Reply

Leave a Reply

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