WTF #01 – Sharing a workbook

This is the London Olympic Stadium at full capacity of 80,000. They won’t all get in or out through one turnstile!

That is the problem we have when ten people need to be sharing one spreadsheet for update at all times!

In fact the Olympic Stadium has 10 turnstile entrances.

In this WTF you’ll see how we can make an unlimited number of people, not only access and update the same data simultaneously, but also simplify the various different activities the users interact with the spreadsheet for.

The result, you’ll see, is very different to shared workbooks, or online spreadsheets like Google Sheets and the various external Cloud solutions out there.

So, if you and your colleagues are constantly falling over each other to get to one spreadsheet this is the OMG for you.

(60 sec)

WTF #02 – Group Consolidation

Is it easier to count your sheet when they’re scattered all over your farm, and your neighbour’s farms? Or is it easier to count them when they’re rounded up into one place?

With spreadsheets, isn’t this what we do? When we need to consolidate hundreds of spreadsheets into group totals and subgroup – we use thousands, or millions of links to link them into a pyramid. We have to do that because the data is scattered over hundreds of spreadsheets, over many folders on many drives.

But, this problem disappears when we bring all this data into one place.

How do we get the data into one place? How do we do what the sheep dog does to the sheep? Watch this OMG.

(52 sec)

WFT #03 – Export to Excel

So your ERP system produces a report every month. You import it into Excel. Or the report is already supplied as an Excel workbook.

You do some work with it during the course of the month. You write comments, including Cell Comments. For example, say it’s an Account Receivables Ageing report.

Great. But here’s the WTF.

Next month you get another Excel workbook report from the ERP system. You need to transfer your notes, all you notes, from last month’s spreadsheet into this one.

This is a pain.

Watch the solution I suggest. If you’ve got this problem in your workplace .. it WILL make you happy.

(45 sec)

WTF #04 – Annual Budgeting

This is a big one.

Here’s the problem in a nutshell. There’s a deadline for the annual budgeting exercise. Budget templates have to be ready in 2 weeks.

Although a lot of work is involved in preparing it, we can’t prepare it early. Why? Because a lot of the information we supply in the templates are not available until right up to the deadline.

For example, the figures from the latest Departmental Accounts won’t be available until shortly before the deadline. The cost drivers depend on the management accounts; not expected until next week. And group structures often change, so if linked spreadsheets is how you plan to consolidate you don’t want to start working on links until the final structure is known. And even that could still change.

There’s heaps of tasks on the critical path. Often we’re dependent on other departments to furnish much of it. And our work can’t actually start until the last minute. The result? Someone (and you don’t want that to be you!) will need to do 6 months work in 6 hours!

And do all that without a single mistake.

No chance!

Watch this solution. It’s based on a real-life crisis where the original spreadsheet model had 377 million links! My solution (what we actually did to solve it) has none. And, in my client company, it continued to be reused in successive years – for years and years.

(60 sec)

WTF #05 – Monthly Roll Forward

The month end Roll Forward, thankfully, isn’t a problem at month end. But just as we say, ‘phew’ thank god that’s the month end done we have a whole heap of spreadsheets that need to be Rolled Forward to next month. And it’s often a nightmare.

