Here’s the chocolate sales data we looked at earlier. Using the techniques discussed in this section, let’s build a simple dashboard to show sales by city and top 5 chocolate flavors. Notice, I’ve already added a tab for the dashboard with some placeholders. This makes it easier to visualize what we’re trying to build. So, the next step is to create the charts we need. First, I’ll create a new pivot table and chart on a new worksheet. This chart is for sales by city, so I’ll name the worksheet to match. Then I’ll add city as a row label, and add sales as a value field. Next, I’ll give the chart a title and remove the legend. I want to display cities from highest sales to lowest, so I’ll sort the pivot table. To clean things up, I’ll hide the field buttons, get rid of the axis, and gridlines, and add data labels. Now to keep the data labels short, I’ll apply a custom format to display numbers in thousands. OK, so we have our first chart, now let’s create the second. I’ll start by duplicating the worksheet, holding down the control key and dragging. Then I’ll name the new worksheet “top flavors” and change the chart title. For this chart, we want to replace city with product. Then, to because the chocolate flavors are rather long, I’ll change the chart type to a bar chart. This leaves more room for the labels. Now I need to filter the chart for the top 5 values. Just right-click product field of the pivot table, and select “top 10” from the filter menu. Then change 10 to 5. OK, so now we have our second chart and we can create our dashboard. I’ll start by copying both charts into the dashboard tab, and resize to fit the placeholders. Now we can insert a slicer. I’ll select one of the charts and insert a slicer for region. Now at this point, the slicer controls only the one chart. To make it control both charts, we need to edit the report connections. To do that, I need to set the slicer to control both pivot tables. And now the slicer controls both charts. You can use this same approach to add additional charts to your dashboard. Once you’ve added the chart, just edit the report connections for the slicer and make sure it controls the pivot table that drives that chart.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
