Using Excel for analysis

Excel is a great analysis tool. A lot of people know that creating an Excel-readable report from your program is simple: just write your data into a text file, separating the columns by spaces or tabs. Here are some less-known features:

Put column titles into the first line. In the menu, select Data | Filter | AutoFilter. You’ll get a drop-down menu for each column title that enables you to display only the lines containing a specific value in that column:

AutoFilter in Excel

In this case, you can choose to display all lines where isGood = 0, for example. Selecting “(Custom…)” lets you display only the lines where the value satisfies a given condition. Finally, if the column titles are in a row other than the top, select it before choosing AutoFilter.

To make that top row not scrollable, go to cell A2, then select in the menu Window | Freeze Panes. The top row will be visible wherever you scroll the rest of the spreadsheet. In general, Freeze Panes makes all rows above and all columns left of the current cell non-scrollable.

Finally, sometimes Excel has a problem guessing the format of your file, e.g., it might decide that your separator is space whereas it’s really tab. There are two ways to fight this:

One is to open the file from Excel’s file menu or the corresponding toolbar icon. Excel will then ask you several questions about the file’s format.

The other is to use the CSV (comma-separated value) format. It’s textual, too, but it uses comma as the separator, and if a cell value contains a comma, you can enclose it in quotes. The file should have the extension “.csv”.

I hope these tips help you get more out of Excel.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: