Microsoft excel bible 2016 pdf free -

Looking for:

Excel Books - PDF Drive 

Click here to DOWNLOAD

     

Excel Bible by John Walkenbach - Ebook | Scribd - Viimeisimmät uutiset

 

When you're satisfied, click the large Print button in the upper-left corner. The page is printed, and you're returned to your workbook. Until now, everything that you've done has occurred in your computer's memory.

If the power should fail, all may be lost — unless Excel's AutoRecover feature happened to kick in. It's time to save your work to a file on your hard drive. Click the Save button on the Quick Access toolbar. This button looks like an old-fashioned floppy disk, popular in the previous century.

Because the workbook hasn't been saved yet and still has its default name, Excel responds with a Backstage screen that lets you choose the location for the workbook file. The Backstage screen lets you save the file to an online location or to your local computer. If you like, you can specify a different location. Click Save or press Enter. Excel saves the workbook as a file. The workbook remains open so that you can work with it some more. By default, Excel saves a backup copy of your work automatically every ten minutes.

However, you should never rely on Excel's AutoRecover feature. Saving your work frequently is a good idea. If you've followed along, you may have realized that creating this workbook was not difficult. But, of course, you've barely scratched the surface of Excel. The remainder of this book covers these tasks and many, many more in much greater detail. This chapter describes what you need to know about entering and modifying data in your worksheets.

As you see, Excel doesn't treat all data equally. Therefore, you need to learn about the various types of data that you can use in an Excel worksheet. An Excel workbook file can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of three basic types of data:. A worksheet can also hold charts, diagrams, pictures, buttons, and other objects. These objects aren't contained in cells.

Instead, they reside on the worksheet's draw layer, which is an invisible layer on top of each worksheet. Chapter 23, Enhancing Your Work with Pictures and Drawings, discusses some of the items you can place on the draw layer. Numeric values represent a quantity of some type: sales amounts, number of employees, atomic weights, test scores, and so on.

Values also can be dates Feb 26, or times such as a. Excel can display values in many different formats. In the Applying Number Formatting section, later in this chapter, you see how different format options can affect the display of numeric values. You may be curious about the types of values that Excel can handle. In other words, how large can a number be?

And how accurate are large numbers? Excel's numbers are precise up to 15 digits. For example, if you enter a large value, such as ,,,,, 18 digits , Excel actually stores it with only 15 digits of precision. This digit number displays as ,,,,, This precision may seem quite limiting, but in practice, it rarely causes any problems. One situation in which the digit accuracy can cause a problem is when entering credit card numbers.

Most credit card numbers are 16 digits, but Excel can handle only 15 digits, so it substitutes a zero for the last credit card digit. Even worse, you may not even realize that Excel made the card number invalid. The solution? Enter the credit card numbers as text.

The easiest way is to preformat the cell as Text. Or you can precede the credit card number with an apostrophe. Either method prevents Excel from interpreting the entry as a number. These numbers are expressed in scientific notation. For example, the largest positive number is 9. Keep in mind, though, that this number has only 15 digits of precision. Most worksheets also include text in some of the cells. Text can serve as data for example, a list of employee names , labels for values, headings for columns, or instructions about the worksheet.

Text is often used to clarify what the values in a worksheet mean or where the numbers came from. Text that begins with a number is still considered text. For example, if you type 12 Employees into a cell, Excel considers the entry to be text rather than a numeric value. Consequently, you can't use this cell for numeric calculations. If you need to indicate that the number 12 refers to employees, enter 12 into a cell and then type Employees into the cell to the right.

Formulas are what make a spreadsheet a spreadsheet. Excel enables you to enter flexible formulas that use the values or even text in cells to calculate a result. When you enter a formula into a cell, the formula's result appears in the cell. If you change any of the cells used by a formula, the formula recalculates and shows the new result. Formulas can be simple mathematical expressions, or they can use some of the powerful functions that are built into Excel. Figure 2. The worksheet contains values, text, and formulas.

The cells in column A contain text. Column B contains four values and two formulas. The formulas are in cells B6 and B Column D, for reference, shows the actual contents of the cells in column B. This workbook, named loan payment calculator. To enter a numeric value into a cell, move the cell pointer to the appropriate cell, type the value, and then press Enter or one of the arrow navigation keys.

The value is displayed in the cell and appears in the Formula bar when the cell is selected. You can include decimal points and currency symbols when entering values, along with plus signs, minus signs, and commas to separate thousands. If you precede a value with a minus sign or enclose it in parentheses, Excel considers it to be a negative number. Entering text into a cell is just as easy as entering a value: activate the cell, type the text, and then press Enter or a navigation key.

