Playing with Excel this Week

I’ve been fortunate enough that I can add some play into my work and still be accomplishing things. For instance, the early part of the week was discouraging as we got pressure to go manual in our reporting process. It was said that somebody in the field actually said, “Well, it worked the last week so I assumed it worked this week, so I didn’t look at the numbers.” Oh, boy, what a loaded gun. Then there were other complaints about our automated process, namely, it was wrong, and blah, blah, blah. My boss and I went back to our area to work on adjusting some things.

That adjustment means changing 40 plus files. I took my starter template to see how I would make the adjustments and it turned out to be a series of formulas. Now trying to roll out those changes to 40 plus files would be a nightmare as I’m inclined to start zoning out when doing a lot of repetitive actions.

Macros to the rescue! And so that is what I did: I practiced developing macros on the fly to perform the changes that needed to be done. This was a great opportunity to play around with macros and still accomplish something. I find that I am getting better at writing up macros, but I still have a long way to go.

Another playing opportunity came when we had a rush assignment from our client (the one that we’ve been doing budgets for). It was to gather data, going back to November, for approximately 23 units, to see if we could discern any improvements in the business. The initial request asked for a weekly set of data but one of our vice president said, “No, it will be monthly.” Then we got one of our system expert, who was familiar with our client’s system, to help up pull the raw data from the system. Finally, it fell upon me to somehow pull the information from all of the downloads. More play time!

I think everyone thought (except my boss) that the data would have to be manually entered. The client had asked one of the folks from the company they are overseeing (long story) on how it would be possible to get the data. She pulled ten reports from the system for one property for a period of one month. She said it took her ten minutes, but I don’t think she actually put the data itself into the spreadsheet. Our vice president said that just for pulling reports from the system, based on her ten minutes, it would take approximately 19 hours to do it for all 23 units, covering five months. We did it in maybe one to two hours. Then I spent maybe four to five hours figuring out which set of data I should use, setting up the formulas, and doing some cross checks.

The play was in figuring out which data to use, figuring out the formulas to pull in the data, and how to populate those formulas across all of those tabs. I normally don’t get to do this but in this instance, there were 23 tabs, representing 23 units, and each tab were formatted the identically. That layout of 23 identical tabs gave me an opportunity to try populating formulas across all 23 tabs simultaneously. What you do is highlight/pick all 23 tabs at the same time via the shift key and picking your tabs. When you do that, you are grouping your sheets. Once your sheets are grouped, any formula that you enter, any inserted rows or columns you make, any formatting you do, will automatically populate across all of the grouped sheets. Very nice way to speedily populate multiple sheets. For the formulas, I got a chance to practice using the Index/Match combo. Normally, I fall back upon vlookup but for this project, the vlookup would not work; the index/match combo did the job instead.

Once I was done with the main task, I decided to pursue further for my own edification some charting so I could see if there were any trends. I added in the ability to pick which metric to investigate and the associated sparklines would appear. I also had a rough counter that would indicate how many units were improving or not. It was kind of an interesting exercise. We might still need something like this, so I have this ready.

All in all, it was a productive set of plays this week.