Computer Tips

 
Visit us often.  Computer tips updated daily.  Click here to--> "Tell a friend" so they can get updated computer tips, too.  Please visit our clients, as they support the computer tips page.

If you would like to submit a tip send us an email with your tip to info@businesswebsitelinks.com.
  ______________________________________________________________

Creating a more versatile PivotTable with the List feature (Excel 2003)

• Excel 2003's List feature, which allows you to segregate, manipulate, and quickly analyze your data, also helps you create a more efficient PivotTable. In previous versions of Excel, if you create a Pivot Table from a range of data, and later expand the range to include more rows, you also needed to re-create the PivotTable based on the new data range. However, if you make a range of data into a list before you create its PivotTable, the PivotTable refreshes to include new rows that you add to the list. To do so, select the data you wish to include in the list and press [Ctrl]L. Then, click OK in the Create List dialog box. Now, to create a PivotTable that can expand as your list expands, select the list and choose Data | PivotTable And PivotChart Report from the menu bar. Click Finish in the PivotTable And PivotChart Wizard to accept Excel's default options. Now, if you add data to the list's insert row (the row with an asterisk), the list expands to include the new data And, if you click the Refresh Data button on the PivotTable toolbar, the new row of data is integrated into the PivotTable.

Tell a story with pictures (2007)

• One of the most dramatic additions to the Excel 2007 Conditional Formatting menu is the addition of icon sets. These icons are illustrative pictures that you can assign to data values for fast data analysis. The icons range from stop lights to flags to check marks and x’s.
• For example, you can assign green, yellow, and red flags to your sales reps’ monthly sales. Green flags indicate sales in the top third percentile, yellow flags indicate sales in the middle percentile, and the red flags designate sales in the bottom third percentile.

Chart a static value with a straight line for easy comparisons (Excel 2000/2002/2003/2004)

• When you're plotting points on a chart, a flat line of static data can provide a visual point of reference, so it's easy to compare the rest of the chart's data to that value.
• To add a line to represent a static value in a chart, first create the chart. Be sure to include a column of data in the chart's source data with all the same values. For instance, in a chart comparing a product's price at different stores, you might want to include a column to hold the item's national average price. When your chart is complete, right-click on one of the static values' data points and choose Chart Type from the resulting shortcut menu. In the Chart Type dialog box, choose Line from the Chart Type list box and click OK.

Display a cheat sheet for your function right in the formula bar (2000/2002/2003/2004)

• You’ve probably seen the ToolTip that appears when you begin to type a formula. Once you enter the opening parenthesis, the arguments for that function appear below the Formula bar or active cell.
  But, many power users find these ToolTips more annoying than helpful, especially for functions they know by heart. You can disable the ToolTips and still view Excel’s cheat sheet when you get stuck.
• To turn off the function ToolTips:
  • 1.Launch Excel and choose Tools | Options to access the Options dialog box.
  • 2.Click on the General tab, if necessary.
  • 3.Deselect the Function ToolTips check box in the Settings panel.
  • 4.Click OK to disable the function ToolTips.
