How to Create a Panel Chart in Excel

Excel Panel Chart Template – Free Download

A panel chart (also known as a small multiple, lattice chart, grid chart, or trellis chart) is a set of small graphs placed next to each other that use the same scales and axes for comparing similar categories across a data set.

The goal of a panel chart is to help you quickly compare multiple sets of closely related data without overwhelming you with its volume or taking up too much precious dashboard space.

As a general rule, the more data series you have mapped out on your plot, the messier it gets—but not in the case of lattice graphs.

Enough of the theory. Let’s move on to the practice. By the end of this tutorial, you will create this panel chart from the ground up—even if you are a complete Excel newbie:

excel panel chart download free template

Sample Data

To walk you through the process, we need some data to work with. In order to build our panel chart, we’ll use some random data on historical stock price trends scraped from the Web:

Before we dive in, here’s the breakdown of this table to help you know what we’re working with:

  • Column A (Company): This column maps out the categories that will be compared with each other across all the sections of the future grid chart.
  • Column B (Year): This column represents the y-axis of our panel chart; for your panel chart to make sense, you need to measure your data with the same yardstick.
  • Columns C and D (Year High and Year Low): These columns determine the actual values; the beauty of the technique you’re about to learn is that you can use as many columns as you want.

Now that we’re on the same page, let’s get down to business.

Step 1. Create the Separators

Our first step is to add a helper column with alternating separators to split the table into four separate sections (panels).

  1. Next to your data table, create Column E (Separator).
  2. For each of the categories in Column A, assign a custom value—either “1” or “2”—to each of the companies in the table in alternating order as shown in the screenshot below.

Once there, your data table should end up like this:

Step 2. Create a Pivot Table

Next stop: create a pivot table based on the expanded table.

  1. Select any cell in the data table (A1:E21).
  2. Navigate to the Insert tab.
  3. Choose “PivotTable.”

In the dialog box that pops up, set up your pivot table by following these instructions:

  1. For the “Table/Range” field, highlight the entire data table (A1:E21).
  2. For where to place the table, select “Existing Worksheet.”
  3. Pick any empty cell in your worksheet.
  4. Click “OK.”
panel line chart excel

The moment you close the dialog box, the PivotTable Fields task pane will appear. In the task pane, you’ll need to rearrange the layout in the following way to lay the groundwork for your small multiples:

Shift the columns around in the exact same order as shown below (it’s important):

  1. Drag “Company”and“Year” to “Rows” (with “Company” on top).
  2. Drag “Year High”and“Year Low” to “Values.”
  3. Drag “Separator” to “Columns” and place it above “Values.”
panel chart excel 2013

Doing all of that will result in this pivot table:

excel three panel chart

Read More: How to Make a Residual Plot in Excel

Step 3. Format the Pivot Table

Now that you have put together the pivot table, let’s get rid of the elements we don’t need. Start by removing the subtotals:

  1. Select the pivot table.
  2. Go to the Design tab.
  3. Open the “Subtotals” dropdown menu.
  4. Click “Do Not Show Subtotals.”
excel panel chart template

Next, remove the grand totals:

  1. In the same tab, select “Grand Totals.”
  2. Choose “Off for Rows and Columns.”
excel small multiples free template

Finally, convert the pivot table into tabular form:

  1. Again, in the same tab, select “Report Layout.”
  2. Click “Show in Tabular Form.”
how to make a grid chart in excel

By the end of this stage, your pivot table should look like this:

how to create a trellis chart in excel

Step 4. Rearrange the Pivot Table (Optional)

Provided you have followed all of the steps outlined above, Excel should automatically position the categories in alternating order using the separators. But that doesn’t always happen.

If you ever get stuck at this stage, here’s a quick-and-dirty way to fix the issue:

  1. Right-click on the name of the category you want to move up or down (such as “Tesla”).
  2. Choose “Move.”
  3. In the menu that appears, select “Move ‘Tesla’ Down.”
how to create a lattice chart in excel

Armed with this simple technique, you can quickly and easily rearrange the pivot table so that the categories are arranged in alternating order.

Step 5. Prepare the Chart Data

At this point, you need to separate the data from the pivot table to be able to use it for building your grid chart.

To start with, drop the sorted data in the pivot table into empty cells somewhere near it (Copy > Paste Special > Values).

excel panel chart free template

After that, copy the column labels characterizing the actual values (“Year High” and “Year Low”); since the dividers split the data into two separate data sets, you need to duplicate the labels as well.

NOTE: The values in the header row will be used for generating the legend of your panel chart.

excel small multiples template

Step 6. Create a Panel Chart

At last, after all that preparation, you can now get down to building your panel chart:

  1. Highlight the freshly created table (N3:S23).
  2. Go to the Insert tab.
  3. Click “Insert Line or Area Chart.”
  4. Choose “Line.”
how to build a trellis chart in excel

And there you have your lovely panel chart:

excel panel line chart example

Step 7. Adapt the Color Scheme

Basically, the trellis graph is a set of two simple line charts. For that reason, before we can call it a day, we need to add a bit more consistency to the color scheme to make the graph readable.

To recolor your data series, right-click on any of the lines charted on the plot area and click “Format Data Series.”

excel panel chart with different scales 03

In the Format Data Series task pane that appears, do the following:

  1. Switch over to the Fill & Line tab.
  2. Open the palette and pick the color you want.
  3. Use the chart legend to double-check the color consistency.
how to recolor a panel chart

Step 8. Clean Up the Chart Legend

Finally, remove the redundant legend labels. Select the legend label you want to remove, right-click on it, and choose “Delete.”

how to format a chart legend in a panel chart

Spruce up the graph with a custom chart title, and your panel chart is ready to go:

panel chart in excel example

(Bonus) Step 9. How to Create an Excel Panel Chart with Different Scales

If you’re wondering how to create a panel chart with different scales, you’ve come to the right place.

For those who jumped straight to this section, first you need to build your panel chart using the step-by-step process outlined above—and before you close this article in a blind panic, know that this should only take a few minutes.

Once we’re on the same page, to plot your panel chart on two separate scales, right-click on the chart plot and choose “Change Series Chart Type” from the menu that appears.

excel panel chart with different scales 01

In the dialog box that pops up, chart some of the data series on the secondary axis:

  1. Switch to the “Combo” tab.
  2. Check the “Secondary Axis” box next to a set of identically named data series—in our case, Series Year Low.”
  3. Click “OK.”
excel panel chart with different scales 02

Finally, you have a clean, neat-looking panel chart with different scales ready to blow everyone away!

excel panel chart with different scales free template