2 Work with data and Excel tables

With Excel 2019, you can visualize and present information effectively by using charts, graphics, and formatting, but the data is the most important part of any workbook. By learning to enter data efficiently, you will make fewer data-entry errors and give yourself more time to analyze your data, so you can make decisions about your organization’s performance and direction.

Excel provides a wide variety of tools you can use to enter and manage worksheet data effectively. For example, you can organize your data into Excel tables so that you can store and analyze your data quickly and efficiently. You can also quickly enter a data series, repeat one or more values, and control how Excel formats cells, columns, and rows that you move from one part of a worksheet to another, all with minimal effort. Finally, with Excel, you can check the spelling of worksheet text, use the thesaurus to look up alternative words, and translate words to foreign languages.

This chapter guides you through procedures related to entering and revising Excel data, moving data within a workbook, finding and replacing existing data, using proofing and reference tools to enhance your data, and organizing your data by using Excel tables.

Enter and revise data

After you create a workbook, you can begin entering data. The simplest way to enter data is to click a cell and type a value. This method works very well when you’re entering a few pieces of data, but it is less than ideal when you’re entering long sequences or series of values.

For example, suppose you are creating a worksheet tracking each customer’s monthly program savings. You could repeatedly enter the sequence January, February, March, and so on by copying and pasting the first occurrence of the sequence, but there’s an easier way to do it: by using AutoFill. With AutoFill, you enter the first element in a recognized series, and then drag the fill handle in the lower-right corner of the cell until the series extends far enough to accommodate your data. By using a similar tool, FillSeries, you can enter two values in a series and use the fill handle to extend the series in your worksheet.

You do have some control over how Excel extends the values in a series when you drag the fill handle. If you drag the fill handle up or to the left, Excel extends the series to include previous values. For example, if you enter January in a cell and then drag that cell’s fill handle up or to the left, Excel places December in the first cell, November in the second cell, and so on.

Another way to control how Excel extends a data series is by holding down the Ctrl key while you drag the fill handle. If you select a cell that contains the value January and then drag the fill handle down, Excel extends the series by placing February in the next cell, March in the cell after that, and so on. If you hold down the Ctrl key while you drag the fill handle, however, Excel repeats the value January in each cell you add to the series.

Other data-entry techniques you’ll learn about in this section include the following:

• AutoComplete This detects when a value you’re entering is similar to previously entered values.

• Pick from Drop-Down List You can use this to choose a value from among the existing values in a column.

• Ctrl+Enter Use this to enter a value in multiple cells simultaneously.

The following table summarizes these data-entry techniques.

Another handy feature in Excel is the AutoFill Options button that appears next to data you add to a worksheet by using the fill handle.

Clicking the AutoFill Options button displays a menu of actions Excel can take regarding the cells affected by your fill operation. The options on the menu are summarized in the following table.

To enter values into a cell

1. Click the cell into which you want to enter the value.

2. Type the value by using the keyboard.

3. Press Enter to enter the value and move one cell down.

   Or

   Press Tab to enter the value and move one cell to the right.

To extend a series of values by using the fill handle

1. Select the cells that contain the series values.

2. Drag the fill handle to cover the cells where you want the new values to appear.

To enter a value into multiple cells at the same time

1. Select the cells into which you want to enter the value.

2. Enter the value.

3. Press Ctrl+Enter.

To enter cell data by using AutoComplete

1. Start entering a value into a cell.

2. Use the arrow keys or the mouse to highlight a suggested AutoComplete value.

3. Press Tab.

To enter cell data by picking from a list

1. Right-click the cell below a list of data.

2. Click Pick from Drop-down List.

3. Click the value you want to enter.

To control AutoFill options

1. Create an AutoFill sequence.

2. Click the AutoFill Options button.

3. Click the option you want to apply.

Manage data by using Flash Fill

When you manage data in Excel, you will often find that you want to combine values from several cells into a single value. For example, one common data configuration is to have a customer’s first name, last name, and middle initial in separate cells.

