Chapter 2: Managing Workbooks and Worksheets

Introduction to Excel taught you to work inside a worksheet. This chapter covers the MO-210 skills for working with a workbook: bringing data in from outside, finding your way around a large file, and controlling what Excel shows on screen. None of these skills is hard, but exam tasks describe them in terms of their results, so you need to know each one well enough to recognize it from a description.

2.1 Importing data

Data often starts outside Excel, in a text file exported from another program or in a table on a web page. Excel's Get Data tools (also called Power Query) bring that data into a worksheet as a table.

2.1.1 Importing a text or CSV file

A delimited text file stores a table as plain text. Each line is a row, and a character called a delimiter separates the columns. In a CSV (comma-separated values) file the delimiter is a comma. Other text files may use tabs, semicolons or another character.

VendorID,Vendor,City,Rating
V-101,Summit Gear Co.,Denver,4.6
V-102,Blue Ridge Textiles,Asheville,4.1
V-103,Cascade Optics,Portland,4.8

To import a text or CSV file:

1. Click the cell where you want the data to start, or start in any cell if you'll load it to a new sheet.
2. Select Data › Get Data › From File › From Text/CSV (or Data › From Text/CSV in the Get & Transform Data group).
3. Find the file and click Import.
4. Excel shows a preview. Check the settings above the preview:
   • File Origin is the file's character encoding. Change it only if accented letters or symbols look wrong.
   • Delimiter is the character that separates columns. Excel usually guesses correctly. If the preview shows everything in one column, choose the right delimiter.
   • Data Type Detection controls how Excel decides whether a column holds numbers, dates or text.
5. Choose how to load the data:
   • Load puts the data in a new worksheet as an Excel table.
   • Load To opens the Import Data dialog box, where you can choose a table, a PivotTable or just a connection, and choose Existing worksheet with a starting cell.
   • Transform Data opens the Power Query Editor, where you can clean the data before loading it. The exam doesn't require the editor.

The imported table stays connected to the file. When the file changes, Data › Refresh All reloads it. To see or change the connection, open Data › Queries & Connections.

2.1.2 Importing data from an online source

Excel can also import a table from a web page:

1. Select Data › From Web.
2. Type or paste the page's address (URL) and click OK. If Excel asks how to connect, choose Anonymous for a public page and click Connect.
3. The Navigator lists every table Excel found on the page. Click one to preview it.
4. Select the table you want and click Load (or Load To to choose the location).

The data arrives as an Excel table connected to the web page, so Refresh All updates it with the page's current values.

2.2 Navigating a workbook

2.2.1 Finding data

Find (Home › Find & Select › Find, or Ctrl+F) searches for text or numbers. Click Options to see its settings:

Find Next jumps to one match at a time. Find All lists every match, with its sheet, cell and value. Click a result to go to that cell.

The Replace tab (Ctrl+H) works the same way, with a box for replacement text.

2.2.2 Going to named cells, ranges and other elements

A workbook can name cells and ranges (chapter 4 shows you how) and contain tables, charts and other objects. There are several ways to jump to them:

• The Name Box, to the left of the formula bar, lists every named range and table. Click its arrow and choose a name, and Excel selects that range. You can also type a cell address such as Z200 in the Name Box and press Enter.
• Go To (Home › Find & Select › Go To, F5 or Ctrl+G) lists the same names. Choose one, or type a reference, and click OK.
• Go To Special, from the Go To dialog box or Find & Select, selects cells of a particular kind, such as blanks, formulas, constants, comments or the last used cell.
• The Navigation pane (View › Navigation) lists every worksheet in the workbook, with the named ranges, tables, charts and pictures on each. Click an item to go to it.

2.2.3 Hyperlinks

A hyperlink is clickable text in a cell that opens a web page, a file, an email message or another place in the workbook. To insert one, select the cell and choose Insert › Link (Ctrl+K). The Insert Hyperlink dialog box has four kinds of link along its left side:

• Existing File or Web Page links to a file on your computer or a web address. Type the URL in Address.
• Place in This Document links to a cell on a worksheet or to a defined name. Choose the sheet, then type the cell reference.
• Create New Document makes a new file and links to it.
• E-mail Address opens a new message to that address, with an optional subject.

Two other boxes matter on the exam:

