How to Show Missing Values in Tableau

Ever created a line chart in Tableau expecting a smooth, continuous line, only to see it broken by frustrating gaps? Those breaks in your visualization aren't errors, they represent missing values or nulls in your dataset. This article will guide you through several easy-to-follow methods to fill those gaps, ensuring your reports are complete, accurate, and tell the full story.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why Bother with Missing Values?

Leaving gaps in your data visualizations can do more than just look unprofessional. It can actively mislead your audience and obscure important insights. Here’s why properly handling missing data is so important:

• Improved Data Integrity: Gaps can misrepresent trends. A missing month in a sales report might look like a temporary glitch in the chart, but it could represent a month with absolute zero sales - a critical piece of business information. Showing that drop to zero paints a much more accurate picture of business performance.
• Accurate Calculations: Many of Tableau's more powerful features, like moving averages or running totals, rely on a continuous data range. Missing values can break these calculations, leading to incorrect results that could lead to poor business decisions.
• Better Audience Comprehension: A continuous line chart or a complete table is intuitively easier to understand. Gaps force your audience to pause and question the data's credibility. Filling them correctly builds trust and helps your stakeholders focus on the insights, not on figuring out the chart itself.

The Easiest Fix: Tableau’s "Show Missing Values" Feature

For most time-series data, Tableau has a simple, built-in feature designed specifically for this problem. This method is perfect when you have date dimensions (or numeric "bins") that are missing records.

Imagine you have sales data for January, March, and April, but no sales were recorded for February. When you build a line chart of Sales by Month, Tableau draws a line from January to March, completely skipping over February.

Here’s how to fix it in two clicks:

1. Create your chart with a continuous date dimension on the Columns shelf (this typically looks like a green pill) and your measure (like SUM(Sales)) on the Rows shelf.
2. Right-click the date pill on the Columns shelf.
3. From the context menu, select "Show Missing Values."

That's it. Tableau will instantly "pad" the date axis, adding a mark for February and connecting the line through it. Tableau now recognizes that February exists in the time continuum, even if your data source has no record for it. Visually, the line will now likely drop to zero, accurately reflecting the lack of sales. This one-stop solution is often all you need for fixing broken time-series charts.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Filling the Gaps: Using ZN() to Turn Nulls into Zeros

While "Show Missing Values" created the space for February in our chart, the underlying value for that month isn't truly zero - it's still a null. If you hover over the data point, you'll see a blank tooltip for your sales measure. For clarity and calculation accuracy, you often want to convert these nulls into actual zeros.

This is where the ZN() function comes in. It stands for "Zero Null" and does exactly what its name implies: it checks an expression, and if the value is null, it replaces it with a 0.

Let's create a calculated field to handle this.

Step-by-Step Guide to Using ZN()

1. Create a Calculated Field: Click the down arrow in the top left of the Data pane and select "Create Calculated Field."
2. Name Your Field: Give it a descriptive name, like "Sales (ZN)."
3. Write the Formula: The syntax is straightforward. You wrap your measure in the ZN() function. For an aggregated measure, it looks like this:
4. Use the New Field: Drag your new "Sales (ZN)" calculated field onto the Rows shelf, replacing the original SUM(Sales) pill.

Your chart will look the same, but now when you hover over the February data point, the tooltip will explicitly show "Sales (ZN): 0". This small change makes your visualization technically more accurate and prevents nulls from interfering with other calculations you might want to layer on later.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Advanced Techniques: Using LOOKUP() to Carry Values Forward

What if a zero isn't the right answer? Sometimes, a missing value doesn't mean "nothing happened", it means "no new data was recorded." A perfect example is tracking monthly subscription counts or inventory levels. If you have 500 subscribers at the end of May and no new data for June, it’s far more likely you still have 500 subscribers, not zero. You want to carry the last known value forward.

For this, we need a table calculation: the LOOKUP() function.

The LOOKUP() function can fetch a value from a different row in the visualization. By telling it to look at the previous row (an offset of -1), we can fill nulls with the last valid data point.

Step-by-step Guide to Using LOOKUP()

Here's how to create a calculated field that fills null values with the previous value:

1. Create a Calculated Field: Open a new calculated field and name it something like "Subscribers (Filled)."
2. Write the Formula: This formula uses a logic statement to check if the current value is null. If it is, it looks up the previous value.
3. Add it to the View: Drag your new “Subscribers (Filled)” field onto the Rows shelf. Because it’s a table calculation, Tableau will add a small triangle (a delta symbol) to the pill.

Your chart will now show a flat line across June, carrying the 500 subscriber count from May. This provides a much more realistic view for metrics that maintain a state, rather than metrics that reset to zero, like sales.

Handling Gaps in Tables and Categorical Data

Missing data isn't limited to line charts. You’ll often encounter it in text tables (crosstabs) where certain combinations of dimensions just don't exist in your dataset. For example, if you build a table showing Sales by Region and Product Category, you might have no records for "Office Supplies" in the "East" region.

By default, Tableau simply won't show a row for that combination. If you want to display that gap in a complete matrix of all possibilities, you need a different setting.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

How to Show Empty Rows and Columns

1. Create a view with two or more dimensions on the Rows and/or Columns shelves.
2. In the top menu, go to Analysis > Table Layout.
3. Select either "Show Empty Rows" or "Show Empty Columns," depending on your table's structure.

Tableau will now draw the full matrix, displaying the "Office Supplies" row for the "East" region, leaving the measure value cell blank. This is incredibly useful for seeing a complete picture of your product-market landscape and identifying exactly which combinations are missing. From here, you can use the ZN() technique described earlier to fill those blank cells with zeros for a clean, comprehensive table.

Putting It All Together: Best Practices

As you work with missing values, keep these quick tips in mind to ensure your reports are consistently clear and reliable.

• Know Your Nulls: Before you fill a gap, ask yourself what it means. Does it mean "zero," or does it mean the data is genuinely missing, or did the activity not exist yet? Filling January and February with zero sales for a product launched in March is misleading. Let the context guide your choice of method.
• Choose the Right Method for the Job:
• Always Add Context: Don't make your audience guess. Add a caption or an annotation to your dashboard explaining how you've treated missing data. A simple note like, "Gaps in daily sales data are shown as zero" or "Monthly headcount is carried forward from the previous month" builds trust and prevents misinterpretation.

Final Thoughts

Handling missing values in Tableau isn't about hiding flaws, it’s about presenting a more complete and honest narrative with your data. By combining Tableau’s built-in options like "Show Missing Values" with the logical power of calculated functions like ZN() and LOOKUP(), you can transform fractured visualizations into clear, authoritative reports that drive better decision-making.

The manual effort spent tuning visualizations and managing data quirks in tools like Tableau is a core reason we created Graphed. A key part of our process is to intelligently clean and prepare your data the moment you connect sources like Google Analytics or your CRM. This way, you can move directly to analysis instead of getting stuck on data prep. When you ask a question like, "Show me my revenue by month for the past year broken down by campaign," Graphed automatically generates a continuous, clean chart without you ever needing to right-click a menu or write a formula to fix the gaps.