Excel Data Entry Tips

Enter Excel data quickly with Excel keyboard shortcuts and Excel mouse shortcuts. Enter dates and times that will update automatically, or remain locked, and more Excel data entry tips

Excel Quick Tricks

This video shows 3 quick Excel tricks that will save you time. See a quick way to enter grand totals, a fast method for creating a chart, and how to use AutoFill or Flash Fill for speedy data entry.

Data Entry Shortcuts

Here are some time-saving Excel shortcuts to use when you're entering data.

• Keyboard Shortcuts
• Mouse Shortcuts
• Go to the Excel Shortcuts page for an interactive list, and to get the file.

Keyboard Shortcuts

Date and Time

• Enter the current Date:  Ctrl + ;
• Enter the current Time:  Ctrl + Shift + ;

Watch this short video to see the keyboard shortcuts for entering dates and times in a worksheet.

Enter Data in Multiple Cells

1. Select all the cells in which you want to enter the same value or formula
2. Type the value or formula in the active cell



3. Hold the Ctrl key and press Enter

Copy to Adjacent Cells

1. Select the range, starting with the cell that contains the data to be copied
2. Use a shortcut key to fill right or down:
   • Copy Value from cell above:  Ctrl + Shift + '
   • Copy Formula (exact) from cell above:  Ctrl + '
   • Copy Formula (relational reference) from cell above:  Ctrl + D
   • Fill Right -- Ctrl + R
   • Fill Down -- Ctrl + D

Mouse Shortcuts

Use these mouse shortcuts to quickly fill adjacent cells. Also, see the mouse double-click shortcuts, for other timesaving tips.

Copy Data to Adjacent Cells

Point to the bottom right corner of the selected cell, and drag, to quickly fill adjacent cells.

1. Select the cell that contains the data to be copied
2. Point to the Fill Handle -- the black square at the lower right of the selection

   

3. When the pointer changes to a black plus sign, press the Left mouse button, and drag left, right, up or down, across one or more cells.

   

4. When finished, release the mouse button.

Copy to Adjacent Cells -- with options

Instead of pressing the left mouse button, use the Right mouse button to see options when filling cells.

1. Select the cell that contains the data to be copied
2. Point to the Fill Handle -- the black square at the lower right of the selection
3. When the pointer changes to a black plus sign, press the Right mouse button, and drag left, right, up or down, across one or more cells.
4. When finished, release the mouse button.
5. Select one of the options from the shortcut menu

Watch this Excel Quick Tips Video for creating a list of dates that are a week apart.

Pasting Tips & Troubleshooting

Here are a few tips for pasting, and for solving problems when pasting data in Excel.

• Pasted Numbers Change to Dates
• Ignore Blank Cells When Pasting
• Copy and Paste Numbers Only
• Paste Error, Missing Formulas- Multiple Selections

Pasted Numbers Change to Dates

If numbers such as 1-4 or 3/5 are pasted into Excel, they will usually change to dates. Other types of numbers, such as zip codes with leading zeros, lose their formatting too. For example, copy the numbers below, and paste them onto a worksheet, to see how Excel adjusts them.

• 3/4
• 02345
• 1-3

In the screen shot below, the original data is at the left, and the data pasted onto a worksheet, at the right. Two of the numbers were changed to dates, and the leading zero was dropped from the other number.

Prevent Numbers From Changing to Dates

To prevent Excel from changing the number format, follow these steps:

• In Excel, select the columns where you will paste the data -- be sure to include enough columns for all of the data that will be pasted
• On the Ribbon's Home tab, in the Number group, click the drop down arrow, and click on Text

• Select the data and copy it
• In the column(s) that you formatted, right-click the starting cell where you want to paste the data
• In the popup menu, under Paste Options, click Match Destination Formatting

• The data will be pasted in its original formatting, instead of changing to dates.

Get the Formatted Number Data

Get this data in Excel format, in the download section at the end of this page. The sample file has a text box, where you can copy the formatted numbers. Then, paste into the cells, to see how Excel automatically changes them to dates.

Ignore Blank Cells When Pasting

This video shows how to copy new data, where some cells are blank, and paste over existing data, without pasting the blank cells. Use the Paste Special command, and select Skip Blanks, to avoid overwriting existing data with blanks.

1. Copy the cells with the new data
2. Select the cells where you want to paste
3. On the Ribbon, click the arrow below the Paste button, and click Paste Special
4. In the Paste Special window, add a check mark in Skip Blanks, then click OK

You can get the sample file, to follow along with the video.

Copy and Paste Numbers Only

If a range of cells contains numbers, blank cells and text, you can use the Go To Special command to select just the cells with numbers. Then, copy those cells, and paste in another location on the worksheet.

