2,001 Tips
Applications
Spreadsheets

Excel 7.0

Once Is Enough!

Eliminate repetitive entry of the same text string when entering data in a list. Right-click in a blank cell and choose Pick From List to display a drop-down list of all previous entries for that column.

More than the Sum of its Parts

Use AutoCalculate to automatically total the values of highlighted cells and display that value in the status bar. Right-click on that value and you'll see a list of other operations, including Average, Count, and Max and Min. Select one and AutoCalculate will perform that operation instead.

Get to the Bottom (or Top) Of Things

Use AutoFilter to find the top or bottom values in a numerical range. With AutoFilter activated (select Data/Filter/AutoFilter), choose Top 10 from the drop-down list in the column containing the values you wish to filter. Choose filter for the top or bottom values, the number of elements you want to see, and whether you want to filter by actual value or percentile.

Find Files Fast

Find the file you're looking for even if you can't remember the exact name. Use the File/Open dialog to select files based on Text or Property and Last Modified values. The search dialogs also include a drop-down button that displays the most recent searches executed.

Wend Your Way Through Workbooks

Move between multiple sheets within a workbook by right-clicking on the VCR-style controls in the left-hand corner of the status bar. Excel displays a list of all sheets in the workbook; select the one you want and Excel takes you there.

The Legend of the Data Maps

To display the more informative full legend of a data map, rather than the default "compact" version, double-click to activate your map, right-click on the legend and select Edit. In the Edit Legend dialog, turn off the Use Compact Format option. Customize your legend text.

Show What Your Maps Are Made Of

To display labels or data values for your Data Map, double-click to activate your map and select Tools/Labeler. To display the names of geographical features on your map (states, countries and so on), choose Map Feature Names. To display data values, select Values From. Hover your mouse over the map to see the labels.

Chart Data in Reverse

To reverse the order of data in a chart, select the Categories in Reverse Order option in the Format Axis dialog. Double-click to activate your chart, select the axis you wish to reverse, click the right mouse button, and select Format Axis. (You can also double-click to activate your chart and double-click on the axis.) Choose the Scale tab and click the Values in Reverse Order box.

Data Detente

Use the ampersand character (&) to join text and data. For example, if the value in cell D10 is 500 and you want another cell to display this as "500 dollars," choose that other cell and enter the formula +D10&" dollars" and press Enter.

Copycat Sheets

Copy all Page Setup properties-headers, footers, margins and so forth-from one worksheet to another. Activate the sheet that has the Page Setup properties you wish to copy. Choose File/Page Setup and click on OK in the dialog box. Activate the sheet to which you want to apply this setup. Choose Edit/Repeat Page Setup or press Ctrl+Y.

One-Step Centering

If you frequently center text across columns using the Format Cells dialog, place a button on the toolbar to do this in one step. Right-click on any toolbar and choose Customize. Choose the Text Formatting category and drag the Center Across Columns button to your toolbar.

Avoid Shared Spreadsheet Chaos

To maintain an audit of past versions of shared spreadsheets, keep a list of all conflicting entries made to a shared list and record which entries won and which lost. Choose File/Shared Lists and select the Editing tab. To track conflicts, select the Show Conflict History option when you activate file sharing for your worksheet.

Take a Spin Around a PivotTable's Data

After creating a PivotTable, double-click in any cell to create a list of all the data records behind that cell. The new list is added to your workbook on a separate sheet.

[ Go to 2,001 Tips Table of Contents ]