A cell can contain a maximum of about 32, characters — more than enough to store a typical chapter in this book. Even though a cell can hold a huge number of characters, you'll find that it's not possible to actually display all these characters. If you type an exceptionally long text entry into a cell, the Formula bar may not show all the text.

To display more of the text in the Formula bar, click the bottom of the Formula bar and drag down to increase the height see Figure 2.

Pressing this key combination toggles the height of the Formula bar to show either one row or the previous size. What happens when you enter text that's longer than its column's current width? If the cells to the immediate right are blank, Excel displays the text in its entirety, appearing to spill the entry into adjacent cells.

If an adjacent cell isn't blank, Excel displays as much of the text as possible. The full text is contained in the cell; it's just not displayed. If you need to display a long text string in a cell that's adjacent to a nonblank cell, you have a few choices:. Wrap the text within the cell so that it occupies more than one line.

Choose Home Alignment Wrap Text to toggle wrapping on and off for the selected cell or range. Excel treats dates and times as special types of numeric values. Dates and times are values that are formatted so that they appear as dates or times.

If you work with dates and times, you need to understand Excel's date and time system. Excel handles dates by using a serial number system. The earliest date that Excel understands is January 1, This date has a serial number of 1. January 2, , has a serial number of 2, and so on. This system makes it easy to deal with dates in formulas. For example, you can enter a formula to calculate the number of days between two dates. Most of the time, you don't have to be concerned with Excel's serial number date system.

You can simply enter a date in a common date format, and Excel takes care of the details behind the scenes. For example, if you need to enter June 1, , you can enter the date by typing June 1, or use any of several different date formats. Excel interprets your entry and stores the value , which is the serial number for that date. The date examples in this book use the U. English system. Your Windows regional settings will affect the way Excel interprets a date you've entered.

For example, depending on your regional date settings, June 1, may be interpreted as text rather than a date. In such a case, you need to enter the date in a format that corresponds to your regional date settings — for example, 1 June, For more information about working with dates, see Chapter 12, Working with Dates and Times.

When you work with times, you extend Excel's date serial number system to include decimals. In other words, Excel works with times by using fractional days. For example, the date serial number for June 1, , is Noon on June 1, halfway through the day , is represented internally as Again, you normally don't have to be concerned with these serial numbers or fractional serial numbers for times.

Just enter the time into a cell in a recognized format. In this case, type June 1, You can also modify a cell by changing its formatting. However, formatting a cell affects only a cell's appearance. Formatting doesn't affect the cell's contents.

Later sections in this chapter cover formatting. To delete the contents of a cell, just click the cell and press the Delete key. To delete more than one cell, select all the cells that you want to delete and then press Delete. Pressing Delete removes the cell's contents but doesn't remove any formatting such as bold, italic, or a different number format that you may have applied to the cell. For more control over what gets deleted, you can choose Home Editing Clear.

This command's drop-down list has five choices:. Clear All: Clears everything from the cell — its contents, its formatting, and its cell comment if it has one.

Clear Formats: Clears only the formatting and leaves the value, text, or formula. Clear Contents: Clears only the cell's contents and leaves the formatting. This has the same effect as pressing Delete. Clear Hyperlinks: Removes hyperlinks contained in the selected cells. The text and formatting remain, so the cell still looks like it has a hyperlink, but it no longer functions as a hyperlink.

Remove Hyperlinks: Removes hyperlinks in the selected cells, including the cell formatting. Clearing formats doesn't clear the background colors in a range that has been designated as a table unless you've replaced the table style background colors manually.

See Chapter 5, Introducing Tables, for more about tables. To replace the contents of a cell with something else, just activate the cell and type your new entry, which replaces the previous contents. Any formatting applied to the cell remains in place and is applied to the new content.

You can also replace cell contents by dragging and dropping or by pasting data from the Clipboard. In both cases, the cell formatting will be replaced by the format of the new data. If the cell contains only a few characters, replacing its contents by typing new data usually is easiest.

However, if the cell contains lengthy text or a complex formula and you need to make only a slight modification, you probably want to edit the cell rather than re-enter information. When you want to edit the contents of a cell, you can use one of the following ways to enter cell-edit mode:. Select the cell that you want to edit and then click inside the Formula bar to edit the cell contents in the Formula bar.

