Skills > Data Entry > Excel Shortcuts List Excel Data Entry TipsEnter 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 ShortcutsHere are some time-saving Microsoft Excel shortcuts to use when you're entering data.
Keyboard ShortcutsDate and TimeUse 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
Mouse ShortcutsUse these mouse shortcuts to quickly fill adjacent cells. Also, see the mouse double-click shortcuts, for other timesaving tips. Copy Data to Adjacent CellsPoint to the bottom right corner of the selected cell, and drag, to quickly fill adjacent cells.
Copy to Adjacent Cells -- with optionsInstead 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 & TroubleshootingHere are a few tips for pasting, and for solving problems when pasting data in Excel.
Pasted Numbers Change to DatesIf 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 DatesTo prevent Excel from changing the number format, follow these steps:
Get the Formatted Number DataGet 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 PastingThis 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. Copy and Paste Numbers OnlyIf 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 OnlyTo 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. Copy and Paste Multiple SelectionsYou 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 SelectionsIf 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 SelectionsIf 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:
|
Video: Clear Data Entry CellsIn 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 DataTo 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. Video: Create Random Test DataSee how to quickly create test data with month headings, customer numbers, and random numbers, then change the formulas to static values. |
Video: Create a Custom ListCreate 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 ListIn 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 DialogTo open the Custom Lists dialog box, follow these steps, or use the keyboard shortcut in the next section:
Keyboard Shortcut to Open Custom Lists DialogThis 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 ListThe 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 EntryYou 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:
|
Add Line Break in Cell or FormulaTo 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
CHAR(10) For example, change this formula: ="Total amount is: " & SUM(C1:C6) to this: ="Total amount is: " & CHAR(10) & SUM(C1:C6) Turn on Wrap TextWhen 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:
Video: Find and Replace Line BreaksIn 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 BreaksTo 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:
Troubleshooting TipIf you try to find line breaks a second time, Excel might say it can't find any. Try these steps to fix the problem:
|
Video: Add Bullets to Cell or Text BoxThere'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 CellIf 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 FormatIf 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 KeypadThanks 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 ListThis 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 ListHere'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 MenusIf 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-inThis 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-inThis free add-in, by Doug Glancy from YourSumBuddy, makes it easy to customize the right-click menu. No programming skills needed. |
Get the Sample FilesCopy 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 |
Last updated: November 9, 2022 11:59 AM