Data Visualization: Move From Spreadsheets to Dashboards With These Four Tips

According to Harvard Business Review, the ability to create smart data visualizations to enhance data analytics has changed from a “nice-to-have skill” to a “must-have skill for all managers.” As a result, many businesses are undertaking the effort to replace their traditional reporting environments with data visualization tools like Power BI or Tableau. Like any major initiative, this process can be complex from both a technical and people perspective. To ease some of those challenges, this blog post will cover four easy tips to put your company in the driver’s seat of a move to better visual analysis.

Data visualization expert Ryan Sleeper points out that “visualizing data reduces the time to insight and improves the accuracy of insights when compared to data in [tabular] form,” but when businesses first try to use a data visualization tool, end users often just want to recreate tabular or spreadsheet views of their data. Instead of showing all the details to begin with, a data visualization tool works best when you can highlight key takeaways, then provide your users a flexible workflow to drill into the detail as needed. To illustrate how to accomplish this, we will walk through a real-world example of turning a traditional spreadsheet view of sales data into an insightful dashboard of data visualizations.

Data Visualization Example & Four Tips

Our example will use the Sample – Superstore dataset that comes with Tableau, which has order information with sales values broken down by products, geography, and month. Our goal is to understand which products are over/underperforming their sales Year over Year (YoY).  A spreadsheet version of this data can be seen below

A common first step to analyzing this data in a spreadsheet tool would be to utilize a pivot table. This can be recreated in a tool like Tableau to show the largest YoY sales variance for each region and product category

At this point, a logical next step might be to dive deeper into the data to understand what is causing the YoY sales variance for a particular region and category. In a spreadsheet, you could double-click on a YoY sales variance to get all the records that make up a particular variance, and then repeat the pivot table process. This strategy would meet our needs but is time consuming and disjointed with the flow of analysis. Instead, you can use the power of a data visualization tool to make this process seamless and reduce the time to insight, which highlights our first technical tip:

Tip 1: Split out different dimensions of analysis into separate visualizations, then use filtering to dive deeper into your data.

A very simple implementation of this tip is shown below, where we make a visualization to look at YoY sales variance by sub-category (1), state (2), and month (3) in addition to our standard table from above (4):

Source: Credera

With one simple click on the largest variance in the table (1), the main driver of the negative variance becomes apparent: a huge drop-off in copier sales (2) in Indiana (3) in October (4):

Source: Credera

Tip 2: Provide additional context in tooltips to showcase the next level of insights.

By default, the tooltips that pop-up when you hover over an item of interest in a dashboard will only show the basic information that is already present on the visualization, as seen below:

Source: Credera

At a minimum, it’s helpful to put in the individual components so the user can understand how the calculation was done. In this example, we’ve included the 2018 and 2019 sales as context for the YoY sales variance for copiers:

When adding context like this, it’s also important to consider deeper levels of analysis that might provide answers to common follow-up questions. For example, now that we’ve verified that copier sales truly did drop off YoY, we can provide additional metrics in the tooltip that showcase what made up the drop-off. In this case we actually had more customers place orders for copiers (1), but the average sales/order took a huge hit (2): (Note that this example used an additional visualization within the tooltip, but this same info could be presented in text format if your data visualization tool doesn’t have that feature.

Source: Credera

As this example demonstrates, understanding how your organization utilizes your data allows developers to design visualizations that truly reduce time to insight for your end users. To provide the same level of insight in a spreadsheet would require creating multiple pivot tables, and the insights likely wouldn’t be as obvious buried in a sea of numbers on multiple tabs.

Tip 3: When necessary, provide the ability to get to a table of underlying details.

Sometimes users will still want or need to see the underlying details in a tabular format, and there’s still a way to accomplish that without abandoning the tips we’ve discussed thus far. Continuing with our example above, a user might still need to see the order details for copiers to check against variances or errors. A table of details about those orders is a logical solution in this situation, but we don’t necessarily want to clutter our dashboard with a giant table of underlying details. Instead, you can create a separate dashboard that is accessible through your tooltip or on a different tab that is automatically filtered based on selections in your summarized dashboard.

The example below has an added link in the tooltip (1) we’ve already created that allows the user to “See Order Details” which takes them to a sheet filtered to just the relevant orders (2):

Tip 4: Increase transparency into data shown on the visualization.

Getting your organization to accept and utilize the new information available to them can often be another huge hurdle to adopting a data visualization. Understanding how users engage with the dashboard and how they perceive its worth can impact the success of a transition from a spreadsheet-based tool to a visualization tool.

The “trust gap,” according to TDWI, is having a lack of “trust in the data itself.” While presenting data visually may be more beneficial in the long run, users may be resistant initially because the presentation is different than what they are accustomed to seeing. Addressing the trust gap is a key factor in the success of any data related initiative and becomes especially noticeable when transitioning from a spreadsheet-based tool to a visualization tool. If your organization is encountering one of these pitfalls, you might find yourself asking these questions:

• Is the data validated?

• How can I be sure the calculations are correct?

One way to address the trust gap is by increasing transparency within the organization. Providing transparency could be anything from providing definitions for calculations in a data dictionary to adding details to a reference document that will help to provide information visibility. Visibility can also help to address any other change resistance that may be encountered.

Users who are resistant to change may be unwilling to learn a new way of performing a task. In the transition from spreadsheet-based tools to visualization tools, users may not fully realize the benefits of using a specific visualization because of past practice or may seek out an alternate way to perform a given task that is less than optimal for what they are looking to achieve. An additional way to address this is to employ organizational change management techniques as a means to manage through the change resistance and fully realize the power of changing behaviors and technologies.

Start Your Data Visualization Journey

As you embark on your transition, remember the technical and people perspectives so you successfully avoid any pitfalls you may encounter. If you’d like to download and play around with the sample workbook above, you can find it here. If you’re curious about learning more about how your organization can make this transition, reach out to findoutmore@credera.com.

• Analytics
• Data
• Data Visualization