Excel 2019 is used in many organizations to fill out information on customers, orders and products. Some of the data items are repetitive, meaning that you don't type data into a cell but rather select from a data list. For instance, you might want to select a state from a dropdown list in a column that stores the state for a customer address. You can make it easier to fill out forms in Excel 2019 using data lists, and this article shows you how to create them.
Set Up the List
Before you create a dropdown list in Excel, you need a list of data to use. Since you can make multiple worksheets in one Excel workbook, most people use a separate sheet to populate the data list. You can then configure any specific cell in the Excel form to point to this list of data. You then set the cell as a list, and Excel does the rest for you.
You don't have to use a separate sheet to populate a data list. You can use the same sheet or even a separate workbook. You can use a separate workbook, but any changes to this workbook could affect the data list. Also, if the workbook is moved to a different directory or storage location, your list will no longer function.
For simplicity, this article's examples use the same workbook and worksheet, but just know that you can use references to separate worksheets and workbooks for your own data lists.
Create a blank worksheet in a currently opened workbook, or you can create a new workbook to store the new data list. In the following scenario, a list of states is used for a data list. We'll add five states to the first column [A] in a new worksheet.
[List of states set up for a dropdown]
In the image above, five states were added to their own cells. Notice at the top that "State" is used to label the column list. This is beneficial for the dropdown that we'll make, and it helps label data so that others can understand what the list is used for. The list can be as long as it needs to be, but we've only added five states for simplicity.
You now need a place to display the dropdown that will contain these values. For simplicity, we'll set up a dropdown in the same worksheet. Make sure that you label the dropdown as well so that users know what the dropdown list is for.
[Location of the state dropdown and its label]
In the image above, the cell E4 is selected as the cell that will contain the dropdown list. In D4, the label "States:" is displayed so that users know a list of states will be shown in the dropdown. It also helps users understand that they must select a state when they fill out the form. Labeling cells is an important part of creating effective spreadsheets that store the right data, especially among several users that add data and revise it periodically.
Creating a Dropdown Based on External Data
With the cell next to the "States:" label selected, click the "Data" tab. The "Data" tab has several data tools that you can use to work with lists. Click the "Data Validation" button, which is found in the "Data Tools" section of the "Data" tab.
[Location of the "Data Validation" button]
Clicking this button displays another dropdown selection. Click the "Data Validation" option, and a new window opens where you can configure your data list and cell that contains the options for a user to choose from.
[Data validation configuration window]
In the window above, you have several options to set a dropdown using a list of data. For a dropdown, you need to choose the "List" option. The "List" option is found in the "Allow" dropdown box.
[Select "List" for the validation criteria]
In the image above, notice that the "Allow" dropdown has the "List" option selected. This option tells Excel 2019 that you want to use a data list. If you hadn't already created a data list in an external workbook or in a worksheet in the current workbook, you would need to cancel out of this window and create a list. For this reason, it's better to create a list of data that you want to display in a dropdown before setting up this configuration option.
When you select "List" from the dropdown, a new list of options display in the "Data Validation" window. The "Source" text box is where you can type the range of cells. With a list of only five states, it's easy to take a look at the cells that contain states and type them into this text box. However, when you have a range of cells that span several rows, you'd have to scroll down the spreadsheet to find the last cell in the list. You could also have empty cells that you miss as you scroll down the list. Instead of typing the cell range manually, you can click the arrow up button adjacent to the "Source:" text box. This opens a view of your spreadsheet where you can use your mouse to select the cells that contain a list of data.
Click the arrow button next to the "Source" text box to trigger the data selection feature. The "Data Validation" window collapses and then only the Source text box displays. Use the mouse to highlight each state. You don't need to click the states individually. Instead, click the first state and drag your mouse to the last state in the list. The data validation tool will automatically create the range in the text box with the right textual labels that reference cells containing the information to display in the dropdown.
[Data list selected and displayed in Source text box]
Click the "Input Message" tab to see a list of options to prompt the user with information when the input cell is selected. In some scenarios, having a label such as "State" is not enough to explain what the user should input. You might need additional information to explain what is needed using a prompt. You could set a Note but these notes are used for revisions and not the best option for setting instructional information. With an Input Message, you can prompt the user and display additional information when the cell is selected.
[Input message configuration window]
The input configuration window contains a title and an input message. The "Title" text box is where you set up the main title that the user will see when clicking the cell. The "Input Message" is arguably the more important input text box. This text box describes the purpose of the input selected by the user. It gives additional information to help the user understand the right data selection when filling out the Excel 2019 form.
Fill out these two input fields if you think more information is needed for the user as the form is filled out. Click the "Error Alert" tab to set up an error message or warning for the user should one occur.
[Error alert option for a data dropdown]
In the configuration window above, the check box labeled "Show error alert after invalid data is entered" tells Excel to stop the user and display the configured error should the user enter invalid input. For instance, we have five states from which the user can select in a dropdown. If the user decides to enter a state that isn't in the list, then Excel would display an error and prompt the user to re-enter information to valid input. You can remove this selection to skip the option, but you should only skip this option if you do not want to restrict data entry to only what's available in the dropdown list.
In the "Style" dropdown, you can choose if you want to stop the user [the default], or if you want the message to show as an informational note or a warning. This gives you granular control of invalid data errors, so that you can choose if you want to allow the user to continue filling out the form even if data is incorrectly entered.
The "Title" and "Error message" input boxes are the text displayed to the user. Just like the 'Input Message" option, the "Error message" text box is the most important because it gives the user detailed information on why the input is invalid.
After you make your choices in all three tabs, click the "OK" button, and Excel 2019 sets up the dropdown list in the selected cell.
[Dropdown using a data list]
You can test the new dropdown data list by selecting data. You can also test the error messages by setting the cell to a state that does not match one in the set list.
For instance, first click the arrow in the dropdown and select "Florida." Notice that no error message is displayed. Next, click the dropdown cell, which is E4 in this example. Type the word "Text" in the input cell. Click away from the input cell by clicking cell F1. Notice that an error message displays indicating that incorrect input was detected.
[Error message displayed from invalid input]
This error and validation control let you stop invalid input so that you can keep a spreadsheet of data that follows a strict rule set. By blocking invalid input, you can ensure that only a specific set of data is added to your input forms.
Dropdown lists are a common part of forms, and Excel makes it easy to create them whether the list contains 2 or 2000 cells. Dropdown lists are valuable when you have a set list of items from which you want users to choose, so they cannot just type any value into a cell. Using data lists makes creating long forms with multiple dropdown lists faster and remember that you aren't limited to just the current sheet. You can reference any worksheet or workbook that contains your data by just adding the appropriate reference syntax in your data validation options.
Entering and managing lots of data can be a daunting task.It's easy to get overwhelmed in all of those rows and columns of information.The solution is to use a form.A form is simply a dialog box that lets you display or enter information one record [or row] at a time.It can also make the information more visually appealing and easier to understand.
Most people who are familiar with the MS Office suite associate complex forms with Excel's sister program Access, but you can use them in Excel as well.In fact, you can even share data between the two programs.
There are two kinds of forms available in MS Excel: data forms and worksheet forms.
Data forms are generally used for data entry.They are simple forms that list the contents of a single column.What's more, they can display up to 32 fields at a time.This is especially helpful when dealing with a data range that reaches across more columns than will fit on a screen.You can insert, alter, delete, and even find records with data forms.
Worksheet forms are more sophisticated and specialized.They can be customized to fit the information at hand or to fill a particular need.They can even be complex and appealing enough to be printed or distributed online.Worksheet forms must be created using the Microsoft Visual Basic Editor.
Adding the Form Button to the Quick Access Toolbar
The Form button is not included in the ribbon, but you can add it to the Quick Access Toolbar, which, if you remember, is in the upper left hand corner of the application window.
To add the Form button, click the arrow to the right of the Quick Launch toolbar and select More Commands. This will launch the Excel Options window, which can also be accessed by clicking Options on the File tab.
In the "Choose commands from" box, select Commands Not In the Ribbon, then scroll down through the list until you find Forms. Select it and click Add. When you are finished, click OK.
This is what the Form button looks like:
Adding Records Using the Data Form
When you click the Form button for the first time, Excel will analyze the row of field names and entries for the first record.It uses that information to create a data form.
Let's click on the Form button now.
The first record appears in the dialogue box:
Click Find Next to find the next record.
If you want to add a new record using the data form, click New.
As you can see in the snapshots above, the number of the record appears above the New button.
However, when you click the New button, it lets you know that it's a new record.
Let's add a new record.
Enter the information, then hit Enter.
The new record appears in your list:
You can hit ESC when you're done adding records.
Editing Records in the Data Form
In addition to adding records, you can also edit them. You can use the data form to find records that need updated or removed from the list.
To do this, click the Form button on the Quick Access Toolbar.
Find the record that you want to update or remove.
Now click in any of the fields to update the data.You can also delete data in the field.
Navigating Through Records in the Data Form
If you have a lot of records, navigating through them can be a challenge.The data form makes it a bit easier.
If you want to move to the next record in the data form, press Enter or the down arrow key.
To move to the previous record, hit the up arrow or press Shift+Enter.
To move to the first record in the data list, press CTRL+ ? [up arrow] or press CTRL+PgUp.
To go to a new data form that comes after the last record, press CTRL+ ? [down arrow] or CTRL+PgDn.
Find Records in the Data Form
If you want to find a specific record, you can use the Criteria button in the data form.It's pictured below.
When you click the Criteria button, Excel 2019 clears the field entries in the data form.Instead, the word Criteria is displayed.
Let's push the Criteria button to show you what we mean.
We now see a blank data form.
Notice that the word Criteria is above the new button, and the new button is inactive since we're searching for a record.
Now, let's say you're looking for a woman named Mary Webb, but you have 5000 women named Mary in your list.
Enter the criteria:Mary Webb.
Now click the Find Next button.
As you can see, it brings up the record.
You can also include the following operators in search criteria to help you find the record you need:
? for single
* for multiple wildcard characters
= Equal to
> Greater than
< Less than
>= Greater than or equal to
New Scene in the 3D Map screen.
From the dropdown menu, select New Custom Map.
You will then see this dialogue box:
Locate the background picture you want to use.
Click Open.
You can now see your picture open in the 3D Map window.Use the dialogue box to adjust the XY coordinates if needed.
Click Apply, then Done.
Video liên quan