M2: Cash to Wealth
If you do it right first, you'll do it once
If by now you've fallen in love with Excel and all you want to do is mess about with spreadsheets all day, don't bother with this section. Enjoy rebuilding your entire analysis when your boss asks you to change one number. Bask in the glory that is hunting for the source of that magic number that your client is questioning, when you're already four referenced cells deep. Snuggle deeper into your office chair as you watch the clock tick over to 9PM for the third night in a row as your colleague asks you to add something they didn't ask for in the first place but now is mission critical.
​
Not keen? Me neither.​ It takes a special kind of masochist to wilfully redo the same thing over and over when a few changes at the start would have avoided most, if not all, of that mess.
​
From the previous module, you have at least one set of data that is reasonably clean and you're now going to do something with it. Add, subtract, split by category, model... whatever it may be, you don't want it becoming useless if something:
-
changes
-
is added
-
is removed
​
The following principles should - when used consistently - deal with the vast majority of such drama.
First, think!
Creating something that isn't usable for its intended purpose and/or users is a frustrating time suck of an exercise. Before you start, consider: ​
​
-
Who's going to use this spreadsheet under what conditions?
-
Will someone else only touch it if you go under a bus?
-
Will you be interrupted on holiday or when you're feeling terrible if the process isn't done?
-
What knock on effects does this have on the rest of the organisation?
-
How dependent do you want others on you for this particular job? ​
​​
How you answer these questions will create a natural priority to the construction of the spreadsheet and associated documents, which may override the other principles shown in this module. The point of this is to think, not blindly follow a checklist, no matter how well thought out or written. ​​
​
As they say, no plan survives contact with the enemy [reality]. This 'plan' of a module won't help in every scenario and it's your job to think about whether it is helpful for you and what you're doing.
1) Every piece of static data has one place
As much as possible, keep your static* input data (data that isn't calculated or summoned by formulas) in one place. Ideally this is a separate tab, or a separate colour if the data must be split across multiple tabs.
​
Any reference in a formula to that data then should refer back to the same cell. Think of it as creating the shortest possible path to answer the question your boss inevitably asks (awkwardly, in front of everyone in the meeting, in the you are an idiot tone) 'where did this number come from?'. If you have to click through a digital matryoshka doll, you've lost.
A simple example is the cost of milk that your spreadsheet pulls from a file your vendor sends through. That lives on the 'Inputs' tab in cell C2.
If you're then using that to figure out the cost of your tea breaks, you'll reference C2. If you need to pull that number into a report for management, you reference C2. What you do NOT do is to, on say the 'Breakfast' tab, reference the milk cost from the 'ManagementReport' tab. This makes bug fixing, edits and updates unnecessarily complicated.
​
If you have a particularly complex set of data that multiple people update, you can also split the inputs by person - so Jack has their own sheet, Ling has theirs and the data that comes in from your data vendor is put into another one.
​
*static means you do not calculate it in your analysis, it comes from another source as is. This could be a price you receive from an external data vendor, a cost you get from the accounting system, a forecast you receive from the sales team, or something else relevant to your work.
Editor's note: this video doesn't have an associated spreadsheet
2) No in cell magic numbers
With all Excel work there are numbers that appear to be unchanged... until they change. Like in a bakery, you might set the price of your cherry pie at £9.10 and when you calculate your revenue you enter that number into each cell that needs to reference it.
​
Monday's revenue from cherry pies = 9.1*B2 (where B2 is the pies sold)
Tuesday's revenue from cherry pies = 9.1*B3 (where B3 is the pies sold)
​
The obvious weakness here is if the price changes - or even if you want to see what the effect a price change could have. The better basic structure is to put 9.1 in its own cell, say A1, call it Cherry Pie price or similar, then simply reference that cell in your formulas.
​
Monday's revenue from cherry pies = A1*B2
Tuesday's revenue from cherry pies = A1*B3
​
You can also name a cell (because a cell is also a range; the smallest range possible) and then reference the name in the formula but I don't recommend that in most cases as it's just an extra step for not enough benefit, particularly if those around you aren't familiar with named ranges and can't amend them, or figure out what they reference.
3) Mapping tables
Unless you work in unicorn land (and if you do, spill because we all want your job), you will need to add data to your clean dataset from a source that you do not control. If you're looking for one number, not a big deal to copy/paste. If you need that number for each of your clients, copy/paste isn't a good use of time. The easiest way to pull data is by using a matching formula like VLOOKUP.
​
But.
​
If you do not control the source of the data, you'll be stuck looking up against whatever identifiers they use for your client. If that exactly matches the name you use, great. If they use a random number that is their own internal system identifier... you're screwed.
​
Unless!
You create a mapping table. Think of this as a place to store all of the AKAs for your client (or your ingredient or whatever it is you have a list of), just as you may be known as Sam McPherson, Sam, McPherson, S McPherson, s.mcpherson@sms.com. As a reminder, Excel won't know that Sam McPherson is the same as S McPherson unless it's explicitly defined to be, which is why we build mapping tables.
​
Once you have a mapping table, when you need client revenue from system A, assigned costs from system B, total client potential revenue from an external vendor, and average time to close a deal from another external vendor in order to put together your sales projections for your 300 prospects, it's a few VLOOKUPs and will update immediately as soon as you paste in new data.
If you have more than 5 items, mapping tables are worth it.
​
Devil is in the details of course - normally putting together reference tables is one of the messiest tasks. It's helped with wildcard lookups:
Click here to download the workbook from the video.
Grey cells contain formulas
Thoughts on 15 years of data cleaning
​
Wildcard lookups to match data is as much an art as a science. I generally follow this order (remember, think first. If your data won't fit neatly in this framework, do something different!).
Note: A1 is the cell with the data we are trying to match in whichever formula- VLOOKUP, Index(match()), XLOOKUP.
​
-
A1 - it's the most likely to get the right answer
-
*A1* - more likely to have AN answer, still very high chance it's the right one
-
*left(A1,13)* - likely to have AN answer, unlikely to be a bad match. Usually gets rid of issues matching with endings that differ like ltd, limited, co, company, association, assoc. Takes the first 13 characters from the cell and matches on them, ignoring anything at the end. 13 is the number of characters I use, but there's no reason to it beyond what has tended to work with my data sets
-
*left(A1, find(" ", A1))* - searches the first word (aka anything before the first space) of A1 anywhere in the search range. Helpful if the first word is unusual and the rest isn't relevant. Unhelpful if the first word is 'the' or similar.
​
To be honest once you get through those 4 wildcard searches and their variants (you could use the right part of the cell if that's more relevant than the left for example, or use the 2nd word between wildcards), any 'result' will likely be rubbish. At some point brute force will be faster than trying new formulas to pick off that one extra mismatch. If I'm only hitting one or two matches in a different style of search that tells me it's time to copy/paste.
​
You can try to alphabetise or otherwise logically order your search range if that will make a difference as VLOOKUP searches top down (and therefore returns the first cell it determines is a match). This also means that your results may vary if someone sorts your data without you knowing, so use with caution. Mapping tables become static data anyway (so you shouldn't have formulas in them anymore) but it's good to know especially if you are working collaboratively on a single file with colleagues who are trying to help you find matches.
4) Template your process
If you're using Excel to deal with a repeating process, create a template so that each (week, month, quarter) you do the same actions in the same workbook. I recommend working from left to right so your actions follow along the tab order (if you have multiple tabs).
Here I've amended the spreadsheet from the previous example to demonstrate what it could look like as a templated process.
Click here to download
the example workbook
Thoughts on 15 years of sharing workbooks and processes with colleagues
​
​If you do nothing else,
Save a backup copy
of the template in some obscure directory on your server - and if possible on your own harddrive - so that when the inevitable happens and someone messes up the template while you're on holiday, it's a quick fix instead of a day's faff with no overtime pay.
​
The other basics of a good templated process are:
-
Separation of formulas vs inputs (where possible) - no one wants to reconstruct formulas overwritten by an overenthusiastic copy/paste job
-
Instruction pages - have one. Better yet, use Scribe or similar to record yourself doing the process and turn it into a procedure manual. Video is OK, but doesn't have CTRL F so...
-
Specify the deadline, inputs, the outputs (and where they go!), as well as basic error checking steps. If it makes sense, include contact details of people to speak to if something goes wrong.​
-
-
Order your tabs logically - if you have to paste something in tab A, then adjust/check something in tab B, before sorting on tab K, put them left to right A, B, K. There is nothing more frustrating than searching for a tab in 30 tabs that's buried on the far right hand side!
-
If you are naming ranges, tabs, or anything else, include information that helps distinguish what they are, and ideally start with a lower case letter, then upper case for following words.
-
unicornCol - right format but useless unless you actually work with unicorns​
-
salestargets2024 - helpful, but hard to read
-
salesTargets2024 - 3rd time's the charm
-
5) Differentiate by colour, save formulas
and write notes
​It is easy to - particularly in large data sets - accidentally overwrite a column of formulas that you assumed you needed to paste data into. I recommend differentiating by colour, with grey columns as formulas (less tempting to mess with) and green as columns for data pasting. If your spreadsheets regularly go to people who are colourblind, this may not be appropriate. Think - and ask - before you create something unusable.
​
Use the comment and notes box (depending on your Excel version) to save your formulas in the headers of the columns (these are rarely accidentally overwritten) so that in the face of the inevitable, you can quickly restore it.
​
Ideally once you have your template settled - by settled I mean you've been able to successfully run it the same way on at least 3 consecutive occasions, add a 'Read Me' tab as the left most tab and outline the process as you've constructed it. This makes holiday handovers easy, highlights any assumptions about data and where's it's sourced from, and is handy for those quarterly processes that you mostly remember doing but forget the nuances.
6) Ask!
Data issues
Have you received data from a vendor or from internal company systems that has no useful identifier? Ask the person who sent it whether there's an additional field they can pull. If there are universally (or at least industry) agreed identifiers, like company number, ISBN for books, ISIN for securities, ask if they can be included.
​
Usually these are available somewhere in a database, even if they aren't set to appear by default on the final report, and it won't always occur to the person producing the report to include them unless specifically asked.
​
Formula issues
See something that doesn't look right? Ask around. If you're right and it's an error, you've just saved yourself or your colleagues from embarrassment. If it's right, you now know exactly what it does. Win win!
​
People issues
With some exceptions*, work related questions in a professional setting should be answered comprehensively without mockery. If you run into someone who mocks you for asking about a number or other data, that's a red flag that they don't know what they're doing. It is possible that they're just a jerk (in which case ideally find another job as life's too short to work for jerks), but it is more likely they have no idea and can't deal with not knowing in public. Find someone useful to ask, or assume that number is garbage and figure out another way to calculate it yourself to check it.
​
​*if you're not sure about your question, askamanager.com or reddit are good places to get feedback
Learned something? Leave a tip!
Help keep this course free by leaving a tip if you found the content on this page helpful, timesaving, nice to know, funny, or otherwise feel it is worth keeping online. The average contributor pitches in £5 - £10 per module, and if you need an invoice or receipt to submit to your company for continuing professional development drop me a line and I'll sort it out.
Next chapter coming soon!
Keeping & Growing (Wealth)