How do I replicate Excel in Tableau?

The short answer is don’t do it. Tableau is not a spreadsheet program, so if you are trying to make it into one you will often find yourself beating your head against a wall in frustration. At the same time, Excel is not a data visualization tool even though people often try to make it into one because it’s the tool they have on hand and it can generate a wide variety of charts and graphs. Having said that, both tools can do some of the same things so all is not lost. Now for the longer answer…

Often when I hear that someone is trying to replicate an Excel spreadsheet as a data visualization in Tableau it is because someone higher up wants it to be done. They are comfortable with the Excel spreadhseet which has worked in the past and don’t want to change to something new and different. In these situations, you often have to do the best you can to make Tableau work. There is some benefit for consistency and familiarity for end users which gives some merit to doing this.

And actually, in the case of simple numeric tables with limited formatting, replicating them in Tableau is a good way to get started. In my current position we have been using pivot tables in Excel for several years. We knew the data being shown was accurate (or at least it was as accurate as we could get). So, when we first began the migration to Tableau recreating the tables was a good starting point for us. If we could create a table that “looked” the same as the pivot table and the values matched then we knew things were working correctly in terms of the structure and calculations. From there we could begin to further explore options to visualize the data. Tableau made it easy to switch between different types of charts and graphs. We could move things around to look at things in new ways to figure out the best way to show what we felt was important in the data.

When trying to replicate Excel in Tableau, start with getting the numbers to match. Sometimes this means that you need to restructure the data to get it into a format better suited for Tableau. The data interpreter in Tableau 9 and above can help with this. You need to have one row for each combination of dimensions and separate columns for each of your measures. You will likely need to play around with calculated fields in Tableau to work out issues where you’ve been using IF statements in Excel, but it can be done. Since how to do that will vary from case to case, I’m not going to go into it here.

Once you have the numbers matching, you can then begin to work on formatting. This is often the trickier part because of ways that conditional formatting of cells can be done in Excel. In most examples I’ve seen, the conditional formatting is used to highlight or color code certain values that are significantly different from others in the table.

Depending on the type of formatting needed, using highlight tables will allow you to shade the “cells” for different values. By editing the colors (and using advanced options) you can often get close to what you might be looking to do.

Another tool that can come in handy are table calculations. One way to get started with table calculation is to use the built in quick table calculations that Tableau provides (e.g. Difference). Using the “Edit Table Calculations” get it as close to what you are after as you can by adjusting the direction (across or down), relative to (first, last, previous), etc. Now, drag the pill with the calculation from the Marks card to measures. It will create a new calculated field (e.g. Calculation1) which you can open to see the formula being used. Common functions you will see in the calculation are: ZN() which replaces null values with zeros; LOOKUP() which looks up a value or expression based on an offset; FIRST(), LAST(), and PREVIOUS() which are easy ways to reference other values in the table.

Other functions worth knowing about are WINDOW_ calculations. For example WINDOW_SUM can be used to sum across elements that are displayed which might not match how they appear in the underlying data. One example I’ve seen with this is counting the number of distinct cities. If you create a table with state and city you will notice that some city names show up in multiple states. If you do a total of count distinct, you get the number of unique city names regardless of state. If you do window_sum on the count distinct then it takes state into account.

Level of Detail (LOD) calculations can also be helpful to create values that might not be readily available in the table or underlying data. For example, in working with retention data I create a variable that represents the first semester a student shows up in the database {fixed [student id] : min([semester])}. I can then use this variable to calculate the number of semesters, years, etc. that the student has been at the university. It can also help in assigning the student to a cohort. There are several other things you can do with LOD expressions, so I definitely recommend learning about them.

Once you have a calculation created you can drag them to color (or size) in order to customize the formatting in the table. I know this doesn’t go into all the specifics you might need to accomplish particular tasks, but hopefully it will give you some ideas on how to move forward with what you need to do.