Four Things a Nightmare Excel Workbook Taught Me

When my students make an error during class, I remind them that we often learn more from making an error in Excel and correcting it than from doing it right the first time. The more Excel you know, the better your workbooks will be; that is a given. But if you’ve ever inherited a workbook that has been passed down from several individuals without a lot of Excel knowledge who had a very definite idea of what they wanted, but not how to get it in the best way possible, learning opportunities abound!

I recently took one on that fits that description. Here are the biggest lessons that came out of it.

1. Set up the data right the first time. By following the best practice principles of setting up a good list, the data will be able to be used for multiple purposes. Those principles are:

  • Define what’s in each column with column headers. And, be sure that there is nothing above those column headers, so Excel can automatically detect them for sort, filter and about a dozen other tools that need them. If you must include report titles or other information above the column titles, separate it from your column titles by a blank row. If you don’t like the way it looks, just hide the blank row.
  • One row contains one record. No one record should be on more than one row. For example, you would want what’s here on the left, but not what’s here on the right.










123 Main St.






123 Main St








Chicago, IL 60625

  • Keep the type of data consistent in each column. If it’s an amount column, all of the data should be values. If it is date data, all of the data should be validly formatted dates.
  • Finally, no completely blank rows and no completely blank columns.

2. Keep your data over HERE and your analysis over —> THERE. By putting your data and your analysis in different worksheets or workbooks, you will prevent bad things from happening to it. If you want to see totals with your data, convert it to a table (Ctrl+T).

3. If your list contains formulas and will keep growing, now and then use the Ctrl+` (~ key) to show all formulas and make sure they are consistently applied down a column. Use the same shortcut to return your worksheet to its natural state. Check this often as your list grows.

4. Use data validation to make sure that data is being entered consistently. This will avoid things like having ABCMicrosoft Excel Company’s data entered as ABC Co., ABC Company, ABC or The ABC Company in different spots all over the workbook. IF you create a named range of valid company names, and make every place in your workbook refer to this list, you will not end up with this potential nightmare. (Data tab, Data Tools group, Data Validation, Allow values from a list.)