• To insert the function arguments right in the cell or formula bar:
  • 1.Enter the function in a cell, along with its opening parenthesis—i.e., =SUM(.
  • 2.Press [Ctrl][Shift]A to enter the arguments right within the active cell (or the Formula bar if you’re working directly n the Formula bar).
  • 3.Replace each argument with your relevant data or cell reference.

Match the worksheet to the job with a custom template (2000/2002/2003/2004)

• When you insert a worksheet using the Insert | Worksheet menu option, Excel automatically bases the new sheet on the default template. However, you may not want to insert just a blank worksheet. For instance, say your company uses a special Project Tracking template. A workbook is dedicated to each client and there could be several project worksheets in each workbook. Instead of inserting a blank sheet, you want to insert a blank Project Tracking form.
• Fortunately, you can select what template Excel uses to create a new worksheet. Instead of choosing Insert | Worksheet from the menu bar, right-click on a worksheet tab ([control]-click in 2004) and then choose Insert from the shortcut menu. The Insert dialog box appears, displaying your PC's templates. Simply select the appropriate one and click OK.

Move worksheets between workbooks in a flash (2000/2002/2003)

• Sometimes Excel users overlook the simplest solutions — usually because experience tells them it couldn’t be so easy! Here’s a pleasantly simple way to move a worksheet to another workbook.
• To move a worksheet from one workbook to another:
  • Launch Excel and open both the original workbook and the destination workbook.
  • Choose Window | Arrange from the menu bar.
  • Select the Horizontal option button in the Arrange Windows dialog box and click OK.
  • Drag the worksheet tab that you want to move to a position in the destination workbook.
• When you release your mouse button, notice that the worksheet displays in the destination workbook, but it’s no longer in its original workbook.

Analyze characteristics of similar items without creating multiple charts

• When you want to compare several aspects of similar items, you probably create a chart for each one -- but it can be difficult to visualize the comparison when you're eyeballing a bunch of charts. Excel's radar charts can help you draw valuable conclusions by putting all of the comparable data in one chart.
• To understand how radar charts can help with data analysis, we'll:
  • Review the basics of radar charts and create our own.
  • Draw comparisons with the data in our radar chart.
  • Tweak the chart by adding a category and a series.
  • Work around a pitfall that can arise if your data series doesn't use a consistent value scale.
  • Read More About This Tip Click Here > MS Excel Tips

Highlight your top 3 — even when the numbers are always changing

• You can't analyze your business unless you know what works -- or doesn't work. By combining the RANK function with conditional formatting, you can keep track of the top or bottom values in your worksheet even when you update the data regularly.
• To emphasize the top three values in our spreadsheet automatically, we'll:
  • Add a column to the spreadsheet that ranks departments based on their current sales totals.
  • Present an alternative formula that eliminates duplicate rankings.
  • Apply conditional formatting that draws attention to the top three departments, even when the ranks change with sales figure updates.
  • Read More About This Tip Click Here > MS Excel Tips

Create hyperlinks that point to areas in a workbook (2000/2002/2003)

• Excel workbooks can store a huge amount of data, and getting from one place to another in a file can be tedious. You can simplify things by creating hyperlinks that jump exactly to a specific place in the file.
• To insert a hyperlink to a specific location in your workbook:
  • 1. Select the cell you want to contain a hyperlink.
  • 2. Enter the text that you want the hyperlink to display.
  • 3. Choose Insert | Hyperlink from the menu bar to open the Insert Hyperlink dialog box.
  • 4. Select the Place In This Document button on the Link To bar on the left side of the dialog box.
• If your workbook contains named ranges, you can select a name from the Defined Names list and the hyperlink will jump to it when you click it. Otherwise, select the appropriate worksheet name under the Cell Reference node and enter a cell or range address in the Type The Cell References text box.

Extract unique entries based on two columns of criteria

• It's challenging enough to weed out duplicate records in a long list when you only have to focus on one piece of duplicate data. But what if you have to find two pieces that match before you're sure you have a duplicate? Our technique uses the Advanced Filter feature to weed out only the unique records in a worksheet -- based on two columns of criteria instead of just one.
• To list only the unique entries in a list using two columns as our criteria, we'll:
  • Display only the unique records in a list using the Advanced Filter feature.
  • Copy the unique items to a separate worksheet range.
  • Discuss some of the limitations of the Advanced Filter and how you can work around them.
  • Read More: MS Excel Tips

Hunt down duplicate data and delete it fast with AutoFilter

• No matter how vigilant you are, duplicate data seems to worm its way into your longer data lists, especially when you regularly import new data. Fortunately, you can combine an Excel formula with the AutoFilter feature to single out duplicate data, making it a snap to delete.
• To find the duplicate data in our worksheet and delete it, we'll:
  • Design a formula that returns a 0 the first time a data item is listed and a 1 for each duplicate of the data item.
  • Filter our data list to show only the items that are duplicates.
  • Delete the duplicates and return to a data list that now only displays unique items.
• You could spend hours going through a large data table and manually identifying and deleting any duplicate entries. The chance for human error is high and you’ll spend too much valuable time on the task. Our field-tested technique combines the power of an Excel formula and the AutoFilter feature to cut the job down to minutes instead of hours.
  Put the technique into action
  To identify duplicates, we’ll add a column to the list and use a conditional worksheet formula that returns a 0 the first time that a particular data item is encountered in the list. Each additional occurrence of the item will return a result of 1 in the column. To review or remove the duplicates, we’ll use the AutoFilter tool to display only the records flagged with 1.

  To flag duplicate data items:

  1.Launch Excel and open our sample workbook, or create one based on the data shown in Figure A.

  2.Select cell C1 and enter Duplicates as the column header.

  3.Press [Enter] to advance to cell C2 and enter the formula =IF(COUNTIF(A$2:A2,A2)>1,1,0).

  4.Double-click on cell C2’s Fill handle to copy the formula down the column and flag duplicates, as shown in Figure B.

  Figure A: We’ll flag duplicate entries using a worksheet formula.

  

  Figure B: The duplicate records display a 1 in column C.

  

  Break down the formula

  This formula uses the COUNTIF function, which counts the number of cells in a specified range that meet particular criteria. This first argument in the COUNTIF function, in this case A$2:A2, is the range that Excel will search. The second argument, A2, indicates the criteria the formula is looking for.

  In our formula, we’re specifying that we want to count the times that a value stored in cell A2 appears within range A$2:A2. The COUNTIF function is nested within an IF function, which returns the result ultimately displayed in cell C2. If the result of the COUNTIF function is greater than 1, our formula returns 1; otherwise it returns 0.

  You may have noticed that we only used the dollar sign symbol in the first part of our COUNTIF range address. Doing so means that we can copy the formula to other worksheet cells and the address used in that part of the formula will always refer to row 2.

  Important: Note that our formula only counts the occurrences in column A — Product codes. This means that if two different product codes had the same product name, Excel doesn’t flag it as a duplicate using our formula. Depending on your data and how you want to define what a duplicate item is, you may need to tweak this. In a future article, we’ll show you how to find duplicates based on several criteria.
   

  Do you need more order?

  Remember that this formula is designed to mark the first instance of a data item as unique. Excel flags every instance that follows as a duplicate. This means that you might want to think about the order of your data. For instance, what if the third instance of a product code actually had the most up-to-date information? Our formula would have thrown out the most recent item as a duplicate. It may help if you add a Date column to your worksheet. This way, you can sort the data table so that the most recent entries are at the top of the list. Then our formula will be sure to keep the most recent information and mark older instances as duplicates ready for deletion.

  Remove the duplicates

  Now that you’ve identified the rows with duplicate product codes, you probably want to remove them. This is where we’ll put the AutoFilter to work.

  To eliminate duplicate data items:

  1.

  Select any cell within your data table.

  2.

  Choose Data | Filter | AutoFilter from the menu bar. Dropdown arrows appear to the right of the column headers.

  3.

  Click on the Duplicates column’s dropdown arrow and select 1. Only the duplicate records are displayed, as shown in Figure C.

  4.

  Select the row headings for the displayed rows and choose Edit | Delete Row from the menu bar.

  5.

  Choose (All) from the Duplicates column’s dropdown arrow to restore your view of the data table. Only the unique items remain, as shown in Figure D.

  Figure C: The AutoFilter hides all of the first occurrences, leaving only the duplicates.

  

  Figure D: The duplicate entries have all been removed from our list.

  

Tip: Specify what your PivotTable displays in place of empty cells and errors (2000/2002/2003)

• PivotTables can look incomplete and unprofessional if there are empty cells or error messages. Instead of manually replacing the error values you don’t want to see or placing zeros into blank cells, you can set a quick preference that does the job for you.
• To set up specific text for empty cells or error messages:
  • 1. Launch Excel and open the workbook with your PivotTable.
  • 2. Select the PivotTable and make sure the PivotTable toolbar is displayed. If it isn’t, choose View | Toolbars | PivotTable from the menu bar.
  • 3. Click the PivotTable button on the PivotTable toolbar and choose Table Options from the resulting dropdown menu to open the PivotTable Options window.
  • 4. Select the For Error Values Show check box and enter the text you want to display in its corresponding text box.
  • 5. Select the For Empty Cells Show check box and enter the text you want to display in its corresponding text box.
  • 6. Click OK to apply the settings to your PivotTable.
• If you want errors to display as blank cells, simply leave the For Error Values Show text box blank.

Tip: Zoom in on your data in seconds (2000/2002/2003)

• If your mouse has a wheel, you probably use it to scroll up and down through lengthy worksheets. You may not realize that you can also use it to change the zoom percentage for your view of the worksheet.
  To do so, hold down the [Ctrl] key and move the scroll wheel. Rolling the wheel down decreases the zoom percentage and rolling it up increases the zoom percentage. Note that if you’re using Excel 2000, the zoom will only go up to 100 percent.
• If you’d rather have your mouse wheel zoom by default, choose Tools | Options from the menu bar, switch to the General tab, and then select the Zoom On Roll With IntelliMouse check box. After you click OK, rolling the wheel zooms and rolling the wheel while holding the [Ctrl] key scrolls up and down the worksheet.

Tip: Put a new slant on your data (2000/2002/2003/2004)

• Minor changes can sometimes make a worksheet’s visual appeal skyrocket. For instance, you may notice that our sample worksheets occasionally include column headers that slant at a 45 degree angle. Want to see how it looks on your data table? It’s as easy as 1-2-3.
• To slant the text in a cell:
  • 1. Launch Excel and open the workbook in which you want to change your text orientation.
  • 2. Select the cell(s) that contain text you want to slant.
  • 3. Choose Format | Cells from the menu bar and click on the Alignment tab.
  • 4. Enter 45 in the Orientation panel’s Degrees spin box and click OK.

Tip: Make international newcomers feel at home within Excel (2000/2002/2003/2004)

• More often, your coworkers may not only be new to the company but also to the country. There are a few things you can adjust within Excel to make international users feel more comfortable in their Excel work and navigation. One of these settings is aimed at those who are used to reading right-to-left as opposed to the Western standard of left-to-right.
• To change your Excel screen to a right-to-left orientation:
  • 1. Launch Excel and open any workbook.
  • 2. Choose Tools | Options from the menu bar and click on the International tab.
  • 3. Select the Right-To-Left option button in the Right-To-Left panel.
  • 4. Preview the effect by selecting the View Current Sheet Right-To-Left check mark.
  • 5. Click OK.
• When you click OK, you’ll see that Excel’s row numbers now appear on the right. The columns begin with A furthest to the right and move through the alphabet as you scroll to the left.

Tip: Never type a decimal point again (2000/2002/2003)

• If you consistently use the same number of decimal places in your Excel values but typing the decimal point slows down your data entry, there’s an easier way. You can permanently set all of the numbers you enter in your Excel workbooks to include the same number of decimal places.
• To always display the same number of decimal places:
  • 1. Launch Excel and open the workbook in which you want to keep a consistent number of decimal places for every number.
  • 2. Choose Tools | Options from the menu bar to open the Options dialog box.
  • 3. Click on the Edit tab.
  • 4. Select the Fixed Decimal Places check box and then use the corresponding spin box to increase or decrease the number of decimal places you want.
  • 5. Click OK to apply the change.
• Now when you enter a number in Excel, it automatically inserts a decimal point at the correct position — even though you didn’t type one when you entered the number. This can save you data entry time and minimize typos.
• This preference will apply to all of your work in Excel until you open the Options dialog box again and deselect the Fixed Decimal Places check box.

Tip: Chart your data with one keystroke (2000/2002/2003)

• Charts can take a lot of time to construct, and there are so many ways to customize them that it’s often an intimidating task.
• The next time you need a quick-and-dirty chart in seconds, just select any cell within the data table you want to chart and press the [F11] key. Excel creates a default Column chart in a new worksheet. From there you can either take the chart as is or tweak it to suit your needs — just as you would any other chart.

Tip: Keep your scrolling under wraps — use a shortcut key instead (2000/2002/2003)

• When you have a large set of data that spans several Excel screens, scrolling to the end of the data isn’t a great option. It’s difficult to control and you often end up exceeding the last row (or column) of data.
• Fortunately, there’s a simple shortcut key that not only finds but also selects every cell to the right and below the currently active cell until it reaches the furthest point Just select your starting cell (in our case, cell A1) and press [Shift][Ctrl][End].
• Beware of one caveat: If you’ve entered a value and then deleted it, Excel may still consider it the last cell in your worksheet’s used range. So, if you had data up to row 200 and then delete the last 10 rows, the shortcut key selects up to row 200.

Tip: Display your bar chart’s data labels right in the bars (2000/2002/2003/2004)

• Data labels are tough to work with because they often make your chart look cluttered or cover up other important data points. The next time you want to display labels for your bar chart’s data points, you can conserve space by embedding them right in the bars. We’ll assume that your chart already displays data labels.
• To nest the data point labels right within the chart’s bars:
  • 1. Launch Excel and open the worksheet that contains your bar chart.
  • 2. Right-click on one of the data labels and choose Format Data Labels from the resulting shortcut menu to display the Format Data Labels dialog box.
  • 3. Click on the Alignment tab and then choose Center from the Label Position dropdown list.
  • 4. Ensure that Center appears in both the Horizontal and Vertical dropdown lists.
  • 5. Enter 90 in the Degrees spin box in the Orientation panel and click OK to apply the changes.
• There are a few things you should be aware of when it comes to this technique:
  • You may need to change the color of your data labels so they contrast well with your chart’s bars.
  • You may need to widen your chart’s bars to accommodate the numbers. You can do so by decreasing the gap width between the chart’s data points.

Tip: Trick Excel’s conditional formatting into giving you what you want (2000/2002/2003/2004)

• Conditional formatting is an excellent tool if you know the ins and outs of using it. One of the most frustrating aspects about it, however, is the error message you receive when you try to input a conditional formatting formula that refers to data in another worksheet. Excel tells you that you can’t refer to data outside of the worksheet in your conditional formats — but we say you can.
• All you need to do is create a named range for the data you want to use in another worksheet. For instance, you can create a range called CFRange in Sheet2. Then when you want to use that range in Sheet1’s conditional formatting formula, simply input CFRange in the formula. Excel accepts it even though the data is from a different worksheet.

Tip: Breathe some life into your PivotChart by making it 3D (2000/2002/2003/2004)

• PivotCharts are a visual version of your PivotTable data — intimately connected with the PivotTable and its changes. However, don’t forget that just because you’re working with a PivotChart, it doesn’t mean that you can’t use your old familiar charting tricks. For instance, don’t settle for the default PivotChart type. Make it pop off the screen by using a 3D alternative.
• To make your PivotChart three-dimensional:
  • 1. Launch Excel and open a workbook that contains a PivotTable and PivotChart.
  • 2. Right-click on the PivotChart’s data series and choose Chart Type from the resulting dropdown list to open the Chart Type dialog box.
  • 3. Choose a chart type from the Chart Type list box. There are 3D alternatives for the Column, Bar, Line, Area, Surface and Bubble chart types.
  • 4. Select a 3D chart option from the Chart Sub-type section. You can read a description of any sub-type by selecting it.
  • 5. Click OK when you’re ready to change your chart type.
    You can also get an idea of how a chart type will look before you click OK by clicking and holding down on the Press And Hold To View Sample button.

Tip: Travel back a few centuries — convert your numbers to roman numerals (2000/2002/2003/2004)

• Sometimes Excel’s vast options for computing and communicating data are surprising. For instance, if you need to convert a set of numbers to roman numerals but you can’t seem to get your L’s and your V’s straight, let Excel do the work for you.
• There’s a little-known function in Excel’s arsenal called the ROMAN function. You can use this function to convert a static number (i.e., =ROMAN(145)) to a roman numeral. Or, just as you would with any other function, you can use a cell reference. For example, =ROMAN(A1) will convert the value in cell A1 to a roman numeral.
• Just keep in mind that the ROMAN function cuts off its values to make them integers. If you type 46.8 in cell A1, cell A2 still returns XLVI. Also, you can’t convert numbers larger than 3999, and negative numbers will produce an error.

Tip: An alternative way to convert data from text to number formatting (2002/2003/2004)

• If you have Excel 2002/2003/2004, you can easily convert text into number formatting with SmartTags. First, you must ensure that Excel flags numbers that you store as text values.
• To flag data that Excel should store as numbers instead of text:
  • 1. Launch Excel and open a workbook with incorrectly formatted numbers.
  • 2. Choose Tools | Options (Excel | Preferences in 2004) from the menu bar, and then select the Error Checking tab in the Options dialog box.
  • 3. Ensure that the Number Stored As Text text box is selected in the Rules panel and click OK.
• Any number that you store as a text value now displays a green triangle in its upper-left corner. When you select the cell, a SmartTag appears.
• To correct the errors and store the values as numbers instead of text:
  • 1. Select the data range of flagged cells with values stored as text.
  • 2. Click on the SmartTag that displays when you select the data range.
  • 3. Select Convert To Number from the SmartTag’s dropdown list.
    In Excel 2004, you must select cells individually to make the conversion from text to number formatting.

Tip: Make sure your ampersand symbol displays in a header or footer (2000/2002/2003/2004)

• If you’ve ever tried to display text that includes an ampersand symbol (&) in a header or footer, you know exactly what happens. You enter the symbol, and it looks fine when you’re creating the custom header. However, when you click OK and return to the Page Setup dialog box, the preview omits the ampersand symbol; the text Smith & Jones Law Offices incorrectly displays as Smith Jones Law Offices.
• The problem is that Excel uses the ampersand symbol as part of its insertion code for dates, times, and page numbers. It doesn’t consider the ampersand as an actual text symbol. Fortunately, you can work around this by simply entering two consecutive ampersand symbols in a header or footer where you want an ampersand to display.
• To enter text that includes an ampersand in a header:
  • 1. Launch Excel and open the workbook in which you’d like to create a custom header or footer.
  • 2. Choose File | Page Setup from the menu bar to open the Page Setup dialog box.
  • 3. Click on the Header/Footer tab, if necessary, and then click the Custom Header button to open the Header window.
  • 4. Enter Smith && Jones Law Offices in the Left Section text box.
  • 5. Include any other information you’d like in the Center Section and Right Section list boxes.
  • 6. Click OK twice to return to your worksheet.
• When you enter two ampersand symbols, the header’s preview displays the symbol correctly.

Tip: Isolate your 3-D chart’s data points with drop lines (2000/2002/2003/2004)

• Although Excel's 3-D charts do a pretty good job of simulating 3-D objects on a 2-D screen, it can sometimes be difficult to visually line up a data point with its corresponding position on the chart's category axis. This is especially true when working with area or line charts. The task can be even more difficult if the chart has been rotated or the perceived angle of elevation has been changed. Fortunately, you can add drop lines, which are visual aids that help anchor each data point to the category axis.
• To display drop lines in a chart:
  • 1. Launch Excel and open the workbook that contains your chart.
  • 2. Select a data series on the chart, and then choose Format | Selected Data Series from the menu bar. Alternatively, you can press [Ctrl]1.
  • 3. Click on the Options tab in the Format Data Series dialog box.
  • 4. Select the Drop Lines check box and then click OK.
• Vertical lines now extend from each data point to the category axis, eliminating confusion and guesswork. Note that you can also apply this formatting option to 2-D area and line charts.

Tip: Display a cheat sheet for your function right in the formula bar (2000/2002/2003/2004)

• You’ve probably seen the ToolTip that appears when you begin to type a formula. Once you enter the opening parenthesis, the arguments for that function appear below the Formula bar or active cell.
  But, many power users find these ToolTips more annoying than helpful, especially for functions they know by heart. You can disable the ToolTips and still view Excel’s cheat sheet when you get stuck.
• To turn off the function ToolTips:
  • 1.Launch Excel and choose Tools | Options to access the Options dialog box.
  • 2.Click on the General tab, if necessary.
  • 3.Deselect the Function Tooltips check box in the Settings panel.
  • 4.Click OK to disable the function ToolTips.
• To insert the function arguments right in the cell or formula bar:
  • 1.Enter the function in a cell, along with its opening parenthesis—i.e., =SUM(.
  • 2.Press [Ctrl][Shift]A to enter the arguments right within the active cell (or the Formula bar if you’re working directly in the Formula bar).
  • 3.Replace each argument with your relevant data or cell reference.

Tip: Get the lowdown on the CONCATENATE function vs. the ampersand (2000/2002/2003/2004)

• There are two methods you can use for joining text from different cells—or combining the flexibility of a function within your text. You can use an ampersand (&) to combine a function with text, or the text from more than one cell. Alternatively, you can use the CONCATENATE function to combine different cell reference and text elements into a single text string. So, what’s the difference? Which method should you use?
  For the most part, these two methods produce the same results; they combine text and/or a cell reference’s data into one cell, formatted as text. The only real difference is the number of items you can combine. There’s no limit to how many pieces you can combine with the ampersand symbol. However, the CONCATENATE function can’t combine more than 30 items.

Tip: Customize chart axis labels even if they aren’t in your source data (2000/2002/2003/2004)

• When you use the Chart Wizard to create a chart from a data table, there are often missing elements. After all, you probably weren’t intending to create a chart when you entered the data. If you’re missing the convenient category (x-axis) labels that run across the chart’s horizontal axis, you can quickly designate your own labels right in the Chart Wizard.
• To generate custom category axis labels in the Chart Wizard:
  • 1.Launch Excel and open the workbook in which you want to chart data.
  • 2.Activate any cell within the data table you want to chart and click the Chart Wizard button CHART WIZARD on the toolbar.
  • 3.Advance through the wizard until you reach the Step 2 Of 4 screen.
  • 4.Click on the Series tab, if necessary.
  • 5.Enter the text for your labels in the Category (X) Axis Labels text box, separating each label with either commas (,) or semicolons (;).
  • 6.Complete the rest of the Chart Wizard with your desired settings.
    When you enter the custom labels, you’ll notice that Excel converts them into an array formula. This is a great alternative to choosing labels from the worksheet, especially if you want abbreviated versions of the worksheet labels or the worksheet doesn’t contain the correct category axis labels at all.

Tip: Apply number formatting to an entire field of PivotTable data (Excel 2000/2002/2003)

• Sometimes, it's difficult to deal with formatting in PivotTables. To make the job go faster, you can apply the same number format to every value within a PivotTable field. To begin, open your PivotTable in Excel and select any cell with a value that belongs to the field you wish to format. Then, click the Field Settings button on the PivotTable toolbar to access the PivotTable Field dialog box. Click the Number button to open the Format Cells dialog box's Number tab. Apply the settings for your desired number formatting and click OK twice to dismiss both dialog boxes. Every value in the field now reflects your new number formatting.

Tip: Improve your printouts by separating groups of data with page breaks (Excel 2000/2002/2003/2004)

• Oftentimes, your workbook contains thousands of rows. When you print it out, the sheer volume of records may be overwhelming, making it hard to locate the data you need. Fortunately, you can insert manual page breaks to separate groups of data onto their own pages.
• To insert a new page break, first switch to Excel's Page Break Preview mode. Launch Excel and open the workbook to which you want to add page breaks. Choose View | Page Break Preview from the menu bar. Then, select a cell (or an entire row or column) below the line where you wish to insert a horizontal break, or to the right of a line where you wish to insert a vertical break. Right-click on the selection and choose Insert Page Break from the resulting shortcut menu.

Tip: Change the username Excel assigns to your workbooks (2002/2003)

• You can easily update username metadata for all future files you save in Excel. That way, the personal information attached to your work in Excel is always correct. To do so, launch Excel and choose Tools | Options from the menu bar. Click on the General tab in the Options dialog box. Then, update or delete the information in the User Name text box to reflect the name you want to assign to all saved workbooks.

Tip: Chart a static value with a straight line for easy comparisons (Excel 2000/2002/2003/2004)

• When you're plotting points on a chart, a flat line of static data can provide a visual point of reference, so it's easy to compare the rest of the chart's data to that value.
• To add a line to represent a static value in a chart, first create the chart. Be sure to include a column of data in the chart's source data with all the same values. For instance, in a chart comparing a product's price at different stores, you might want to include a column to hold the item's national average price. When your chart is complete, right-click on one of the static values' data points and choose Chart Type from the resulting shortcut menu. In the Chart Type dialog box, choose Line from the Chart Type list box and click OK.

Tip: Save smart tags as part of your workbook (Excel 2002/2003)

• Since some smart tags relate directly to your Outlook profile, Excel may not recognize the same ones for different users or different operating systems. Fortunately, it’s easy to preserve your workbook’s smart tags by embedding them. That way, you can let other users use the same shortcuts you enjoyed, or make sure the smart tags are still available when you open the same document on a different computer. To do so, choose Tools | AutoCorrect Options from the menu bar. Then, select the Smart Tags tab. Finally, select the Embed Smart Tags In This Workbook check box and click OK. However, be aware that if a user doesn’t have the necessary information, such as an Outlook Contact, the smart tag’s functions are limited.

Tip: Preserve varying row heights when you copy and paste in your macros (Excel 2000/2002/v. X/2003)

• It may be common place to copy and paste a data range as part of a personalized macro. However, you may not realize that the way you format the range reference can impact how Excel copies that data. For example, if you enter a data range as 1:83 instead of A1:N83, you're actually instructing Excel to copy and paste a range of rows, as opposed to a range of cells. As a result, rows of varying height maintain those different heights when Excel pastes them in a new location. If you copied and pasted the data range as A1:N83, Excel wouldn't include row height in the attributes of the data range. This can really come in handy if you're using smaller empty rows to improve the worksheet's spacing!

Tip: Make your changes across the board by selecting all of your worksheets first (Excel 2000/2002/v. X/2003)

• There are many scenarios in which the worksheets in your workbook may need to have the same formatting and structure. For example, you may have a worksheet for each month of the year, all with identical structures and formatting, but different data. Before you create one worksheet and copy it 11 times to cover each month, consider selecting all of your worksheets before you create the spreadsheet's skeleton. To do so, right-click ([control]-click in v. X) on any worksheet tab. Then, choose Select All Sheets from the shortcut menu. Every worksheet is now selected until you manually select an individual worksheet. Any changes you make while they're all selected is applied to each worksheet instead of just one.

Tip: Rewrite history by changing the length of time your History worksheet tracks (Excel 2000/2002/v. X/2003)

• By default, Excel's History worksheet lists a shared workbook's changes tracked in the last 30 days. However, you can change this setting by choosing Tools | Share Workbook from the menu bar. Then, in the Share Workbook dialog box, click on the Advanced tab, if necessary, and select the first option button in the Track Changes panel. Enter the number of days you’d like to include in your History worksheet in the corresponding spin box, and click OK.

Tip: Displaying text vertically in a cell (Excel 97/2000/2001/2002)

• You may come across some worksheet layouts that would benefit from having text displayed vertically instead of horizontally.  For example, say you have created a complex reference table and one row label caption could apply to many adjacent rows.  You can save space and make the table easier to read by merging the label cells into one narrow, tall cell and then displaying the caption text vertically.  To display such text, select the appropriate cell and choose Format | Cells from the menu bar.  Then, switch to the Alignment tab.  Finally, click on the preview text box on the left side of the Orientation panel and click OK.

Tip: Prevent Excel from asking you whether to update links to outside sources (2002/2003)

• When your workbook contains links to external data (e.g., another workbook, another application, the web), Excel prompts you upon opening the workbook whether you'd like those links updated. In some cases, however, it may be more of a hindrance than a help. If you want the information in your links to remain static, then you're always declining the prompt. Fortunately, you aren't stuck with clicking the Don't Update button every time you open the workbook. You can eliminate the prompt by choosing Tools | Options to access the Options dialog box. Click on the Edit tab, if necessary, and then deselect the Ask To Update Automatic Links check box. Click OK to apply the changes and the prompt to update links no longer appears when you open the workbook.

Tip: Excel 2002 provides quicker access to special paste options

• In previous versions of Excel, the Paste Special dialog box was often used to perform common tasks such as converting formula results to values or transposing data.  Of course, there were other ways to do such tasks, but the Paste Special approach was usually the easiest for most users.  That's no longer true in Excel 2002.  The major drawback the Paste Special dialog box has is that it requires a lot of steps to get straightforward results.  First, you have to take a trip to the menu bar to launch it.  Then, you usually have to configure some settings before you can get the results that you want.  In Excel 2002, the tasks that most often instigated launching the Paste Special dialog box can be performed with an easily accessible toolbar option.  To see for yourself, copy some data.  then, examine the standard Paste toolbar button.  You'll see that in Excel 2002 it now has a dropdown arrow attached to it.  When you click the arrow, Excel displays a menu of often-used Paste Special settings that you can apply with a single click.

Tip: Find all the cells in a worksheet to which you've applied data validation (Excel 97/2000/2002/v. X/2003)

• Data validation is a great Excel feature that allows you to control what kind of data a user enters in your worksheet. However, if you apply data validation often, it's possible to lose track of which cells have data validation settings. Fortunately, you can select all cells with any kind of data validation in your worksheet, or you can select all of the cells with the same validation settings as the active cell. To do so, choose Edit | Go To from the menu bar to access the Go To dialog box. Click the Special button, and then choose the Data Validation option button. Two more option buttons become active: the All option button and the Same option button. Choose the All option button if you want to select any cell with data validation. Choose the Same option button if you're interested in selecting any cell with data validation that matches the currently active cell. Click OK to apply the selection.

Tip: Hide items in a PivotTable's page field's selection list (Excel 97/2000/2001/2002)

• In a PivotTable, a page field is a dropdown list that lets you filter the PivotTable to show only the data associated with the selected field item.  For example, if a PivotTable's page field is Country, you can filter the PivotTable to show just the data pertaining to Canada.  Then, selecting France from the dropdown list alters the PivotTable to reflect the data associated with France.  If you want to prevent items from appearing in the page filter's dropdown list, right-click on the page field's label ([control]-click on the Mac.)  Then, select Field Settings from the menu bar.  Select the items you want to hide in the Hide Items list box and then click OK.  By default, data associated with the hidden page field items is ignored from the PivotTable's totals.

Tip: Add a user-friendly button to run a macro in your workbook (Excel 97/2000/2002/v. X/2003)

• There are several ways to access your macros, but some of them are better suited to end users. For instance, it may be easiest for end users who don't know as much about Excel to run a macro by clicking a button. It only takes a few seconds to make this possible. First, view the Forms toolbar by choosing View | Toolbars | Forms from the menu bar. Then, click the Button icon and click on your worksheet where you'd like the button to appear. In the Assign Macro dialog box that displays, select a macro rom the Macro Name list box and click OK. Or, if you've yet to write the macro, you can enter the macro's name in the text box, click the New button, and then create the macro when the Visual Basic Editor opens.

Tip: Printing an entire workbook at once (Excel 97/2000/2001/2002)

• We recently showed you how to easily print a particular worksheet range, as opposed to a complete worksheet.  There are probably also times when you want to print all of the worksheets in a workbook.  If so, you can use a technique similar to the one we previously discussed:  choose File | Print from the menu bar and select the Entire Workbook option in the Print What panel.  While this option is definitely handy when you need to print a complete workbook that contains a large number of worksheets, there may actually be an easier way if the file only contains a few sheets.  First, click on the first sheet tab in the workbook.  Then, while holding down the [Shift] key, click on the last tab.  Now, simply click the Print button on the Standard toolbar.  Since the default Print What setting in Excel is Active Sheet(s), you'll produce the same result as if you had used the Print dialog box to set the Entire Workbook option.  Note that regardless of which technique you use, printing all of the sheets does maintain any print area setting that may be in place, so some sheets may not be printed in their entirety.

Tip: Counting text entries in Excel (97/2000/2001/2002)

• If you've ever tried to perform a simple count, such as the number of names in a list, you may have been frustrated by the results.  For example, say you have a series of names in cells B2:B8.  In cell B16, you enter the formula:
  • =COUNT(B2:B15)
• Instead of the expected answer of 7, the formula returns 0.  The reason is that Excel's COUNT function actually only counts numeric values.  To count any entry, regardless of type, you must use the COUNTA function.  For example, the formula:
  • =COUNTA(B2:B15)
• returns the number of name entries.  Note that this counts any non-blank cells in the specified range, so a cell will be counted even if just displays an error message.

Tip: Printing a selected range without setting a print area (Excel 97/2000/2002)

• By default, Excel is configured to print the active worksheet in its entirety.  This may often be fine for your needs.  However, there may be times when you want to print just a particular range on a sheet.  If you're going to print the same range repeatedly, you'll typically set the worksheet's print area, but if you're just printing to meet a one-time need, there's an easier way than going to the trouble.  Instead of setting a print area, select the range that you want to print.  Then, choose File | Print from the menu bar.  When the Print dialog box appears, choose the Selection option in the Print What panel.  Finally, click Preview to view the output onscreen or OK to print it.  A nice aspect to using this option is that it overrides a set print area without requiring that you clear it if one exists for the worksheet with which you're working.

Tip: Removing unnecessary smart tag indicators (Excel 2002)

• Smart tags were introduced in Excel 2002 to provide a quick way to act upon specific data entered into worksheets.  For instance, a smart tag that recognizes stock symbols provides an option menu that lets you retrieve additional information about recognized stocks.  By default, smart tags are turned off.  When the feature is activated, a small triangle appears in the lower-right corner of cells containing recognized data to let you know that an option menu is available.  Unfortunately, Excel may sometimes inappropriately interpret and apply smart tags.
• For example, say that you have a worksheet that contains stock symbols, and you want to take advantage of smart tags.  However, the same worksheet contains a cell with an internally used project code entered in a cell: IMA.  Since IMA is also a recognized stock symbol, a smart tag indicator appears.
• To get rid of an unwanted smart tag indicator, hover you mouse pointer over the cell containing it.  Then, click on the smart tag options button that appears and select Remove This Smart Tag from the resulting menu.

Tip: Center your data on the page before you print it (Microsoft Excel 97/2000/2002/v. X/2003)

• When you print a worksheet, you may feel that the printout would look better if the data were centered on the page. If so, you don't have to insert a lot of extra rows and columns or change the individual page margins to get the data to print where you want it. Instead, choose File | Page Setup from the menu bar. Then, click on the Margins tab. You can now use the check boxes in the Center On Page section to control how the data is presented. Note that the centering options apply to the space defined by the Top, Bottom, Left and Right margins, not the physical dimensions of the page.

Tip: Easily remove all non-text entries from a worksheet (Excel 97/2000/2001/2002)

• Even though using a worksheet template is usually considered the ideal solution, it's common to just base an Excel workbook on an already existing file when you need multiple files that share the same general structure or data labels.  For instance, you may create monthly worksheets that list product sales for different regions.  Since the regions and product names are the same from month to month, it's easier to copy the previous month's file and delete the sales figures than it is to re-enter everything from scratch.
• In such cases, you may be able to reduce your cleanup efforts, particularly if the variable data is in a lot of noncontiguous ranges.  Assuming you just want to keep the worksheet's text entries, which act as descriptive labels, you can easily select all of the numeric, date, and other remaining data at once and delete it.
• To do so, choose Edit | Go To from the menu bar.  Then, click the Special button to display the Go To Special dialog box.  Next, select the Constants option button and clear the Text check box.  Finally, click OK.  Excel will select all of the non-text entries.  All you have to do now is press the [Delete] key to remove the unwanted data.

Tip: Take advantage of a shortcut key to redo an action (Microsoft Excel 97/2000/2002/v. X/2003)

• Many Excel users are familiar with the shortcut key that allows you to undo an action. By pressing [Ctrl]Z, it's easy to eliminate the last action you made. However, what if you realize that you should have kept the action, or the undo was accidental in the first place? Fortunately, you can press [Ctrl]Y ([command]Y in v. X) to redo an action in a flash.

Tip: Change the default colors Excel assigns to chart points (97/2000/2001/2002)

• You're probably so used to the standard colors Excel assigns to chart lines and data points that you've never thought about changing them.  For example, when creating a column chart that contains multiple data series, the first data series is always colored a purplish-blue shade by default.  The next series is a magenta color, the next is pale yellow, and so on, for up to eight series.  You may prefer that the default colors are tailored to an overall design scheme or you may simply want a change.  Fortunately, it's easy to change the default colors Excel uses to fill chart items and lines.
• To assign your own colors, choose Tools | Options from the menu bar while the workbook that will contain your charts is open.  Then, click on the Colors tab.  The sample squares next to the Chart Fills and Chart Lines labels indicate the colors that Excel sequentially assigns to chart items.  To change a particular color, select the appropriate square in the Chart Fills or Chart Lines sequence.  then, click the Modify button.  Pick one of the standard colors from the color wheel or click on the Custom tab to create a new color.  Finally, click OK.  If you ever want to restore Excel's defaults, click the Reset button to restore all of the color items to their original settings.  Finally, click OK to save the color modifications.
• Any color setting changes you make will apply only to the current Workbook.  Also, changing the settings will update any existing charts in the workbook that depend on the default settings to use the new colors.

Tip: Quickly run your macros with keyboard shortcuts (Excel 97/2000/2002/v. X/2003)

• If you run a particular macro often, you may want to assign a keyboard shortcut to run it so you don't have to access the Macro dialog box each time you want to execute it. To do so, choose Tools | Macro | Macros from the menu bar. Then, select the macro to which you want assign the keystroke combination and click the Options button.
• When the Options dialog box opens, click in the Shortcut Key text box and press the key that you want to assign to the macro. By default, the shortcut key uses the [Ctrl] key in Windows and an [option][command] combination on the Mac platform. Keep in mind that there's a good chance you'll override an existing keystroke combination that's built into Excel, so it's a good idea to test the shortcut before assigning it to make sure you aren't disabling a shortcut you're likely to miss having available.
• One way that you can reduce the impact on existing shortcuts is to use a [Shift] combination. To do so, click in the Shortcut Key text box, hold down the [Shift] key, and press the key you want assigned to the combination. For instance, if you hold down the [Shift] key and press the [G] key, you'll use the combination [Ctrl][Shift][G] to run the macro on a Windows system. Mac users can also use the [shift] key to create [option][command][shift] key combinations.
• Finally, click OK to close the Macro Options dialog box. You can now close the Macro dialog box and run the macro using the appropriate shortcut key.

Tip: Automatically open a workbook when you start Excel (97/2000/2001/2002/v. X/2003)

• We recently discussed using your system's XLSTART folder when you want to customize the default template that Excel uses for new workbooks (in v. X, you use the Startup: Excel folder). We thought we should point out that it can be used to automatically open any workbooks when Excel starts. In fact, there are two folders that Excel checks for workbooks when you start the program. If any are found, Excel opens the files.
• Your operating system affects the XLSTART folder's location, so the easiest way to access it is to use your operating system's built-in Find or Search feature. In addition to the XLSTART folder, you can specify another folder that Excel should check. To do so:
  • 1. Choose Tools | Options from Excel's menu bar.
  • 2. Click on the General tab.
  • 3. Enter the folder path in the At Startup, Open All Files In text box (in older versions of Excel, the Alternate Startup File Location text box.)
  • 4. Click OK.

Tip: Require a password to access a workbook (Excel 97/2000/2001/2002)

• If you want to prevent people from opening a certain Excel workbook, you can assign a password to the file to control who can access its contents.  In addition, you can use a password to limit the number of people able to edit the file.  To set such passwords, open the file and choose File | Save As from the menu bar.  Then, click the Options button on the Save As dialog box (if your dialog box doesn't have that button, choose General Options from the dialog box's Tools menu).
• Type the password you want to use in the Password To Open text box.  As you type, the password is masked with asterisks.  this password determines who can view the file's contents.  Keep in mind that the password is case-sensitive, so capitalization matters.  If you also want to control whether edits can be made, enter a different password in the Password To Modify text box.
• At this point, click OK.  You'll then need to confirm any password selections you've made.  If you assigned two passwords, the first one you confirm is the one you entered in the Password To Open text box.  When you return to the Save As dialog box, click Save and then click Yes to save over the original workbook with the new password-protected workbook.
• When you later reopen the file, you'll be prompted to enter its password.  Enter it in the Password text box and click OK.  If you supply an incorrect password, Excel displays a warning and the open process is cancelled.
• If you also assigned a password to control modifications, you'll be prompted to enter it now.  If you're unable to supply the right password, or you don't need to make changes, you can click the Read Only button to open the file; however, you won't be able to save changes you make to the workbook unless you save to a new file.

Tip: Sort month and day names chronologically (Excel 97/2000/2002/v. X/2003)

• If you sort data based on a column of month or weekday names, Excel sorts the data alphabetically. Chances are you'd rather that it sort the information in chronological order. Fortunately, it's easy to do so:
  • 1. Select any cell in the column of month or weekday data.
  • 2. Choose Data | Sort from the menu bar.
  • 3. Click the Options button.
  • 4. Select the appropriate custom list using the First Key Sort Order dropdown list.
  • 5. Click OK on the open dialog boxes.

Tip: Change an existing Excel chart to a different type (97/2000/2001/2002)

• After you create a chart, you may wonder how the same set of data would appear as a different chart type.  For instance, you may start with a column chart, but then think that a line chart might present the data better.  In such cases, you don't have to start from scratch with a new chart.  You can simply change your existing chart to a different chart type using a number of different techniques:
  • The Chart Wizard - Select the chart object and then click the Chart Wizard button on the Standard toolbar.  Doing so displays the first screen of the Chart Wizard, allowing you to pick a different chart type, as well as make any other changes you normally can with the wizard.  Select the chart type you want to use and click OK.
  • The Menu Bar - Select the chart object and then choose Chart | Chart Type from the menu bar.  This displays the Chart Type dialog box, which is essentially the same as the Chart Wizard's first screen.  Select the chart type you want to use and click OK.
  • The Chart Toolbar - Select the chart object.  If the Chart toolbar doesn't automatically appear, choose View | Toolbars | Chart from the menu bar.  This toolbar contains a Chart Type toolbar button, which has a small dropdown arrow associated with it.  Click on the arrow to reveal a palette of 18 commonly used chart types.  Simply select the chart type you want to use and the current chart is automatically reconfigured.

Tip: Format numbers as text currency values (Excel 97/2000/2002/v. X/2003)

• If you want to combine text strings with currency data, you may find that results are unsatisfactory. That's because Excel simply uses the numeric value when it concatenates the data, not the formatting. To demonstrate:
  • 1. In cell A1, enter $5,000.00.
  • 2. In cell A2, enter the following formula: ="The total amount is " & A1
• Excel returns the following string: The total amount is 5000
• Fortunately, it's easy to treat a numeric value as a text currency string using the DOLLAR function, which uses the syntax:
  • DOLLAR(number,decimals)
• The number argument is the numeric value or reference to the cell containing the value. The decimals argument specifies the number of decimal places returned, but it's an optional argument. If you leave it blank, Excel uses two decimal places.
• To try the function, change the formula in cell A2 to the following: ="The total amount is " & DOLLAR(A1)
• Excel now returns: The total amount is $5,000.00

Tip: Refresh PivotTable data automatically (Excel 97/2000/2002/v. X/2003)

• You want to ensure that a PivotTable always reflects the most current information if the data on which it's based is volatile. Fortunately, you don't need to remember to refresh the PivotTable if it's been a while since you've used its workbook. Instead, you can configure Excel to automatically refresh the PivotTable when you open the file. To do so:
  • 1. Select a cell in the PivotTable.
  • 2. Open the PivotTable menu on the PivotTable toolbar.
  • 3. Select Table Options.
  • 4. Select the Refresh On Open check box.
  • 5. Click OK.

Tip: Apply commonly used number formats with shortcut keys (Excel 97/2000/2001/2002)

• Inevitably, you come across worksheet cells that use number formatting that's inappropriate for the type of data they display.  For example, a formula that processes date values may automatically return a value formatted as a date, even though it should really appear as an integer.  Or, involving a percentage value in a formula may produce a result that's inappropriately formatted as a percent.  To resolve such situations, you probably select Format | Cells from the menu bar and use the Format Cells dialog box to apply a more desirable number format.  In many cases, that requires a lot more work than is necessary.  Instead, you can use the following shortcut keys to apply several of the most frequently used Excel number formats:
  • [Ctrl][Shift][~] applies the General number format.
  • [Ctrl][Shift][$] applies the currency number format with two decimal places and negative values in parentheses.
  • [Ctrl][Shift][%] applies the Percentage number format with no decimal places.
  • [Ctrl][Shift][^] applies the Scientific number format with two decimal places.
  • [Ctrl][Shift][#] applies the Date format in the form 15-Mar-04.
  • [Ctrl][Shift][@] applies the Time format in the form 12:00 AM.
  • [Ctrl][Shift][!] applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.

Tip: Copy and paste noncontiguous ranges (Excel 97/2000/2002/v. X/2003)

• If you've ever tried to copy and paste noncontiguous ranges, you may have found that Excel refused to comply with your wishes. Although the error message Excel supplies can lead you to believe that doing so is impossible, you actually can copy and paste noncontiguous ranges--as long as the range selections are consistent among the columns involved. Even better, Excel pastes the data as a contiguous range. To demonstrate:
  • 1. Open or create a workbook that contains data in range A1:C15.
  • 2. Select range A1:A3.
  • 3. While holding down the [Ctrl] key, select ranges A8:A15 and C1:C3.
  • 4. From the menu bar, choose Edit | Copy. Excel returns an error message informing you that the command can not be used on multiple selections.
  • 5. While holding down the [Ctrl] key, select range C8:C15.
  • 6. Once again choose Edit | Copy from the menu bar. This time, Excel displays the usual marquee border indicating that the cells will be copied.
  • 7. Select any empty cell in the worksheet and choose Edit | Paste from the menu bar. Excel pastes the data without any of the gaps that separate the source data.

Tip: Easily focus attention on a worksheet range (97/2000/2002/v. X/2003)

• If you ever need to zero in on a specific range in Excel, there's an easy way to magnify the data so that you focus all your attention on it. First, select the appropriate range. Then, simply open the Zoom dropdown list on the Standard toolbar and choose Fit Selection. Excel magnifies the view of the data by whatever percentage is necessary to fill the viewable area of the file's window, up to 400%. When you want to restore your normal view of the worksheet, simply reopen the Zoom dialog box and choose 100%.

Tip: Easily check a range selection's dimensions (Excel 97/2000/2001/2002)

• Judging a range's size can be a challenge, particularly if the range extends beyond one viewable screen's worth of worksheet real estate.  Fortunately, there's an easy way to tell how many rows and columns you've selected.  When you select a range, the Name box next to the Formula bar displays its dimensions as long as you have the mouse button pressed.  For example, the Name box displays 5R x 7C when the selected range is five rows by seven columns.  This can be handy for situations that require you to select a specifically sized range, such as certain copy/paste scenarios, transposing data, or creating array formulas.

Tip: Clarify data on line and area char with drop lines (Excel 97/2000/2001/2002)

• Although Excel's 3-D charts do a pretty good job of simulating 3-D objects on a 2-D screen, it can sometimes be difficult to visually line up a data point with its corresponding position on the chart's category axis.  This is especially true when working with area or line charts.  The task can be even more difficult if the chart has been rotated or the perceived angle of elevation has been changed.  Fortunately, you can add drop lines, which are visual aids that help anchor each data point to the category axis.
• To display droop lines, select a data series on the chart and then choose Format | Selected Data Series from the menu bar.  Then, click on the Options tab.  Select the Drop Lines check box and then click OK.  Vertical lines now extend from each data point to the category axis, eliminating confusion and guesswork.  Note that this formatting option can also be applied to 2-D area and line charts.

Tip: Use a graphic file as a worksheet background (97/2000/2002/v. X/2003)

• You can easily replace the default white background that Excel applies to all worksheet cells with a picture. To do so, choose Format | Sheet | Background from the menu bar. Then, locate and select a graphic file. Most common file types are supported, including BMP, TIF, JPG, and GIF. Click the Insert button to apply the background. Excel displays the image in a tiled format. Note that the background is only visible onscreen--it won't appear on printouts. To remove a background graphic, choose Format | Sheet | Delete Background from the menu bar.

Tip: Print PivotTable groups on separate pages (97/2000/2002/v. X/2003)

• A PivotTable can group data summaries using multiple row fields. For instance, a PivotTable report might provide sales revenue data summarized by several nested levels of detail. At the top level, the PivotTable summarizes by state, then by county, then city, then individual store, and finally by employee.
• When a PivotTable incorporates multiple row fields, you can print groups on their own pages. For instance, you may want to print a new page whenever a new state value is encountered. To set up this printing option, double-click on the appropriate field. Then, click the Layout button. Select the Insert Page Break After Each Item check box and click OK on the open dialog boxes. Note that you can enable this setting for all but the lowest level of grouping fields (the rightmost row field).

Tip: Quickly size columns and rows to fit your data (Excel 97/2000/2001/2002)

• It's incredibly easy to modify a worksheet's column and row sizes.  All you have to do is click and drag the separating line between two column or row headings.  However, if you're constantly adjusting the sizes to fit changing data, there's an even easier way to ensure that a column or row is big enough to display your data.
• Instead of dragging the heading separator line, double-click on it.  When you do so on a column separator, Excel automatically resizes the column so it's wide enough to display the widest item in the column.  Likewise, double-clicking on a row heading separator changes the row height to accommodate the tallest row entry.
• You can apply this trick to multiple columns and rows at once.  To do so, select the multiple row or column headings that you want to resize.  Then, double-click on a separator line associated with any one of the selected headings.

Tip: Speeding data entry with AutoComplete (Excel 97/2000/2001/2002)

• You hardly have to do any data entry before you stumble across Excel's AutoComplete feature.  As you enter text into a cell, Excel checks the other adjacent cells that precede it in the column to see if any existing entries match what you're entering.  As soon as Excel detects a possible match, it fills the cell with the anticipated word.  You can then exit the cell to accept the suggestion keep typing to remove the suggested word and finish your entry.
• For example, open a new workbook and enter United States in cell A1.  Then, enter U in cell A2.  As soon as you press U, Excel displays United States in the cell.  Now, press the [Down Arrow] key to select cell A3 and enter United Kingdom.  Until you press the K key, Excel assumes that United States is the entry you want to make.  As soon as you press K, Excel clears the suggested entry and you can finish typing kingdom.  Finally, select cell A4 and enter United States again.  excel can't differentiate between the prior entries until you press the S key, at which point it makes an assumption about your current entry.
• In a case like this, waiting to get to the character that differentiates similar entries can be tiresome.  Fortunately, you can speed the process.  to do so, press [Alt] and the [Down Arrow] key whey you begin your new entry.  For example, select A5 and press [Alt][Down Arrow].  Excel displays a dropdown list of the column's previous entries.  Use your mouse or keyboard's directional arrows to select an item and press [Enter] to insert it into the cell.  You can also display the item list by right-clicking on a cell ([control]-clicking on the Mac) and choosing Pick From List.
• When working with a long list of column entries, enter the first few letters of the word you're looking for prior to displaying the dropdown list so you don't have to scroll through alot of entries.  To demonstrate, enter Canada, France, Uganda, Ukraine, and Zambia beneath the entries in the current column.  Now, let's say that you want to enter United States again.  In the column's next available cell enter Un and press [Alt][Down Artrow].  The dropdown list opens and the first entry matching the criteria, United Kingdom, is selected.  Although you need to manually select the United States item, this technique is much faster than scrolling through the entire list or typing the name until Excel recognizes it.

Tip: Easily remove unprintable characters from entries (97/2000/2002/v. X/2003)

• We recently told you about the TRIM function, which removes extra spaces from a text string. Unnecessary spaces are a problem you often run into when importing data. Another common occurrence that plagues imported data is the presence of unprintable characters. For instance, text might contain control characters that indicate carriage returns or line feeds. When viewed onscreen, they often appear as small squares within cell entries. You can easily remove such characters using the CLEAN function.
• Let's say your data is in cell A1. In cell A2, simply enter the formula
  • =CLEAN(A1)
• This returns a string without the offending characters.  You'll usually want to save the results as static values so that you can eliminate the original inappropriate entries. To do so, select cell A2 and choose Edit | copy from the menu bar. Then, choose Edit | Paste Special from the menu bar, select the Values option button, and click OK.

Tip: Quickly create hyperlinks to data in Office applications (Excel 97/2000/2001/2002)

• You're probably aware that Excel has long been able to display hyperlinks in worksheets.  For example, entering www.businesswebsitelinks.com/ in a worksheet cell automatically creates a hyperlink by default.  You may also already be aware that worksheet hyperlinks can point to data stored in Excel worksheets, as well as information stored in Word and PowerPoint files.  Even if that's the case, you may not realize how easy it is to create hyperlinks that point to data stored in Office documents.  Ordinarily, you might use Hyperlink dialog box to create your hyperlink.  In many cases, there's an easier way to create a hyperlink.  In fact, it's basically as easy as Copy and Paste.
• For example, say you want to create a hyperlink on the first sheet in a workbook that pints to data on the last sheet.  First, select the cell containing the data to which you wan the hyperlink to point.  Then, choose Edit | Copy from the menu bar.  Next, select the cell that will contain the hyperlink to point.  Then, choose Edit | Copy from the menu bar.  Next, select the cell that will contain the hyperlink.  Finally, choose Edit | Paste As Hyperlink from the menu bar.
• This approach works best when the cell to which the hyperlink points contains text data.  In such a case, the text you copy is used as the hyperlink's text.  When the destination cell contains numeric data, which includes dates, the hyperlink instead uses the sheet name and cell reference for the hyperlink text.  This means you have to edit the hyperlink anyway, in which case the standard Insert | Hyperlink approach actually may be more efficient.
• As mentioned, Excel's hyperlinks can point to data in other Office applications.  The Paste As Hyperlink technique can be used with this data as well.  For example, open a Word document and select some text.  Copy it as you normally would, then open an Excel workbook and choose Edit | Paste As Hyperlink.  Clicking the newly created hyperlink displays the appropriate section in the Word document.

Tip: Customize Excel's default workbook (97/2000/2002/v. X/2003)

• If you'd like certain settings to always be applied to new workbooks, you can override what file Excel uses as the basis for new files. All you have to do is create a special template file and save it in your XLSTART folder.
• Before you actually save the file, you need to identify where your XLStart folder is located (or your Startup: Excel folder if you're using a Mac). The location varies depending on both your Office version and operating system, so your best course of action is to use your operating system's Find or Search feature to locate the folder. You may actually find multiple copies of the folder, depending on whether your system incorporates user profiles--choose whichever makes the most sense for your needs and make a note of its path.
• Once you have the folder path, configure a workbook as you'd like and then save it as a template. To do so, choose File | Save As from the menu bar. Next, choose Template (*.xlt) from the Save As Type dropdown list (the Format pop-up menu on a Mac). Using the Save In dropdown list, locate and open the XLStart or Startup: Excel folder. Finally, change the File Name setting to Book.xlt (just name the file Workbook on a Mac) and click Save.  

Tip: Always open a specific workbook whenever you launch Excel (97/2000/2001/2002)

• If you almost use a particular file when working with Excel, you should know that it's possible to have Excel automatically open it for you every time you start the program.  When you launch Excel, it checks two folders and attempts to open all of the files contained within them.  To open a file automatically, simply move it into one of the folders.  If you don't want to move the file, you can instead place a shortcut to it in one of the folders.
• The folder that Excel checks by default is named XLSTART.  Your operating system affects its location, so the easiest way to access it is to use your operating system's built-in Find or Search feature.  In addition to the XLSTART folder, you can specify another folder that Excel should check.  To do so, choose Tools | Options from Excel's menu bar.  When the Options dialog box appears, click on the General tab.  Then, enter the folder path in the Alternate Startup File Location text box.  (In Excel 2002, the text box is labeled with a more descriptive name:  At startup, Open All Files In.)  Once you've entered the appropriate folder path, click OK.

Tip: Prevent startup Excel macros from running (97/2000/2001/2002)

• Macros are supposed to make your life easier, but sometimes they may get in the way.  For instance, you may have workbooks that automatically run macros when they're opened.  When you need to quickly check out some existing data as you're rushing off to a meeting, sitting through the macro can seem to be an eternity.  Fortunately, you can easily prevent startup macros from running.  Simply hold down the [Shift] key when you open the file.  Note that you'll need to keep the [Shift] key pressed throughout any warning dialog boxes that may appear during the process.

Tip: Remove extra spaces from cell entries (Excel 97/2000/v. X/2002/2003)

• Every once in a while you'll discover that some text data in a worksheet has trailing space characters. For example, information you've imported from another data source may pad all entries with extra spaces so that they are a uniform length. You can easily remove such spaces using the TRIM function. The syntax is simply:
  • TRIM(text)
• The TRIM function isn't just good for fixing trailing spaces. It removes all spaces except those that separate words. For instance, let's say that cell A1 contains the text:
  • THIS IS A TEST
  • In cell A2, enter the following formula:
  • =TRIM(A1)
  • The formula produces the result: THIS IS A TEST
• Keep in mind that if you want to permanently save the converted text, you'll need to convert the formula results to static values.

Tip: Easily sum filtered results (Excel 97/2000/2001/2002)

• When you use the AutoFilter feature to analyze a list, you'll often want to sum a field in the filtered results.  For example, if you have a table of all your company's sales, and you want to see the sum of the sales for a particular state, you can filter the list on the State field and sum the Sales field.  If you change the list to display a different state's sales results, you want the sum to update appropriately.
• To achieve such results, you can't use the SUM function as you normally might.  Instead, you must use the SUBTOTAL function as you normally might.  Instead, you must use the SUBTOTAL function with the filtered list.  Fortunately, it's easy to create the SUBTOTAL formula.  In fact, the AutoSum button automatically creates a SUBTOTAL formula when it's used in conjunction with a filtered list.
• To create an subtotal formula, select any cell in the list you want to analyze and choose Data | Filter | AutoFilter from the menu bar to display dropdown arrows in the lists top row.  Then, use any one of the dropdown arrows to filter the list for a particular criterion value.  Then, select the cell immediately beneath the column of numbers you want to sum.  Click the AutoSum button and you'll see that Excel inserts a SUBTOTAL formula into the cell.  Click the AutoSum button again to complete the formula.  You can now change the lists filter criteria and the SUBTOTAL formula will return the sum of whatever data is visible in the list.

Tip: Simplify moving related objects on a worksheet (Excel 97/2000/v. X/2002/2003)

• You can add some fairly elaborate graphical elements to a worksheet using Excel's various drawing tools. For instance, you can construct custom flow charts using AutoShapes and arrow objects, annotate a chart using callout shapes, or overlay WordArt and shapes to identify a worksheet's key sections. Unfortunately, a graphical masterpiece you've created can cause headaches if you find that you need to move it. Moving an intricate flowchart becomes a tedious reconstruction effort of moving and realigning each piece--unless you take advantage of Excel's ability to group objects. Once grouped, the objects can be repeatedly moved and formatted as a single entity.
• To create a group, hold down the [Shift] key and click on the individual items you want grouped. Then, open the Draw menu on the Drawing toolbar and choose Group (if the toolbar isn't visible, choose View | Toolbars | Drawing from the menu bar). You're still able to format or manipulate an individual item in the group if you need to. Simply click once on the group, wait a moment, and then click on the individual object. To ungroup the objects, simply click on the group, open the Draw menu, and choose Ungroup. Note that you aren't limited to grouping drawing shapes. You can also create groups that incorporate things such as charts, diagrams, and embedded Word objects.  

Tip: A simple way to convert numbers entered as text (Excel 97/2000/2001/2002)

• You may occasionally come across numbers in Excel that were entered with a preceding apostrophe ('), which formats the number as text.  You're especially likely to run into this problem when you import data into Excel from some other application source.  IF you want to convert the value to a genuine number, you may find that the task isn't as easy as you'd suspect.  You might logically assume that changing the cell's format to a number format would do the trick.  Unfortunately, such is not the case.  If you only need to convert one or two values, you can just re-enter them.  However, for a large volume, there are more efficient ways.
• If you have Excel 2002, there's an easy solution.  You can take advantage of a smart tag that addresses this specific problem.  Simply select the cell or range of cells that you want to convert.  When the smart tag button appears, click on it and choose Convert To Number from the action menu.
• Things are a bit trickier if you have an older version of Excel, but the process is still better than a brute force re-entry effort.  First, enter the number 1 in any blank cell.  Next, select the cell and choose Edit | Copy from the menu bar.  Then, select all the cells containing values you want converted.  Choose Edit | Paste Special from the menu bar.  Finally, select the Multiply option button and click OK.

Tip: Find all occurrences of a word at once (Excel 2002/2003)

• In Excel 2002 and later, you can quickly locate and navigate to all instances of a particular character string. To do so, choose Edit | Find from the menu bar or press [Ctrl]F to launch the Find And Replace dialog box. Then, enter the text you want to find in the Find What text box. Finally, click the Find All button. The dialog box expands to show a list of all the found matches. Clicking on the items in the list selects the relevant cell, which is especially handy when working with large worksheets. Another plus is that you aren't just limited to finding occurrences in the current worksheet--you can search an entire workbook. To do so, click the Options button on the Find And Replace dialog box. Then, choose Workbook from the Within dropdown list and click Find All.  

Tip: Change chart formatting without a dialog box (Excel 97/2000/2001/2002)

• When you modify chart formatting, you probably open one of the usual formatting dialog boxes.  For example, to change the shading and font properties for the chart's legend, you likely select the legend and then choose Format | Selected Legend from the menu bar.  While there's certainly nothing wrong with this approach, you may not realize that you can also format many chart elements using the same toolbar buttons that you normally use to format worksheet cells.  For example, the Bold, Underline, Font Color, Fill Color, and other font-related toolbar controls can all be used to format selected chart elements.  although chart-specific properties such as data marker styles and tick marks still require trips to formatting dialog boxes, you'll probably find that a lot of your work is simplified if you get into the habit of using the toolbar buttons.

Tip: Inserting a sheet based on a custom template (Excel 97/2000/v. X/2002/2003)

• When you insert a worksheet using the Insert | Worksheet menu command, Excel automatically bases the new sheet on the default template. However, you may not want to insert just a blank worksheet. For instance, say your company uses a special Project Tracking template. A workbook is dedicated to each client and there could be several project worksheets in each workbook. Instead of inserting a blank sheet, you want to insert a blank Project Tracking form. Fortunately, you can select what template Excel uses to create a new worksheet. Instead of using the menu command, right-click on a worksheet tab (or [control]-click on a Mac) and then choose Insert from the shortcut menu. The Insert dialog box appears, displaying your PC's templates. Simply select the appropriate one and click OK.  

Tip: Move or copy a sheet from one workbook to another (Excel 97/2000/2001/2002)

• If you work with several related workbooks, there may come a time when you decide you'd rather have all of the worksheets brought together into a single file.  Fortunately, you don't have to go through a lot of tedious copying and pasting into new worksheets.  Instead, you can simply move or copy the existing sheets directly into another workbook.
• There are two basic ways to accomplish these tasks.  You can drag the sheets to the appropriate file or use the Move Or Copy dialog box.
• To move a sheet from one workbook to another using the mouse, start by opening both workbooks.  Arrange the workbooks so that you can see both simultaneously.  An easy way to do so it to select Window | Arrange from the menu bar, select the Horizontal option button, and click OK.  When both workbooks are visible, simply drag the appropriate worksheet's sheet tab to move the sheet to the other workbook.  To drag and create a copy of the sheet, leaving the original in place, press and hold the [Ctrl] key when you drag the tab (use the [command] key on the Mac.)
• To use the Move Or Copy dialog box, open both the source and destination workbooks.  Then, select the sheet you want to move or copy and choose Edit | Move Or Copy Sheet from the menu bar.  When the Move Or Copy dialog box appears, choose the appropriate workbook from the To Book dropdown list.  Next, choose which existing sheet in the destination file should follow the new one from the Before Sheet list box.  If you want to copy the worksheet instead of move it, select the Create A Copy check box.  Finally, click OK.

Tip: Grab Excel's latest security and bug patches (2000/2001/2002/v. X/2003/2004)

• Microsoft has released a number of fixes for Excel 2000 that plug both new and previously addressed security holes. In addition, Excel 2004 for Mac receives its first service pack. Here's an overview of the recent batch of patches and updates. Details on the specifics behind the latest Excel security vulnerability are (probably deliberately) sparse at this point. However, the exploit involves a now familiar method of attack. It's a web-based attack that could give a remote evildoer complete control of your system if you to visit a maliciously coded website while logged into your PC with administrator rights. Microsoft lists the vulnerability patch as a Critical update for Windows users and as an Important update for Mac users. The affected versions are:
  • Excel 2000
  • Excel 2000
  • Excel 2002 (XP)
  • Excel 2001 for Mac
  • Excel v. X for Mac
  • (Excel 2002 isn't affected if you have Office XP Service Pack 3 SP3 installed.)

Tip: Reduce unnecessary stops while spell checking (Excel 97/2000/2001/2002)

• Spell checking worksheets helps you catch typos, but it can be cumbersome.  For instance, if a worksheet contains acronyms, abbreviations, or field names, you may find yourself repeatedly hitting the Ignore button.  Fortunately, Excel can be configured to skip some words that you may not want checked by ignoring all uppercase words.  To do so, run the spell checker on data that causes the Spelling dialog box to be displayed.  Then, select the ignore UPPERCASE check box and click OK.  You should be aware that this setting carries through to all subsequently spell-checked workbooks until it's specifically disable.

Tip: Eliminate warnings about non-existent macros (Excel 97/2000/2002)

• Depending upon your set security level, Excel will usually warn you if a workbook you're tying to open contains macros, providing you with the option to cancel the process.  However, sometimes you may wonder what Excel is warning you about--the workbook in question doesn't seem to contain any macros.
• Most likely, a macro was at one time associated with the file.  Even though the macro is now gone, the VBA module that is was stored instill exists.  The module is empty, but Excel displays the warning message whenever you open the file.
• Fortunately, it's usually easy to eliminate this nuisance.  However, before making any changes to your file, it's a good idea to make a copy of the workbook, just in case you accidentally make a change that prevents something from working correctly.
• When you're ready to modify the file, the first step is to see if there's a VBA code module associated with the file.  First choose Tools | Macro | Visual Basic Editor from the menu bar.  Next, look for a folder named Modules in the Project Explorer.  If the Project Explorer isn't visible, choose View | Project Explorer from the menu bar.
• Assuming you find a Modules folder associated with your workbook, double-click on it to reveal its objects.  The folder can contain several module objects.  Double-click on each module.  If the macros were deleted, you should see blank code windows (although they might contain statements like Option Explicit, you shouldn't see any actual macro procedures).
• Assuming that a module is indeed blank, you should be able to delete it with out any repercussions.  To do so, right-click on the module icon in the Project Explorer and then choose Remove Module1 (or the appropriate name) from the shortcut menu.  You'll then be given the chance to export it.  Since you should have nothing to export, just click No.  Repeat these steps for each module in the workbook,  Finally, close the VBE and save the workbook.

Tip: Apply a smart tag action to several cells at once (Excel 2002)

• Excel 2002 uses smart tags to simplify fixing several types of common errors.  When you want to apply the same error correction smart tag action to a number of cells, you don't have to work with each cell individually.
• For instance, say that you've imported data into Excel and it contains numbers formatted as text.  When you select an individual cell, the smart tag button appears and you can use the associated menu to convert the data to a number.  While smart tags are designed to be timesavers, applying such a change to each cell individually would be a pain.
• Fortunately, you can apply a smart tag action to a range.  The range must be contiguous and the cell that's active when you finish the selection must contain the appropriate smart tag.  After you make the selection, the smart tag button appears next to the highlighted range.  Simply choose the appropriate smart tag menu item to apply the action to all of the relevant cells in the selection.

Tip: Quickly add several sheets to an Excel workbook (97/2000/2001/2002)

• If you need to add worksheets to an Excel file, chances are that you use the Insert | Worksheet menu command.  There's a much faster way to go about this task:  Simply press [Shift][F11] and Excel adds a new blank worksheet before whichever sheet you were working on at the time.  If you need to add several worksheets to a workbook, you can use either technique to create all of the sheets at once.  While holding down the [Shift] key, click on the same number of worksheet tabs as new sheets you want to create.  Then, use either the menu command or shortcut key as you normally would.

Tip: Calculate the number of days in a month (Excel 97/2000/2001/2002)

• You may have struggled if you've ever needed to create functions based on the last day of any month, or the number of days in a variable month.  If so, this technique may come in handy.  There's a trick involving the DATE function that will return the last day of a month.  It even takes leap years into account.  The DATE function uses the syntax:
  • =DATE(year, month, day)
  • A formula such as
  • =DATE(2003,9,17)
  • returns the date for September 17, 2003.
• The trick for returning the last date in a month is to specify a month argument that's one after the month you really want and a day value of 0.  For instance, a formula of
  • =DATE(2003,3,0)
  • returns the date February 28, 2003.
• To calculate the number of days in a month, you can wrap the DATE formula in a DAY function.  For example, the formula
  • +DAY(DATE(2003.1,0))
  • returns 31, the number of days in December.
• As mentioned, the technique takes leap years into account.  For instance, the formula
  • +DAY(DATE(2003,1,0))
• returns 28, the standard number of days in February.  However, the formula
  • +DAY(DATE(2000,3,0))
  • return 29 because the year 2000 was a leap year.

Tip: Copy settings from one Excel chart to another (97/2000/2001/2002)

• If you've ever needed to see a set of data displayed as several different chart types, you probably wished that Excel's Format Painter could be used to copy any customized formatting you