10 Excel Data Entry Tips You Can’t Afford to Miss

Excel is an amazing tool with fascinating capabilities to analyze data (be it using functions, charts or dashboards). The ease with which data can be entered and stored in Excel makes it a compelling choice.

In this blog post, I have listed 10 useful Excel Data Entry tips that will make you more efficient and save a lot of time.

#1 Use Excel Data Entry Form

Excel Data Entry form enables you to add records to an existing data set. It gives a pop-up form that can be filled by the user. It is especially convenient when the data set has many columns and would require you to scroll right and left again and again as you key in data points.

Data Form feature is not already available in Excel ribbon. We first need to make it available by adding it to the Quick Access toolbar.

Adding Excel Data Entry Form to Quick Access Toolbar

1. Go to Quick Access Toolbar, right-click and select Customize quick Access Toolbar.
   
2. In the Excel Options Dialogue box, Select All Commands and Go to Form. Select Form and press Add. This will add the Data Forms Icon to the Quick Access Toolbar.
   

Using Excel Data Entry Form

1. Select any cell in the data range and click on the Data Form icon from the Quick Access Toolbar.
2. In the Pop-up Data form, all the column titles are listed vertically. Fill the data in the adjacent text box for each heading.
3. Once you have entered the data, press Enter.
4. Click on New to add a new record.

Using the Excel Data Entry form, you can also navigate through the existing data (row by row), or find data by specifying the criteria (try this by clicking on the criteria button).

Also read: Prevent Duplicate Entries in Excel

#2 Quickly Enter Numbers with Fixed Decimal Numbers

If you are in a situation where you have to manually enter data in Excel that also has a decimal part to it, this trick could be mighty useful.

For example, suppose you have to enter marks for students in percentage with up to 2 decimal points.

You can enable a feature where you just type the numbers without bothering about hitting the dot key every time. To enable this:

1. Go To File –> Options.
2. In the Excel Options Dialogue box, select Advanced.
3. In Advanced Options, check the option “Automatically Insert a decimal point”.
4. Specify the number of decimals you want (for example, 2 in this case).

Now, whenever you enter any number, excel automatically places the last 2 digits after the decimal. So 1 becomes .01, 10 becomes 0.1, 100 becomes 1, 2467 becomes 24.67, and so on.

Once you are done with data entry, simply switch it off by unchecking the same option.

#3 Automatically Add Ordinal to Numbers

Yes, you can use a combination of Excel functions (such as VALUE, RIGHT, IF, OR) to add ordinals (‘st’ in 1st, ‘nd’ in 2nd, and so on..).

Here is the formula:

=A1&IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),"th",IF(OR(VALUE(RIGHT(A1))={1,2,3}),CHOOSE(RIGHT(A1),"st","nd","rd"),"th"))

#4 Fill Down Using Control + D

This is a nifty Excel Data Entry trick. If you have to copy the above cell, just press Control + D.

It copies the content as well as the formatting. If there is a formula in the above cell, the formula is copied (with adjusted references).

This works for more than one cell as well. So if you want to copy the entire row above, select the current row and press Control + D.

If you select more than one cell/cells, than the cell above selection is copied to all the cells.

See Also: 200+ Excel Keyboard Shortcuts.

#5 Quickly Enter Date/Time in Excel Cells

Do not remember the current date? Don’t worry; just press Control + ; (semicolon Key), and it will enter the current date in the cell.

Similarly, you can enter the current time by using the shortcut Control + Shift + : (colon key)

Remember, these are absolute values and won’t change if the date or time changes.

If you want to have a dynamic date and time value that changes whenever you open the workbook or when there is a re-calculation, use TODAY and NOW functions.

Also See: How to Automatically Insert Date and Time Stamp in Excel.

#6 Control + Enter to Fill Entire Selection with Content in Active Cell

When you select multiple cells in Excel, one of the cells with light shade (as compared with the remaining selected cells) is the active cell.

If you enter something in the active cell when the selection is made and press Control + Enter, the value is inserted into all the selected cells.

This holds true for formulas as well. If you enter a formula in the active cell and press Control + Enter, all the cells will get that formula (with adjusted references).

#7 Alt + Down Arrow to Get a List of All Unique Values in that column

This one can save some time that you might otherwise waste in typing. Suppose you are entering data in a column with activity status (To be Done, Completed).

Instead of typing the status repeatedly, you just need to type it in a couple of cells, and then you can use the keyboard shortcut Alt + Down arrow.

It gives you a list of all the unique entries, and you can quickly select from the list.

If you want to select from pre-specified options, you can create a drop down list.

Also read: Cell References in Excel (Absolute, Relative, and Mixed )

#8 Quickly Sift through Multiple Worksheet Tabs in a Workbook

This one saves tons of time. Simply use the keyboard shortcut Control + Page Up/Page Down to navigate through multiple worksheet tabs in a workbook.

See Also: 200+ Excel Keyboard Shortcuts.

#9 Enter Long Text by Using Abbreviations

If you have to enter long names or keywords again and again in your worksheet, this trick will come in handy.

For example, you may want that whenever you type ABC, Excel should automatically replace it with ABC Technology Corporation Limited.

Something as shown below:

This feature can be enabled using the autocorrect options. Read how to enable it.

#10 Enter Data in Non-Contiguous Ranges

Suppose you have a template as shown below, and you have to enter data in all the cells highlighted in red, in the order specified by the arrows.

Here is a quick way to do this:

• Select all the cells where you need to enter the data (press control and then select one by one).
  • Start with the second cell in your sequence of data entry (start with D3). Select the first cell B3 (where data entry begins) at last.
• Once selected, go to Name Box (which is on the left of the formula bar).
• Type any name without spaces.
• That’s It!! Select the Named Range by clicking the Name Box drop-down. Now start entering your data and use Enter/Tab to jump to the next cell.

Read more about this trick.

These are my top ten excel data entry tips that can significantly improve your productivity and save time.

You May Also Like the Following Excel Tips:

• 10 Super Neat Ways to Clean Data in Excel Spreadsheets.
• 24 Daily Excel Issues and their Quick Fixes.
• How to Track Changes in Excel.
• How to Recover Unsaved Excel Files.
• How to Enable Conditional Data Entry in Excel.
• Suffering from Slow Excel Spreadsheets? 10 tips to SPEED-UP your Excel.
• Get more out of Find and Replace in Excel (4 Amazing Tips).
• How to Use Excel Freeze Panes in Excel.
• 100+ Excel Interview Questions
• Fill Down Blank Cells Until the Next Value in Excel