There are lots of people out there who do really great and wonderful things in Excel, including advanced statistical formulas, trend analysis on huge data sets, or giant financial spreadsheets. I don’t do stats or huge data sets, but I have, on occasion, done some large financial spreadsheets (at least by non-financial officer standards). I have a semi-fond memory of working on a huge “options” spreadsheet for a large project on Dec. 23rd one year, just before I was going on holiday for two weeks and had to leave an updated version for our finance officer and senior management while I was gone.

It was a Friday, everybody else had left for the Christmas break, and there I was slaving away on this spreadsheet that for some reason WOULD NOT BALANCE. It was about $70M, and I was out about $113K somewhere in the middle, even though I knew the totals were right. Which meant looking through a table of about 20 columns and about 200 rows, even though I knew finding it was not going to alter anyone’s decision-making when using it, they were more doing macro stuff. But it took me a good 90 minutes to figure out that some formula that I had “adjusted” manually for a one-off adjustment was copied over to 7 other cells, so all of them were off by about $14K each. Not a figure you would see when looking at $113K spread out. Finding it was like a EUREKA! moment. It’s been 9 years and I still remember those meetings and that work very clearly.

So, yeah, I’ve done the financial spreadsheets, graphs, tables, simple lookups, complicated filtering, etc., but I’m not an expert. What I *do* like to do with it is create simple tools to help me with other projects. So I thought I would talk about some of my examples for fun. This post is what I consider “level 1” because it doesn’t use any real functions in Excel, just taking advantage of a large table layout.

Project tracking for my website

I frequently use my Excel spreadsheets like a simple project tracker. I create the various steps across the top and the phases down the side, and I track as I complete each step. Lots of people create pseudo-GANTT charts in Excel, and while I see the desire, I think people often get so bogged down in the beauty of the chart, they forget what they needed was a very simple linear tracker. They need the steps and the big parts, not the visuals as to when it is due, particularly if the critical path has no hard end-date/deadline. I know some managers who want their staff to update a critical path daily, even though they themselves (the managers) NEVER use it to make decisions, it is just giving them a warm fuzzy feeling that things must be on track. They forget that these are management tools, not simply presentation graphics. If you don’t use them to plan and manage, they’re not usually worth doing, but I digress.

I prefer tools that simply help me track things, and so I use Excel extensively when doing big stuff on my website. For example, when I was doing a recent redesign, I set up a simple table that had columns for 7 stages — take a category offline, convert entirely to Gutenberg blocks, flag if graphics need to be updated later, add in a new signature block, add in a new featured image, check the layout / look / feel, republish — and then rows for different categories like book reviews, movie reviews, astronomy articles, quotes, etc.

I like the approach because it lets me work horizontally or vertically. For example, taking a category offline is a horizontal workflow…I wouldn’t generally take everything offline at once, would I? Converting to Gutenberg blocks though might be something I could do vertically — all the categories at once — with certain tools. Similarly for a new signature block or replacing a featured image. But by the time I was down to checking layouts, that is more of a horizontal function again, making sure all the quote posts look the same. Some people wouldn’t bother doing the tracker, they would just have a to do list with each category. Or maybe supplement it with a separate to do list that was the horizontal workflow.

Those methods also work, but they are also weak in two areas. First and foremost, they won’t identify economies of scale where I can go vertical on all the categories at once. I would essentially lock myself into a single horizontal workflow and that’s it. Secondly, because the tool is a transitory work product i.e., when you’re done with it, it’s no good for anything else, people forget that the value is in being able to know exactly where you are in the process so the next time you work on the project (if you can’t work on it straight through), you know exactly where to re-start. You know where you left off. A to do list almost does that, but a tick box encourages you to finish a whole column or row, it’s not easy to indicate “partial completion”.

There’s another hidden element though, which is that a tracker like this also serves to ensure consistency in approach. I can’t take credit for any great insight here, hospitals figured this out long ago. They were trying to improve consistency in operating rooms, and in particular, avoid a problem where a sponge or a pair of scissors (!) could get left in a patient, the person would get an infection, and the patient would either have to be re-opened or die. So a bunch of ORs created a checklist-system based on some trials and experiments by efficiency experts.

One of the checklists that is still being used today is for the nurse assisting with the operation. They count sponges and equipment — the number going in, the number coming out. If they don’t match, the surgeon can’t close the wound. It sounds ridiculous, I know, but if you’re there doing a routine operation, do you remember when you’re done if you used 5 sponges or 6? How easy is it to forget if suddenly there’s a bleed, you try to staunch the flow, and you grab 2 instead of 1?

But hospitals didn’t stop with sponge counting. They found that simply having a checklist of the order in which you put a gown on after scrubbing your hand a set amount of times made sure the person doing it ACTUALLY didn’t forget a step. It’s the routine stuff that is easy to miss. And, to be honest, your brain goes on auto-pilot more than you think. They have video of people in these studies totally forgetting to wash their hands because someone spoke to them just before they did it, and their brain skipped over the step. They actually swear they did it, when the video shows they didn’t. But if they’re following the checklist, they know they didn’t do it. The goal is to reduce mental load so you don’t have to think about routine steps, the best method is already right in front of you.

This isn’t rocket science, I guarantee you’ve followed a similar approach before because all cooking recipes follow the same approach. A set of clear instructions to do the steps each time in the same order and get the same result. You don’t put the yeast in before the flour, for example.

