At my previous job as an IT Trainer, there were days when not a lot was going on. I would use this slow time to wander the halls, mainly to remind people that I was there. Software training is one of those things that most people feel they don’t have time to attend or don’t need. However, if they saw me out and about, they felt comfortable asking about something they had been having problems with. That is what happened when Darlene saw me.

Darlene had just started her new job as admin support in the department. She was still getting to know many of the documents that she had to work with.

“How weird to see you at this very moment,” said Darlene as I walked up to the service counter.

“What’s going on?” I responded.

“I need to update the “motorpool” spreadsheet and the formulas aren’t working,” she said as her eyes never left the monitor.

I came around the counter and she got up from her chair so I could take a seat. I clicked into various cells and then informed Darlene “the formulas are not working because there are no formulas.”

Darlene looked at me with a bit of confusion.

To clarify, I said “whoever created this spreadsheet did not setup formulas, they simply did the calculations manually and entered the result into the spreadsheet.”

“Wow,” was her response.

Wow, indeed. One of the main reasons for using Excel is to collect data. Many people keep lists in Excel. Even if you are not sure how you want to use this data or list in the future, setup your spreadsheet the right way. You will realize over time what data you want to extract. You can’t measure what you don’t track.

Take a look at this spreadsheet above. This is the “motorpool” spreadsheet that Darlene had inherited. It is for tracking what technician has checked out a car, what unit they are assigned to and how long the car is out.

What’s wrong with this?

Notice Column C (Usage), that the technician last name, hours checked out, and total hours are all in this column. Four pieces of data in one column. Notice also, that Column D and E are simply repeating the data in B and C, except it is the evening unit.

Starting in Row 15, is the Average Usage Per Unit. All of these totals are manually inputted. Not a single formula in this spreadsheet. The way this spreadsheet was structured,  Darlene could not pull any useful data out of it.

We revamped the data. Each piece of information had its own column. We separated out the time the vehicle was checked out and checked back in. This allowed us to set up a formula in Column G to automatically calculate the total hours the vehicle was out.

With the spreadsheet reworked, Darlene could now easily filter the data. If she wanted to look at only the Day shift, then she could filter out the evening shift.

No more manual calculations.

How you setup your spreadsheet really does matter.