Tips for the Excel User

My first pocketknife had two blades, a nail file, and a bottle opener. This gadget served me very well over the years on fishing and camping trips, or for just whittling on an old stick. Now I own one of those multipurpose pliers that has three knives, a saw, scissors, several files, screwdrivers (Phillips and flat head), and an ice pick. The concept of a simple pocketknife has been expanded so much that it's hard to see how anything less will do the job.

Much like that first pocketknife, the first spreadsheet program I ever used was a wonderful tool for financial analysis. However, today's Microsoft Excel® spreadsheet program has evolved into a multipurpose tool that enhances one's ability to store, analyze, and report on various sorts of data. It has so many functions that it's hard, at first, to know which ones will be truly useful.

Let's review some of the more useful yet less-known functions of Excel. Try them on an Excel spreadsheet.

EDIT/REPLACE

Have you ever copied a large section of a spreadsheet from one area to another and then needed to change parts of the text or formulas that are now spread over a large area? This can be done by manually, editing each cell one by one. A quicker way to do this is to highlight the area you want to change and use the Edit/Replace function. This allows you to replace one set of characters with another set all at once within the area you highlighted, even if they're in the middle of the text or the formula!

COMMENTS

One practice that's crucial in an environment where spreadsheets are shared among many users is to leave good notes about the inputs, changes, assumptions and other information. However, sometimes you don't want these notes to show up on the final printed copy. Try inserting comments into the cells of particular importance. When viewing the spreadsheet, you can easily tell which cells contain a comment, since they're marked with a little triangle. You can then read the note by resting the mouse pointer over the cell. There are also options in the print settings that let you print out the comments at the end of the spreadsheet.

AUTOFILTER AND SUBTOTAL

When doing research with a large database of information, you'll usually need to take a quick look at a subset of the database. The AutoFilter function, found under Data/Filter on the main menu, is an easy, quick way to filter out information temporarily from a database. Simply highlight the entire database, including any labels on the first row, and click on the AutoFilter. When this function is on, drop-down arrows will appear in each column of data, allowing you to filter data based on one or more of these columns. There is a number of options for filtering information, ranging from showing the top percent to applying a custom filter that allows you to apply two criteria.

After applying a filter, all rows that don't meet the filtering criteria are hidden, allowing you to view just the records that interest you. Using the Subtotal formula at the bottom of a filtered database will return the sum of numeric fields for just those rows currently showing. If the filtering criteria are changed, the subtotal formula shows the sum of the data for the new set of filtered rows.

TABLE LOOKUP

Table lookup formulas allow you to refer to certain values contained within a database in other areas of your spreadsheet. One example is using a database that contains shipping charges for all inventory items based on the zip code of their shipping destination. Two formulas, the Vlookup for vertically aligned databases and the Hlookup for horizontally aligned databases, allow you to refer to the appropriate shipping charge based on two criteria: the inventory item and the zip code for the customer. Once the inventory ID and ZIP code are input, the Lookup formula uses that information to arrive at the appropriate shipping charge. Although these functions can be a bit tricky at first, you'll find that they're extremely useful.

OUTLINES

Sometimes I need to present information in detail and in a summary. Try using the Group and Outlines functions on the detailed areas so that you can show or hide as much detail as needed with the click of the mouse. An outline can have up to eight levels of detail, with each indented level providing details for the preceding level. Microsoft Excel can automatically create the Outline groups and subgroups in cells that contain the sum formula for subtotals and totals. I've used this tool to hide details in an income statement, so that a summarized statement could be presented without having to copy all the data onto a new worksheet.

SCENARIOS

A great tool for budgeting or forecasting models is the Scenarios function. Often certain variables need to be changed to show the best- or worst-case scenarios. The Scenario Manager begins by defining which cells or variables will be changed for each scenario. Then you define the various scenarios and the values each variable should contain. The results of each scenario can be seen by clicking from one scenario to the next. Excel also provides the ability to merge scenarios. The Reporting Manager allows you to set up standard reports using the different scenarios you've set up for the worksheet.

The best way to master the tools reviewed here is to practice using them on the next spreadsheet you create. Sometimes it pays to take a step back and ask, "How can this basic spreadsheet be enhanced?" When these tools (along with a myriad of others that Excel has to offer) are applied properly, it's amazing what a spreadsheet can do.

Once these tools are mastered, it will be difficult to go back to that old pocketknife. When preparing your next spreadsheet, keep in mind the Excel tools discussed here, and use them often. Just as your trusty pocketknife comes in handy in many different situations, so will these tips for Excel spreadsheets.