You can use whichever method you prefer. Some people find editing directly in the cell easier; others prefer to use the Formula bar to edit a cell. These settings affect how editing works. To access this dialog box, choose File Options. If the Allow Editing Directly in Cells option isn't enabled, you can't edit a cell by double-clicking. In addition, pressing F2 allows you to edit the cell in the Formula bar not directly in the cell.

All these methods cause Excel to go into edit mode. The word Edit appears at the left side of the status bar at the bottom of the window. When Excel is in edit mode, the Formula bar enables two icons: Cancel the X and Enter the check mark. Clicking the Cancel icon cancels editing without changing the cell's contents. Pressing Esc has the same effect. Clicking the Enter icon completes the editing and enters the modified contents into.

Open navigation menu. Close suggestions Search Search. User Settings. Skip carousel. Carousel Previous. Carousel Next. What is Scribd? Explore Ebooks. Bestsellers Editors' Picks All Ebooks. Explore Audiobooks. Bestsellers Editors' Picks All audiobooks. Explore Magazines. Editors' Picks All magazines. Explore Podcasts All podcasts. Difficulty Beginner Intermediate Advanced.

Explore Documents. Enjoy millions of ebooks, audiobooks, magazines, and more. Start your free days Read preview. Publisher: Wiley.

Released: Oct 20, Size : 7. Accessibility Features In Microsoft Excel Description : You will learn how to control the visual appearance of your spreadsheet. Additionally, best practices and effective spreadsheet structure are also covered to help you when using Excel. Microsoft Excel Part 3: Advanced. Excel Advanced. Excel Analytics and Programming. Size : 3. Advanced Microsoft Excel Description : Microsoft Excel is program designed to efficiently manage spreadsheets and analyze data.

It contains both basic and advanced features that anyone can learn. Size : 2. Excel Introduction. Create functional spreadsheets that work Master formulas, formatting, pivot tables, and more Get acquainted with Excel 's new features and tools Customize downloadable templates and worksheets Whether you need a walkthrough tutorial or an easy-to-navigate desk reference, the Excel Bible has you covered with complete coverage and clear expert guidance.

E-Book Information Year: 2, For example, your sheet may contain old data that is no longer needed, or you may want to remove empty rows or columns.

Right-click and choose Delete from the shortcut menu. If you select multiple cells in the column, Excel deletes all rows in the selection. Deleting columns works in a similar way. For example, you can make columns narrower to show more information on a printed page. Excel provides several ways to change the widths of columns and the height of rows. Widen the column to solve the problem. Before you change the column width, you can select multiple columns so that the width will be the same for all selected columns.

To select multiple columns, either click and drag in the column border or press Ctrl while you select individual columns. To select all columns, click the button where the row and column headers intersect.

Instead of selecting an entire column, you can just select cells in the column, and the column is adjusted based on the widest entry in your selection. This command displays a dialog box into which you enter the new default column width. If you enter a long number that displays as hash symbols , you need to change the column width manually.

Changing row heights Row height is measured in points a standard unit of measurement in the printing trade — 72 pt is equal to 1 inch. The default row height using the default font is 15 pt, or 20 px. In addition, Excel automatically adjusts row heights to accommodate the tallest font in the row.

So, if you change the font size of a cell to 20 pt, for example, Excel makes the row taller so that the entire text is visible. Changing the row height is useful for spacing out rows and is almost always preferable to inserting empty rows between lines of data.

Hiding rows and columns In some cases, you may want to hide particular rows or columns. To hide rows in your worksheet, select the row or rows that you want to hide by clicking in the row header on the left.

Then right-click and choose Hide from the shortcut menu. To hide columns, use the same technique, but start by selecting columns rather than rows. You must drag the border in the row or column heading.

Drag the bottom border of a row upward or the right border of a column to the left. A hidden row is actually a row with its height set to zero. Similarly, a hidden column has a column width of zero. When you use the navigation keys to move the cell pointer, cells in hidden rows or columns are skipped. Notice, however, that Excel displays a narrow column heading for hidden columns and a narrow row heading for hidden rows.

You can click and drag the column heading to make the column wider — and make it visible again. For a hidden row, click and drag the small row heading to make the column visible. For example, if column A is hidden, you can press F5 and specify cell A1 or any other cell in column A to move the cell pointer to the hidden column. Understanding how best to manipulate cells and ranges will save you time and effort.

This chapter discusses a variety of techniques that are essential for Excel users. Understanding Cells and Ranges A cell is a single element in a worksheet that can hold a value, some text, or a formula. For example, cell D9 is the cell in the fourth column and the ninth row.

