Do you spend a lot of time in your job using Microsoft Excel for repetitive data entry?
Are you frustrated at how long it takes to manually enter data from physical documents or other data sources?
Excel has a built-in Data Form which allows the users to Enter or Delete data. This Form can also be used to Find data satisfying specific criteria. Anyway, most of the Excel users are not aware of this great feature as this option is not included in the Excel Ribbon or Quick Access Toolbar by default. Excel for Microsoft 365 for Mac, Excel 2019 for Mac, Excel 2016 for Mac Excel for Mac 2011 You can create a form in Excel by adding content controls, such as buttons, check boxes, list boxes, and combo boxes to a workbook. Important: If you see a message that states 'Too many fields in the data form,' you need to reduce the number of columns, because a data form can contain only up to 32 columns.One solution (when working with a range of columns) is to insert a blank column, effectively breaking one range into two ranges. Then, if necessary, create a separate data form for the columns to the right of the blank. Apr 16, 2020 Use an Excel Data Entry Form. If you want to perform your data entry using a simple Excel form, check out this easy guide for creating a Form in Microsoft Excel. Using a Form allows you to enter or edit data via a simple dialog box instead of directly into the Excel workbook.
Since Microsoft Excel is a spreadsheet application, it’s a good file format for entering data. Because it’s such a well known application, lots of programs have the ability to import data from Excel.
But the sheer time it can take to enter data in the first place can be extremely time consuming – especially if you don’t use a lot of shortcuts that can save you time and speed up the process.
There’s no silver bullet here to solve all of your data entry problems, but we promise you that these tactics & hints can definitely cut out a lot of wasted time.
What You’ll Learn
In this guide you’ll find out how to best set up your Microsoft Excel workbook to make data entry simple, as well as a range of shortcuts & hints to save time as you work through your Excel data entry.
We’re going to dive into:
- How to prepare your Excel file so that your data entry is set up correctly for the long term
- The basics of fast data entry
- Some advanced tricks that are useful in some unique situations
Are you ready? OK, let’s go!
Post Contents
Before you Start: Preparing your Excel Workbook for Data Entry
If you’re lucky enough to start off with a blank Excel file to start your data entry or data processing, then it’s worth spending the time to set up your file correctly.
Thinking about the format & requirements for 5-10 minutes will save you hours in the future!
Know the Data you Want to Enter
If you’re going to be manually entering data from PDF files, physical documents or any other source of data, you need to know what values or pieces of information you want to enter.
The documents or information that you’re obtaining the data from could have lots of information, and a lot of it might not be required.
Two main types of data for Excel data entry
Usually, the type of data you want to enter is either:
a) Some kind of reference information (ie: a list of retail stores that your company manages), or
b) Transactional data (eg: a list of sales that were made during the last 3 months at your store, or a list of invoices that your finance team has issued so far this year).
If you’re looking at reference data, then you’ll be preparing some kind of master list. This information will usually include:
- A code or ID (eg: a store number, an invoice number or an employee ID)
- The full name or description (ie: the full store name or the employee name
- Any kind of categories or groupings (such as the state, province or country, or the department of the employee)
Transactional data is a lot more detailed, in comparison. Transactional data will often have:
- The date of the transaction or event
- The time of the transaction or event
- A code or a description that identifies where or who caused the transaction (eg: the store or salesperson that made the sale)
- The quantity or number of units (if it’s relevant)
- The total price/cost
Spend a little bit of time making sure you have everything you need, before you start your data entry.
Now that you know what you want to enter, let’s think about how you plan on entering it into Excel.
Separate Your Data into Multiple Columns
Do you have some columns that include multiple values grouped together?
For example, your data might include someone’s full name, which is useful information:
But what if you need to sort this data by last name? The employee’s names are all in one cell, so Excel can’t confirm what is a first name and what is a last name.
If we sort by last name, we want to see John, then Suzette, then Abraham. But if we sort this data in Excel we’ll have to sort on everyone’s first name which isn’t helpful at all.
But if we entered the data into two separate cells like this…
Now we can sort on first name OR last name!
The lesson here is – if you can split up your data into multiple cells, you’ll save yourself lots of time later on depending on how you want to use that information.
It’s much easier to combine data fields together than it is to split them up.
Another example of this might be an invoice number or a sales order number that contains a few values together:
Now that you know what data you need, and you know how you’re going to enter it into each column in Excel, the last thing to do is to talk about formatting.
How Microsoft Excel Auto-formats your Data
The last thing to do is to format the cells in your Excel sheet before you start data entry.
Why?
Because Excel will automatically format your data based on what it thinks is the type type of data.
But what do you mean by types of data?
Well, I’m talking about things like currencies, percentages, decimal numbers, dates and text.
Let’s talk about the auto-formatting a bit more.
For example, if you type in “3/4” into a cell, it can be presented in four totally different ways depending on how the cell is formatted!
In the example above, the value “3/4” can be represented as a date (ie: 3rd of April or 4th of March, depending on where you live), or as a number, or even as a percentage!
By default, Excel will treat “3/4″ as a date” so if you’re planning on entering fractions you should format that column as a Number.
How to Stop Excel Auto-formatting Your Data
Don’t worry – there’s a way to override this and stop Excel making these strange decisions.
How you format your cells before you start entering information is crucial if you want your data to be presented in a specific way.
To format your cells to be a specific type, simply select the cells you want to format then choose the number format from the Number Format dropdown box in the Excel Ribbon:
Do you want more shortcuts on how to format in Excel? Try this list of Excel formatting shortcuts.
You don’t have to do this for all of your columns – you only have to do it where you really care about the formatting. The most common reasons why Excel users format cells are due to:
- Wanting to keep 0’s at the start of numbers
- Ensuring that fractions aren’t formatted as dates
- Entering the time of day (ie: “4:45 PM”)
Let us know in the comments if you have a specific need for cell formatting for data entry.
Data Entry: The Basics
If your Excel file is all ready for your data entry, let’s go through the basics of data entry best practices that will save you time & frustration.
Using Tab & Enter Effectively
When you’re entering information into a cell, there are a few ways you can finish what you’re typing and move on to the next cell:
- Hit Enter to move to the cell underneath
- Press an arrow key which will move to the adjacent cell in that direction
- Use Tab which will move you to the cell to the right
- Clicking on the next cell that you want to enter data into (hint: this is a really slow way of doing this!)
Pressing the Right Arrow key or pressing the Tab key do the same thing here, but what some Excel users don’t know is that Tab will retain your original column until you press Enter.
Let’s go through how this works visually. In the first case, let’s see what happens when you use the Right Arrow:
And here’s what happens if you use the Tab key instead:
So if you need to start each row from the left, consider using the Tab key.
Copying Data Down, or to the Right
Does your data entry require copying data down from the rows above, or copying information from left to right?
Then the most important shortcuts you’ll need are:
These two just on their own could save you a lot of time.
But as with any Excel shortcut, it’s easy to take note of these but they aren’t that easy to memorise unless you have a smart way of remembering what they represent.
If you want an easy way to remember these shortcuts, just remember that to copy a cell “d”own, remember the letter “d”. And if you want to copy a cell to the right, remember the letter “r”.
Does this method of memorisation work for you? Let us know if you have another way to remember these shortcuts.
Select from the list of previously entered values
If you have a column which only has a handful of values that can be populated, there is a simple way to see the list of values so you can easily select it and not have to type out the full details.
To do this, press Alt + Down and Excel will give you the list of values (in alphabetical order) that already appear in that column.
Get more simple, time-saving tips in the FREE eBook “10 Simple Microsoft Excel Tips to Save You Time”. Get the free eBook now!
Let’s look at an example.
Here is a list of regions that are associated with order numbers:
Every time you enter in a new order, you’ll have to type out the Region in full.
Let’s see what happens if you press Alt + Down in any blank cell in column B:
Cool! You can use the arrow keys to choose your selection & press Enter to instantly populate the cell.
Keep in mind this won’t work on all columns. If you try it on column A, the list won’t appear because there are too many unique values.
Use an Excel Data Entry Form
If you want to perform your data entry using a simple Excel form, check out this easy guide for creating a Form in Microsoft Excel.
Using a Form allows you to enter or edit data via a simple dialog box instead of directly into the Excel workbook.
Advanced Data Entry Shortcuts in Excel
Now that you know the basics, have a look at these more advanced shortcuts.
Adding a Timestamp to your Data
If you are completing some data entry that needs to be logged and have a timestamp added, then this section is definitely for you.
If you need to add a timestamp to a cell, use the shortcut Ctrl + ; and it will instantly include the current date & time.
If you only want the time, use the shortcut Ctrl + Shift + ; instead.
Stay on the same cell after editing
If you edit a cell and you press Enter, the cursor will move on to the next row.
But if you want the cursor to stay where it is, use the shortcut Ctrl + Enter.
Microsoft Excel will jump out of the editing mode and you can go back to what you were doing.
Enter data into multiple cells at once
If you want to fill multiple cells in Excel with the same value, here’s what you can do:
- Select multiple cells (either click & drag with the mouse, or use Ctrl + click to select individual cells)
- Start typing in the value you want
- Press Ctrl + Enter
Conclusion
Hopefully you feel more confident in completing your data entry in record time.
Which of these shortcuts and tips helped you the most? Or did you have another special tip that wasn’t listed? Feel free to tell us in the comments.
Have fun with your data entry in Excel!
Unfortunately, the command to access the data form is not on the Ribbon, but you can do one of thefollowing:
- Add it in your own menu:
- add it in the Quick Accesstoolbar:
To use the data form, follow these steps:
1. Select data with headers of the columns in the first row of your datarange:
2. Click the Form button on your own menu or in QuickAccess toolbar. Excel displays a dialog box customized to your data:
Notes:
Excel Data Entry Form 2016
- To move between the text boxes, press Tab.
- You can't modify formulas using the data entry form - the formula result appears as text, not as anedit box:
- To add a new row to your table, click the New button.
- You can also use the form to edit the existing data.
Data Entry Form For Excel 2016 Mac How To Sort And Filter
See also this tip in French:Comment utiliser un formulaire pour la saisie de données.