Spreadsheets for Poets

I use Microsoft Word all day long, but I'm only a casual user of Excel, maybe a few times a year. Mostly I use it to track large projects. Most of those projects don't involve calculations. They involve listing things. Spreadsheets can be really good at listing things because you can sort, move, and play with the stuff in your lists.

For example, when I wrote my last book I interviewed college deans and had to keep track of their contact information, titles, questions I'd asked them to answer for the book, chapters their answers belonged in, and more. At the moment, I'm tracking products that we'll feature in the Last Gadget Standing competition we're running on Yahoo! Tech.

Both of these involve large, unwieldy lists of things that don't involve any real math calculations. But I use a few features that are great for "listers" like me that I figured I'd share with you.

Freeze Pane

A spreadsheet, as most of you know, is a bunch of rows and columns of information. If you have a lot of entries, you often can't see your headings. In my case, that usually means seeing a lot of "yes" or "no" answers that I can't recall the questions to. Freeze Pane locks an area of the spreadsheet by splitting it into a part that you can scroll and a part that remains locked. To lock rows, select the row below where you want the split to appear. To lock columns, select the column to the right of where you want the split to appear. To lock both rows and columns, click the cell below and to the right of where you want the split to appear. Then on the Windows menu, click Freeze Panes. For a walkthrough visit Microsoft's site. To remove the freeze, go to the Windows menu again and select Unfreeze.

Sort

Let's say I want to view my list of college deans by geography or find the ones who answered questions for my chapter on health. In most spreadsheets, this column is called the sort key. In Excel, you select Data and then Sort. You will be prompted to answer which column you want to sort on and whether your sort will be in ascending or descending order (either alphabetical or numerical). That's the simple version. There are quite a few options that allow you to do more complex sorts.

You can also use the AZ Sort button (an icon on your toolbar). Just click on it. If you do, make sure you select only a single cell to sort on. If you select the column, then just the information in that column would be sorted, causing all of the data to be mismatched with the data in the rest of the spreadsheet. I've done this more than a few times by accident and it's really painful to recover from, so be careful.

Comment

When you're searching through data, you see things that you'll want to remember. You can record those "aha" moments right in the cell of the spreadsheet. To put a comment in a spreadsheet, select Insert Comment. A box will appear on the screen. You can type your comment into the box. Click outside of the box and it closes. To edit or delete a comment, just right-click on the box. By default, comments show up as tiny triangles in the corner of a spreadsheet. You can change the default to view the entire comment by using the Tools Options menu and selecting to view both comment and indicator. When you print the spreadsheet, the default is not to see the comments. To modify this go to Page Setup and select Print Comments. You'll be prompted on how you'd like the comments printed.

Tomorrow let's look at the bane of my existence: printing a spreadsheet so that you can actually read what you've printed in an intelligible fashion.