Excel is a lot more than a data entry tool.
But if we only talk about data entry, it’s a damn good one.
If your work involves entering data in Excel, you’ll find some amazing tips in this article that will help you save time and be super productive.
Excel Data Entry Tip #1 – Use Fill Handle to Quickly Complete Series
If you have to enter a series of data such as serial numbers, of day names, or month names, you need not do that manually.
Instead, use the Fill handle feature in Excel to do this quickly.
Here is how this works:
- Enter the first two items of the series. For example, if it’s serial numbers, enter 1 in a cell, and 2 in the cell below it. If it’s day names, enter Monday in one cell and Tuesday in another.
- Select both the cells and place your cursor at the bottom right of the selection. You will notice that your cursor changes to a plus icon.
- Press the left click from the mouse and drag it down.
You will notice that Excel would automatically fill the series for you.
Similarly you can also use it for day names (Mon, Tue, Wed…) or Month Names (Jan, Feb, Mar…).
Excel Data Entry Tip #2 – Use Drop Downs to Restrict Data Entry to Available Options
An Excel Drop Down List is a great way to make sure you enter only acceptable data items without errors.
For example, if you have a list of products and you want only those products to be entered in a range of cells, you can create a drop down list in it.
Now, you can easily select one of the values from the drop down.
This will make sure that there are no errors or misspelled words in your data entry.
Here are the steps to create a drop down list in Excel:
- In the worksheet, have the list of the items you want in the drop down.
- Select the cell where you want the drop down. If you need it in more than one cell, select all the cells where you want it.
- Go to the Data tab and select the Data Validation option.
- In the Data Validation dialog box, within the settings tab, select List as the validation criteria.
- Click on the source field, and use the mouse to select the cells that have the items that you want in the drop down.
- Click OK.
Now, when you click on the cell that has the drop down, you will see a small downward pointing arrow that indications the presence of a drop down list.
Just click on the arrow icon and the drop down list would appear.
Now you can choose the item from it and it will automatically be entered in the cell.
Excel Data Entry Tip #3 – Fill Down Data Using Control + D
If you have a data point in the cell above that you want to copy, simply use the keyboard shortcut Control + D (hold the control key and then press D).
This will copy the content, formatting, as well as formula (if there is any) from the cell above.
This trick also works for more than once cell.
For example, if your data is in A1:C1 and you want to copy it in A2:C2, just select A2:C2 and use the keyboard shortcut Control + S.
Excel Data Entry Tip #4 – Quickly Enter Current Date and Time
If you need to enter the current date and/or time in a cell, use the following keyboard shortcuts:
- To enter current date: Control + ; (hold the control key and press the semi-colon key)
- To enter current time: Control + Shift + ; (hold the control and shift keys and press the semi-colon key).
Note that the date and time values entered using these shortcuts are static values (i.e., these wouldn’t change with data and time).
Excel Data Entry Tip #5 – Get a List of all Unique Values in the Column
If you’re entering data that comprises repetitive items (say product names or region), you can use this awesome trick.
When you have entered data in a couple of cells in a column, you can get the list of unique values by using the keyboard shortcut ALT + Down Arrow (hold the ALT key and press the down arrow).
It will instantly show you all the unique values that you have already entered in the column.
Now you can simply select the one you want instead of typing it again and again.
These are my top five data entry hacks that save me tons of time every day.
What are yours? Do share with me in the comments section.