5 Excel Shortcuts to Drastically Speed Up Data Entry

Even if you’ve been using Excel for a while, there may be some shortcuts you’re not familiar with. I’ve been using Excel for over 30 years, and I still learn new things about the program almost every day.

In this article, I’ll show you five shortcuts that can help cut down on the time you spend doing data entry in Excel. The five shortcuts will show you how to:

1. Keep Leading Zeros
2. Enter the Current Date and/or Time
3. Group Cells for Data Entry
4. Use AutoFill and Paste Options
5. Fill a Selection of Cells at Once

Let’s see how each of these shortcuts work!

One: Keep Leading Zeros

When you enter a number in Excel that starts with a zero, Excel will drop the leading zero. This is because Excel interprets what you have typed as a numeric value, and numeric values don’t need leading zeros.

If you want to keep the leading zero, for instance, if you are typing zip codes, you can type an apostrophe (‘) before your entry, and Excel will interpret the value as Text and keep the leading zero. But it can get annoying typing apostrophes.

An easier way to handle this issue is to format the cells where you know you might have leading zeros as Text before typing.

As you can see in the image above, the first column shows what happens when you type zip codes with no special formatting; the leading zeros are dropped off. The second column shows zip codes typed with leading apostrophes (you can’t see it in the image, but if you looked at the formula bar for any of these cells, you’d see it); this keeps the leading zeros. The third column shows the zip codes entered in cells formatted as Text; again, the leading zeros are kept.

To format cells as Text:

• Select the cells to format.
• Click the Number Formatting dropdown on the Home tab of the command ribbon.
• Select the “Text” option.

Two: Enter the Current Date and/or Time

There will be plenty of times when you want to enter the current date or the current time (or both) in a cell of your worksheet. You can use the keyboard shortcuts in the table below to quickly enter the current date, time, or both in a cell.

If you want to include the date and the time in a cell:

• Use the shortcut to enter the date.
• Release the CTRL-key.
• Type a Space.
• Use the shortcut to enter the time.

Just keep in mind that these shortcuts enter the current date or time, and the value will not change each time the worksheet is opened or updated. If you need a dynamic date or time, you’ll need to use one of Excel’s built-in functions like Today() or Now().

Three: Group Cells for Data Entry

When you have a group of cells that you need to enter data in, a great way to speed things up is to select all the cells first. This process works especially well if the cells you need to enter data in are not adjacent. Instead of moving your hands off the keyboard to use the mouse for the next selection, you can continue using the keyboard for the data entry.

As an example, let’s say you needed to update a weekly timesheet, like the one below. You need to enter the times worked for each employee, but there is a column between them. If you enter the data without selecting the cells requiring an entry, you’d need to press the TAB key an extra time between each employee’s name.

If you select all the cells you want to enter data in, you can avoid the extra cells.

If you needed to enter the times for all five days for all three employees in the Weekly Timesheet above, the best way to do it would be to:

• Select all the cells for John K. (D5 -D9).
• Hold the CTRL-key.
• Select all the cells for Martin T. (F5 – F9).
• Continue holding the CTRL-key.
• Select all the cells for Jessica C. (B5 – B9).
• Release the CTRL-key.

When you’re done selecting the cells, your worksheet will look like the image below:

Now you can enter the times for the new week, starting with Jessica C’s Monday entry. Then press TAB or ENTER to get to the next cell. When you enter Friday’s value for Jessica C, the TAB or ENTER key will take you to John K’s Monday entry cell.

If you do this data entry each week, you can make things even easier by saving the selected cells as a named range. If you make a copy of the spreadsheet for the next week, you can select the name from the name box, and the selection will be highlighted and ready for data entry.

Four: Use AutoFill and Paste Options

With the AutoFill feature, you can enter data in one cell and quickly copy it to other cells above, below, to the left, or to the original cell’s right.

To use AutoFill, enter a value or formula in a cell, then use the AutoFill Handle to copy the original value or formula to other cells. The AutoFill Handle is the tiny square in the bottom right-hand corner of the selected cell.

The handle can be used in two ways:

• Drag the handle in the direction you want to fill.
• Double-click the handle to copy down the current column.

When you double-click the fill handle, the current cell’s contents will be copied down the column to the last row with data in the contiguous data surrounding the cell. If you were to double-click the AutoFill handle to copy the Markup formulas in the product list above, the formula in cell D4 would be copied to cells D5 through D8.

Sometimes AutoFill does something unexpected when it pastes the values. In the example below, when I AutoFill the column, the formatting from cell D4 is applied to cells D5 through D8. This messes up the alternating row background colors.

To correct this issue, you can use the Paste Options icon after you AutoFill and select a better paste option. In my case, I chose the “Fill Without Formatting” option so that my cell background colors were not changed.

Five: Fill a Selection of Cells at Once

If you’d like to enter the same value or formula in multiple cells, you can speed up the process by first selecting all the cells and then press CTRL+ENTER instead of just ENTER to save your changes. This will add the same value or formula to all the selected cells.

I used the RANDBETWEEN() function to add random, six-digit numbers to all the selected cells in the image below.

Conclusion

So those are just some of the shortcuts you can use in Excel to speed up data entry. Were any of these shortcuts new to you? Do you have any data entry shortcuts that you like to use in Excel? Share them below!

Brenda Keller is a software coach who helps people feel comfortable with and learn how to use software. She has been an Excel user since it was first introduced in 1985 and has taught the program in various settings. Her new Microsoft Excel: Quick Start course was designed to help those new to Excel or those wanting a refresher to get a handle on Excel’s key features quickly. Check out her course on Udemy here or view her YouTube channel here to get more help with Excel. To learn more about Brenda Keller, check out her official website here.