You could combine this data into a separate cell to show each customer’s full name manually or by creating a formula. Alternatively, you can use Flash Fill. Flash Fill can figure out the pattern if you give it a few examples.

Note that in this example, Flash Fill did not include middle initials in the FullName column. This was because some rows did not contain a middle initial. If you click in the FullName cell next to a row that contains an Initial value and edit the name as you would like it to appear, Flash Fill recognizes the new pattern for this subset of the data and offers to fill in the values. Press Enter to accept the values Flash Fill suggests.

Flash Fill also lets you fix errors in your data. One common issue occurs when you try to enter numbers with leading zeros, such as United States postal codes, into cells formatted as General or with a number format. If you enter a zero-leading number into such a cell, Excel removes the zero.

To fix this error, select the cells that contain the postal codes and format those cells and the cells in the next column as text. Then, in the blank cell next to the first postal code that should have a leading zero, enter the postal code as it should appear, and press Enter. When you start entering the postal code into the second cell, Flash Fill offers to change the data by adding a zero to every value in the list.

Flash Fill guessed that you wanted to add a zero to every postal code, but this change is incorrect for any value that should start with a number other than zero. To correct this, after you accept the values Flash Fill suggests, move to a blank cell next to a postal code that shouldn’t start with a zero and enter the correct value. When you do, Flash Fill updates its logic to suggest the correct values.

To enter data by using Flash Fill

1. In a cell on the same row as data you’re working with, enter the desired value based on data in that row, and press Enter.

2. In the cell directly below the first cell into which you entered data, start entering a new value based on data in that row.

3. Press Enter to accept the suggested value.

To correct a Flash Fill entry

1. Create a series of Flash Fill values in a worksheet.

2. Edit a cell that contains an incorrect Flash Fill value so that it contains the correct value.

3. Press Enter.

Move data within a workbook

You can move and copy data in lots of ways. First, though, you must select the data. The most direct method of selecting data is to click the cell that contains it. The cell you click will be outlined in black, and its contents, if any, will appear in the formula bar. When a cell is outlined, it is the active cell. You can cut, copy, delete, or change the format of the contents of a selected cell.

You’re not limited to selecting cells individually. You can also select cells that are a part of a range. Alternatively, you can select an entire column or row. For example, you might need to move a column of price data one column to the right to make room for a column of headings that indicate to which category a set of numbers belongs. To move an entire column (or columns) of data at a time, you first click the column’s header, located at the top of the worksheet, to select it. Clicking a column header highlights every cell in that column so that you can copy or cut the column and paste it elsewhere in the workbook. Similarly, clicking a row’s header highlights every cell in that row, so that you can copy or cut the row and paste it elsewhere in the workbook.

When you copy a cell, cell range, row, or column, Excel copies the cells’ contents and formatting. The Paste Live Preview capability in Excel displays what your pasted data will look like without forcing you to commit to the paste operation.

If you point to one icon in the Paste gallery and then point to another icon without clicking, Excel will update the preview to reflect the new option. Depending on the cells’ contents, two or more of the paste options might lead to the same result.

After you click an icon to complete the paste operation, Excel displays the Paste Options button next to the pasted cells. Clicking the Paste Options button displays the Paste Options palette. Pointing to one of the options in the palette doesn’t generate a preview, however. If you want to display Paste Live Preview again, you will need to press Ctrl+Z to undo the paste operation and, if necessary, cut or copy the data again with the icons in the Clipboard group of the Home tab.

After cutting or copying data to the Clipboard, you can access additional paste options from the Paste gallery and from the Paste Special dialog box.

In the Paste Special dialog box, you can specify which aspect of the Clipboard contents you want to paste, restricting the pasted data to values, formats, comments, or one of several other options. You can also perform mathematical operations involving the cut or copied data and the existing data in the cells you paste the content into, and you can transpose data—that is, change rows to columns and columns to rows—when you paste it.

