Graphed LogoGraphed
Login

How to Remove Zero Values from Chart in Tableau

Cody Schneider•

Seeing your carefully crafted Tableau chart cluttered with zero values can be frustrating. Zeros can flatten your line charts, add unnecessary noise to bar graphs, and generally make it harder to spot meaningful trends. This guide will walk you through a few practical methods for removing or hiding zero values, cleaning up your visualizations, and letting your key insights shine through.

Why Zeros Can Be Problematic in a Chart

Before jumping into the solutions, it helps to understand why zero values can be a visual problem. In many cases, a zero isn't just a number, it represents an absence of activity. A day with zero sales, a product with zero stock, or a campaign with zero clicks are data points, but including them in every visualization isn’t always helpful.

  • Distorted Scale: In a line chart showing daily sales, a few zero-sale days can drag the entire line down to the axis, making it much harder to see the variation and trends on the days you did make sales. The significant peaks and valleys get flattened.

  • Visual Clutter: Imagine a bar chart displaying sales for 100 different products. If 40 of them had no sales in the selected period, you have 40 empty spaces or tiny placeholder bars cluttering the view and distracting from the products that actually performed well.

  • Misleading Averages: If you're calculating an average and include a large number of zero values, your average can become artificially low and misleading. Sometimes, you want the average of the active items, not all items.

The goal is to present your data with clarity and focus. Removing these zeros often helps achieve that by letting you and your stakeholders focus on the actual performance and activity that occurred.

Method 1: The Simple Filter Approach

The quickest and most common way to get rid of zero values is by using a standard filter. This method removes the entire row of data containing the zero from your visualization. It's perfect for charts like bar charts, scatter plots, or tables where you simply want to exclude rows that have a value of zero for a particular measure.

Let’s say you have a bar chart showing Sales by Product Sub-Category, and you want to remove any sub-categories that had zero sales.

Step-by-Step Instructions:

  1. Drag the measure you want to filter (in this case, SUM(Sales)) from the Data pane onto the Filters shelf.

  2. A dialog box will appear asking how you want to filter the measure. Since Sales is a continuous measure, you'll see options like "Range of values", "At least", "At most", etc. Select "Range of values" and click Next.

  3. You'll now see a slider with a lower and upper bound. To exclude zeros, you have a couple of options:

    • Drag the left side of the slider just slightly to the right, a tiny bit past zero.

    • For more precision, click on the lower number text box and type in a very small value, like 0.01. This ensures that only values strictly greater than zero are included.

  4. Click OK. Your chart will update immediately, and all the bars representing zero sales will disappear.

When to use this method:

This is your go-to method for visuals where the absence of a mark doesn’t create confusion. It's clean, fast, and easy to understand.

A key consideration: Be mindful that this approach removes the data entirely from the sheet's view. For line charts across a continuous date axis, this can cause the line to break and behave in ways you might not expect, which leads us to our next method.

Method 2: Using a Calculated Field to Handle Zeros in Line Charts

Line charts showing a trend over time are a classic case where the simple filter approach falls short. If you filter out a day with zero sales, you might create a sudden gap in your data, and Tableau might try to connect the preceding day with the following day, creating a misleading straight line over the missing day.

What you often want is for the line to stop, show a visible gap for the zero-value day, and then resume on the next day with data. The way to achieve this is by converting the zero values to NULL values, because Tableau doesn’t plot nulls in a line chart. The tool for this job is a calculated field.

Continuing with our sales example, let’s create a line chart showing daily sales that treats zero-sale days as gaps.

Step-by-Step Instructions:

  1. Click the dropdown arrow at the top of the Data pane and select Create Calculated Field.

  2. Name your new field something memorable, like Sales (No Zeros).

  3. In the formula box, enter the following calculation:

This simple logic checks the sum of sales. If it's zero, the formula returns a NULL. Otherwise, it returns the actual sum of sales.

  1. Click OK to save the calculated field.

  2. Now, build your line chart. Place your date dimension (e.g., Order Date) on a continuous 'Day' level on the Columns shelf.

  3. Instead of dragging your original Sales measure to the Rows shelf, drag your new calculated field, Sales (No Zeros), to the Rows shelf.

You will now see a line chart that displays sales over time. On any days where sales were zero, the line will break, showing a clear gap. This is far more accurate and intuitive than having the line drop sharply to the zero axis or ignoring the day completely.

Method 3: Creating a Set for Dynamic Filtering

For more advanced or dynamic scenarios, using a Set can be an incredibly powerful way to handle zeros. A Set acts like a dynamic grouping. In this case, you can create a set that automatically includes all members of a dimension that have a value greater than zero.

This is particularly useful if your data updates frequently. As new data comes in, the set will automatically adjust without you having to manually re-configure your filters. Let’s create a set for all customers who have made a purchase.

Step-by-Step Instructions:

  1. In the Data pane, find the dimension you want to filter by (e.g., Customer Name).

  2. Right-click on Customer Name and select Create > Set.

  3. Give your set an informative name, like Customers with Sales.

  4. In the 'Create Set' dialog box, there are three tabs: General, Condition, and Top. Click on the Condition tab.

  5. Select the option By field.

  6. In the dropdowns, configure the condition as follows:

    • Field: Sales

    • Aggregation: Sum

    • Operator: >

    • Value: 0

This condition tells Tableau, "Include a customer in this set only if the sum of their sales is greater than 0."

  1. Click OK. You'll now see your new set in the Data pane at the bottom, under the 'Sets' area.

  2. To use it, simply drag the Customers with Sales set to the Filters shelf.

Tableau will now automatically filter your view to show only the members that are "IN" the set — in this case, every customer with sales. Any customer with zero sales is automatically excluded. The best part is that this set is completely dynamic. If a customer without previous sales makes a purchase tomorrow, they will automatically be added to the set when the data refreshes.

Method 4: Hiding Zeros with Custom Number Formatting

Sometimes you don't want to filter the data out of the view, but you simply don't want to see the number zero. This is common in crosstabs or text tables where leaving a cell blank is cleaner than displaying a "0". For this, custom number formatting is the perfect tool.

Important note: This method only changes the visual display. The underlying zero value is still present in the data and will be included in any totals, averages, or other calculations.

Step-by-Step Instructions:

  1. In your view, right-click the measure pill whose zeros you want to hide (it could be on the Rows shelf, Columns shelf, or on the Text mark).

  2. Select Format.

  3. The Format pane will appear on the left. Make sure you are on the Pane tab (or Axis, depending on where the pill is).

  4. Under the 'Default' section, click the Numbers dropdown.

  5. Choose Custom.

  6. In the 'Format' box, type the following custom format string:

Let's break down this format. A custom number format in Tableau has up to three sections, separated by semi-colons:

  • For Positive Numbers

  • For Negative Numbers

  • For Zero Values

By putting "" (two empty quotes) as the third part of the string, you're telling Tableau to display nothing when the value is exactly zero. The number formatting for positive and negative numbers remains the same. The zeros on your chart or table will now appear as blank spaces.

Final Thoughts

Cleaning up zero values can take your Tableau dashboards from good to great by improving readability and focusing your audience's attention on what really matters. Whether you use a simple filter for a bar chart, a NULL calculation for a line chart, a dynamic set for changing data, or custom formatting for a text table, you now have a versatile toolkit to create clearer, more impactful visualizations.

While mastering these techniques in dashboards and BI tools is a valuable skill, it often means stopping the flow of analysis to set up filters, write calculations, or adjust formats. We created Graphed because we believe getting the insights shouldn’t be bogged down by configuring the tool. Instead of manually excluding zeros, you could simply ask, "show me website sessions from the UK by day last month, but ignore days with no traffic," and instantly get a clean, intelligent chart that already understands your intent. We connect all your data sources so you can get presentation-ready reports and dashboards just by asking questions in plain English.