Watch this short video, to see the steps. To follow along with the video, get the Copy and Paste Numbers sample file. The file is in xlsx format, and does not contain macros.

How to Copy and Paste Numbers Only

To select just the numbers in a column with formulas:

• Select the entire column
• On the Excel Ribbon's Home tab, click Find and Select
• Click Go To Special
• In the Go To Special window, select Formulas
• Remove the check marks from Text, Logicals and Errors, then click OK

NOTE: This technique can be used for numbers that were typed into the cells, or for numbers that are the result of a formula.

Copy and Paste Multiple Selections

You can run into problems if you copy multiple ranges, and try to paste them to a new location. You might see an error, "This action won't work on multiple selections", or values might be pasted, instead of formulas.

Watch this short video, to see how to avoid problems when copying and pasting multiple selections. To follow along with the video, get the sample file for copy and paste problems.

Avoid Copy Error for Multiple Selections

If you select more than one group of cells on a worksheet, and try to copy them, Excel might show an error message, "This action won't work on multiple selections".

To avoid that error, be sure to select multiple regions in either:

• the exact same columns
• OR, the exact same rows

For example, Excel will show an error message if you select cells in columns A:C, and make another selection in different columns, A:B

Be sure that each selection includes exactly the same rows or columns, like the next example, with both selections in columns A:C.

Avoid Paste Problem for Multiple Selections

If you copy more than one group of cells on a worksheet, and paste them in a different location, Excel might change your formulas to values.

To keep the formulas, follow these steps:

• Copy the multiple selections
• Right-click the cell where you want to start the paste
• In the pop-up menu, click Paste Special
• In the Paste Special window, click OK

Insert and Delete Cells

To insert or delete a block of cells, you can use the mouse commands, or use the Fill Handle as a shortcut.

To see the steps, watch this short video

Insert Multiple Blank Rows

You can quickly insert a single row in Excel by using a mouse shortcut. Did you know that you can insert several rows at the same time? This video shows you the quick tip for inserting a single row in Excel, or inserting multiple rows.

Go Back to Previous Locations

In Excel you can store up to 4 locations temporarily, and quickly go back to those cells. Watch this video to see the steps. The written instructions are below the video.

NOTE: The locations are only stored temporarily, so when you close the file and reopen it, you will have to store a new set of temporary locations.

Go Back to Previous Locations

Here are the steps to store up to 4 temporary locations, and go to back to those locations quickly.

1. Select a cell on the worksheet
2. Click in the Name Box, which is to the left of the Formula Bar

   

3. Press the Enter key, to temporarily store that location.

To return to a stored location:

1. To open the Go To dialog box, press the F5 key, or press Ctrl + G

   

2. If the location that you want is in the Reference box, press the Enter key, to go to that cell
3. To go to another location in the list, double-click on that location in the list.

Video: Clear Data Entry Cells

In Excel, you can use a built-in command to quickly select all the cells with data typed into them, and ignore the cells with formulas. Then, after you select the data cells, use the keyboard or a Ribbon command to clear the cells.

Watch this short video to see the steps.

Quickly Create Random Data

To quickly create data for a quick test, use the RANDBETWEEN function, with minimum and maximum values. To see the steps, watch the video below.

1. In the screen shot below, cells B2:G7 are selected.
2. Type the formula: =RANDBETWEEN(10,100)
3. Press the Ctrl key, then press Enter, to enter the formula in all the selected cells.

If you want to change the formulas to static values, follow the instructions in the next section.

You can also get an Excel file with sample data to use for your tests.

Video: Create Random Test Data

See how to quickly create test data with month headings, customer numbers, and random numbers, then change the formulas to static values.

Quickly Change Formulas to Values

To quickly change formulas into values, you can use this mouse shortcut, shown in the video below. The written instructions are below the video.

To change formulas to their values, follow these steps:

1. Select the cells with the formulas that you want to change to values.
2. Point to the border of the selected range, and the pointer should change to a four-headed arrow.
3. Press the right mouse button, and drag the cells slightly to the right.
4. Keep pressing the right mouse button, and drag the cells back to their original location.
5. Release the right mouse button and a shortcut menu will appear.
6. Click on Copy Here as Values Only.

Video: Create a Custom List

Create custom lists in Excel, and you can sort based on the list items, or use the list for quick data entry. Watch this short video to see the steps, and the written instructions are below the video..

Create a Custom List

In Excel, you can create custom lists, like the built-in lists of weekdays and months. For example, you could create a custom list of districts, department names, or reporting categories, and then use the custom lists to sort the items in a list or in a pivot table.

The entries for the custom list can be imported from a worksheet list, or typed in the Custom Lists dialog box. In this example, the list of cities is typed.