To select a cell or cell range

1. Click the first cell you want to select, and then drag to highlight the other cells you want to select.

To select disconnected groups of cells

1. Click the first cell you want to select.

2. Hold down the Ctrl key and select additional cells you want to include in the selection.

To move a cell range

1. Select a cell range.

2. Point to the edge of the selection.

   

   The cursor changes to a four-pointed arrow.

3. Drag the range to its new location.

   Tip

   If you move the cell range to cover cells that already contain values, Excel displays a message box asking if you want to replace the existing data.

To select one or more rows

1. Do any of the following:

   • At the left edge of the worksheet, click the row’s header.

   • Click a row header and drag to select other row headers.

   • Click a row header, press and hold the Ctrl key, and click the headers of other rows you want to copy. The rows do not need to be adjacent to each other.

To select one or more columns

1. Do any of the following:

   • At the top edge of the worksheet, click the column’s header.

   • Click a column header and drag to select other column headers.

   • Click a column header, press and hold the Ctrl key, and click the column headers of other columns you want to copy. The columns do not need to be adjacent to each other.

To copy a cell range

1. Select the cell range you want to copy.

2. On the Home tab of the ribbon, in the Clipboard group, click Copy.

   Or

   Press Ctrl+C.

To cut or move a cell range

1. Select the cell range you want to cut or move.

2. In the Clipboard group, click Cut.

   Or

   Press Ctrl+X.

To paste a cell range

1. Copy or cut a cell range.

2. Click the cell in the upper-left corner of the range where you want the pasted range to appear.

3. In the Clipboard group, click Paste.

   Or

   Press Ctrl+V.

To paste a cell range by using paste options

1. Copy a cell range.

2. Click the cell in the upper-left corner of the range where you want the pasted range to appear.

3. In the Clipboard group, click the Paste arrow (not the button).

4. Click the icon representing the paste operation you want to use.

To display a preview of a cell range you want to paste

1. Copy or cut a cell range.

2. Click the cell in the upper-left corner of the range where you want the pasted range to appear.

3. Click the Paste arrow (not the button).

4. Point to the paste operation for which you want to see a preview.

To paste a cell range by using the Paste Special dialog box controls

1. Copy a cell range.

2. Click the cell in the upper-left corner of the range where you want the pasted range to appear.

3. Click the Paste arrow (not the button), and then click Paste Special (scroll down if necessary).

4. Select the options you want for the paste operation.

5. Click OK.

Find and replace data

Excel worksheets can hold more than one million rows of data. With a large data collection, it’s unlikely you would have the time to move through a worksheet one row at a time to locate the data you want to find.

You can locate specific data in an Excel worksheet by using the Find and Replace dialog box. It contains two tabs—one named Find, the other named Replace—that you can use to search for cells that contain particular values. Using the controls on the Find tab, you can identify cells that contain the data you specify; using the controls on the Replace tab, you can substitute one value for another.

When you need more control over the data that you find and replace—for instance, if you want to find cells in which the entire cell value matches the value you’re searching for—you can expand the Find and Replace dialog box to display more options.

The following table summarizes the elements of the Find and Replace dialog box.

To edit a cell’s contents

1. Do any of the following:

   • Click the cell, enter a new value, and press Enter.

   • Click the cell, edit the value on the formula bar, and press Enter.

   • Double-click the cell, edit the value in the body of the cell, and press Enter.

To edit part of a cell’s contents

1. Click the cell.

2. Edit the part of the cell’s value that you want to change on the formula bar.

3. Press Enter.

Or

1. Double-click the cell.

2. Edit the part of the cell’s value that you want to change in the body of the cell.

3. Press Enter.

To find the next occurrence of a value in a worksheet

1. On the Home tab, in the Editing group, click the Find & Select button to display a menu of choices, and then click Find.

2. In the Find what box, enter the value you want to find.

3. Click Find Next.

To find all instances of a value in a worksheet

