Cleaning Up Regressions In Excel
The output of the regression tool in Excel is not very user friendly. It is a sign that you care about
viewers of your results if you clean it up a bit.
Absolutely Required, Every Time, Every Class, No Exceptions, Ever
- The output has no information about what dependent (Y) variable you are trying to explain. You must add
this by hand. I usually write something like "The Dependent Variable Is Such and Such" next to the cell
that says "SUMMARY OUTPUT".
- Depending on how you set up the regression tool, the labels for the X variables often are not that good.
Edit these as needed.
Optional, but Viewers of Your Output Will Appreciate It
- Limit the number of digits you show. There is no right number, but fewer is better (how often does
someone ask you to show more digits?). I often use 2 or even 1 to the right of the decimal point. Here's a
couple things to consider: some cells might have a lot of leading zeros. If so, you probably want to have
enough digits showing so that the two rightmost ones are non-zero. A second point is the number of
significant digits in your data: don't report to downstream users more digits than are significant. This
will just make them think your results are more accurate then they really are. You may use ALT+O+E for this.
- Size your columns so that all the labels and data are visible. You may use ALT+O+C+A for this.
- Center your columns. Use ALT+H+A+C for this.
Totally Optional, but Impressive
- Choose a font that "stacks digits on top of one another". These days, most default fonts do this, but
reading your spreadsheet will bug people if they don't line up.
- Choose a font in which zeros are easily distinguished from the letter "o". Usually this means choosing
one where zero is shown with a slash through it (so that it also looks like a null symbol from a mather
class). The recommended font for Excel 2010 is Consolas. This is probably on your computer, but it is not
the default font.
Adventurous?
It Bugs Viewers When Your Columns Don't Line Up On the Decimal Point - Two Methods to Fix This
- Method 1
- Highlight your column.
- Format your columns as "Currency" by using ALT+O+E.
- Set the symbol to "None".
- Align as needed.
- The pro of this method is that it's easy. The con is if someone does something that makes it apparent
that these cells aren't really currency and reveals your quick-and-dirty workaround.
- Method 2
- Highlight your column.
- Navigate to custom format using ALT+O+E.
- Set up 2 "picture codes" for how you want your cells to look. Put these in the "Type" box. The first one will be for positive
numbers, and the second will be for negative ones. Separate them with a semi-colon. In your "picture code",
spaces are spaces, dots are dots, and commas are commas - but zeros mean print a digit, and question
marks mean print a digit only if it is non-zero. So, entering this " ?0.00;-?0.00" in the "Type" box tells
Excel to leave a space at the front of a positive number, print the tens digit only if it is non-zero, and
then print 1 digit to the left of the decimal place, and two to the right. It tells Excel to do the same
thing for a negative number, just with a dash at the front.
- Align as needed.
- The con of this method is that there is a learning curve. The pro is that once you learn it, you can
potentially solve all your cell formatting problems.