These are the Forecast type of spreadsheets. Where the columns are rolling. It usually involved deleting columns, adding new columns, adjusting links, avoiding broken links. If its a model involving many departments you might have links to other spreadsheets (as in WTF #02) that also need to be adjusted carefully.

If you make a mistake you’re basically F***ed!

In this solution you’ll see a One Click roll forward idea (like this) you can use.

Watch this video explainer I made in 2013.

(40 sec)

WTF #06 – Pass The Parcel

Every time we email a spreadsheet to a colleague, copied to half a dozen others, including your boss and the bosses of your colleagues you start a chain that creates potentially 20 new spreadsheets. Each of which can evolve into yet another version of your original spreadsheet. And you have a dozen people having to wade through information that’s way beyond the boundaries of their specific interest.

It may not be the original spreadsheet that goes viral. Each recipient potentially uses some information from your spreadsheet to plug into theirs, copy paste usually, and it’s that spreadsheet that makes the next leg of the email journey.

In this OMG you see why it is unnecessary to email spreadsheets.

Yeah, I know that’s an audacious statement. That’s why you’ll be amazed when you see my solution.

(52 sec)

WTF #07 – Pointless Work

Now here’s something we do a lot. Pointless work!

We get reports from the ERP system. Either as Excel workbooks or CSV files that we import into spreadsheets.

Then we link them up, do some copy paste, manually make them pretty, and when we’re done we do several things. We email them to a number of people. We, and others, input the results of your work back into the ERP system.

One such scenario is where we get departmental accounts on several separate Excel reports, that we group consolidate in one or many ways.

We don’t need to do that. You saw on WTF #02, if we get the data into one place we don’t need to use linked spreadsheets. The data is already in one place when it’s in the ERP system. So, why the f*** do we split it up only to bring it together again manually?

Watch this video to understand this better. And see how to avoid this nonsense.

WTF #08 – Security and Sensitive Data

You have a spreadsheet with the data for many departments. You need to distribute it to several department heads. But only the data for their department.

How do we do that? Well, usually we split up the spreadsheet into separate workbooks. Password protect them and send them out.

You may know, in Excel, there isn’t a built in method for password protecting separate parts of the workbook.

But now we have some new problems. We can’t do analytics on all of the data, without a mess because it’s no longer all together.

The solution?

This shouldn’t come as a new idea to most people, but I’m sure it will – because it’s new in the context of spreadsheets. We can make users … login. Then we can restrict them to only get the data they are entitled to. Only update data they are allowed to.

I say it shouldn’t come as a new idea because logging into things is what we do all the time nowadays. So why not use the same idea here?

Watch this. I’ll show you how. TBA

(58 sec)

WTF #09 – Progress Tracking

Whichever progress tracking you use for your month end processes the chances are you need to update it manually when a task has been completed.

Usually the tasks on the checklist involve things that other people have to complete – a task is complete when you receive some figures from them, often on a spreadsheet.

If you’ve grasped the principle I’m promoting, they don’t have to send you a spreadsheet. Then you don’t need to manually update a checklist to keep track of how the month-end is progressing?

Watch this OMG. See how that works. TBA

(32 sec)

WTF #10 – Who did what?

Everybody knows this. On a spreadsheet we can’t tell who changed what, or when.

But we can. When we store the data outside the spreadsheet each update is uploaded to a table. It is just as easy to insert an extra step to add the details of the update to an additional table. That can serve as a log.

If ever it’s necessary to look up who did what, and when, we just need to look up that log – which of course we can see on our spreadsheet tab that we can call ‘log’. From now on, we can never not-know who did what.

WTF #11 – Formulas take ages to calculate!

Some formulas are unnecessary. A common example is a VLOOKUP on 100,000 rows. Say two lists are imported from two separate enterprise systems. You need to do a look up, one from the other. Traditional Excel spreadsheet practice says we use a lookup formula.

If we upload the two lists to tables in a data model, either internal or external, the lookup is done by a join. And then a query will retrieve the looked-up result into the spreadsheet. This should go like lightning.

WTF #12 – Let’s go Contactless!

Do you remember how we paid for things in shops years ago?

We wrote checks and handed over our check guarantee card. Now we can just touch the Contactless terminal with our bank card. Job done.

How many user-interactions are in your spreadsheet processes? I’m guessing (and this must be true, surely?) – the more manual user action on a spreadsheet the more likelihood of risk.

In this OMG learn how to reduce that down to a simple touch.

Do you know any more to add to this series?

Hiran de Silva

View all posts

2 comments

Your email address will not be published. Required fields are marked *