1. On the Find & Select menu, click Find.

2. In the Find what box, enter the value you want to find.

3. Click Find All.

To replace a value with another value

1. On the Find & Select menu, click Replace.

2. In the Find what box, enter the value you want to change.

3. In the Replace with box, enter the value you want to replace the value from the Find what box.

4. Click the Replace button to replace the next occurrence of the value.

   Or

   Click the Replace All button to replace all occurrences of the value.

To require find or replace to match an entire cell’s contents

1. On the Find & Select menu, click either Find or Replace.

2. Set your Find what and, if applicable, Replace with values.

3. Click Options.

4. Select the Match entire cell contents check box.

5. Complete the find or replace operation.

To require find or replace to match cell contents, including uppercase and lowercase letters

1. On the Find & Select menu, click either Find or Replace.

2. Set your Find what and, if applicable, Replace with values.

3. Click Options.

4. Select the Match case check box.

5. Complete the find or replace operation.

To find or replace formats

1. On the Find & Select menu, click either Find or Replace.

2. Click Options.

3. Click the Find what row’s Format button, set a format by using the Find Format dialog box, and click OK.

4. If you want to perform a Replace operation, click the Replace with row’s Format button, set a format by using the Find Format dialog box, and click OK.

5. Complete the find or replace operation.

Correct and expand upon data

After you make a change in a workbook, you can usually remove the change as long as you haven’t closed the workbook. You can even change your mind again if you decide you want to restore your change.

After you enter your data, you should check and correct it. You don’t need to verify visually that each piece of numeric data is correct, but you can make sure that your worksheet’s text is spelled correctly by using the Excel spelling checker. When the spelling checker encounters a word it doesn’t recognize, it highlights the word and offers suggestions representing its best guess of the correct word. You can then edit the word directly, pick the proper word from the list of suggestions, or have the spelling checker ignore the misspelling. You can also use the spelling checker to add new words to a custom dictionary so that Excel will recognize them later, saving you time by not requiring you to identify the words as correct every time they occur in your worksheets.

If you’re not sure of your word choice, or if you use a word that is almost but not quite right for your intended meaning, you can check for alternative words by using the thesaurus included with Excel 2019.

Excel 2019 includes a new capability called Smart Lookup, which lets you use the Bing search engine to find information related to a highlighted word. When you use Smart Lookup, Excel displays the Insights task pane, which has two tabs: Explore and Define. The Explore tab displays search results from Wikipedia and other web resources. The Define tab displays definitions provided by Oxford Dictionaries from Oxford University Press.

Finally, if you want to translate a word from one language to another, you can do so by selecting the cell that contains the value you want to translate and clicking the Translate button on the Review tab. The Translator task pane opens and displays tools you can use to select the original and destination languages.

To undo or redo an action

1. Do either of the following:

   • Click the Undo button on the Quick Access Toolbar to undo the action.

   • Click the Redo button on the Quick Access Toolbar to restore the change.

To check spelling in a worksheet

1. On the Review tab, in the Proofing group, click Spelling.

2. For the first misspelled word, do one of the following:

   • Click Change to accept the first suggested replacement for this occurrence of the misspelled word.

   • Click Change All to accept the first suggested replacement for all occurrences of the misspelled word in the worksheet.

   • Click a different word from the Suggestions list to replace the misspelled word and click Change or Change All.

   • Enter the spelling you want in the Not in Dictionary box and click Change or Change All.

   • Click Ignore Once to ignore this occurrence of the word and move to the next misspelled word.

   • Click Ignore All to ignore all occurrences of the word.

3. Repeat step 2 until you have checked spelling for the entire worksheet.

4. Click Close.

To add a word to the main dictionary

1. Click Spelling.

2. When the word you want to add appears in the Not in Dictionary box, click Add to Dictionary.

3. Finish checking spelling and click Close.

To change the dictionary used to check spelling

1. Click Spelling.

2. Click the arrow next to the Dictionary language box, and click the dictionary you want to use.

To look up word alternatives by using the thesaurus

1. Select the cell that contains the word for which you want to find alternatives.

2. On the Review tab, in the Proofing group, click Thesaurus.

3. Use the tools in the Thesaurus task pane to find alternative words.

4. On the title bar of the Thesaurus task pane, click the Close button to close the task pane.

To research a word by using Smart Lookup

1. Select the cell that contains the word you want to research.

2. On the Review tab, in the Insights group, click the Smart Lookup button.

3. On the Explore tab of the Insights task pane, use the resources in the Explore with Wikipedia and other web resources lists.

   Or

   On the Define tab of the task pane, look up definitions of the selected word.

4. On the title bar of the Insights task pane, click the Close button to close the task pane.

To translate a word from one language to another

1. Click the cell that contains the word you want to translate.

2. On the Review tab, in the Language group, click Translate.

3. If necessary, click Yes to send the text over the Internet.

4. Review the results.

5. Click the Close button to close the task pane.

Define Excel tables

With Excel, you’ve always been able to manage lists of data effectively, so that you can sort your worksheet data based on the values in one or more columns, limit the data displayed by using criteria (for example, show only routes with fewer than 100 stops), and create formulas that summarize the values in visible (that is, unfiltered) cells. Excel 2019 provides those capabilities, and more, through Excel tables.

Excel can also create an Excel table from an existing cell range, as long as the range has no blank rows or columns within the data, and there is no extraneous data in cells immediately below or next to the list. If your existing data has formatting applied to it, that formatting remains applied to those cells when you create the Excel table, but you can have Excel replace the existing formatting with the Excel table’s formatting.

Entering values into a cell below or to the right of an Excel table adds a row or column to the table. After you enter the value and move out of the cell, the AutoCorrect Options button appears. If you didn’t mean to include the data in the Excel table, you can click Undo Table AutoExpansion to exclude the cells from the Excel table. If you never want Excel to include adjacent data in an Excel table again, click Stop Automatically Expanding Tables.

You can resize an Excel table manually by using your mouse. If your Excel table’s headers contain a recognizable series of values (such as Region1, Region2, and Region3), and you drag the resize handle to create a fourth column, Excel creates the column with a label that is the next value in the series—in this example, Region4.

Excel tables often contain data you can summarize by calculating a sum or average, or by finding the maximum or minimum value in a column. To summarize one or more columns of data, you can add a Total row to your Excel table.

When you add the Total row, Excel creates a formula that summarizes the values in the rightmost Excel table column. You can change the summary function by picking a new one from the partial list displayed in the Excel table or by selecting a function from the full set.

Much as it does when you create a new worksheet, Excel gives your Excel tables generic names such as Table1 and Table2. You can change an Excel table’s name to something easier to recognize in your formulas. Changing an Excel table name might not seem important, but it helps make formulas that summarize Excel table data much easier to understand. You should make a habit of renaming your Excel tables, so you can recognize the data they contain.

If for any reason you want to convert your Excel table back to a normal range of cells, you can do so quickly.

To create an Excel table

1. Click a cell in the list of data you want to make into an Excel table.

2. On the Home tab, in the Styles group, click Format as Table.

3. In the gallery that appears, click the style you want to apply to the table.

4. In the Format As Table dialog box, verify that the cell range is correct.

5. If necessary, select or clear the My table has headers check box, and then click OK.

To create an Excel table with default formatting

1. Click a cell in the range that you want to make into an Excel table.

2. Press Ctrl+L.

3. In the Format As Table dialog box, verify that the cell range is correct.

4. Click OK.

To add a column or row to an Excel table

1. Click a cell in the row below or the column to the right of the Excel table.

2. Enter the desired data and press Enter.

To expand or contract an Excel table

1. Click any cell in the Excel table.

2. Point to the lower-right corner of the Excel table.

3. When the mouse pointer changes to a diagonal arrow, drag the Excel table’s outline to redefine the table.

