How to Use Excel 2010 Pivot Tables and Charts – Part 4

Slices are a new, particular type of Filter within Microsoft Excel 2010, and are useful when working with Pivot functionality.

Slices function like Filters, are created through the Insert Slicers dialog, and correspond to particular data fields. Slices are a preferred type of Filter for presentation and meeting situations, due to their ability to establish attractive interfaces for data analysis.

Numerous edits and customizations can be made to Slices, such as choosing style and color, specifying size and creating caption text. In addition to Slices, Excel 2010 includes Calculated Fields as well as numerous Pivot Table layouts, functionalities which provide an additional level of customization for Pivot Tables and Charts.

Watch the free video here, transcripts for the entire video follow:

Do you need to learn Microsoft Excel? Get 35 hours of Microsoft Excel training – click here.

Video transcripts:

Hello again and welcome back. In this section, we’re going to] start by looking at Slices which are one of the really interesting new features in Excel 2010. Now, I’m going to make a couple of changes to our current table and chart. I’m going to ungroup the branches, so select Eastern, on the Options tab, click on Ungroup, and select Western, on the Options tab, click Ungroup and then I’m also going to introduce department again and department is actually down in the Row Labels. So I think I’m going to put it up in the Column Labels. So I’ve got branch and department, so that’s fine.

Right, what I’m going to do now is to remove the various Filters that are currently applied. So on department, the Filter there, I’m just going to put that as Select All again and branches I will make, it’s all ready all. Date is going to be all of them and that’s fine.

So, having removed the various Filters that were on my data, I’m now back to a situation where I can see all five branches over all, seven months for which I have data. Now what I’m going to do is in order to make space to see what’s going now, I’m going to cut the chart and I’m going to position it further down the page and then I’m going to scroll down there and I’m going to work right here where I’ve got a little bit more space. And what I’m going to do is to, if I click on the Analyze tab, I’m going to remove the Field buttons, so there’s my chart and then on the Insert tab I’m going to Insert and then Slicer. And I come up with the Insert Slicers dialog and I’m going to choose the first four fields for Slices and click on OK and what I get are the four slices, one for each of the fields and I’m now going to position them as well as I can. There we are. Now these Slices, will actually work as Filters. At the moment everything is included, but if I say just wanted the figures for Chicago I could select Chicago alone just by clicking on Chicago.

Now when I make a selection in one of the slices, the button in the top right corner becomes enabled. That’s one where I can actually just click to remove that filter again. It sometimes takes a few moments for it to happen. Remember it’s got over 30,000 lines of data to process every time I click a button here. So we’re back to having all included and of course I can include two. So, for instance, if I wanted breads and flatbreads I’d hold the Control key down and click on flatbreads and I’ve got two selected. Again, it takes a couple of moments for that to take effect.

Now note that when I applied that filter Excel worked out that I only have any data for those departments, breads and flatbreads in April, May, and June and so it narrowed the data down to show just April, May, and June. If I clear that filter again, I’m back to having the full set of six months in place. Now it’s quite often the case that Slices are used in presentations such Dashboards. They’re a lot easier to use than the little Filter controls that we’ve seen elsewhere in Pivot Tables and Pivot Charts. And so they can be a really nice user interface option for applying Filters. There are a number of things you can do to make their presentation more efficient and better looking.

You can, for instance, change the style of the Slices. So let’s say the Branch one, if I select the Branch one you’ll notice it’s selected I get highlight around it, change the Style and Colors for that, then perhaps click on the department one, wait until it’s selected. Bear in mind that particularly when you’re working with large volumes of data, anything to do with Pivot Tables and Pivot Charts can take a few moments to actually execute properly, so when you’re doing this sort of thing wait until the particular Slicer is selected. Let’s have a different Style for that one and the Date one a different Style again. And then the Value one we don’t actually need, I don’t think. So I’m going to click on that, right click, and just say Remove Value. If you have a lot of Slices, you may actually finish up needing to stack them on top of each other and in fact when they are stacked, there’s pretty much a hierarchy. So there are some controls here that enable you to move them around. So, for instance, if I’d got the Department one selected, I could send it backwards and send it backwards again, and I could perhaps bring the Date one forwards and whichever one I’ve got may appear at the front, I can then work with it. Now having got my Slices, the other thing I can do is, I can change their sizes to make better use of the space available to me. So that one, for instance, could be made quite a bit smaller and there’s also a Slicer Settings dialog over here.

