 |
|
| TheExcelAddict.com | |
| December 17, 2019 | |
| Hi fellow Excel Addict, | |
| Thanks for joining me again this week for another
Excel in Minutes 'magic trick'. This will be my last newsletter for a few weeks as Tina and I will be flying 4,300 kilometers to Alberta to spend Christmas with my daughter Kristen and our adorable granddaughter Gracie. Online video chatting is awesome but it doesn't allow hugs.  I will be back with you again around the second week of January to begin sharing more awesome, time-saving Excel tips. It's hard to believe, I have been publishing my newsletter for almost 16 years and amazingly there are loyal readers who have been with me since the beginning. I want to thank every one of you for your encouragement and support throughout 2019 and over the years. If you celebrate Christmas, I wish you, your family and friends a very Merry Christmas. For everyone else, Happy Holidays and I wish you a safe, prosperous and awesome New Year. Now go check out my awesome tip below. Keep on Excelling, Francis Hayes (The Excel Addict) Email: fhayes[AT]TheExcelAddict.com  |
|
|
|
|
|
|
|
| Missed my last newsletter? | |
Click Here to View it Online |
|
|
|
|
| |
|
|
|
|
If you have a favourite quote, send it to me and I
may post it in my newsletter.
|
|
THIS WEEK'S 'EXCEL IN MINUTES' TIP |
|
| Create
In-Cell Dropdown Menus For Easier
And More Accurate Data Input |
|
I'll bet that some, if not many, of the spreadsheets you work with regularly need to be updated with values from specific lists of items. Maybe you have already discovered (or maybe you read it in my newsletter a while ago) that if you hold down the ALT key and press the down arrow key, you will be presented with a dropdown menu of all items previously entered from the column above. You then use the arrow keys to select an item and press ENTER to input the item in the current cell. Yes, that is a nice time saver but if you want to enter a value that has not been previously entered in the column, you will have to type it out. Many Excel users simply copy and paste values from above or just type each value. This can be really time consuming. Today I'm going to show you some hidden tricks you can use in Excel that can make this task easier for you and your workbook end users. Store Your Menu Lists in One-Column Excel Tables The key to making the lists of values for your dropdown menus easier to manage is to store these lists in Excel tables and assign a name to each table.
If you want, you can download this practice file to follow
along.
1) First, create a one-column list of valid values you want for your dropdown menu — preferably on a sheet separate from the dropdown cells; 2) Convert this list to an Excel Table by selecting a single cell in your list or the full list and then pressing CTRL+T followed by Enter. You will see that a table format is applied. Now that your list is an Excel Table, any items you add to the end of the list will automatically be included. You will not need to redefine your list range if you add or subtract tems from it;  3) Next, select all the values in the table (i.e. single column) excluding the headed; 4) From the Formulas tab, click Define Name and type a name (excluding blank spaces) in the Name field, then click OK;  5) Next, select the cell or range of cells on your input sheet where you want the dropdown list to appear; 6) Then, from the Data tab click Data Validation, Data Validation…; 7) In the Allow dropdown, select List;  8) Click in the Source field (you should see the cursor blinking), then click the Formulas tab on the Ribbon and click Use in Formula; 9) Select the list name you want to use as input values for the selected range and click OK.  10) Repeat steps 5 to 9 for each range you want to apply a dropdown menu;  11) Now you can use these dropdowns to select valid input values for those cells. No typing. No misspellings.  The Problem with putting extra blank cells at the end of your Data Validation Lists Do you have existing Data Validation Lists where you have added blank cells at the end to allow for future expansion? The problem with using extra blank cells at the end of your Data Validation Lists is that when you click on the dropdown, it sometimes opens at the end of the list rather than the top. This can be annoying if you then have to scroll back up a long list. The reason this happens is that when you click the dropdown, Excel tries to match what is in the current cell. Since the cell is blank, it looks to the bottom of the list to find a matching blank. Therefore, it is better NOT to use extra blank cells in your Data Validation Lists. Instead, convert your list to an Excel Table as shown above and you won't have this problem. |
|
|
|
|
To share this tip with your friends and
|
|
|
|
|
 |
|
|
|
|
 |
|
| Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose. | |
|
"Spreadsheets Tips From An Excel
Addict" is a weekly publication of
TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved. 8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630 |