A group of cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon. A1:B1 Two cells that occupy one row and two columns. A1:A cells in column A.

A1:D4 16 cells four rows by four columns. C1:C An entire column of cells; this range also can be expressed as C:C. A6:XFD6 An entire row of cells; this range also can be expressed as A1:XFD All cells in a worksheet. This range also can be expressed as either A:XFD or When you select a range, the cells appear highlighted.

The exception is the active cell, which remains its normal color. Figure 4. Cell A5, the active cell, is selected but not highlighted. Then release the mouse button. If you drag to the end of the window, the worksheet will scroll. Press F8 again to return the navigation keys to normal movement. When you finish making the selection, the Name box reverts to showing the address of the active cell. Quick Analysis? When you select a range of data, Excel may display a Quick Analysis icon at the lower right of your selection.

You can add conditional formatting, create a chart, add formulas, create a pivot table, and generate Sparkline graphics. The exact options vary, depending on the data in the range. For example, you may want to apply the same numeric format or the same alignment options to an entire row or column. The column of the active cell or columns of the selected cells is highlighted.

The row of the active cell or rows of the selected cells is highlighted. You can also click the area at the intersection of the row and column borders to select all cells. Selecting noncontiguous ranges Most of the time, the ranges that you select are contiguous — a single rectangle of cells. Selecting noncontiguous ranges is also known as a multiple selection. If you want to apply the same formatting to cells in different areas of your worksheet, one approach is to make a multiple selection.

When the appropriate cells or ranges are selected, the formatting that you select is applied to them all. Three ranges are selected: A6:F6. AF14, and AF Then press and hold Ctrl as you click and drag the mouse to highlight additional cells or ranges. Separate each range address with a comma. Enter the range or cell address in the Reference box, and separate each range address with a comma.

Click OK, and Excel selects the ranges. Selecting multisheet ranges In addition to two-dimensional ranges on a single worksheet, ranges can extend across multiple worksheets to be three-dimensional ranges.

Suppose that you have a workbook set up to track budgets. One approach is to use a separate worksheet for each department, making it easy to organize the data. You can click a sheet tab to view the information for a particular department. The workbook has four sheets: Totals, Operations, Marketing, and Manufacturing. The sheets are laid out identically. The only difference is the values. The Totals sheet contains formulas that compute the sum of the corresponding items in the three departmental worksheets.

This workbook, named budget. Assume that you want to apply formatting to the sheets — for example, make the column headings bold with background shading. A better technique is to select a multisheet range and format the cells in all the sheets simultaneously. The following is a step-by-step example of multisheet formatting using the workbook shown in Figure 4.

Activate the Totals worksheet by clicking its tab. Select the range B3:F3. Press Shift and click the Manufacturing sheet tab. This step selects all worksheets between the active worksheet Totals and the sheet tab that you click — in essence, a three-dimensional range of cells see Figure 4. Excel applies the formatting to the selected range across the selected sheets.

Click one of the other sheet tabs. This step selects the sheet and cancels Group mode; [Group] is no longer displayed in the title bar. When a workbook is in Group mode, any changes that you make to cells in one worksheet also apply to the corresponding cells in all the other grouped worksheets. You can use this to your advantage when you want to set up a group of identical worksheets because any labels, data, formatting, or formulas you enter are automatically added to the same cells in all the grouped worksheets.

In general, selecting a multisheet range is a simple two-step process: select the range in one sheet, and then select the worksheets to include in the range. To select a group of contiguous worksheets, you can press Shift and click the sheet tab of the last worksheet that you want to include in the selection. To select individual worksheets, press Ctrl and click the sheet tab of each worksheet that you want to select.

If all the worksheets in a WOW! When you make the selection, the sheet tabs of the selected sheets display in bold with underlined text, and Excel displays [Group] in the title bar. To select all sheets in a workbook, right-click any sheet tab and choose Select All Sheets from the shortcut menu. After you make your choice in the dialog box, Excel selects the qualifying subset of cells in the current selection. Often, this subset of cells is a multiple selection.

If no cells qualify, Excel lets you know with the message No cells were found. If you bring up the Go to Special dialog box with only one cell selected, Excel bases its selection on the entire used area of the worksheet. Otherwise, the selection is based on the selected range. Some of the options are very useful.

TABLE 4. Use the check boxes under the Formulas option to choose which types of nonformula cells to include. Formulas Selects cells that contain formulas. Blanks Selects all empty cells. If a single cell is selected when the dialog box displays, this option selects the empty cells in the used area of the worksheet.

