Arrow left
Back to guides
Excel Tips

How to insert Scatter Charts in Excel with missing data points

In this article, you will learn a simple way to insert scatter charts with missing data points in Excel.

What are Scatter Charts in Excel?

Scatter charts are used to plot information that contains independent and dependent variables, thereby determining the correlation between the two variables.

For example: It can be used to visualize and calculate the correlation between the number of sales personnel and revenue earned on a monthly basis as you can see below.

Scatter chart with all data points

Scatter Charts in Excel with missing data points

If the data set contains certain data points that are not available or missing, such missing data points are often denoted with free text such as “NA” or “Missing” or “Not Available” or any other text comment. Thus, the problem arises!

Scatter charts recognize any text as equal to zero and hence, incorrectly display the chart, line of best fit, and correlation (R²) value, as shown in the following picture.

Scatter chart with a missing data point displaying results incorrectly

How to insert Scatter Charts in Excel with missing data points?

Replace the missing data points with the formula “=NA()” in excel. The scatter chart now understands that a particular data point is missing and hence will exclude it.

For example: When the number of sales personnel is not available for June, if you use the excel formula “=NA()” instead of manually inputting a text “NA”, the scatter chart will exclude the month of June as shown below.

Scatter chart with a missing data point displaying results correctly

Analyze your live financial data in a snap in Google Sheets

Are you learning this formula to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows, update numbers in real-time, and save time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. Are you interested in this product but are an Excel user? That’s not a problem at all. You can connect Google Sheets to Excel quickly. 

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

You can learn about other Excel and Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides

Learn how to do this step-by-step in the video below 👇

Automate financial reporting with LiveFlow

Want to eliminate manual updates of your Excel & Google Sheets models?

Yes, show me how

Need help?

Our team is here to help you any time between 9am and 10pm EST.
Check Icon
Email us at:

Liked this article? Then you'll love the ones below