Violin Plots in Excel: No Built-in? How to Plot

What are Violin Plots?

A violin plot is a powerful data visualization tool that combines a box plot with a kernel density plot. It shows the distribution of numeric data across different categories. Unlike a simple box plot, which only displays summary statistics like median and quartiles, a violin plot reveals the full shape of the data distribution, including peaks, valleys, and skewness. This makes it ideal for comparing distributions across groups, spotting multimodality, and understanding data density.

Why Use a Violin Plot?

Violin plots are especially useful when you want to:

• Compare distributions across multiple categories.
• Identify whether data is skewed or has multiple peaks.
• See the probability density of data at different values.
• Combine the benefits of box plots and density plots in one chart.

They are widely used in fields like biology, finance, and social sciences. However, creating them in Excel can be tricky because Excel has no built-in violin plot.

Excel No Built-in Violin Plot: What Now?

If you search for "excel violin plot" in Excel's chart options, you won't find it. Microsoft Excel does not offer a native violin plot chart type. But that doesn't mean you can't create one. With a bit of creativity and Excel's existing charting capabilities, you can build a violin plot from scratch. The key is to use a kernel density chart (also called a density plot) and combine it with a box plot or simply mirror the density curves.

Understanding Kernel Density Estimation

Kernel density estimation (KDE) is a statistical method to estimate the probability density function of a random variable. In simple terms, it smooths out a histogram to show the distribution shape. Excel doesn't have a built-in KDE function, but you can calculate it manually using formulas. Once you have the density values, you can plot them as a filled area chart, which forms the basis of a violin plot.

How to Create an Excel Violin Plot Step by Step

Here's a practical guide to making an excel distribution plot that resembles a violin plot. We'll use a combination of Excel formulas and charts.

Step 1: Prepare Your Data

Organize your data in columns. For example, suppose you have test scores for three groups: Group A, Group B, and Group C. Each group's scores should be in separate columns or in one column with a group label. For simplicity, let's assume each group's data is in its own column.

Step 2: Calculate Kernel Density

For each group, you need to compute the density estimate. Follow these sub-steps:

• Determine the range: Find the minimum and maximum values across all groups. Create a sequence of evaluation points (e.g., from min to max in increments of 1 or a suitable step).
• Choose a bandwidth: The bandwidth controls the smoothness. A common rule of thumb is to use Silverman's rule: bandwidth = 1.06 * standard deviation * n^(-1/5), where n is the sample size.
• Compute density: For each evaluation point x, calculate the sum of kernel functions for each data point. The Gaussian kernel is: (1/(sqrt(2*pi)*bandwidth)) * exp(-0.5*((x - data_point)/bandwidth)^2). Sum these for all data points and divide by n.

This can be done with Excel formulas. For each group, create a column of evaluation points and a corresponding column of density values. Use absolute references for bandwidth and data range.

Step 3: Create the Density Curves

Now that you have density values, you can plot them. Select the evaluation points and density values for one group, then insert a scatter plot with smooth lines. Alternatively, use a filled area chart for a more violin-like appearance. Repeat for each group, but plot them on the same chart. To make it look like a violin, you'll need to mirror the density curve. One way is to plot the density as positive values and then add a second series with negative density values, so the shape is symmetrical around a central axis.

Step 4: Combine into a Violin Plot

To create a true violin plot, you can:

• Plot the mirrored density curves for each group side by side.
• Add a box plot on top of each violin to show the median and quartiles.
• Adjust the x-axis so each group has its own violin.

This requires some manual chart formatting. You can use Excel's combo chart feature to combine area charts (for density) and scatter charts (for box plot elements). It's a bit of work, but doable.

Step 5: Fine-Tune and Format

Once the basic chart is ready, customize it:

• Remove unnecessary gridlines and axes.
• Add a legend to identify groups.
• Use colors to differentiate violins.
• Add data labels if needed.

Remember, the goal is to make the distribution easy to compare. A clean, well-labeled chart is more effective than a cluttered one.

Alternatives to Excel for Violin Plots

If building a violin plot in Excel feels too cumbersome, consider using other tools like Python (with seaborn or matplotlib), R (ggplot2), or dedicated statistical software. These tools have built-in functions for violin plots and can save time. However, if you're restricted to Excel, the method above works well for small to medium datasets.

Conclusion

Violin plots are excellent for visualizing data distributions, but Excel no built-in violin plot means you have to create them manually. By leveraging kernel density estimation and Excel's charting features, you can produce an excel kernel density chart that mimics a violin plot. While it requires some effort, it's a valuable skill for anyone doing data analysis in Excel. So next time you need an excel distribution plot, don't shy away from violin plots—roll up your sleeves and build one!