Know more about in Excel: Tips for Excel

Excel is not a statistical tools. But Excel is likely the software most often used for data analysis. So, even though we do not advocate its use, here are some Excel tips. I have these tips in the hopes that they make it easy to move your data out of Excel and into a statistical tools appropriate to your analysis. Hopefully, you will find them worthwhile, even if only in a "know thine enemy" kind of way.

Looking at Data in Excel: Freeze Panes, Splits, and Hides

Excel has rows and columns, not variables. If your variables have names other than the column headings (A, B, C), you are not able to see the names as you scroll through your data. Nor can you easily look at more than about 50 consecutive rows and 15 consecutive columns at a time. However, Excel does include options that make it easier to view your data as you wish to view it.

Freeze Pane allows you to pick a cell and freeze the rows above the cell and the columns to the left of the cell. Then, you can see row headings while scrolling down and row IDs as you scroll across your dataset. You can also use Freeze Panes if you wish to have certain columns or rows side by side for easy comparison, even though the columns or rows are not side by side in your dataset.

To use Freeze Panes, select it from the Window drop-down list. To unfreeze panes, select Unfreeze Panes from the same list.

Split is a similar command. It divides your Excel window into four parts, and you can scroll down or across two of the four parts at a time. To split your spreadsheet, select a cell and choose Split from the Window drop down.

Another option that can make viewing data in Excel easier is the Hide option for rows and columns. Simply select any rows or columns that are, for the time being, getting in the way. Then choose Row or Column from the Format menu, and select Hide.

Very Special Pasting

A surprising number of Excel's more subtle tricks use the Paste Special option after "copying" data. In Excel, if you wish to use a formula or a format from a certain cell repeatedly, you can do so using Copy and Paste Special. If you've calculated values using a formula, and you want to copy the values (and not the formulas) into other cells, you can use Paste Special. Paste Special can even be used to transpose data or (in a weird, way) apply mathematical operations to a set of cells.

The full set of options can be seen by selecting Paste Special from the Edit menu after selecting and copying cells.