To add a Total row to an Excel table

1. Click any cell in the Excel table.

2. On the Design tool tab in the ribbon, in the Table Style Options group, select the Total Row check box.

To change the calculation used in a Total row cell

1. Click any Total row cell that contains a calculation.

2. Click the cell’s arrow.

3. Select a summary function.

   Or

   Click More Functions, use the Insert Function dialog box to create the formula, and click OK.

To rename an Excel table

1. Click any cell in the Excel table.

2. On the Design tool tab, in the Properties group, enter a new name for the Excel table in the Table Name box.

3. Press Enter.

To convert an Excel table to a cell range

1. Click any cell in the Excel table.

2. On the Design tool tab, in the Tools group, click Convert to Range.

3. In the confirmation dialog box that appears, click Yes.

Skills review

In this chapter, you learned how to:

• Enter and revise data

• Manage data by using Flash Fill

• Move data within a workbook

• Find and replace data

• Correct and expand upon data

• Define Excel tables

Practice tasks

The practice files for these tasks are located in the Excel2019SBSCh02 folder. You can save the results of the tasks in the same folder.

Enter and revise data

Open the EnterData workbook in Excel, and then perform the following tasks:

1. Use the fill handle to copy the value from cell B3, Fabrikam, to cells B4:B7.

2. Extend the series of months starting in cell C3 to cell C7, and then use the Auto Fill Options button to copy the cell’s value instead of extending the series.

3. In cell B8, enter the letters Fa and accept the AutoComplete value Fabrikam.

4. In cell C8, enter February.

5. Enter the value Ground in cell D8 by using Pick from Drop-down List.

6. Edit the value in cell E5 to $6,591.30.

Manage data by using Flash Fill

Open the CompleteFlashFill workbook in Excel, and then perform the following tasks:

1. On the Names worksheet, in cell D2, enter Mark Hassall and press Enter.

2. In cell D3, enter J and, when Excel displays a series of names in column D, press Enter to accept the Flash Fill suggestions.

3. Edit the value in cell D3 to include the middle initial found in cell C3, and press Enter.

4. Click the Addresses sheet tab.

5. Select cells F2:F5 and then, on the Home tab, in the Number group, click the arrow next to the Number Format button and click Text.

6. In cell F2, enter 03214 and press Enter.

7. In cell F3, enter 0 and then press Enter to accept the Flash Fill suggestions.

8. Edit the value in cell F4 to read 98012.

Move data within a workbook

Open the MoveData workbook in Excel, and then perform the following tasks:

1. On the Count worksheet, copy the values in cells B2:D2.

2. Display the Sales worksheet, preview what the data would look like if pasted as values only, and paste the contents you just copied into cells B2:D2.

3. On the Sales worksheet, cut column I and paste it into the space currently occupied by column E.

Find and replace data

Open the FindValues workbook in Excel, and then perform the following tasks:

1. On the Time Summary worksheet, find the cell that contains the value 114.

2. On the Time Summary worksheet, find all cells with contents formatted as italic type.

3. Click the Customer Summary sheet tab.

4. Replace all instances of the value Contoso with the value Northwind Traders.

Correct and expand upon data

Open the ResearchItems workbook in Excel, and then perform the following tasks:

1. Check spelling in the file and accept the suggested changes for shipped and within.

2. Ignore the suggestion for TwoDay.

3. Add the word ThreeDay to the main dictionary.

4. Use the thesaurus to find alternate words for the word Overnight in cell B6, then translate the same word to French.

5. Click cell B2 and use Smart Lookup to find more information about the word level.

Define Excel tables

Open the CreateExcelTables workbook in Excel, and then perform the following tasks:

1. Create an Excel table from the list of data on the Sort Times worksheet.

2. Add a row of data to the Excel table for driver D116 and assign a value of 100 sorting minutes.

3. Add a Total row to the Excel table, and then change the summary function to Average.

4. Rename the Excel table to SortTimes.