1. On the Ribbon, click the File tab, and click Excel Options.
2. In the list of categories, click Advanced, and in the General section, click Edit Custom Lists.

   

3. In the Custom Lists dialog box, under Custom Lists, select NEW LIST
4. Click in the List Entries section, and type your list, pressing the Enter key after each item, to separate the list items.

   

5. In this example, the list is New York, Boston, Chicago, Seattle, Los Angeles, Dallas, Miami
   Tip: Instead of typing a list in the List Entries box, you can import a list from the worksheet by selecting the list and clicking the Import button
6. Click OK twice, to close the dialog boxes.

Use the Custom List For Data Entry

You can use the custom lists when sorting, and you can also use them with the AutoFill feature.

To quickly create a list, based on a custom list:

1. Type any item from a custom list in a cell
2. Select the cell, and point to the Fill handle

   

3. Drag down, up, left or right, to complete the list.

Add Line Break in Cell or Formula

To show text on separate lines within a single cell, add a line break. The steps are shown below.

Add a line break when typing in a cell

1. Select the cell
2. In the formula bar, click where you want the line break

   

3. On the keyboard, press Alt + Enter, to add a line break
4. Press Enter, to complete the formula

The cell will automatically be formatted with Wrap Text, and you might need to widen the column.

Add a line break in a formula

1. Select the cell
2. In the formula bar, click in the formula, where you want the line break

   

3. To add a line break use this character, with the & operator:

CHAR(10)

4. Press Enter, to complete the formula

For example, change this formula:

="Total amount is: " & SUM(C1:C6)

to this:

="Total amount is: " & CHAR(10) & SUM(C1:C6)

Turn on Wrap Text

When you add a line break to a formula, the cell is NOT automatically formatted with Wrap Text, so you might need to turn that feature on.

Otherwise, you will see a small box where the line break should be.

To turn on Wrap Text:

1. Select the cell
2. On the Excel Ribbon, click the Home tab
3. In the Alignment group, click Wrap Text.

Video: Find and Replace Line Breaks

In this video, Sarah shows how to add a line break in a cell. Later, you can replace all the line breaks with a space character, by using the Find and Replace dialog box. The written instructions are below the video.

Find and Replace Line Breaks

To create a line break in a cell, you press Alt + Enter, as described in the previous section. Later, if you want to replace all the line breaks with a space character, you can use a special shortcut -- Ctrl + J -- in the Find and Replace dialog box.

Note: A line break (line feed) is character 10 in the ASCII characters, and the Ctrl + J shortcut is the original ASCII control code for character 10.

To find a line break:

1. Select the cells that you want to search
2. On the keyboard, press Ctrl + F to open the Find and Replace dialog box, with the Find tab active
3. Click in the Find What box
4. On the keyboard, press Ctrl + J to enter the line break character -- NOTE: Nothing will appear in the Find What box
5. Click Find Next or Find All, to find the cells with line breaks.

• NOTE: See the troubleshooting tips below, if Excel can't find line breaks

To replace a line break with a space character:

1. Select the cells that you want to search
2. On the keyboard, press Ctrl + H to open the Find and Replace dialog box, with the Replace tab active
3. Click in the Find What box
4. On the keyboard, press Ctrl + J to enter the line break character -- NOTE: Nothing will appear in the Find What box
5. Press the Tab key on the keyboard, to move to the Replace With box
6. Type a space character
7. Click Find Next or Find All, to find the cells with line breaks.
8. Click Replace or Replace All, to replace the line breaks with space characters.

Troubleshooting Tip

If you try to find line breaks a second time, Excel might say it can't find any. Try these steps to fix the problem:

1. Press Ctrl+H to open Find and Replace
2. Click on the Replace tab -- you'll see a light border around the word
3. Press the Tab key -- that selects everything in the Find What box
4. Type Ctrl+J -- that replaces anything in the Find What box
5. Then click click one of the Find or Replace buttons at the bottom of the form

Enter Data With a Built-In Form

Use Excel's built-in Data Form to make it easier to enter data in a list. It will display a maximum of 32 fields.

Watch the following video, to see how the data form works. There are written instructions below the video.

Video: Enter Data With a Built-In Form

To see the steps for using the Data Form, and adding it to the Excel 2010 Quick Access Toolbar, please watch this short video tutorial

Open the Built-In Data Form

Note: Fields which contain a formula, such as Total in the Data form shown here, will not have a text box. The formula will be entered and calculated automatically.

The Data Form command isn't on the Ribbon in Excel 2010, so you can use a keyboard shortcut to open it, or add the command to the Quick Access Toolbar (QAT).