Current Region Selects a rectangular range of cells around the active cell. This range is determined by surrounding blank rows and columns. Current Array Selects the entire array. Objects Selects all embedded objects on the worksheet, including charts and graphics. Row Differences Analyzes the selection and selects cells that are different from other cells in each row. Column Differences Analyzes the selection and selects the cells that are different from other cells in each column. Precedents Selects cells that are referred to in the formulas in the active cell or selection limited to the active sheet.

You can select either direct precedents or precedents at any level. Dependents Selects cells with formulas that refer to the active cell or selection limited to the active sheet. You can select either direct dependents or dependents at any level.

See Chapter 31 for more information. Last Cell Selects the bottom-right cell in the worksheet that contains data or formatting. For this option, the entire worksheet is examined, even if a range is selected when the dialog box displays. Visible Cells Only Selects only visible cells in the selection. This option is useful when dealing with a filtered list or a table. The All option selects all such cells. The Same option 4 selects only the cells that have the same conditional formatting as the active cell.

The Same option selects only the cells that have the same validation rules as the active cell. The placement of these suboptions can be misleading. For example, when you select Constants, the suboptions under Formulas become available to help you further refine the results.

Likewise, the suboptions under Dependents also apply to Precedents, and those under Data Validation also apply to Conditional Formats. The Find and Replace dialog box is shown in Figure 4. The dialog box expands to display all the cells that match your search criteria. For example, Figure 4. You can click an item in the list, and the screen will scroll so that you can view the cell in context. The Find and Replace dialog box allows you to return to the worksheet without dismissing the dialog box.

The Find and Replace dialog box supports two wildcard characters:? Searching for 1? For example, searching for smith does not locate Smith. When using wildcard characters, an exact match is not required. Excel makes copying or moving ranges of cells easy. The source cell is copied to every cell in the destination range. The primary difference between copying and moving a range is the effect of the operation on the source range.

When you copy a range, the source range is unaffected. When you move a range, the contents are removed from the source range. When you copy a cell that contains a formula, the cell references in the copied formulas are changed automatically to be relative to their new destination. Select the cell or range to copy the source range , and copy it to the Clipboard. To move the range instead of copying it, cut the range instead of copying it.

Move the cell pointer to the range that will hold the copy the destination range , and paste the Clipboard contents.

When you paste information, Excel overwrites any cells that get in the way without warning you. When you copy a cell or range, Excel surrounds the copied area with a thick-dashed border. As long as that border remains visible, the copied information is available for pasting.

If you press Esc to cancel the border, Excel removes the information from the Clipboard. Because copying or moving is used so often, Excel provides many different methods. I discuss each method in the following sections. Copying and moving are similar operations, so I point out only important differences between the two. You only need to activate the upper-left cell in the destination range. Each time that you cut or copy information, Windows replaces the information previously stored on the Clipboard with the new information that you cut or copied.

The Windows Clipboard can store data in a variety of formats. Because Windows manages information on the Clipboard, it can be pasted to other Windows applications, regardless of where it originated.

Microsoft Office has its own Clipboard the Office Clipboard , which is available only in Office programs. Whenever you cut or copy information in an Office program, such as Excel or Word, the program places the information on both the Windows Clipboard and the Office Clipboard. However, the program treats information on the Office Clipboard differently from the way it treats information on the Windows Clipboard.

Instead of replacing information on the Office Clipboard, the program appends the information to the Office Clipboard. With multiple items stored on the Clipboard, you can then paste the items either individually or as a group. For more control over how the pasted information appears, right-click the destination cell and use one of the paste icons in the shortcut menu see Figure 4.

Instead of using Paste, you can just activate the destination cell and press Enter. Click the list or press Ctrl , and you see the options shown in the figure here.

These options enable you to specify how the data should be pasted, such as values only or formatting only. In this case, using the paste option buttons is an alternative to using options in the Paste Special dialog box. Count me in the latter group. Copying or moving by using drag-and-drop Excel also enables you to copy or move a cell or range by dragging.

Oddly, you do not get a warning if a drag-anddrop copy operation will overwrite existing cell contents. The mouse pointer is augmented with a small plus sign.

Then drag the selection to its new location while you continue to press the Ctrl key. The original selection remains behind, and Excel makes a new copy when you release the mouse button.

Copying to adjacent cells Often, you need to copy a cell to an adjacent cell or range. You can use the same formula to add the values in the other columns. Rather than re-enter the formula, you can copy it to the adjacent cells. Excel provides additional options for copying to adjacent cells. Excel copies the original selection to the cells that you highlight while dragging.

