There are no fancy Excel stunts here. These are 6 simple pointers that escape the Excel tutorials. Like anything that’s done well, there are personal habits that quietly develop to keep you focused or avoid traps. I wasn’t aware of my own habits until I saw a student’s spreadsheet and realized that she’d been watching me and adopted entry #1 in this list.
Let’s look at 6 under-the-radar habits that aren’t as sexy as array formulas but they make the sexiness possible.
1: USE COLOR CODED TABS FOR GUIDANCE
There are accidents when we work with data. One precaution we can take is develop habits that keep us out of the fire.
In the image below, the green “Working Data” tab started as a copy of the red “Original” tab. This is a habit I’ve had for many years. If something in the Working Data is accidentally deleted, it’s easy to go back to the Original and start all over again. Occasionally, something just seems wrong and the Original data can be used to verify that “no, nothing is wrong.”
2: ALWAYS KNOW WHERE YOU’RE GOING AND WHAT THE FINISH LINE LOOKS LIKE
When we’re looking for duplicates in a dataset or, building an app or, making complex calculations or anything else we do with data, the purpose has to remain clear. That may sound obvious but hold on …
The world of data is a murky world. It’d be ideal to have access to all the data in the universe immediately available. But we don’t have such access. We also don’t have infinite time, money, skill, tools, etc. So, our data management needs have to match the realities of our limitations.
A “finish line” looks very different when you have 4 days vs. 10 days to work. Consider:
- 10-day finish line: clear all duplicate customer records and segment the list by profession and by major metro areas
But we don’t have 10 days. So, what’s going to be good enough in 4 days?
- 4-day finish line: clear the duplicates in the top 10 metro areas
Without this clarity of what “finished” looks like, a de-dupe task can 1. get mired in unrelated minutiae; or 2. get completely lost at an undefined step in an undefined complicated task.
When I feel like I’m going in circles I do stop and force myself to articulate the overall goal, why it matters and what pieces still need to be put in place.
♥♥
ADDENDUM: “finished” can shift on you. We have to step back and re-assess once in a while. That’s covered in “Close the Frikken Spreadsheet.”
3: WHEN YOU RECEIVE A NEW DATASET THROW IT INTO A PIVOT TABLE
Knowing where you’re going requires knowing where you’re starting and what you’re starting with. I detail that as Ann Emery’s guest blogger at Pivot Tables: Your Tool for Exposing Miscreant Data. Pivot tables are known for their ease of presenting data multiples way. However, there isn’t enough said about the use of pivot tables before you start doing any work.
Have a read and check out Ann’s site. Ann does a wonderful job of mixing data management concepts and strategies with nitty-gritty how-to tutorials.
4: BUILD COMPLEX FORMULAS IN SMALL STEPS
Task: randomize the duties of 5 people and do it in a single formula using INDEX and RANDBETWEEN
- Get INDEX working first
- =INDEX($J$2:$J$6,2)
- and then nest the RANDBETWEEN inside
- =INDEX($J$2:$J$6,RANDBETWEEN(1,5))
From here, if this formula needs to be further nested, you know that this piece works.
5: DON’T HIDE ROW 1 OR COLUMN A
The un-hide method is not like un-hiding every other row or column. It’s a frikken pain compared to unhiding other rows and columns. (I’ll leave you to Google how to unhide those things.)
6: THINK “DATA MANAGEMENT” BECAUSE EXCEL IS ONLY A TOOL
I still laugh about the time I built a masterpiece spreadsheet. The client responded, “it looks smart but I kinda hate it.” No admiration of my excellence? C’mon!
That kick in the pants was a signal that I’d fallen in love with a tool and my own skill. It was like going to plant flowers, falling love with one’s shoveling prowess, then ending up digging a hole to the center of the Earth.
Howard A. Tullman wrote a great article for Inc.com, “Why Good is Good Enough.” Tullman describes how clients need solutions and need to get going. Training, manuals, instructions, etc. can be more than overkill, they can just plain kill a solution. It’s just another reminder that the goal was to pant flowers; it wasn’t to show off.
Ann Emery of Emery Evaluation, once commented that the data management tool that we really need to develop is our brain. That’s really what this blogpost is about. Mindfulness. Strategy. Good habits. Personal methods for working effectively. These small things aren’t taught and they’re often what’s missing when a person is handling data in the most sloppy and painful manner. They’re trying to mindlessly force something to happen.
Data Management is mental. Keep developing that tool between your ears and together we’ll rid this world of the misery associated with crap data.
driving photo credit: digidreamgrafix
brain photo credit: samuiblue
Trackbacks/Pingbacks