Home > Skills > Data Entry > Tips & Troubleshooting 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 ShortcutsIn the sections below, you'll find 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. For the written steps, and the sample Excel file, go to the Skip Blanks When Pasting page. 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:
|
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:
to this:
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. 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: August 3, 2023 9:59 AM