Copying a range to other sheets You can use the copy procedures described previously to copy a cell or range to another worksheet, even if the worksheet is in a different workbook.

You must, of course, activate the other worksheet before you select the location to which you want to copy. Excel offers a quicker way to copy a cell or range and paste it to other worksheets in the same workbook: 1. Select the range to copy. Press Ctrl and click the sheet tabs for the worksheets to which you want to copy the information. A dialog box appears to ask you what you want to copy All, Contents, or Formats. Make your choice and then click OK.

Excel copies the selected range to the selected worksheets; the new copy occupies the same cells in the selected worksheets as the original occupies in the initial worksheet. You can quickly overwrite lots of cells with this command and not even realize it. To make the Clipboard task pane open automatically, click the Options button near the bottom of the task pane and choose the Show Office Clipboard Automatically option.

Repeat this process, selecting the next cell or range that you want to copy. To paste an individual item, click it in the Clipboard task pane. The items are pasted, one after the other. The Paste All button is probably more useful in Word, for situations in which you copy text from various sources and then paste it all at once.

The Office Clipboard has a serious problem that limits its usefulness for Excel users: if you copy a range that contains formulas, the formulas are not transferred when you paste to a different range. Only the values are pasted. Pasting in special ways You may not always want to copy everything from the source range to the destination range. For example, you may want to copy only the formula results rather than the formulas themselves. Or you may want to copy the number formats from one range to another without overwriting any existing data or formulas.

Click the icon to use the selected paste option. Here, the information is copied from E4:G7 and is being pasted beginning at cell F11 using the Transpose option. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed. When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range.

The destination for the copy can be a new range or the original range. In the latter case, Excel replaces the original formulas with their current values. A Paste Options drop-down appears at the lower right of the pasted range. Click it or press Ctrl , and you see the paste option icons again. You can also right-click and choose Paste Special from the shortcut menu to display this dialog box. This dialog box has several options, which I explain in the following list. Excel actually has several different Paste Special dialog boxes, each with different options.

This section describes the Paste Special dialog box that appears when a range or cell has been copied. For the Paste Special command to be available, you need to copy a cell or range. In addition, the Paste Special dialog box enables you to perform other operations, described in the following sections.

Performing mathematical operations without formulas The option buttons in the Operation section of the Paste Special dialog box let you perform an arithmetic operation on values and formulas in the destination range.

For example, you can copy a range to another range and select the Multiply operation. Excel multiplies the corresponding values in the source range and the destination range and replaces the destination range with the new values.

This feature also works with a single copied cell, pasted to a multicell range. Assume that you have a range of values, and you want to increase each value by 5 percent. Then select the range of values and bring up the Paste Special dialog box. If the destination range contains formulas, the formulas are also modified.

In many cases, this is not what you want. Transposing a range The Transpose option in the Paste Special dialog box changes the orientation of the copied range. Note that you can use this check box with the other options in the Paste Special dialog box.

As a result, the destination range automatically reflects changes in the source range. For example, WOW! You can paste a cell or range name into a formula by using Formula Autocomplete.

See Chapter 10 for information on Formula Autocomplete. Creating range names in your workbooks Excel provides several methods you can use to create range names. Excel also uses a few names internally for its own use.

Using the Name box The fastest way to create a name is to use the Name box to the left of the Formula bar. Select the cell or range to name, click the Name box, and type the name. Press Enter to 98 WOW! If the name you type includes an invalid character, Excel displays an error message. Attempting to do so simply selects the range. The Name box is a drop-down list and shows all names in the workbook. To choose a named cell or range, click the Name box and choose the name. The name appears in the Name box, and Excel selects the named cell or range in the worksheet.

Using the New Name dialog box For more control over naming cells and ranges, use the New Name dialog box. Start by selecting the cell or range that you want to name. Excel displays the New Name dialog box, shown in Figure 4. Note that this is a resizable dialog box. Click and drag a border to change the dimensions. Use the Scope drop-down list to indicate the scope for the name. If you like, you can add a comment that describes the named range or cell. Click OK to add the name to your workbook and close the dialog box.

Using the Create Names from Selection dialog box You may have a worksheet that contains text that you want to use for names for adjacent cells or ranges. For example, you may want to use the text in column A to create names for the corresponding values in column B.

Excel makes this task easy. These items can be individual cells or ranges of cells. A multiple selection is allowed.