Creating a dropdown list in your Excel spreadsheet can help to increase the efficiency of your work. This might come in handy, especially when you want your coworkers to provide certain information that may be relevant to the company. By using an Excel dropdown list, you can control exactly what can be entered into a cell by giving the users an option to select from a pre-defined list.
When you add a dropdown list to a cell, an arrow will be displayed next to it. Clicking on this arrow will open up the list and give the user the option to choose one of the items on the list. This will not only save you space on your spreadsheet but also make you look like like a superuser and impress your co-workers and boss. Here’s a step-by-step guide on how to create a dropdown list in Excel.
Step 1: Define the Contents of Your List
1. Open up a new Excel worksheet and put down the contents you want to appear on your list. Make sure each entry occupies one cell, and all entries are vertically aligned in the same column. Also, ensure there are no blank cells between the entries. In our case our dropdown menu will open a list of cities to choose from.
2. Once you’re done assembling the list, highlight all the entries, right-click on them, and select “Define Name” from the menu that will show up.
3. This will open a new window with the title “New Name.” Choose a name for your list and enter the same in the “Name” text box.
4. Click “OK.”
Step 2: Adding Your Dropdown List to a Spreadsheet
The next step is to add your dropdown list to a spreadsheet. Here’s how to do it.
1. Open a new or existing worksheet where you want to place your dropdown list.
2. Highlight the cell where you want to place the dropdown list. Click the “Data” tab, then locate the “Data validation” icon in the data tools section and click on it.
3. A data validation box will appear that has three tabs: Settings, Input Message, and Error Alert. In the Settings tab, select “List” from the “Arrow” dropdown list. A new option titled “Source” will now appear at the bottom of the window. Click the text box and then enter a “=” sign, followed by the name of your dropdown list. In our case it should read =cities.
The “Ignore blank” and “In-cell dropdown” boxes are checked by default. With the Ignore blank cell checked, it means it’s okay for people to leave the cell empty. But if you want every user to select an option from the cell, uncheck the box.
4. Click OK. That’s it. You have added a dropdown list to your spreadsheet.
With that completed, you can now proceed to the Input message tab.
Step 3: Set Input Message for Data Validation (Optional)
At times you may want a message (with description) to pop up when the cell containing the dropdown list is clicked. In that case you’ll need to click the “show input message” box in the Input message tab. You’ll also need to fill out the title and the input message in their respective boxes. Your dropdown list should now look something like the following image.
The last tab is for the error alert. Once defined, this one sends an error message if someone enters invalid data – data that is not on the list.
Wrapping Up
Dropdown lists are very common on websites and are very intuitive for the user. Their versatile nature renders them useful in virtually all industries. Be it in surveys, the business world or even in schools, you’ll always find the need to use Excel dropdown lists.
Was this article useful? Feel free to comment and share.
Microsoft Excel is the best spreadsheet program around. All those features can also be intimidating. These free tutorials and courses are for those who find the software overwhelming but want to still learn it.
The new Office Basics training videos are the official tutorials for newcomers, covering everything you would need to know. I especially like how Microsoft has broken down each segment, like Quick Start, Intro to Excel, Add and Format Charts etc. All videos are free to stream as well as download, in case you want offline backups or to train teams.
Go through one video at a time, practice it, and only then move on to the next. And take your time with it, don’t rush through.
The Goodwill Community Foundation’s LearnFree online academy is an outstanding accompaniment to Microsoft’s official course. It is broken down into similar sections and videos, all of which are free on YouTube.
Again, it’s the careful break-up of learning Excel that is key here. GCF LearnFree turns it into a step-by-step process, with a total of 29 sections. Each section has a video, a long article, as well as recommended exercises.
Microsoft has a free version of Excel that anyone can use through a browser. Excel Online (or Office Online, as the official name goes) only requires a free Microsoft account and an active internet connection. While it’s not as robust as Excel 2016, it’s still pretty good.
Anyone who tells you that you need several days of dedicated time to learn Excel is flat out lying, according to the creators of Spreadsheeto. Instead, all you need are 10 minutes daily to watch a video, and another five minutes to practice what you learned.
The free version of Spreadsheeto is a great introduction to the basics and how you will learn in the full-version paid course that costs $200. The daily emails will take you from basics to intermediate to advanced uses for Excel.
But it never gets overwhelming because of the staggered approach. And you can’t skip ahead either, since tomorrow’s lesson has not yet been delivered to you. Spreadsheeto forces you to learn at a slower pace, absorbing more. With time, you’ll even master VLOOKUP, the most important function in Excel.
It makes no sense that Excel Easy is available for free on the internet. When you see the number of paid courses to learn Excel, it’s flabbergasting that such a great tutorial is available for free.
You start with the introduction, move on to the basics, learn how functions work, start analyzing data with Excel, and finally learn Excel VBA at the advanced stage. Each of those has the simplest explanation possible in the form of a single page, with screenshots and easy language.
Once you find out how many features exist in Microsoft Excel, it can feel overwhelming and daunting. I’ve felt that in the past, and especially when I saw others doing things that were way beyond the simple sums I was able to execute. The gap between what I could do and what they could do was too large, and that in itself made it seem too much effort to ever learn.
Enjoyed this article? Stay informed by joining our newsletter!
If you’re a fan of desktop email, chances are that you use Microsoft Outlook to manage your inbox. Outlook stores your email data in a file with the PST extension. This contains all of your mail, calendar, and contact info.
You can hunt down this PST file by hand if you need it — perhaps you’re migrating to a new Windows account because of unfixable issues. However, if you want to regularly back up this file, or need to move it but don’t want to do so manually, there’s a tool that makes it fast and easy.
Safe PST Backup is a free utility that backs up your Outlook data to any folder you choose. The site asks you to enter your email address before downloading, but you can follow this direct download link to skip this. Once downloaded, install it as normal and double-click the new icon running in your system tray to open Safe PST Backup.
Without many options, the tool is simple to use. Click inside the Backup destination folder box to choose a place for your backups. You should pick a different hard drive if you’re using this for backup purposes. Then, the Start Backup button will begin the process. Once complete, the program will continue to back up your Outlook data once every four hours.
Click Options to change a few ways that Safe PST Backup runs. You can change the automatic backup schedule, or turn it off entirely. Most of the other settings won’t be useful for you, as they’re intended for enterprise use.
Sharing Excel workbooks makes it easy to collaborate on data collection. But before you put your Excel files in the hands of other people, here are a few tips to get your worksheets ready for sharing.
Allow Multiple Simultaneous Edits
If you want multiple users to be able to edit a shared Excel sheet at the same time, do the following:
Go to the Review tab and under Changes, click Share Workbook.
In the dialog that opens up, make sure that Allow changes by more than one user at the same time is checked.
Save your Excel file in a shared location where other users can access it.
One thing to note about allowing this: you could get conflicting changes if two users make edits to the same cells. The owner of the file will be alerted and can choose which changes to keep or discard.
Protect Worksheets or Cells
If there’s specific data you don’t want anyone to modify or delete, you can protect an entire worksheet, protect a workbook, or protect specific cells.
If you want to protect an entire worksheet or workbook:
Go to the Review tab and click Protect Worksheet. (If you would prefer to protect the entire Workbook, click Protect Workbook.)
Enter a password. This will allow anyone who has the password to unprotect it, but people without the password will still be able to view it.
Under ‘Allow all users of this worksheet to’ you can select what other users can do on the worksheet including: format cells, add/delete rows or columns, and add hyperlinks.
If you want to protect a selection of cells:
Select the cells you would like others to edit, right-click then, and click on Format Cells in the menu that pops up.
Navigate to the Protection tab and make sure that Locked is unchecked.
Go through the same three steps above.
Now all cells, aside from those that set you as unlocked, should be protected.
Add Drop-Down Menus
If you want other users only to add from a selection of data to specific cells, you can create a drop-down menu for a specific range of cells. You can give users the choice of selecting only from that list or allowing them to enter other information.
To add a drop-down menu to a range of cells, do the following:
In a separate sheet in your workbook, create a list of the items you wish to include in your drop-down menu. These items should be in one row or column.
Select the entire list, right-click, and select Define name.
A dialog box will pop up where you can enter a name for your list. It can be anything that works for you — just don’t include any spaces.
In the sheet where you will be entering data, select the cell or cells where you want the drop-down to appear. Navigate to the Data tab and click Data Validation.
A dialog box will open up to a Settings tab. In the Allow field, select List.
In the Source field, type =ListName.
Make sure that In-cell dropdown box is checked. If you don’t want users to be able to leave the cell blank, make sure that Ignore blank box is unchecked.
The following steps are optional:
If you want a message to appear when a cell is clicked, navigate to the Input Message tab. Here you can enter a message up to 225 characters that will pop up.
If you want an Error Alert to appear, navigate to the Error Alert tab and make sure that Show error alert after invalid data is entered is checked. You can enter a specific message for your error alert.
What tips do you have for Excel users who want to share their workbooks? Let us know in the comments.
Enjoyed this article? Stay informed by joining our newsletter!
We’re going to achieve macro ninja status using Onetastic, an add-in for OneNote with several functions. You’ll have to choose which version to download based on whether you have the 32-bit or 64-bit version of OneNote installed. This is separate from a 32- or 64-bit version of Windows, so you should confirm which you’re using.
Open OneNote and visit File > Account, then click the About OneNote button on the right side. At the top of this window, you’ll see purple text that says either 32-bit or 64-bit. Head to the Onetastic download page and choose the right version for your installation. Unzip the file, open the executable, and Onetastic installs in an instant.
Once you’ve got it installed, pop open OneNote and you’ll see new options for Onetastic on the right side of the Home Ribbon tab. Since we’re interested in the macros on the right side of the list, you can click Settings then Show in a separate tab to send these buttons to a new Macros tab on the Ribbon.
Onetastic is free to start using, and includes its basic features like OneCalendar, custom styles, and pinning pages and sections to the desktop. Macros are only available for a free trial period, but it’s based on usage, not time. Thus, if you only use a few macros from time to time, you can likely use the service for free. Once you approach the end of the trial period, you’ll see a warning that you need to upgrade to the Pro license for $15.
With a Pro subscription, you gain access to all the macros that were available at the time of your purchase as well as any that debut in the next 365 days. So, if you buy Pro today, you can download and use any macros that are released in the next year forever. Any macros released after one year will require another Pro license to use.
You don’t have to write a line of code to start boosting your OneNote productivity with macros. Here are the best ones available from Macroland at the time of writing. To install one, just download and open it to add it to your Macros tab.
1. Sort Pages
A simple macro, but super useful. Sometimes you have a ton of pages that are out of order and need to quickly sort them. This macro sorts all pages in your current section by ascending or descending alphabetical order. It also keeps sub-pages under their parents and sorts them as well.
If you’re using OneNote to write a wiki or guide, a table of contents (TOC) is probably something you need. You can easily generate one using this macro, which adds a new page with the contents of your current section. The Fast mode is quick but won’t work if you rename pages, while the Resilient mode will properly link to renamed pages at the cost of speed.
Those who like to map out their months will love this one. The Insert Monthly Calendar macro does just what you’d expect: pick a year, month, and first day of the week, and you’ll have an instant calendar on your page.
Though you can add a line in other Office programs by just typing several hyphens, there’s no easy way to do so in OneNote. This macro fixes that by giving you a quick shortcut to a long horizontal line. It’s not too exciting, but it beats holding down the underscore key for several seconds!
It’s hard to believe that OneNote doesn’t have a Find and Replace functionality like so many other apps. Microsoft’s help page on this topic suggests that you use the Ctrl + F shortcut and paste over every single occurrence of a word to replace it. This would be a complete waste of time if the word appeared more than once, so you’ll definitely want this macro instead.
Just supply a word you want to replace and the text you want to replace it with. Then choose the scope of the search and whether it should match case, and you’re all done. Much better than manually replacing it.
Depending on what you’re using OneNote for, sorting by date might be more useful than sorting by name. Maybe you want to find pages that are old and in need of an update, for example. There’s a macro to help with just that. You can sort in a section or an entire notebook, and sort by created or last modified date. 12 Ideas to Run Your Life Like a Boss With OneNote12 Ideas to Run Your Life Like a Boss With OneNote OneNote can be your one-stop-shop for everything from organizing information, via managing projects, to keeping a daily journal. Here we propose a dozen ways in which you can use OneNote. Read More
Collaborating with OneNote means that you probably have multiple people who edit the same notebook. When this is the case, it’s useful to see which pages have recently changed. Whether you want to make sure that someone is making changes where they’re supposed to or just want to remember what you were editing recently, give this macro a try. How You Can Use Microsoft OneNote for Project ManagementHow You Can Use Microsoft OneNote for Project Management OneNote is the ideal collaboration tool for managing simple projects in small teams. And it’s completely free. See how OneNote can help your projects take off! Read More
Microsoft Word has a built-in word counter, but OneNote does not. You could always copy and paste your page into a free word counting tool, but this macro makes it faster. Seeing how much content you have in OneNote might mean you take notes like a famous scientist!
Caps Lock is a huge pain. You almost never need to type in all caps, and only ever trigger it by mistake. If you haven’t remapped that key to something more useful, you might find yourself typing entire sentences in all caps before you realize it’s on.
Instead of pasting the text into an online converter, this macro lets you quickly convert selected text to all lower case. This is a quick way to fix an annoying mistake.
After you use OneNote for a while, you might get bogged down in all the notebooks, section groups, and pages. You certainly don’t want to get lost in your own notebooks, so using this macro lets you quickly find your bearings. Launch it, and you’ll get a nice breadcrumb trail of your current location.
OneNote is already capable of so much, but these macros make it even better. If these aren’t enough for you, take a look at the hundreds of other options on Macroland, make your own, or even edit someone else’s macro to fit your own needs. Chances are if you want to do it, it’s possible with a macro. Start automating these slow tasks via macros and you’ll save yourself time! 12 Tips to Take Better Notes with Microsoft OneNote12 Tips to Take Better Notes with Microsoft OneNote These 12 tips show you how to take better notes in MIcrosoft OneNote. Today, digital note-taking is as much about skill, as it is about the tool. Read More
