Chapter 8
IN THIS CHAPTER
Typing and formatting data
Moving around a spreadsheet
Searching a spreadsheet
Editing a spreadsheet
Printing
Everyone needs to perform simple math. Businesses need to keep track of sales and profits, and individuals need to keep track of budgets. In the old days, people not only had to write down numbers on paper, but they also had to do all their calculations by hand (or with the aid of a calculator).
That’s why people use Excel. Instead of writing numbers on paper, they can type numbers on the computer. Instead of adding or subtracting columns or rows of numbers by hand, they can let Excel do it faster. By using Excel, you can focus on typing in the correct numbers and let Excel worry about calculating accurate results quickly.
Excel organizes numbers in rows and columns. An entire page of rows and columns is called a spreadsheet or a worksheet. (A collection of one or more worksheets is stored in a file called a workbook.) Each row is identified by a number such as 1 or 249; and each column is identified by letters, such as A, G, or BF. The intersection of each row and column defines rectangular spaces called cells, each of which contains one of three items:
Numbers provide the data, and formulas calculate that data to produce a useful result, such as adding sales results for the week. Of course, just displaying numbers on the screen may be confusing if you don’t know what those numbers mean, so labels simply identify what numbers represent. Figure 8-1 shows the parts of a typical spreadsheet.
The strength of spreadsheets comes by playing “What-if?” games with your data, such as “What if I gave myself a $20-per-hour raise and cut everyone else’s salary by 25%? How much money would that save the company every month?” Because spreadsheets can rapidly calculate new results, you can experiment with different numbers to see how they create different answers.
Every cell can contain a number, a label, or a formula. To type anything into a spreadsheet, you must first select or click in the cell (or cells) and then type a number or text.
To type data in a single cell, follow these steps:
Type a number (such as 34.29 or 198), a label (such as Tax Returns), or a formula.
You can see how to create formulas in Chapter 9.
After you type data in a cell, you can press one of the following four keystrokes to select a different cell:
If you type data in cell A1 and press Enter, Excel selects the next cell below, which is A2. If you type data in A2 and press Tab, Excel selects the cell to the right, which is B2.
However, what if you want to type data in a cell such as A1 and then have Excel select the next cell to the right (B1)? Or what if you want to type data in cells A1 and A2 but then jump back to type additional data in cells B1 and B2?
To make this easy, Excel lets you select a range of cells, which essentially tells Excel, “See all the cells I just highlighted? I only want to type data in those cells.” After you select multiple cells, you can type data and press Enter. Excel selects the next cell down in that same column. When Excel reaches the last cell in the column, it selects the top cell of the column to the right.
To select multiple cells for typing data in, follow these steps:
Highlight multiple cells by choosing one of the following:
Excel selects the cell that appears in the upper-left corner of your selected cells.
Press Enter.
Excel selects the cell directly below the preceding cell. If the preceding cell appeared at the bottom of the selected column, Excel highlights the top cell in the column that appears to the right.
You can also move backward by pressing Shift+Enter instead.
If you need to type the names of successive months or days in a row or column (such as January, February, March, and so on), Excel offers a shortcut to save you from typing all the day or month names yourself. With this shortcut, you just type one month or day and then drag the mouse to highlight all the adjacent cells. Then Excel types the rest of the month or day names in those cells automatically.
To use this shortcut, follow these steps:
Click a cell and type a month (such as January or just Jan) or a day (such as Monday or just Mon).
The Fill Handle, a box, appears in the bottom-right corner of the cell.
You can also type in a sequence of numbers in Step 1. So if you typed the numbers 2, 4, and 6 in adjacent cells, highlighted all these adjacent cells, and grabbed the Fill Handle, Excel is smart enough to detect the pattern and display the numbers 8, 10, and 12 in the next three adjacent cells.
Hold down the left mouse button and drag (move) the mouse down a column or across the row.
As you drag the mouse, Excel displays the remaining month or day names that it will add to the cells, as shown in Figure 8-2.
When you first create a spreadsheet, numbers and labels appear as plain text. Plain labels may look boring, but plain numbers (such as 8495 or 0.39) can be difficult to read and understand if the numbers are supposed to represent currency amounts ($8,495) or percentages (39%).
To make labels visually interesting and numbers appear more descriptive of what they actually represent, you need to format your data after you type it into a spreadsheet.
To format the appearance of numbers, follow these steps:
Select one or more cells by using the mouse or keyboard.
To select multiple cells, drag the mouse or hold the Shift key while pressing the arrow keys.
Click the Number Format list box in the Number group.
A pull-down menu appears, as shown in Figure 8-3.
The Number group also displays three icons that let you format numbers as Currency, Percentage, or with Commas in one click, as shown in Figure 8-4. If you click the downward-pointing arrow to the right of the Accounting Number Format icon, you can choose different currency symbols to use, such as $, £, or €.
Click a number format style, such as Percentage or Scientific.
Excel displays your numbers in your chosen format.
Because many people use spreadsheets for business, they often want negative numbers to appear highlighted so they can see them easier. Excel can display negative numbers in parentheses (–23) or in red so you can’t miss them.
To define how negative numbers appear in your spreadsheet, follow these steps:
Click the Format icon in the Cells group.
A menu appears, as shown in Figure 8-5.
Choose Format Cells.
The Format Cells dialog box appears, as shown in Figure 8-6.
Choose Currency or Number from the Category list.
You can choose how to format negative numbers only if you format your numbers by using the Currency or Number category.
Click a negative number format and then click OK.
If any of your numbers become negative in the cell or cells you select in Step 1, Excel automatically displays those negative numbers in the negative number format you choose.
If you format cells to display numbers with decimal places, such as 23.09 or 23.09185, you can modify how many decimal places appear. To define the number of decimal places, follow these steps:
Click in the Number Format list box (refer to Figure 8-3) and choose a format that displays decimal places, such as Number or Percentage.
Excel formats the numbers in your chosen cells.
To make your data look prettier, Excel can format the appearance of cells to change the font, background color, text color, or font size used to display data in a cell.
Excel provides two ways to format cells: You can use Excel’s built-in formatting styles, or you can apply different types of formatting individually. Some of the individual formatting styles you can choose include
Excel provides a variety of predesigned formatting styles that you can apply to one or more cells. To format cells with a built-in style, follow these steps:
Click the Cell Styles icon in the Styles group.
A pull-down menu appears listing all the different styles you can choose, as shown in Figure 8-8.
Move the mouse pointer over a style.
Excel displays a Live Preview of how your selected cells will look with that particular style.
Click the style you want.
Excel applies your chosen style to the selected cells.
Different fonts can emphasize parts of your spreadsheet, such as using one font to label columns and rows and another font or font size to display the actual data. Text styles (bold, underline, and italic) can also emphasize data that appears in the same font or font size.
To change the font, font size, and text style of one or more cells, follow these steps:
Click the Font list box.
A pull-down menu of different fonts appears.
Each cell displays data in a Font color and a Fill color. The Font color defines the color of the numbers and letters that appear inside a cell. (The default Font color is black.) The Fill color defines the color that fills the background of the cell. (The default Fill color is white.)
To change the Font and Fill colors of cells, follow these steps:
Click the downward-pointing arrow that appears to the right of the Font Color icon (the icon looks like a capital A).
A color palette appears, as shown in Figure 8-9.
Click the color you want to use for your text.
The color you select appears directly on the Font Color icon. The next time you want to apply this same color to a cell, you can click the Font Color icon directly instead of the downward-pointing arrow to the right of the Font Color icon.
Click the downward-pointing arrow that appears to the right of the Fill Color icon (the icon looks like a pouring bucket).
A color palette appears.
Click a color to use to fill the background of your cell.
The color you select appears directly on the Fill Color icon. The next time you want to apply this same color to a cell, you can click the Fill Color icon directly instead of the downward-pointing arrow to the right of the Fill Color icon.
For another way to highlight one or more cells, you can add borders. Borders can surround the entire cell or just the top, bottom, left, or right side of a cell. To add borders to a cell, follow these steps:
Click the downward-pointing arrow to the right of the Border icon in the Font group.
A pull-down menu appears, as shown in Figure 8-10.
Click a border style.
Excel displays your chosen borders around the cells you selected in Step 1.
If you have a large spreadsheet, chances are good that some information may be hidden by the limitations of your computer screen. To help you view and select cells in different parts of your spreadsheet, Excel offers various ways to navigate a spreadsheet by using the mouse and keyboard.
To navigate a spreadsheet with the mouse, you can click the onscreen scroll bars or use the scroll wheel on your mouse (if it has one). To use the scroll bars, you have three choices:
Click the up/down or right/left arrows on the horizontal or vertical scroll bars.
This moves the spreadsheet one row (up or down) or column (right or left) at a time.
Click the scroll area (any area to the left/right or above/below the scroll box on the scroll bar).
This moves the spreadsheet one screen left/right or up/down.
If your mouse has a scroll wheel, you can use this wheel to move through a spreadsheet by two methods:
Using the mouse can be a faster way to jump from one place in a spreadsheet to another, but sometimes trying to line up the mouse just right can be frustrating. For that reason, you can also use the keyboard to move around a spreadsheet. Some of the common ways to move around a spreadsheet are shown in Table 8-1.
TABLE 8-1: Using the Keyboard to Navigate a Spreadsheet
Pressing This |
Does This |
Up arrow (↑) |
Moves up one row |
Down arrow (↓) |
Moves down one row |
Left arrow (←) |
Moves left one column |
Right arrow (→) |
Moves right one column |
Ctrl+↑ |
Jumps up to the top of a column that contains data |
Ctrl+↓ |
Jumps down to the bottom of a column that contains data |
Ctrl+← |
Jumps to the left of a row that contains data |
Ctrl+→ |
Jumps to the right of a row that contains data |
Page Up |
Moves up one screen |
Page Down |
Moves down one screen |
Ctrl+Page Up |
Displays the previous worksheet |
Ctrl+Page Down |
Displays the next worksheet |
Home |
Moves to the A column of the current row |
Ctrl+Home |
Moves to the A1 cell |
Ctrl+End |
Moves to the bottom-right cell of your spreadsheet |
If you know the specific cell you want to move to, you can jump to that cell by using the Go To command. To use the Go To command, follow these steps:
Click the Find & Select icon in the Editing group.
A pull-down menu appears.
Click Go To.
The Go To dialog box appears, as shown in Figure 8-11.
You can also choose the Go To command by pressing Ctrl+G.
Click in the Reference text box and type the cell you want to move to, such as C13 or F4.
If you’ve used the Go To command before, Excel lists the last cell references you typed. Now you can just click one of those cell references to jump to that cell.
Click OK.
Excel highlights the cell you typed in Step 4.
One problem with the Go To command is that most people won’t know which cell contains the data they want to find. For example, if you want to view the cell that contains the total amount of money you owe for your income taxes, you probably don’t want to memorize that this cell is G68 or P92.
To help you identify certain cells, Excel lets you give them descriptive names. To name a cell or range of cells, follow these steps:
After you name a cell, you can jump to it quickly by following these steps:
Click the downward-pointing arrow to the right of the Name box.
A list of named cells appears.
Click the named cell you want to view.
Excel displays your chosen cell.
Eventually, you may want to edit or delete a name for your cells. To delete or edit a name, follow these steps:
Click the Name Manager icon.
The Name Manager dialog box appears, as shown in Figure 8-13.
Rather than search for a specific cell, you may want to search for a particular label or number in a spreadsheet. Excel lets you search for the following:
You can search for a specific label or number anywhere in your spreadsheet. To search for text or numbers, follow these steps:
Click the Find & Select icon in the Editing group.
A pull-down menu appears.
Click Find.
The Find and Replace dialog box appears, as shown in Figure 8-14.
If you click the Replace tab, you can define the text or number to find and new text or numbers to replace it.
Click in the Find What text box and type the text or number you want to find.
If you click the Options button, the Find and Replace dialog box expands to provide additional options for searching, such as searching in the displayed sheet or the entire workbook.
Formulas appear just like numbers; to help you find which cells contain formulas, Excel gives you two choices:
To display (or hide) formulas in a spreadsheet, you have two options:
Figure 8-16 shows what a spreadsheet looks like when formulas appear inside of cells.
To highlight all cells that contain formulas, follow these steps:
Click the Find & Select icon in the Editing group.
A pull-down menu appears.
Click Formulas.
Excel highlights all the cells that contain formulas.
The two ways to edit a spreadsheet are
To edit data in a single cell, follow these steps:
Double-click the cell that contains the data you want to edit.
Excel displays a cursor in your selected cell.
Using the mouse can be a quick way to modify the sizes of rows and columns. To change the height of a row or the width of a column, follow these steps:
Move the mouse pointer over the bottom line of a row heading, such as the 2 or 18 heading. (Or move the mouse pointer over the right line of the column heading, as for column A or D.)
The mouse pointer turns into a two-way pointing arrow.
Hold down the left mouse button and drag (move) the mouse.
Excel resizes your row or column.
If you need to resize a row or column to a precise value, it’s easier to type a specific value into the Row Height or Column Width dialog box instead. To type a value into a Row Height or Column Width dialog box, follow these steps:
Click the row or column heading that you want to resize.
Excel highlights your entire row or column.
Click the Format icon that appears in the Cells group.
A pull-down menu appears, as shown in Figure 8-17.
Click Row Height (if you selected a row) or Column Width (if you selected a column).
The Row Height or Column Width dialog box appears.
Type a value and then click OK.
Excel resizes your row or column.
After you type labels, numbers, and formulas, you may suddenly realize that you need to add or delete extra rows or columns. To add a row or column, follow these steps:
Click the row or column heading where you want to add another row or column.
Excel highlights the entire row or column.
Click the downward-pointing arrow of the Insert icon in the Cells group.
A menu appears.
Choose Insert Sheet Rows or Insert Sheet Columns.
Excel inserts a new row above the selected row or inserts a column to the left of the selected column.
To delete a row or column, follow these steps:
Click the downward-pointing arrow of the Delete icon in the Cells group.
A pull-down menu appears.
For greater flexibility, Excel lets you create individual spreadsheets that you can save in a single workbook (file). When you load Excel, it automatically provides you with a sheet, but you can add more if you need them.
To add a new sheet, choose one of the following:
By default, Excel gives each sheet a generic name such as Sheet1. To give your sheets a more descriptive name, follow these steps:
Double-click the sheet tab that you want to rename.
Excel highlights the entire sheet name.
Type a new name for your sheet and press Enter when you’re done.
Your new name appears on the sheet tab.
You can rearrange the order that your sheets appear in your workbook. To rearrange a sheet, follow these steps:
Hold down the left mouse button and drag (move) the mouse.
The downward-pointing black arrow points where Excel will place your sheet.
Using multiple sheets may be handy, but you may want to delete a sheet if you don’t need it.
To delete a sheet, follow these steps:
Choose one of the following:
If your sheet is empty, Excel deletes the sheet right away. If your sheet contains data, a dialog box appears to warn you that you’ll lose any data stored on that sheet.
Click Delete.
Excel deletes your sheet along with any data on it.
After you create a spreadsheet, you may need to delete data, formulas, or just the formatting that defines the appearance of your data. To clear out one or more cells of data, formatting, or both data and formatting, follow these steps:
Click the Clear icon in the Editing group.
A pull-down menu appears, as shown in Figure 8-19.
After you create a spreadsheet, you can print it out for others to see. When printing spreadsheets, you need to take special care how your spreadsheet appears on a page because a large spreadsheet will likely get printed on two or more sheets of paper.
This can cause problems if an entire spreadsheet prints on a one page but a single row of numbers appears on a second page, which can make reading and understanding your spreadsheet data confusing. When printing spreadsheets, take time to align your data so that it prints correctly on every page.
Excel can display your spreadsheets in two ways: Normal view and Page Layout view. Normal view is the default appearance, which simply fills your screen with rows and columns so you can see as much of your spreadsheet as possible.
Page Layout view displays your spreadsheet exactly as it will appear if you print it. Not only can you see where your page breaks occur, but you can also add any headers to the top of your spreadsheet.
To switch back and forth from Normal view to Page Layout view, follow these steps:
Headers and footers are useful when printing out your spreadsheet. A header may explain the information in the spreadsheet, such as 2014 Tax Return Information, and a footer may display page numbers. To create a header or footer, follow these steps:
Click the Text icon.
A menu appears underneath the Text icon, as shown in Figure 8-21.
Click the Header & Footer icon.
Excel displays the Design tab and creates a text box for your header and footer, as shown in Figure 8-22.
Click the Go To Footer icon in the Navigation group.
Excel displays the footer text box.
Gridlines appear on the screen to help you align data in rows and columns. However, when you print your worksheet, you can choose to omit gridlines or print them to make your data easier to understand.
To print gridlines and/or row and column headings, follow these steps:
Sometimes you may not want to print your entire spreadsheet but just a certain part of it, called the print area. To define the print area, follow these steps:
Click the Print Area icon in the Page Setup group.
A pull-down menu appears, as shown in Figure 8-23.
Choose Set Print Area.
Excel displays a line around your print area.
Click the File tab and then click Print.
A print preview image of your chosen print area appears.
Click the Print Area icon in the Page Setup group.
A pull-down menu appears.
Choose Add to Print Area.
Excel displays a line around your newly defined print area.
After you define the print area, you can always remove it by following these steps:
Click Print Area.
A pull-down menu appears (refer to Figure 8-23).
One problem with large spreadsheets is that when you print them out, parts may get cut off when printed on separate pages. To correct this problem, you can tell Excel exactly where page breaks should occur.
To insert page breaks, follow these steps:
Click the Breaks icon in the Page Setup group.
A pull-down menu appears, as shown in Figure 8-24.
Choose Insert Page Break.
Excel inserts a horizontal page directly above the cell you selected in Step 1, as well as a vertical page break to the left of that cell.
To remove a page break, follow these steps:
Click the Breaks icon in the Page Setup group.
A pull-down menu appears (refer to Figure 8-24).
Choose Remove Page Break.
Excel removes your chosen page break.
If you have a large spreadsheet that fills two or more pages, Excel may print your spreadsheet data on separate pages. Although the first page may print your labels to identify what each row and column may represent, any additional pages that Excel prints won’t bear those same identifying labels. As a result, you may wind up printing rows and columns of numbers without any labels that identify what those numbers mean.
To fix this problem, you can define labels to print on every page by following these steps:
Click the Print Titles icon in the Page Setup group.
The Page Setup dialog box appears, as shown in Figure 8-25.
Click the Collapse/Expand button that appears to the far right of the Rows to Repeat at Top text box.
The Page Setup dialog box shrinks.
Click the Collapse/Expand button again.
The Page Setup dialog box reappears.
Click the Collapse/Expand button that appears to the far right of the Columns to Repeat at Left text box.
The Page Setup dialog box shrinks.
Click the Collapse/Expand button again.
The Page Setup dialog box reappears.
To help you squeeze or expand your spreadsheet to fill a printed page, you can define different margins for each printed page. To define margins, follow these steps:
Click the Margins icon in the Page Setup group.
A pull-down menu appears, as shown in Figure 8-26.
Paper orientation can be either landscape (the paper width is greater than its height) or portrait mode (the paper width is less than its height). Paper size defines the physical dimensions of the page.
To change the paper orientation and size, follow these steps:
Click the Orientation icon in the Page Setup group.
A pull-down menu appears.
Click the Size icon in the Page Setup group.
A pull-down menu appears, as shown in Figure 8-27.
When you finish defining how to print your spreadsheet, you’ll probably want to print it eventually. To print a worksheet, follow these steps:
Click Print.
The Print Preview appears in the right pane.
13.58.161.216