In my case, I am in the process of drastically altering my photo gallery on my website, although the redesign part is done. Mostly what I am doing right now is populating the site with 15y of photos. About 250 galleries when I’m done. And I want them all to work seamlessly together in the same way. Which means I need consistency. So I doubled down on the microwork of Thomas Edison creating an assembly line breakdown of workflows, all the efficiency experts doing time and motion studies, and even the hospital systems creating workflow checklists, and I created my own grid. Here is a picture of what it looks like for my gallery process for 2008.

[singlepic id=6962 float=”center” w=”800″]

Yes, it looks anal-retentive. While the image is too small to see very well on the website, the columns across the top are all the steps to create one uniform gallery while the rows are the various galleries in the year. For example, going across the top, the steps are:

• Acquiring the image, which seems redundant, BUT some of my images are originally in paper format only, so it isn’t simply “taking the image”, it could be scanning it;
• Moving it from various devices around my house (including other people’s computers) on to my system, sorting by date, converting video to a format that can be uploaded to my site;
• Importing it into my photo management software, Mylio, and then sorting it, editing, running facial recognition to auto-add certain tags, updating metadata in three area, saving the metadata (yes that’s a whole separate step, sigh), and moving the files within my setup from “processing” to “complete”, i.e. ready for upload and backups;
• Within the website, there are two parts that happen relatively in tandem as certain parts of each have to be completed before the next step in the other area, so I end up creating a page (including page name, editing the URL slug from standard defaults to what I want it for the gallery, adding some special CSS coding, editing the breadcrumb, editing a a gallery description, editing any video descriptions and adding separate links, and setting some parameters if I want to tweak something from default which is very rare) AND creating the gallery (create the holder, upload photos and video, edit the internal descriptions, set up a link to the page above, add the gallery to an overall album in WordPress, confirm the main gallery image i.e. cover photo, and sort the photos and videos in the software if the defaults don’t get read perfectly); and,
• When that is all done, I have to test the page for look and feel, as well as functionality for extra gallery pages and that the videos are loaded properly, publish it, share it on FaceBook, and tweak the way it appears in the gallery submenu.

Down the side are the various galleries that I’m working on. Months are generally “obvious”, but sometimes in that month I’ll have a special event — a trip, a wedding, a baby shower — and I’ll want that as a separate gallery. Equally, I also might be planning a blog post about, I don’t know, using Excel as a project tracker. For that, I don’t want the images cluttering up my media library, nor do I want them in with my monthly photos or my special events. So I have a special gallery for the month called “Blog posts” where I can upload images that no one would ever “browse” out of context, so I put them in the blog posts gallery so I can insert them as per above. But I still have to do all the “right” steps to create that gallery too.

When everything is created, I go through the whole year to make note of any possible “blog entries” I want to write in the future about an event, or maybe as a “throwback Thursday” type post. Or even just to go to a specific image in the gallery and share that separately on FB because not everyone will click through and therefore might not realize there’s a great image of Andrea’s uncle hiding in that collection, for example.

Is it overkill? Perhaps. For the 2008 image above, I have 34 steps, and many of them in a given phase (such as moving images around) are relatively obvious. I don’t need the workflow most of the time. But when I finish a gallery, and I’m “done”, I’m surprised when going back through the workflow to double-check that I did everything, how often I suddenly realize that “Oh yeah, I never saved the metadata” or “I didn’t rename the final directory to the right naming convention” so to speak, and now that gallery will not sort properly later. Maybe I missed a step, maybe I got a phone call in the middle. Usually it means I was winging it and NOT following the workflow checklist that I created.

And it is easy to agree that it probably doesn’t matter too much. Why be so anal?

Well, two reasons. About a month or so ago, I was looking for two images in my collection. I knew approximately WHEN they had been taken, but not precisely. I just remember the year approximately. So I simply went in and searched using the tags. One came up immediately, the other did not. Huh? They should have both showed up. I spent another 15 minutes of searching to find the second one, only to realize the reason it didn’t show up was that I had never completed the metadata steps. Somehow missed it. Now, it was not yet in my final “check-off” stage as being complete, so the checklist hadn’t completely failed me, I would have noticed eventually when I got to it, but it added 15m of work that it shouldn’t have needed.

Secondly, once I complete those “desktop” functions, and I upload to the website, if I miss a big step like adding CSS code to the gallery, I can find myself in the middle of something else, but suddenly my site is NOT showing properly. It has the wrong menu, it has the wrong header, it has the wrong layout, etc. And I don’t know why. After all, everything is set properly, right? Well, not always. Without the checklist, I can get that proverbial phone call that distracts me, and while I’m not leaving a sponge in a patient who then dies, my website has a sponge stuck in one of the galleries, and the site looks septic. Because I didn’t count the sponges.

I could use a whiteboard, but it’s hard to simply “add a step” in the process on a large table. I could use MS Word, although admittedly, 34 columns might give it a tad case of indigestion. Excel works great because it can have as many rows or columns as I want or need. Word works great if the steps are very different, but if it’s something that is repetitive, a simple table tracker in Excel works fine.

That’s it, and as you can see, my example is not that complicated. Anal perhaps, but not complicated, just a simple table tracker. Hence why I call it “Level 1”. Let’s see what Level 2 looks like.