Microsoft office excel 2003 step by step pdf

Bamboo Trellis appears in cell C9. A list of existing values in the column appears. Bird Netting appears in cell C Sheet1 appears. Excel highlights the cells in the rectangle defined at the upper left by cell C8 and at the lower right by cell J Note that cell C8 is still the active cell. The value 0 appears in every selected cell. You can then edit the word directly, pick the proper word from the list of suggestions, or have the spelling checker ignore the misspelling.

To undo a change, you click the appropriate toolbar button or open the Edit menu and choose the Undo command. If you decide you want to keep a change, you can use the Redo command to restore it. You can use a distinct text format to identify data you might need to change later. Catherine could format the changeable prices differently from the rest of the prices in the worksheet and call the representative to update her worksheet just before the products became available.

After receiving the new prices, she could use Find Format in the Find and Replace dialog box to locate the old prices and then change them by hand. In this exercise, you have just found out that the manufacturer of the Comfy Chair has changed the name of the product to the Cushy Chair. You use Find to determine whether there are any occurrences of the word Comfy, and if there are, you use Replace to change them to Cushy. After you have made that change, you use Find Format to locate specially formatted data and change it.

Finally you use the spelling checker to ensure that your text data has been entered correctly. On the Edit menu, click Find. The Find and Replace dialog box appears and opens to the Find tab.

In the Find what box, type Comfy, and then click Find Next. The first cell containing Comfy is highlighted. The second cell containing Comfy is highlighted. Tip Clicking Find All would generate a list of matching cells and their contents below the dialog box. Clicking one of the matches moves you to that instance of the matching word or phrase.

The Find what box still shows the word Comfy. Click Replace All. A dialog box appears, indicating that Excel has completed the operation and that three replacements were made. The three occurrences of the word Comfy have been switched to Cushy.

Important You can change the occurrences of the word Comfy one at a time by clicking the Replace button instead of the Replace All button. The Find tab page appears. The Find tab page options appear. The Find Format dialog box appears. The Font tab page appears. The Find Format dialog box disappears. Excel highlights the first cell containing italicized text. The Find and Replace dialog box disappears.

Excel removes italics from the text in the selected cell. Italic 18 Type Excel replaces the previous value in the cell with the value you just entered. You will now undo the change you just made.

The contents of cell C12 revert to Undo 20 Click the Undo button. The contents of cell C12 are once again italicized. The contents of cell C12 are no longer italicized.

Redo 22 On the Tools menu, click Spelling. The Spelling dialog box appears. The first misspelled word Excel detects appears in the Not in Dictionary box, while the list of suggested replacements appears in the Suggestions list. Tip If you click Change, Excel inserts the suggested word. If you wanted Excel to ignore this occurrence of Arbo, you could have clicked Ignore Once; clicking Ignore All would cause Excel to skip over any occurrence of Arbo in the worksheet.

Clicking Add to Dictionary means Excel would forever recognize Arbo as a word that did not need to be corrected. A dialog box appears, indicating that the spelling checker found no more misspellings. The dialog box disappears. The Research task pane appears. The results of your search appear in the Research task pane. One of the real strengths of Microsoft Excel is that the program helps you manage large quantities of data with ease.

Part of the reason managing large data collections is so easy with Excel is that you can change how Excel displays your data within a worksheet. You can also change how those descriptions appear in a cell, setting them apart from the data in the worksheet. Adding graphics to worksheets promotes awareness of your company, identifies the data as belonging to your company, and, in the case of a product image, gives viewers valuable information they need to make a purchase decision.

See Also Do you need a quick refresher on the topics in this chapter? Excel provides several ways to set up signposts directing users toward the data they want. Once users have opened the proper workbook, you can guide them to a specific 25 2 Setting Up a Workbook worksheet by giving each worksheet a name; the names are displayed on the sheet tabs in the lower left corner of the workbook window. Choosing Rename opens the worksheet name for editing. You can also change the order of worksheets in a workbook by dragging the sheet tab of a worksheet to the desired position on the navigation bar, bringing the most popular worksheets to the front of the list.

If you need more than three worksheets in most of the workbooks you create, you can change the default number of worksheets in your new workbooks. To change the default number of worksheets, on the Tools menu, click Options.

