Excel is a great (but underrated) BI tool. Several BI vendors gave up fighting it and offer Excel platform add-ins as front-ends for their BI solutions. So, if you want
What is wheat and what is chaff? Here is a list to help you take sides:
- If you want to fit the data into the shape of real-world objects, that’s not data visualization;
- If you use more than one dimension to represent a data point, that’s not visualization;
- If your project breaks basic perception laws, that’s not data visualization;
- If there is a perceptually simpler way to represent the data, what you are doing is not visualization;
- If you use color when color is not needed, that’s not visualization;
- If you want to grab attention, that’s not visualization;
- If you reject a chart type because the audience may not be familiar with it, that’s not data visualization;
- If your audience don’t know what the point of the chart is, that’s not visualization;
- Alphabetical sort is not visualization;
- If your chart is nothing more than a glorified table, that’s not visualization;
- No variation, no data visualization;
- If a simple formula can be a better answer, there is not place for data visualization;
- If all you can do with a chart is to compare data points, that’s not visualization;
- If you have three or fewer data points, that’s not visualization;
- If you have to scroll, that’s not visualization;
- If you have to compare slides, that’s not data visualization;
Like Michelangelo said, “I saw the angel in the marble and carved until I set him free.” If we want to talk about “data visualization” we must find its essence, its form (in the platonic sense) to be able to compare them to the shadows of everyday life.
Actually, we don’t have to. We can always decide that the goals, the tools, the audiences and the processes are too dissimilar to accept a single concept of “data visualization”.
We stop loving someone or something when we feel there is nothing more to discover, when we have no more questions, when we don’t care about the answers.
Here are practical questions about data visualization (from a business user perspective) that, once answered, will surely trigger new ones. Here is a sample, with a few starting points:
1. Reason or Emotion?
This is the big one. We’ve been discussing reason vs. emotion over the last 2,500 years, and we still disagree. The right answer would be reason and emotion (easier said than done). When applied to data visualization, this translates into charts that respect the data, attracts readers attention because they are beautiful and keeps it because they are interesting and insightful. And contain no cheap emotional tricks. This filters out 99,9% of all infographics published nowadays.
2. Pies or No Pies?
No self-respecting data visualization expert likes pie charts. And there seems to be a daily contest to find the “worst pie chart ever”. And people keep making them, relentlessly… Why?
3. Are there interchangeable charts?
Everyone tells you that you should use bar charts instead of pies. Well, I think you should. Not because pie charts are bad, but because you’ll have to ask better questions. But that’s not the point. The point is that there are no interchangeable charts. Different charts tell us different stories. You cannot tell a part-of-a-whole story with a bar chart. You have to have a pie.
4. Should I use logarithmic scales?
I’m still trying to find a simple way to implement a logarithmic perspective in my eyes… Logarithmic scales are important to look at growth from a different perspective, but if you can’t find a way to tell the readers how to read the chart you should consider not to use them.
5. Should origin start at zero?
Yes, by default. If you need to improve resolution set the scale to 20% below minimum and 20% above maximum values . Don’t do it with bar charts (origin should always be set to zero).
I would consider this rule: to improve resolution you must have more than one series. In a line chart, comparing slopes is often more interesting than the slope itself.
6. What kind of data visualization skills the information worker needs?
Above all, know your business. Learn how to use a database (table structures, basic SQL). Refresh what you know about descriptive statistics. Choose a tool and be an advanced user or hire someone who is. Learn the basics on how human perception works. Understand how perception impacts design choices and the other ways around. Learn what each chart type is used for. Find your charting style. Spread this knowledge across your organization. Know your business (again).
7. What is the role of design in data visualization?
We saw that a chart is a visual representation of distances between data points. Everything else is design. The first role of design in data visualization is to improve cognition. The second role is to provide aesthetic consistency. The third role is to grab users attention.
8. How to design a dashboard?
I think we owe Tufte and Bertin a consistent approach to data visualization, at the chart level. We still need something similar for dashboard design.
9. How to sell data visualization?
Find a sponsor and make a lot of bad chart/(good chart comparisons. Compress a 100-slide presentation into 50 slides. They will get it, sooner or later.
10. Should I use animation?
You probably shouldn’t. Try small multiples first. Try animation if you have too many series, if the animation defines clear patterns and if it is consistent with the law of common fate. But remember: they are no interchangeable charts.
Every business is bound to produce reports periodically on a monthly basis or weekly. Generating reports manually and presenting with pixel perfect representation is no more a tedious task.
To start generating reports, the first and foremost thing you require is a formula set and a grid for representation. To represent a set of data on the basis of month, the only thing you will be in need of will be an Excel Range to determine the set of months available in a year and also the current month of a year. When you are ready to find the current month, then rest of your tasks becomes easy and confined only to segregate the data on a monthly basis.
Now the one thing you will have to do is to design a script and frame the query appropriately to extract the necessary data from different forms of data sources. All data has to be represented in different tabs with appropriate formatting done. One significant feature of higher versions of Excel Reporting Tool is the Excel driven slicers which will keep track of months’ names. This in turn will allow the users to pick the month they are in need of to represent the data. This reduces the time and effort required to code them or drive them manually.
In this way, data can be represented on monthly basis with ease in report rendered.
Many people find it very difficult to represent the time in reports. Sometimes the date will be displayed in some mess of numbers which will look like Greek and Latin to business end users. To avoid such scenarios, the best way is to use formatting. Setting formats to the data during execution will render the report in required styles. Since Excel also allows you to import style sheets and formatted templates, it will make your work much easier for generating reports.