Tips & Tricks for Consolidating Multiple Worksheets, replacing multiple copies of same datasets to one In Tableau

Courtesy: Tableau.com

Hello Everyone,

Here is my other post on Tableau.

While working on my Sepsis project with my team and the whole batch we encountered various problems while consolidating multiple worksheets in a single workbook and with just a single dataset. During the process, I have faced many challenges from slow Tableau workbooks, taking more time to open the charts and also while consolidating creating errors and values were changing. So, I did my research and overcame those challenges.

I realized that when there are multiple datasets copied, it slows down the tableau performance so switching to one single dataset is a more feasible option. I thought to share with you, how I was able to solve those challenges and how to consolidate multiple worksheets and replace multiple datasets with one in very less time without creating charts from scratch. (Note: These steps I have mentioned are helpful if you and your team are working with the same dataset csv file)

Similar to my case, if you are also working with a big team with multiple charts and worksheets that you need to consolidate in a single workbook with just one dataset then you can follow the steps.

1.     First, you need to have a Tableau Desktop version installed on your laptop. You can start 14-day free trial using your tableau public sign in credentials. You can uninstall the current version and reinstall the older version and restart the free trial. If you are a student, you will get a 1-year free trial activation code if you submit your enrollment letter with Tableau.

2.     Before consolidating, make sure all your calculated fields are in a single folder. Even in Tableau public, you can follow these steps to create a folder. It will keep your worksheets more organized and if any issue after consolidation, you can locate and fix it in no time.

Here are steps on how to create a folder for calculated fields. (Note: If you know how to create folders and group all calculated fields together, skip these steps and check step 5 or 8 for consolidation process)\

- First create a calculated field. In my case, I will be creating a folder for the sunburst chart and my teammate has already created the field WBC_level.

So, just select the calculated field- right-click- Group by selecting the ‘Folder’ option. It will activate the Folder option which we will use to create the Folder.

-       Again select the same calculated field Right click- Folder- Create Folder and Name it, in my case, I will be using chart type so I named as ‘Sunburst1’.

-       The calculated field automatically groped under the ‘Sunburst1’ Folder.

-       Similarly, if you have many calculated fields, just CTRL+Select all at once, right-click – Folder- Add To – Select the folder you want to group calculated fields.

So, all your calculated fields will be under a single Folder. Save the workbook.

NOTE: Below mentioned steps are helpful when you are using simple to advance charts that are not linked with any other Excel sheets (apart from the dataset) for creating paths (eg, Radial Bar chart, Dendogram chart, or Shankey chart). So, in that case, keep the advanced charts dataset and replace all other datasets with a single dataset as mentioned in the below steps.

3.     Open a new workbook or your (destination) workbook where you will be consolidating all charts, also open the charts/ workbook that you want to copy to the Tableau desktop.

So, I have two workbooks with another team2 chart (workbook Team2) and I need to consolidate my 3 charts (Workbook T4) with them using a single dataset only. So I have opened both workbooks in Tableau Desktop. Now I will ctrl+select all charts that I want to copy in the Team2 workbook, right click, and copy it. 

4.     Go to new workbook Team2, open new worksheet (sheet1) right click on sheet name, paste it. So, all selected charts will be pasted in the Team2 workbook.

5.     Before replacing datasets, let's create a new (Sunburst2) folder in the new dataset (Dataset 1) to keep it organized and copy all calculated fields from the (Sunbusrt1) folder to the newly created folder. 

Even if you copy all charts, all related fields will get copied however, if any calculated field has any issue, it will show an error and it will take more time to locate it. Follow the same procedure to create a folder. 

Click on any field – right-click- Group by Folder- Select Folder. 

Again, select the same field- right-click- Folder- Create Folder- and name it (Sunburst2).

You may have noticed the selected field has been grouped in the Sunburst2 folder. You need to remove it.

Click on the same field- right click- Folder- Remove from Folder. 

Great, our new folder (Sunburst2) is ready so we can copy all calculated fields there and keep it organized.

6.     Before switching, let's copy all calculated fields from Dataset 1(2) to Dataset 1. So click on previous folder (Sunburst1) – ctrl + Select all calculated fields- right click- copy

7.     Go to Dataset 1, click on any calculated field- right-click- Paste those fields.

You will notice all copied calculated fields are already copied and highlighted.

So just right-click- Folder- Add to folder – Select folder (Sunburst2). All calculated fields will be grouped under the Sunburst2 folder.

8.     Now, we need to switch from 2 or multiple datasets to a single dataset only. I have two datasets – Dataset 1 and Dataset 1(2). So, I will be switching my all charts from Dataset 1(2) to Dataset 1.

Go on Data Tab- select the Dataset that you want to replace (Dataset 1(2))- right click- Replace Data Source.

9.     Select the current dataset that you want to replace (Dataset 1(2)) and Replacement as the dataset that you want to keep (Dataset 1) click OK. It will replace the dataset with one dataset now. 

10.     Dataset 1 & Dataset 1(2) both are still visible in the left top corner. So we need to remove the other dataset (Dataset 1(2)).

So again go on Data Tab- select the dataset that you just replaced (Dataset 1(2)) – right click- Close. Now, you can see the Dataset that you replaced has been removed.

11.     So finally you have switched all your charts in the single workbook and under a single dataset (Dataset 1).

Awesome, you have merged multiple workbooks/ worksheets in a single workbook and also switched from multiple datasets to a single dataset.

Note: You may face below mentioned issues therefore please follow the steps properly.

-       Some of the calculated fields will show errors. It's because you didn’t select and copy all calculated fields based on which those new formulae are created. So make sure you can copy every calculated field you used for creating charts.

-       You can just click on those calculated fields, edit, and see if any of the fields are changed or not highlighted. In that case, again copy those in your dataset.

Hoping, this guide will help you to keep your dashboards and worksheets more organized and replace datasets without creating charts from scratch.  Soon, I will be posting another blog, about how we can switch advanced charts in less time with a single data source. Keep reading and supporting.

Thanks.