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 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.

‍

‍

‍

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.

‍

‍

‍

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.

‍

‍

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