Intro from Rob: In the car today I realized two things. One, I have been a terrible host. Multiple guest posts got stuck in the pipeline while I focused on PowerPivotPro School. Much effort went into these articles and I apologize to all of you who have been waiting patiently to see them shared. My original post idea for today will wait.
Today we start with Anne Walsh, who you will see employs a very different “lens” than the one typically used by yours truly. Except for her movie and pop culture tie-ins – those are quite consistent!
The 2nd realization was triggered by Anne’s references to Ratatouille. Remember the line “anyone can cook?” And how that rankled the elitist chefs? There’s a strong parallel there in my belief that “anyone can business intelligence.” As long as they savvy Excel, that is 🙂 Onwards…
What Excel users could learn from Masterchef or why preparation is so important!
By Anne Walsh
Colette: [Linguini is making a mess at the kitchen] What is this? Keep – your station – clear! When the meal rush comes, what will happen? Messy stations slow things down. Food doesn’t go, orders pile up, disaster! I’ll make this easier to remember: keep your station clear, or I WILL KILL YOU! (Taken from Ratatouille)
Before we begin, let me point out that while I respect Colette’s fierce commitment in Ratatouille, I am not espousing her approach to her co-workers! But if you have ever had to start doing something that required a well organised list only to find that it was actually all over the place – you may have some empathy for her..
However I always notice that a key part is glossed over. The chefs work in an environment with great well prepared ingredients and top notch equipment. You don’t see them running out because someone forgot the vanilla essence…
Alas in the Excel world, we start well before that. We usually don’t have any minions to get our saucepans, knives and source perfectly shaped tomatoes. Usually we have to assemble our lists (often with blemishes and long gone sell by dates) so this is where I want to start. We have to go to the market to get the ingredients, and prep them.
So that’s the focus of this post: prepping your lists (aka tables) for input into PowerPivot. I rather suspect many of the readers here will know this stuff already but my experience as an Excel trainer is that making this stuff implicit really helps users. So apologies in advance if what much of I write here seems – to use a British colloquialism “bleedin’ obvious” – it’s an occupational hazard of being a trainer. On being asked for directions I have been known to tell, point, draw a diagram (and occasionally) ask them to repeat the instructions back to me…..
Preparing your list from scratch:
Quite often the list you need to use is already set up and all you need to do is make the headings bold.(So that Excel knows NOT to sort them) In other cases, you will have to prepare the list from the very beginning. The temptation is to get as much as possible on one line. This is the WRONG way. Yes, it may look as though it’s easier and you are saving time but it will catch up with you later..
Instead give every transaction its own line – that means if you are recording an invoice – it’s one line per item on the invoice not just one line for the invoice so you will have the invoice line repeated many times. This may seem redundant and a lot of extra work but when it comes to preparing your pivot list – ah, then it makes sense. The right way is..
Cupboard staples and Vlookups – or why you need to have certain formulas in your repertoire
I have come across people who have to combine information from different lists who have started off by doing it manually (sort of like grinding your own wheat – actually no, more like tilling their own soil) and who on being shown a vlookup have begun to sob – just a little.
It’s as if they have realised you can you know – buy the flour, in a packet….However, if you are reading this blog, I’m pretty sure you already a vlookup ninja, but if you convert your table array into a table (as per the outline below) – it’s a handy little tweak. But of course now with the use of Power Pivot, I am starting to see it as a wooden spoon in comparison to an industrial food mixer!
Think of vlookups as painstakingly beating your sugar and butter by hand…or you could just use the blender i.e. relationships in Power Pivot. I know what I’d prefer..
Getting it all together: No gaps, no gaps , no gaps – did I mention no gaps?
Ever notice in MasterChef how the participants always have all the ingredients assembled together i.e. they don’t run down to the end of the building to get a saucepan. Well, that’s also true for Excel. Although many lists come already prepared, it’s important that you remove blank rows and columns. One useful technique (although this has the major limitation of moving your data where you use it with blank cells) is to (a) highlight your lists – I usually suggest highlighting the headers and then using Ctrl + Shift + Arrow Down to highlight the block down to the first gap. Rinse and repeat. Then use F5 – Special – Blanks to quickly identify blank rows and columns
and then use Ctrl and – (minus) to delete. Or if you want to be more cautious – colour them in first.
Getting pork when you have ordered lamb
So you think you will have it easy – this time all you have to do is use something someone has prepared earlier e.g. getting a list from another programme. However you find that it ain’t what you thought…..Your numbers are being coy and don’t seem to understand that when you put them together they are supposed to co-operate and give you a number – not shriek “Value!”. Often a clear indicator of this is the green triangle in the cell corner. This often indicates that what looks like a date or a number is actually seen by Excel as text so therefore your formulas will not work correctly – hence the demented “Value!!!!!” shrieks (extra exclamation marks optional…)
In this case you are going to have to just convert them to the appropriate data type. .Of course you could individually press Enter on each cell (and I’ve had users tell me they have done that…)
However, here’s the solution: straightforward and surprisingly effective. The only issue is that you will have to do one column at a time..(Data tab, choose Text to Columns, follow the defaults and on the third step (3 of 3) choose General).
Of course another sneaky way to sort this out is to try the Penny of the Big Bang Theory approach and turn off the error notifications (File: Options:Formulas: Error checking)
Trim – it’s not just for vegetables you know, it’s great for relationships too
If you are having problems with your vlookups, one of the things to check out are trailing spaces – you don’t see them because – well they are spaces. The Trim function is excellent for that. Sort of like an automated data trimmer. Just zaps those pesky spaces. Reference the column with the text you want to trim and then use Paste Values to replace the untrimmed text with the tidy version.
This also applies with relationships in your Power Pivot as well. So if you find that your relationships are just not cookin’ the way they should…this is one place to look.
Tables – essential for Masterchefs and also the Best Thing Excel Has Brought In For A Very Long Time… OK maybe until PowerPivot, that is.
I have to say I love tables in Excel not just because they look so snazzy and it’s so much easier to cook on them….but they are also really powerful in creating dynamic ranges. But as I get more and more familiar with Power Pivots, I’m not feeling as much lurve as I used to. I must admit. But still ….as an ingredient – they are delightfully versatile. For example if you use tables when creating your validation lists and with your vlookups they grow automatically as you add to them. (Much easier than using the offset function)
Here’s da recipe: Convert your vlookup table array to a table,(Ctrl and T), give it a range name in the name box (usual caveats – no spaces etc). Then use this range name instead of your usual table array in your vlookup (or as a range name in your list validation). (You can use F3 to call up your list of range names.) However, one caveat (and I’m very grateful to Rorya in Experts Exchange for putting me onto this). Using this technique does make the range name case sensitive. However the way to fix that is a tiny bit obscure (like one of those ingredients that you have to go to a specialist shop for….) . Assuming you are using Excel 2010 – File: Options: Advanced. Go to the end and clear the box for Transition Formula Evaluation. Nope, me neither.
If you have any other suggestions for how to create great user-friendly Excel lists – it would be great to share them here. However I have to say that one of the exciting glimpses of Power Pivot for me is that I would regularly get asked in class about handling large amounts of data and how to combine files effectively. It’s a joy for me now to say “Have you got Excel 2010 or later?” Well checkout Power Pivot (and then I usually give them this website as well)…