Previous Column Values in a Drop-Down List

Written by Allen Wyatt (last updated August 21, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


3

James notes that when entering data in a column, Excel has an AutoFill feature based on what you previously entered in the column. He wonders if there is a means to get the previous column entries in the form of a drop-down list so the user can enter one of those previous values without the need to start typing anything in the cell.

There are a couple of ways you can approach this. If you simply want to choose from a drop-down list, you can do so by right-clicking the blank cell that is "next down" in the column. Excel displays a Context menu from which you can choose the Pick from Drop Down List option. (See Figure 1.)

Figure 1. Choosing from a drop-down list.

The options presented in the drop-down list are all of the unique text values in the cells above the cell on which you right-clicked. They are presented in alphabetic order, and you can click on one of the values to enter it in the cell.

If you prefer to not use the mouse, you can also select the "next down" cell and press Alt+Down Arrow. You'll see the same drop-down list just described, and you can use the arrow keys to move through the list and select the value desired. (Just press Enter to choose the selected value.)

If you want to be more restrictive in what people can enter into the additional cells, you can always create your own list of unique entries from the column and then use that list with data validation on the cells. Exactly how you can apply data validation to the cells has been covered in other ExcelTips.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (1609) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Including a Data Table with Your Chart

Microsoft Graph allows you to display both your chart and data, if desired. All it takes is to make a simple change to a ...

Discover More

Changing Fonts in Multiple Workbooks

If you need to change fonts used in a lot of different workbooks, the task can be daunting, if you need to do it ...

Discover More

Using the FORECAST Function

Excel provides a handy worksheet function that allows you to forecast values based upon a set of known values. This ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Changing References in a Lot of Defined Names

Need to change some cell references in your defined names? Changing one or two is easy; changing dozens is a good deal ...

Discover More

Three-Dimensional Transpositions

Excel makes it easy to transpose your data so that rows become columns and columns rows. It doesn't have a built-in ...

Discover More

Undoing Actions in Only the Active Workbook

When you undo actions within Excel, those steps you undo may affect the multiple workbooks in which you've been working. ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is five more than 6?

2021-08-23 13:48:46

Tomek

Alt +down only works in the cell just below the range with entries. and it only gets the entries in the unbroken range just above it. But so does the automatic fill feature.
To overcome this create a help range with unique values from your column possibly using UNIQUE function. Then use this help range as data validation list as suggested in the second part of the tip..


2021-08-23 03:17:14

Alex Blakenburg

@Ken Blair, it is on the standard context menu about 3 from the bottom.


2021-08-22 16:28:15

Ken Blair

Allen,

Using Excel 2016. The Alt+down option works but a right click gives me the standard context menu that Cut, Copy, Paste Options, etc. Is there a setting that I might be missing?

Ken


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.