In the Options dialog box, click the General tab, and, in the Sheets In New Workbook box, type the number of worksheets you want in your new workbooks, and click OK. After you have put up the signposts that make your data easy to find, you can take other steps to make the data in your workbooks easier to work with. Tip You can apply the same change to more than one row or column by selecting the rows or columns you want to change and then dragging the border of one of the selected rows or columns to the desired location.

When you release the mouse button, all of the selected rows or columns will change to the new height or width. You insert rows by clicking a cell and then, on the Insert menu, clicking Rows. Excel inserts a row above the active cell.

You insert a column in much the same way by clicking Columns on the Insert menu. When you do this, Excel inserts a column to the left of the active cell. Likewise, you can insert individual cells into a worksheet. To insert a cell, click the cell that is currently in the position where you want the new cell to appear, and on the Insert menu, click Cells to display the Insert dialog box.

In the Insert dialog box, you can choose whether to shift the cells surrounding the inserted cell down if your data is arranged as a column or to the right if your data is arranged as a row.

When you click OK, the new cell appears, and the contents of affected cells shift down or to the right, as appropriate. In a similar vein, if you want to delete a block of cells, select the cells, and on the Edit menu, click Delete to display the Delete dialog box, complete 26 2 Setting Up a Workbook with option buttons that let you choose how to shift the position of the cells around the deleted cells.

Tip The Insert dialog box also includes option buttons you can select to insert a new row or column; the Delete dialog box has similar buttons that let you delete an entire row or column. In some cases, the values you want to put in the new cells might already exist in your worksheet. For example, Catherine Turner might have typed some sales data into a blank worksheet in anticipation of modifying the sheet once the rest of the data was entered.

After you select the cells and click the Cut toolbar button on the Standard toolbar, on the Insert menu, click Cut Cells. While you can type individual words in cells so that the label fits in the worksheet, another alternative is to merge two or more cells. Merging cells tells Excel to treat a group of cells as a single cell as far as content and formatting go. To merge cells into a single cell, you click the Merge and Center toolbar button. As the name of the button implies, Excel centers the contents of the merged cell.

Tip Clicking a merged cell and then clicking the Merge and Center toolbar button removes the merge. If you want to delete a row or column, you right-click the row or column head and then, from the shortcut menu that appears, click Delete.

You can temporarily hide a number of rows or columns by selecting those rows or columns and then, on the Format menu, pointing to Row or Column and then clicking Hide. Instead, they have just been removed from the display until you call them back; to return the hidden rows to the display, on the Format menu, point to Row or Column and then click Unhide. When you insert a row, column, or cell in a worksheet with existing formatting, the Insert Options button appears.

As with the Paste Options button and the Auto Fill Options button, clicking the Insert Options button displays a list of choices you can 27 2 Setting Up a Workbook make about how the inserted row or column should be formatted.

The options are summarized in the following table. Option Action Format Same as Above Apply the format of the row above the inserted row to the new row.

Format Same as Below Apply the format of the row below the inserted row to the new row. Format Same as Left Apply the format of the column to the left of the inserted column to the new column. Format Same as Right Apply the format of the column to the right of the inserted column to the new column. Clear Formatting Apply the default format to the new row or column. First you name the worksheet and bring it to the front of the list of worksheets in its workbook. Next you increase the column width and row height of the cells holding the sales data.

Then you add a column to the left of the first column of data and then hide rows containing data for all but the first week of the month. OPEN: Easier. Select cells B5 to C7. On the Insert menu, click Cut Cells.

The Insert Paste dialog box appears. The cut cells appear in cells B5 to C7, pushing the existing cells to the right. The values in cells C5 to C7 are repeated incorrectly in cells D5 to D7. The Delete dialog box appears. Cells D5 to D7 are deleted.

Sheet2 is highlighted. The name of the worksheet changes from Sheet2 to January. The January sheet tab moves to the left of the Sheet1 sheet tab. Columns A through M are highlighted. Rows 3 through 35 are highlighted. On the Formatting toolbar, click the Merge and Center toolbar button. On the Insert menu, click Rows.

