Tableau Conditional Formatting 101: Easy Hacks to Modify

Tableau Conditional Formatting can aid in highlighting patterns and trends in your data. To use it, you create rules that determine how cells are formatted based on their contents. Tableau Conditional Formatting can be applied to a range of cells, an Excel table, and even a PivotTable report in Excel for Windows.

Tableau Conditional Formatting is one of the most widely used methods for quickly classifying data. Color-coding is the most important aspect of this Data Analysis tool, and it essentially means formatting data values based on certain conditions.

This blog is a guide to Tableau Conditional Formatting. You’ll learn how to perform Tableau Conditional Formatting by modifying data and rows and columns in this tutorial.

What is Tableau?

Tableau is a well-known Business Intelligence and Data Analytics tool that was developed to assist in visualizing, analyzing, and understanding complex business data to make data-driven decisions. It is a smart platform that allows businesses to move more quickly and in a way that clients and consumers can understand. The most important feature of this tool is that it makes it extremely simple for users to organize, manage, visualize, and understand data.

What is Tableau Conditional Formatting?

• Many times during your analysis, you’ll run into situations where you’ll need to classify something quickly. The classification aids in the understanding of significant differences, which can then be used to make critical decisions in the context of the problem.
• Tableau Conditional Formatting is one of the most widely used methods for quickly classifying data. As the name implies, color-coding is the most important aspect of Tableau Conditional Formatting data analysis tool, and it essentially means formatting data values based on certain conditions.
• Tableau Conditional Formatting has features that enable the concept to be applied even to graphs, making visualizations more interactive and communicative.
• Tableau Conditional Formatting is a good way to draw attention to textual information. Color and Symbols are two common examples of Tableau Conditional Formatting. When you use conditional formatting in Tableau visualizations, you can quickly highlight important data.

Understanding the Tableau Conditional Formatting Process

Modifying Rows & Columns 

Modifying Rows & Columns helps you get a better understanding of data and helps you get the information you need. Rows & Columns can be modified easily using Tableau Conditional Formatting feature.

Microsoft Excel is generally used to perform Condition Formatting. You have a ready-to-use option in Microsoft Excel that formats the background color of the cells based on the values. The color intensity varies depending on the magnitude of the value. However, to use Tableau Conditional Formatting in your analysis, things get a little tricky. To achieve this in Tableau Conditional Formatting follow the steps below:

• Step 1: Profit and sales figures for twenty major Indian cities are included in your data. Tableau has been used to load the data. Moving to the sheet tab, you can see the only dimension City, as well as the two measures Profit and Sales, all of which are present in their own sections.

• Step 2: To begin, drag the City Dimension into the Rows region, as shown below.

• Step 3: Create a Calculated Field as shown below:

• Step 4: Create a calculated field called 1.0 and type 1.0 in the code section. This field can be found in the Measures section after it has been created, as shown below.

• Step 5: Drag each of the calculated fields 1.0 into the Columns region one at a time. AVG should be used instead of 1.0. (Average). To do so, select Average in Measure from the field’s dropdown menu.

• Step 6: Now, select Dual Axis from the dropdown menu of the second 1.0 measure.

• Step 7: The top and bottom axes will be changed now. Right-click on the top axis and select Edit Axis from the pop-up menu.

• Step 8: The Edit Axis dialogue box’s default selections and values are shown below.

• Step 9: Make the changes shown below in the General section of the Edit Axis dialogue box.

• Step 10: Select None for Major Tick Marks and Minor Tick Marks in the Tick Marks section of the dialog box.

• Step 11: Changes in both the General and Tick Marks sections of the bottom axis are similar to those in the top axis, except that the title is left blank in the General section, as shown below.

• Step 12: Following the steps above for Tableau Conditional Formatting, you get the following visualization. Because you have value 2 in the Fixed end in the General section of the Edit Axis dialogue box, and value 1 in the 1.0 field, the blue bars are half the size of the cell.

• Step 13: To get full-size bars, double-click the first 1.0 measure pill and simply add 1 as shown below.

• Step 14: The steps above result in full-size in-cell bars, as shown below.

• Step 15: Drag Profit over Color for the first 1.0 field in the Marks Card. This results in a variety of colored bars. Please note that the different colored bars represent different Profit measure values.

•  Step 16: Drag Profit over Text in Marks to the second 1.0 measure, and values will appear in the cells as shown below.

• Step 17: The colors haven’t filled the entire cell in the previous step, as you can see. To fill the entire cell with color, drag the size slider to the rightmost side for the first 1.0 field and select the largest size. The following diagram depicts this. The cells are now completely colored, as you can see.

• Step 18: If the brightness of the background colors is too bright, you can reduce it by adjusting the opacity using the Opacity slider in the Color section, based on your needs. You chose to keep the opacity at 90% in this case. Similarly, the second 1.0 measure can be used to change the opacity of text values.
• However, the opacity of text values should be set to 100% to ensure that they stand out against the background colors. It’s worth noting that negative values are clearly distinguished from positive values in the visualization below.
• Negative values are indicated by orange, while positive values are indicated by blue. Furthermore, the degree of brightness for the color changes depending on the value.

• Step 19: You completed the steps above for the Profit metric. In the dataset, you have another important metric called Sales. You intend to conduct a similar analysis for measuring Sales as well. Simply repeat the steps above. Finally, change the color of Sales to Green, as shown below, or any other suitable color.

