The "HR" Syndrome

There is something amiss in the world of Human Resources when it comes to Excel. Other disciplines also suffer from the same problems. I am picking on HR because they seem to have the most posts with problems of the kind described below.

They (whoever they are) should teach data organization as part of HRIM certification. There must be something wrong with the way Excel is taught (Is it taught at all?) in an HR environment.

The first issue is that most of what many people want Excel to do are jobs much better handled by a database.

But it is a fact of life that management knows spreadsheets (even if they are HTML files from a database-driven dashboard).

Also, Excel is a basic part of Office while Access or any other database is not. Management has no concept of databases, only what a final report should look like. Numbers appear at the correct intersection of columns and rows by magic and totals as the “Bottom Line.” That is the format they want to see it in and that’s exactly what Excel looks like.

So, it has to be done in Excel. With simple enough data, the right table organization and data that isn’t too deeply cross-wired (co-dependent), Excel can be made to behave like a database. Put enough VBA behind it, and you can make it look exactly like a database for very specific applications. But that’s usually doing work that’s automatic in a database management tool.

So let me explain the problem: I call it “going wide instead of deep.”

Spreadsheets with this problem share the same characteristic: they look like the final report the person wants to see with dates going across the columns, people going down the rows and numbers or letters at the intersections. Also to complicate matters, each month is on a separate sheet.

While very intuitive, this is not the best way to organize the data, particularly if you ever want to look at it in some other format.

The solution is to organize the data in a normalized format, typically something like: Date | ID | Item | Value

For example: 9/4/2018 | Mickey Mouse | Vacation | 8 hours.

On the surface this looks like more work, but actually it isn’t. For example, a person calls in sick: 9/27/2018 | Goofy | Sick | 8

This takes probably less effort than finding the right sheet, going down the rows to find the right person on the sheet and going over exactly 27 columns to the correct date and entering in an “S.”

One advantage of this type of data organization is that it only logs data when data needs to be logged. In other words, you don’t have to enter anything for the other 29 days in the month when Goofy is not sick or on vacation. Likewise, if Goofy is a seasonal worker June to September, you don’t have to “carry” him and have him obscure your data October to May.

Secondly, it makes the data very “queriable” and you can organize, filter, display and summarize it in almost any format (including one with the dates going across and names going down) with a pivot table. Anything a pivot table cannot handle, Index / Match probably will.

I would say that this is a pet peeve of mine, but it’s not. HR people are by no means stupid. Somebody is not teaching this subject the right way to the people who need to know it. When (if) I retire, I’ll write a book “Excel for HR Specialists.”

Hi dflak,

I just watched this video and think some HR people might be very good with Excel.

https://www.youtube.com/watch?v=N0MesGk0VpI

Excel is a work horse, and, in its most basic form, relatively simple and easy to use - that is why it is so widely used, and why some people that use it, do not (yet) know the best way to lay data out.

It is part of our job to help correct that situation, not try and make the OP feel dumb (not saying you are, just saying). We all had to start somewhere on this ladder, they are at the bottom, where we all used to be at some point

Fortunately, Power Query now makes it SO much easier to normalise and organise data from sub-optimal formats.

I used to share your frustration entirely - "Why can't people just input normalised data?!"

Now I'm more relaxed - let them enter their data in a format they're comfortable with - I can always reformat it pretty easily into a data model, then report on that.

Yes, I've even built a function to normalize data. It's a problem I face because that's the way people give data to me too (and they are not HR ).

I've never taken a course in Excel nor any other computer related course, so I don't know what is being taught or how it is taught.

That's really a shame.

I've been blind to the issue since I'm a former DBA; data organization is so apparent to me that I can't see it. I've made numerous attempts to explain what normalized data is but I'm not totally satisfied with any of them.

I've seen definitions out there that explain WHAT it is, but still haven't come across anything that adequately explains HOW to do it.

I think I now have something to work on when I have some spare time.

I get your frustration. I used to share the same sentiment toward HR, as I had to clean up a lot of their mess (from auditing their data to generating report from their format)...

Then it hit me...
Rather than cleaning up their data, why not enforce strict rules upfront at data entry.

So I got working on project plan along with getting buy-in from stake holders and built tool(s).
3 months later, time spent on HR related data clean up was reduced to less than 15min/month from 1 day per month.
And HR was happy that they had easy to use tool that met their need.

Other times, when building data entry tool isn't an option... I work with stake holders to ensure compliance/audit process is in place to minimize data clean-up required.

In my observation, most Excel courses teach how to use functions etc, but do not focus on how to structure your data (though some website do focus on this aspect).
After all, Excel's strength is in it's flexibility and not in data integrity.

It's not HR in particular, but they seem most affected by the issue. I agree with and applaud your method: head off the problem at the source. It makes life easier for everyone.