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
Here are some time-saving Excel shortcuts to use when you're entering data.
Watch this short video to see the keyboard shortcuts for entering dates and times in a worksheet.
Use these mouse shortcuts to quickly fill adjacent cells. Also, see the mouse double-click shortcuts, for other timesaving tips.
Point to the bottom right corner of the selected cell, and drag, to quickly fill adjacent cells.
Instead of pressing the left mouse button, use the Right mouse button to see options when filling cells.
Watch this Excel Quick Tips Video for creating a list of dates that are a week apart.
Here are a few tips for pasting, and for solving problems when pasting data in Excel.
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.
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.
To prevent Excel from changing the number format, follow these steps:
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.
You can download the sample file, to follow along with the video. The zipped file is in xlsx format, and does not contain macros.
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, download the Copy and Paste Numbers sample file. The file is in xlsx format, and does not contain macros.
To select just the numbers in a column with formulas:
NOTE: This technique can be used for numbers that were typed into the cells, or for numbers that are the result of a formula.
You can run into problems if you copy multiple ranges, and try to paste them to a new location. You might see an error, "That command cannot be used on multiple selections", or values might be pasted, instead of formulas.
See the steps for copying and pasting multiple selections in this short video tutorial.
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
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.
Here are the steps to store up to 4 temporary locations, and go to back to those locations quickly.
To return to a stored location:
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.
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.
If you want to change the formulas to static values, follow the instructions in the next section.
You can also download an Excel file with sample data to use for your tests.
See how to quickly create test data with month headings, customer numbers, and random numbers, then change the formulas to static 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:
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..
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.
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:
The cell will automatically be formatted with Wrap Text, and you might need to widen the column.
For example, change this formula:
="Total amount is: " & SUM(C1:C6)
="Total amount is: " & CHAR(10) & SUM(C1:C6)
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.
This video shows how to create a line break in a cell, and then replace all the line breaks with a space character, by using the Find and Replace dialog box.
The written instructions are below the video.
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:
To replace a line break with a space character:
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.
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
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).
To open the data form from the keyboard:.
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:
Follow these steps to add the Data Form command to the Quick Access Toolbar (QAT):
Follow these steps to open the Data Form in Excel 2003.
After you open the Data Form, use these steps to add, view, and edit the records
For a data form with additional features, you can try John Walkenbach's Enhanced Data Form. It's free, and allows unlimited fields.
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.
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.
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
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.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: November 28, 2018 7:02 PM
Contextures RSS Feed