By: Eric Parker
Eric Parker lives in Seattle and has been teaching Tableau and Alteryx for 5 years. He's helped thousands of students solve their most pressing problems. If you have a question, feel free to reach out to him directly via email.
One of the frustrating data formats that can make data unusable in Tableau is report-formatted spreadsheet that have headers with following blanks.
Here’s an example:
In order for this data to be usable in Tableau, the following blanks must be filled in. That isn’t something that can currently be done in Tableau Desktop or Tableau Prep. There are other tools like Alteryx which have a Multi-Row Formula Tool to solve this problem, but it can also be solved with a simple calculation in Excel.
At scale, this is time consuming to fix manually. With a little creativity, we can solve this problem with a calculation in Excel.
We can add a new column and write the following calculation. It states that if the cell of data in the far left column is null, the value from the cell in the prior row should be inserted, otherwise the non-null value should be used.
When the calculation is dragged all the way down the worksheet it looks like this:
The last step is to copy and paste the values in column B over the calculation and then delete column A.
Copying and pasting values only:
The final output:
