Four Better Ways to Manage Data
At the recent Springfield IMA conference, I had an interesting experience. The featured speaker was Mr. Excel. Yes, a man who makes a career of showing people how to use a 30-year-old tool that has little to show for improvement other than shinier bar charts.
While I was talking about business intelligence, the accountants in attendance kept asking me, “Who makes business intelligence,” as though BI was a specific product, not a business function. I asked them, “Who makes accounting?” But, I don’t think they got the joke…
If Humor Doesn’t Work, Tell ‘Em How to Make Their Jobs Less Awful
Needless to say, my failed attempts at self-promotion through humor did not generate many attendees. Mr. Excel’s “Advanced VLOOK UP” course drew a packed audience.
For the few people who did attend my session, I took a brief tangent to explain advanced VLOOKUP. Of course, my advanced VLOOKUP advice was, “Stop using VLOOKUP!” It’s slow and memory-dumb, and if you must use Excel, use Index(Match()) instead. This approach uses only the reference and lookup columns instead of the whole table. And, it can look up left to right and right to left. Here’s some detailed INDEX MATCH information.
Those 5 minutes of advice engaged the crowd more than my 15-minute breakdown of different data sources and how BI tools connect to them. My message was being lost. I assumed people would want to learn how to use advanced methods to make their jobs more efficient. I was wrong. People don’t want to hear that — it’ scary. No, people want to know how to make their job less awful.
Four Things in Excel You Must Stop Doing Now
Here’s a quick list of things in Excel that stink, why you should stop doing them, and what to do instead.
1. Stop making “dashboards” in Excel. Instead, use something connected to data.
Excel is not a dashboard tool. I’m 99% confident, that what you made in Excel is not a dashboard, so stop calling it that.
A dashboard is something directly connected to your company’s data, which refreshes at a speed fast enough for you to take meaningful action. Remember, dashboards are called that because they serve the same purpose as the instrument panel on your car. A dashboard wouldn’t do you any good if every time you wanted to know the speed, the speedometer had to request a speed extract from the engine and then copy and paste that data into its display.
Every proper BI tool out there lets you connect directly to a data source and refreshes your reports automatically. This means you make the report once, and it updates itself. That way, it actually behaves like a dashboard instead of just being called one.
2. Stop using calculations in pivot tables and use Spotfire or Tableau cross tabs instead.
Let’s face it, making a calculated field in a pivot table stinks. Even if you can figure out how to do it, the calculation will often break as soon as you modify the pivot table. And, modifying the table is the whole reason you’re using the pivot table in the first place! This is why you might find yourself adding extra columns to the Excel table instead of the pivot table, which becomes difficult to manage if you ever need to update the data in Excel.
Use Spotfire… When you enter Spotfire, Excel creates cross-tabular reports, which group data by some calculation. For example, I have a data set of 10 department stores in five regions, and each store has three products. I want to know the percentage of total sales represented by Product A. All I need to do is create a cross table with this calculation:
sum(Product A) / sum(Product A, B, and C)
Now, I can roll up the table by store, region or both, without worrying about my calculation breaking!
…or Tableau. You can use Tableau to take this process a step farther. Tableau developers understood you will probably reuse calculations over and over again. In Excel, if you need to modify calculations, you have to modify them manually everywhere. Not so, with Tableau. You create your calculation once, then drag and drop it into the report where you need it. If you accidentally use Product B instead of Product A as your numerator, you can fix it in seconds, before the boss finds out!
3. Stop using pie charts. Use bar charts, or tree maps instead.
Excel might as well be called a pie chart factory. I’ve seen dashboards with 20 pie charts. Please, stop! Pie charts are the least efficient way to communicate large amounts of information. You can find a great post from an author, who said they are the Nickleback of data visualizations.
In Excel, pie charts are easy, and pivot charts are hard! Not in Spotfire and Tableau. Both platforms let you point and click your way to powerful visualizations in seconds.
For showing percentages, tree maps are the best pie chart replacement, and Spotfire is the best of them. Check out this quick Spotfire primer. Bar charts are easy in both platforms. Here’s an example from Tableau, which shows how to make a bar chart in three clicks.
4. Stop thinking Excel is cheap, and BI platforms are expensive.
From a perspective of pure license costs, yes, Excel costs less than most BI platforms. And honestly, no BI platform will completely replace Excel. That said, if you do choose a BI platform, you can replace Excel with an open source spreadsheet application like LibreOffice. But, that’s another article.
However, you shouldn’t consider only the license costs. Instead, consider the time savings as well. You might spend 10 hours creating and updating a dashboard on Excel every week. If it takes only takes 5 minutes to update it on a BI tool, you save 10 hours a week. That’s a 500-hour annual savings. Even for analysts paid the paltry sum of $20 per hour, you save $10,000 a year per analyst!
When you consider that you can get five users started with BI for less than $20,000 , for a perpetual license (that is, you only pay $20,000 once, not every year), Excel stops seeming cheap. It starts looking downright expensive!
Still not convinced? Get a free 30-day Trial, from TIBCO Spotfire, a Syntelli partner, and take your data visualizations to the next level!