For weathered consultants, Excel is not new. In fact, we teethed our brain on it many years ago as analysts, consultants, senior consultants. In fact, it’s possible – just possible – that we have gotten pretty slow at it in our, uh um, old age. Please find a list of Excel tools and functions that we all should know. Hat top ExcelJet.
Clean and Trim: Get rid of Spaces and
Hard Returns
Start with good data. It’s worth slowing down to clean up source data, so it won’t give you trouble later.
Fill in missing data
You see this a lot when you export data from a client’s system, and it is in report format. . . Excel cannot use this. So, yes, you can rightcorner drag all the information, but this is way simpler, more elegant and a lifesaver.
Pivot tables
You’d be surprised how far you can get in life with pivot tables. Super basic, super necessary. Consulting is entirely about putting things into buckets, and well, that’s what pivot tables are.
Vlookup – Lifesaver
If I could only use 1 function, it might be vLookup. Critical way to match up different data sets.
Concatenate and Text
This is more of a “nice-to-have” formatting approach. Not necessary, but a great want to tidy up a model you are making for a manager’s review. When it is going to the client, small details matter.
Nested Formulas – A few examples of geeking out
Complex formulas are just (simple formulas (that have simple formulas)) inside them. It takes practice, and sometimes it can get a little out-of-hand. Note: don’t make the nested formulas so complex no one understands them, or it takes 2-3 rows in the formula bar to show it.
Great reference: 30 Most common Excel Shortcuts
Once again, great shout-out to ExcelJet. This is their work, not mine. It’s excellent.
Finally, emailed with the founder of this website (David), and strongly encouraged you to take a look at this guide to 500 formulas here.
Great tutorials!
Thanks for reading.
Great summary. To add from years of fighting the good fight…build to the level of the client and/or analysis need. Deep nesting is great, clean, and shows off your promotion readiness. Consider if your client SME wants to see the building blocks, and adjust to “de-nest” and show the work in progressive columns/sheets.
Small edit…”Strat” and “Start”
Many thanks for the words of wisdom and the proof (too).