Use Keyboard Shortcut

To open the data form from the keyboard:.

1. Press Alt + D, then type o

This keyboard shortcut is based on the old Excel 2003 menu command to open the data form. In the screen shot below, the hot keys are underlined, and you could press those keys to activate that menu or command:

• the D is underlined in the Data menu
• the o is underlined in the Form command

Add QAT button

Follow these steps to add the Data Form command to the Quick Access Toolbar (QAT):

1. Right-click the QAT, and click Customize Quick Access Toolbar
2. From the drop down list, select All Commands
3. Scroll down and click on Form..., then click Add, to put it on the QAT
4. Close the window, then click the Form button on the QAT

To show the Data Form in Excel 2003:

Follow these steps to open the Data Form in Excel 2003.

1. Select a cell in the list.
2. Choose Data|Form
3. Click the New button, and enter the new record

Use the Data Form

After you open the Data Form, use these steps to add, view, and edit the records

• To add a record, click New, and enter the data
• To move through the records, click the Up arrow and Down arrow keys, or use the scroll bar on the Data Form.
• To edit a record, move to that record, and change the data in the entry boxes.
• While editing a record, you can click the Restore button, to restore the current values. To complete the edit, click Close, or move to another record. NOTE: After editing a record, if you click Find Prev or Find Next, the changes are not saved.
• To delete a record, scroll to that record, then click the Delete button
• To find a specific records, or the first record that meets your criteria, click the Criteria button, and enter the criteria, then click Find Prev or Find Next.

  

Data Form Quirks

• Data Validation lists, and other data validation settings, are ignored.
• It doesn't matter which cell in the table is selected -- the Data Form will open at the first record.
• If there is a range named Database on the worksheet, the Data Form will always show that list, even you have selected a cell in a different table.
• The width of the boxes in the Data Form is connected to the widest column in the underlying table. If you widen that column, the Data Form will adjust automatically.
• If numeric columns are too narrow, and some cells show number signs, those number signs might be shown in the Data Form, even if its data entry boxes are wide enough to show the number. And unlike the worksheet, where you can point to a cell with number signs, and see a popup with the value, there's no such feature in the Data Form.

  

Video: Add Bullets to Cell or Text Box

There's no built-in bullet for cells, but this video shows how you can add a bullet to a cell with a keyboard shortcut (using numbers on the number keypad) -- press Alt + 0149 or Alt + 7. For an open circle bullet, use Alt + 9

In a Text Box, it's easier to add bullets to a list -- right-click on the text, and click Bullets, in the popup menu.

Quick Pick From List

This quick data entry trick works best in a table where you already have quite a few entries. To see the trick, watch this short video. The written instructions are below the video, and you can get the sample file, to follow along with the video.

Quick Pick from List

Here's an example of how I use the quick pick trick. Every day I enter weather info in a worksheet, with the high temperature, and a short description.

Instead of typing out the description, I type the first letter or two in the cell, then right-click on it (while still in the cell). In the pop-up list, click on Pick From Drop-down List

• TIP: To use a keyboard shortcut, right-click in the cell after typing a letter or two, then press the Alt key, and tap the Down Arrow key.

A list appears, with all the items previously entered in the active column, in alphabetical order. It selects the first item that starts with the letters that you typed -- a real time-saver!

Scroll up or down, if necessary, to find the item that you want, and click on it to put it into the cell.

You can even use this trick in cells that have a Data Validation (DV) drop down list. It's handy if there's a long list of items, and you want something near the end of the list.

NOTE: In a cell with a DV drop down, unless you type a letter or two, the Alt+ Down Arrow shortcut will open the Data Validation list. In other cells, it will open the "Quick Pick" list.

Customize Right-Click Menu

For an easy way to customize the right-click menu, get a copy of Doug Glancy's free Excel tool, the MenuRighter Add-in.

To see the steps for adding a command to a right-click menu, by using the free MenuRighter add-in, watch this short video.

For more instructions, and to get the MenuRighter add-in, you can visit Doug Glancy's website: MenuRighter Add-in.

Get the Sample Files

Copy and Paste Problems: Get the sample file for multiple selections copy and paste problems, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.

Formatted Numbers: Get the sample file for pasting formatted numbers, to see how Excel sutomatically changes the numbers to dates. The zipped file is in xlsx format, and does not contain any macros.

Ignore Blanks: Get the sample file for pasting with Ignore Blanks, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.

Pick From List: Get the Quick Pick from List sample file, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.

Line Breaks: Get the sample file for adding and removing line breaks, to follow along with the video. The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Fix Copy & Paste Problems

Fill Blank Cells

AutoFill Examples

Excel Shortcuts List

Add Number to Multiple Cells