Now here I can do things like, for instance, I can change the Caption, I could change it to “Month of Sale” and I can also change the number of columns. So, for instance, if I’ve got a Slicer like this with very short fields, basically just abbreviations of month names, I could say, let’s have two at a time and actually save space by having more on the individual dialogs. So there’s quite a few things you can do there to optimize the space usage of your slices.

Now we’re going to finish this look at Pivot Tables and Pivot Charts in Excel 2010 by a quick review of two or three other important features that I haven’t mentioned so far. One of them is that when you have Pivot Table like this, let’s take this cell here. This is a cell for all of Denver’s sales in June. If you double click on that one cell, Excel creates a new worksheet containing all of the data for that particular cell. So, it’s got a list of all of the transactions for Denver in the month of June which is a pretty useful facility as well.

Another very useful facility is to introduce a Calculated Field. Now we don’t have a particularly good example with this data, but I can illustrate it perfectly adequately. Click anywhere inside the Pivot Table and you get the Pivot Table Tools, two tabs, one of which is Options and within the Options tab there is a Calculations group. Click on Calculations and then choose Fields, Items, and Sets and select Calculated Field. Now this lets us create a Calculated Field. Now I think what I’m going to do is I’m going to invent something here. I’m going to say “Value Inc. Tax” and I’m going to make out that there is an amount called Value Including Tax that is relevant to the Value to each of these sales and it’s calculated by a formula. And the formula is based on the Value Field, so it’s equals Value and then we’ll say times 1.10, so add 10% for tax. Click on OK and that creates a new field called “Value Inc. Tax.”

Now once I’ve done that Value Inc. Tax is now included in both the Pivot Table and the Pivot Chart in pretty much the same place that the Value Field is, so down here under the Field List in the drop zone in Values I’ve got Sum of Value Inc. Tax and Sum of Value. If I look at the Pivot Chart, I can see that wherever I’ve got Sum of Value for each of the months for each of the branches, I’ve now got two total values and then I’ve got in the Pivot Table itself Sum of Value and Sum of Value Inc. Tax. If I wanted to only see Value Inc. Tax, I could actually switch it off here and it automatically gets removed from the values list here. So I’m now looking at something which is Value Included Tax. So you can make up pretty complex formulas here and if you’ve got a Pivot Table with numbers in it where you can do useful calculations and use those calculated values in the Pivot Table, it’s a pretty good facility.

So we’ve looked at Pivot Tables and Pivot Charts and we’ve seen that there are various tabs and ribbons associated with them. So let’s just finally go through a few of the things we haven’t touched on yet. We have looked at some of the options for Pivot Table, including Calculated Fields, as we just did. We can also, if you look at Design under Pivot Table Tools, we can actually change the Style of the Pivot Table in the usual way, so we’ve got a number of different Layouts with and without Borders and Shading and so on there that we can choose from. We can choose the Style Options in terms of whether we want Banded Rows, Banded Columns, and so on. And there are various other Layout Options to do with Grand Totals and so on, which you can experiment with. And then when we have the Pivot Chart selected, as we’ve all ready seen, we have the standard tabs for Design, Layout, and Format and on the Analyze tab we’ve already looked at such features as switching on and off the Field List. There’s also an option to Expand the Fields in the chart, so if I expand the entire field I get an expanded display with Boston department by department. And then, of course, I can collapse that back up again. So, that is it on Pivot Tables and Charts for now.

In the next section, we’re going to start looking at Smart Art Graphics and Shapes. So I’ll see you then.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!