We covered a bit about formulas and conditional formatting today. I'm not going to talk about that. Instead I'll tell you about the most ridiculous Excel project I've ever worked on. It's one of the reasons why I tend to adopt a 'less is more' mantra. It's also why I know a lot of this stuff in the first place.
Many years ago, before working in higher education had ever occurred to me as a possibility, I did some temping work at British Rail to help pay for uni. I was based in an office above a terminus station with just two other people - the boss, who despite carrying himself with an extremely strict, professional manner nevertheless had an obvious screaming fabulousness bubbling away just under the surface, kept under a tight wrap presumably until the weekends hit, and his shy and efficient PA who was very meticulous about the tea area.
I'd been brought in to help modernise several internal systems to do with rolling stock or timetabling or something, honestly I can't remember. The existing set up was comprised of a seemingly infinite number of individual Excel sheets dating back to before British Rail stopped being the company's real name and they needed a system that could consolidate all that data, make it easier to pull various stats out and automate a bunch of number crunching.
They'd already had some of the work done but for whatever reason it needed finishing off and smartening up. The fact a major update had been left in some disarray should have set a few alarms off but I was young and grateful for some work that was at least in my general specialism area so I didn't question it and accepted the placement. With hindsight I should have insisted on a consultancy rate.
The first thing that was wrong with the 'system' was that despite the sheer number of documents that needed managing and despite the organisation having a lot of government resources to draw on it wasn't really a system at all. There were no databases or specialised software to be seen; instead the Excel documents remained, reorganised into some sort of filing system that probably wasn't all that different to the one it started with and they had all been deeply linked together through cell formulas often running over ten lines deep then stuffed full of macros, like a bowl of data spaghetti in too much sauce. They were literally treating this sprawling mess of spreadsheets like it were an application and Excel were the coding tool. Bits of it worked, but look at it the wrong way and cross-file cell references would break, lookups would fall like dominoes and #REF!s would cascade through the sheets like a tornado.
I'd taken this job (more accurately they'd accepted me) based on my decent computer skills and my claim to have a reasonable knowledge of Excel. I thought I had a reasonable knowledge; my expertise in using SUM and AVERAGE and even my ability to nest IFs as much as three or four deep was evidently not going to give me the experience level I needed.
I should have said something with that realisation, but the only other placement available was the one I was trying to get away from (in a giant refrigerator sorting through freshly sliced onions to pick out the rings for Burger King - the worst job I've ever had) so I was determined not to announce my own doubts.
The work environment also wasn't the most relaxed or comfortable either. The office had those high ceilings you get in old public sector buildings and was very sparsely decorated. I felt very exposed at my desk, made all the worse by the near silence of both my colleagues, only ever talking when it was required, no casual interaction save for the occasional arched eyebrow as the boss reigned it back in.
In short I was in at the deep end. I couldn't really talk about it with anyone, and I didn't want to quit, so I did the only thing I could: I learned the hell out of Excel very quickly. We had Internet (dial-up of course) but the web wasn't anywhere near the treasure trove we have today, so I buried myself in the help files and worked out how to do the things I needed bit by bit.
I started by tidying up the existing formulas - not rewriting them, just getting to know them by making things a bit neater. From there I went from the bottom up, fixing the easy things first while learning what was to come, so it never seemed like the work wasn't being done. I delved into pivot tables, learned ever more complex functions and even managed to modify some of the macros. By the time the contract ended they were still just a massive pile of Excel files, but the pile was a little smaller and better organised, the files relied on each other that little bit less and the macros were just a bit more efficient.
A small victory perhaps, but I learned a lot. Not so much in Excel itself, as I'm sure I've forgotten more than half of everything, but in how (not) to structure a project, and how to deal with pressure. Most importantly I learned that just because you might be able to create a multidimensional relational database and CMS out of a bunch of spreadsheets it doesn't mean you should. The moral being, use the right tool for the job.
Many years ago, before working in higher education had ever occurred to me as a possibility, I did some temping work at British Rail to help pay for uni. I was based in an office above a terminus station with just two other people - the boss, who despite carrying himself with an extremely strict, professional manner nevertheless had an obvious screaming fabulousness bubbling away just under the surface, kept under a tight wrap presumably until the weekends hit, and his shy and efficient PA who was very meticulous about the tea area.
I'd been brought in to help modernise several internal systems to do with rolling stock or timetabling or something, honestly I can't remember. The existing set up was comprised of a seemingly infinite number of individual Excel sheets dating back to before British Rail stopped being the company's real name and they needed a system that could consolidate all that data, make it easier to pull various stats out and automate a bunch of number crunching.
They'd already had some of the work done but for whatever reason it needed finishing off and smartening up. The fact a major update had been left in some disarray should have set a few alarms off but I was young and grateful for some work that was at least in my general specialism area so I didn't question it and accepted the placement. With hindsight I should have insisted on a consultancy rate.
The first thing that was wrong with the 'system' was that despite the sheer number of documents that needed managing and despite the organisation having a lot of government resources to draw on it wasn't really a system at all. There were no databases or specialised software to be seen; instead the Excel documents remained, reorganised into some sort of filing system that probably wasn't all that different to the one it started with and they had all been deeply linked together through cell formulas often running over ten lines deep then stuffed full of macros, like a bowl of data spaghetti in too much sauce. They were literally treating this sprawling mess of spreadsheets like it were an application and Excel were the coding tool. Bits of it worked, but look at it the wrong way and cross-file cell references would break, lookups would fall like dominoes and #REF!s would cascade through the sheets like a tornado.
I'd taken this job (more accurately they'd accepted me) based on my decent computer skills and my claim to have a reasonable knowledge of Excel. I thought I had a reasonable knowledge; my expertise in using SUM and AVERAGE and even my ability to nest IFs as much as three or four deep was evidently not going to give me the experience level I needed.
I should have said something with that realisation, but the only other placement available was the one I was trying to get away from (in a giant refrigerator sorting through freshly sliced onions to pick out the rings for Burger King - the worst job I've ever had) so I was determined not to announce my own doubts.
The work environment also wasn't the most relaxed or comfortable either. The office had those high ceilings you get in old public sector buildings and was very sparsely decorated. I felt very exposed at my desk, made all the worse by the near silence of both my colleagues, only ever talking when it was required, no casual interaction save for the occasional arched eyebrow as the boss reigned it back in.
In short I was in at the deep end. I couldn't really talk about it with anyone, and I didn't want to quit, so I did the only thing I could: I learned the hell out of Excel very quickly. We had Internet (dial-up of course) but the web wasn't anywhere near the treasure trove we have today, so I buried myself in the help files and worked out how to do the things I needed bit by bit.
I started by tidying up the existing formulas - not rewriting them, just getting to know them by making things a bit neater. From there I went from the bottom up, fixing the easy things first while learning what was to come, so it never seemed like the work wasn't being done. I delved into pivot tables, learned ever more complex functions and even managed to modify some of the macros. By the time the contract ended they were still just a massive pile of Excel files, but the pile was a little smaller and better organised, the files relied on each other that little bit less and the macros were just a bit more efficient.
A small victory perhaps, but I learned a lot. Not so much in Excel itself, as I'm sure I've forgotten more than half of everything, but in how (not) to structure a project, and how to deal with pressure. Most importantly I learned that just because you might be able to create a multidimensional relational database and CMS out of a bunch of spreadsheets it doesn't mean you should. The moral being, use the right tool for the job.
Comments
Post a Comment