A new row, labeled row 3, appears above the row previously labeled row 3. A new column, labeled column A, appears to the left of the column previously labeled column A.

Rows 13 through 36 are highlighted. Rows 13 through 36 disappear from the worksheet. The hidden rows reappear in the worksheet. The Options dialog box appears. The General tab page appears. Excel saves the document. For instance, you could choose not to merge the cells because you might want to add data or comments to the neighboring cells later. If the cell to the right were empty, the text in the left cell would simply spill over into the cell to its right.

When there is data in the cell to the right, however, Excel brings it to the front, hiding any data that spills over from the adjoining cell. To avoid hiding the text in the first cell behind the text in the second cell, you can have the text wrap within the first cell, as seen in the following graphic.

Another method for making your data easier to read is to distinguish any data labels by changing how the data appears in a cell. One way to separate data labels from the data that follows them is to change the alignment of the labels in their cells. For example, you can center the data labels in their cells, setting the labels apart from the right-aligned data farther down in the column. You can also make your worksheet data easier to read by ensuring that the data labels at the top of a column freeze, or remain on the screen regardless of how far down in the document you scroll.

For instance, Catherine Turner, the owner of The Garden Company, might not remember which data is kept in which column in a worksheet. Freezing the data labels at the top of the column would let her scroll to the last row of the worksheet and still have the labels visible as a reference.

Excel marks the division between frozen and unfrozen cells with a split bar. Split bar 32 Setting Up a Workbook 2 Troubleshooting When you tell Excel to freeze rows in your worksheet, Excel freezes the rows above the active cell and the columns to the left of the active cell.

So, if you want to freeze the top three rows of your worksheet, click the first cell in the fourth row cell A4 and then turn on the freeze. If you wanted to freeze the top three rows and the first column, you would click the second cell in the fourth row cell B4. In this exercise, you prevent the text in a cell from spilling over into adjoining cells, allowing you to enter comments in those adjoining cells without obscuring the contents of the first cell.

You then change the alignment of the cells containing the data labels for the columns in your worksheet and then freeze those data labels so that they remain at the top of the page as you scroll down through the worksheet. Click cell B The Format Cells dialog box appears.

The text in cell B34 wraps to fit within the original borders of the cell. Center 33 2 Setting Up a Workbook The contents of the selected cells are centered within those cells.

A split bar appears between row 1 and row 2. Row 1 stays in place while the remaining rows scroll normally. The split bar disappears, and all rows scroll normally. Excel saves your changes. Adding a Graphic to a Document Microsoft Office Specialist Specialist An important part of establishing a strong business is creating a memorable corporate identity.

Once you or your graphic artist has created a logo, you should add the logo to all of your documents, especially any that might be seen by your customers. Not only does the logo mark the documents as coming from your company, it also serves as an advertisement, encouraging anyone who sees your worksheets to call or visit your company. One way to add a picture to a worksheet is to go through the Insert menu and click the Picture item. You can also change the appearance of the picture by opening the Format menu and choosing Picture.

You can also resize a picture by clicking it and then dragging one of the handles that appears on the graphic. If you do accidentally resize a graphic by dragging a handle, just click the Undo button to remove your change.

Tip To remove a background image from a worksheet, open the Format menu, point to Sheet, and click Delete Background. On the Insert menu, point to Picture, and then click From File.

The Insert Picture dialog box appears. The chosen graphic appears in the AddPicture. The Format Picture dialog box appears. The Size tab page appears. Notice that the Lock aspect ratio check box is selected. The picture is resized, maintaining the original aspect ratio, and rotated. The graphic moves with your mouse pointer.

Troubleshooting Remember that dragging one of the handles at the edge of the graphic will resize the graphic. If you accidentally resize the logo instead of moving it, click the Undo button. The Picture tab page appears. The image changes to reflect the properties you set.

The image disappears. The Sheet Background dialog box appears. The image repeats in the background of the active worksheet. If you always use workbooks where each worksheet represents a month of the year, change the default number of worksheets to 12! Be sure your worksheet columns and rows are roomy enough to accommodate your data. Rather than go through a lengthy cut-and-paste routine when you forgot to type a cell value, just add a cell where you need it.

