Skip main navigation

Creating Dashboards with Slicers

.
16.8
Welcome back. In this part, we’ll continue the building of the dashboard that I’m going to present very soon to Lucy, because I think that we are now much closer to what she had in mind. But we still need a few touches, and maybe another pivot. So first of all, I want to be able to philtre the dashboard and to exclude some years, some categories, to just see one of them, and so on. And one very useful tool to do that is called a slicer. So if I’m now in Excel, and I am looking at the–
65.6
first, want to do our first slice, and then we– remember, I promise you to sort out, literally to sort out, the problem with the sort on this bucket, each bucket, because it doesn’t look good. But let’s do it as slices. So if I go, for example, to the country field, and I right click on it, I have an option says Add a Slicer. I can add it to different areas, and slicer is like another area. So added the slicer. And here, the slicer is coming, floating, and I can place it in different areas, in different shapes. I will show you in a moment.
104.6
And by clicking on the different countries in the slicer, you see that this report respond to this clicking and show me a different country. And I can also, by using control-click, select two countries of now. For example, the numbers represent the combination of Australia and Germany. I can add France. Click, control-click on Germany, so now, it’s only Australian friends, and so on. For example, if I wanted to be here, and I want it to be, actually, a totally different shape, I will give it this shape. And when I click on it, you’ll see that it also have its own area in the ribbon.
148.1
I click on the options, and one of the things I can do is I can increase the number of– I did a little bit over– yes, I can increase the number of columns for it. So now, all the columns will actually fit across instead of in different rows. So now I can do the same from here. And I can have multiple slices. Let’s add another slicer that will be by year. If I go here back, I go to the year. Although it’s already active in this chart, I can still add it as a slicer. And when I click, if I click on a year, it will just remove all the other years.
196.2
I will just open all the countries. And I will click on Australia. And I won’t show. See, so both slicers are now actually affecting this pivot table. Now, I want this slicer to affect not just this pivot, but affect all other pivots. So let’s see how we can do it. We can do it from both directions. We can go to a specific slicer, go to the options, go from the options to Report Connections, and see all the other pivot tables, and check on these checkboxes to connect them to connect this slicer to the other pivot tables.
247.9
Notice that I see here the pivot table name, which is a different name that was given by Excel, pivot table 19, 20, and so on. And I also see the worksheet name, so not to be confused by the chart, which was on another worksheet, which is I’m not using today right now. So I have one slice of connected to the three pivot tables. Notice also that if I will rename the pivot tables, and give them meaningful names, it will be easier for me to understand the relationship between the slices and the pivot. So when I click OK now, every click on this chart will actually affect the three pivot tables and the corresponding pivot charts.
295.5
If I open them all, I can see the effect on the three charts. And another way to do it– I told you that it can be done in both ways. I can go to the special area in the ribbon for the pivot, and I can actually go to something called philtre connections. And now I see all of the slicers, and I can say this pivot will be affected by those two slices, for example, the country slicer and the year slicer. So now, also this one will start to affect not only the original one, from which I created the slicer, but also this one. So there’s a many to many relationship between pivots, and the slices.
345.2
For example, if a slicer, like here, shows the years– maybe I don’t want to be affected by year, maybe I do want. So I can decide, if I want to, which pivot table should be affected by which slice. Before we continue with exploring more about slices, let me do a slight deviation, go a little bit to the side, to solve this issue that is bothering us from the beginning, which is the fact that the age group, the buckets, are not sorted correctly. Remember, there is this column here of buckets that I can drill down from to the different actual detailed ages. But I want the greater than 61 to be sorted at the end.
394.9
So this can be solved in this way. I can copy let me copy the values, let’s copy and place them anywhere. It doesn’t matter, it’s just that in a moment they will be gone. So I’m using them here. Here are the values. And I’m going to use the option from the data ribbon of sort, as if I want to sort this list. This is sort in column P. I want to sort by the values. And the order– I want to order them by a custom list. Custom list is something that exists in Excel. By default, you see all the sleeves that are already there– Sunday, Monday, Tuesday, January, February, March. They are, by default, already there.
445.2
But what I can do now, I can actually create a list that will have the values that we want to be sorted. So this is like telling Excel, every time you see this series of values, sort them by this way, and not by the regular alphabetical order, because there is a special meaning. And so 40 to 50, 51 to 60, and greater than 61. This is it. I add this list, click OK. Now, when I finish, it’s actually sorted correctly. It also has another effect, by the way. If I started to write anywhere– right, I started with this letter.
501.9
First option, or first item, in the list, and I start dragging this thing around, you see that, actually, it’s continuing to create the rest of the list. It’s the same as if I start to write here– January, then by default, except because it has a list based on the names of the months, if I drag them out all the way to December, I drag down, and I get the list of January. So now, I created my own custom list. Now, if I go here, and I want to say that I want to sort them by alphabetical order, A to Z, actually, A to Z now have a special meaning.
548.7
And that’s why the first item is still less than 20, but the last item is greater than 61. So I wanted just to teach you this little nice trick that you can use in different aspects in different places in Excel to manage custom lists. So we sorted this one. In the next, we’ll continue to build the chart, the dashboard that we have here, by adding another– let me move this for now. And I want it here, with mirror. I want to create one last chart and pivot combination. I want it to be by year and months. So insert the pivot based on sales data.
604.4
I want to have years of this time on rows. And I want the revenue, as always. And I want to use months as a detail level, to years. But I don’t have the year ready, so I can go back to the table to add a column for months. So I can go anywhere, can be here, doesn’t have to be, but next to the year. Add a new table column. And now I’m going to be using– I wanted the months to show January, February, and so on, so I can use the text function to use the date column, and to use the format string of MMMM.
654.7
And I will rename this to be called Month. OK. I’m pretty happy with that. I’m back to my dashboard, and I’m looking for the monies to appear here. But it doesn’t. The reason it doesn’t appear here is that the pivot does not reflect the content of the table right away. You still need to do something in order to tell the pivot that there is new content– either new rows, or in this case, a new column. So I will have to go to the pivot, right-click on it, and click Refresh. It actually reads the data from the table again, and now I have a months column. And I can drag it just below years, and see.
699.9
It can sort it correctly– it doesn’t come sorted by alphabetical order, but the right order of the months, because we saw already that there was a custom list standard in Excel for the months. So that’s how I created the months column, and I edited, and it sorted, and I clicked Refresh in order to see it. See you on the next time.

Last week, we discovered how to organise our data in dashboards. In this step, we return to dashboards and explore additional features for building dashboards.

Filtering Dashboards

At times you might want to visualise different sets of values from within your Excel table. A useful Excel tool that you can use in your pivot tables and charts is Slicer. Slicers are visual filters that allow you to filter your data by clicking on the type of data you want.

You can create slicers to use in a specific pivot or connect your slicers to other pivots. When a slicer is connected to multiple pivots, each pivot will be affected if you filter one of the pivots with the slicer.

Sorting Data

When you create a pivot table or chart, the pivot automatically sorts the data. The pivot does not recognise the logical order of your data, for example, Monday, Tuesday, Wednesday, etc.

If you have reusable data that is stored in a logical order, you can create a custom list that your future pivots would recognise and order accordingly.

Join the discussion

To gain a better understanding of dashboards, let’s do some research. Dashboards are commonly grouped into three different categories. List these three categories and give a short description of each.

Use the discussion section below and let us know your thoughts. Try to respond to at least one other post and once you’re happy with your contribution, click the Mark as Complete button to check the step off, then you can move to the next step.

This article is from the free online

How to Use Microsoft Excel for Data Analysis

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education