Download Wiley Microsoft Office Excel 2007 Data Analysis
Transcript
Introduction to Data Analysis with Excel You can also use Excel to create charts. A chart is a graphical representation of your data. When using Excel, Introduction to Data Analysis with Excel AL RI D TE Click the Office button. GH A menu appears. Click New. CO PY RI 2 Excel provides a way for you to create and maintain lists. A list is a series of rows and columns. Each column has a label — for example, name, address, telephone number. Each row under a column has information pertaining to the column label. You can sort, filter, and analyze your lists in Excel. 1 2 OPEN A NEW WORKBOOK 1 A PivotTable is an interactive worksheet table you can use to analyze data. A PivotTable gives you an easy way to summarize and view large amounts of data. Using a PivotTable, you can rotate rows and columns of data so you can see different views of your data easily. You can use Excel to create PivotTables. TE Each Excel file is a workbook. Each workbook can have multiple worksheets. Worksheets are made up of rows and columns of cells you use to enter information. One of the many useful features of Excel is the ability to calculate. When you enter a formula into Excel, Excel can automatically calculate the result, and when you make changes to your worksheet, Excel can automatically recalculate. you can choose from several types of charts, including Column, Line, Pie, Bar, Area, and Scatter. Charts can make your data easier to read, easier to understand, and easier to compare. MA T his book is about using Microsoft Excel to analyze your data. Microsoft Excel is an electronic worksheet you can use to maintain lists; perform mathematical, financial, and statistical calculations; create charts; analyze your data with a PivotTable; and much more. Excel can help you locate data, find trends in your data, and present your data to others. The New Workbook dialog box appears. 3 Double-click Blank Workbook. Excel opens a new workbook. 2 3 Excel renders windows based on the size of your screen, the resolution to which your screen is set, and the other screen display options. W hen you open an Excel workbook, Excel presents the Excel window. Your window should be similar to the one in the illustration. It may not be quite the same because B A C D N L K M Chapter 1: Getting Started Understanding the Excel Window R O P E Q F H G I J A OFFICE BUTTON Click the Office button to open, save, print, prepare, send, publish, and close files. B QUICK ACCESS TOOLBAR Place commands you use often on the Quick Access toolbar. C TABS Click a tab to view Ribbon options. D RIBBON Click the buttons in the Ribbon to execute Excel commands. E CELL Enter data into cells. F SHEETS Each workbook has multiple sheets. You can enter data into each sheet. G INSERT SHEET Click to insert a new sheet. H ZOOM Drag to adjust the magnification of your worksheet. I STATUS BAR Right-click to adjust what appears on the status bar. J RECORD MACRO BUTTON Click to begin recording a macro. K SELECT ALL BUTTON Click to select everything in your worksheet. L NAME BOX Displays the name of the active cell. M DROP-DOWN LIST Displays a list of defined range names. N FORMULA BAR Use the formula bar to enter and edit data. O INSERT FUNCTION Opens the Insert Function dialog box. P ENTER BUTTON Click to accept a cell entry. Q CANCEL BUTTON Click to cancel a cell entry. R EXPAND FORMULA BAR Click to make the formula bar larger. 3 Enter Data orksheets divide information into rows and columns of data. People often use worksheets to calculate financial, statistical, or engineering data. Microsoft Excel is an electronic worksheet. You can use it to enter, display, manipulate, analyze, and print the information you organize into rows and columns. W To move to a cell, move your mouse pointer to the cell and then click. The cell becomes the active cell and Excel surrounds it with a black border. Once in a cell, you can use the arrow keys on your keyboard to move up, down, left, and right. You can enter text, numbers, dates, and formulas into cells. Each Excel 2007 worksheet has more than 1 million rows and more than 16,000 columns. Excel labels each row in numerical order, starting with 1. Excel labels each column in alphabetical order, starting with A. When Excel reaches the letter Z, it begins ordering with AA, AB, AC, and so on. You refer to the intersection of a row and column as a cell. The intersection of a cell also forms the cell name. For example, you refer to the first row in column A as cell A1 and the seventh row in column C as cell C7. When using Excel, you enter your data into worksheet cells. Alphabetic characters and numerical data you do not use in mathematical calculations are text. Any sequence of characters that contains a letter, Excel considers text. By default, Excel considers all numerical data numbers. If you wish to enter numerical data as text, precede your entry with an apostrophe. As you type, the data you enter into a cell appears on the formula bar. You can press the check mark on the formula bar or you can press the Enter key to enter your data into a cell. Enter Data ENTER TEXT 1 Move to the cell in which you want to enter text. 2 Type the text you want to enter. 3 Press Enter. 1 2 Excel enters the text into the cell and then moves down to the next cell. • Alternatively, you can click the check mark on the formula bar to enter data. ENTER NUMBERS 1 Move to the cell in which you want to enter a number. 2 Type the number you want to enter. 3 Press Enter. Excel enters the number into the cell and then moves down to the next cell. • 4 You can also click the check mark on the formula bar to enter data. 1 2 1 2 3 3 Move to the cell in which you want to enter a number as text. 2 1 Type an apostrophe followed by the number you want to enter. Click the check mark. Excel enters the number into the cell. Alternatively, press Enter. 3 If you receive an error, click the Error button ( ) and then click Ignore Error. 1 ENTER DATES 1 2 3 Chapter 1: Getting Started ENTER NUMBERS AS TEXT 2 Move to the cell in which you want to enter a date. Type the date you want to enter. Click the check mark. Excel enters the date into the cell. Alternatively, press Enter. When you enter numbers as text, an Error button ( ) may appear. Excel is checking to see if you entered the number as text by mistake. You should click the button and then click Ignore Error. When you press Enter after typing an entry into a cell, by default Excel moves down one cell. If you want Excel to move to the cell to the right, press the right-arrow key or the Tab key. If you want Excel to move up, press the up-arrow key. If you want Excel to move to the left, press Shift+Tab or the left-arrow key. By default, when you press the Enter key after typing an entry, Excel moves down one cell. You can change the default location to which Excel moves. Click the Office button. A menu appears. Click Excel Options in the lower-right corner. The Excel Options dialog box appears. Click Advanced. Make sure the After Pressing Enter check box is selected and then choose Right, Up, or Left in the Direction field to cause Excel to move right, up, or left when you press Enter. 5 Format Numbers F ormatting makes your data easier to read and helps you conform to company, country, or industry standards for formatting. Excel provides a variety of options for formatting numbers, dates, and times. By applying formatting, you change the way a number, date, or time appears. For example, you can use Excel’s formatting options to tell Excel you want to separate the month, day, and year of a date with slashes. The Number group on the Home tab has several buttons you can use to format numbers quickly. Click the down arrow next to the Accounting Number Format button to choose to apply a United States currency format, a United Kingdom currency format, a Euro format, or another currency format. Use the Percent Style button to display the value in a cell as a percent. Use the Comma Style button to display the value in a cell with a thousands separator. Use the Increase Decimal and Decrease Decimal buttons to increase and decrease the number of decimal places. The Number Format box is located on the Home tab in the Number group. You can use it to format numbers quickly. Just click the down arrow to display a menu of options. Then click a format option to apply it to a cell or cell range. Click the More Number Formats option to open the Format Cells dialog box. You can also click the launcher in the Number group to open the Format Cells dialog box. The Format Cells dialog box has four categories you can use to format numbers: General, Number, Currency, and Accounting. The General format is the default format. It displays numbers exactly the way you type them. Format Numbers GENERAL NUMBER FORMAT 1 3 4 Type numbers. The numbers appear in the format you type them. 2 3 4 Click and drag to select the cells you want to format. Click the Home tab. Click the Comma Style button in the Number group. • Excel separates the thousands in the numbers. • • Excel adds two decimal places. • Zeros are represented by a dash. 6 Negative numbers appear in parentheses. 1 2 1 2 • 3 • Select the numbers to format. 2 Excel adds a dollar sign, aligned with the left side of the cell. Excel reserves space for a right parenthesis for negative values. 2 3 4 5 6 7 • 1 Click the Decrease Decimal Place button. Each click removes a decimal place. 2 If you click the Increase Decimal Place button, each click adds a decimal place. 1 CURRENCY FORMAT 1 3 Click the Accounting Number Format button in the Number group. Chapter 1: Getting Started ACCOUNTING NUMBER FORMAT Select the numbers to format. Click the Number group’s launcher. 3 4 In the Number tab of the Format Cells dialog box, click Currency. 5 6 Click here and set the number of decimal places. Click here and select a currency symbol. Click to select a number format for negative numbers. 7 Click OK. Excel formats your numbers. Changing a number format can increase the contents of the cell. If your number is too long to fit in its cell, Excel fills the cell with pound signs (#####). To view the number, double-click the line at the top of the column that separates columns, or click and drag the line to make the cell wider. You can use the Text format in the Format Cells dialog box to convert a number to text. Numbers formatted as text are not used in mathematical calculations. Certain numbers — for example, employee numbers — are never used in mathematical calculations and should be formatted as text. If you want to format a number as text as you type it, precede the number with an apostrophe (’). If you right-click in any cell that contains a number, you can choose number formatting options from the mini-toolbar or you can click Format Cells from the context menu to open the Format Cells dialog box. continued ➔ 7 Format Numbers (continued) W hen using the Format Cells dialog box, you can use the Number format option to apply special formats to your numbers. You can set the number of decimal places, specify whether your number should display a thousands separator, and determine how to display negative numbers. You can choose from four formats for negative numbers: preceded by a negative sign (–), in red, in parentheses, or in red and parentheses. The Currency format offers you the same options as the Number format except you can choose to display a currency symbol. The currency symbol you choose determines the options you have for displaying negative numbers. If you choose the dollar sign ($), thousands are separated by commas by default. Format Numbers (continued) Excel designed the Accounting format to comply with accounting standards. When using the Accounting format, if you use the dollar sign symbol ($), the dollar sign aligns with the left side of the cell, decimal points are aligned, a dash (–) displays instead of a zero, and negative values display in parentheses. Countries vary in the way they display dates and times. Use the Date and Time format option to choose a locale. If you choose English (U.S.), you have more than 15 ways to display a date and a variety of ways to display time. To learn more about dates and times, see Chapter 3. The Percentage option converts numbers to a percent. You can choose the number of decimal places you want to display. The Fraction option converts numbers to a fraction. If your locale is English (U.S.), you can use the Special format option to format ZIP codes, phone numbers, and Social Security numbers. PERCENTS 1 Click and drag to select the numbers you want to format. 2 Click the Percent Style button in the Number group. 2 1 • 2 Excel converts the numbers to percentages. DATES 1 Click and drag to select the cells you want to format. 2 Click the Number group’s launcher. 3 In the Number tab of the Format Cells dialog box, click Date. 4 Click to choose a format. 5 Click OK. 1 3 4 5 8 2 Excel formats the dates. TIMES 1 1 Click and drag to select the cells you want to format. 2 Click the Number group’s launcher. 3 In the Number tab of the Format Cells dialog box, click Time. 4 Click to choose a format type. 5 Click OK. 3 4 5 • 2 Excel formats the time. 1 FRACTIONS 1 Click and drag to select the cells you want to format. 2 Click the Number group’s launcher. 3 In the Number tab of the Format Cells dialog box, click Fraction. 4 Click to choose a format type. 5 Click OK. 4 3 5 Excel formats the numbers as fractions. • Chapter 1: Getting Started • These cells show how Excel formats the numbers. Excel has several special formats you can use to format Social Security numbers, ZIP codes, and phone numbers. To apply the Social Security number special format, type nine digits into a cell. Click in the cell. Click the launcher in the Number group. The Format Cells dialog box opens to the Number tab. Click Special in the Category box. Click Social Security number in the Type box. Click OK. Excel formats the digits you entered as a Social Security number. You can preformat cells so that when you enter data into a cell, Excel automatically formats it. To preformat the cells, select the cells you want to preformat and then apply the format to them. When you type data into the preformatted cells, Excel automatically applies the format to your data. You can set the default number of decimal places Excel applies when you type a number into a worksheet. Click the Office button. A menu appears. Click Excel Options in the lower-right corner. Click Advanced. Make sure the Automatically Insert Decimal Point check box is checked. Type the number of decimal places you want in the Places field. Click OK. 9 Format Cells F ormatting enhances the presentation of reports. Rows and column headings give your data a visual orientation and highlight important information about the structure and content of your data. You can use the Home tab to format cells in a variety of ways. Clicking the launcher in the Font or Alignment group opens the Format Cells dialog box; here, you can format numbers, align data within or across cells, apply a variety of formats to fonts, add borders, and fill cells with color. Many of the options available to you in the Format Cells dialog box are also available in the Ribbon. When you use the Ribbon, you can frequently apply a format with a single click. You can set off cells by applying a colored background and changing the font color. Use a fill to create a colored background for a cell. You can set off columns or other important information by applying borders. A border adds color to the lines that surround a cell. You can choose the type and thickness of the border line, and you can choose to apply your border only to the sides of the cell you specify. When applying a border, you can choose the color, style, and placement of the border. On the Home tab, in the Font group, Excel provides an Increase Font Size button and a Decrease Font Size button. You can click the Increase Font Size button to make your font larger. You can click the Decrease Font Size button to make your font smaller. You can also enter a font size directly into the Ribbon and/or select a new font. Format Cells CREATE A FILL 1 2 3 • Click the Home tab. 1 3 3 Click and drag to select the cells you want to format. Click here and then select a fill color. 2 Excel applies a fill color to the cells you selected. CHANGE THE TEXT COLOR 2 1 2 2 10 Click and drag to select the cells you want to format. Click here and select a font color. 1 Excel applies a font color to the cells you selected. 2 ADD A BORDER 1 2 Click and drag to select the cells you want to format. 4 Click the down arrow next to the Borders button. 3 Click here and select a color. 4 Click to select a border style. 1 3 • Chapter 1: Getting Started • 3 Excel adds a border to your cells. You can set the default font and font size for all of your workbooks. Click the Office button. A menu appears. Click Excel Options in the lowerright corner. The Excel Options dialog box appears. Click Popular. In the Use this Font field, select the font you want to use. In the Font Size field, select the font size you want to use. Click OK. The next time you open a workbook, it will use the font and font size you selected. If you want text to stand out, you can change the font. If you want to adjust the size of your text so it fits in a cell, you can adjust the font size. You can click the down arrow next to the Font field to change the font for the selected range. Click the down arrow next to the Font Size field to change the font size in the selected range. You can also right-click and then use the mini-toolbar to change the font and font size. continued ➔ 11 Format Cells (continued) I f the text you enter is too long to fit in a single cell, Excel allows the text to spill over into an adjacent cell. If you place text or data in the adjacent cell, Excel cuts off the text in the original cell and you cannot see all of it. If you want to display the text in the original cell on multiple lines in a single cell, use the Excel Wrap Text feature. By default, data or text you enter in a cell displays from left to right. You can change this by clicking the Orientation button and selecting a new orientation. You can angle your text or show your text vertically. Titles provide a brief summary of your data and you may want to center them over the data they summarize. You can center text within a cell by using the Center button. To center text across several cells, you can use the Merge and Center button. In addition to being able to merge and center, you can merge cells in Excel 2007 without centering and you can merge several rows and columns of cells into a single cell. If you want to return merged cells to their original state, you can select the cells and then click the Unmerge Cells option. The Excel Ribbon also has several options you can use to align text within a cell. You can align text with the top, middle, or bottom of a cell and/or with the left, right, or center of a cell. Format Cells (continued) MERGE AND CENTER 1 2 3 • Click the Home tab. 2 12 3 Click and drag to select the cells you want to merge and center. 2 Click the Merge and Center button in the Alignment group. Excel merges and centers your text. WRAP TEXT 1 1 2 Click and drag to select the cells whose text you want to wrap. Click the Wrap Text button in the Alignment group. 1 Excel wraps your text. 2 ORIENTATION 1 2 Chapter 1: Getting Started • Click the cell or cells whose orientation you want to change. 2 Click here and select an orientation. 1 • Excel changes the orientation of your text. Excel has several buttons you can use to align data within a cell. Use the Align Left button ( ) to align your data with the left side of the cell, use the Align Right button ( ) to align data with the right side of the cell, and use the Center button ( ) to center data in the cell. Excel has buttons you can use to place data at the top, bottom, or middle of the cell. Use the Top Align button ( ) to place data at the top of the cell, use the Middle Align button ( ) to place data in the middle of the cell, and use the Bottom Align button ( ) to place data at the bottom of the cell. To set data off, you may want to put it in a cell but indent it. You can use the Increase Indent button ( ) to increase the amount of the indent in a field and the Decrease Indent button ( ) to decrease the amount of the indent in a field. 13 Select Data B efore you can execute an Excel command, you must select the cells to which you want the command to apply. For example, if you want to add a blue fill to several columns of cells, you start the process by selecting the cells. The most common way to select cells is to click and drag. Excel highlights the selected cells. The range of cells you select does not have to be contiguous. You can hold down the Ctrl key as you click and drag to select noncontiguous groups of cells. If you do not hold down the Ctrl key, Excel deselects the first range of cells when you begin to select a new range of cells. If you select multiple ranges of cells, Excel highlights each selected range. You can select a single cell or the entire worksheet. To select a single cell, click in the cell. To select every cell in a worksheet, click the Select All button or press Ctrl+A. To select an entire row or an entire column, simply click the row or column identifier. For example, to select all of the cells in column C, click the C identifier for the column. To select multiple columns, click the first column and then continue holding down the mouse button as you drag to the other columns you want to select. To select entire rows, you click the row identifiers on the left side of the rows. You can quickly select a large range of cells by clicking in the first cell you want to select, holding down the Shift key, and then clicking in the last cell you want to select. Select Data SELECT THE ENTIRE WORKSHEET 1 1 Click the Select All button. Alternatively, press Ctrl+A. SELECT CONTIGUOUS CELLS 1 2 Click the first cell you want to select. 1 Drag to the last cell you want to select. Alternatively, click the first cell, hold down the Shift key, and then click the last cell. 2 14 1 2 1 Click the corner of the first block of cells. Drag the mouse to highlight the desired cells. 3 Press Ctrl. 4 Select the next block of cells. 2 Repeat Steps 3 and 4 to select additional cell blocks. SELECT COLUMNS OR ROWS 1 2 • 4 1 Chapter 1: Getting Started SELECT NONCONTIGUOUS CELLS 2 Click the label for the first column or row you want to select. Drag to the last column or row you want to select. Excel selects the columns or rows. You can format multiple worksheets at the same time. For example, say you are collecting data for three different regions and want to present your data in three worksheets that use the same format. Select three worksheets and type the formatting once to have it appear on all three worksheets. You select multiple worksheets by holding down the Ctrl key as you click the tab of each worksheet you want to select. When you enter data or make changes to any one of the selected worksheets, Excel changes all of the other selected worksheets as well. To deselect multiple worksheets, click a tab for an inactive worksheet while not holding down the Ctrl key. You can also use the arrow keys to select cells. Click in any cell, hold down either the Shift key or the F8 key, and then use the left, right, up, and down arrow keys to expand your selection. To select noncontiguous ranges of cells, select the first range of cells. Click Shift+F8, and then select the next range of cells. You can press Ctrl+Shift+an arrow key to select everything from the active cell to the next blank cell that is to the right, to the left, above, or below the active cell. 15 Copy, Cut, and Paste Cells I places it in a new location. When you apply the Cut or Copy command to a range of cells, Excel surrounds the cells with a dotted line. The selected cells remain marked until you paste or press the Esc key to deselect the cells. If you want to move information from one location to another, you can select, cut, and paste. Cutting and pasting removes data from the original location and After you cut or copy a range of cells, you can paste the cell contents to any location within your current workbook, another Excel workbook, or any other Microsoft Windows program. When you paste to an Excel workbook, Excel replaces the content of the cells into which you paste with the cut or copied values. For that reason, be careful when you paste, because you can overwrite other data. The best method is to select the first cell into which you want to paste the contents and then apply the Paste command. f you want to use the same values in multiple locations, you can copy and paste instead of retyping. For example, you can copy a list of data in one worksheet to another worksheet, or you can copy a formula to multiple other cells. When you copy and paste a cell or range of cells, Excel duplicates everything in the cell — including the cell values, formulas, formatting, comments, and data validation — and leaves the original cell values unchanged. You can select, copy, and paste multiple cells only if the cells are adjacent. Copy, Cut, and Paste Cells COPY AND PASTE 1 Select the cells you want to copy. 2 Click the Home tab. 3 Click the Copy button in the Clipboard group. 2 1 A dotted line appears around the copied cells. 4 Place the mouse pointer where you want to paste the cells. 5 Click Paste in the Clipboard group. • 5 Excel places a copy of the copied cells in the new location. 4 16 3 1 Select the cells you want to move. 2 Click the Home tab. 3 Click the Cut button in the Clipboard group. 2 3 A dotted line appears around the selected cells. 4 5 • Place the mouse pointer where you want to paste the cells. 1 Chapter 1: Getting Started CUT AND PASTE 5 Click Paste in the Clipboard group. Excel places the data in the new location. 4 To use your mouse to move a range of cells, select the cells you want to move and then point to the border of your selection. When your mouse pointer turns to a , drag your selection to a new location. To use your mouse to copy a range of cells, select the cells you want to copy and then hold down the Ctrl key while you point to the border of your selection. When your mouse pointer turns to a , drag your selection to a new location. You can select cells and press Ctrl+C to copy or Ctrl+X to cut and then press Ctrl+V to paste. When you cut or copy a range of cells that have hidden rows or columns and then paste, Excel includes the hidden rows and/or columns when it pastes. If you want to copy only visible cells, select the cells you want to copy. Click the Home tab. Click Find & Select in the Editing group. A menu appears. Click Go To Special. The Go To Special dialog box appears. Click Visible Cells Only. Click OK. Press Ctrl+C. Move to the Paste area. Press Ctrl+V. 17 Copy with the Office Clipboard W ith Office 2007, you can place content into a storage area called the Clipboard and then paste the content into Excel or another Office application. Cut and copied content stays on the Clipboard until you close all Office applications. The Office Clipboard can store up to 24 cut or copied items. When you add the 25th item, Office deletes the first item. You can store text and graphics on the Clipboard. As you add items to the Clipboard, they appear at the top of the Clipboard task pane. All the items on the Clipboard are available for you to paste to a new location in Excel or into another Office document. The Clipboard is not visible until you access it. In Excel, you access the Clipboard by clicking the launcher in the Clipboard group of the Home tab. Each item on the Clipboard appears with an icon that tells you the Office application the information originated from and shows a portion of the text or a thumbnail if the item is a graphic. You can also use the Clipboard to store a range of cells. The Office Clipboard pastes the entire range, including all the values, but any formulas in the cells are not included when you paste. You can paste everything on your Clipboard into your worksheet by clicking the Paste All button. You can clear the Clipboard by clicking the Clear All button. After you paste an item from the Clipboard, Excel provides the Paste Options icon menu. You can use the menu to choose whether you want to use the source formatting or the destination formatting for the pasted data. Copy with the Office Clipboard 1 Click and drag to select the cells you want to copy. 2 Click the Home tab. 3 Click the Copy button in the Clipboard group. 2 3 Excel places a copy of the information on the Office Clipboard. 1 4 • Click the launcher in the Clipboard group. The Clipboard task pane appears. 4 18 6 Click the destination cell. Click the item you want to paste. 6 5 • The content is pasted into the new location. • In the Paste Options icon menu, choose whether to keep the formatting of the copied item or change it to match the formatting of the new location. The default is to match the formatting of the new location. Press Esc to accept the default and remove the menu. Chapter 1: Getting Started 5 You can set the following options by clicking the Options button in the Clipboard task pane. OPTION DESCRIPTION Show Office Clipboard Automatically Shows the Office Clipboard automatically when you copy. Show Office Clipboard When Ctrl+C Pressed Twice Shows the Office Clipboard when you press Ctrl+C twice. Collect Without Showing Office Clipboard Prevents the Clipboard task pane from appearing while you are copying. Show Office Clipboard Icon on Taskbar When the Office Clipboard is active, displays an icon on the Windows taskbar. Show Status Near Taskbar When Copying Shows the number of items collected out of 24 when you add an item to the Office Clipboard. 19 Insert and Delete Cells A s you develop your worksheets, you will sometimes want to make changes to the layout. For example, as you modify your worksheet, you may find that you need to insert or delete cells or even insert or delete entire rows or columns of cells. In Excel, you can shift a cell or group of cells up, down, left, or right. You can also add or delete rows and columns. When you insert cells, rows, or columns, Excel automatically adjusts any formulas that reference the cells, whether they are relative or absolute. See Appendix C to learn more about relative and absolute cell references. When you delete cells, rows, and columns, the same is true; however, when you delete a cell that you directly reference in a formula, Excel cannot adjust the formula and displays a #REF error instead. If you want to insert columns, select the number of columns to the left of where you want the new columns and then select the Insert Column option. For example, if you want to insert three columns, select three columns and then select the Insert Column option. If you want to insert rows, select the number of rows above where you want the new rows and then select the Insert Row option. For example, if you want to insert three rows, select three rows and then select the Insert Row option. If you want to insert nonadjacent columns or rows, hold down the Ctrl key as you select where you want to place the rows or columns. Insert and Delete Cells INSERT CELLS 1 2 3 Click the point at which you want to insert cells. Select multiple cells if you want to insert multiple cells. 2 3 Click the Home tab. Click the down arrow next to Insert in the Cells group. 1 A menu appears. 4 • 5 6 • Click Insert Cells. The Insert dialog box appears. Click the direction in which you want to shift cells ( changes to ). Click OK. Excel shifts the number of cells you selected. Note: If you want to delete cells, select the cells, click Home, click the down arrow next to Delete, click Delete Cells, choose the direction in which you want to shift the cells, and then click OK. 20 5 6 4 1 Click and drag column or row labels where you want to insert columns or rows. 2 This example uses rows. 2 3 • Click the Home tab. Click Insert in the Cells group. 1 3 Chapter 1: Getting Started INSERT COLUMNS OR ROWS Excel inserts the columns or rows. You can delete the contents of cells by selecting the cells and then pressing the Delete key. You can also use Excel’s Clear options to remove everything or to delete formats, contents, or comments from a cell. To remove everything from a cell or group of cells, select the cells and then click the Home tab. Click Clear ( ) in the Editing group and then click Clear All. To remove formats while leaving the contents intact, select the cells and then click the Home tab. Click Clear in the Editing group, and then click Clear Formats. To remove contents while leaving the formatting intact, select the cells and then click the Home tab. Click Clear in the Editing group, and then click Clear Contents. You can use comments to annotate your worksheet. To add a comment, click the cell to which you want to add a comment, click the Review tab, and then click Comment. You can then type your comment in the block provided. To remove a comment, select the cell with the comment, click the Home tab, click Clear, and then click Clear Comments. To learn more about comments, see Chapter 11. 21 Find and Replace A s worksheets get larger, finding the information you want can be difficult. You can use Excel’s Find feature to locate information. If you want to replace the found information with new information, use Excel’s Find and Replace feature. Use the Find tab in the Find and Replace dialog box to find information. Use the Replace tab in the Find and Replace dialog box to find and replace information. You can use substitutions in the Find and Replace dialog box. You can use the asterisk (*) as a substitute for any sequence of characters. You can use the question mark (?) as a substitute for any single character. For example, typing *ber finds September, October, November, and December. Typing J?ne finds Jane and June. When you click the Find All button, Excel by default finds every instance of the value you are looking for in the active worksheet and lists the workbook, worksheet, cell name, cell address, value, and formula for each found value at the bottom of the Find and Replace dialog box. When you click Find Next, Excel moves to the first instance of the value, and Excel moves to the next instance with every additional click of the Find Next button. If you want to replace the values you find with a new value, click Replace All on the Replace tab to replace every instance of the value. Click Replace to replace the selected instance of the values and then move to the next instance. Click Find Next if you want to move to the next instance without replacing the selected instance. In the Find and Replace dialog box, you can use the Options button to set additional options. Find and Replace FIND 1 1 Click the Home tab. 2 Click Find & Select in the Editing group. 2 3 A menu appears. 3 • 4 5 Click Find. The Find and Replace dialog box appears. Type what you want to find into the Find What field. Click Find All to find all instances. Click Find Next to find the first instance. This example uses Find All. 6 • 22 If you clicked Find All, click an instance to move to that instance. Excel moves to the instance you clicked. 4 5 6 Chapter 1: Getting Started REPLACE 1 2 3 4 • Repeat Steps 1 to 3 under Find. In the Find and Replace dialog box, click the Replace tab. Enter what you want to find. Enter your replacement. 2 3 4 Click the Replace All button to replace all instances. This example uses Replace All. • • Click Find and then Replace to find and replace the first instance; then click Find Next to find the next instance. 5 Excel replaces the data. 6 A message box appears telling you Excel made replacements. 5 6 Click OK. Click the Close button to close the Find and Replace dialog box. You can click the Options button on the Find and Replace tabs of the Find and Replace dialog box to set several options. In the Within field, select Sheet if you want to search only the active worksheet. Select Workbook if you want to search the entire workbook. In the Search field, select By Rows if you want to search right to left across the rows. Select Column if you want to search top to bottom down the columns. Select the check box in the Match Case field ( changes to ) if you want your match to be case sensitive. For example, if this option is not selected, abc is considered the same as ABC or aBc. Select the check box in the Match Entire Cell Contents field ( changes to ) if you want what you type in the Find What field to match the cell contents and not contain any extraneous information. For example, say one cell contains the value Jane Smith and another cell contains the value Smith. If you select Match Entire Cell Contents, Excel will find Smith but not Jane Smith. 23 Find and Replace Formats C ells can contain numbers, text, formats, and formulas. With Excel, you can search for any of these elements to view them, replace them, or perform some other action. You may, for example, find and replace values to correct mistakes, or perhaps you need to return to a value to add a comment or apply formatting. You can access the Excel Find and Replace dialog box on the Home tab in the Editing group or by pressing Ctrl+H. The Find feature is part of Find and Replace and is available on the Home tab in the Editing group or by pressing Ctrl+F. To find and replace formats, specify what you are seeking and with what you want to replace the item you are seeking. Click the Options button in the Find and Replace dialog box to specify additional details. Use the Within drop-down menu to indicate whether to search the current worksheet or the current workbook. In the Find and Replace dialog box, clicking a Format button opens the Find Format or Replace Format dialog box. You can use these dialog boxes to specify the Number, Alignment, Font, Border, Fill, or Protection you want to find and/or replace. Use the Formatting button to restrict your search to characters formatted in a certain way, such as bold or percentages. Before you start a new Find and/or Replace for formats, make sure you clear all formats by clicking the down arrow next to the two Format buttons and then clicking Clear Find Format and Clear Replace Format. Find and Replace Formats 1 2 3 1 Click the Home tab. 2 Click Find & Select in the Editing group. 3 Click Replace. Alternatively, you can press Ctrl+H to open the Find and Replace dialog box. 4 In the Find and Replace dialog box, click Options if your dialog box does not look like the one shown here. Note: The Options button allows you to toggle between the short and long forms of the dialog box. 5 6 24 Click here and select Choose Format From Cell. Click in a cell that has the format you want to replace. 6 5 5 4 7 8 • 9 A preview of the format you selected appears. 8 Click here and select Choose Format From Cell. Click in a cell that has the format you want to use as a replacement. Click Replace All. You can click Replace to make one change at a time. • If you want to find instead of replace formats, click Find All or Find Next to highlight cells in the worksheet without replacing formats. 0 ! 9 A preview of the format you selected appears. • • 7 Chapter 1: Getting Started • ! Excel replaces the formats. A message box appears, telling you Excel made replacements. Click OK. 0 Click Close. On the Home tab, when you click Find & Select in the Editing group, Excel presents a menu of options. If you click Formulas, Comments, Conditional Formatting, Constants, or Data Validation, Excel finds all the formulas, comments, conditional formatting, constants, or data validations in your worksheet and selects them. You can use the Tab key and Shift+Tab keys to move among the cells. If you want to move around your worksheet quickly, you can use the Go To dialog box. Press Ctrl+G or click the Home tab, click Find & Select in the Editing group, and then click Go To to open the Go To dialog box. In the Go To field, you can double-click a range name to move to the named range. In the Reference field, type a cell address and then click OK to move to a cell. 25