{"id":4410,"date":"2022-10-11T00:01:00","date_gmt":"2022-10-11T04:01:00","guid":{"rendered":"https:\/\/exceltheatre.com\/blog\/?p=4410"},"modified":"2022-10-07T15:48:52","modified_gmt":"2022-10-07T19:48:52","slug":"how-to-make-panel-chart-in-excel-video-step-by-step","status":"publish","type":"post","link":"https:\/\/exceltheatre.com\/blog\/archives\/2022\/10\/11\/how-to-make-panel-chart-in-excel-video-step-by-step\/","title":{"rendered":"How to Make Panel Chart in Excel &#8211; Video Step-by-Step"},"content":{"rendered":"<p>In Excel, there&#8217;s no built-in Panel Chart type, but you can create line panel charts, like the one shown below. Watch a video, to see the steps, and there&#8217;s a transcript too, and a link where you can download the sample Excel file.<\/p>\n<p><!--more--><\/p>\n<h3>Excel Panel Chart Example<\/h3>\n<p>This chart has product sales data for four cities, over 7 months, with the line charts in the<br \/>\nfour panels, arranged horizontally.<\/p>\n<p><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/panelchartline01.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"Excel Panel Chart sales 7 months\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/panelchartline01_thumb.png\" alt=\"Excel Panel Chart sales 7 months\" width=\"487\" height=\"211\" border=\"0\" \/><\/a><\/p>\n<h3><a name=\"video\"><\/a>Panel Chart Video<\/h3>\n<p style=\"clear: left;\">This video shows the steps for creating a line panel chart,<br \/>\nusing a Line Chart, and adding error bars and formatting, to separate the data into panels.<\/p>\n<p style=\"clear: left;\">The full written transcript for the video is below the video.<\/p>\n<p style=\"clear: left;\">There are detailed written steps on <a href=\"https:\/\/www.contextures.com\/excelpanelchart.html\" target=\"_blank\" rel=\"noopener\">the Excel Panel Chart page on my Contextures site<\/a>.<\/p>\n<p style=\"clear: left;\"><strong>Video Timeline<\/strong><\/p>\n<ul>\n<li>0:00 Introduction<\/li>\n<li>0:24 Step 1 &#8211; Insert Column<\/li>\n<li>1:11 Step 2 &#8211; Create Pivot Table<\/li>\n<li>2:16 Step 3 &#8211; Copy Data As Values<\/li>\n<li>2:48 Step 4 &#8211; Create Line Chart<\/li>\n<li>3:31 Step 5 &#8211; Add Dividing Lines<\/li>\n<li>7:06 Step 6 &#8211; Final Formatting<\/li>\n<\/ul>\n<p><iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/a0X0Ltf2JIY?rel=0\" width=\"450\" height=\"283\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><\/iframe><\/p>\n<h3>Get the Sample File<\/h3>\n<p>To follow along with the video, you can download the Excel file from <a href=\"https:\/\/www.contextures.com\/excelpanelchart.html\" target=\"_blank\" rel=\"noopener\">the Excel Panel Chart page on my Contextures site<\/a>..<\/p>\n<p>The zipped file is in xlsx format, and does not contain macros.<\/p>\n<h3>Video Transcript: Create a Line Panel Chart<\/h3>\n<p>This is the full written transcript for the How to Create a Panel Chart in Excel video, shown above.<\/p>\n<p>&#8216;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;<\/p>\n<h3>Introduction<\/h3>\n<p>In an Excel panel chart, you can show two or more similar sets of data side by side.<\/p>\n<p>In this chart, we have:<\/p>\n<ul>\n<li>four cities<\/li>\n<li>showing seven months of data for each city<\/li>\n<\/ul>\n<p>They&#8217;re in a single Excel chart, but it looks like four separate charts arranged horizontally.<\/p>\n<p>In this tutorial, we&#8217;ll see the steps for creating a line panel chart like this one, and we&#8217;ll follow these six main steps.<\/p>\n<h3>Step 1 &#8211; Insert Column<\/h3>\n<p>Our first step will be to add a separator field to the source data.<\/p>\n<p>Here&#8217;s the data table, here&#8217;s the city field and we have four different cities.<\/p>\n<p>I&#8217;ve listed them here, alphabetically, and we&#8217;re going to number them in two groups. I&#8217;ve put Boston and New York as one and San Diego and Los Angeles will be two.<\/p>\n<p>I&#8217;m going to insert a column. So I&#8217;ll right-click and Insert. I&#8217;m just going to select New York and Boston, type a one in those cells.<\/p>\n<p>And then the other two cities will be number two.<\/p>\n<p>Now the cities are numbered and I&#8217;m going to name this column stagger, because we&#8217;ll use it to stagger the data.<\/p>\n<p>So instead of it appearing in one column, when we create a pivot table, it will be in columns one and two that will create some blank areas in the data and those blanks won&#8217;t be plotted in the chart.<\/p>\n<h3>Step 2 &#8211; Create Pivot Table<\/h3>\n<p>The next step in creating a panel chart is to summarize this data in a pivot table.<\/p>\n<ul>\n<li>Click on any cell in the data and on the Insert tab, click Pivot Table.<\/li>\n<li>This table where the data is stored is Table1.<\/li>\n<li>We want this on a new worksheet and click OK.<\/li>\n<li>And there&#8217;s our blank pivot table.<\/li>\n<\/ul>\n<p>I&#8217;m going to put City and order date in the row labels area. We&#8217;ll put total price in the values and across the top we want Stagger.<\/p>\n<ul>\n<li>So I&#8217;ll drag that into the column labels<\/li>\n<li>And the other field we want there is Category and I&#8217;ll drag that below Stagger.<\/li>\n<li>I&#8217;m going to get rid of these city subtotals. I&#8217;ll right-click on Boston and remove the check mark from subtotal city.<\/li>\n<\/ul>\n<p>I also want the layout a little different, so that city and order date are in separate columns<\/p>\n<ul>\n<li>On the design tab where we are, there&#8217;s a Report Layout<\/li>\n<li>Right now, we&#8217;re in Compact form, which is the default. I&#8217;m going to go with Tabular form.<\/li>\n<li>We also have grand totals in this pivot table and I&#8217;m going to remove those because we don&#8217;t need them. So click Grand totals off for rows and columns.<\/li>\n<\/ul>\n<h3>Step 3 &#8211; Copy Data As Values<\/h3>\n<p>We&#8217;re going to as the next step, copy this data and put it onto another sheet, pasted as values<\/p>\n<p>I&#8217;ve zoomed out so we can see the entire pivot table.<\/p>\n<ul>\n<li>I&#8217;m going to select from the city heading down to the last cell with data<\/li>\n<li>And on the Home tab, click Copy<\/li>\n<li>And on a new, sheet click where I want it to be pasted, click the arrow for Paste.<\/li>\n<li>And we want to paste values and number formatting.<\/li>\n<li>I&#8217;m going to put the word Total in these two headings<\/li>\n<li>And remove the city and order date headings.<\/li>\n<\/ul>\n<h3>Step 4 &#8211; Create Line Chart<\/h3>\n<p>The next step will be to create a line chart from this data that we&#8217;ve copied<\/p>\n<ul>\n<li>On the Insert tab, click Line and create a 2d line chart<\/li>\n<\/ul>\n<p>There we can see two sets of data with bars, cookie, and total, and they&#8217;re formatted with different colors.<\/p>\n<p>We want them all to appear to be the same set of data. So I&#8217;m going to change the formatting on the second set.<\/p>\n<ul>\n<li>I&#8217;ve clicked to select the legend<\/li>\n<li>And then I&#8217;ll click on bars and that selects that item.<\/li>\n<li>I&#8217;m going to change its formatting, so it matches the first bars.<\/li>\n<\/ul>\n<p>Once they&#8217;re all formatted in the same color, you can delete the second set from the legend.<\/p>\n<ul>\n<li>With that item selected. I&#8217;ll just press the delete key and it&#8217;s removed from the legend.<\/li>\n<\/ul>\n<h3>Step 5 &#8211; Add Dividing Lines<\/h3>\n<p>The next step is adding the vertical dividing lines between the city data<\/p>\n<ul>\n<li>I&#8217;ve created a table on the worksheet.<\/li>\n<li>I&#8217;ve got three rows, one that will be used for each vertical line.<\/li>\n<li>We have data from January to July.<\/li>\n<li>So we have seven data points in each city set<\/li>\n<li>The first dividing line we want after the seventh data point, midway between that and eight. So it will be at this number plus 0.5.<\/li>\n<li>Each one that follows will be seven points after preceding one.<\/li>\n<li>We want this series to be at the zero point, so it will run along this horizontal axis.<\/li>\n<\/ul>\n<p>We&#8217;re also going to create error bars and they&#8217;ll all be the same height, and we&#8217;ve set that at one<\/p>\n<ul>\n<li>To add a series to the chart, we&#8217;re going to select the green cells and copy them.<\/li>\n<li>So with Ctrl + C, I copied, then I&#8217;ll select the chart.<\/li>\n<li>And on the Home tab, I&#8217;ll click the arrow for paste and click paste special.<\/li>\n<li>I&#8217;m going to paste this as a new series in columns names in the first row categories in the first column and click OK<\/li>\n<\/ul>\n<p>It&#8217;s been added to the chart. We just see a little blue line here. We&#8217;re going to change this into a scatter chart.<\/p>\n<ul>\n<li>So I&#8217;ve selected that little blue line<\/li>\n<li>And on the Design tab, change chart type X, Y scatter<\/li>\n<li>And I&#8217;ll select a line with no markers and click OK.<\/li>\n<\/ul>\n<p>Now this has automatically added a secondary axis and our line is now longer across the bottom.<\/p>\n<ul>\n<li>I&#8217;m going to click on it to select it.<\/li>\n<li>And if you have any trouble selecting it, you can go to the Layout tab, and there&#8217;s a dropdown where you can select any item. So I could select series vertical grid.<\/li>\n<li>So with this selected, I&#8217;m going to add error bars<\/li>\n<li>On the Layout tab, click Analysis, Error bars, and click More error bar options.<\/li>\n<li>We are going to put in vertical error bars. We want plus no cap and a custom setting here.<\/li>\n<li>I&#8217;m going to move this so that I can see the cells that have the one, then click specify value.<\/li>\n<li>And for the positive error values, select the three blue cells that have the one in them and click OK, and then close this window.<\/li>\n<\/ul>\n<p>We now have the vertical bars. They don&#8217;t reach all the way up to the top though, because they go up to one and this axis goes up to 1.2.<\/p>\n<ul>\n<li>So click on the axis and on the Layout tab, click Format Selection<\/li>\n<li>For this, we want the minimum to be zero, which we can see in there. We could fix that at zero<\/li>\n<li>Maximum we&#8217;ll fix at one.<\/li>\n<li>We also want to hide the marks that are on that secondary axis.<br \/>\nSo for Major Tick Mark Type, put None, this is already set to None.<\/li>\n<li>The labels we&#8217;ll put to None, and click OK<\/li>\n<\/ul>\n<p>So the secondary axis is still here, but everything is hidden.<\/p>\n<ul>\n<li>We don&#8217;t need this secondary horizontal axis. It&#8217;s causing these vertical lines to appear in the wrong spot because they&#8217;re on that secondary axis.<\/li>\n<li>I will select that and press Delete.<\/li>\n<\/ul>\n<p>Now these vertical lines are on the primary axis and lined up correctly.<\/p>\n<p>We still have this blue line at the bottom.<\/p>\n<ul>\n<li>So I&#8217;m going to select that series and click format selection<\/li>\n<li>The line color, we want No Line, and click Close.<\/li>\n<\/ul>\n<p>So I can see little marks at the bottom of these vertical lines. So there is an X error bar there as well.<\/p>\n<ul>\n<li>If I click and select the X error bars<\/li>\n<li>When they&#8217;re highlighted, I can press the Delete key and get rid of those as well.<\/li>\n<\/ul>\n<h3>Step 6 &#8211; Final Formatting<\/h3>\n<p>And the final thing we&#8217;ll do is a little formatting to make these grid lines look a little nicer. They&#8217;re quite dark and narrowly spaced.<\/p>\n<ul>\n<li>So if I double click on one of those, I get the major grid lines.<\/li>\n<li>I&#8217;ll select solid line, a light grey<\/li>\n<\/ul>\n<p>For the axis, I format that.<\/p>\n<ul>\n<li>Right now, the major unit is 5,000. I can make that 10,000 instead. So they&#8217;re a little better spacing.<\/li>\n<\/ul>\n<p>And finally, to make these dates look better, I&#8217;ll have to go to the source data and format them.<\/p>\n<ul>\n<li>Here are the dates.<\/li>\n<li>On the Home tab, I&#8217;ll click the Dialog Launch button<\/li>\n<li>Number tab, Date<\/li>\n<li>And I&#8217;ll pick this single M which is going to give me just the first letter of the date.<\/li>\n<li>Click OK<\/li>\n<\/ul>\n<p>So now we just have a single letter there and in the chart it&#8217;s been picked up automatically.<\/p>\n<p>It&#8217;s just showing every second one though.<\/p>\n<ul>\n<li>So if I select that axis, and format it<\/li>\n<li>Instead of Automatic Interval, I&#8217;ll click Specify Interval, and set that at one.<\/li>\n<li>When I click close, now there&#8217;s a letter for each month<\/li>\n<\/ul>\n<p>And I&#8217;m going to, as my last thing, just take this grid out of the logo, and our chart is ready<\/p>\n<p>_______________________<\/p>\n<h3>How to Make Panel Chart in Excel &#8211; Video Step-by-Step<\/h3>\n<p><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/panelchartvideo01b.jpg\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"How to Make Panel Chart in Excel - Video Step-by-Step\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/panelchartvideo01b_thumb.jpg\" alt=\"How to Make Panel Chart in Excel - Video Step-by-Step\" width=\"450\" height=\"225\" border=\"0\" \/><\/a><\/p>\n<p>_______________________<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Excel, there&#8217;s no built-in Panel Chart type, but you can create line panel charts, like the one shown below. Watch a video, to see the steps, and there&#8217;s a transcript too, and a link where you can download the sample Excel file.<\/p>\n","protected":false},"author":2,"featured_media":4408,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_kadence_starter_templates_imported_post":false,"footnotes":""},"categories":[6],"tags":[30,31],"class_list":["post-4410","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-videos","tag-excel-charts","tag-panel-charts"],"_links":{"self":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4410","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/comments?post=4410"}],"version-history":[{"count":2,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4410\/revisions"}],"predecessor-version":[{"id":4413,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4410\/revisions\/4413"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media\/4408"}],"wp:attachment":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media?parent=4410"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/categories?post=4410"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/tags?post=4410"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}