• Text to display is what the cell shows. A task such as "display the text Vendor list" means this box.
• ScreenTip is the note that appears when someone points at the link.

To change a link, right-click it and choose Edit Link. To remove one, right-click it and choose Remove Link. That keeps the text but removes the link. To remove links from a whole range, select the range and choose Home › Clear › Remove Hyperlinks.

2.3 Customizing the Quick Access Toolbar

The Quick Access Toolbar is a small row of command buttons above or below the ribbon. It's always visible, whichever ribbon tab is open. If you don't see it, right-click the ribbon and choose Show Quick Access Toolbar.

You can change the toolbar in three ways:

• Add a command from the ribbon. Right-click any ribbon button and choose Add to Quick Access Toolbar.
• Use the toolbar's menu. Click the Customize Quick Access Toolbar arrow at the end of the toolbar and check a common command, such as Quick Print or Sort Ascending, or choose Show Below the Ribbon or Show Above the Ribbon to move the toolbar.
• Use Excel Options. Choose More Commands from the toolbar's menu (or File › Options › Quick Access Toolbar). Pick a command from the list on the left and click Add. Use the up and down arrows to change the order, Remove to take a command off, and Reset to restore the defaults.

To remove a command quickly, right-click it on the toolbar and choose Remove from Quick Access Toolbar.

2.4 Views and windows

2.4.1 Worksheet views

The View tab's Workbook Views group, and the three buttons at the right end of the status bar, switch between three views:

• Normal is the view you usually work in.
• Page Layout shows each printed page with its margins, headers and footers. You can type headers and footers directly in this view.
• Page Break Preview shows where pages will break when you print. Blue lines mark the breaks: dashed lines are breaks Excel chose and solid lines are ones you set. Drag a line to move a break. Areas outside the print area are gray.

To add a page break yourself, select the row below (or column to the right of) where the break should go and choose Page Layout › Breaks › Insert Page Break.

2.4.2 Freezing rows and columns

Freezing keeps rows or columns in view while you scroll, so headings stay on screen. Choose View › Freeze Panes and one of these options:

• Freeze Top Row keeps row 1 visible.
• Freeze First Column keeps column A visible.
• Freeze Panes freezes every row above and every column to the left of the active cell. To freeze rows 1–3 and column A, select cell B4 first. To freeze only rows, select a cell in column A (A4 freezes rows 1–3).

To undo freezing, choose View › Freeze Panes › Unfreeze Panes.

2.4.3 Window views

The View tab's Window group changes how workbook windows are arranged:

• New Window opens a second window on the same workbook, so you can see two parts of it at once. The title bar shows :1 and :2 after the workbook's name. Edits in either window change the same file.
• Arrange All places all open windows side by side. Choose Tiled, Horizontal, Vertical or Cascade. Check Windows of active workbook to arrange only the windows of the current workbook.
• View Side by Side compares two windows. Synchronous Scrolling scrolls them together, and Reset Window Position makes them the same size again.
• Split divides the window into two or four panes above and to the left of the active cell. Each pane scrolls on its own. Drag a split bar to move it, or click Split again to remove it.
• Hide and Unhide hide a whole workbook window without closing it.
• Switch Windows lists all open windows so you can jump to one.

2.5 Workbook properties and showing formulas

2.5.1 Built-in properties

Every workbook has properties: information about the file such as its title, author, subject, tags and categories. To change them, choose File › Info. The Properties list on the right shows the common ones. Click a value, such as Add a title or Add a tag, and type. Click Show All Properties to see the rest, such as Subject, Company and Manager.

You can also choose Properties › Advanced Properties at the top of the list. The Summary tab of that dialog box has boxes for title, subject, author, manager, company, category, keywords (tags) and comments.

2.5.2 Displaying formulas

Normally a cell shows the result of its formula. To show the formulas themselves in every cell, choose Formulas › Show Formulas or press Ctrl+` (the grave accent key, to the left of 1). Columns widen to fit the formulas. Choose the command again to switch back. Show Formulas applies only to the current worksheet.

2.6 Practice

VendorID,Vendor,City,Rating
V-101,Summit Gear Co.,Denver,4.6
V-102,Blue Ridge Textiles,Asheville,4.1
V-103,Cascade Optics,Portland,4.8
V-104,Prairie Packs,Omaha,3.9
V-105,Harbor Footwear,Savannah,4.4