Scatter Plot Matrix in Excel (12x12 panels) with Correlation Matrix
Originally published: 02/03/2021 08:29
Last version published: 07/04/2021 09:50
Publication number: ELQ-38780-4
View all versions & Certificate
certified

Scatter Plot Matrix in Excel (12x12 panels) with Correlation Matrix

Excel template to build a Scatterplot Matrix—with up to 12 variables—in one chart for ease of analysis and formatting.

Description
Small multiples—an expression coined by Edward Tufte—is a series of small frames sharing the same graphical design structure, yet showing different data as the eye moves from one panel to the next.

Small multiples are efficient architecture for showing a large amount of multivariate data all in one eye span—hence enabling comparisons.

Unfortunately, Excel charting library doesn't include native small multiples. Users are either obliged to resort to VBA or to take the time consuming task of creating each and every chart, formatting them and arranging them in a suitable way to show patterns. If the data changes, the process has to be repeated. Not fun. Not Efficient.

So here I'm offering you an Excel template that takes a data set as an input and creates a scatterplot matrix with the following capabilities:

- Handles up to 12 variables (12x12 matrix)
- Displays the regression line in each panel
- Displays the correlation matrix in graphical format
- Evaluates the statistical model for user-selected variables
- Displays the 6-Plot charts—6 charts needed for assessment of the fit of regression analysis
- Data entered into Excel Table—hence the charts and formulas update automatically when the datasource expands or shrinks
- Flexibility to define the scales (tick-mark spacing) and their number formatting
- Ability to rearrange the order of the variables in the scatterplot matrix
- Ability to define the number of panels to be displayed in the scatterplot matrix
- The size of the data matrix is only limited by Excel's charting and calculating capabilities
- Ability to adjust the margin from the edges of the panels to the data
- User-friendly interface to adjust the above settings
- Fully customizable chart using standard Excel functionality
- Unlocked workbook/worksheets with ability to expand template to suit your needs


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.


NOTE: THE TEMPLATE RELIES HEAVILY ON EXCEL'S DYNAMIC ARRAYS CAPABILITIES, ONLY AVAILABLE TO OFFICE 365 SUBSCRIBERS.

This Best Practice includes
1 Excel model

Acquire consulting license for $25.00

Add to cart

Add to bookmarks

Discuss

Further information

To enable business analysts, scientists, statisticians, data visualizers and journalists to produce scatterplot matrices in Excel along with the correlation matrix in graphic form.

Moreover, the Excel template generates the statistical model along with the 6-Plot—a collection of 6 specific graphical techniques whose purpose is to assess the validity of a Y versus X fit.

Business, science, statistics, data visualization, data journalism

Template relies heavily on Excel's Dynamic Arrays capabilities, only available to Office 365 subscribers.

Reviews

  • Be the first to review this Downloadable Best Practice

    Write a review


keyboard_arrow_leftkeyboard_arrow_right

More Best Practices from Hicham Bou Habib

keyboard_arrow_leftkeyboard_arrow_right

Related categories


See all
keyboard_arrow_leftkeyboard_arrow_right

Any questions on Scatter Plot Matrix In Excel (12x12 Panels) With Correlation Matrix?

The user community and author are here to help. Go ahead!


0.0 / 5 (0 votes)

please wait...