To make data entry easier, you can create a drop down list in an Excel cell, using data validation.
This works well with a list of words, but the list doesn't show symbols if you create the list in a font such as Webdings. In the example below, I entered 5, 0, and 6 in cells B2:B4 and formatted the cells with Webdings.
The drop down list shows the number values, instead of the formatted symbols.
Use Built-In Symbols
You can’t create a drop down list of symbols based on formatted cells, but you can use some well-hidden
Continue reading Create a Drop Down List With Symbols
Yesterday, one of my clients emailed to let me know that she was having trouble entering January dates in a file that I had created. My first guess was that there was an issue with the regional settings, because her company uses the dd/mm/yyyy format.
But when I tried entering a January date, with my mm/dd/yyyy settings, I got an “Invalid date” message too.
Limit Dates with Data Validation
The date that I had entered – 1/3/13 – was a valid date and in a valid format, so I checked the data validation settings. And that’s where I found
Continue reading Limit Date Range with Excel Data Validation
One of my Excel sample files lets you double-click on a cell that has a data validation list, and a combo box pops up. The combo box has advantages, because you can set the font size, and the number of rows in the drop down list, and the entry autocompletes as you type.
However, one of the disadvantages is that the combo box lets you type anything, even if it’s not a valid entry from the data validation list. In the combo box shown below, I was able to type “Cold” as a month name.
Continue reading Validate Entries in Excel Combo Box
When you’re setting up data validation on a worksheet, you can include an Input Message, to help anyone who’s using the workbook.
You’ll have to get to the point quickly though – the message is limited to 255 characters.
There are other limitations too – you can’t control the size of the text box, and you can’t change its font size or fill colour, unless you change your Windows settings.
Show Input Message in a Text Box
As an alternative to the Input Message popup, you can show a message in a text box, at the top of the
Continue reading Show Data Validation Message in Text Box
You can make data entry easier by adding a drop down list on an Excel worksheet.
In most cases, you want people to select an item from the list, to prevent typos and invalid entries. If they try to type something that's not in the list, they'll see an error message, and will have to try again.
Allow Non-List Entries
In some workbooks though, you might want to allow other entries in those cells, in addition to the items in the drop down list.
To allow other entries, you can turn off the Error Alerts in the
Continue reading Allow Other Entries With Excel Drop Down List
With Excel's data validation, you can restrict the dates that can be entered on a worksheet. For example, you could specify start and end dates on the worksheet, and only dates within that range can be entered.
In the screen shot below, start and end dates are entered in column E, and dates in column B must be within that date range.
Set Up the Data Validation
After entering the start and end dates on the worksheet, follow these steps to set up the data validation:
Select the cells where the data validation will be applied –
Continue reading Restrict Date Entries with Data Validation
When you create a drop down list with data validation, you can't change the font or font size. If you have reduced the zoom setting for a worksheet, it can be difficult to read the items in the list. And even at 100%, it can tough to read the tiny print, at the end of a long workday.
Zoom In to Read the List
To make the text appear larger, you can use a bit of VBA code to increase the zoom setting when a data validation cell is selected. (Note: this can be a bit jumpy)
Continue reading Make Data Validation List Appear Larger
To make data entry easier, you can create a drop down list of items in a worksheet cell. Then, instead of typing a product name in an order list, you can select a valid product name from the list.
However, in some worksheets, when you click the arrow to open a drop down list, the selection goes to a blank at the bottom of the list, instead of the first item in the list. That can be annoying if you have to scroll back to the top of a long list, where you have the put the most popular
Continue reading Excel Drop Down Opens At End
If you're building a budget in Excel, you can limit the total amount that is entered, to help prevent anyone from going over budget. I shared this tip a few years ago, in this blog post: Limit the Total Amount Entered in Excel
In the new version, I've added a few more features, to help you fill in the correct amounts.
Below the Budget Limit, in cell D3, you can see the amount that hasn't been added to the budget yet. In column D, you can see the maximum amount that can be entered in each
Continue reading Control the Budget With Excel Data Validation
When you build an Excel tool or template, it's rare that you're ever really finished building. There's always something that would make the tool a little better, either for your own use, or for your customers.
And that's the case with the Excel worksheet data entry form, which I've just updated again. The original version was by Dave Peterson, and the form has evolved into a version in which you can add and update items in the database.
In the latest version, I fixed an issue with the navigation. Thanks to Travis, who let me know about the
Continue reading Check Excel Database Before Adding New Item