78 Ways to Make Software Do More

Microsoft Excel 2003

People use the world's most popular spreadsheet program to manage everything from multinational corporations to Little League baseball teams. Unfortunately, many of Excel 2003's best tools are difficult to find. And some of its most useful features require a third-party add-on. These tips and downloads will jet you through your worksheets and make the program work the way you want it to.

Enter Data in a Flash

Why waste time typing a data series into Excel when you can use the fill handle to enter the data in one step? The program generates all kinds of numeric and text sequences for you automatically, and it can even figure out custom sequences as you create them. For example, type January into a cell, select it, and hover your pointer over the black square (the fill handle) at the bottom-right of the cell. Drag the fill handle down or to the right to fill in values 'February', 'March', and so on. Or enter another series--say, 5, 10--in adjacent cells (you'll need at least two entries to see the effect), select the cells, and drag the fill handle to have Excel fill in '15,' '20', '25', ... to the end of your selection. If you want Excel to copy the series, press the <Ctrl> key as you drag (it will repeat the series as many times as you have room for).

Summarize Your Data Automatically

One of my favorite Excel 2003 features is the program's data list, which treats a set of columns and rows as they would be treated in a database table. This enables the program to total columns automatically. It also allows you to change a column's summary operation. For example, if you have a table with columns containing customer ID numbers and rows recording each of their orders, you can create a data list by typing a set of column headers into a worksheet, selecting the header cells, and then choosing Data, List, Create List. Check the My list has headers box and click OK. A new data list appears, complete with a data entry row. Now if you'd like to add a summary row at the bottom of the table, you can do so by selecting Data, List, Total Row. If you would like to change the default 'Sum' summary operation that any cell in the Total row uses, you just select the cell, click the down arrow that appears, and choose a different summary operation (Average, Max, Min, and so forth).

Expand Your Named Ranges

Excel lets you define named ranges so your formulas are more descriptive. (For example, you can replace '=SUM(C2:C25)' with '=SUM (Bonus)'.) Although Excel's named ranges are usually static cell ranges, such as 'C2:C25', you can create a dynamic named range that expands as you add new data. To rename a range and make it dynamic, note the worksheet's name (such as 'Bonus') and the address of the first data cell in it that's not a column header (such as '$C$2'). Click Insert, Name, Define; enter the worksheet name in the 'Names in workbook' field. In the 'Refers to' field, type =OFFSET(Bonus!$C$2,0,0,COUNTA(Bonus!$C:$C)) with no spaces, and then press <Enter> to create the named range. This formula defines your named range, counts the number of nonblank cells in column C (starting from cell C2), and extends the range by that number of cells.

Name the Dates

If someone sends you a worksheet with dates in mm/dd/yyyy format ('10/23/2006'), you can add the days of the week that those dates fall on, with just a few quick swipes of your mouse. Simply select the dates, right-click them, choose Format Cells, pick Date in the Category list on the left, choose Wednesday, March 14, 2001 in the Type list on the right, and click OK. Now the days of the week are paired with their corresponding full calendar dates. To create a date with a custom pattern, choose Custom in the Category list, and in the Type box on the right, select the format you prefer. For example, selecting 'd-mmm-yy' will cause Excel to convert '10/23/2006' into '23-Oct-06'. For a list of date codes, browse to "Excel Custom Number Formats" and scroll down to the page's "Formatting Codes" section.

Find Target Values

Does your boss ask you questions like, "How much do we have to reduce our labor costs by so they represent no more than 20 percent of our expenses?" Put down that aspirin, and let Excel do the heavy lifting with its Goal Seek feature. Open the relevant worksheet, select the cell containing the labor-cost number, and choose Tools, Goal Seek. Type the target value in the 'To value' field, and enter the address of the cell that you want to change (in this example, the labor expense cell) in the 'By changing cell' field. Press <Enter>. If a solution is possible, Goal Seek displays the result. If not, it tells you there is no solution.

Excel Does PowerPoint

You don't have to depend on PowerPoint to create every presentation slide you make. Excel's often-overlooked View function presents your data pretty effectively, too. If you have a worksheet with five cells documenting, say, departmental expenses, simply select the cells and choose View, Zoom. In the dialog box, choose Fit selection and click OK. To save this view, select View, Custom Views, click the Add button, supply a name for the view, and press <Enter>. To recall the view, choose View, Custom Views, select the view from the list, and then click the Show button.

Share a PivotTable, Not the Data

Businesses large and small use Excel's PivotTables to summarize their important data. But PivotTables often contain sensitive data, so you might very well hesitate to share them. One simple strategy is to separate your PivotTable from its underlying data. To do so, right-click the PivotTable's worksheet tab at the bottom of the screen, and select Move or Copy. In the 'To book' drop-down, click (new book) and then OK. Excel will move the worksheet to a new workbook. Give the new workbook a different file name from the original, and save it. Now you can distribute the workbook holding your PivotTable without exposing the source data.

Really Hide a Worksheet

Some data is too sensitive to share. For example, a workbook may calculate a department's total sales commissions on a worksheet that everyone is allowed to see, but it may also list each salesperson's commission rate on another worksheet that you need to keep private. Hiding a worksheet by clicking the worksheet's tab and choosing Format, Sheet, Hide is too obvious a trick. (Anyone can easily undo it by selecting Format, Sheet, Unhide.) To hide a worksheet effectively, select Tools, Macro, Visual Basic Editor. In the Project pane in the upper left corner, click the name of the worksheet you want to hide. In the Properties pane below, choose the Visible property label, click the down arrow in the field to the right, and select 2 - xlSheetVeryHidden. The sheet won't show up in the Unhide dialog box! To reveal the worksheet later, change the property to -1 - xlSheetVisible or 0 - xlSheetHidden.