Microsoft Excel workbooks give you a handy place to store and organize your data, but you can also do a lot more with your data in Excel. One important task you can perform in Excel is to calculate totals for the values in a series of related cells. You can also use Excel to find out other information about the data you select, such as the maximum or minimum value in a group of cells.

Finding the maximum or minimum value in a group can let you identify your best salesperson, product categories you might need to pay more attention to, or suppliers that consistently give you the best deal. Regardless of your bookkeeping needs, Excel gives you the ability to find the information you want. And if you should make an error, you can find the cause and correct it quickly.

Excel makes it easy to reference a number of cells at once, letting you define your calculations quickly. Rather than specify the cells individually every time you want to use the data they contain, you can define those cells as a range also called a named range. For instance, you could group the items from the previous graphic into a range named OrderItems1. Whenever you want to use the contents of that range in a calculation, you can simply use the name of the range instead of specifying each cell individually.

You can create a named range in a number of ways, two of which you can access through the Insert menu. The first method works well if you have a column of data with a label at the head of the column, as in the following graphic.

In this case, you access the Create Names dialog box by pointing to Name on the Insert menu and clicking Create. You can also create and delete named ranges through the Define Name dialog box, which you access by pointing to Define on the Insert menu and clicking Name. A final way to create a named range is to select the cells you want in the range, click in the Name box next to the formula bar, and then type the name for the range.

Assigning the name of an existing range to a new range removes the original reference, likely affecting how your worksheet behaves. In this exercise, you will create named ranges to streamline references to groups of cells.

Click cell C3 and drag to cell C The selected cells are highlighted. The Create Names dialog box appears. Excel assigns the name Price to the cell range. The Supplies worksheet appears. On the Insert menu, point to Name, and then click Define.

The Define Name dialog box appears. Excel assigns the name SuppliesPrice to the cell range, and the Define Name dialog box disappears. The Furniture worksheet appears. Click in the Name box. The contents of the Name box are highlighted. Excel assigns the name FurniturePrice to the cell range. Price appears in the Names in workbook box. The Define Name dialog box disappears. After the equal sign, you type the formula. After you have entered a formula into a cell, you can revise it by clicking the cell and then editing the formula in the formula bar.

Remember, the equal sign must be the first character! Typing the cell references for 15 or 20 cells in a calculation would be tedious, but Excel makes it easy to handle complex calculations. To create a new calculation, you click Function on the Insert menu. The Insert Function dialog box appears, with a list of functions, or predefined formulas, from which you can choose.

The most useful functions in the list are described in the following table. The NOW function returns the time the workbook was last opened, so the value will change every time the workbook is opened. The PMT function is a bit more complex. It calculates payments due on a loan, assuming a constant interest rate and constant payments. To perform its calculations, the PMT function requires an interest rate, the number of 45 3 Performing Calculations on Data months of payments, and the starting balance.

The elements to be entered into the function are called arguments and must be entered in a certain order. That order is written PMT rate, nper, pv, fv, type. The following table summarizes the arguments in the PMT function. In both cases, when you release the mouse button, the references of the cells you selected appear in the formula.

Another use for formulas is to display messages when certain conditions are met. This kind of formula is called a conditional formula, and it uses the IF function. To create a conditional formula, you click the cell to hold the formula and open the Insert Function dialog box. From within the dialog box, you select IF from the list of available functions and then click OK. The Function Arguments dialog box appears. Now you need to have Excel display messages indicating whether the customer should receive a free magazine.

In this case, you would type "Qualifies for a free magazine! When you do, Excel will try to change the formula so that it works in the new cells. Excel has reinterpreted the formula so that it fits the surrounding cells! Excel knows it can reinterpret the cells used in the formula because the formula uses a relative reference, or a reference that can change if the formula is copied to another cell.

Relative references are written with just the cell row and column for example, C If you want a cell reference to remain constant when the formula using it is copied to another cell, you can use an absolute reference.

Tip If you copy a formula from the formula bar, use absolute references, or use only named ranges in your formula. The cells with the cost of products in this order are stored in the named range OrderItems. D7 becomes the active cell. Excel copies the formula in cell D7 to the Clipboard. Copy 4 Click cell D8, and then, on the Standard toolbar, click the Paste button.

