5 Things to ALWAYS use in your spreadsheets

October 12, 2017

I’m a big believer that there’s a right tool for every job, and it’s a mistake to force the wrong solution when it doesn’t fit. However in the world of spreadsheet modeling, there are a few tricks that you should always employ.

1. PARSE FORMULAS

In Excel, you can split your formula into multiple lines by holding down the [ALT] key and pressing the [ENTER] key at the same time. This helps to break the formula into logical pieces making it more readable.

For example, this spreadsheet is asking you to count how many people are on Jones’ A teams.

You could write the formula like this:

=SUMIFS($E$9:$E$14,$C$9:$C$14,”Jones”,$D$9:$D$14,”A”)

But then it becomes a game of hunt and peck; trying to find commas, so you can mentally break down the formula into its component parts.

The better option is to parse out the pieces of the formula and add a few spaces like this:

 

2. TRIM YOUR DATA

When working with system data, you will frequently run into the problem of having too many spaces in between words.

For example, your system may have a city listed as “New York”. But a user might accidentally type an extra space in between the words making it “New   York” and upload that data. A second user might come along and accidentally type a few extra spaces at the beginning making it ”     New York” and upload that data. Now you have three different entries for New York instead of one.

Simply using the TRIM formula will remove any extra spaces from your work and keep your data clean.

 

3. REMOVE GRIDLINES

Gridlines typically don’t make formatting easier, BUT they do make errors harder to spot. Also, you can include a small, blank column to the far left to ensure that your first column is formatted correctly. Take a look at the before and after photos below. Which way makes the errors easier to spot?

Just try it for 2 weeks, and I think you’ll agree that gridlines have no place in your spreadsheets. You can remove gridlines by going to the “View” menu and unchecking the box for gridlines.

 

4. USE TABLE FORMATS

If you have never used table formats for your data, then you need to start now. There are so many benefits to doing this, that it’s difficult to list them all.

  • Allows you to write formulas using field names rather than addresses.
  • Automatically formats the data.
  • Automatically names table and fields.
  • Automatically expands ranges & fields when data is added.
  • Automatically copies down formulas.
  • Automatically sorts data.

For an example, look at the picture below. You can tell that the formula is summing SALES, based on WORK LEVEL, and based on LOCATION simply by reading the formula. You don’t need to lookup any addresses. Also, the table will automatically expand as you add data to the bottom.

To set up a table format first select your table, and ensure that there are no blank column headings. Then go to the “Insert” menu, and click “Table”.

 

5. SEPARATE INPUTS, CALCULATIONS, LOOKUPS, AND OUTPUTS

This seems like such a simple thing, but many people still do not do this in practice. If you get into the habit of separating and labeling the sections of your spreadsheet, you will see many benefits.

  • Makes work more clear and visually obvious.
  • Facilitates the hand off between the model creator and the user.
  • Encourages a simple, linear flow of information.
  • Reduces risk of errors from intermingling operations in the spreadsheet.

Leave a Reply

Your email address will not be published. Required fields are marked *