Data entry isn’t fun, but sometimes it has to be done. Excel has a neat hidden feature made just for data entry. The data entry form allows you to view one record at a time in a more natural horizontal orientation which can make the data entry a bit easier in some cases.
To use the data entry form you will need your data to be in certain format.
- One record of your data should be in one row in Excel.
- Your data should have column headings in each column above the data.
- Your data should have at least one record in it before you activate the data entry form.
I recently installed Office 365 on a new mac. This includes the newest version of Excel for mac which is v 15. I can't seem to locate the option to enter data by creating a data entry form. Previously you'd go to (menu) DATA and select FORM after selecting the appropriate cells. Once you complete the process above, Excel the inserts the UserForm.By default, the UserForm contains an empty dialog box. Notice that, in addition to displaying the UserForm window on the right side of the screen, the VBE adds the following items to the Project Explorer. If the VBA Project you're working on already contains a Forms node, the VBE doesn't add it again.
I was sad to discover that the data form function is missing from excel for mac 2016. According to Microsoft, it's a compatibility issue with Apple's OS. I mean to say, can we create a data entry user form without using macros or data entry form feature / controls and still make a small application which asks the user to input data in sheet (user form), stores in another sheet (database) and provides the summary analysis in the 3rd sheet (dashboard). If you press the left arrow to complete the entry, Excel simply moves the cell cursor to the next cell outside the data list table. Using the Form button. Instead of entering the records of a data list directly in the table, you can use Excel’s data form to make the entries. The only problem with using the data form is that the command to.
Step 1: Adding the data entry form to the Quick Access Toolbar.
Open the quick access toolbar options menu.
- Press the small options icon to the right of the quick access toolbar.
- Select More Commands from the drop down list.
Add the Form to your quick access toolbar.
- From the Choose commands drop down list select Commands Not in the Ribbon.
- Scroll down until you see Form… and select it.
- Press the Add button.
- You should see the Form… appear in the right most box.
- Press the OK button.
Step 2: Using the data entry form.
Now we can use the data entry form.
- Make sure your data is similarly formatted like the example data then place the active cell cursor anywhere inside your data.
- Each row is one record set (i.e. in our example row 2 contains all information about Luke, row 2 contains all information about Han etc…).
- Each column of data has a column heading (i.e. in our example Name, Planet and Email in row 1).
- We have at least one row of data.
- Press the Form button in the quick access toolbar.
- A data entry and viewing form will appear with your data items allowing you to view or add records.
- You can navigate your existing data by using the Find Prev or Find Next button.
- You can add a record by pressing the New button or you can also delete existing records from the table with the Delete button.
Scrolling through your data
Adding a new record
Deleting an existing record.
Wow, who knew data entry could be so much fun 😀
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?
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.
Data Entry Form For Excel 2016 Mac Download
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
How Do I Create A Data Entry Form In Excel 2016 For Mac
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:
Data Entry Form For Excel 2016 Mac Histogram
- 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.
Data Entry Form For Excel 2016 Mac How To Merge Cells
Have fun with your data entry in Excel!