Thread: Eight Handy Tools in Excel

IF YOU USE EXCEL JUST TO KEEP simple spreadsheets, you might as well be using a ledger and a quill pen. Well, okay, the ledger won't calculate totals for you. But
really, there are tons of valuable tools just below the surface that will help you with a wide variety of tasks. Here are eight of our favorites.

1. SUBTOTALS.

When you've got reams of data organized by date, it may be hard to see the big picture. Excel's built-in subtotal feature can help. Click Subtotal on the Data ribbon in Excel 2007 or choose Subtotals from the Data menu in 2003. By default it offers a sum-type subtotal at each change in the leftmost column, but you can pick the column and operation you prefer. You can even create multiple subtotals, perhaps for month, quarter, and year.

2. AUTOMATIC CONDITIONAL FORMATTING.

Conditional formatting in Excel 2003 is a drag. Excel 2007's automatic conditional formatting easily helps point out patterns in data. Highlight a group of cells and click Conditional Formatting on the Home ribbon. As you mouse over the choices, you see an immediate preview. You can give each cell a color that reflects its rank in the whole range of values, add a transparent data bar whose length reflects the cell's value, and more.

3. PIVOTTABLES.

Excel's PivotTable feature offers quick and flexible data analysis. Want to see how many times each value in a long column occurs? Highlight the column, click PivotTable on the Insert tab (in Excel 2003, select PivotTable from the Data menu), and click Finish. Drag the column-head field name into the Row Labels box and into the Values box (in Excel 2003, the Drop Row Fields Here area and the Drop Data Items Here area). You'll get a sorted list of all unique values in the column, and the number of times each value occurs.



4. DOCUMENT INSPECTOR.

Your Excel 2007 documents contain a lot more information than just rows and columns of data. They may include tracked changes, comments, private properties, and more. To examine (and clean out) the hidden data in an Excel 2007 document (sorry, you can't do this with Excel 2003), click the Office button at top left, point to Pre¬pare, and click Inspect Document. It's very similar to the Document Inspector in Word, but instead of seeking hidden text it looks for hidden rows and columns, hidden worksheets, and objects formatted as invisible.

5. PAGE BREAK PREVIEW.

Excel will print your spreadsheet using as many pages as necessary to display all the data both vertically and horizontally. If the last column doesn't quite fit, printing the spreadsheet may take twice as many pages. To avoid this annoyance, click Page Break Preview on the View ribbon (in Excel 2003 select Page Break Preview from the View menu). Now when you resize columns, change font sizes, or make other layout changes, you'll immediately see the effect on page breaks.

6. AUTOSUM.

After you've entered a column of figures, nine times in ten you'll end the column with a total. Excel makes it easy to total up a row or column. Click a cell just after the row or column, and click the AutoSum button from the Home rib¬bon in Excel 2007 or the toolbar in Excel.