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
Data Entry Shortcuts
Here are some time-saving Microsoft Excel shortcuts to use when you're entering data.
Date and Time
Use a shortcut key to quickly enter the date or time in your spreadsheet:
Watch this short video to see the keyboard shortcuts for entering dates and times in a worksheet.
Enter Data in Multiple Cells
Copy to Adjacent Cells
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.
Copy to Adjacent Cells -- with options
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.
Pasting Tips & Troubleshooting
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.
Prevent Numbers From Changing to Dates
To prevent Excel from changing the number format, follow these steps:
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.
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 get the sample file, to follow along with the video.
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:
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, "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:
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:
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.
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.
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 get 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.
Open the Custom Lists Dialog
To open the Custom Lists dialog box, follow these steps, or use the keyboard shortcut in the next section:
Keyboard Shortcut to Open Custom Lists Dialog
This is more of a "longcut" than a shortcut, but here's how you can open the Custom Lists dialog box with keystrokes, if you prefer to keep your hands on the keyboard:
Create a New Custom List
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.
To create a new custom list, follow these steps:
Use the Custom List For Data Entry
To quickly create a list, based on a custom list:
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
The cell will automatically be formatted with Wrap Text, and you might need to widen the column.
Add a line break in a formula
For example, change this formula:
="Total amount is: " & SUM(C1:C6)
="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:
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:
To replace a line break with a space character:
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:
Use Excel's built-in Data Form to make it easier to enter data in a list.
Note: This Excel data entry form 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).
Use Keyboard Shortcut
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:
Add QAT button
Follow these steps to add the Data Form command to the Quick Access Toolbar (QAT):
To show the Data Form in Excel 2003:
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
Here are a few quirks that you'll encounter, when using the built-in Excel data entry form.
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.
Add Degree Symbol to Cell
If you're typing numbers that are temperatures, there's no built-in number format that applies a degree symbol. Instead, you can use a custom number format, to automatically add a degree symbol after each number.
Custom Number Format
If you're entering temperatures in Excel, you can format the cells to show the degree symbol.
Follow these steps if your computer keyboard has a number keypad, at the right side of the keyboard.
Note: There are more Custom Number Format examples on the Rounding functions page
Enter Degree Symbol Without Number Keypad
Thanks to Alex Blakenburg, who sent these instructions. See more of Alex's Excel tips and sample files.
If you don't have a number keypad, try this alternate method to enter a degree symbol.
Then, return to Add Degree Symbol to Cell steps above, from Step 6, to complete the custom number format.
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
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.
How to Customize Excel Right-Click Menus
If you use the mouse most of the time, when you’re working in Excel, you probably right-click, to see the popup menus. For example, right-click a column heading to insert a new column.
If your favourite commands aren’t on those right-click popup menus, here are a couple of free Excel add-ins that you can use to change Excel right-click menus.
1) Excel Filter Right-Click Add-in
This free add-in puts filter commands at the top of the right-click menu.
Click here to go to the Filter Right-Click add-in page.
2) MenuRighter Add-in
This free add-in, by Doug Glancy from YourSumBuddy, makes it easy to customize the right-click menu. No programming skills needed.
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.
Last updated: November 9, 2022 11:59 AM