Paste 5 Press A. The formula in cell D8 disappears. The Insert Function dialog box appears. The Function Arguments dialog box appears, with the contents of the Number 1 box highlighted.

Finding and Correcting Errors in Calculations Microsoft Office Specialist Specialist Including calculations in a worksheet gives you valuable answers to questions about your data. As is always true, however, it is possible for errors to creep into your for mulas.

Excel makes it easy to find the source of errors in your formulas by identifying the cells used in a given calculation and describing any errors that have occurred. The process of examining a worksheet for errors in formulas is referred to as auditing. Excel identifies errors in several ways. The first way is to fill the cell holding the for mula generating the error with an error code.

The following table lists the most common error codes and what they mean. The formula attempts to divide by zero. Another technique you can use to find the source of formula errors is to ensure that the appropriate cells are providing values for the formula. You can also audit your worksheet by identifying cells with formulas that use a value from a given cell. For example, you might have the total cost of a single order used in a formula that calculates the average cost of all orders placed on a given day.

As with tracing precedents, you can point to Formula Auditing on the Tools menu and then click Trace Dependents to have Excel draw blue arrows from the active cell to those cells that have calculations based on that value. To hide the tracer arrows on a worksheet, you point to Formula Auditing on the Tools menu and click Remove All Arrows. If you prefer to have the elements of a formula error presented as text in a dialog box, you can use the Error Checking dialog box which you display by clicking Error Checking on the Tools menu to view the error and the formula in the cell where the error occurs.

You can also use the controls in the Error Checking dialog box to move through the formula one step at a time, to choose to ignore the error, or to move to the next or the previous error. Finally, you can monitor the value in a cell regardless of where in your workbook you are by opening a watch window that displays the value in the cell.

For example, if one of your formulas uses values from cells in other worksheets, or even other workbooks, you can set a watch on the cell that contains the formula and then change the values in the other cells. To set a watch, click the cell you want to moni tor, point to Formula Auditing on the Tools menu, and then click Show Watch Window. Click Add Watch to have Excel monitor the selected cell. As soon as you type in the new value, the watch window displays the new result of the formula.

In this exercise, you use the formula auditing capabilities in Excel to identify and cor rect errors in a formula. Cell D20 appears in the watch window. The arrow disappears. The Error Checking dialog box appears. The error in cell D8 appears in the Error Checking dialog box. Blue arrows appear, pointing to cell D20 from cells D7 and D The arrows disappear. The Evaluate Formula dialog box appears, with the formula from cell D20 displayed.

The result of the formula in cell D20 appears. In the watch window, click the watch in the list. The watch disappears. The watch window disappears. An important aspect of working with data entered into a workbook is ensuring that the data is easy to read. Changing how data appears on a worksheet helps set the contents of a cell apart from the contents of surrounding cells. The simplest example is that of a data label. To save time, you can define a number of custom formats and then apply them quickly to the desired cells.

A quick way to distinguish when a customer is close to his or her credit limit is to change how their outstanding balance is presented in its cell. Catherine might, for example, change the color of the font from the standard black to blue when a customer is within 10 percent of his or her limit.

In addition to changing how data appears in the cells of your worksheet, you can also use headers and footers to add page numbers, current data, or graphics to the top and bottom of every printed page. To make your data labels or any other data stand out, you can change the format of the cells in which the data is stored. If you want to set your data labels apart by making them appear bold, click the Bold button. Items on the Formatting toolbar that give you choices, such as the Font Color control, have a down arrow at the right edge of the control.

Clicking the down arrow displays a list of options accessible for that control, such as the fonts available on your system or the colors you can assign to a cell. Borders Another way you can make a cell stand apart from its neighbors is to add a border around the cell. For example, you could select a group of cells and then choose the border type you wanted.

That method of adding borders is still available in Excel, but it has some limitations. The most important limitation is that, while creating a simple border around a group of cells is easy, creating complex borders makes you select different groups of cells and apply different types of borders to them. The current version of Excel makes creating complex borders easy by letting you draw borders directly on the worksheet.

To use the new border-drawing capabilities, display the Borders toolbar. Draw Border Erase Border Line Color Line Style To draw a border around a group of cells, click the mouse pointer at one corner of the group and drag it to the diagonal corner.

