|
|
|
SOUNDING THE ALL CLEAR! To get rid of just the contents of an Excel spreadsheet cell selection (rather than deleting the cells along with their contents), select the range of cells to be cleared and press Delete or choose Edit, Clear, Contents Del from the menu bar. If you want to get rid of more than just the contents of a cell selection, choose Edit, Clear from the menu bar and then choose from among the submenu commands:
USING AUTOFILL TO COMPLETE A SERIES IN EXCEL In Excel, to use AutoFill to enter a series of incremental values, follow these steps.
As you drag the fill handle, Excel displays a small box that tells you what it's planning to enter into each cell. Remember: After you complete the drag operation, Excel displays the AutoFill Options Smart Tag. You can click the Smart Tag to select a different fill option. For even more control, drag the fill handle while pressing the right mouse button. After you release the button, you see a list of options to choose from.
GETTING CREATIVE WITH CUSTOM AUTOFILTERS You can create custom AutoFilters that enable you to filter your Excel database records that meet un-exacting criteria (such as last names starting with the letter M) or ranges of values (such as salaries between $25,000 and $50,000 a year). To create a custom filter for a field, click the field's drop-down list button and then select the Custom option at the top of the pop-up list box -- between Top 10 and the first field entry in the list box. In the dialog box that appears, select the operator that you want to use in the first drop-down list box, then enter the value (text or numbers) that should be met, exceeded, fallen below, or not found in the records of the database in the text box to the right. Note that you can select any of the entries made in that field of the database by choosing the drop-down list button and selecting the entry in the drop-down list box. If you only want to filter records in which a particular field entry matches, exceeds, falls below, or simply is not the same as the one you enter in the text box, you then click OK or press Enter to apply this filter to the database. However, you can use the Custom AutoFilter dialog box to filter the database to records with field entries that fall within a range of values or meet either one of two criteria. To set up a range of values, you select the "is greater than" or "is greater than or equal to" operator for the top operator and then enter or select the lowest (or first) value in the range. Then make sure that the And radio button is selected and select "is less than" or "is less than or equal to" as the bottom operator and enter the highest (or last) value in the range. To set up an either/or condition in the Custom AutoFilter dialog box, you normally choose between the "equals" and "does not equal" operators (whichever is appropriate) and then enter or select the first value that must be met or must not be equaled. Then you select the Or radio button and select whichever operator is appropriate and enter or select the second value that must be met or must not be equaled. For example, if you want to filter the database so that only records for states WA (Washington) or IL (Illinois) are displayed, you select "equals" as the first operator and then select or enter WA as the first entry. Next, you select the Or radio button, select "equals" as the second operator, and then select or enter IL as the second entry. When you then filter the database by clicking OK or pressing Enter, Excel displays only those records with either WA or IL as the code in the State field.
YOU AUTOFIT IN EXCEL Excel's AutoFit feature determines the best fit for the column or columns selected at that time, given their longest entries.
GET TO KNOW AUTOSUM The AutoSum tool on the Standard toolbar is worth its weight in gold -- it does more than just enter the SUM function. It also selects the most likely range of cells in the current column or row that you want totaled and automatically enters them as the function's argument. And nine times out of ten, Excel selects (by highlighting) the correct cell range to be totaled. For that tenth case, you can manually correct the range by simply dragging the cell pointer through the block of cells that need to be summed. Position the cell pointer in the cell where the sum is to appear, and click the AutoSum tool (the button looks like a stylized capital letter E). Excel inserts the SUM function (equal sign and all) onto the Formula bar; places a marquee (the moving dotted line) around the cells it thinks you want to add; and uses that cell range as the argument of the SUM function.
BRING ON THE BORDERS! To emphasize sections of your Excel worksheet or parts of a particular table, you can add borderlines or shading to certain cells. Don't confuse the borderlines that you add to accent a particular cell selection with the gridlines normally used to define cell borders in the worksheet -- borders that you add are printed whether or not you opt to print the worksheet gridlines. To add borders to a cell selection, choose Format, Cells. Click the Border tab from the Format Cells dialog box that opens. Select the type of line you want to use in the Style area of the dialog box (such as thick, thin, bold, or hash marks) and then select from the Border section of the dialog box the edge or edges you want this line applied to. When selecting where you want the borderlines drawn, keep these points in mind:
To get rid of borders, you must select the cell or cells that presently contain them, open the Format Cells dialog box, and click the None button in the Presets section. Note that you can also do the same thing by clicking the first button in the Borders pop-up menu (the one showing only dotted lines around and within the rectangle).
HIDING CELL CONTENTS IN EXCEL You can "hide" the contents of a cell by using the following formatting options:
Both formatting techniques have the same flaw: If the cell pointer is on the cell, its contents are visible in the Formula Bar.If you want to avoid this flaw and make the cell contents truly invisible, follow these steps:
CELL VERSUS FORMULA BAR EDITING IN EXCEL Excel gives you a choice between editing a cell's contents either in the cell or on the Formula bar. Whereas most of the time, editing right in the cell is just fine, when dealing with really, really long entries (like humongous formulas that seem to go on forever or text entries that take up paragraphs and paragraphs), you may prefer to do your editing on the Formula bar. This is because Excel expands the Formula bar to as many rows as necessary to display the entire cell contents; in the worksheet display, however, the cell contents may be running right off the screen. To edit the contents in the Formula bar rather than in the cell itself, you must position the cell pointer in the cell and then double-click somewhere (probably the first place that needs changing) in the cell contents on the Formula bar.
NAMING A CELL RANGE By assigning descriptive names to cells and cell ranges, you can go a long way toward keeping on top of the location of important information in a worksheet. Rather than trying to associate random cell coordinates with specific information, you just have to remember a name. And, best of all, after you name a cell or cell range, you can use this name with the Go To feature. To name a cell or cell range in a worksheet:
TRANSPOSING A CELL RANGE IN EXCEL If you need to change the orientation of a cell range, Excel can do it for you in a snap. If you transpose a range, rows become columns and columns become rows. To transpose a range, follow these steps:
Remember that Excel adjusts any formulas in the original range so that they work correctly after you transpose them.
CELLS: THE BUILDING BLOCKS OF WORKSHEETS The cells in an Excel worksheet are formed by the intersection of the column and row grid. Technically, such an arrangement is known as an array. An array keeps track of different pieces of information stored in it by referring to its row position and its column position. To display your worksheet data in its grid and tabular format, Excel just reads from the row and column position associated with the data that you enter there.
MERGING CELLS - OH, SO SIMPLE! Excel offers a helpful feature that enables you to merge cells into a single, larger cell. This feature enables you to have cells of unequal sizes. If you have a table that spans six columns, for example, you can merge six cells at the top to form a single larger cell for the table's title. Following are some of the things to remain aware of in merging cells:
To merge a range of cells, follow these steps:
To unmerge cells, select the cells and deselect the Merge Cells check box on the Alignment tab of the Format Cells dialog box.
REFERENCING CELLS IN OTHER WORKSHEETS If your Excel formula needs to refer to a cell in a different worksheet in the same workbook, use the following format for your formula.
Precede the cell address with the worksheet name, and follow it with an exclamation point. Remember: If the worksheet name in the reference includes one or more spaces, you must enclose it in single quotation marks. The following is a formula that refers to a cell on a sheet by the name of All Depts:
CHANGE AN EXCEL CHART TO A GRAPHIC Pasting an Excel chart as a graphic is a good option if you're sure the data will never change. You can add such a graphic into a PowerPoint presentation or Microsoft Word document. A chart pasted as a graphic can be moved and resized. When you double-click a chart graphic, the Microsoft Office picture-editing tools and settings become available. Your chart is now a graphic - any connection to Excel is gone. If you just need a graphic of an Excel chart, you can use a great shortcut:
CUSTOMIZING A CHART WITH A PICTURE In Excel, you can put a picture into the chart area or plot area. The method is to select a graphic file on your system and the picture becomes the background of the chart area or plot area. Be sure to use a picture that doesn't make reading the chart difficult. Here's how to put a picture in the chart area:
ADDING COMMENTS TO EXCEL CELLS In Excel, you can add comments to the current cell by one of two ways. You can either click the New Comment button on the Reviewing toolbar or choose Insert, Comment on the Excel menu bar. Excel responds by adding a comment box with your name listed at the top (or the name of the person who shows up in the User Name text box on the General tab of the Options dialog box). You can then type the text of your comment in this box. When you finish typing the text of the note, click the cell to which you're attaching the note or any other cell in the worksheet to close the Comment box. Excel indicates that you've attached a comment to a worksheet cell by adding a red triangle to its upper-right corner. To display the Comment box with its text, position the thick, white-cross mouse pointer on this red triangle, or position the cell pointer in its cell and then click the Show Comment button on the Reviewing toolbar.
HANDLING MISSING DATA IN AN EXCEL CHART Sometimes, data that you're charting may be missing one or more data points. Excel offers several options for handling the missing data. Just follow these steps:
Remember: The options that you set apply to the entire active chart; you can't set a different option for different series in the same chart.
ADDING A LEGEND TO A CHART If you create your chart by using the Chart Wizard in Excel, you can include a legend. If you don't include a legend as you create the chart, you can add one later if you need one. To add a legend, follow these steps:
A CHART OF LEGENDARY PROPORTIONS A chart legend in Excel lists each pattern or symbol used as a data marker in a chart and follows the pattern or symbol with the chart data series name. Generally, it's a good idea to use a legend in a chart where you're not using category labels. You can customize the legend that you add to the chart in various ways. You can click the Legend button on the Chart toolbar to add or delete a legend for your chart. Click the Legend tool to display a legend if none is showing or to delete a legend if one is showing. When you select a legend in a chart, it appears with sizing handles around it that enable you to resize the legend markers and text. To move the legend, click the arrowhead pointer within its borders, and then drag its rectangle to the position you want. Be careful to drag the legend to a blank part of the chart area where it doesn't overlap and obscure other chart elements. As with chart text, you can change the border around the legend and the pattern within the area of the legend box. The easiest way to make the changes is to double-click the legend to open the Format Legend dialog box and then use the options on the Patterns tab to format its border and background as you want.
ROTATING A PIE OR DOUGHNUT CHART In Excel, a formatting option is available for Pie and Doughnut charts to rotate the chart by setting the angle of the first slice. The setting doesn't change any angles of the slices relative to each other -- it just lets you position the sequence of slices along the circumference. Here's how to rotate a Pie or Doughnut chart:
When formatting a Doughnut chart, you can also specify how large the doughnut hole is. The possible values are from 10 percent (a very small hole) to 90 percent (a very large hole).
CHANGING THE CHART TYPE IN EXCEL If you want to change the type of Excel chart, such as changing from a Column chart to a Bar chart, here's what you do.
Presto! The Column chart is now a Clustered Bar chart.
UNDERSTANDING THE STANDARD CHART TYPES Excel provides several chart types to use for displaying data. You can select a chart type when using the Chart Wizard to create a chart, or when the Chart Type dialog box is displayed while editing an existing chart. The standard chart types are summarized here.
Different chart types are used to display different facts about data. There is an art to selecting the right type of chart. How many series are in the data? Does one series correlate to another? (For example, does a drop in temperature cause more fractured ankles?) Do you need to emphasize the dominant factor of a sum of items? An incorrect chart type can produce a great-looking chart and yet still not display the correct information.
ADDING PERSPECTIVE TO YOUR CHARTS Perspective provides Excel charts with an effect of objects that are closer appearing larger and those that are farther away appearing smaller. In other words, perspective can add depth to a chart. This, of course, simulates the way you see things out in the world. Charts don't necessarily need this treatment, but it's available for you to use. Here's how to adjust the perspective of a 3-D chart:
The use of perspective can work against you. The use of perspective may make it seem that the data points are not equal in value, although in fact they're identical.
ROTATING EXCEL CHARTS Sure, they're neat to look at, but as you work with 3-D charts in Excel, some data may be completely or partially obscured. How can you convince the bigwigs that you aren't inept? Luckily for you, you can rotate the chart so that it shows the data better. To do so, follow these steps:
QUESTIONABLE CIRCUMSTANCES The first place to go for help in Excel is the Ask a Question box that appears on the right side of the Excel Menu bar. This combo box is your ever-present key to accessing the Answer Wizard, a component of the Excel Help system that tries to respond intelligently to your queries by suggesting related help topics to which you may want to refer. Although it's called the Ask a Question box, you really don't have to ask a formal question; entering a phrase with key terms will usually do just as well. For example, instead of typing out the formal question, "How do I get my spreadsheet headings to print on every page of my report?" you can simply enter the keyword phrase "print headings." To use the Ask a Question box to consult the Answer Wizard, follow three easy steps:
AUTOFIT A COLUMN TO ITS CONTENTS The easiest way to adjust the width of a column to suit its longest entry is to use Excel's AutoFit feature. AutoFit determines the best fit for the column or columns selected at that time, given their longest entries.
These AutoFit techniques work well for adjusting all columns except for those that contain really long headings (such as the spreadsheet title that often spills over several blank columns in row 1), in which case, AutoFit makes the columns far too wide for the bulk of the cell entries.
COPYING EXCEL COLUMN WIDTHS Although the Format Painter in Excel makes copying from one cell to another range a real breeze, copying formats isn't its only claim to fame. You can also use the Format Painter to copy column widths in a worksheet. To do this, you click the letter of the column whose width you want to copy in the column header and then double-click the Format Painter button to engage it. Finally, click or drag through the letters of the columns that need to be the same width, and then click the Format Painter to disengage it.
COPYING EXCEL DATA TO WORD The easiest way to embed an Excel table of worksheet data or an Excel chart into a Word document is to use the drag-and-drop method. Simply highlight the cells or chart you want to copy, hold down the Ctrl key, and drag the selected cells or chart between the Excel and Word program windows. The only trick to dragging and dropping between programs is the sizing and maneuvering of the Excel and Word program windows themselves -- that is, you have to arrange the windows so that you can see both of them. You can also use the cut-and-paste method to embed worksheet data into a Word document. Simply select the cells in Excel and then copy them to the Clipboard (Ctrl+C). Then, open the Word document and position the cursor at the place where the spreadsheet table is to appear. Choose Edit, Paste Special from the Word menu bar. Click Microsoft Excel Worksheet Object in the As list box and then click OK. Word then embeds the data in the body of the Word document just as though you can Ctrl-dragged the data from the Excel window.
CALIBRATING COLUMNS For those times when Excel doesn't automatically adjust the width of your columns to your complete satisfaction, the program makes your changing the column widths a breeze. The easiest way to adjust a column is to do a best-fit, using the AutoFit feature. With this method, Excel automatically determines how much to widen or narrow the column to fit the longest entry currently in the column. Here's how to use AutoFit to get the best-fit for a column:
You can apply a best-fit to more than one column at a time. Simply select all the columns that need adjusting (if the columns neighbor one another, drag through their column letters on the frame; if they don't, hold down the Ctrl key while you click the individual column letters). After you select the columns, double-click any of the right borders on the frame.
CREATING A DATA FORM FROM FIELD NAMES ALONE You can create a data form for a new database simply by entering a row of field names and then positioning the cell pointer in the first one before you choose Data, Form from the menu bar. When you do this, Excel displays the alert dialog box indicating that the program can't determine which row in your list contains the column labels (that is, the field names). To have Excel use the selected row as the field names, click OK or press Enter. Excel will then create a blank data form listing all the fields down the form in the same order as they appear across the selected row. Creating a blank data form from field names alone is just fine, provided that your database doesn't contain any calculated fields (that is, fields with entries that result from a formula's computation rather than from manual entry). If your new database will contain calculated fields, you need to build the formulas for the fields in the appropriate fields of the first record. Then select both the row of field names and the first database record with the formulas indicating how the entries are calculated before you choose Form on the Data menu. Excel knows which fields are calculated and which are not. (You can tell that a field is a calculated field in the data form because Excel lists its field name but does not provide a text box for you to enter any information for it.)
DEALING WITH MISSING DATA Sometimes, data that you're charting in Microsoft Excel may be missing one or more data points. Excel offers several options for handling the missing data. Just follow these steps:
INSERTING AUTOMATIC DECIMAL POINTS IN EXCEL If you're entering lots of numbers with a fixed number of decimal places, you can save some time by having Excel enter the decimal point. (This feature is similar to the feature that is available on some adding machines and office calculators.) To do so, follow these steps:
Excel subsequently supplies the decimal points for you automatically. If you have it set for two decimal places and you enter 12345 into a cell, for example, Excel interprets it as 123.45. To restore things to normal, just deselect the Fixed Decimal check box in the Options dialog box. Remember: Changing this setting doesn't affect any values that you've already entered.
DOCUMENT RECOVERY TO THE RESCUE Excel offers a document recovery feature that can help you in the event of a computer crash because of a power failure or some sort of operating system freeze or shutdown. The AutoRecover feature saves your workbooks on regular intervals. In the event of a computer crash, Excel displays a Document Recovery task pane the next time you start Excel after rebooting your computer. When you first start using Excel, the AutoRecover feature is set to automatically save changes to your workbook every ten minutes. You can shorten or lengthen this interval as you see fit. Choose Tools, Options, and then click the Save tab. Use the spinner buttons or enter a new automatic save interval into the text box marked "Save AutoRecover Info Every 10 Minutes" before clicking OK. Note that the AutoRecover feature works only on Excel workbooks that you have saved at least one time. In other words, if you build a new workbook and don't bother to save and rename it prior to experiencing a computer crash, the AutoRecover feature will not bring back any part of it. For this reason, it is very, very important that you get into the habit of saving new workbooks with File, Save very shortly after beginning work on one of its worksheets. Or, you can use the trusty keyboard shortcut Ctrl+S.
DRAG-AND-DROP FUN IN EXCEL In Excel, drag-and-drop spreadsheet editing is most effective in two situations: when you not only need to move or copy a range of cells to a new (blank) place in the worksheet, but also when you want to insert the cells into an existing table of data. To do this kind of insert moving or copying, you must hold down the Shift key as you drag the cell selection to its new position. Excel indicates where the selected data will be inserted at the time you drop it by displaying an I-beam-type indicator in front of the mouse pointer:
PERSNICKETY DRAG-AND-DROP Drag-and-drop in Insert mode is one of Excel's more finicky features. Sometimes, you do everything just right and you still get the alert box indicating that Excel is about to replace existing entries instead of pushing them aside (always click the Cancel button). Fortunately, you can insert things with the Cut (Alt, C) and Insert Paste (Alt, I) commands without worrying about which way the I-beam selection goes.
E-MAIL AN EXCEL WORKBOOK You've finally finished your Excel spreadsheet and now you want to share its wondrous beauty by e-mailing it to a colleague. Here's how to spread the glory:
EXPORTING A TEXT FILE IN EXCEL If you want to save information in an Excel worksheet so that it can be used by other programs that can't read Excel files directly, you can export your worksheet as a text file. Most applications can read text files. To export a worksheet as a text file, follow these steps:
When you save a workbook as a text file, be aware that text files simply contain data. These files have no formulas, formatting, or charts. If you want to save your formulas to a text file, you can. Select Tools, Options and click to place a check mark in the Formulas check box. This action displays formulas (instead of their resultant values) in your spreadsheet. Then save your spreadsheet as a text file. In some cases, you may be able to use the Office Clipboard to copy data from Excel and paste it directly to the other application.
FORMAT FONTS ON AN EXCEL CHART In an Excel chart, you can easily format the font for an individual axis. Just follow these steps to make changes to the font:
Repeat Steps 1 through 4 to try different settings. The selected font settings are applied to just the single axis. The other axis or axes can be formatted in the same way. Note the Auto Scale check box. When checked, the font size is enlarged or reduced as the chart is resized. When unchecked, the font size remains unchanged as the chart is resized.
USING DATA ENTRY FORMS If you're entering data that is arranged in rows, you may find it helpful to use Excel's built-in data form for data entry. To enter data using a data entry form, follow these steps.
COPYING JUST THE FORMAT IN EXCEL In Excel, you can copy just the formatting applied to a cell or cell range and -- leaving behind the contents -- paste that formatting into a new range. The range into which you copy the formatting can be blank or can contain cell entries. If the range is blank, all cell entries that you make there take on the copied formatting. If the range already contains cell entries, the copied formatting is immediately applied to them, replacing any existing formatting. To copy just the formatting from a cell or cell range to a new place in the worksheet, follow these steps:
COPYING EXCEL FORMATTING In Excel, you can copy just the formatting applied to a cell or cell range. You leave behind the cell contents and paste that formatting into a new range. Here's how:
FOOLING AROUND WITH FORMAT PAINTER In Excel, when you feel the urge to format on the fly (so to speak), use the Format Painter on the Standard toolbar (the button that looks like a paintbrush right next to the Paste tool). This wonderful little tool enables you to take the formatting from a particular cell that you've fancied up and apply its formatting to other cells in the worksheet simply by selecting those cells. To use the Format Painter to copy a cell's formatting to other worksheet cells, just follow these easy steps:
To keep the Format Painter selected so that you can format a bunch of different cell ranges with the Format Painter pointer, double-click the Format Painter button after you select the sample cell with the desired formatting. To stop formatting cells with the Format Painter pointer, you simply click the Format Painter button (it remains depressed when you double-click it) again to restore the button to its undepressed state and return the mouse pointer to its normal thick, white-cross shape.
HIRING OUT THE FORMAT PAINTER The Format Painter button (the one with the paintbrush icon takes formatting from the current cell and apply it to cells that you "paint" by dragging its special thick-white-cross-plus-paintbrush mouse pointer through them. This tool provides a quick and easy way to take a bunch of different formats (such as a new font, font size, bold, and italics) that you applied individually to a cell and apply them to other cells. To use the Format Painter, follow these steps:
The moment you release the mouse button, the cells in the selected range become formatted the same way as the sample cell.
CREATING CUSTOM NUMBER FORMAT CODES You can create your own custom format codes by doing a little creative editing. Although you do have to use these junky number format codes (ugh) when performing this editing, you really don't have to be a rocket scientist to figure out how to do it. To build a custom hidden number format, choose the Format Cells dialog box (Ctrl+1), click Custom in the Category list box in the Number tab of the Format Cells dialog box, and then select General in the Type list box and replace this text with your custom code. Note that the custom number formats that you create are added to the bottom of the Type list box. This means that when you next open the Format Cells dialog box and select Custom in the Category list box of the Number tab, you may have to scroll through the Type list box all the way to the bottom before you can locate and select the number format codes that you're responsible for adding.
A NUMBER OF SPECIAL FORMATS Excel contains a nifty category of number formats called Special. The Special category contains the following four number formats that may interest you.
These Special number formats really come in handy when creating databases in Excel, which often deal with stuff like zip codes, telephone numbers, and sometimes even Social Security numbers.
DISPLAYING FORMULAS IN EXCEL One way to audit your Excel workbook is to display the formulas rather than the results of the formulas. Then you can examine the formulas without needed to scroll through the worksheet. To display formulas instead of the formula results (values), choose Tools, Formula Auditing, Formula Auditing Mode. You may want to create a new window for the workbook before issuing the command. That way, you can see the formulas in one window and the results in the other.
TRACING FORMULA ERROR VALUES Often, in Excel, an error in one cell (for example, #DIV/0!, #VALUE!, #NA, and so on) is the result of an error in a precedent cell. Excel helps you identify the cells or cells that are causing the error value to appear. To trace the source of the error value, follow these steps:
You can also click the Remove All Arrows button on the Formula Auditing toolbar.
CONVERTING FORMULAS TO VALUES Sometimes in Excel, you may want to convert a formula to its current value (in other words, remove the formula and leave only its result). To do so, follow these steps.
Remember: The preceding procedure overwrites the formulas. If you want to put the current values of the formulas in a different (empty) area of the worksheet, select a different range before Step 3 in the preceding list.
CREATE FORMULAS USING THE POINTING METHOD The pointing method of entering a formula in Excel still involves some manual typing. The advantage is that you don't need to type the cell or range references. Instead, you point to them in the worksheet, which is usually more accurate and less tedious. The best way to explain this procedure is with an example. Follow these steps to enter the formula =A1/A2 into cell A3 by using the pointing method:
USING THE IF FUNCTION IN EXCEL IF is a very handy function. It tests a condition and then returns one of two results depending on the outcome of the test. The test must return a true or false answer. For example, a test may be B25 > C30. If true, then IF returns its second argument. If false, IF returns its third argument. IF is often used as a validation step to avoid unwanted errors. The most common use of this is to test if a denominator is 0 before doing a division operation. By testing for 0 first, you can avoid the #DIV/0! error. One of the great things about IF is that the result can be a blank. This function is great for when you only want to return a result if the test comes out one way but not if the test comes out the other way. The syntax is:
CHANGING THE HEADER OR FOOTER IN EXCEL A header is information that appears at the top of each printed page. A footer is information that appears at the bottom of each printed page. Headers and footers each have three sections in Excel: left, center, and right. You can, for example, specify a header that consists of your name left-justified, the worksheet name centered, and the page number right-justified. Remember: In Excel, the default is no header or footer. To select a predefined header or footer, follow these steps:
To define a custom header or footer, follow these steps:
OGLING ONLINE EXCEL HELP You can get online help with Excel 2003 anytime that you need it while using the program. The only problem with the traditional online Help system is that it is only truly helpful when you are familiar with the Excel jargon. If you don't know what Excel calls a particular feature, you'll have trouble locating it in the Help topics (just like trying to look up in a dictionary a word that you have no idea how to spell). To help alleviate this problem, Excel makes use of the Answer Wizard to enable you to search for information on Microsoft's Web site. In your own words, you can type in a question about how to do something in Excel. The Answer Wizard then attempts to translate your question, phrased in perfectly good English, into its horrible Excel technobabble so that it can then display the Help topics that give you the information you need.
REMOVING HYPERLINKS If you want to remove a hyperlink from a cell entry or graphic object in Excel without getting rid of the text entry or the graphic, right-click the cell or graphic and then click the Remove Hyperlink item on the cell's or object's shortcut menu. If you want to clear the cell of both its link and text entry, click the Delete item on the cell's shortcut menu. To get rid of a graphic object along with its hyperlink, right-click the object (this actions opens its shortcut menu) and then immediately click the object to remove the shortcut menu without either deselecting the graphic or activating the hyperlink. At this point, you can press the Delete key to delete both the graphic and the associated link.
ON THE IMAGE TRAIL IN EXCEL 2003 The Excel 2003 Clip Art task pane that automatically appears when you choose Insert, Picture, Clip Art on the menu bar or click the Insert Clip Art button on the Drawing toolbar enables you to use key words to search for different types of images stored in your computer's Media Gallery. Results of the search then appear as thumbnails in the task pane, which you click to insert into the current worksheet.
USING KEYBOARD SHORTCUTS IN EXCEL You can forget all about those darned tab scrolling buttons and sheet tabs in Excel and just go back and forth through the sheets in a workbook with your keyboard. To move to the next worksheet in a workbook, press Ctrl+PgDn. To move to the previous worksheet in a workbook, press Ctrl+PgUp. The nice thing about using these keyboard shortcuts is that they work whether or not the next or previous sheet tab is currently displayed in the workbook window!
KEEPING TRACK OF SAVED MACROS Where Excel saves your macro depends on how you created the macro in the first place.
FLOATING THE EXCEL MENU BAR Toolbars, like the Standard and Formatting toolbars, aren't the only things that float in Excel. You can even float the Excel menu bar containing all the pull-down menus. When you select a menu on a floating menu bar, its commands may appear above the bar next to the menu's name rather than below the bar (as is normal), depending upon how much room there is between the floating bar and the bottom of the screen. To float a toolbar on the screen, position the mouse pointer somewhere on the bar that appears at the very front of the toolbar (this bar looks fuzzy because it's made up of tiny, gray horizontal lines), hold down the mouse button, and drag the toolbar where you desire. To restore a floating toolbar to its original docked position on the screen, drag the toolbar's title bar to that location.
MINDING YOUR MOUSE MANNERS Windows programs such as Excel use three basic mouse techniques to select and manipulate various objects in the program and workbook windows. Here's a sampling:
When clicking an object to select it, you must make sure that the tip of the mouse pointer is touching the object you want to select before you click. To avoid moving the pointer slightly before you click, grasp the sides of the mouse between your thumb (on one side) and your ring and little fingers (on the other side), and then click the primary button with your index finger. If you run out of room on your desktop for moving the mouse, just pick up the mouse and reposition it on the desk (which does not move the pointer).
JUST NAME IT! Name each Excel worksheet that you use in a workbook with some intelligible English name such as Invoice or Price List (by double-clicking the sheet tab, entering a new name on the tab, and then pressing Enter) rather than leaving them with their normally indecipherable names like Sheet1 and Sheet2. That way, you not only know which sheets in your workbook you've already used, but you also have a fair idea of what they contain and when you should select them.
SOME SORTA ORDER When you use the ascending sort order with a key field that contains many different kinds of entries, Excel places numbers (from smallest to largest) before text entries (in alphabetical order) . This is followed by any logical values (TRUE and FALSE), error values, and finally, blank cells. When you use the descending sort order, Excel arranges the different entries in reverse: Numbers are still first, arranged from largest to smallest; text entries go from Z to A; and the FALSE logical value precedes the TRUE logical value.
FREEZING PANES Window panes are great for viewing different parts of the same worksheet that normally can't be seen together. You can also use window panes to freeze headings in the top rows and first columns so that the headings stay in view at all times, no matter how you scroll through the worksheet. Frozen headings are especially helpful when you work with a table that contains information that extends beyond the rows and columns shown on-screen. To create and freeze window panes, position the pointer in the cell after the one you want to freeze. For example if you want all the content in row 1 to be frozen, place your pointer in a cell in row 2. Choose Window, Freeze Panes on the menu bar. Note: If you want to freeze the panes in column C, row 5, then place your pointer in cell D6.
NESTING PARENTHESES You don't have to worry too much about nesting parentheses in an Excel formula if you don't pair them properly. For example, if you don't include a right parenthesis for every left one, Excel displays an alert dialog box that suggests the correction that needs to be made to balance the pairs. If you agree with the program's suggested correction, you simply select the Yes button.
ADDING A PICTURE TO AN EXCEL CHART You can put a picture into the chart area or plot area of an Excel chart. The method is to select a graphic file on your system, and the picture becomes the background of the chart area or plot area. Be sure to use a picture that doesn't make reading the chart difficult. Here's how to put a picture in the chart area:
MODIFYING A PIVOTTABLE'S STRUCTURE IN EXCEL A PivotTable report that appears in an Excel worksheet includes the field buttons. You can drag any of the field buttons to a new position in the PivotTable report. (This action is known as pivoting). You can, for example, drag a column field to a row position. Excel immediately redisplays the PivotTable report to reflect your change. You can also change the order of the row fields or the column fields by dragging the buttons. This action affects how the fields nest and can have a dramatic effect on how the table looks. Remember: A PivotTable is a special type of range, and (except for formatting) you can't make any changes to it. You can't, for example, insert or delete rows, edit results, or move cells. If you attempt to do so, Excel displays an error message. You can also drag fields from the PivotTable Field List to modify an existing PivotTable's structure.
POINTER PLACEMENT Excel automatically advances the cell pointer to the next cell down in the column every time you press Enter to complete the cell entry. If you want to customize Excel so that pressing Enter doesn't move the cell pointer as the program enters your data, or to have it move the cell pointer to the next cell up, left, or right, choose Tools, Options on the menu bar, then select the Edit tab in the Options dialog box. To prevent the cell pointer from moving at all, choose the "Move selection after Enter" checkbox to remove its check mark. To have the cell pointer move in another direction, choose the Direction pop-up list box right below and then select the new direction you want to use (Right, Up, or Left). When you finish changing the settings, click OK or press Enter.
CLEARING THE PRINT AREA Microsoft Excel includes a special printing feature called Print Area. You can use the File, Print Area, Set Print Area command to define any cell selection on a worksheet as the Print Area. After the Print Area is defined, Excel then prints this cell selection anytime you print the worksheet. Whenever you fool with the Print Area, you need to keep in mind that once defined, its cell range is the only one you can print until you clear the Print Area. To clear the Print Area (and therefore go back to the printing defaults), you just have to select File, Print Area, Clear Print Area on the menu bar. You can also define and clear the Print Area from the Sheet tab of the Page Setup dialog box. To define the Print Area from this dialog box, insert the cursor in the Print Area text box on the Sheet tab and then select the cell range or ranges in the worksheet. To clear the Print Area from this dialog box, select the cell addresses in the Print Area text box and press the Delete key.
AUDIBLE PROOFREADING Excel comes with a text-to-speech feature that you can use as an aid in proofreading your spreadsheet data. This tool is useful for the sight-impaired but can also help if you're entering a lot of data manually -- for example, from a printed sheet. While you're entering the data into the range, you can look at the printed sheet while Excel confirms each entry audibly. Or, if you've already entered the data, you can check the printed sheet while Excel speaks out each entry. To use the text-to-speech feature, you need to display the Text to Speech toolbar. Follow these steps to display the toolbar and use its options:
Remember: Your PC must have the appropriate equipment for the text-to-speech feature to work. At minimum, you must have a sound card with speakers or a pair of headphones. The Speak On Enter tool persists across Excel sessions -- that is, if you enable it during one session and shut down Excel, it's still enabled the next time that you start Excel.
PROTECT AND SHARE IN EXCEL If you create an Excel workbook with contents to be updated by several different users on your network, you can use the Protect and Share Workbook command from the Tools->Protection submenu. This command ensures that Excel tracks all the changes made and that no user can intentionally or inadvertently remove Excel's tracking of changes made to the file. To do this, you simply select the Sharing with Tracked Changes check box in the Protected Shared Workbook dialog box that appears after you choose the command. After you select this check box, you can add a password in the Password (optional) text box below that each user must supply before he or she can open the workbook to make any changes.
PROTECTING A WORKBOOK Sometimes, you may want to protect an Excel workbook by preventing users from adding or deleting sheets. Or, you may want to ensure that the workbook's window size or position is not changed. Here's how:
To remove protection from a protected workbook, choose the Tools, Protection, Unprotect Workbook command.
PROTECT YOUR EXCEL WORKSHEET Deleting a single formula in an Excel worksheet often creates a ripple effect, causing other formulas to produce an error value or, even worse, incorrect results. Circumvent such problems by locking the cells that you don't want to be modified and then protecting your worksheets from modification by following these steps:
A RANGE BY ANY OTHER NAME Cells are identified by their position on the grid of an Excel spreadsheet - their so-called cell address. This address is normally noted with the cell's column letter followed by its row number. So the first cell (located at the intersection of the first column and row) in every Excel spreadsheet has the address A1. Cell ranges are always noted in formulas by the first and last cell that you select, separated by a colon (:); therefore, if you select cell A1 as the first cell and H10 as the last cell, and then use the range in a formula, the cell range appears as A1:H10. This same block of cells can just as well be noted as H10:A1 if you selected cell H10 before cell A1. Likewise, the same range can be equally noted as H1:A10 or A10:H1, depending on which corner cell you select first and which opposite corner you select last. Keep in mind that despite the various range notations that you can use (A1:H:10, H10:A1, H1:A10, A10:H1), you are working with the same block of cells, the main difference being that each has a different active cell whose address appears in the Name box on the Formula bar (A1, H10, H1, and A10, respectively).
RELATIVE REFERENCES You need to be able to distinguish between relative and absolute cell references. By default, Excel creates relative cell references in formulas except when the formula includes cells in different worksheets or workbooks. The distinction becomes apparent when you copy a formula to another cell. Let's say the following formula, which uses the default relative references, appears in cell D2:
Now when you copy this formula to the two cells below it, Excel doesn't produce an exact copy of the formula; rather, it generates these formulas:
Excel adjusts the cell references to refer to the cells that are relative to the new formula. Think of it like this: The original formula contained instructions to multiply the value two cells to the left by the value one cell to the left. When you copy the cell, these instructions get copied, not the actual contents of the cell. Usually, this is exactly what you want. You certainly don't want to copy the formula verbatim; if you did, the new formulas would produce the same value as the original formula. When you cut and paste a formula (move it to another location), the cell references in the formula aren't adjusted. Again, this is what you usually want to happen. When you move a formula, you generally want it to continue to refer to the original cells.
CALCULATED RISKS Applying a number format to a cell doesn't change the value - only how the value appears in the worksheet. For example, if a cell contains .874543 you might format it to appear as 87%. If that cell is used in a formula, the formula uses the full value (.874543), not the displayed value (.87). In some situations, formatting may cause Excel to display calculation results that appear incorrect, such as when totaling numbers with decimal places. For example, if values are formatted to display two decimal places, you may not see the actual numbers that are used in calculations. But because Excel uses the full precision of the values in its formula, the sum of two values may appear to be incorrect. You can format the cells to display more decimal places. You can use the ROUND function on individual numbers and specify the number of decimal places Excel should round to. Or you can instruct Excel to change the worksheet values to match their displayed format. To do this, choose Tools, Options, select the Calculation tab, and then check the Precision as displayed check box.
CALCULATING SUBTOTALS IN EXCEL Excel has the capability to create subtotal formulas automatically; this is handy and can save you lots of time. The formulas all use the Subtotal worksheet function to insert the subtotals. To use the subtotal feature, you must have a list that's sorted. To insert subtotal formulas into a list, follow these steps:
INSERTING ROWS AND COLUMNS IN EXCEL If you insert new rows or columns in Excel, the program places blank rows or columns in the worksheet, and surrounding rows or columns move out to accommodate the new rows or columns. To insert new rows or columns in your worksheet, follow these steps:
You can also insert entire rows or columns by selecting the entire rows or columns first. Then use any of the methods in Step 2 to choose the Insert command. Excel deletes the rows or columns without displaying the Insert dialog box. USING SCENARIO MANAGER IN EXCEL Excel's Scenario Manager feature makes it easy to automate your what-if models. (What-if analysis is the process of changing one or more input cells and observing the effects on one or more dependent formulas.) You can store different sets of input values (known as changing cells) for any number of variables and give a name to each set. You can then select a set of values by name, and Excel displays the worksheet by using those values. You can generate a summary report that shows the effect of various combinations of values on any number of result cells. The summary report can be an outline or a PivotTable. To define a scenario, follow these steps:
An excellent practice is to create names for the changing cells, because names make the cells easier to identify in the Scenario Values dialog box. Names also help make scenario reports more readable. Remember: The limit to the number of changing cells for a scenario is 32.
GOING SHEET TO SHEET VIA THE KEYBOARD If you're working with multiple worksheets and need to switch from one to the next, you can forget all about the darned tab scrolling buttons and sheet tabs and just go back and forth through the sheets in a workbook with your keyboard. To move to the next worksheet in a workbook, press Ctrl+PgDn. To move to the previous worksheet in a workbook, press Ctrl+PgUp. The nice thing about using the keyboard shortcuts Ctrl+PgDn and Ctrl+PgUp is that they work whether or not the next or previous sheet tab is currently displayed in the workbook window.
STRAIGHTENING OUT THE SHEETS Spreadsheet programs like Excel 2002 refer to their electronic sheets as worksheets rather than spreadsheets. And, although it is perfectly acceptable (even preferable) to call one of its electronic sheets a worksheet, you never, never refer to Excel as a worksheet program -- it's always called a spreadsheet program. So you can think of Excel as a spreadsheet program that produces worksheets, but not as a worksheet program that produces spreadsheets.
USING A CUSTOM SORT ORDER Excel, by default, has four custom lists, and you can define your own. To sort by a custom list, click the Options button in the Sort dialog box; then select the list from the First Key Sort Order drop-down list. Excel custom lists are as follows:
CHARTING STOCKS IN EXCEL Stock charts in Excel are used to plot stock quotes over a certain period of time, such as a single business day or week. Excel offers you a choice of four subtypes of stock charts.
MERGING STYLES FROM OTHER WORKBOOKS In Excel, you may create one or more styles that you use frequently. Although you could go through the motions and create these styles for every new workbook, a better approach is to merge the styles from a workbook in which you previously created them. To merge styles from another workbook, open both the workbook that contains the styles that you want to merge and the workbook into which you want to merge styles. From the workbook into which you want to merge styles, choose Format, Style and click the Merge button. Excel displays a list of all open workbooks. Select the workbook that contains the styles you want to merge and click OK. Excel copies styles from the workbook that you selected into the active workbook. When you're merging styles, colors are based on the palette stored with the workbook in which you use the style. Therefore, if the two workbooks involved in the merge use different color palettes, the colors used in the styles may not look the same in each workbook.
FORMATTING A TABLE AUTOMATICALLY The Excel AutoFormat feature applies attractive formatting to a table automatically. To use AutoFormat, follow these steps.
You can't define your own AutoFormats, but you can control the type of formatting that Excel applies. If you click the Options button in the AutoFormat dialog box, the dialog box expands to show six options. Initially, the six check boxes are all selected, which means that Excel applies formatting from all six categories. To skip one or more categories, just deselect the appropriate check boxes by clicking them before you click OK.
TIDYING UP YOUR EXCEL TABLES Economy is an important consideration because when you open an Excel workbook, all its data is loaded into your computer's dynamic memory (also known as RAM memory). This may not pose any problems if your computer is one of the latest generation of PCs with more memory than you can conceive of using at one time, but it can pose quite a problem if you share the workbook file with someone whose computer is not so well equipped. Also, depending on just how much data you cram into the workbook, you may even come to see Excel creep and crawl the more you work with it. To help guard against this problem, make sure that you don't pad the data tables and lists in your workbook with extra empty "spacer" cells. Keep the tables as close together as possible on the same worksheet (with no more than a single blank column or row as a separator, which you can adjust to make as wide or high as you like), or - if the design allows - keep them in the same region of consecutive worksheets.
ADDING MORE SMART TAGS IN EXCEL The eServices part of the Microsoft Office Web site has a bunch of different types of Smart Tags that you can download and add for use in Microsoft Office programs, such as Excel. These downloadable (and free) Smart Tags run the gamut from the Expedia Smart Tag - for getting travel-related information by entering destinations - all the way to the FedEx Smart Tag - for tracking your packages by its tracking number. To get new Smart Tags for Excel, click the More Smart Tags button on the Smart Tags tab of the AutoCorrect dialog box (Tools, AutoCorrect). When you do this, Excel opens your Web browser, which then takes you to the Microsoft Office eServices home page, where you can follow links for getting Smart Tags in three major categories: Communication Services, Reference, and News & Travel.
WRAPPING TEXT ENTRIES IN THEIR CELLS You can use the Wrap Text check box in the Text Control section of the Alignment tab to have Excel create a multi-line entry from a long text entry that would otherwise spill over to blank cells to the right. In creating a multi-line entry in a cell, the program also automatically increases the height of its row if necessary to display all the text. When you create multi-line text entries with the Wrap Text option, you can decide where each line breaks by inserting a new paragraph. To do this, put Excel in Edit mode by clicking the insertion point in the Formula bar at the place where a new line should start and press Alt+Enter. When you press the Enter key to return to Ready mode, Excel inserts an invisible paragraph marker at the insertion point that starts a new line both on the Formula bar and within the cell with the wrapped text. If you ever want to remove the paragraph marker and rejoin the text split on different lines, click the insertion point at the beginning of the line that you want to join on the Formula bar and press the Backspace key.
CHANGING TEXT ORIENTATION IN EXCEL Changing text orientation in Excel can be done in a few easy steps. Normally, the contents of a cell appear horizontally. In some cases, you may want to display the text vertically or even at an angle. To change the orientation of text in a cell, follow these steps:
To quickly rotate text 90 degrees downward, select the text and click the vertical box to the left of the orientation gauge. Remember: Excel adjusts the row height to display the text. If you don't want this adjustment, you can use the Merge Cells feature to avoid a larger row height.
CALCULATING ELAPSED TIME IN EXCEL Some spreadsheets require that formulas calculate the amount of elapsed time between a starting and ending time. For example, suppose that you keep a worksheet that records the starting and stopping times for your hourly employees, and you need to calculate the number of hours and minutes that elapses between these two times in order to figure their daily and monthly wages. To build a formula that calculates how much time has elapsed between two different times of the day, subtract the ending time of day from the starting time. For example, you enter a person's starting time in cell B14 and ending time in C14. In cell D14, you would enter the following subtraction formula:
Excel then returns the difference in cell D14 as a decimal value representing what fraction that difference represents of an entire day (that is, a 24-hour period). If for example, cell B14 contains the starting time of 9:15 a.m. and cell C14 contains an ending time of 3:45 p.m., Excel returns the following decimal value in cell D14:
To convert this time of day into its equivalent decimal number, you convert the time format automatically given to it by the General format (Ctrl+Shift+~), which displays the following result in cell D14:
To convert this decimal number representing the fraction of an entire day into the number of hours that have elapsed, simply multiply this result by 24 as in =D14*24, which gives you a result of 6.5 hours when you apply the General format to it.
TIME FUNCTIONS IN EXCEL The HOUR, MINUTE, and SECOND functions enable you to extract specific parts of a time value in an Excel spreadsheet. Each of these three time functions takes a single serial_number argument that contains the hour, minute, or second that you want to extract. So, for example, if cell B5 contains the time 1:30:10 p.m. (otherwise known as serial number 0.5626157) and you enter the following HOUR function in cell C5:
Excel returns 13 as the hour in cell C5 (hours are always returned in 24-hour time). If you then enter the following MINUTE function in cell D5:
Excel returns 30 as the number of minutes in D5. Finally, if you enter the following SECOND function in cell E5:
Excel returns 10 as the number of seconds to cell E5.
FREEZING ROW OR COLUMN TITLES Many Excel worksheets (such as budgets) are set up with row and column headings. As you scroll through such worksheets, you can easily get lost after the row and column headings scroll out of view. Excel provides a handy solution to alleviate this problem: freezing rows and/or columns. To freeze entire rows or columns, follow these steps:
Remember: Excel freezes all rows above the row you select and all the columns to the left of the column you select. Therefore, to freeze a single row and/or column, you must put your row headings in row 1 and your column heading in column A. To unfreeze the frozen rows or columns, choose Window, Unfreeze Panes from the menu bar.
TRANSIENT TOOLBARS Normally, the Standard and Formatting toolbars appear side by side on the second bar at the top of the Excel program in a stationary position politely referred to as being in a docked position. Although, Excel automatically docks these toolbars together at the top of the screen, you are free to move them (as well as other toolbars that you open) around by dragging them into new positions. When you drag the Standard or Formatting toolbar down from its perch and into the work area containing the open workbook, the toolbar then appears in a separate little window. Such toolbars-in-a-window are referred to as floating toolbars because they float like clouds above the open workbook below (how poetic!). And not only can you move these little dears, but you can resize them as well:
ADDING A "TOTAL" ROW IN EXCEL One of the coolest features of an Excel 2003 list is the ability to instantly add a Total row at the bottom of the list. To do this, click the Toggle Total Row button on the List toolbar after you select one of the cells in the Excel list. The moment you click this button, Excel adds a Total row at the bottom of the list. You can then use this row to total columns with numeric entries by following these steps:
If you don't want to sum the items in a particular column, you can have the program perform another type of computation, such as averaging the numbers in the column or counting the items (something you can have Excel do even in columns that contain text entries). All you do is select the appropriate type of calculation -- Average, Count, or any of the other available computations -- from the Total cell's drop-down list.
ADDING TRENDLINES TO AN EXCEL CHART You can add trendlines to your Excel charts that display a trend implied by the charted data. Trendlines are often added to XY (Scatter) charts that correlate two different sets of numerical data to graphically point out the correlation between the two sets. To add a trendline to your chart, you choose Chart, Add Trendline on the Excel menu bar (note that the Chart menu appears on the regular Excel menu bar only when you select an embedded chart). This action opens the Add Trendline dialog box. Here, you can choose the type of trend in the Trend/Regression Type section (Linear, being the most common, is the default) and the data series on which to base the trend in the Based on Series list box.
MUCH UNDO ABOUT SOMETHING One very useful feature in Excel is its multilevel undo. This means that you can reverse your recent actions, one step at a time. For example, if you discover that you accidentally deleted a range of data several minutes ago, you can use the undo feature to "backtrack" through your actions until the deleted range reappears. Undoing your actions can be done only in a sequential manner. In other words, if you want to undo an action you must also undo all of the actions that you performed after the action that you want to undo. You can undo the past 16 operations that you performed. In case you don't know where the undo feature is, head to the Standard toolbar and look for the curved arrow that points to the left. Click it to undo your most recent action.
VIEW EXCEL WORKSHEETS SIDE-BY-SIDE In Excel, you can work on two sheets -- from different workbooks -- by placing them in vertical, side-by-side windows. When you have two workbooks open at the same time, choose Window, Compare Side by Side With. (You can also use this feature with more than two windows, but the data become difficult to read.) As soon as you choose this command, Excel automatically opens a floating Compare Side by Side toolbar. The toolbar includes the Close Side by Side button, which you can use to close the windows as soon as you're done comparing or transferring data between the two. While the two workbook windows are in open in Excel, you can select different worksheets and scroll to different regions in either one by using its sheet tabs and scroll bars that appear at the edge of the window. Just make sure the workbook is active, either by clicking the window's title bar or one of the cells of its worksheets.
RESET EXCEL'S STANDARD WIDTH You can use the Standard Width command to set all the columns in an Excel worksheet to a new uniform width. (The default width is 8.43 characters.) To do so, go to the menu bar and choose Format, Column, Standard Width. The Standard Width dialog box appears. Replace the default 8.43 in the Standard Column Width text box with your new width (in characters), and then click OK or press Enter. Note that when you set a new standard width for the columns of your worksheet, this new width doesn't affect any columns whose width you've previously adjusted either with AutoFit or in the Column Width dialog box.
COPY A WORD TABLE TO EXCEL If you began to create a table in Word and then decided that it'd be better in Excel, you can easily copy the Word table into any Excel worksheet. There's no need to completely start over. Just follow these steps:
The Word table and the Excel worksheet are not linked together. Any changes made to one don't reflect on the other.
PLACING CHARTS ON A WORKSHEET By default, an Excel chart is placed as an object on a worksheet. This approach has some advantages. First, you can resize and move the chart object around the worksheet, which gives you full control of where to place the chart and how big it should be. For example, you may opt to place the chart near its source data and then resize it such that when you print, the data and the chart fit nicely together on a single sheet of paper. In the Chart Wizard Step 4, when selecting to place the chart as an object on a worksheet, the drop-down list to the right of where the worksheet name goes gives you the list of all the worksheets in your workbook. You can place the chart on any worksheet, regardless of which worksheet holds the data. The data that a chart is based on and the chart itself do not have to be on the same worksheet.
PUTTING A CHART ON A WORKSHEET By default, a chart is placed as an object on an Excel worksheet. This approach has some advantages. First, you can resize and move the chart object around the worksheet, which gives you full control of where to place the chart and how big it should be. For example, you may opt to place the chart near its source data, and then resize it such that when you print, the data and the chart fit nicely together on a single sheet of paper. In the Chart Wizard Step 4, when selecting to place the chart as an object on a worksheet, the drop-down list to the right of where the worksheet name goes gives you the list of all the worksheets in your workbook. You can place the chart on any worksheet, regardless of which worksheet holds the data. The data that a chart is based on and the chart itself do not have to be on the same worksheet.
COLOR-CODING A WORKSHEET Excel 2003 enables you to color-code the worksheets in your workbook, making it possible to create a color scheme that helps either identify or prioritize the sheets and the information they contain. (Think of how you use different colored folder tabs in a filing cabinet.) When you color a sheet tab, note that the tab appears in that color only when it's not the active sheet. The moment you select a color-coded sheet tab, it becomes white with just a bar of the assigned color appearing under the sheet name. Note, too, that when you assign darker colors to a sheet tab, Excel automatically reverses out the sheet name text to white when the worksheet is not active. To assign a new color to a sheet tab, follow these steps:
To remove color-coding from the sheet tab, click the No Color option at the top of the Format Tab Color dialog box.
CHECKING FOR SPECIFIC WORKSHEET ERRORS Excel comes with a feature that alerts you to specific types of errors or potential errors in your worksheet. Excel can flag the following types of errors.
Remember: Use Excel's suggestions to serve as a guide only to alert you of potential errors on your worksheet because some of the data that you enter intentionally Excel may flag as errors. Excel's error-alert feature isn't foolproof. You should use it in conjunction with other techniques to make the most of it.
AMAZE YOUR FRIENDS WITH EXCEL WORKSHEET TRIVIA If you were to produce the entire worksheet grid on paper, you would need a sheet that was approximately 21 feet wide by 1,365 feet long to do it! On a 14-inch computer screen, you can normally see no more than between 10 and 12 complete columns and between 20 and 25 complete rows of the entire worksheet. With columns being about 1 inch wide and rows about 1/4 inch high, 10 columns represent a scant 3.9 percent of the total width of the worksheet, while 20 rows fill only about 0.03 percent of its total length. This should give you some idea of how little of the total worksheet is visible on the screen as well as just how much area is available.
|