Module 7: Visualizing Data in Excel

Module 7 > Visualizing Data in Excel > Lecture

• This time we will concentrate on doing the actual visualizations, adding pivot charts, adding slicers, connecting both.
• We have one of the examples I had before, but actually I’m not going to touch it.
• Now a pivot charts can be standalone, this is not new for 2016 but it was added in the previous version of Excel, the fact that you can add a pivot chart without adding the pivot table first.
• So I can use Insert Pivot Chart, the default, again, is to use the workbook Data Model.
• One good thing is actually, before we continue, it’s always a good idea in creating these kind of visualizations, to turn off a few Excel features like grid lines, the most important one.
• Here’s the Pivot Chart and the Pivot Chart has the field list.
• Actually you see it only as the measures in it, and it shows as sales with a sigma in front.
• Let’s build another one, another Pivot Chart.
• Here, Insert / Pivot Chart once again, OK. Again, make it a little smaller and put it somewhere here, maybe making it the same size.
• Actually I added the manufacturers to the product table so I can choose it from here.
• Now probably what I want is also to add a bunch of slicers so that the person who was using this dashboard can actually control it and slice on different things.
• So now when I click on it, it does affect the chart, but it affects only one chart and not the other.
• If we want to make this slicer connect to the other chart, I will have to go either through the options for the slicer, go to Report Connections, and check the other chart in my worksheet which is currently named Sheet3.
• It’s actually a good idea to go and name the charts and the pivots and give them meaningful names so it will be easier to know what slides are connected to which pivot table or pivot chart.
• I might have more slicers, and some of the slicers might affect all pivot charts, and some of the slicers may affect just a subset of the pivot charts.
• Slicers can also be connected to elements like pivot charts and pivot tables, which actually reside even on other worksheets, not necessarily this one.
• So it could be a whole network of elements like pivot tables, pivot charts and slicers in different worksheets.
• It’s the location in which I can actually see, or define, new hierarchies.
• The calendar table that I created through the automatic tool in Power Pivot already have a hierarchy, name, date hierarchy.
• So hierarchy is a collection of fields, here it’s year, month, and date, that they actually naturally go together.
• So if you define those three together, then when you move them to the pivot they go together.
• As you saw before, we also have drilling experience on the pivot charts.
• In theory I could actually create any arbitrary hierarchy and not just those natural ones which are like geographic one and the time.
• Let’s go back to Excel, and now I want to explain a little bit about the other option to show and to visualize data which is not pivot tables and not pivot charts.
• They created a pivot table, so let me create a new pivot table right in the bottom here, and let me choose Measure.
• So you notice when I drag the Geo to hierarchy, I actually can now immediately start drilling from countries to state and to cities and okay I have this.
• Or actually, you know what, let’s connect this pivot to the existing slicer by year.
• To do that, I can go either to the options as I saw before, of the slicer and connect it to the new pivot.
• I can also go to the pivot, and from the special UI for the pivot I can go to something called Filter Connections and say that I want to connect this pivot to a slicer called Year.
• So once I have this pivot, I can actually go, and again from the analyze area in the ribbon, I can go somewhere called OLAP Tools and I can use something called Convert to Formulas.
• When I did that, actually the area in which the pivot used to be looks almost the same.
• There are seven of them and now I will show you, so I was able to convert the pivot into a bunch of formulas.
• They actually reference the slicer and I can continue to control them with the slicer.
• Now actually I’m writing something that is not, I added something manually which is the function.
• Now we can actually reuse it against the model that we have in Excel.
• If you want to do a little bit of more advanced stuff and not just convert pivot tables to functions, you have to know a little bit of this MDX language.
• Now once I have this, actually in this cell I have the list of all manufacturers.
• I could always do something like cut and paste them somewhere else, which is something it would not be able to do if it was a pivot.
• We saw how to create a few pivot charts, connect them to one or more slicers.
• We also saw some of the basics of creating cube functions either by converting a pivot into functions or formulas, or by doing manual creation of formulas.