Search Contextures Sites

 

More Data Entry Links

Fill Blank Cells

Convert Text to Numbers

Increase Numbers With Paste Special

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

Learn how to create Excel dashboards.

 

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

Learn how to create Excel dashboards.

 

 

Live-link your Excel dashboards to nearly any web data.

 

 

 

Learn how to create Excel dashboards.

 

Learn how to create Excel dashboards.

Excel Data Entry Tips

Data Entry Keyboard Shortcuts

Use Shortcut Keys

  • Enter the current Date:  Ctrl + ;
  • Enter the current Time:  Ctrl + Shift + ;
  • Copy Value from cell above:  Ctrl + Shift + '
  • Copy Formula (exact) from cell above:  Ctrl + '
  • Copy Formula (relational reference) from cell above:  Ctrl + D

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

Enter Data in Multiple Cells -- Ctrl + Enter

  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 Data 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:
    • Fill Right -- Ctrl + R
    • Fill Down -- Ctrl + D

Data Entry Mouse Shortcuts

Copy Data to 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

  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.

Video: 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

Video: 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.

Go Back to Previous Locations

In Excel, you can store up to 4 temporary locations, and to back to those locations quickly.

To store a location:

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

    4. 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. name box

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

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.

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 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.

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. custom list dialog box

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

  7. 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
  8. 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. custom list dialog box

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

Add Line Break in Cell or Formula

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:
  4. CHAR(10)

  5. 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.

Pasted Numbers Change to Dates

If you copy numbers such as 1-4 or 3/5 and paste them into Excel, they will usually change to dates. In the screen shot below, you can see the original data at the left, and the data pasted onto a worksheet, at the right.

Note: You can get the sample data here, to test copying and pasting it

numbers change to dates

To prevent this, follow these steps:

  1. 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
  2. On the Ribbon's Home tab, in the Number group, click the drop down arrow, and click on Text

    number format text

  3. Copy the data that you want to paste onto the worksheet
  4. Select the starting cell where you want to paste the data
  5. On the Ribbon's Home tab, click the drop down arrow on the Paste command
  6. Click Match Destination Formatting
    OR click Paste Special, then click Text, and click OK.

    number format Match Destination Formatting

  7. The data will be pasted in its orginal number formatting, instead of changing to dates.

Video: 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 download the sample file, to follow along with the video. The zipped file is in xlsx format, and does not contain macros.

Video: Find and Replace Line Breaks

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.

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.

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.

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 in Excel 2010:

  1. Follow these steps to add the command to the Quick Access Toolbar (QAT):
  2. Right-click the QAT, and click Customize Quick Access Toolbar
  3. From the drop down list, select All Commands
  4. Scroll down and click on Form..., then click Add, to put it on the QAT
  5. Close the window, then click the Form button on the QAT

Add QAT button in Excel 2010:

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

add data form to 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.

    DataForm07

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.

    DataForm07

For a data form with additional features, you can try John  Walkenbach's Enhanced Data Form. It's free, and allows unlimited fields.

Video: Copy and Paste Problems

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.

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.

Search

Search Contextures Sites

 

More Data Entry Tutorials

1. Excel Data Entry Tips
2. Excel Data Entry -- Fill Blank Cells
3. Excel Data Entry -- Convert Text to Numbers
4. Excel Data Entry -- Increase Numbers With Paste Special

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: November 18, 2014 3:29 PM