Submitted by Office XP Users
Here is a collection of all the tips about Microsoft Excel version 2002 that were past winners of the monthly Office XP "T-Shirts for Tipsters" Contest, which ended May 31, 2002. For tips about using Microsoft Excel 2000, visit the Excel 2000 Tips & Tricks page.
Count Your Excel Records Based on Multiple Conditions
From Nick Fusee, Owings Mills, Maryland
Have you ever wanted a quick count of the number of records in your Excel worksheet that meet a set of conditions? Use an array formula. You create array formulas the same way that you create other formulas, except that you press CTRL+SHIFT+ENTER to enter the formula.
Let's look at an example. Say you're running a produce department and you want to analyze your inventory to find which items cost more than 25¢ and have a total inventory of two items.
Your current inventory looks like this.
A B C
Banana 0.25 2
Pear 0.25 2
Orange 0.33 3
Grape 0.5 4
Prune 0.5 5
Apple 0.25 3
Lime 0.33 2
Lemon 0.5 4
Kiwi 0.5 4
Peach 0.25 3
A1:A10 is the product name
B1:B10 is the product price
C1:C10 is the number on the shelf
Here's the array formula you'd use:
1. In the cell where you want the results type: =SUM(IF($B$1:$B$10 > .25, IF($C$1:$C$10=2,1,0)))
2. Press CTRL+SHIFT+ ENTER.
This formula checks column B for values greater than .25 and, for each record meeting that condition, checks column C for values that equal 2. Then it adds all the records that meet both conditions.
In the example given, the result is 1.
Enter a Line Break Within a Cell
From Jill Baird, Owego, New York
You can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it.
1. Click the cell where you want the label or heading to appear.
2. Type the first line of information.
3. Press ALT+ENTER.
4. Type the second line. Then repeat step 3 if you have additional lines to enter.
5. Press ENTER when you've finished typing.
Perform Quick Operations on Your Excel Data
From Gregg Petruzzelli, Boston, Massachusetts
Have you ever wanted to quickly perform an operation on your Excel data, without replacing the data? For example, maybe you have some yearly figures, and you want to see what the daily ones look like. It's easy to do, using the Paste Special command.
1. Type =365 in a cell.
2. Click Copy.
3. Highlight the data for which you want the daily figures.
4. On the Edit menu, click Paste Special.
5. In the Paste Special dialog box, click Divide and then click OK.
All of the data you have highlighted will be divided by 365. If you click in any of the cells, you'll see the operation that was performed displayed in the formula bar.
Import Access Tables into Excel
From Linda Short, Oklahoma City, Oklahoma
You've gathered the data, now you want to analyze it. Here's a quick way to copy an Access table into Excel.
1. In the Access database window, click the table you want to export.
2. On the Standard toolbar, click Office Links.
3. Click Analyze It with Excel.
Excel automatically opens and displays your table in a worksheet.
Another Way to Copy Access Data into Excel
From Timothy E. MacKay, Elmhurst, New York
L. J. Cook of Arkansas City, Kansas offered a tip on how to import Microsoft Access data into Excel. Here's an alternative way that just requires a simple copy and paste.
1. In Access, open the table, query, or form that contains the records you want to copy.
2. On the View menu, click Datasheet View.
3. Select the records you want to copy. Or press CTRL+A to select the entire column.
4. Click Copy on the File menu.
5. Open an Excel workbook.
6. Click the upper-left corner of the worksheet area where you want the first field name to appear. (To ensure that the copied records do not replace existing records, make sure that the worksheet has no data below or to the right of the cell you click.)
7. Click Paste on the File menu in Excel.
Import Access Data into Excel
From L. J. Cook, Arkansas City, Kansas
Did you know you could import data from your Microsoft Access databases into Microsoft Excel? Here’s a quick and easy way to do it:
1. Open the Excel workbook into which you want to import the data.
2. On the Data menu, point to Import External Data, and then click Import Data.
3. In the Select Data Source dialog box, click New Source.
4. In the Data Connection Wizard dialog box, click ODBC DSN, and then click Next.
5. Click MS Access Database, and then click Next.
6. In the Select Database dialog box, browse to the database file you want to import, and then click OK.
7. In the Data Connection Wizard dialog box, click the name of the table that contains the data you want to import, and then click Next.
8. Type a name and description, and click Finish.
9. In the Select Data Source dialog box, click the data source you just created and then click Open.
10. In the Import Data dialog box, specify where you want to put the data, and then click OK. (While the Import Data dialog box is open, you can click the row on your spreadsheet where you want the data to appear and the Existing worksheet box will update automatically with the correct information.)
Keep Links to Source Workbooks Up-to-Date
From Christian Avrillon, South Africa
One of the great things about Excel is that you can create formulas in one workbook that link to data stored in another (source) workbook. But, when your source workbook changes regularly (for example, if you update the source and save it under a new name each month), it can be very time-consuming to find and update links to the old source workbook. Fortunately, there's an easy way to do this:
1. Open the workbook that contains the link(s) .
2. On the Edit menu, click Links.
3. In the Source box, click the name of the link with the source you would like to change.
4. Click Change Source.
5. In the Change Source dialog box, click the source workbook you want to refer to.
Editor's Note: To successfully change source workbooks, the linked data must reside in the same cells (for example, A15, D24) in the new source workbook as they did in the old.
Generate Random Numbers in Excel
From Ben Marshall, Woking, Surrey, England
Some types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically. Here are a few of the ways you can use it:
* Type =RAND() in a cell to generate a number between 0 and 1.
* Type =RAND()*100 to generate a number between 1 and 100.
After entering a function, you can then use the fill handle to quickly populate as many cells as you'd like with random numbers. To use the fill handle, click the cell, move your pointer over the lower-right corner of the cell until it turns into a black plus sign, and drag it horizontally or vertically across the cells you wish to populate.
Editor's Note: To change the number format of your random numbers (for example, if you'd prefer whole numbers to decimal points), click Cells on the Format menu. In the Format Cells dialog box, click the Number tab and then click Number in the Category list. Then in the Decimal places box, enter the number zero and click OK.
Create Forms for Easier Data Entry
From Rajesh Chintala, Delhi, India
Entering large amounts of data into an Excel spreadsheet can be very time consuming. Using data entry forms makes your task easier. A data entry form is a dialog box that gives you a convenient way to enter a complete row of information at one time.
To use a data entry form to edit a list:
1. Click a cell in the labeled row you want to add the record to.
2. On the Data menu, click Form.
3. Click OK. A data entry dialog box appears, with field labels that correspond with the column labels in your list.
To add a new record
1. Click New.
2. Type the information for the new record.
3. When you finish typing data, press the ENTER key to add the record.
4. When you finish adding records, click Close to add the new record and close the data form.
Keep the Result, Lose the Formula
From Ralph Hughes, West Palm Beach, Florida
I receive invoices from vendors containing formulas that calculate billing data. Before I can use the billing data, I need to convert the formula results to plain numbers. Fortunately, in Excel it's easy to copy and paste a result without the formula.
1. Select the cell containing data you want to copy.
2. Press CTRL+C to copy the cell data.
3. Press CTRL+V to paste the data in a new location.
4. Click the arrow next to the Paste Options smart tag, and then click Values Only.
Use Your Spreadsheet Like a Database with AutoFilter
From Bob Brannen, location unavailable
You can use AutoFilter to analyze the data in your Excel spreadsheet based on specific criteria. For example, if you are a salesperson who has a spreadsheet listing all the clients you have in each region you cover, you can use AutoFilter to sort by a specific region and get a snapshot of just the clients in that region. Here's how you to use the feature:
1. Click a cell in the list you want to filter. You should choose a cell that appears in a row that contains a heading and related data, such as a set of client names or phone numbers.
2. On the Data menu, point to Filter, and then click AutoFilter. Arrows appear at the heading of each column.
3. Click an arrow, and choose your filter criteria from the drop-down menu. (For example, you could filter for a number that's greater or less than a target figure.)
Only rows containing data that meet the criteria are displayed. Great for reporting!
Editor's Note: To see an example of AutoFilter in action, visit the How Ed Viesturs Uses Excel page, which shows how America's premier mountaineer uses AutoFilter to sort his packing list.
Give Your Excel Workbooks a Consistent, Professional Look
From Ken Leisey, Richmond, Virginia
Most of my company's work for clients is done in Microsoft Excel. To maintain a consistent and professional look in the documents we send them, we created a macro that automatically formats our workbooks with certain elements. Among other things, our macro sets the page layout to landscape, specifies the page margins, and adds standard elements such as copyright information and page numbers to page headers and footers.
Identifying repetitive tasks and recording them as macros saves us a lot of time, helps to maintain consistency, and reduces mistakes.
The following procedure demonstrates how to create a macro you can use to insert a custom footer into your documents.
To create the macro:
1. Open a new Excel workbook.
2. On the Tools menu, point to Macro, and then click Record New Macro.
3. In the Macro name text box, type the name for the macro, such as FormatPage.
4. In the Store macro in list, select Personal Macro Workbook. (Note: You must save the macro in your Personal Macro Workbook, or it will be lost.)
5. Click OK.
6. On the View menu, click Header and Footer.
7. Click the Custom Footer button.
8. Click in the Left section, Center section, or Right section box, and then click the buttons to insert the header or footer information you want in that section; or, type in your own information.
9. Click the Font button (the button with a large A) to change the font attributes.
10. Click OK.
11. On the Tools menu, point to Macro, and then click Stop Recording.
To use the macro in a new document:
1. Open a document.
2. On the Tools menu, point to Macro, and then click Macros.
3. In the Macro name box, click the name of the macro you want to run.
4. Click Run.
To view your results, click Print Preview on the Standard toolbar.
Editor's Note: To use Print Preview, you must have filled in at least one cell in the workbook.
Navigate Blocks of Data in Excel 2002
From Eladio Miguel Knipping, Irvine, California
A simple way to navigate through blocks of contiguous data in Excel version 2002 is to use the END key in combination with the arrow keys. To move by one block of data within a row or column, press END followed by an arrow key. For example, to move to the last (or rightmost) cell in a row of data, press END+RIGHT ARROW.
Or, to move to the last cell in the worksheet, in the bottom-most used cell of the rightmost used column, press CTRL+END.
Draw Borders in Excel Worksheets
From Mark Cross, The Villages, Florida
For years Microsoft Word users have been able to create tables that meet their own unique specifications. Now, Excel version 2002 offers users a similar feature: Draw Borders. Here's how to use it:
1. On the Formatting toolbar, click the arrow next to Borders, and then click Draw Borders on the palette.
2. On the Borders toolbar, click the arrow next to Draw Border or Draw Border Grid, and then click Draw Border on the palette.
3. Do one or more of the following:
1. Draw a border line on cells. Click the line you want as a border or click and drag on the lines you want as borders.
2. Draw an outside border around a row. Click in the center of a cell and drag across the row.
3. Draw an outside border around a column. Click in the center of a cell and drag down the column.
4. When you are finished drawing borders, close the Borders toolbar to leave Draw Borders mode.
Here are some keyboard shortcuts to use with the new Draw Borders feature:
* To draw borders around every cell within the row or column, press the CTRL key while you drag the cursor.
* To erase the borders you've drawn around a row or column, press the SHIFT key while you drag the cursor across the row or down the column.
* To erase the borders you've drawn around every cell within a row or column, press CTRL+SHIFT while you drag the cursor across the row or down the column.
Editor's Note: To apply a different line style to a border, click the arrow next to Line Style, and then click a line style on the palette. To apply a different line color to a border, click Line Color, and then click a color on the palette.
Build a Timesheet with a Simple but Powerful Function in Excel
From Brian Nicholson, Watertown, Wisconsin
Excel offers a simple yet powerful way to collect employees' timesheet entries: the NOW() function. Using this function, you can create a macro that enables an employee to clock in or clock out with the click of a button.
To record a macro that enters and updates the NOW() function:
1. On the Tools menu, point to Macro, and then click Record New Macro.
2. In the Macro name box, enter a name for the macro, such as "Timesheet".
3. In the Store macro in box, click the location where you want to store the macro. If you want a macro to be available whenever you use Excel, select Personal Macro Workbook.
4. If you want to include a description of the macro, type it in the Description box.
5. Click OK.
6. In the worksheet, select the cell in which the employee's clock-in time should appear, type the formula =Now(), and press ENTER.
7. Copy the cell.
8. Right-click the same cell, and click Paste Special on the shortcut menu. Under Paste, select Values, and then click OK. Doing this freezes the clock-in/out time so it cannot be altered by the employee.
9. Press ENTER.
10. On the Stop Recording toolbar, click Stop Recording.
Now you have a macro that updates a selected cell with the current time. The next step is to assign that macro to a button, so that the entry can be accomplished with a single click.
To create a custom toolbar button and assign the new macro:
1. On the Tools menu, click Customize, and then click the Commands tab.
2. In the Categories box, click Macros.
3. Drag the Custom Button icon from the Commands box to a toolbar. Leaving the Customize dialog box open, do the following:
1. Right-click the new button and then type a name, such as "ClockInOut", in the Name box on the shortcut menu.
2. Right-click the new button, click Change Button Image, and then click an image. Or, to display the button name instead of an image, click Text Only (Always).
3. Right-click the new button and click Assign Macro. Under Macro Name, click the name of the macro you just created, and then click OK.
4. Close the Customize dialog box.
Now all the employee has to do is select the appropriate cell and click the "Clock In/Out" button.
Switch Between Absolute and Relative Cell References in Excel
From Rolando S. Jamilla, Katy, Texas
When you create a formula in Excel, the formula can use relative cell references, which refer to cells relative to the position of the formula, or absolute references, which refer to cells in a specific location. Formulas can also contain a mix of relative and absolute references. An absolute reference is indicated by the $ symbol. For example, $B$1, is an absolute reference to column B, row 1.
When working with formulas, you can easily change column and row references from relative to absolute, and back again, using this handy shortcut:
1. Select the cell that contains the formula.
2. In the formula bar, select the reference you want to change.
3. Press F4 to toggle through the combinations.
Use This Shortcut to Insert Time/Date in Excel or Access
From Kimberly Schenk, Smyrna, Tennessee
Here are a few keyboard shortcuts you can use to insert the current time and date in a Microsoft Access table or Excel spreadsheet.
* Current date: Press CTRL+SEMICOLON
* Current time: Press CTRL+SHIFT+ SEMICOLON
* Current date and time: Press CTRL+ SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON
In Access, this keyboard shortcut only works if you are entering data in the Datasheet or Form view.
Editor's Note: When you insert the date and time using this tip, the information remains static. To update this information automatically, you must use the TODAY and NOW functions. To learn how to do this, search for Insert the current date and time in a cell in Excel Help and then click Insert a date or time whose value is updated.
Quickly Calculate a Person's Age in Excel
From Kimberly Schenk, Smyrna, Tennessee
The DATEDIF() function in Excel calculates the number of days, months, or years between two dates. So, this function makes it easy to calculate a person's age. To try this tip:
1. In a blank worksheet, type the birth date in cell A1, using slashes to separate day, month, and year.
2. In cell A2, type =DATEDIF(A1,TODAY(),"y") and press ENTER.
The age (in years) will be displayed in cell A2.
Editor's Note: For more information on the proper syntax to use for the IF worksheet function, search for IF worksheet function in Excel Help.
Quickly Clear All Spreadsheet Formatting
From Thomas Nilsson, Malmö, Sweden
Here's an easy way to quickly clear all formatting in your Excel spreadsheet.
In Excel version 2002:
1. Click any cell in the spreadsheet and then press CTRL+A to select all cells in the worksheet.
2. On the Edit menu, point to Clear, and then click Formats.
Hide Whole Worksheets in Excel
From Mike Figueroa, Mt. Cisco, New York
You can hide Excel worksheets to reduce the number of sheets on the screen and to prevent unwanted changes. When you hide parts of a workbook, the data disappears from view but is not deleted from the workbook.
1. Select the sheets you want to hide.
2. On the Format menu, point to Sheet, and then click Hide.
Note that you will not be able to hide a worksheet if the workbook has been protected.
Web Queries Keep Excel Worksheets Up to Date
From Brian Lema, Nepean, Ontario, Canada
Web pages often contain information that is perfect for analysis in Excel. For example, you can use Excel to analyze stock prices copied directly from a Web page. But what if you need to replace the information often to keep it current? The refreshable Web queries now available in Excel version 2002 make that task easy.
To create a new, refreshable Web query:
1. In your browser, browse to the Web page from which you want to query data (such as stock quotes on MSN MoneyCentral).
2. Copy the data and paste it into an Excel worksheet. A Paste Options smart tag will appear just below your pasted data.
3. Click the arrow on the right side of the Paste Options smart tag, and click Create Refreshable Web Query.
4. In the New Web Query dialog box, click the yellow arrow next to table of data you want in your Web query.
5. Click Import.
Animation of Refreshable Web Query feature
Refreshing this data can be done manually or automatically in Excel. To do a manual refresh:
1. On the View menu, point to Toolbars and click External Data.
2. Then click the Refresh button on the External Data toolbar.
To have your data refreshed automatically when the file is opened, at timed intervals, or in the background:
1. Click Data Range Properties on the External Data toolbar.
2. Then select the check boxes for the options you want under Refresh control.
Editor's Note: When you retrieve data from a Web site, you might lose some formatting or content, such as scripts, .gif images, or lists of data in a single cell.
Rest Your Eyes with Text to Speech
From Fran Weaver, Huntington Beach, California
Proofreading a spreadsheet can be time-consuming, blurry-eyed work, especially when you're comparing your spreadsheet against data in another document. But the new Text to Speech feature in Excel 2002 can really help speed up this process by reading selected data back to you for verification. Each cell is highlighted as the value is spoken, and when you hear an error, you can stop to correct the error in that cell.
To play back a group of cells:
1. On the Tools menu, point to Speech, and then click Show Text To Speech Toolbar.
2. Select a group of cells to read back.
3. Choose how the computer will read back your data by clicking By Rows or By Columns on the Text To Speech toolbar.
4. Click Speak Cells if you want the computer to read back each cell in your selection.
5. To correct an error, click Stop Speaking, and use your mouse and keyboard to make the necessary changes.
6. Click Speak Cells to continue.
To play back after every cell entry:
1. On the Text to Speech toolbar, click Speak On Enter.
2. Enter data in a cell. After you press ENTER, the computer will read back the data in the cell.
You can also specify a male or female voice and the speed of playback.
Editor's Note: To use Text to Speech, your computer must have a sound card installed and speakers attached. The available voices depend on your default language installation and any language packs you may have installed. Text to Speech is not part of the standard installation of Excel, so have your installation CDs ready.
Color-Code Your Excel Sheet Tabs
From Jessica Kovalik, Littleton, Colorado
In Excel 2002, you can color-code sheet tabs for easier identification or grouping of related sheets. Here's how:
1. Select the sheets you want to color by holding down the CTRL key and clicking the tabs.
2. On the Format menu, point to Sheet, and then click Tab Color. You can also right-click the sheet tab and then click Tab Color.
3. Click the color you want, and click OK.
Get Easy Access to New AutoSum Functions
From Leanne Rasmussen, Oratia, Auckland, New Zealand
In older versions of Excel, the AutoSum feature was handy, but limited. In Excel 2002, the AutoSum button is linked to a longer list of formulas that you can add to your worksheet. With this more powerful AutoSum, you can quickly calculate the average of selected cells, find the maximum or minimum value in a range of values, and much more.
1. Click the cell below the column of numbers, or to the right of the row of numbers, you want to calculate.
2. Click the arrow next to AutoSum on the Standard toolbar, click the formula you want to use, and then press ENTER.
Copy an Excel Table and Its Formatting in Word
From Ismail Mitha, Stanger, Natal, South Africa
When you copy a table of data from Excel 2002 into Word 2002, you can choose to keep the formatting that was applied to the table in Excel, or you can match the destination table style and your table will be formatted in the Word default table style.
To copy a table from Excel to Word:
1. Open both the Word document you want to copy to and the Excel worksheet that contains the table.
2. In Excel, select the table you want to copy.
3. On the Edit menu, click Copy.
4. Switch to Word, and then click where you want the table to appear.
5. On the Edit menu, click Paste.
6. Using the Paste Options smart tag, select one of the following options:
* To keep the formatting applied in Excel, select Keep Source Formatting. (Or, to link the table so that it automatically updates with new data, select Keep Source Formatting and Link to Excel.)
* To match the style of a table already in your Word document, select Match Destination Table Style. (Or, to link the table instead of copying it, select Match Destination Table Style and Link to Excel.)
![Christo [PCD]](/thumb.php?src=%7Be_AVATAR%7Dupload%2Fap_1_newavav4.jpg&aw=100&ah=100)
