Select any cell in the pivot table ( G4:L26).Now, change the layout type and remove the redundant table elements. Move “ Orange Ltd” and “ Banana Ltd” to “ Values.”Īt this point, your pivot table should look something like this:.In this task pane, shift the items in the field list into the following order-the order is important-to modify the layout of your new pivot table: Immediately after your pivot table has been created, the PivotTable Fields task pane will pop up. Step #3: Design the layout of the pivot table. When the Create PivotTable dialog box appears, select “ Existing Worksheet,” highlight any empty cell near your actual data ( G1), and click “ OK.” Highlight any cell within the dataset range ( A1:E25).Once you have completed Step #1, create a pivot table. In the same way, fill the remaining blank cells in the column, alternating between the two separator values as you move from state to state. Then type “ 2” into all the respective cells that fall into the second category, Minnesota ( E8:E13). Type “ 1” into each corresponding cell of column Separator that belong to the first category, Iowa ( E2:E7).
#HOW TO PLOT A GRAPH IN EXCEL AND GET A FUNTION HOW TO#
Otherwise, we will show you how to use a pivot table to manipulate the data into the necessary format.īefore you can create a panel chart, you need to organize your data the right way.įirst, to the right of your actual data ( column E), set up a helper column called “ Separator.” The purpose of this column is to split the data into two alternating categories-expressed with the values of 1 and 2-to lay the groundwork for the future pivot table. This approach may be preferable if your data is simple. Technically, you can manipulate the data manually (if so, skip to Step #5). In the end, we want our data to look like this: We are going to use just two sets of data for illustration purposes, but with the method shown in this tutorial, the sky is the limit. Orange Ltd and Banana Ltd – These are your actual values.The values and formatting should be identical across all panels. Year – This column determines the horizontal axis scale.In our case, every mini line chart illustrates the performance dynamics for each of the states. State – This column represents the categories by which the chart will be split into smaller charts (panels).With all that said, consider the following table: As you may have already guessed, we are going to compare the historical track record of the franchisees of two companies, Orange Ltd and Banana Ltd, in four different states-Iowa, Minnesota, Texas, and Utah. To illustrate the steps for you to follow, we need to start with some data. In this tutorial, you will learn how to plot a customizable panel chart in Excel from the ground up. But before we begin, check out the Chart Creator Add-in, a versatile tool for creating advanced Excel charts and graphs in just a few click. Unfortunately, this chart type is not supported in Excel, which means you will have to manually build it yourself. Not only does the chart allow you to neatly display more information, it also helps you quickly compare or analyze the relationship between multiple data sets at once-while saving you a great deal of dashboard space.Īs an example, take a look at the panel chart below, which combines four separate line charts comparing the annual revenue of Orange Ltd and Banana Ltd franchisees across four states for the past six years.Īs you can see, the panel chart illustrates a bottomless well of useful data, as opposed to the jumble of lines you would end up getting if you simply opted for its built-in counterpart. Since these mini charts share the same axes and are measured on the same scale, basically, a panel chart consolidates all of them into one place. Step #11: Hide the helper chart elements.Ī panel chart (also called a trellis chart or a small multiple) is a set of similar smaller charts compared side-by-side and divided by separators.Step #8: Change the chart type of the dummy series.Step #6: Make the line colors consistent.Step #4: Extract the data from the pivot table.Step #3: Design the layout of the pivot table.Return to Charts Home How to Create a Panel Chart in Excel Create, Save, & Use Excel Chart Templates