You will see your border expand as you move the mouse pointer. If you want to add a border in a vertical or horizontal line, drag the mouse pointer along the target grid line—Excel will add the line without 59 4 Changing Document Appearance expanding it to include the surrounding cells. You can also change the characteristics of the border you draw by using the options on the Borders toolbar.

Another way you can make a group of cells stand apart from its neighbors is to change their shading, or the color that fills the cells. On a worksheet with monthly sales data for The Garden Company, for example, owner Catherine Turner could change the fill color of the cells holding her data labels to make the labels stand out even more than by changing the formatting of the text used to display the labels. If you want to change the attributes of every cell in a row or column, you can click the header of the row or column you want to format and select your desired format.

The standard font when you install Excel is Arial, a simple font that is easy to read on a computer screen and on the printed page. If you want to choose another font, click Options on the Tools menu, which displays the General tab page, and use the Standard Font and Size controls to set the new default for your workbook. After those tasks are complete, you change the default font for the workbook. Click cell E2.

Cell E2 is highlighted. The text in cell E2 appears bold. Row 5 is highlighted. The contents of the cells in row 5 are centered. Center 7 Click cell E2. The Borders toolbar appears, and the mouse pointer changes to a pencil. A border appears around cell E2. The Borders toolbar disappears. The Fill Color color palette appears.

Fill Color 12 In the Fill Color color palette, click the yellow square. Cell G2 fills with a yellow background. Click the Standard Font down arrow and select Courier New. Applying an Existing Format to Data Microsoft Office Specialist Specialist As you work with Excel, you will probably develop preferred formats for data labels, titles, and other worksheet elements.

You can find the predefined formats available to you in the Style dialog box. You can apply an existing style to a cell from within the Style dialog box. If none of the existing styles are what you want, you can create your own by typing the name of your new style in the Style name box and then clicking Modify.

Of course, if you want to change the formatting of an entire worksheet, the Format Painter and the Style dialog box are not the most efficient tools available to you. Instead, Excel lets you apply AutoFormats, which are pre-defined format patterns for a group of cells as opposed to styles, which are pre-defined formats for individual cells.

To apply an AutoFormat, you select the cells you want to format and click AutoFormat on the Format menu. In this exercise, you create a style, apply the new style to a data label, and then use the Format Painter to apply the style to the contents of another cell. Finally you assign an AutoFormat.

Click cell C3. The Style dialog box appears, with Normal in the Style name box. The Add button is activated. The text in the Preview pane, in the lower right corner of the dialog box, changes to reflect your choice. The Alignment tab page appears.

Download or read online Excel written by Judy Mardar,Russel Stolins, published by Unknown which was released on Download or read online Microsoft Office Powerpoint written by Sandra Caldwell, published by Unknown which was released on Get Microsoft Office Powerpoint Books now! Excel Crash Course for Engineers is a reader-friendly introductory guide to the features, functions, and applications of Microsoft Excel in engineering.

The book provides readers with real-world examples and exercises that are directly related to engineering, and offers highly illustrated, step-by-step demonstrations of techniques to solve and visualize engineering problems. Easy to follow step by step lessons enable students to quickly and efficiently learn the intermediate features of Microsoft Excel and how to use them at school, at home, and in the workplace. This Microsoft Official Academic Course offers friendly, straightforward instruction with a focus on real-world business scenarios.

The pattern of the book is based on ethics of Comdex series books simple language, ample of screen shots and three stage learning system. Microsoft Office Excel provides powerful new tools with which to create, analyze, and share spreadsheet information. Excel takes advantage of the latest technologies such as XML and Microsoft SharePoint to extend desktop productivity and workspace collaboration over an intranet or the Internet.

This book covers these changes, as well as. Microsoft Office Access provides powerful new tools with which to manage data, find and retrieve information faster than ever, share information, and integrate solutions with server side products. Access incorporates some of the latest technologies such as XML and Microsoft SharePoint to extend data exchange over an intranet or the.

For the past three decades, the Shelly Cashman Series r has effectively introduced computers to millions of students, consistently providing the highest quality, most up-to-date, and innovative materials in computer education.