Dynamic Scatterplot in Excel

If you ask a group of data analysts and data visualization experts to choose the most important chart type to display data, most probably “The scatterplot” would be the response you’ll get. And they have a point. Edward Tufte, in the Visual Display of Quantitative Information, crowned the scatterplot—and its variants—as the greatest of all graphical designs. The scatterplot encourages the viewer to assess relationships by showing how one variable affects another.

So here I'm offering you an Excel template that takes a data set as an input and creates a dynamic scatterplot with the following capabilities:
- Handles up to 5 categories (i.e. region, economic sector, subject)
- Displays the regression line for all the data set or for each category
- Evaluates the statistical model for up to 5 categories and for all the data set
- Displays the 6-Plot charts—6 charts needed for assessment of the fit of regression analysis
- Ability to exclude selected data points from the analysis (i.e. to check the impact of outliers on the regression line)
- Ability to show or hide the trendlines
- Ability to show the data labels for selected data points
- Ability to segment the data by category, dynamic refrence lines (Mean, Median or user defined), or by brushing the data
- Ability to switch the two variables between the X and Y axis
- Flexibility to define the number formatting for the two variables
- User-friendly interface to adjust the above settings
- Data entered into Excel Table—hence the charts and formulas update automatically when the datasource expands or shrinks
- The size of the data matrix is only limited by Excel's charting and calculating capabilities
- Fully customizable chart using standard Excel functionality
- Unlocked workbook/worksheets with ability to expand template to suit your needs

System requirements

The template relies heavily on Excel's Dynamic Arrays capabilities, only available to Microsoft 365 subscribers.

What's the refund policy?

If what you see is not what you expected, just reply to the download email within 30 days, and you'll get a full refund. No questions asked.