Your Input Does Matter (MS Excel)

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.

Why Use Microsoft Excel? Need a reason to use Excel?!

Excel.

Chances are, that word alone stirs visions of lengthy spreadsheets and budgets with complicated formulas and charts.

It is true—Excel has become the standard in offices for pretty much anything that requires management of large amounts of data.

But, if you think Excel is only good for making you cross-eyed while looking at a bunch of numbers and financial reports, think again. There are tons of uses for Excel in business and at home.

Numbers floating off of a spreadsheet.

The problem is that when people are asked to find a use for it outside of business or number crunching, most people are stumped.

I teach an Excel class at a community college and I get students who say they hope they don’t forget what they have learned.

Maybe you learned a little bit of Excel at work and you want to get better at it, but you can’t think of a reason to use Excel.

One thing I usually recommend is create a personal budget. But using Excel, doesn’t always have to involve numbers. When you have data, any data, use Excel to store it. One of Excel’s simplest yet most fundamental abilities is organizing data.

You can create:

  • Grocery lists
  • Inventory
  • Budget plans
  • Expense tracking
  • Vacation schedule
  • Trip planner
  • Tracking birthdays and anniversaries

You don’t have to have a lot of data to use Excel.

Of course, there are plenty of fancy apps and tools out there to help you do a lot of what I just mentioned. But, if you want to become really good with Excel, then you need to use Excel.  

A passion of mine is cycling. I ride almost year-round. I have a few different bikes and I have to keep them maintained. What does that have to do with Excel?

I keep a very simply log of my bicycle maintenance. I track the date, which bike, what the maintenance was or the purchase made, cost and who performed the maintenance.

Really the purpose of this spreadsheet is so I can remember what I did with what bike. Because I am tracking this information in Excel there are some basic tasks I can do with this data, such as

  • Sort and Filter
  • Using the SUM function to see what my total expenses have been
  • Rename a worksheet
  • Add borders and fill colors
  • Use the Sumif and Countif functions
  • Create charts

Watch the video on my YouTube Channel.