Excel. The multi-tool in every BA’s tool-belt. Or if it’s not, it should be.
From capturing user stories, calculating cost basis, data analytics, metrics tracking, management dashboards, and it’s default use as the table-creation-tool du-jour; it does it all. It may not be the BEST tool for any of those but it’s the tool you can count on almost everyone in an office environment having and being at least somewhat familiar with.
It’s also the tool that most people I know turn to when you get one or more data dumps of some sort and you need to try and make sense of the information, or just make it more accessible for further analysis.
In this post I am going to discuss a specific problem I encountered with a data extract in the form of an Excel file. That problem was that:
Out of about 25 data elements in the file, only about 10 were in distinct columns
The rest of the data elements were concatenated together into one string in a single column
There were nearly a thousand rows of data and I would be getting a new file every month, meaning a manual solution was not desirable
The discreet data values in the string were not always consistently present (e.g. a value might be present in one row but not another)
The data values in the strings were not always in the same order
So in this post I am going to go through the logic and eventual Excel functions that were created to solve this problem. Hopefully you find it useful.
If you are a business analyst, you probably use Excel. And if you use Excel, there is a REALLY good chance that you don’t use most of its capabilities and don’t know all of the ways to use it most efficiently. I know that is true for me. 🙂
That’s why I want to recommend you watch this video. It was private session done by Joel Spolsky to employees of Trello, Fog Creek Software, and Stack Exchange. And if you weren’t aware of it already; besides founding or co-founding all three of those companies, Joel Spolsky used to be a program manager at Microsoft back in the early to mid-1990’s and he worked a lot on Excel.
He moves quick in this video, but there are a ton of good tips and tricks for using Excel the way most BA’s do. That is, NOT for rigorous data analysis. 🙂
It’s only 54 minutes long and it might worth your time to watch.
I haven’t posted anything new to the site in a while both because I’ve been busy with other things, and because the main articles I am working on are ones that will probably take me quite a while to research and write up to my satisfaction. I have several in the works (including a few smaller ones I can turn to if necessary) so hopefully I’ll have at least one new wiki article up in August.
But in order to make sure the site gets at least a bit of new content each month, I want to share a quick software tip in the form of a couple of Excel formula’s that I wrote.
These formulas convert single cells with names in [Last Name], [First Name] order to [First Name] [Last Name] order. The first version leaves in middle initials and suffixes. The second version trims everything after the first name.