• Step 20: Now, the Tableau Conditional Formatting Analysis you performed looks like this. The color assigned based on the range of Profit and Sales values can be seen on the right side. The concept’s name comes from the values-based color-based formatting that provides critical data insights.

• Step 21: The image below shows a more detailed view of the Tableau Conditional Formatting Analysis you performed.

Modifying Data

You can modify data in with the help of Tableau Conditional Formatting feature. This makes data more readable.

You’re working with distribution volume data here, which is volume distributed by distributed units. Region and Distribution Unit are important dimensions, and Distribution Volume is a measure.

The goal is to categorize distribution units based on their volume of distribution using the feature Tableau Conditional Formatting. You’ll divide them into units with very high, high, moderate, and low volume distribution. Here’s how you’ll go about doing it:

• Step 1: To load the data source, go to the Data menu and select New Data Source. Alternatively, select Connect to Data from the drop-down menu.

• Step 2: Select the required data source type in the Connect section. It’s Microsoft Excel in this case. The data should then be loaded.

• Step 3: The data that has been loaded can be seen in the Data Source tab, which is shown below.

• Step 4: When you switch to the sheet tab, you can see the dataset’s dimensions and measures. They can be found in the appropriate sections.

• Step 5: To begin, drag the Distribution Unit dimension into the Rows region and the Distribution Volume dimension into the Columns region. As shown below, a horizontal bar chart is created. If the default chart type isn’t a bar chart, choose one in Show Me.

• Step 6: By moving the cursor over the Sort icon as shown below, you can sort the results in descending order.

• Step 7: Following that, you’ll create three parameters to help you achieve your goal. The parameters will primarily aid in the classification of distribution units based on distribution volumes. To make the first parameter, right-click anywhere in the Data section’s blank space and select Create Parameter from the menu that appears, as shown below.

• Step 8: Following the steps above, the Create Parameter dialogue box appears, as shown below.

• Step 9: In the Create Parameter dialogue box, make the following adjustments. Change the name to Threshold 1, keep the data type to Float, and set the current value to 100,000, with Automatic as the Display format and All as the Allowable values. To complete the process, click OK.

• Step 10: Create a second parameter called Threshold 2, with the Current value set to 50,000 and the rest of the details as shown below.

• Step 11: Finally, add the third parameter to the equation. Threshold 3 is the name you give it. Set the current value of this parameter to 25,000, and the rest of the parameters as shown below.

• Step 12: You can see the three parameters you created in the image below. You’ll now put them to use to accomplish your goal.

• Step 13: If you don’t use the parameters you created in a calculated field to categorize distribution units based on distribution volume correctly, they won’t serve your purpose. As a result, you’ll have to create a calculated field. To do so, right-click anywhere in the Data section’s blank space and choose Create Calculated Field.

• Step 14: As you can see, the field you created looks like this. DU Classification is the name you gave it. The most important point is that in the calculated field, you used the three parameters you created earlier. If the value of any distribution unit is greater than Threshold 1, i.e. 100,000, the code will classify it as Very High Volume DU.
• It will also handle the other classifications in the same way. Here, you didn’t hard code the values in the field because doing so might prevent you from dealing with context-dependent changes.
• If you have parameters at your disposal, changing parameter values will be reflected in the calculated field as the context changes.
• E.g. If you want to categorize distribution units with a distribution volume of more than 200,000 as Very High Volume DUs, you can simply change the Current value for Threshold 1 from 100,000 to 200,000 without changing the value in the calculated field.

• Step 15: Under Measures, the calculated field DU Classification appears, as shown below.

• Step 16: Simply drag the calculated field DU Classification over Color in Marks, and the Distribution Units will be divided into four categories based on their volume contribution, as shown below. The categories are listed on the right side of the page.

• Step 17: Examine the visualization by Tableau Conditional Formatting in greater detail. You can also change the order of the categories in the legend by dragging a category up or down with your mouse. The categories appear to be in the correct order in legend now.

• Step 18: A category’s color can also be changed. To do so, go to the Marks card and click on Color, then Edit Colors to make the necessary color selections.

You can also modify data by highlighting the top five and bottom five cities. Below are the steps how you can do it: 

• Step 1: As shown below, the data has been loaded with dimensions and measures.

• Step 2: As shown below, drag the Distribution Unit dimension into the Rows region and measure Distribution Volume over Text in the Marks card.

• Step 3: Sort the results in descending order so that the cities are listed in order of Distribution Volume from highest to lowest.

• Step 4: Create a calculated field next. The steps for making a calculated field have already been demonstrated. Name the calculated field Top 5 & Bottom 5 to highlight the top five and bottom five distribution units. The INDEX() and LAST() functions are used in the code section.
• The INDEX() function returns the current row’s index, while LAST() returns the number of rows between the current and last rows. INDEX() calculates from 1 to 0, whereas LAST() calculates from 0 to 1. The two functions have been combined in the example below.

• Step 5: As shown below, the newly created calculated field appears as a measure.

• Step 6: Simply drag the newly created calculated field Top 5 & Bottom 5 over Color in the Marks card, and the top and bottom five regions will be highlighted in red. As seen on the right side of the screen, the categories appear as False and True values.

Conclusion

This blog helps you gain a better understanding of Tableau Conditional Formatting by looking at various illustrations. However, because it is a highly interactive visualization tool, it can be used in a variety of ways that necessitate the use of the tool’s features. It makes it easier to apply Tableau Conditional Formatting to visualizations without limiting them to a specific format.