IntroductionWelcome| 00:04 | Hi, I'm Curt Frye.
Welcome to Excel 2010.
| | 00:07 | In this course I'll show you how to
use PivotTables to make sense of your
| | 00:11 | organization's data.
| | 00:12 | I'll begin by showing you how to
create a PivotTable from data already in
| | 00:16 | your Excel workbooks.
| | 00:18 | Then using that knowledge as a base,
I'll demonstrate how to create PivotTables
| | 00:22 | using data from an external source.
| | 00:23 | We will enhance your ability to analyze
your data by showing how to reorganize
| | 00:28 | or pivot a PivotTable.
| | 00:30 | After you've organize your
PivotTable's data, you can sort and
| | 00:33 | filter its contents to develop
useful insights into your business.
| | 00:37 | I'll also show you how to create
conditional formats that highlight subsets of
| | 00:41 | your data, to control your PivotTable
using macros, and to summarize huge data
| | 00:46 | sets using the new PowerPivot add-in.
| | 00:49 | In short, I'll you how to summarize and
analyze data using one of Excel's most
| | 00:53 | powerful and overlooked features.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you are a premium member of the
lynda.com Online Training Library or if
| | 00:04 | you're watching this tutorial on a DVD,
you can have access to the exercise files
| | 00:09 | used throughout this course.
| | 00:10 | The exercise files are laid out within
a series of folders and I have them in an
| | 00:16 | Exercise Files folder on my desktop.
| | 00:19 | So if I double click, you can see that
I have a folder for each chapter and if I
| | 00:23 | open a folder, say the one for
Chapter 4, then you'll see that I have the
| | 00:28 | files for that chapter.
| | 00:29 | So you should have an easy time finding them.
| | 00:32 | If you're a monthly subscriber or annual
subscriber to lynda.com, you don't
| | 00:36 | have access to the exercise files, but you can
follow along from scratch with your own assets.
| | 00:40 | Let's get going.
| | Collapse this transcript |
|
|
1. Creating and Pivoting PivotTablesIntroducing PivotTables| 00:00 | Before I get into the details of how
to create and manipulate PivotTables,
| | 00:04 | I would like to demonstrate a few of
the ways a PivotTable helps you analyze
| | 00:07 | your worksheet data.
| | 00:09 | When you look at your PivotTable, it
looks a lot like a regular worksheet with
| | 00:12 | rows and columns of data,
labels, subtotals, and grand totals.
| | 00:17 | The difference is that a PivotTable
allows you to rearrange your data
| | 00:21 | dynamically within the worksheet.
| | 00:23 | If you click a cell inside of a
PivotTable, then you will see that Excel
| | 00:28 | displays the Options and
Design tabs on the Ribbon.
| | 00:33 | These contextual tabs allow you to
manipulate your PivotTable either by
| | 00:37 | changing a style, which you can do on
the Design tab, or on the Options tab
| | 00:43 | refreshing its data,
changing its data source, and so on.
| | 00:46 | What's really exciting about PivotTables
is that you can change the structure of
| | 00:50 | your data to emphasize
different aspects of that data.
| | 00:53 | PivotTables are divided
into several different areas.
| | 00:57 | First, you have the PivotTable Field
List which allows you to manipulate the
| | 01:01 | PivotTable and within the field list,
you have the number of fields which are
| | 01:06 | data types that you can use.
| | 01:08 | So you have the Year in this case, the
Quarter, which is not displayed, Month,
| | 01:12 | Company name and Revenue.
| | 01:14 | And you can see those fields
reflected in the body of the report.
| | 01:18 | You change the arrangement of the
fields within your PivotTable using the
| | 01:22 | field area, which is down here at the
bottom of the PivotTable Field List task pane.
| | 01:26 | Say for example, you can have your
Company data as the column labels and you can
| | 01:32 | see that reflected here, in the body
of the PivotTable with FirmA and FirmB.
| | 01:37 | Then the row labels would be the year,
in this case 2009, and then the month,
| | 01:42 | starting with January and
going down through December.
| | 01:46 | If you want to change the PivotTable's
layout, you can do that by dragging a
| | 01:49 | field name from one area to another.
| | 01:52 | So for example, let's say that you
wanted to lay out your data so that it was
| | 01:56 | Company, Year, and Month
| | 01:58 | and each of the Company, Year, and
Month labels were in the row area.
| | 02:02 | To make that pivot, you would drag the
Company field name to the top of the Row
| | 02:07 | Labels area and then when you release
the mouse button the PivotTable updates
| | 02:12 | the data to reflect your new organization.
| | 02:15 | So now, we have all the results for
FirmA in 2009 by month and then below the
| | 02:21 | year 2009 we go to 2010 and again by
Month, and lower down in the PivotTable
| | 02:27 | you'll see the same result for FirmB.
| | 02:30 | So I will scroll down using my mouse
wheel and below the grand total for FirmA
| | 02:36 | you see the same data for FirmB.
| | 02:39 | You can also filter the data in
your PivotTable to focus your analysis.
| | 02:43 | So let's say, for example, as I scroll
back up to the top that I wanted only to
| | 02:47 | display the data for FirmB.
| | 02:50 | To create a filter, I can into the
PivotTable Field List and then click the
| | 02:55 | Company fields down arrow
and then create the filter.
| | 02:59 | In this case, I only want to show the
data for FirmB, so I will clear the check
| | 03:04 | box next to FirmA, and then click OK.
| | 03:09 | When I do, Excel updates the PivotTable so
that only the data for FirmB is displayed.
| | 03:14 | If I want to clear that filter, then
I can go to the PivotTable Field List,
| | 03:19 | click the Company fields bar, and then
click Clear Filter From "Company". When I do,
| | 03:26 | Excel restores the data.
| | 03:28 | You can also change the
look of your PivotTable.
| | 03:30 | You can do that by adding or changing a style.
| | 03:34 | So for example, if I click the
Design tab I can select any one of the
| | 03:38 | available PivotTable styles.
| | 03:40 | So if I click the more button in the
gallery then, I can click any of the
| | 03:44 | available styles and I will go down and I'll
click one that's a bit darker for contrast.
| | 03:49 | I hope his brief
demonstration has given you a feel for
| | 03:52 | PivotTables capabilities.
| | 03:54 | In the remaining movies in this course
are going to much more detail on how to
| | 03:57 | create and manipulate
PivotTables and the data they contain.
| | Collapse this transcript |
| Formatting data for use in a PivotTable| 00:00 | PivotTables enable you to summarize
and reorganize your data dynamically.
| | 00:04 | But you can't summarize just any data set.
| | 00:07 | In most cases, your data needs
to be laid out as a data list.
| | 00:11 | So then the question becomes
what do I mean by a data list?
| | 00:14 | In this case, a data list is
something that you might think of as a table.
| | 00:18 | with distinct columns of data and each
one of those columns having a header.
| | 00:23 | In this data list my column headers are
Year, Quarter, Month, Company, and Revenue.
| | 00:29 | And then within the list, we have a
series of rows, each of which contains a
| | 00:33 | value for each of those headers.
| | 00:35 | In the first row for example, we
have the Year 2009, Quarter 1, Month of
| | 00:39 | January, the Company is FirmA, and then
we have the Revenue for that Month.
| | 00:44 | To be able to use the data inside of a
PivotTable we need to make sure there are no blank rows.
| | 00:49 | So if I scroll down in this list, you'll see
that from A1 to E49 that there are no blank rows.
| | 00:57 | It's okay to have blank cells.
| | 00:58 | That'll just be included
in the PivotTable as blanks.
| | 01:02 | Rather than leave your data as a data
list, in Excel 2007 and now in Excel 2010
| | 01:08 | you can create what are called Excel tables.
| | 01:11 | And Excel tables allow you to do a lot
more with your data than you could with
| | 01:15 | just a normal data list.
| | 01:17 | To create an Excel table, click any cell
in your data list and then on the Home
| | 01:22 | tab of the ribbon in the Styles group
click Format as Table and then select a
| | 01:26 | format for your table.
| | 01:27 | In this case, I'll just use
the simple black-and-white style.
| | 01:31 | Then in the Format As Table dialog
box verify that Excel has correctly
| | 01:37 | identified where the data for your table resides.
| | 01:39 | In this case, it is A1 through E49
and then Excel has detected that your
| | 01:44 | table has headers. Those are the Year,
Quarter, Month, Company and Revenue
| | 01:49 | values in A1 through E1.
| | 01:51 | So everything looks correct and I can click OK.
| | 01:55 | Excel tables are the best source for
PivotTable data because Excel refers to
| | 02:00 | the table as a whole
regardless of how many rows it contains
| | 02:03 | and not just a specific group of
cells that you might have to change later.
| | 02:08 | I'll cover the differences between Excel
tables and data list as sources,in the
| | 02:12 | movie entitled "Updating and refreshing
PivotTable data sources," which you can
| | 02:16 | find it later in this chapter.
| | 02:18 | If you do use a data source that's not
in Excel table, make sure that the column
| | 02:22 | headers are formatted
differently than the data.
| | 02:25 | In the data list that I
used here to create this table,
| | 02:27 | the values Year, Quarter, Month,
Company and Revenue were all centered and
| | 02:31 | displayed in bold text.
| | 02:33 | While we're here, I would like to
show you a few Excel table features.
| | 02:37 | Those are the total row and filtering.
| | 02:40 | So let's say, for example, that I
wanted to filter this table so that it only
| | 02:45 | displayed results for the month of January.
| | 02:47 | To do that, you click the Month columns
filter arrow and then select the values
| | 02:53 | that you want to display.
| | 02:54 | In this case, I want to display
just results for the month of January.
| | 02:59 | So to do that, I will clear the Select
All checkbox, which clears all of the
| | 03:04 | checkboxes, meaning no values would be
displayed if I were to apply this filter,
| | 03:08 | then I'll scroll down, check
the box for January, and click OK.
| | 03:14 | When I do, the table displays
only the values for January.
| | 03:18 | To remove the filter, I can click
the filter arrow again and click Clear
| | 03:23 | Filter From "Month".
| | 03:25 | You can also add what's called a total row.
| | 03:28 | If I scroll down to the bottom of the
table and I'll do that using my mouse's
| | 03:31 | scroll wheel, you'll see that
the data just ends in Row 49.
| | 03:36 | However, if I add a total row and to do
that I click any cell in the table and
| | 03:42 | then on the Table Tools design tab
on the Ribbon, check the Total Row box.
| | 03:49 | When I do, Excel adds a total row to
the table and the value here, 3683, is the
| | 03:55 | total of all the values in the Revenue column.
| | 03:58 | And if you want to use a different
summary operation, you can click any cell
| | 04:03 | that contains a total. Click the down
arrow that appears and you can select any
| | 04:08 | one of these other functions.
| | 04:10 | So for example, if I wanted to change
the Summary to Average, then Excel will
| | 04:14 | create a new formula, finding the
average of all the values in that column.
| | 04:19 | Once you have your source data arranged
in a data list, you can create a PivotTable.
| | 04:23 | There is one other case, which I will
cover in the movie entitled "Consolidating
| | 04:27 | data from multiple sources," which
you can find later in this chapter.
| | Collapse this transcript |
| Creating a PivotTable| 00:00 | PivotTables are powerful and
flexible Excel analysis tools.
| | 00:04 | A PivotTable lets you arrange, sort, and
filter a data set on the fly, so you can
| | 00:08 | analyze it from several different
perspectives with a minimum of effort.
| | 00:12 | In this movie, I'll show you how to
create a PivotTable from a data list that's
| | 00:15 | stored in the same workbook.
| | 00:17 | To begin you need to have a data list
such as the Excel table I have here.
| | 00:23 | We have five columns Year, Quarter, Month,
Company and Revenue and each of the rows
| | 00:27 | inside of this Excel table contains
a value for each one of those columns.
| | 00:31 | To create the PivotTable, click the
Insert tab on the ribbon and then click
| | 00:36 | the PivotTable button.
| | 00:38 | When you do, Excel displays the
Create PivotTable dialog box.
| | 00:42 | First you should verify that Excel
has identified your data correctly and in
| | 00:47 | this case it's Table1 and you can see
the marquee outline around that selection
| | 00:52 | inside of the workbook.
| | 00:53 | So we know everything is right, and then
you need to decide where you want the
| | 00:57 | PivotTable report to be placed.
| | 00:59 | I always place my reports on the new
worksheet and you can see that the New
| | 01:03 | Worksheet option has been selected by
default, because that's the most common choice.
| | 01:07 | However, if you did want to create your
PivotTable on an existing worksheet, say
| | 01:12 | for example starting at cell G2 on this
worksheet then, you can select the Existing
| | 01:18 | Worksheet option and then click the
collapse dialog box button, and I want to
| | 01:24 | click cell G2 as my target, and then
click the expand dialog box button and you
| | 01:31 | see that location is identified
within the Create PivotTable dialog box.
| | 01:36 | So that's how you do it if you want
to create it on an existing worksheet.
| | 01:39 | I'll go back to my original choice of
creating on a New Workshee, to avoid
| | 01:43 | crowding and then click OK.
| | 01:46 | When I do, Excel creates a New
Worksheet and displays a blank PivotTable and
| | 01:51 | also displays the PivotTable Field List.
| | 01:54 | If for any reason, you don't see the
PivotTable Field List task pane over here
| | 01:58 | on the right, then you can click any
cell inside the PivotTable and then on the
| | 02:03 | Options contextual tab on the Ribbon
you can click the Field List button.
| | 02:08 | So clicking the Field List button
while the field list is displayed will hide it
| | 02:12 | and clicking the button while
the field list is hidden will display.
| | 02:17 | Now, you can move your data into
place to create the PivotTable.
| | 02:20 | So, let's say for example that I
wanted to have my Year as my color labels.
| | 02:27 | So in other words along
the top of the PivotTable.
| | 02:30 | I will have the Years, in this case
2009 and 2010, and then I will have the
| | 02:35 | Company field provide the
values for the row labels.
| | 02:38 | So I can drag the Company field header
down to the Row Labels area and now I
| | 02:43 | have 2009, 2010, FirmA, and FirmB.
| | 02:48 | Now I can add the Revenue field to the
Values area and that provides the values
| | 02:54 | for the body of the PivotTable.
| | 02:56 | If I want to add another field
to the report then I can do that.
| | 03:00 | So let's say for example that I want to add
Month below Company in the Row Labels area.
| | 03:05 | So now my PivotTable has data for FirmA
| | 03:07 | and then each Month and then along the
columns we have 2009 and 2010, and then
| | 03:13 | we have FirmB with the data
laid out exactly the same way.
| | 03:17 | I always make one change to
the default PivotTable layout.
| | 03:21 | When Excel creates a PivotTable, it
puts the subtotals for each first level
| | 03:24 | grouping at the top of the group.
| | 03:27 | So for example here, we have FirmA and
then in 2009 the total was 963. In 2010
| | 03:34 | it was 961 with a grand total of 1924.
| | 03:37 | I personally prefer the subtotal
positions to be at the bottom of the group.
| | 03:42 | To make that change, click any cell in
the PivotTable and then on the design
| | 03:46 | contextual tab click the Subtotals
button and then click Show all Subtotals
| | 03:52 | at Bottom of Group.
| | 03:53 | It's simply a matter of personal
preference, but I like two things about this.
| | 03:58 | First I would like to see the
individual values before I see the total.
| | 04:02 | And secondly, I like that Excel puts a
blank row beside the name FirmB instead
| | 04:08 | of having all the data run together.
| | 04:10 | So to me, visually this is a
little easier to understand.
| | 04:14 | Now that you've learned how to create
a PivotTable, you can move forward and
| | 04:17 | discover how to rearrange your data dynamically.
| | Collapse this transcript |
| Pivoting a PivotTable| 00:00 | Excel lets you summarize your data quickly,
| | 00:02 | but the real power of the pivot table comes
when you want to rearrange your data dynamically.
| | 00:07 | A task that would take
several minutes of done by hand
| | 00:09 | takes just a few seconds when you
summarize your data in a PivotTable.
| | 00:13 | The row headers and column headers provide
the basic structure for your PivotTable.
| | 00:18 | In this case my rows are the
months January through December
| | 00:22 | and the columns are the two companies,
FirmA and FirmB, for which I have data.
| | 00:26 | If I wanted to, I could change my data layout so that
the companies provided the headers for the rows
| | 00:33 | and the months provided the
values for the column labels.
| | 00:36 | To do that I can go into the
PivotTable Field List task pane.
| | 00:40 | And then in the lower part of the field list,
| | 00:43 | I can change the position of the labels
| | 00:45 | by dragging them from one area to another.
| | 00:47 | So for example I can drag Company
| | 00:50 | to the Row Labels area and Month
| | 00:54 | to the Column Labels area.
| | 00:55 | As I desired, the companies
provide the values for the rows
| | 00:59 | and the months provide
the values for the columns.
| | 01:02 | If I want to divide my data a little more to provide
more detail as opposed to the summary that I have right now,
| | 01:07 | then I can add the second rows or columns
headers to create the subdivisions. So for example,
| | 01:13 | let's return to the PivotTable to
its original position where I have Month
| | 01:17 | in the row area and the
Company in the column area.
| | 01:21 | Let's say that I want to divide my
months into quarters. To do that,
| | 01:25 | I would drag the Quarter field
| | 01:29 | down to the top of the Row Labels area
| | 01:31 | and you can see a thim bluish gray line
that appears just above the Months header
| | 01:37 | here in the Row Labels area.
| | 01:39 | If I were to drag Quarter below Month, then the
line appears below it indicating its position,
| | 01:45 | but I'll put Quarter above Month and when I release the left
mouse button then you see that the data is arranged first by Quarter
| | 01:52 | and then by Month.
| | 01:54 | If I were to reverse the
order of those two labels,
| | 01:56 | by putting Quarter below Month.
| | 01:59 | then we would have January, which belongs in quarter 1,
and February, which belongs in quarter 1, and so on.
| | 02:05 | That arrangement doesn't make
much sense, so I'll put it back
| | 02:09 | and have the months arranged by quarter
for January, February, March, Quarter 1,
| | 02:13 | April, May, June, Quarter 2, and so on.
| | 02:16 | If I want I can add another layer.
| | 02:19 | In this case I'll use the Year
field and I'll put it above Quarter
| | 02:22 | in the Row Labels area and
| | 02:24 | then again, you can see the
line when it will be dropped.
| | 02:27 | So here we have Quarter 1 for 2009
| | 02:29 | Quarter 2, and so on,
| | 02:31 | and if I use my mouse
scroll wheel to move down,
| | 02:35 | you'll see the same data for 2010.
| | 02:38 | There's one other thing about pivoting
a PivotTable that I'd like to show you
| | 02:41 | and that is how to defer the
layout updates when you're pivoting.
| | 02:45 | So let's say that they you have an extremely large dataset.
It could be tens of thousands or hundreds of thousands of rows,
| | 02:51 | or it could be a dataset you're connecting to over a
network and the network connection might be slow for some reason.
| | 02:57 | If that's the case then you can do what's
called deferring to the layout update. To do that,
| | 03:03 | at the bottom of the PivotTable Field List,
check of the Deferral Layout Update box
| | 03:07 | and then make your changes. So let's say
for example that I want to take out Month
| | 03:13 | and then also take out Quarter.
| | 03:17 | Now you notice that the PivotTable hasn't
changed to reflect my update. When I'm ready
| | 03:22 | and I've made all the changes I want to make,
| | 03:24 | click the Update button
| | 03:26 | and Excel updates the PivotTable.
| | 03:28 | And I will drag the scroll bar up
| | 03:30 | so that you can see the
PivotTable in its new state,
| | 03:33 | which is Year providing in the value for the row
labels and Company providing the value for the column labels.
| | 03:39 | Changing a PivotTable's arrangement shifts the data's
emphasis, which enables you to examine the data from different
| | 03:44 | perspectives quickly and easily.
| | Collapse this transcript |
| Configuring a PivotTable| 00:00 | Then you create a PivotTable in Excel
| | 00:02 | Excel shows the number of
PivotTable elements by default.
| | 00:05 | You can show, hide, or in some cases move these elements
to change the PivotTable configuration to your liking.
| | 00:11 | For example one element of the
PivotTable that's associated with the Ribbon
| | 00:14 | are the options and the
design of contextual tabs.
| | 00:18 | If you want to hide those contextual tabs,
| | 00:20 | you can click any cell
outside of the PivotTable,
| | 00:23 | and when you do in Excel
takes those tabs off the Ribbon.
| | 00:27 | If you want to bring them back,
just click any cell in the PivotTable
| | 00:30 | and the contextual tabs reappear.
| | 00:32 | You can also show or hide the
PivotTable Field List task pane.
| | 00:36 | So let's say for example that you have
raised your PivotTable so that is fairly wide.
| | 00:41 | So let's say that I have Company
| | 00:44 | in the Row Labels area,
| | 00:46 | and then I have Year and then Months
| | 00:50 | in the Column Labels area and that means that
my PivotTable is too wide to fit on the screen.
| | 00:55 | If I want to hide the PivotTable Field List
| | 00:57 | to allow more the data to appear on the screen,
| | 01:01 | then I can go into the PivotTable Field List and click
the close button to close it and display most of the data.
| | 01:07 | And if I want to display all of it
than I can just change my zoom level.
| | 01:10 | To do that, you get to the View Shortcuts toolbar
at the bottom right corner of the program window
| | 01:16 | and click the Zoom Out button.
| | 01:18 | So just click it a couple of times.
| | 01:22 | And now all of your data
appears on the worksheet.
| | 01:25 | Now, if you want to bring
the field list task pane back,
| | 01:28 | you can go up to the Options contextual tab
| | 01:31 | and click the Field List button.
| | 01:33 | And now you can pivot your PivotTable back
into its original configuration. So we'll move
| | 01:39 | Months to the bottom of
the row area, Years above it,
| | 01:43 | and Company over to the side, and then increase
or zoom level back to 130% by clicking five times.
| | 01:50 | If you want, you can also show and hide the
expand and contract buttons, which are here
| | 01:55 | to the left of each of the row labels.
| | 01:58 | So here I have the year 2009,
| | 02:01 | and if I want to collapse all 2009, in
other words not displayed within the worksheet,
| | 02:06 | then I can click the
collapse button, which looks like a minus,
| | 02:10 | and collapse that data.
| | 02:12 | If I want to expand it back out, you'll notice
that the button has changed to look like a plus sign.
| | 02:17 | Now it's an expand button. So I can click it
| | 02:20 | and display all the data under 2009.
| | 02:23 | If you want to get rid of the
expand and contract buttons entirely,
| | 02:28 | then on the Options contextual tab,
| | 02:30 | you can click the expand and
contract or the plus/minus buttons,
| | 02:35 | and when you do Excel hides them.
| | 02:37 | If you want to bring them back,
| | 02:39 | just click the button again and they reappear.
| | 02:42 | if you want, you can also hide the field
headers, which are the values of row labels
| | 02:47 | and the column labels. Those are
currently in cells B3 and A4.
| | 02:52 | If you want to hide that text,
| | 02:54 | on the Options tab you can
click the Field Headers button.
| | 02:57 | and now there's a little more room
inside of your PivotTable outline.
| | 03:01 | If you want to bring those values back,
| | 03:03 | you'll just click the Field Headers
button again. And they reappear.
| | 03:07 | The PivotTable Field List task pane,
| | 03:09 | the expanding contract buttons, and field
headers are all useful PivotTable components.
| | 03:13 | But you don't have to use them.
| | 03:15 | If you want toremove them from
your PivotTable's current view
| | 03:18 | you can do so by using the controls on
the Options contextual tab on the Ribbon.
| | Collapse this transcript |
| Connecting to an external data source| 00:00 | Excel enables you to build PivotTables
from data found both inside and outside
| | 00:04 | the workbook where you create the PivotTable.
| | 00:06 | If you want to create a PivotTable from
a data source you either can't or don't
| | 00:09 | want to import into Excel directly,
| | 00:12 | you can create a connection to that
data source and work with it in place.
| | 00:15 | So let's say that you want to create a
PivotTable using data from another Excel workbook.
| | 00:21 | To do that, you go to the Insert
tab and click the PivotTable button.
| | 00:27 | Then in the Create PivotTable dialog
box you select the Use an external data
| | 00:31 | source option and then click Choose Connection.
| | 00:36 | Then in the Existing Connections
dialog box, if the connection or the data
| | 00:40 | source that you want already appears,
then you can click it and then click Open.
| | 00:44 | In this case, I haven't created any
connections yet so I will click the Browse
| | 00:48 | for More button and this displays
a version of the Open dialog box.
| | 00:53 | In this case, it's named Select Data Source.
| | 00:56 | The data that I want is in my Exercise
Files folder and that is on my Desktop
| | 01:01 | and it's in Exercise Files Chapter 01
and the file that I'm using for this
| | 01:07 | exercise is called OutsideSource.
| | 01:10 | So, I can click that file and then click Open.
| | 01:14 | Then the Select Table dialog box
appears and the Select Table dialog box shows
| | 01:18 | you the different data sources that you
can use within that target data source
| | 01:23 | to create your PivotTable.
| | 01:25 | In this case, there's only one and that
is named Sheet2 so it is selected.
| | 01:29 | You can see it's highlighted here in blue.
And then I happen to know from working
| | 01:33 | with this data before that the first
row of the data does in fact contain
| | 01:36 | column headers, so I can leave that box
selected and then, I can click OK, and
| | 01:42 | Excel accepts that choice and sends me
back to the Create PivotTable dialog box.
| | 01:47 | Now I want to create a
PivotTable on an existing worksheet.
| | 01:51 | So in this case, I have Sheet1, cell
A1, which is selected here, and when I'm
| | 01:57 | ready I can click OK.
| | 01:58 | After I click OK, Excel creates the
PivotTable on the Sheet1 and now I can
| | 02:04 | create the PivotTable as I would normally.
| | 02:06 | So let's say for example that I want
to have my Company field in the Column
| | 02:10 | Labels area and then the Year and
Quarter in the Row Labels area and then the
| | 02:19 | Revenue in the values area, and
it's a PivotTable just like any other
| | 02:23 | PivotTable. You can pivot it,
filter it, add styles to it, and so on.
| | 02:28 | One important thing to note is that
if you use an Excel workbook as your
| | 02:32 | data source, you can't open that
workbook while the workbook that contains
| | 02:36 | the PivotTable is open.
| | 02:37 | So in other words, if I display my
Excise Files folder and I double-click
| | 02:42 | Outside Source to try to open it,
then Excel displays an Error dialog box
| | 02:47 | indicating that the files in
use and that it can't open it.
| | 02:50 | In this case, the error is at the file
format or file extension is not valid,
| | 02:54 | but that's not the real error.
| | 02:56 | In this case, the file is
just in use by another file.
| | 03:00 | So we can click OK to close that
dialog box and now if I close the external
| | 03:06 | workbook and I'll do that by pressing
Ctrl+W and I will not save my work and
| | 03:12 | then,go back to my Excise Files folder,
if I double-click Outside Source then
| | 03:18 | the file opens normally.
| | 03:20 | If possible you should always draw the
data for a PivotTable from the original
| | 03:24 | source so that any changes to that
source are reflected in the PivotTable that
| | 03:28 | use to analyze your data.
| | Collapse this transcript |
| Connecting to an Access database| 00:00 | If you run a small business, you
might maintain important business data in
| | 00:03 | an Access database.
| | 00:05 | If you do, you can create a PivotTable
based on the original database table,
| | 00:09 | instead of importing the data into Excel.
| | 00:12 | In this movie, I'll show
you how to do just that.
| | 00:15 | To create a PivotTable, first you click
the Insert tab on the ribbon, and then
| | 00:20 | click the PivotTable button. Then in
the Create PivotTable dialog box, select
| | 00:26 | the Use an external data source
option, and then click Choose Connection.
| | 00:31 | If the connection we want doesn't
appear in the existing connections list, so
| | 00:36 | we'll click Browse for More and then
navigate to my Exercise Files folder, which
| | 00:40 | is on my Desktop in the Exercise
Files folder and in Chapter 01.
| | 00:47 | And the database that I want to use is
called PTDatabase, so I'll click it and click Open.
| | 00:55 | Now we can create the PivotTable report
on an existing worksheet and right now
| | 01:01 | the location is a Sheet1 cell A1 and that
is the highlighted cell here. That's fine.
| | 01:07 | So we can click OK and create a link
to that data and create a PivotTable
| | 01:12 | based on that data.
| | 01:14 | And now I can create the
PivotTable as I would normally.
| | 01:17 | So in this case, I will have Company
providing the values for the Row Labels
| | 01:23 | area and then we'll put Quarter over in
the Column Labels area and then we'll
| | 01:29 | fill in the values or the sum of the
sales for each Company for each of those
| | 01:33 | Quarters in the Values area.
| | 01:37 | Creating a PivotTable by connecting to
an Access database table saves you the
| | 01:40 | trouble of importing the
data into your workbook.
| | 01:43 | However, you must be able to connect to
the database either as a file on a same
| | 01:47 | computer or over a network for your
PivotTable have the most current data.
| | 01:51 | If you think maintaining your
connection to the database will be an issue,
| | 01:54 | then you should import the data into
Excel and create your PivotTable from
| | 01:58 | that imported data.
| | 02:00 | I should also point out that the
techniques I showed you in this movie also
| | 02:03 | let you connect to an online analytical
processing or OLAP cube, such as those
| | 02:08 | created by SQL Server Analysis Services.
| | Collapse this transcript |
| Consolidating data from multiple sources| 00:00 | Most of the time you'll create
a PivotTable from a data list or Excel table.
| | 00:04 | But you can also summarize multiple
data ranges using a PivotTable, if the data
| | 00:08 | is formatted appropriately.
| | 00:10 | To consolidate a series of data ranges
into a PivotTable the ranges must be
| | 00:14 | laid out in cross tabular format
and have exactly the same structures.
| | 00:19 | In a sample file here, I have three worksheets:
| | 00:21 | SupportCalls, OrderCalls, and ReturnCalls.
| | 00:24 | And the data on the SupportCalls
worksheet has a series of distribution regions,
| | 00:29 | North, South, East and West, along the Row
Label area and then the Years 2007,
| | 00:35 | 2008, 2009 and 2010 along the top,
providing the values for the Column Labels.
| | 00:41 | And then here in cell B3
you'll see that I have Region-Year.
| | 00:47 | Now, normally if you were to
create a cross-tabular table like this, you would
| | 00:51 | put Region in cell B3 and Year in cell C2.
| | 00:56 | The reason you don't do that in
this case is because this region that contains
| | 01:01 | the data and all of the labels
must be perfectly rectangular.
| | 01:05 | In other words, there must be
data in each one of those cells.
| | 01:08 | So if I were to have Region in cell
B3 and Year in cell C2, then the cell would
| | 01:16 | be blank as would cells D2 through
F2 and the data layout would not be perfectly
| | 01:22 | rectangular, so the technique
I am about to show you would not work.
| | 01:26 | And the data I have on my other
worksheet, OrderCalls, has the same layout and
| | 01:32 | ReturnCalls has the same layout as well.
| | 01:35 | So now, I'll switch back to the first worksheet,
| | 01:37 | SupportCalls, and continue on.
| | 01:40 | If you want to create a PivotTable
using this data, you need to do something a
| | 01:43 | little bit different than
| | 01:44 | you would when you create a PivotTable
from a data list or an Excel table.
| | 01:49 | To run the PivotTable and PivotChart Wizard,
you need to hold down the Alt key,
| | 01:53 | press D, and then press P.
| | 01:57 | When you do, you launch the
PivotTable and PivotChart Wizard.
| | 02:00 | On the first page of the wizard,
select the Multiple consolidation ranges option
| | 02:05 | and then click Next.
| | 02:07 | Then on the second page select the
I will create the page fields option and click Next.
| | 02:14 | And now on the next page you can select
the worksheet ranges that you want to consolidate.
| | 02:19 | Just start with the cursor flashing
in the Range box, click the collapse
| | 02:24 | dialog box button, and then on the first
worksheet select the cells that contain the data.
| | 02:29 | So we have B3 through F7. Click the
expand dialog box button and then click
| | 02:38 | Add and now we do the same for the other worksheets.
| | 02:42 | So click the collapse dialog
box button again, change to the OrderCalls
| | 02:47 | worksheet by clicking the sheet tab,
and then select B3 through F7. Expand dialog.
| | 02:55 | Click Add to add that range to the
list of ranges and then finally click the
| | 03:00 | collapse dialog box button again,
click ReturnCalls, select that data, expand
| | 03:07 | the dialog, and click Add.
| | 03:09 | In this case, we don't want any page fields,
which are also called report filter fields.
| | 03:14 | So we'll leave that option
selected to 0 and then click Next.
| | 03:20 | On the final page of the wizard,
verify that the New worksheet option is
| | 03:24 | selected and click Finish.
| | 03:27 | When we do, the data appears in the new PivotTable.
| | 03:30 | Now notice over here in the PivotTable
Field List, that the fields have the
| | 03:35 | names Row, Column and Value.
| | 03:37 | Value is okay, but I'd like to change the other two.
| | 03:40 | To do that I click any cell in the
PivotTable with a value for the field that I want.
| | 03:45 | In this case, we have North and
that is part of the row group.
| | 03:51 | If I want to change the name of
the active field, then on the Options
| | 03:55 | contextual tab in the Active Field
group select the name in the Active Field box
| | 03:59 | and change the name.
| | 04:01 | In this case, I'll change it to Region
and press Enter, and you'll notice that
| | 04:08 | the name of the field changed
over in the PivotTable Field List.
| | 04:11 | And now I can do the same for year.
| | 04:13 | I'll click any cell that contains
a year of Value and then on the Options
| | 04:17 | tab in the Active Field box, I can
type Year, press Enter, and the change takes place.
| | 04:24 | You probably won't create many PivotTables
by consolidating multiple data ranges.
| | 04:29 | But it's nice to know the capability
is available if you're working with legacy
| | 04:32 | workbooks that have their data
laid out in cross tabular formats.
| | Collapse this transcript |
| Updating and refreshing PivotTable data sources| 00:00 | Companies generate new sales,
budget, and operations data all the time.
| | 00:04 | So PivotTable data sources rarely remain static.
| | 00:08 | In this movie I'll show you how to
manage your data connection to ensure your
| | 00:12 | PivotTable contains the
most recent data available.
| | 00:15 | You can display your PivotTable's data
connection by clicking any cell in the
| | 00:19 | PivotTable, as I've done here, and then
on the Options contextual tab click the
| | 00:24 | Change Data Source button.
| | 00:27 | When you do, the Change PivotTable
Data Source dialog box appears and in this
| | 00:31 | case you can see that this PivotTable draws
its data from a table with the name of Firm Data.
| | 00:38 | If you want to change the data
source you can select a new data source
| | 00:41 | within the Workbook.
| | 00:42 | But notice that the Use an
external data source option is grayed out.
| | 00:48 | The reason that's the case is because
you can change a data source to another
| | 00:52 | one of the same kind.
| | 00:54 | That is, you can swap one internal data
source for another or one external data
| | 00:58 | source for another but you can't change
from one type of connection to another.
| | 01:02 | If you want to use external data for a
PivotTable when you've already created
| | 01:06 | it using internal data, then you will
need to create a new PivotTable instead of
| | 01:11 | editing the current one.
| | 01:12 | Now let's go back to the PivotTable that
I displayed earlier, so I'll click Cancel.
| | 01:17 | This PivotTable draws its data from
an Excel table and one of the major
| | 01:21 | benefits of using an Excel table as
your data source is that when you add a row
| | 01:26 | to the table and refresh the data
connection, the new data appears in the
| | 01:29 | PivotTable automatically.
| | 01:31 | So the data for this PivotTable is on
the Table Data Worksheet and if I scroll
| | 01:37 | down using my scroll wheel to the last
row then I can add another row to the
| | 01:42 | table and have that data
appear in the PivotTable.
| | 01:44 | So let's say that I click this last
cell and press Tab to add a new row to the
| | 01:49 | table and then we'll put in data from
2011 so it will be 2011 Quarter 1, Month
| | 01:57 | of January for FirmA, and then for
revenue we'll just make it 200 so it stands
| | 02:06 | out from all the other data and press Enter.
| | 02:09 | Now when I go back to the FrontTable
Worksheet you don't see any change in the
| | 02:13 | PivotTable yet and I'll scroll down to
the bottom of 2010 so you can see the
| | 02:17 | change when it occurs.
| | 02:18 | Now there is new data in the table.
| | 02:20 | So if I click the Options contextual
tab and then click the Refresh button,
| | 02:25 | the data that I just added to the Excel
table appears in the PivotTable automatically.
| | 02:30 | By comparison, if you build your
PivotTable based on a data list as opposed to
| | 02:34 | an Excel table, then that data does not
appear when you refresh the connection.
| | 02:39 | So I have another
PivotTable on the FromList worksheet.
| | 02:44 | It's exactly the same table based on
the similar data and this data is on the
| | 02:48 | ListData worksheet, and you'll notice
that this is simply a worksheet list.
| | 02:53 | It hasn't been turned into an Excel table.
| | 02:56 | If I use my scroll wheel to scroll
down and then in the next available row,
| | 03:01 | type in the exact same data we had
before, 2011 Quarter 1, for January, FirmA,
| | 03:10 | and then Revenue of 200 and press Enter.
| | 03:14 | If I go back to the PivotTable on
the FrontList worksheet and I'll scroll
| | 03:19 | down using my scroll wheel again,
then on the Options contextual tab click
| | 03:24 | Refresh, nothing happens.
| | 03:27 | What I need to do is
update the data source manually.
| | 03:30 | So to do that, again on the Options tab
I can click the Change Data Source button
| | 03:35 | and then you'll see the
definition here in the Table/Range box.
| | 03:40 | I added another row and
that increments in number.
| | 03:43 | So now instead of having the table or range
end on cell E49 it actually ends on cell E50.
| | 03:51 | So I'll Backspace over 49, type in 50,
click OK, and now you see that the
| | 03:56 | PivotTable updates reflect the new definition.
| | 03:59 | Whenever I view a PivotTable I always
click the Refresh button to include any
| | 04:03 | updates to the data source.
| | 04:05 | If you create PivotTables based on
Excel tables the new data will appear
| | 04:09 | automatically when you refresh.
| | 04:11 | However, if your PivotTable draws
its data from a data list, you should
| | 04:14 | examine the source data list to
identify any new rows and edit the data source
| | 04:19 | to reflect the change.
| | Collapse this transcript |
| Managing PivotTables| 00:00 | PivotTables behave a bit differently
than other Excel workbook elements, so
| | 00:03 | you'll need to use a few slightly
different techniques to select, move, copy and
| | 00:08 | delete them within your workbook.
| | 00:10 | You can rename a PivotTable by clicking
any cell on the PivotTable and then on
| | 00:15 | the Options contractual tab on the
Ribbon in the PivotTable group at the far
| | 00:19 | left corner of the ribbon, you can
type a new name for the PivotTable.
| | 00:24 | So in this case, I will call it
Firm Revenue and press Enter.
| | 00:30 | This name will help you and other
users interpret formulas that draw on
| | 00:34 | this PivotTable's data.
| | 00:35 | If you want to clear all the fields,
filters, and formats from PivotTable, you
| | 00:40 | can do so again on the Options
contractual tab. Click the Clear button and
| | 00:45 | then click Clear All.
| | 00:48 | Doing so doesn't delete the PivotTable;
instead what it does is remove all the
| | 00:52 | fields from the Row area, Column area,
Report Filter area and the Values area,
| | 00:58 | so that you can rebuild it from the ground up.
| | 01:00 | I'll put my fields back. So we have
Year, Month, Company and Revenue.
| | 01:10 | If you want to create a second
PivotTable from the same data range, you can do
| | 01:13 | that by copying and pasting the PivotTable.
| | 01:17 | So again, with any PivotTable cell
selected, on the Options contextual tab
| | 01:22 | click the Select button and
then click Entire PivotTable.
| | 01:27 | Now press Ctrl+C to copy the PivotTable
and then select where you want to paste it,
| | 01:34 | in this case we'll go to the
PTPaste worksheet. Cell A1 is selected.
| | 01:39 | Now I will press Ctrl+V and when I
do, Excel pastes in the PivotTable.
| | 01:45 | Now the two PivotTables work off the same
data, but they are independent of each other.
| | 01:49 | So for example, if I were to take the
Month field out of the Row labels area on
| | 01:56 | this version of the PivotTable, and then
switch back to the original PivotTable,
| | 02:00 | which is on the PT worksheet, you'll
see that this PivotTable is still in its
| | 02:05 | original configuration and I'll press
the Escape key to remove the selection
| | 02:10 | marquee from around the PivotTable.
| | 02:12 | If you want to copy a particular
PivotTable arrangement to another worksheet
| | 02:16 | but instead of creating a new PivotTable
do so as a data, then you can do that
| | 02:21 | by selecting the entire PivotTable and
again you do that by clicking any cell
| | 02:25 | in the PivotTable and on the Options tab
click Select > Entire PivotTable and then
| | 02:33 | press Ctrl+C. That copies the
PivotTable to the clipboard.
| | 02:38 | Now go to another worksheet and in
this case I will go to DataPaste and then
| | 02:43 | with cell A1 selected, on the Home tab
I'll click the Paste button's down arrow
| | 02:49 | and then click the first
icon in the paste values row.
| | 02:55 | When I do, Excel pastes in the contents
of the PivotTable, but this time instead
| | 02:59 | of creating a PivotTable,
it only paste the data.
| | 03:02 | If you want to delete a PivotTable,
which I could do by switching back to the
| | 03:06 | PTPaste worksheet and then
clicking any cell in this PivotTable, I can
| | 03:11 | delete the PivotTable by clicking
Options then Select > Entire PivotTable and
| | 03:18 | pressing the Delete key.
| | 03:21 | With these techniques in mind
you'll be able to manage your
| | 03:24 | PivotTables effectively.
| | Collapse this transcript |
|
|
2. Summarizing PivotTable DataAdding, removing, and positioning subtotals and grand totals| 00:00 | Excel PivotTables display your data in
as much or as little details you prefer.
| | 00:05 | Pivoting and filtering the PivotTable
changes how the individual data elements
| | 00:08 | are displayed, but you can also position
the subtotals and grand totals for rows
| | 00:12 | and columns to summarize your data as desired.
| | 00:15 | For example, in this PivotTable I
currently have both Subtotals and Grand
| | 00:19 | Totals turned on so you'll see that
for FirmA the subtotal for the year 2009
| | 00:25 | was 963, for FirmB the same subtotal
for that year was 924, and we have a grand
| | 00:31 | total for that year of 1887, and
you'll see a subtotal for each of the firms
| | 00:36 | and the grand total for 2010.
| | 00:38 | And then at the bottom of the
PivotTable, which I displayed by scrolling down
| | 00:42 | using my mouse's scroll wheel, you'll see
that we have a grand total for FirmA of
| | 00:46 | 1924, FirmB of 1729, and a
grand total for both of 3683.
| | 00:53 | Now, I'd like to show you how to
change how subtotals and grand totals are
| | 00:56 | displayed, so I'll scroll back up so
you can see my change more clearly.
| | 01:00 | Now after clicking any cell inside
the PivotTable I can go up to the Design
| | 01:05 | contextual tab on the Ribbon and then
in the Layout group click the Subtotals
| | 01:09 | button and then I can select the
option that I want for subtotals.
| | 01:14 | At present I'm showing all subtotals at
the top of the group, so I'll change it
| | 01:18 | now to the bottom by clicking Show
all Subtotals at Bottom of Group.
| | 01:23 | And when I do, Excel simply moves the
subtotal to the bottom of the group, below
| | 01:27 | the individual data lines.
| | 01:29 | So I have the year and then
individual months and their totals, the grand
| | 01:34 | total for both firms for a given
month, and then the subtotals and grand
| | 01:38 | totals as displayed here.
| | 01:41 | If you want to turn off subtotals
entirely then you can click the Subtotals
| | 01:45 | button and click Do Not Show Subtotals.
| | 01:48 | And putting subtotals at the top or
bottom the group is simply a matter of
| | 01:52 | personal preference.
| | 01:53 | Personally I prefer to have subtotals
at the bottom of the group because I like
| | 01:57 | to see the individual data points and
see what they add out to at the end, but
| | 02:01 | if you prefer to see the
total and then the details,
| | 02:03 | perfectly fine. Just select the option you want.
| | 02:06 | Grand totals on the other hand
summarize all of the values in a row or column.
| | 02:10 | You can't reposition them but you can turn
them on or off for rows or columns or both.
| | 02:16 | So let's say that I want
to turn them off for rows.
| | 02:19 | Just click any cell in the PivotTable
and then on the Design contextual tab
| | 02:24 | click Grand Totals and then I'm going to
turn them on for columns only, in other
| | 02:29 | words from them off for rows.
| | 02:31 | So On for Columns Only, and you'll see
that the grand totals appear at the bottom.
| | 02:35 | Now if I scroll back up and then click
Grand Totals and turn them On for Rows
| | 02:41 | Only then you'll see the grand totals
for each of the rows and then if you
| | 02:46 | scroll down you see that they've been
turned off for columns. Or if you prefer,
| | 02:50 | you can click the Grand Totals
button and turn them off entirely.
| | 02:57 | To bring them back, just go to Grand
Totals and then On for Rows and Columns.
| | 03:02 | If you change the summary operation
that a PivotTable uses to display its data,
| | 03:06 | then Excel applies that change to
the Subtotal and Grand Total fields.
| | 03:10 | I'll show you how to do that in
another movie later on in this chapter.
| | 03:14 | Subtotals and grand totals provide easy-
to-read summaries of the values in your
| | 03:17 | PivotTable's rows and columns.
| | 03:19 | If you find those extra rows and
columns are a distraction, or if you just
| | 03:22 | want to emphasize the values in the
body of your data area, you can hide them quite easily.
| | Collapse this transcript |
| Changing the PivotTable data field summary operation| 00:00 | Most of the data that you summarize
using PivotTables will have some sort of
| | 00:03 | financial and personal aspect, such as
sales or hours worked, which means that
| | 00:08 | you'll usually want to find the
sum of the values in the list.
| | 00:11 | You're not limited to
adding values together though.
| | 00:13 | You can choose from several summary
calculations and also have the ability to
| | 00:17 | change the type of value shown in the data area.
| | 00:20 | For example, you can express individual
values as a percentage of the column or
| | 00:24 | row total or compare values
to those of the previous year.
| | 00:28 | If you want to change the summary
calculation used in a PivotTable,
| | 00:31 | right-click any cell in the
PivotTable's data area and that means any cell that
| | 00:36 | contains an individual value.
| | 00:38 | So, for example, not a grand total or
a subtotal and not any of the labels
| | 00:42 | either here in the columns or here for the rows.
| | 00:45 | So instead, right-click any cell in the
data area, point to Summarize Values By,
| | 00:51 | and then select the
operation that you want to use.
| | 00:54 | In this case, I'll change it to Average.
| | 00:57 | Now note that the values in the body
of the data area didn't appear to change
| | 01:01 | and that's because each individual
value is its own average. There is only one
| | 01:05 | value for 2008 in the month of
January so the average will be 67.
| | 01:10 | However, here in the Grand Total area
you'll see that the values have changed.
| | 01:14 | So for example, let's look at March.
| | 01:16 | We have the value of 128 and then add
the value of 34, which is 162, and then
| | 01:22 | add the value of 138 in cell D8.
| | 01:26 | That's a total of 300.
| | 01:27 | There are three values, the average of
which is 100, which is displayed here in
| | 01:32 | the Grand Total column.
| | 01:34 | In addition to changing the summary
operation you can change how the values are
| | 01:38 | displayed within the body of the PivotTable.
| | 01:40 | To do that, first I will switch the
aummary operation back to Sum, so I'll
| | 01:45 | right-click a cell point to
Summarize Values By and then click Sum.
| | 01:49 | And now I'm going to change
how the values are calculated.
| | 01:53 | To do that right-click any cell in the
data area and then point to Show Values As
| | 01:58 | and then select the type of
operation that you want to do.
| | 02:01 | In this case, I want to compare the
values in 2009 to those of 2008 and the
| | 02:06 | values in 2010 to those in 2009.
| | 02:09 | In other words, I want to compare each
year to its previous year. Click % Of.
| | 02:16 | Doing so displays a dialog box that you can
use to set your Base field and the Base Item.
| | 02:22 | So in this case, I want the Base field
to be Year, so I am comparing year on year,
| | 02:27 | and I want to compare each year
to its previous year and the Base Item is
| | 02:32 | already selected as (previous) so I
can click OK, and Excel updates the
| | 02:36 | PivotTable with those calculations.
| | 02:38 | So for example, the value from 2009
was 191% roughly of the same value for
| | 02:45 | January 2008 and in 2010 that value
was only 65% of the value from 2009.
| | 02:53 | Now let's say that I want to
compare 2009 and 2010 to 2008.
| | 02:58 | To do that, I can right-click any cell
in the data area, point to Show Values As
| | 03:03 | and then again click % Of
to display the dialog box.
| | 03:08 | We're still comparing Years but now I
can click the Base Item box's down arrow
| | 03:13 | and instead of clicking
(previous) I can click 2008.
| | 03:17 | And when I click OK, the PivotTable
updates its calculations and now 2009 and
| | 03:22 | 2010 are both compared to the
values for the same month from 2008.
| | 03:26 | There is one other summary operation I'd
like to show you and that is creating a
| | 03:30 | running total in each column.
| | 03:33 | To do that right-click any cell in the
data area, point to Show Values As, and
| | 03:38 | then click Running Total In.
| | 03:41 | Then in the dialog box you can select
your Base field and in this case I do
| | 03:45 | want it to be Month, and you can click
OK and the PivotTable now contains the
| | 03:50 | running total for each year by month.
| | 03:53 | So for example, January of 2008 had
a value of 67 and then at the end of
| | 03:59 | February we had a total of 136. So the
value for February isn't 136 but the sum
| | 04:05 | of all revenue for January
and February is 136 and so on.
| | 04:11 | Now notice that there is not a
subtotal at the bottom of this group and the
| | 04:15 | reason is because that subtotal actually
appears in December, because of that is
| | 04:20 | the running total of all 12
months in an individual year.
| | 04:23 | You should take some time to experiment
with the summary operations and settings
| | 04:27 | available for use in the data area.
| | 04:28 | You'll probably find one or more
operations other than addition that provide
| | 04:32 | meaningful information about your data.
| | Collapse this transcript |
| Summarizing more than one data field| 00:00 | When most users visualize a PivotTable, they
see it with a single data set in the data area.
| | 00:05 | While that's certainly the most
common data area configuration, you can
| | 00:09 | summarize more than one data field at a time.
| | 00:11 | So let's say for example that I have a
monthly revenue worksheet for two firms
| | 00:15 | and I already have the
Revenue field in the data area.
| | 00:18 | So if you look in the PivotTable Field
List, you can see the Revenue field there
| | 00:23 | and then you can also see the same
data in the body of the PivotTable.
| | 00:27 | Now let's say that I want to create a
PivotTable that displays the revenue data
| | 00:31 | for each month and I also want to
show the same data but as a comparison.
| | 00:36 | Say for example, if I wanted to compare the
values between FirmA and FirmB as a percentage.
| | 00:42 | To do that I can add a second copy of
the Revenue field to my data area, and to
| | 00:47 | do that click any cell on the PivotTable and
then on the PivotTable Field List drag
| | 00:52 | the Revenue field header
down to the Values area.
| | 00:55 | Again, when I drag it down, even though
the Revenue field is already there, if I
| | 01:00 | drag the Revenue field header down to
the Values area and release, then Excel
| | 01:05 | creates a second copy of the Revenue
field in that area, and you'll notice that
| | 01:10 | the PivotTable changes.
| | 01:12 | I have Sum of Revenue, and
then I have Sum of Revenue2.
| | 01:16 | And then also in the Column Labels area
in the PivotTable Field List task pane,
| | 01:21 | you'll see that I have a Sigma values header.
| | 01:24 | What that means is that for the column
labels to create room for that second
| | 01:28 | data set, they had to
create a second values column.
| | 01:33 | Having two copies of the same data
summarized in the same way doesn't
| | 01:36 | really help us, so what we need to do is
change how one copy of that data is summarized.
| | 01:41 | To do that you can right-click any
cell in either of the two data areas.
| | 01:46 | So in this case I have Sum of Revenue2.
I'll right-click a cell in one of those
| | 01:51 | columns, point to Show Values As, and
then I'll do percentage of, and then we'll
| | 01:57 | make it a percentage of the previous month.
| | 02:00 | So right now our Base field is Month.
| | 02:02 | That's correct, and then I'll change the
Base Item to (previous) and then click OK.
| | 02:10 | So the value in January is 100% because
we are comparing it to itself, but then
| | 02:15 | the value in each successive month is
compared as a percentage to the value
| | 02:20 | from the previous month.
| | 02:21 | So for example, 69 is 102.99% of 67,
128 is 185.51% of 69, and so on.
| | 02:31 | Now you don't have to have two copies
of the same field in your data area.
| | 02:36 | So for example, if I were to remove the
second Sum of Revenue field, dragging
| | 02:41 | it back to the Field area, I can also
add another numerical value such as, for
| | 02:46 | example, the number of sales and
that data is in the Sales column.
| | 02:51 | So I'll drag it down to the Values area.
| | 02:54 | And now I have the revenue for each month,
and then I also have the number of sales.
| | 02:58 | So for example, I could now determine
that I had $67,000 of revenue based on
| | 03:04 | this number of sales.
| | 03:06 | That information gives me a lot more
insight into my sales performance and also
| | 03:10 | it allows me to perform other
calculations that I'll show you how to do in a
| | 03:13 | movie later on in this chapter.
| | Collapse this transcript |
| Creating a calculated field| 00:00 | PivotTables enable you to analyze
your data efficiently, but some questions
| | 00:04 | can only be answered by performing calculations
on the data used to create your PivotTable.
| | 00:08 | To perform that type of analysis
you can create custom fields that summarize
| | 00:12 | PivotTable data using a formula. Without
calculated fields, you'd have to copy
| | 00:16 | the data from your PivotTable, paste
it on another worksheet, and create the
| | 00:20 | formulas there and that's a pain.
| | 00:22 | Now you have to dig a little to
find the button you click to create a calculated
| | 00:26 | field, but you can find it by clicking
the Options contextual tab on the Ribbon
| | 00:31 | and then in the Calculations group
click Fields, Items & Sets, and the item you
| | 00:37 | want on this menu is Calculated Field.
| | 00:40 | When you click Calculated Field the
Insert Calculated Field dialog box appears.
| | 00:45 | Now you can type a name for your
calculation and in this case I will call it
| | 00:49 | Average Sale, and now I can create the formula.
| | 00:55 | To create a formula I'll click in the
Formula box and backspace to delete the
| | 01:00 | zero and now I can insert fields and
any mathematical operators that I want.
| | 01:05 | So I want to find the average value
of each sale, so I'm going to divide total
| | 01:10 | revenue per month by the number of sales per month.
| | 01:14 | So for that I'll click the Revenue
field here in the Fields list, and click
| | 01:18 | Insert field and then type a forward
slash, which means a divide in Excel, and
| | 01:25 | then I'll click Sales Count,
Insert Field, and then click OK.
| | 01:32 | When I do, Excel adds the Average
Sale field to the body of the PivotTable.
| | 01:37 | Now even though I don't know the
number of sales for a given month, I know
| | 01:40 | that in January I had a total revenue
of 67 and the average revenue per sale
| | 01:46 | was 9.57 approximately.
| | 01:48 | You should note that even though the
newly calculated field appears here in the
| | 01:53 | PivotTable Field List it hasn't
been added to the original data source.
| | 01:58 | So for example, this PivotTable was
built off of data found on Sheet2 in this
| | 02:03 | worksheet, and you'll see that the only
data I have are for the Year, Quarter,
| | 02:07 | Month Company, Revenue, and Sales Count.
| | 02:10 | The new field, Average Sales, only
exists within the PivotTable's data cache,
| | 02:15 | which is its temporary memory.
| | 02:17 | So you can see that calculated fields
extend the type of analysis that you
| | 02:21 | can perform in Excel, making PivotTables even
more useful for your operational analysis.
| | Collapse this transcript |
| Grouping PivotTable fields| 00:00 | When you summarize your data using a
PivotTable, Excel organizes the data based
| | 00:04 | on the order of the fields and the
Columns area and in the Row area.
| | 00:08 | You can then use the expand and
collapse controls at each organizational level
| | 00:12 | to hide the details for those rows.
| | 00:14 | So for example in this PivotTable, the Row
area is arranged by Company, Year and Month.
| | 00:20 | If I wanted to hide all of the details
for the months within the year 2009 for
| | 00:24 | example, then I could click the Collapse
control here beside 2009, and when I do
| | 00:31 | I just see the summary for 2009
as opposed to the individual months.
| | 00:35 | If I click the expand control,
then the details reappear.
| | 00:40 | Now the months represent the lowest
level of organization so you can't
| | 00:43 | hide them individually.
| | 00:45 | As the PivotTable stands now, you can
only hide the monthly results by hiding
| | 00:48 | every month for an entire year.
| | 00:50 | But if you do want to show or hide groups
of months, you can do so by creating a group.
| | 00:54 | A group is a user-defined set of rows
that you can expand or collapse as desired.
| | 00:59 | To create a group, select one set of
PivotTable rows that contain the values you
| | 01:04 | want to include in the group.
| | 01:05 | So for example, let's say that I
wanted to select February, March, and April.
| | 01:10 | I move the mouse pointer, which you can
see here, over the left edge of one of
| | 01:15 | the row labels. When it changes to a
right pointing black arrow, then I know
| | 01:19 | that it is in position to select rows.
| | 01:22 | So I will click the left mouse
button and drag down, and I will select
| | 01:24 | February through April.
| | 01:27 | Now you will notice that Excel is
selecting February through April 2009 and it
| | 01:32 | was also selecting
February through April of 2010.
| | 01:37 | So regardless of which months you
select, the selection would be the same for
| | 01:41 | every year within the PivotTable.
| | 01:43 | Now with those of PivotTable rows
selected go to the Options contextual tab and
| | 01:48 | then in the Group group, click Group
Selection. When you do, Excel creates a
| | 01:55 | custom group for those months.
| | 01:57 | If you want to hide the details of that
group and just see a summary, then you
| | 02:00 | can click the collapse control beside
the name Group1 and hide those details.
| | 02:07 | If you want to bring them back, you can
click the expand control to bring them back.
| | 02:12 | Now the name Group1 isn't very
descriptive, so if you want to edit that name,
| | 02:17 | you can click the cell that contains
the name Group1 and then select the value
| | 02:21 | on the formula bar and it
changes. So for example,
| | 02:25 | it might be Sale Month, and press Enter.
When you do, you change the value of the label.
| | 02:33 | Now note that normally double-
clicking a cell would allow you to edit the
| | 02:37 | value within the cell, but in this case
double-clicking the cell that contains
| | 02:40 | the label Sale Months would collapse the group
and double-clicking it again would expand it.
| | 02:47 | If you want to remove a group from the
PivotTable, in other words to ungroup
| | 02:51 | the selection, you can click the header
cell and then on the Options contextual
| | 02:55 | tab, in the Group group, click on Ungroup.
| | 03:00 | Grouping PivotTable fields enables
you to hide or display rows of data that
| | 03:03 | belong together without creating
a new field in your data source.
| | 03:07 | Be sure to change the name of the
group to something more descriptive than
| | 03:10 | Group1, and when you're done with your analysis,
| | 03:13 | consider ungrouping the fields to
make the PivotTable's layout simpler.
| | Collapse this transcript |
| Using PivotTable data in a formula| 00:00 | When you create a formula, it's usually
a good idea to draw the formula's values
| | 00:04 | from the original data source.
| | 00:06 | That way if the source changes,
your formulas result will update the next time
| | 00:10 | Excel recalculates your workbook.
| | 00:12 | But what you do if your PivotTable
contains data drawn from an outside source
| | 00:15 | and you can't get at the original tables?
| | 00:18 | In that case you can refer to a cell
within the PivotTable using a GetPivotData formula.
| | 00:23 | Let's say that you want to use the
data in a PivotTable cell in your
| | 00:27 | formula and for that we will make
it the value in C6, which is the value
| | 00:33 | for January 2009 for FirmB.
| | 00:37 | To create this formula, you start
typing another formula such as Sum, so click
| | 00:42 | any cell outside the PivotTable and
then type equal and then name of the
| | 00:46 | function, Sum, and then a left
parenthesis and now you click the cell that you
| | 00:52 | want to use the data from inside the PivotTable.
| | 00:55 | So in this case I will click cell C6
and you see that Excel creates a GetPivotData function,
| | 01:01 | which has a
bunch of information inside of it.
| | 01:04 | This information refers to the cell
that I just clicked inside the PivotTable
| | 01:07 | and I will walk you through step by
step to show you what it's doing to
| | 01:10 | identify that cell.
| | 01:12 | The first thing it's doing is
indicating the field that contains the value,.
| | 01:17 | That field name is Revenue and you
can see that the sum of Revenue field is
| | 01:23 | what's summarized within the body of
the PivotTable, so that's the first part.
| | 01:27 | The second part is the cell A3.
| | 01:29 | A3 is the first cell at the top
left corner of the PivotTable.
| | 01:33 | Year indicates the Year field and 2009 is
the year that is applied to the cell in C6.
| | 01:40 | So as you can see, the year is 2009
and cell C6 clearly falls within that
| | 01:46 | part of the PivotTable.
| | 01:48 | Next the month is January and you can
see that here it is in fact for January,
| | 01:54 | and then finally you have the Company,
which are the columns, and it is FirmB,
| | 01:59 | and cell C6 is under FirmB
so it is that company's data.
| | 02:04 | Now you can close out the formula
by typing a right parenthesis and
| | 02:08 | then pressing Enter.
| | 02:10 | Normally you wouldn't create such a
simple formula as I have here. Instead you
| | 02:13 | would use your GetPivotData function
as part of a larger calculation, but I
| | 02:18 | wanted to keep it simple
just for demonstration purposes.
| | 02:21 | So I'll click a cell in the
PivotTable to bring the PivotTable Field List back.
| | 02:26 | When you pivot the PivotTable, as long
as the cell that contains the data you
| | 02:29 | want still appears within the
PivotTable, then the formula won't change.
| | 02:33 | So let's say that I put Company below
Year and Month in the Row Labels area.
| | 02:39 | When I do that, even though the
arrangement of the PivotTable has changed the
| | 02:43 | value of the formula remains the same,
because the value is still displayed
| | 02:46 | within the PivotTable. And you can see
that value here. It's currently in cell B7.
| | 02:53 | And if were to pivot the PivotTable
back by putting Company back in the
| | 02:57 | Column Labels area, the value goes back to C6
instead of B7 and the formula remains the same.
| | 03:04 | Even though you can pivot your
PivotTable and have the formula retain its value,
| | 03:08 | if you hide one of the fields that the
GetPivotData formula uses to identify
| | 03:13 | the proper cell within the PivotTable,
then the formula will generate an error.
| | 03:17 | So let's say for example
| | 03:19 | that instead of having Company within
the Column Labels or the Row Labels area
| | 03:23 | that I remove it from the PivotTable.
When I do, the formula in cell F1 which
| | 03:29 | refers to the PivotTable cell generates
a reference error and that's because it
| | 03:34 | no longer has the information that it
needs to identify the proper cell within
| | 03:38 | the PivotTable, and also the value for
January 2009 for FirmB no longer exists
| | 03:45 | within this particular
range within the PivotTable.
| | 03:48 | You can see that all we have is the sum
of revenue for both companies and it's
| | 03:52 | not broken out by individual company.
| | 03:54 | If I drag the Company field header
back to the Column Labels area, then the
| | 03:58 | formula gets restored to its proper
order and it can identify the cell it
| | 04:02 | needs to return a value.
| | 04:04 | Now if you find that clicking a
PivotTable cell doesn't generate a GetPivotData formula,
| | 04:09 | then it's possible you or
someone else might have turned off the
| | 04:11 | option that enables you to do that.
| | 04:13 | To make sure you can use PivotTable
data in the formula, click the File tab,
| | 04:18 | then click Options, and then on the
Formulas tab of the Excel Options dialog box,
| | 04:24 | make sure that the
GetPivotData functions for PivotTable References
| | 04:29 | check box is selected.
| | 04:31 | When it is, you can create your
GetPivotData functions the way that I did in this movie.
| | 04:37 | The GetPivotData function makes it easy to
summarize PivotTable data in your worksheets.
| | 04:41 | If it's not practical to draw the
data from the original source, you could
| | 04:44 | always use the PivotTable reference instead.
| | Collapse this transcript |
| Drilling down to the underlying data| 00:00 | One often overlooked aspect of
PivotTables is that they make it easier for you
| | 00:04 | to locate individual
data points. As an example,
| | 00:07 | suppose you have a PivotTable that
summarizes sales data over a series of years.
| | 00:11 | If you want to display the data row
that provides the value displayed in a
| | 00:15 | PivotTable cell, double-clicking that
cell causes Excel to drill down into the
| | 00:19 | data source and create a new worksheet
that contains an Excel table with a copy
| | 00:23 | of the appropriate row. So for example,
| | 00:25 | let's say that I wanted to see the row
in the data source for this PivotTable
| | 00:30 | that provided the value for
FirmA 2009 in the month of March.
| | 00:36 | That's the cell that I
selected inside the PivotTable.
| | 00:39 | To do that, I double-click the cell
and Excel creates a new worksheet and it
| | 00:44 | shows an Excel table with the entire row
providing the data for that cell in the PivotTable.
| | 00:49 | Now this drill-down capability only
works for one cell at a time, but it is
| | 00:54 | possible to drill down into a data
source even if the PivotTable draws its data
| | 00:58 | from an external source, such as a
database table or another workbook.
| | 01:02 | You can do that because Excel draws
the row it displays from the program's
| | 01:06 | internal representation of the data
source, which is called the Pivot Cache,
| | 01:10 | instead of the source table itself.
| | 01:12 | After you have displayed the row
providing value for PivotTable cell, you can
| | 01:16 | press Ctrl+Z to try to delete the
worksheet. When Excel indicates that data may
| | 01:22 | exist in the sheet that you have
selected for deletion, then click the Delete
| | 01:26 | button in the dialog box and
Excel gets rid of the sheet.
| | 01:30 | Drilling down into the original data
source provides context for the data that
| | 01:33 | appears in your PivotTables.
| | 01:35 | Rather than finding the right row
among hundreds or even thousands of rows in
| | 01:38 | the source table, you can display just
the one you want by double-clicking the
| | 01:42 | cell you want to investigate.
| | Collapse this transcript |
|
|
3. Sorting and Filtering PivotTable DataSorting PivotTable data| 00:00 | When you summarize your data in the
PivotTable you might want to display all of
| | 00:03 | the rows in the PivotTable, but
change the order in which they appear.
| | 00:07 | So let's say for example
| | 00:08 | that I have PivotTable here that
contains sales data for two companies, broken
| | 00:14 | down by month for the years 2009 and 2010.
| | 00:17 | The current PivotTable arrangement
organizes the data first by year and the
| | 00:22 | years are sorted in numerical order,
so 2009, 2010, and then the months are
| | 00:27 | sorted January, February, March and so on,
in order of the months as they occur
| | 00:32 | in the year, as opposed
to an alphabetical order.
| | 00:34 | But let's say that I wanted to sort the data
inside the PivotTable from highest to lowest.
| | 00:39 | So let's say for example
| | 00:41 | that I wanted to find the months where FirmB
had the highest sales within the year 2009.
| | 00:48 | To do that I click any cell in the
field and in the column that I want to
| | 00:52 | sort and then on the Options
contextual tab of the ribbon in the Sort and
| | 00:56 | Filter group, I can click one of
three buttons. I can click either Sort
| | 01:01 | Smallest to Largest or Sort Largest to
Smallest or I can create a custom sort
| | 01:06 | by clicking the Sort button.
| | 01:07 | In this case, I'll click the
Sort Largest to Smallest button.
| | 01:10 | It goes from Z to A. Click that
and you see that Excel has sorted the
| | 01:15 | PivotTable data within each year based
on the months for FirmB that have the
| | 01:20 | highest sales values.
| | 01:22 | If I wanted to I could do the same
thing for FirmA. So click any cell in the
| | 01:26 | FirmA column and then let's say that I
want to have the smallest values on top.
| | 01:30 | I can click these Sort Smallest to
Largest button and sort in that direction.
| | 01:36 | If I want to undo a sort, then I can
press Ctrl+Z or click the Undo button on
| | 01:41 | the Quick Access toolbar, but I will
press Ctrl+Z once to undo the first sort
| | 01:46 | and then Ctrl+Z a second time to undo
the second, and now my data is back in
| | 01:50 | its original order.
| | 01:52 | Sorting a PivotTable moves the data
you want to highlight to the top of the
| | 01:55 | PivotTable, enabling you to concentrate
on the values that you want to focus on.
| | Collapse this transcript |
| Creating a custom sort order| 00:00 | Most of the time when you sort
PivotTable data you can use the standard methods
| | 00:03 | to sort the values in
alphabetical or numeric order.
| | 00:06 | There will be times though when you
might want to define a custom list of
| | 00:10 | values and sort a PivotTable
using those values. For example,
| | 00:13 | if your company has stores in four
regions, north, south, east, and west, and you
| | 00:18 | want the regions to show in that order,
instead of alphabetical order, you can
| | 00:22 | create a custom list and use that
list to sort your fields' values.
| | 00:26 | So for this example, I have a
PivotTable with sales data for four regions, east
| | 00:31 | north, south, and west, but I want to
have them sorted into the order north,
| | 00:35 | south, east and west, instead of
alphabetical order like they are now.
| | 00:39 | To do that we can create a custom list.
| | 00:42 | So click the File tab on the Ribbon
to display the Backstage view, and then
| | 00:47 | click Options to display the Excel
Options dialog box, and then on the Advanced
| | 00:53 | page scroll down, which I'll do using
my mouse with scroll wheel, and then under
| | 01:00 | the General heading, click
the Edit Custom List button.
| | 01:03 | Doing so displays the Custom Lists dialog box.
| | 01:08 | Now you can create your list and in
this case we will do it by typing it in.
| | 01:12 | So here in the List Entries bo, we can
type North, then press Enter. Each item
| | 01:19 | needs to be on its own line, and we
have South, Enter, East, Enter, and West.
| | 01:30 | So all the entries look to be correct
and spelled correctly so I can click OK.
| | 01:35 | And then click OK again to
close the Excel Options dialog box.
| | 01:40 | And now I can sort the field values based
on the new list that I have just created.
| | 01:45 | Now to sort by row labels, in this case,
the regions, into north-south-east-west
| | 01:50 | order, just click any cell that contains
one of those labels, in this case East
| | 01:54 | is already selected, and then on the
Options contextual tab of the Ribbon, click
| | 01:59 | the Sort button to display the Sort
dialog box, and then select the ascending A
| | 02:05 | to Z option by, and then make sure that
the region field is selected, and then
| | 02:11 | click the More options button.
| | 02:14 | In the More Sort Options dialog box,
clear the Sort Automatically checkbox here
| | 02:18 | at the top, and then under First key
sort order click the down arrow and
| | 02:24 | select list that you want to use, in this case
North, South, East and West, and then click OK.
| | 02:30 | Then back in the Region dialog box
click OK again, and when you do Excel
| | 02:36 | applies the sort using your custom list.
| | 02:38 | Custom lists help you arrange your
PivotTable data to emphasize the elements
| | 02:42 | you feel are most important, making
sorting a more powerful and useful tool
| | 02:46 | than ever before.
| | Collapse this transcript |
| Filtering a PivotTable field by selection| 00:00 | You can use PivotTables to summarize
huge data collections, but many times you
| | 00:04 | will want to limit the data displayed
within a given category. For example,
| | 00:08 | if your company's operations are
divided into four regions, you might want to
| | 00:12 | display just those
results for one of the regions.
| | 00:15 | You can limit the data displayed in
a PivotTable by creating a filter.
| | 00:18 | There are three main ways to filter
the contents of the PivotTable field:
| | 00:22 | by selection, by rule, and by search term.
| | 00:26 | Filtering by selection means you
display a list of values in the field and
| | 00:29 | select the values you want to display.
| | 00:31 | So for example, let's say that I have
the results here in those PivotTable that
| | 00:36 | displays sales for four different regions,
East, North, South, and West, and let's
| | 00:41 | say that I only want to display those
values for the North and West regions.
| | 00:46 | To do that I'll click any cell on the
PivotTable and then over in the PivotTable
| | 00:50 | Field List I move my mouse pointer over
the header of the field that I want to
| | 00:55 | filter, and then click the downward
pointing black triangle that appears, and
| | 01:00 | when I do, the Sort and Filter menu appears.
| | 01:04 | In this case I want to select the
values that I want to appear and those are
| | 01:08 | North and West. So I can go into
the Filter list, clear the Select All
| | 01:12 | checkbox, and then check North and
scroll down, and check West. With those two
| | 01:19 | values selected I can click OK and
Excel applies the filter limiting the data
| | 01:24 | to only North and West.
| | 01:26 | If I want to edit the filter, then I
can display the Filter menu again, and
| | 01:30 | again that's over in the PivotTable
Field List, and then let's say that I want
| | 01:34 | to display East and West. So I'll clear
the checkbox next to North and checkbox
| | 01:39 | next to East, click OK, and
Excel updates the filter.
| | 01:43 | If I want to clear the filter, then I
can go back into the Filter menu and on
| | 01:49 | the menu a little bit higher up above
the select list, I can click Clear Filter
| | 01:53 | From Region and the item in double
quotes then will change, based on the fields
| | 01:58 | that you're working with.
| | 02:00 | So in this case it's Clear Filter From
Region. Click it and Excel restores the
| | 02:05 | PivotTable to its unfiltered state.
| | 02:07 | You're not limited to
creating one filter at a time.
| | 02:10 | So let's say for example that I want to
display the values for the North and West regions.
| | 02:15 | So I will click Region, click the
down arrow that appears next to it, clear
| | 02:20 | Select All, and then check the North
and West boxes and click OK. And then
| | 02:26 | let's say that I only want to see
the values for January and February.
| | 02:30 | So then I will move the mouse pointer
over the Month header, click the downward
| | 02:34 | pointing black triangle, clear the
Select All checkbox, which removes the
| | 02:38 | checkmarks from all the boxes in the list,
and then select January and February.
| | 02:45 | When I click OK, Excel applies
the second filter to the table.
| | 02:49 | Filtering by selection gives you
pinpoint control over the values that appear
| | 02:52 | in your PivotTable.
| | 02:54 | You should use this type of filter
when you want to display or exclude a few
| | 02:57 | values from the display.
| | Collapse this transcript |
| Filtering a PivotTable by rule| 00:00 | PivotTables help you summarize large
amounts of data, but you can always limit
| | 00:04 | the data that appears by creating a filter.
| | 00:07 | If the data you want to display in
your PivotTable fits the rule, such as
| | 00:10 | all values greater than 1000, you can define
that rule and use it to filter your PivotTable.
| | 00:16 | To filter a PivotTable using the values
in the field, you can click any cell in
| | 00:20 | the body of the PivotTable and then
go over to the PivotTable Field List task
| | 00:24 | pane and position your mouse pointer
over the field that you want to filter by.
| | 00:29 | In this case, I want to filter by month,
so I will move the mouse pointer over
| | 00:33 | that field, and then on the right edge
click the down arrow that appears and you
| | 00:38 | can select the type of
filter that you want to create.
| | 00:41 | You can create two different types of
filters: a label filter or a value filter.
| | 00:46 | As the name imply,s a label filter lets
you filter on the values in the label area.
| | 00:51 | So for example, in this PivotTable we
have Years, 2009, Quarters, 1 2 3 and 4, and
| | 00:57 | then Month, January, February, March and so on.
| | 01:00 | If we want to filter based on one of those
label values, we would create a label filter.
| | 01:05 | However, if we wanted to create a
filter based on the values within the data
| | 01:09 | area of the PivotTable, then
we can create a value filter.
| | 01:13 | So for the Month field, I will create a
value filter and then I can select the
| | 01:19 | type of filter that I want to create.
| | 01:21 | So in this case I want to display any
month that has a total of more than $200.
| | 01:26 | So I will click Greater Than and
then in the Value Filter dialog box,
| | 01:32 | I have Sum of Revenue is greater than, and
then I can type in my criteria, which is 200.
| | 01:39 | When I click OK, Excel applies the
filter to the PivotTable and you can see that
| | 01:44 | it only displays rows where
the grand total is more than 200.
| | 01:48 | To remove the filter, I can go back
into the PivotTable Field List, click the
| | 01:53 | Month header, and then
click Clear Filter from Month.
| | 01:57 | Now let's say that I want to filter
based on label values and for this example
| | 02:02 | I want to display only those values
for Quarters 3 and 4, in other words
| | 02:08 | Quarters with a value greater than 2.
| | 02:11 | To do that I will filter based on the
Quarter label. So back in the PivotTable
| | 02:16 | Field List, I will position my mouse
pointer over the Quarter field, click the
| | 02:21 | down arrow, point to Label
Filters and then click Greater Than.
| | 02:27 | I want to display any Quarter that is
greater than 2 so I will type 2 in the
| | 02:32 | second box and click OK.
| | 02:36 | When I do, Excel filters the
PivotTable so it only displays results
| | 02:40 | from Quarters 3 and 4.
| | 02:41 | Filtering PivotTable data helps you
gain insight into your data by focusing
| | 02:45 | the display on the values you want to examine.
| | 02:48 | You should experiment with the many
types of filters available to you, so you
| | 02:51 | will know which to use to answer
particular questions about your data.
| | Collapse this transcript |
| Filtering a PivotTable using a search filter| 00:00 | When you work with a PivotTable, you'll
often find that you want to locate data
| | 00:03 | that contains a particular string of characters.
| | 00:06 | For example, if you have monthly data,
you might want to look for data that only
| | 00:09 | occurs in the months of June and July.
| | 00:12 | Because both of those months have the
characters J-U next to each other, you can
| | 00:17 | create a filter that looks
for exactly those characters.
| | 00:19 | This is a new capability in Excel
2010 and is called a search filter.
| | 00:24 | To create a search filter, you go
over to the PivotTable Field List task pane,
| | 00:28 | and then you move your mouse-pointer
over the field name that you want to filter.
| | 00:33 | In this case, we'll filter by Month,
then click the downward-pointing black
| | 00:38 | triangle that appears and then type the
character string that you want to search
| | 00:42 | for in the Search box.
| | 00:44 | In this case, I'm looking for the
characters J and U, and when I type that in,
| | 00:50 | you'll see that Excel indicates the
values that would be selected if we were
| | 00:55 | to apply this filter.
| | 00:56 | So right now, we have June and July.
| | 00:59 | When I click OK, Excel applies
the filter to the PivotTable.
| | 01:03 | Let's say that I'm interested
in June, July, but also August.
| | 01:08 | I can create a second search filter that
adds on to the filter that I've already created.
| | 01:13 | To do that I'll go back into the
Filter menu for the Month field and then in
| | 01:18 | the Search box I will type AU.
| | 01:21 | And all the characters I have typed so far
occur at the beginning of the month name,
| | 01:25 | JU for June and July and AU for August.
| | 01:28 | But the search string that I type in
can occur anywhere within the field name.
| | 01:32 | So for example, if I were to type
in ER, then I would get December,
| | 01:37 | October, November and so on.
| | 01:39 | So I have typed in AU and August is displayed.
| | 01:43 | If I check the Add current selection
to filter box and then click OK, Excel
| | 01:50 | adds the month of August to the filter.
| | 01:52 | If I want to remove the filter, I can
click the Month header's down-arrow and
| | 01:58 | click Clear Filter From Month and
when I do, Excel removes the filter.
| | 02:03 | The filters I have created so far using
the Search capability are the equivalent
| | 02:07 | of what is called a contains filter.
In other words, we're looking for values
| | 02:12 | that contain the letters JU in that order.
| | 02:15 | If you want to create the inverse of
this type of filter, you can create a
| | 02:19 | does not contain filter.
| | 02:20 | So let's say that I want to exclude any
month that has the characters ER in its name.
| | 02:26 | To do that, I can click the Month
header's down arrow, point to Label Filter,
| | 02:32 | because I'm filtering based
on values in the label area,
| | 02:35 | and then in the list of filters that are
available, I can click Does Not Contain.
| | 02:39 | Now, I can type in the characters ER,
and again they can occur anywhere in the
| | 02:44 | Month name, click OK, and Excel
lists the data based on that filter.
| | 02:50 | Again, to remove the filter, just click
the Month header's down arrow and click
| | 02:54 | Clear Filter From Month.
| | 02:57 | Search filters help you limit the data
in your PivotTable to those values that
| | 03:00 | contain a specific text string.
| | 03:02 | If you find you want to exclude items
that contain a common text string, you can
| | 03:06 | also create a Does Not Contain
filter to limit your data that way.
| | Collapse this transcript |
| Filtering a PivotTable using slicers| 00:00 | When you filter a PivotTable,
Excel indicates that a field has been filtered
| | 00:04 | by placing dilter icons in the body
of the PivotTable and in the PivotTable Field List.
| | 00:09 | Unfortunately, these icons are small
and somewhat hard to see and also give no
| | 00:14 | indication of which values are
included in or excluded from the filter.
| | 00:18 | In Excel 2010, you can filter
your PivotTables by using slicers, which
| | 00:23 | graphically indicate which values
are included and excluded by a filter.
| | 00:27 | To filter a PivotTable using the values
in the field, just click any cell to
| | 00:31 | activate the PivotTable and then on
the Options contextual tab click the Insert
| | 00:36 | Slicer button to display the Insert Slicers dialog box.
| | 00:41 | Now, you can check the box next to any
of the fields for which you want to create a slicer.
| | 00:46 | So let's say in this case
I will do it for Month.
| | 00:50 | So I'll just check that box and click OK.
| | 00:53 | When I do, Excel creates a slicer
that has an entry for each month that's
| | 00:59 | displayed in the PivotTable.
| | 01:01 | When Excel created the slicer,
it isn't large enough to display all the values.
| | 01:05 | So I'll move my mouse-pointer over
the bottom edge of the slicer and when the
| | 01:10 | mouse pointer changes to an up- and
down-pointing arrow, I can drag down to
| | 01:13 | resize it, and now
we can see all of the months.
| | 01:16 | So now let's say that I want to display
only those values for the month of May.
| | 01:21 | To do that, I just click the May button.
| | 01:24 | When I do, Excel displays only the values
for 2009 and 2010 for the month of May.
| | 01:30 | Now, let's say that I only want to
see the values for May and September.
| | 01:34 | To select both of those months, first
I select the one that I want, in this case May,
| | 01:39 | and then hold down the Ctrl key and
click the second month that I want to
| | 01:44 | display, in this case September.
| | 01:45 | After I release the left-mouse button,
Excel updates the Slicer to indicate that
| | 01:51 | both May and September are selected
and the PivotTable updates to show the data
| | 01:56 | for May and September within it.
| | 01:58 | Now let's say that I want to see
all the data for January through April.
| | 02:02 | Instead of Ctrl+Clicking each
individual month, what I can do is Shift+Click
| | 02:07 | and that will select every month in that range.
| | 02:11 | So let's say that I click January
and then hold down the Shift key and because I
| | 02:16 | want to see January through April,
I click the month of April.
| | 02:20 | When I do, Excel selects each of
the months in the range January through April.
| | 02:25 | If you want to clear filter applied
by a slicer, then you can click the Clear
| | 02:29 | Filter icon, which is here at the top-
right corner of the slicer, and also you
| | 02:35 | can create slicers for more than one field at a time.
| | 02:38 | So let's say that in addition to
month I want to filter by Company.
| | 02:42 | To do that I'll click any cell
within the PivotTable and then on the Options
| | 02:47 | contextual tab click the Insert Slicer button again.
| | 02:51 | Now, I'll select a slicer for a Company.
| | 02:54 | So I'll check that box, click OK,
and I get my second slicer this time for Company.
| | 03:02 | So let's say that I only want to see
the values for FirmB in August and September.
| | 03:07 | So click FirmB and I'm interested
in the months of August and September.
| | 03:13 | So I'll click August and then Ctrl+Click
September to display only the values
| | 03:18 | for FirmB in August and September of 2009 and 2010.
| | 03:23 | With that work done, I can clear the
slicers by clicking the Clear Filter
| | 03:28 | button in each of them and then I
can remove the slicers by right-clicking it,
| | 03:33 | and then in the Shortcut menu that
appears, click Remove "Company" to get rid
| | 03:38 | of the Company slicer and do the same
for Month, clicking Remove "Month" to get
| | 03:44 | rid of that slicer.
| | 03:46 | Filtering your PivotTables using slicers
helps you and your audience visualize
| | 03:49 | which values are included
and excluded from your filter.
| | 03:53 | Slicers work best for filtering
fields with 20 or fewer unique values,
| | 03:57 | especially if your PivotTable takes
up more than half the screen after you apply the filter.
| | Collapse this transcript |
| Formatting slicers| 00:00 | Filtering a PivotTable using a slicer
helps you and your colleagues visualize
| | 00:04 | which values the filter includes and excludes.
| | 00:06 | You have a lot of control over the
slicer's appearance and other characteristics
| | 00:10 | so you can customize it to meet your needs.
| | 00:12 | In this case, I have the Year and Month
fields displayed within the slicers, so
| | 00:17 | I can use them to filter my PivotTable.
| | 00:19 | One thing that jumps out immediately
is that the slicers aren't visually
| | 00:23 | distinguished from the body of the PivotTable.
They are formatted in pretty much the same way.
| | 00:27 | In other words,
so they sort of blend in.
| | 00:29 | What I'd like to do is change the style
of the slicers so that they stand out
| | 00:34 | a bit more and makes it easier to
see which values are included and excluded
| | 00:38 | from the PivotTable.
| | 00:40 | To change the appearance of the
slicer, just click the Slicer.
| | 00:43 | In this case, I'll work with Month
and then click the Options contextual tab
| | 00:48 | that appears and then in the Slicer
Styles gallery click a new style.
| | 00:53 | To see all the styles available to you,
click the More button at the
| | 00:56 | bottom-right corner of the gallery.
| | 00:58 | And in this case, I'll select the dark
red style just because it stands out the most.
| | 01:05 | I'll do the same thing for the Year slicer.
| | 01:09 | So, on the Options tab, I'll click the
Dark 2 style which incorporates dark red.
| | 01:15 | If you want you can create your own custom slicer style.
| | 01:18 | To do that, click the More button at
the bottom-right corner of the Slicer
| | 01:21 | Styles gallery, and then click New Slicer
Style, and then use the controls to
| | 01:27 | create the style that you want.
| | 01:29 | In this case, I will call the style New Custom.
| | 01:33 | Then I'll just change the color of
the header by clicking that header within
| | 01:37 | the slicer element list and then click Format
and I'll change the fill color to a burnt umber.
| | 01:47 | When I click OK, Excel displays a
preview of the style here and that's the
| | 01:52 | only change I'll make.
| | 01:53 | You can experiment with any other changes
that you want to make with any of the
| | 01:56 | other elements and then click OK.
| | 02:00 | That slicer style appears in your gallery.
| | 02:03 | Clicking the More button
you can see it up here in Custom.
| | 02:06 | So I'll apply that to the
active slicer which is here.
| | 02:10 | If you want, you can also
change the caption of a slicer.
| | 02:13 | So for example the slicer with
the orange header has Year as its caption.
| | 02:19 | If I want to change that to Fiscal
Year, then with the slicer selected on
| | 02:23 | the Options contextual tab, I can go over
to the Slicer Caption box and edit the value.
| | 02:29 | So in this case, instead of making it Year,
I'll make it Fiscal Year. So Fiscal Year.
| | 02:38 | When I press Enter, Excel changes
the value in the header of the slicer.
| | 02:43 | You can also change a slicer's layout.
| | 02:45 | So for example in the Month slicer,
we have a list of 12 months.
| | 02:49 | But let's say that I wanted to divide
those into two columns instead of just one.
| | 02:54 | To do that, I could select the slicer
so that it was active, and you can tell a
| | 02:58 | slicer is active by the
border that appears around it.
| | 03:01 | It's called a selection border.
| | 03:02 | And then on the Options contextual tab
in the Buttons group, edit the value in
| | 03:08 | the Columns box so that you have the
number of columns you want and in this
| | 03:11 | case we'll just make it 2.
| | 03:13 | You can also change the Height
and Width of an individual button.
| | 03:18 | So because I'm making it 2 columns
rather than having the width of each
| | 03:21 | individual button be 1.82 inches like
it is now, I'll just reduce it to 1.
| | 03:27 | So I'll edit that value, type 1 and press Enter.
| | 03:31 | Slicers provide a visual
representation of a PivotTable filter.
| | 03:35 | So you should take care to format
and customize your slicers so they are
| | 03:39 | visually distinctive, but don't
overwhelm the data within the PivotTable.
| | Collapse this transcript |
| Filtering a PivotTable with report filter fields| 00:00 | Whenever you add a field to a
PivotTable's column area or row area, you change
| | 00:04 | the PivotTable structure by adding a
layer of detail. But suppose you have a
| | 00:08 | great PivotTable layout and want to
filter the PivotTable using the values in
| | 00:12 | a field that doesn't
appear within the arrangement.
| | 00:14 | For example, you might want to filter
monthly sales by quarter, but without
| | 00:19 | having the Quarter field
change the PivotTable's layout.
| | 00:22 | How do you do that?
| | 00:24 | Well the answer is that you add the
quarter field to the Report Filter field
| | 00:28 | area and create the filters as normal.
| | 00:31 | So the first thing you do is click any
cell new PivotTable and make sure that
| | 00:36 | the PivotTable Field List task pane
is displayed and in the bottom of the
| | 00:40 | PivotTable Field List you
see several different areas.
| | 00:43 | There is the Column Labels area, the
Row Labels area, and the Values area, which
| | 00:48 | allow you to create the
structure of the PivotTable.
| | 00:51 | The area we haven't covered yet
is called the Report Filter area.
| | 00:55 | As the name implies you can add
fields to that area without changing the
| | 00:59 | structure of the interior of the PivotTable and
create filters to limit the data that appears.
| | 01:04 | So let's say in this case that I wanted a
filter based on the values in the Quarter field.
| | 01:09 | To do that I drag the Quarter field
down to the Report Filter Area and when
| | 01:15 | I do, Excel asks if I want to replace the
contents of the destination cells in Sheet 3.
| | 01:22 | And the reason it is asking that is
because the value Corporate Revenue
| | 01:26 | appears in cell A1.
| | 01:28 | Cells A2 and A1 are where the Report
Filter area will go once I add a field to
| | 01:34 | the PivotTable so the
contents of cell A1 will be lost.
| | 01:38 | In this case, that's fine.
| | 01:39 | I only put it there as an example, so I
can click OK and when I do Excel creates
| | 01:45 | the Report Filter area.
| | 01:48 | Now to create a filter I can click the
down arrow next to the Quarter field name
| | 01:54 | and then the selection list is a
little bit different than it is for when you
| | 01:58 | create a value filter or a label
filter based on a regular filed.
| | 02:02 | In this case, if you want to select a single
item, all you need to do is click that item.
| | 02:07 | So let's say that I only want to
see the results for quarter number 1.
| | 02:10 | To do that I click 1 and click OK and
Excel displays only those values from
| | 02:16 | January, February, and March of both years.
| | 02:19 | However, let's that I want to
show the results quarters 1 and 3.
| | 02:24 | To do that, click the Filter arrow
for the Quarter field and then check the
| | 02:29 | Select Multiple Items box.
| | 02:32 | When you do you see the familiar
selection filter area that we have seen when we
| | 02:36 | created other filters for this PivotTable.
| | 02:39 | Simply select any box next to
the item that you want to display.
| | 02:42 | In this case 1 and 3, and 4 is
unselected. Click OK and now you see the results
| | 02:49 | for quarters 1 and 3.
| | 02:52 | If you want to clear a Report Filter
you can just click the down arrow, click
| | 02:58 | the All button, and then click OK.
| | 03:02 | One nice thing about adding a Report
Filter field is that you can create individual
| | 03:06 | worksheets for each of the values in that field.
| | 03:10 | So let's say for example that I wanted
to create a worksheet that had only data
| | 03:14 | for quarter 1, another for data with
only quarter 2, and so on through quarter 4.
| | 03:20 | To do that make sure that you have a
filed in the Report Filter area. You don't
| | 03:24 | have to have a Report
Filter applied at the time.
| | 03:26 | It just needs to be a field in that area
and then on the Options contextual tab,
| | 03:31 | in the PivotTable group, click the
Options button down arrow and then click Show
| | 03:37 | Report Filter Pages.
| | 03:40 | In the dialog box make sure that
the quarter field appears and is
| | 03:43 | highlighted and then click OK.
| | 03:47 | When you do, Excel creates a new
worksheet and each worksheet contains data for
| | 03:52 | each value in the field that
was in the Report Filter area.
| | 03:56 | So for example, we have quarter 1,
which is on worksheet 1, and then worksheet 2
| | 04:02 | has the data for quarter 2,
worksheet 3 number 3, worksheet 4 number 4.
| | 04:08 | Now these are complete copies of the data.
| | 04:10 | So for example, if I wanted to display
all of the data on the 4 worksheet
| | 04:15 | then I can click All, click
OK, and all of the data appears.
| | 04:21 | But when Excel creates the worksheet for
you, it is filtered so it only displays
| | 04:25 | the data for an individual item from
the field in the Report Filter area.
| | 04:30 | If you have a worksheet with the same
name as one of the field values, Excel
| | 04:34 | names the new sheet value (2).
| | 04:36 | So let's say for example, that there is
already a worksheet named 4 in this workbook.
| | 04:44 | In that case this
worksheet would be named 4 (2).
| | 04:48 | Filtering PivotTables using the
Fields and Report Filter area is a
| | 04:51 | powerful capability.
| | 04:53 | Not only can you limit the data
that appears in your PivotTable without
| | 04:56 | changing its structure, you can create
separate worksheets for each value in
| | 04:59 | the Report Filter field.
| | Collapse this transcript |
| Clearing and reapplying PivotTable filters| 00:00 | You can limit the data that appears
in a PivotTable by applying a filter.
| | 00:04 | But there would be little sense in
applying a filter if you weren't able
| | 00:06 | to remove it later.
| | 00:08 | In Excel you can remove filters from
individual fields, remove all filters at
| | 00:12 | the same time, and reapply
filters you just got rid of.
| | 00:15 | So for example in this PivotTable here, I have
filters applied to the Year and Month fields.
| | 00:22 | So let's say that I want to
clear the filter from the Year field.
| | 00:26 | To do that I can go into the
PivotTable Field List, click the downward-
| | 00:30 | pointing black triangle at the right
edge of the Year field header and then
| | 00:35 | click Clear Filter From Year.
| | 00:38 | When I do, Excel removes the filter.
| | 00:40 | Now let's say that I want
to bring the filter back.
| | 00:42 | Well I could either recreate the
filter or I could use the Undo feature.
| | 00:46 | To do that I'll go up to the Quick
Access Toolbar and click the Undo button and
| | 00:52 | when I do Excel reapplies the filter.
| | 00:54 | Now let's say that you have filters in
many fields of your PivotTable and you
| | 00:58 | want to remove them all at one time.
To do that, click any cell in the on the
| | 01:02 | PivotTable and then on the Options
contextual tab click the Clear button and
| | 01:09 | then click Clear Filters.
| | 01:11 | Doing so removes every
active filter in the PivotTable.
| | 01:15 | When you're done with the filter, simply
remove it and continue with the rest of your work.
| | 01:19 | You can always re-create the filter
or if you haven't many changes you
| | 01:23 | don't want to lose you can also use
Ctrl+Z or the Undo button to bring the filter back.
| | Collapse this transcript |
|
|
4. Formatting PivotTablesApplying a PivotTable style| 00:00 | When you create a PivotTable, Excel
applies some basic formatting so you can
| | 00:03 | easily distinguish with the labels and
organizational layers from the data in
| | 00:07 | the body of the PivotTable.
| | 00:09 | Excel does come with a substantial
number of built-in styles from which to
| | 00:12 | choose, so if you do want to change your
PivotTable's formatting you can do so easily.
| | 00:17 | Before I get into styles I do
want to point out that you can format
| | 00:21 | individual cells within a PivotTable
and have that formatting stay with us
| | 00:25 | when you pivot the table.
| | 00:26 | So for example let's say that I
wanted to highlight the value 130 that is
| | 00:31 | currently in cell C6 and I will do that just
by applying a yellow fill color to that cell.
| | 00:37 | When I pivot the PivotTable, that yellow
formatting will go along with that cell.
| | 00:41 | So let's say that I put Company on top
of the Row Labels area and when I do,
| | 00:49 | the formatting has stayed with the cell
that contains the value 130 and I just
| | 00:53 | scroll down using my mouse wheel to display it.
| | 00:56 | Then if I put Company back in the
Column Labels area and scroll back up, then
| | 01:01 | you can see that the cell has
retained its formatting. And I will just go
| | 01:05 | ahead and clear that by clicking the cell
and clicking No Fill in the Cell Fill button.
| | 01:11 | If you want, you can change the
formatting of your entire PivotTable by apply a
| | 01:15 | style which is available
from the Styles gallery.
| | 01:18 | Click any cell in the PivotTable
and then on the Design contextual tab,
| | 01:23 | click the More button in the
PivotTable Styles gallery to see the styles
| | 01:27 | that are available to you.
| | 01:29 | So I will just pick another style at
random and I will select one here that has
| | 01:33 | kind of an orangey burnt umber color.
And when I click it Excel applies that
| | 01:37 | style to the PivotTable.
| | 01:38 | And you might've noticed when I move my
mouse pointer over any of the styles in
| | 01:43 | the Styles gallery that Excel displays
a live preview of how that style would
| | 01:47 | like if it were applied to the PivotTable.
| | 01:50 | So if I move the mouse pointer over
this style or this style I don't need to
| | 01:55 | actually apply it to have Excel let me know
what it would look like if I were to apply it.
| | 01:59 | But now let's say that I have some
existing formatting applied to a PivotTable.
| | 02:04 | What I will do now is press Ctrl+Z once
to remove the style that I applied and
| | 02:09 | I'll change the formatting of the
FirmA and FirmB cells by pressing Ctrl+I to
| | 02:18 | make the text italic in addition to being bold.
| | 02:21 | Now there are two ways that I
can apply another PivotTable style.
| | 02:25 | I can either click the style like I did
before, which applies the style and does
| | 02:30 | not overwrite any existing formatting.
| | 02:33 | So let's say that I apply this style.
| | 02:36 | When I do, Excel retains the
italic formatting for FirmA and FirmB.
| | 02:41 | However if I right-click the style, then
a shortcut list of options appears and I
| | 02:46 | can either choose to apply and
maintain the formatting, which is the default
| | 02:51 | choice, or apply and clear
formatting, which if I select
| | 02:55 | will apply the style that I
just selected and also remove an
| | 03:00 | existing formatting.
| | 03:01 | So whereas the values FirmA and FirmB
where italics before, now they're not.
| | 03:06 | Now PivotTable styles are part of
Office themes, which are collections of
| | 03:11 | predefined color schemes.
| | 03:13 | If you apply a built-in PivotTable
style and then change in the Office theme
| | 03:17 | applied to your workbook, the built-
in PivotTable style changes as well.
| | 03:21 | So for example I'll apply another PivotTable
style and I'll make it Medium 2, which is blue.
| | 03:29 | If I go to the Page Layout tab of the
ribbon and then in the Themes group click
| | 03:34 | the Themes button to display other
Office themes and I will select another theme,
| | 03:39 | say Austin. When I hover my mouse
pointer over that theme you can see how in the
| | 03:44 | background it would affect the
PivotTable style that I previously selected.
| | 03:48 | So if I were to hover over Black
Tie, you can see how that would change.
| | 03:52 | Concourse, Composite, Clarity and so on.
| | 03:56 | I am going to exit out without
actually applying another theme, but hopefully
| | 04:00 | you'll get the idea of what would happen
if you were to change your theme while a
| | 04:04 | PivotTable Style was applied.
| | 04:06 | After you apply a PivotTable style, you
can turn individual elements on and off.
| | 04:10 | For example, let's say that you apply
a style where the even and odd rows are
| | 04:15 | formatted differently.
| | 04:17 | Excel refers to that scheme as banding.
| | 04:20 | So to illustrate the point I will
go to the Design tab and then in the
| | 04:24 | PivotTable Styles gallery I will apply
the style called PivotTable Light 16.
| | 04:29 | So I will come down to the third
row. There is 15 and there is 16.
| | 04:36 | When I click it, Excel
applies it to the PivotTable.
| | 04:38 | Now this is a banded style but the
banding doesn't appear because
| | 04:42 | the Banded Rows checkbox on the
Design contextual tab isn't selected.
| | 04:47 | If I select it you can see that the
alternate rows are dark and light to help
| | 04:52 | distinguish them within the PivotTable.
| | 04:55 | Regardless of the style that you apply,
you can always change the formatting
| | 04:58 | of individual cells.
| | 05:00 | You can also create your own custom styles.
| | 05:02 | I will show you how to
do that in the next movie.
| | Collapse this transcript |
| Creating a PivotTable style| 00:00 | The built-in PivotTable Style gallery
contains a lot of good color schemes, but
| | 00:04 | they are by design somewhat generic.
| | 00:06 | When you want to create your own
PivotTable style to reflect your personal
| | 00:10 | aesthetic or your company's graphic art
guidelines, you can do so using the new
| | 00:14 | PivotTable Quick Style dialog box.
| | 00:17 | To display that dialog box, click any
PivotTable cell and then on the Design tab
| | 00:22 | of the Ribbon click the More button at
the bottom-right corner of the PivotTable
| | 00:26 | Styles gallery and then
click New PivotTable Style.
| | 00:32 | The first thing you should do is type
the name for your new PivotTable style.
| | 00:35 | So if you were creating a style to
reflect your company's graphic standards,
| | 00:39 | you could call it something
like Graphics Standards 2011.
| | 00:45 | Next you click the table element you
want to change and then click the Format
| | 00:49 | button to display the formatting
tools to apply to that element.
| | 00:52 | So for example let's say that I want
to change the header row's formatting.
| | 00:57 | To do that, I click the header row table
element from the list and then click Format.
| | 01:03 | Then, this is the
familiar Format Cells dialog box.
| | 01:06 | You can change any aspect of
the formatting that you want.
| | 01:10 | So in this case, we will make the
Font Style Bold and the Color White.
| | 01:16 | So to do that and to have the text show
up, we need to have a dark background.
| | 01:20 | So we will click Fill and
then I will select Purple.
| | 01:24 | When I'm done in the Format
Cells dialog box I can click OK.
| | 01:28 | And when I do, Excel displays a preview of
what my style will look like after I save it.
| | 01:33 | That's the only change I will make
but you can make changes to any other
| | 01:36 | elements that you like.
| | 01:37 | just feel free to experiment using
the elements in the Table Element list.
| | 01:42 | When you are ready, go ahead and
click OK and you have created your style.
| | 01:47 | To apply that style, click the More
button on the Design contextual tab and
| | 01:52 | you'll see your new style at the
top of the list in the custom area.
| | 01:56 | To apply it, just click the style and
Excel applies it to your PivotTable.
| | 02:01 | If you want to edit a custom style,
just right-click it within the gallery, so
| | 02:06 | click the More button to display the
entire gallery and then right-click your
| | 02:10 | style and then click Modify.
| | 02:14 | The Modify PivotTable Quick Style dialog
box is essentially the same in all but name
| | 02:18 | as the Create
PivotTable Quick Style dialog box.
| | 02:22 | So for example, let's say that I
wanted to add a second row stripe.
| | 02:27 | To do that, I can click Second Row
Stripe, click Format, and I will make every
| | 02:32 | second row a dusty purple.
| | 02:34 | So I have selected the color that I want,
click OK, and we see the preview here. Click OK.
| | 02:40 | Now you will notice that even though I
edited the style, the second row stripe
| | 02:45 | doesn't appear and that's because
the Banded Rows option isn't turned on.
| | 02:50 | So when I go up to the Design tab and
check the Banded Rows box, Excel applies
| | 02:56 | the full style which includes the
Second Row Stripe that I defined when I
| | 02:59 | modified the PivotTable Style.
| | 03:01 | Now let's say that you
want to edit a built-in style.
| | 03:04 | You actually can't, but what you can do
is duplicate an existing style and then
| | 03:09 | make changes based on the duplicate.
| | 03:10 | So let's say for example that I
wanted to create a style that was based on
| | 03:15 | Pivot Style Light 1.
| | 03:17 | To do that I right-click the style that I
want to work with and then click Duplicate.
| | 03:23 | When I do, Excel creates a new style
and I can change its name, change any of
| | 03:29 | its values, any of its formatting, and so on.
| | 03:32 | It's exactly the same as creating or
modifying another PivotTable style so I
| | 03:36 | won't work through all the details.
| | 03:38 | When you're done you can click OK to
save your changes or do as I will do, which
| | 03:42 | is click Cancel to exit the
dialog box without saving your changes.
| | 03:46 | If you want to delete a custom
PivotTable style, you can right-click the style,
| | 03:51 | and this is the custom style I created
earlier, and then on the Shortcut menu
| | 03:55 | that appears click Delete.
| | 03:58 | Excel asks if you are
sure and you can click OK.
| | 04:01 | When you do, Excel reverts to the
previous style applied to the PivotTable.
| | 04:05 | Now finally, if you want to remove
all formatting from your PivotTable,
| | 04:10 | click any cell on the PivotTable,
and then on the Design contextual tab,
| | 04:14 | click the More button at the bottom-
right corner of the Pivot Styles Gallery
| | 04:19 | and then click Clear.
| | 04:21 | Excel gives you the tools to control exactly
how your PivotTable appears in your workbook.
| | 04:26 | Creating your own formats enables you
to control how your PivotTable appears in
| | 04:29 | your company's documents and
presentations, enhancing your corporate identity
| | 04:33 | while making the data
easier for viewers to comprehend.
| | Collapse this transcript |
| Changing the PivotTable layout| 00:00 | Excel PivotTables enable you to
summarize your data in formats that are easy to
| | 00:04 | read, and very helpfully, easy to modify.
| | 00:07 | You can also change your PivotTable's
layout, choosing whether to display blank
| | 00:10 | rows below each item and selecting
from three report layouts with subtle but
| | 00:14 | important differences that are
useful for you to know about.
| | 00:18 | By default, Excel PivotTables have
no blank rows after an item ends.
| | 00:22 | In this PivotTable, there is a subtotal
here for yearly sales in 2010, but there
| | 00:27 | is no gap between the Summary of 2010,
and the last month, December of 2009.
| | 00:34 | If you want to add a blank row there,
you can click any cell in the PivotTable
| | 00:39 | and then on the Design contextual tab
click the Blank Rows button and then
| | 00:44 | click Insert Blank Line after Each Item.
| | 00:47 | When you do, Excel inserts the blank line.
| | 00:50 | If you want to get rid of it, again on
the Design tab click Blank Rows, and
| | 00:55 | click the Remove Blank
Line after Each Item button.
| | 00:58 | As I mentioned before there are
three report layout options that display
| | 01:02 | PivotTable data in slightly different ways.
| | 01:04 | The first layout I'd like to
show you is called Outline view.
| | 01:07 | To display a PivotTable in Outline view,
go to the Design contextual tab, click
| | 01:11 | the Report Layout button, and
then click Show in Outline Form.
| | 01:17 | Outline Form moves each row label over one
column so there is a horizontal gap between them.
| | 01:22 | The first entry under each item, in this
case Months as part of Years, starts one
| | 01:26 | row below the header.
| | 01:27 | This view makes it easier to pick out
divisions within a PivotTable by scanning
| | 01:31 | down the appropriate column.
| | 01:32 | Finally, notice that Outline Form
puts subtotals at the top of each group.
| | 01:37 | Tabular Form is similar to Outline Form,
but Excel aligns the first detail row
| | 01:41 | with the header and displays the
subtotals at the bottom of each group.
| | 01:45 | To display a PivotTable in tabular form,
on the Design contextual tab click
| | 01:49 | Report Layout, and then
click Show in Tabular Form.
| | 01:53 | Finally, you can display
your data in compact form.
| | 01:56 | For that, click Report Layout,
and click Show in Compact Form.
| | 02:02 | This is the most efficient way to
display your PivotTable data, but if you're
| | 02:05 | working at a low zoom level or you
have a lot of data to work with, it can be
| | 02:09 | hard to pick out individual values.
| | 02:11 | Now finally, in Excel 2010 there is a new
capability that allows you to repeat a column header.
| | 02:18 | It works in Outline view and Tabular view.
| | 02:20 | So I am going to switch back to Outline Form,
clicking Report Layout > Show in Outline Form.
| | 02:27 | My goal is to have the year 2009 appear
next to each of the months of the year.
| | 02:32 | so January, February, and so on.
| | 02:34 | And it won't make a big difference
here because all of the months fit on one
| | 02:37 | screen, but if you had a category for
all of the individual values such as weeks
| | 02:41 | in a year didn't fit on a particular
screen, then it might be hard for you to
| | 02:46 | remember which year you're
working with at one time.
| | 02:49 | So to repeat the value 2009, click
any cell on the PivotTable, click Report
| | 02:54 | Layout, and then click Repeat All Item Labels.
| | 02:59 | When you do, the labels appear
in the body of your PivotTable.
| | 03:02 | If you want to get rid of them,
click Report Layout and then click Do
| | 03:05 | Not Repeat Item Labels.
| | 03:08 | Modifying PivotTable layouts enables
you to present your data as effectively as
| | 03:11 | possible, both for your personal
viewing and as part of a presentation.
| | 03:16 | You should take the time to
experiment with these options so you can decide
| | 03:18 | what you like best.
| | Collapse this transcript |
| Changing the data field number format| 00:00 | When you create a PivotTable, Excel
displays the values in the data fields
| | 00:03 | without any formatting.
| | 00:05 | When you look at numbers in the hundreds
and below, the lack of commas and other
| | 00:08 | formatting doesn't really matter.
| | 00:10 | But, when you add those values in a
Subtotal or Grand Total cell, the lack of
| | 00:15 | thousand separators makes the
values harder for humans to process.
| | 00:18 | You can make your data easier to read by
changing the data field's number format.
| | 00:22 | To change the number format of a
PivotTable data field, right-click any cell in
| | 00:27 | the field, and then click Number Format.
| | 00:31 | When you do, the Format Cells dialog box
appears and you can set the format for yourselves.
| | 00:37 | In this case we have a number.
| | 00:38 | So I'll click the Number category and
then I can apply settings for these numbers.
| | 00:44 | In this case all the values I am
working with are whole numbers so I can set
| | 00:48 | decimal places to 0 and some of my
totals go over a thousand, so I will check
| | 00:56 | the Use 1000 Separator box.
| | 00:58 | In the United States, the
1000 separator is a comma.
| | 01:01 | But, if you're in Europe, you might find
that they use a period instead of a comma.
| | 01:06 | The 1000 separator that appears will
depend on your local Windows settings.
| | 01:10 | I don't need to make any
other changes so I can click OK.
| | 01:14 | And when I do, you will notice that
the numbers in the body of the PivotTable
| | 01:18 | didn't change. 140 is still 140 and so on.
| | 01:21 | But the Grand Total for 2009 is 1,887
and the 1000 separator, in this case the
| | 01:28 | comma, appears, making the number easier to read.
| | 01:31 | You should always change the PivotTable
data field's number format if you have
| | 01:35 | any values, including Subtotals and
Grand Totals that could go over 1000.
| | 01:40 | In general, it's a bad idea to use the
currency or accounting formats mainly
| | 01:44 | because the currency symbols take
up space within the cells and can be
| | 01:47 | distracting when you're trying to read
numbers in the body of the PivotTable.
| | 01:50 | If you are summarizing currency values,
use a number format that displays two
| | 01:54 | places to the right of the decimal
point so you can include cents in addition to dollars.
| | Collapse this transcript |
|
|
5. Applying Conditional Formatting to PivotTablesHighlighting cells by applying a rule| 00:00 | PivotTables help you summarize large
data collections in an Excel worksheet
| | 00:04 | but it can be hard to find data that matches
specific criteria just by looking at the numbers.
| | 00:09 | It's much easier to change a cell's fill or
text color to indicate its relative value.
| | 00:14 | Formats that change the appearance of
cell's contents by applying rules are
| | 00:18 | called conditional formats.
| | 00:20 | Excel lets you apply conditional
formatting rules to PivotTable cells and have
| | 00:24 | the formats change position
when you pivot the PivotTable.
| | 00:27 | Creating a conditional format
is a lot like creating a filter.
| | 00:30 | In both cases you create rules to
indicate which cells you want to be
| | 00:33 | affected by your action.
| | 00:35 | As an example, I will create a
greater than rule for the Revenue field.
| | 00:40 | So for this example what I'd like to
do is highlight any cell that contains a
| | 00:44 | value greater than 100.
| | 00:46 | To do that, click any cell in the
field to which you want to apply the
| | 00:50 | conditional format and then on the
Home tab click the Conditional Formatting
| | 00:54 | button, and then select the
type of rule you want to create.
| | 00:58 | In this case I want to create
a Highlight Cells Rules format.
| | 01:02 | So I'll point to that item and then I can
select which of the criteria I want to use.
| | 01:08 | In this case, I want to
create a Greater Than formula.
| | 01:12 | And when I select that option,
the Greater Than dialog box appears.
| | 01:15 | Now I can type-in the value that
I want the cells to be greater than.
| | 01:19 | So in this case, I want
all values greater than 100.
| | 01:22 | So I will type 100 in the left-hand cell
and then I can click the down-arrow in
| | 01:28 | the second box, and I can either
select an existing format or I can click
| | 01:32 | Custom Format to create the format that I want.
| | 01:36 | I will create a simple format.
| | 01:37 | I will just fill any cell that contains
a value that meets my criteria in yellow.
| | 01:42 | So I'm on the Fill contextual tab and
I will click the yellow square. Click OK.
| | 01:49 | Everything looks good in this dialog
box so I will click OK again and Excel
| | 01:54 | applies the format, but
only to the selected cell.
| | 01:56 | But you'll notice that there is an
Options button that appears next to the cell.
| | 02:01 | When you click that, you are allowed
to select which type of cells you want
| | 02:04 | to apply the rule to.
| | 02:05 | There are three choices you can make.
| | 02:07 | The first is the Selected Cell, which
means only the cell that you highlighted, or
| | 02:12 | you can select All Cells
showing "Sum of Revenue" values.
| | 02:16 | When you click that, Excel includes
cells that are in the main data area but
| | 02:21 | also grand totals and subtotals.
| | 02:24 | The third option is All Cells Showing "Sum
of Revenue" values for "Month" and "Company".
| | 02:30 | If you select that option, then Excel
excludes any cells in grand total and
| | 02:35 | subtotal rows and columns.
| | 02:38 | In general, you want to apply all
conditional formats using the last option
| | 02:42 | which excludes subtotal and grand total cells.
| | 02:45 | Applying a simple rule based
conditional format can make it much easier to
| | 02:48 | interpret your PivotTable data.
| | 02:50 | All you need to do is determine the
criteria that reflects the data you want to highlight.
| | Collapse this transcript |
| Highlighting the top or bottom values in a PivotTable| 00:00 | Companies of all kinds are interested
in their best salespeople, their best
| | 00:03 | customers, and their best-selling products.
| | 00:06 | Excel enables you to create what are
called Top 10 conditional formats to
| | 00:10 | identify the top or
bottom values in a PivotTable.
| | 00:13 | You can create two types of Top 10
conditional formats in Excel, formats that
| | 00:17 | identify a certain number of top or
bottom values or a format that identifies
| | 00:21 | the top or bottom values
based on the percentage.
| | 00:23 | For example, if you wanted to identify
the top 10% of your customers by sales
| | 00:28 | regardless of the total number of customers,
you could create a percentage based format.
| | 00:33 | In this case, I have monthly revenue
data from two companies, FirmA and FirmB, and
| | 00:38 | what I'd like to do is identify the
top seven values within the PivotTable.
| | 00:42 | In other words, I don't want to look
at totals or grand totals or subtotals;
| | 00:47 | instead, I want to look at individual months.
| | 00:50 | To create that Top 10 conditional
format I'll click any cell in the data area
| | 00:56 | and then on the Home tab click
Conditional Formatting, point to Top/Bottom Rules,
| | 01:01 | and then select the type of format
that I want to create, and in this case I
| | 01:05 | will make it Top 10 Items.
| | 01:08 | I want to format cells that rank in the
top seven so I will edit the value in
| | 01:13 | the number box to the left so that it
read 7, and then I want to create a custom
| | 01:18 | format rather than using any of the
existing formats, so I'll click the Format
| | 01:23 | box's down arrow and click Custom Format.
| | 01:27 | And this is the Format Cells dialog
box that we are all familiar with.
| | 01:31 | In this case, I will change the format
of any cell that contains one of the top
| | 01:35 | 7 values to a light orange, and with
that change in place I'll click OK and
| | 01:41 | click OK again to accept the
format and to apply the change.
| | 01:45 | Now, in this case, because I had a
single cell selected, Excel only applied the
| | 01:49 | conditional format to that cell.
| | 01:51 | However, if I click the Format Options
button I can select which other cells to
| | 01:56 | apply the format to.
| | 01:57 | Now I could select All cell showing
"Sum of Revenue" values, but that would
| | 02:02 | include Grand Totals, Subtotals, and so on.
| | 02:05 | So instead, what I want to do is to
click the Formatting Options button again,
| | 02:11 | and click the final option, which is
All cells showing "Sum of Revenue" values
| | 02:15 | for "Month" and "Company".
| | 02:17 | that excludes all of the
Subtotal and Grand Total cells.
| | 02:20 | I'd also like to give you an example of
how to create a Bottom Percentage Format.
| | 02:25 | So let's say that I want to find the
bottom 33% of the values in this PivotTable.
| | 02:30 | To do that click any data cell in the
PivotTable. One is already selected.
| | 02:35 | Then click Conditional Formatting, point to
Top/Bottom Rules and then click Bottom 10%.
| | 02:41 | In this case, I want to identify the
bottom 33%, so I will delete the 10 in the
| | 02:47 | percentage box and type 33, and in this
case I'll leave the format exactly the
| | 02:51 | same, Light Red Fill with Dark Red Text.
| | 02:55 | Click OK and there's the format.
| | 02:57 | Now I want to apply it to all of the
cells excluding subtotals and grand totals,
| | 03:02 | so I'll click the Formatting Options
button and then click the last option.
| | 03:07 | Conditional formats that identify the
top or bottom values in the PivotTable
| | 03:10 | field make it easier for you to
visualize the most and least effective
| | 03:14 | performers in your organization.
| | 03:16 | Remember that you can identify a
specific number of top or bottom values or
| | 03:20 | create a rule that identifies the top or
bottom percentage of the group, such as the Top 15%.
| | 03:26 | This flexibility enables you to
create exactly the rule you need.
| | Collapse this transcript |
| Formatting PivotTable cells using data bars| 00:00 | When you summarize numerical data using
a PivotTable, Excel displays the values
| | 00:04 | with either no formatting, which can
make the numbers difficult to interpret, or
| | 00:08 | using a number format.
| | 00:09 | In both cases, to get an idea of how
two values compare you must visually
| | 00:13 | estimate the number of digits used to
express the values and if they're of the
| | 00:17 | same length, focus on the individual digits.
| | 00:20 | Comparing two numbers isn't difficult, but
comparing a few dozen or more can be confusing.
| | 00:25 | In Excel, you can add color
bars to a cell's background.
| | 00:28 | The length of the bar reflects the
relative magnitude of the value in the cell.
| | 00:33 | To create a color bar, click any data
cell in the body of the PivotTable and then
| | 00:37 | on the Home tab click the Conditional
Formatting button, then point to Data Bars,
| | 00:43 | and then select the type of
data bar that you want to create.
| | 00:46 | And there are two different groups
within this palette. The first one is the
| | 00:49 | Gradient Fill and the second is the Solid Fill.
| | 00:52 | Gradient fills taper off as they move
to the right, so it actually makes it
| | 00:56 | little bit harder to tell the
relative magnitude of values.
| | 00:59 | Those were introduced in Excel 2007
and in Excel 2010 they've been kept for
| | 01:04 | backward compatibility. But I highly
recommend that you use one of the Solid
| | 01:08 | Fill Data Bar styles which you can
find at the bottom of the palette.
| | 01:12 | So I will select the Orange and when
I click it Excel applies the data bar
| | 01:17 | conditional format to this cell.
| | 01:20 | I actually want to apply this data bar
conditional format to all the cells in
| | 01:24 | the data area so I can click the
Formatting Options button to the right of the
| | 01:28 | cell and then I can either select All
cells showing "Sum of Revenue" values,
| | 01:33 | which includes grand totals and
subtotals, or I can select the bottom option
| | 01:37 | which limits the data bar conditional
format to those cells within the body of
| | 01:41 | the PivotTable excluding
subtotals and grand totals.
| | 01:45 | And the bottom option is the one I want,
so I'll click it and apply the data bars.
| | 01:50 | You can create a data bar by using
one of the six default colors or you can
| | 01:54 | select a custom color.
| | 01:55 | To do that, click the Conditional
Formatting button, click Data Bars, and
| | 02:01 | then click More Rules.
| | 02:04 | When you do, you can go down to the Bar
Appearance section of the New Formatting
| | 02:07 | Rule dialog box and select your color
by clicking the Color button and then
| | 02:11 | selecting a color from the palette.
| | 02:14 | You can also select whether to
have a border around the bar.
| | 02:17 | So for example, I currently have No
Border but I can make it a Solid Border and
| | 02:21 | then choose the color from here if I wanted to.
| | 02:24 | If you have negative values that you
want to represent using a data bar then you
| | 02:27 | can click the Negative Value and Axis
button and that opens a dialog box of the same name
| | 02:33 | and you can select
how to fill negative values.
| | 02:37 | In this case, my example file has no
negative values but I wanted to let you
| | 02:40 | know that these options are here
so that you can work with them.
| | 02:43 | I'll click Cancel to close that.
| | 02:45 | And finally, if you only want to display
the data bar and not the data behind it
| | 02:50 | you can select the Show Bar Only checkbox.
| | 02:53 | in this case I do want to display the
value so I'll clear it and then click Cancel.
| | 02:59 | One downside to using data bars is
that because they fill a percentage of the
| | 03:03 | cell's interior and don't have a fixed
maximum length, two cells with the same
| | 03:07 | value can have data bars of different lengths.
| | 03:10 | In this PivotTable, which is laid out in
compact form, similar values in columns
| | 03:15 | B and C have differently sized data bars.
| | 03:18 | When I apply data bars to a PivotTable I
like to choose Outline Form as my report layout.
| | 03:23 | To do that you can click any cell on
the PivotTable and then on the Design
| | 03:27 | contextual tab click Report
Layout and then click Outline Form.
| | 03:33 | When you do, the cells are given
identical widths, which makes it easier to
| | 03:37 | compare relative values.
| | 03:39 | Data bars provide a quick visual
summary of the relative magnitude of values
| | 03:42 | in your PivotTable.
| | 03:44 | When you format your PivotTables so the
columns are all the same width, the bars
| | 03:47 | provide information that aids your analysis.
| | Collapse this transcript |
| Formatting PivotTable cells using color scales| 00:00 | One of the more recent developments
in data presentation is the concept of
| | 00:04 | the heatmap which uses a cell's
value to determine which fill color to
| | 00:07 | assign to the cell.
| | 00:09 | Unlike rule-based formats that either
apply a color or not, the Color Scale
| | 00:13 | conditional format applies a color
from a two or three color gradient.
| | 00:17 | Gradients can be hard to visualize if
you've never worked with one before, so
| | 00:20 | I'll start with an example and
then show you how to create it.
| | 00:23 | I applied a yellow to red format
to this PivotTable's data field.
| | 00:28 | The lower the value the more yellow
the cell's interior color; the higher
| | 00:31 | the value the more red.
| | 00:33 | The middle values, which in this
example are all around 50%, are filled with a
| | 00:38 | mix of yellow and red making orange.
| | 00:39 | So you can see the 100% are very dark,
the 49% is toward the middle, and lower
| | 00:46 | values such as 8% or 7%
have a lot of yellow in them.
| | 00:50 | Now I am going to clear the format for
the PivotTable and to do that I'll just
| | 00:54 | click Conditional Formatting > Clear
Rules. I'll just Clear Rules from This
| | 00:58 | PivotTable to get rid of it.
| | 01:00 | To create a color scale using a built-
in format, you can click any cell in the
| | 01:04 | PivotTable and then on the Home tab
click Conditional Formatting, point to
| | 01:09 | Color Scales, and then select
the color scale that you want.
| | 01:13 | You can choose from two color
scales and three color scales.
| | 01:16 | And yes, the yellow red scale I showed
you has orange at the middle, but three
| | 01:20 | color scales have a middle
color that also affects the format.
| | 01:24 | Let's say that you wanted to apply
the Green-Yellow-Red format and that is
| | 01:28 | where the highest values have green,
the middle values have yellow, and the red
| | 01:33 | values are the lowest.
| | 01:34 | If I click that within the palette that
appears, Excel applies that format but
| | 01:39 | only to the active cell.
| | 01:41 | If I want to apply it to other cells
within the PivotTable I can click the
| | 01:45 | Formatting Options button and then
click the last option which applies the
| | 01:50 | format to All cell showing "Sum of
Usage" for "Workstation" and "Day", which
| | 01:54 | means that it would exclude any
subtotal or grand total cells that were
| | 01:58 | included in the PivotTable.
| | 02:00 | When you apply that format you see that
the highest values are green, the middle
| | 02:04 | values are yellow, and the low values are red.
| | 02:07 | The values between the middle and the
high are yellowish green and the values
| | 02:11 | between the middle and low
are yellowish red or orange.
| | 02:14 | Now once again I am going to clear
this particular format. I'll go to
| | 02:17 | Conditional Formatting > Clear Rules >
Clear Rules from This PivotTable.
| | 02:22 | And now I am going to create my own
custom color scale, and to do that I'll go
| | 02:27 | back to the Conditional Formatting >
Color Scales menu and then click More Rules.
| | 02:32 | We can now use the controls in the
New Formatting Rule dialog box to create
| | 02:36 | our custom color scale.
| | 02:37 | The first thing I am going to do is
select the All cell showing "Sum of Usage"
| | 02:42 | values for "Workstation" and "Day",
which again applies the format to all cells
| | 02:46 | within the PivotTable, but not subtotal
or grand total cells, and then I'll use
| | 02:50 | the controls at the bottom end in the
Edit the Rule Description to create my
| | 02:55 | custom two color format.
| | 02:57 | For the lowest values I'm going to
choose yellow so we have Minimum and Type
| | 03:02 | indicating that this is the Lowest
Value. Click Color and I'll just select
| | 03:06 | yellow, and then for the
Highest Values I will select blue.
| | 03:10 | With those selections in place I can
click OK and Excel applies the format.
| | 03:16 | You go through exactly the same
procedure when you create a three color scale,
| | 03:19 | but there you obviously have to
pick the middle color as well.
| | 03:23 | Color scales provide terrific visual
feedback for resource utilization maps.
| | 03:27 | If you run a grocery store and want
to track which shelf positions get the
| | 03:30 | most traffic or if you run an
Internet cafe and track workstation usage, a
| | 03:35 | Color Scale conditional sormat will
indicate which resources are the most and least popular.
| | Collapse this transcript |
| Formatting PivotTable cells using icon sets| 00:00 | One of the most popular trends in corporate management is to use
icons to indicate how a company's performance compares to its goals.
| | 00:07 | If you set up your workbook as a
dashboard that summarizes performance
| | 00:10 | you can indicators such as the familiar
red and yellow and green stoplight images
| | 00:15 | to indicate acceptable,
unacceptable, and excellent results. In Excel,
| | 00:20 | such a group of indicators is called an icon set.
| | 00:24 | To apply an Icon Set
conditional format to a PivotTable,
| | 00:27 | click any cell in the PivotTable data field.
| | 00:30 | Then on the Home tab,
click the Conditional Formatting buttons,
| | 00:34 | point to Icon Sets,
| | 00:37 | and then at the bottom
click More Rules. When you do,
| | 00:41 | the New Formatting Rule dialog box appears.
| | 00:44 | The first thing you can do
is select which cells to apply the rules to
| | 00:49 | and in this case, you only want the
cells in the body of the PivotTable.
| | 00:52 | Not the grand total or subtotal cells.
| | 00:55 | So you should select the third option,
| | 00:58 | which is All cells showing "Sum of
Revenue" values for "Month" and "Company".
| | 01:02 | Next, you can select the
icon style that you want.
| | 01:06 | So if you click the down arrow, you can select and I
usually prefer to use the black rimmed traffic lights.
| | 01:12 | And now you can establish your rules.
| | 01:14 | Now, it's very important that you select
the type of rule you want to create first.
| | 01:20 | In this case this PivotTable contains
numerical data as opposed to percentages
| | 01:25 | so we'll create a Number type of rule.
| | 01:28 | So I'll click the Type down arrow
in the first row and click Number.
| | 01:34 | When you select a new type,
| | 01:35 | Excel presets the rules so
you can define its values.
| | 01:39 | so in the Value field I
want to show a green light
| | 01:42 | for anything a hundred or higher.
| | 01:44 | So I'll edit that values it reads 100,
| | 01:47 | and then I can select the type for the
rule that will define what shows a yellow light.
| | 01:53 | So I'll click the second type of cell
| | 01:56 | and click Number,
| | 01:58 | and for that value I'll click 50.
| | 02:00 | So that rule says that it
will display a yellow light
| | 02:03 | for any value less than 10 and
greater than or equal to 50. And the final rule
| | 02:09 | says it will show a red light or anything else.
| | 02:11 | So with those changes in place I can click OK
| | 02:15 | and Excel applies the conditional
format to the body of the PivotTable.
| | 02:19 | Icon sets can provide useful
information about large datasets.
| | 02:23 | But they really come into their own when you use
them to summarize a relatively small amount of data.
| | 02:28 | If you or your executive team
monitor company performance using dashboards,
| | 02:32 | you should strongly consider using icon sets to enhance
your ability to understand the dashboard summary at a glance
| | 02:38 | and also to determine where to reward strong
performance and investigate underperformance.
| | Collapse this transcript |
| Editing conditional formatting rules| 00:00 | After you create a conditional
formatting rule, you might need to change it to
| | 00:03 | reflect new operating
additions or goals of your company.
| | 00:07 | For example, you might revise your sales
targets to reflect an economic downturn
| | 00:11 | or you could decide to pay a bonus to
the top 15% of your sales representatives
| | 00:15 | instead of your top 10
because you had a great year.
| | 00:18 | To edit a conditional format, you
click any cell that contains the format as
| | 00:23 | I've done here, and then on the Home
tab of the Ribbon, click the Conditional
| | 00:27 | Formatting button and then click Manage Rules.
| | 00:32 | When you do, the Conditional
Formatting Rules Manager dialog box appears and
| | 00:36 | then you can click any
rule that you want to edit.
| | 00:39 | In this case there's only
oneso I will click it.
| | 00:42 | So it's highlighted in blue and then to open
the editing box I'll click the Edit Rule button.
| | 00:48 | You can use the controls in the Edit
Formatting Rule dialog box to change every
| | 00:52 | aspect of your conditional format,
| | 00:54 | including the type of rule, the cells to
which it's applied, and the rule's conditions.
| | 00:58 | Now in this case I have an Icon Set
conditional format and just as an example
| | 01:03 | I'll change it to a color
scale using the same conditions.
| | 01:07 | So the conditions that I have are
green for the high values, in this case
| | 01:11 | greater than or equal to 115,
| | 01:13 | yellow for anything that is less than
115, but greater than or equal to 50, and
| | 01:17 | red for everything else.
| | 01:18 | So to change the type of rule, I will
click the Format Style down arrow and
| | 01:24 | then click to 2-Color Scale and the
reason that I click 2-Color instead of
| | 01:29 | 3-Color is because in a 2-Color Scale
the top and bottom colors will combine to
| | 01:35 | form a third color.
| | 01:36 | So for example, if we had red and
yellow as my two colors than we would have
| | 01:41 | orange in the middle.
| | 01:43 | So I'll pick a couple of colors here,
so for the lower values I'll make it
| | 01:47 | yellow and then the color for the
higher values I will make blue and when I
| | 01:53 | click OK, Excel displays a
preview of the conditional format in the
| | 01:57 | Conditional Formatting Rules Manager
and when I click OK, Excel applies the
| | 02:02 | rule to my worksheet.
| | 02:03 | You're not stuck with a
conditional format after you create it.
| | 02:07 | You can change any aspect of the rule you like.
| | 02:09 | So feel free to experiment with the
rule that provides you with the information
| | 02:12 | you and your audience require.
| | Collapse this transcript |
| Controlling how multiple rules are applied| 00:00 | Excel lets you create many types of
conditional formats and it gives you many
| | 00:04 | ways to manage those rules.
| | 00:06 | For example, in Excel 2003 and
earlier each cell could have up to three
| | 00:10 | conditional formatting rules applied to it.
| | 00:12 | What's more, only one of those
rules could be applied at the same time.
| | 00:16 | When Excel discovered that one rule
is true it simply stopped checking.
| | 00:20 | In Excel 2010, there is no
practical limit to the number of conditional
| | 00:23 | formatting rules you can apply to a cell.
| | 00:25 | It's also possible for more than
one conditional formatting rule to be
| | 00:28 | applied at the same time.
| | 00:30 | So let's say that you have applied a
number of conditional formatting rules to your
| | 00:34 | PivotTable and you want to go in
and manage how they are applied.
| | 00:38 | To do that click any cell in the body
of the PivotTable and then on the Home tab
| | 00:42 | click Conditional Formatting and
then at the bottom of the menu that
| | 00:46 | appears click Manage Rules.
| | 00:50 | When you do the Conditional Formatting
Rules Manager appears and it lists each
| | 00:54 | of the formats you've created.
| | 00:56 | The first thing you should do is
ensure that you're working with the rules
| | 00:59 | applied to the PivotTable.
| | 01:01 | To do that you can look at the value in
the Show formatting rules for box and in
| | 01:05 | this case it does say This PivotTable.
| | 01:07 | If you click the down arrow you can see
the other options, which are the Current
| | 01:10 | Selection, This Worksheet, This
PivotTable, which is currently selected, and then
| | 01:15 | either Sheet2 or Table1.
| | 01:17 | So all these are options and in this case
this PivotTable selected and it's correct.
| | 01:23 | So I'll click outside the list to close it.
| | 01:25 | In Excel 2003 and earlier versions, the
program stopped checking conditions when
| | 01:30 | it found one that was true.
| | 01:31 | Excel 2010 doesn't stop checking
conditions unless you tell it to.
| | 01:35 | So for example, if you want Excel to
stop checking conditions if a particular
| | 01:39 | condition is true, you can check
that condition Stop If True box.
| | 01:44 | So for several let's say that if the
cell value is greater than 110 and you
| | 01:49 | wanted Excel to stop checking after that,
then you could check the Stop If True box.
| | 01:54 | In this case I don't want to make that
change so I'll clear the box but it's
| | 01:58 | there if you wanted it.
| | 02:00 | You can also change the order in
which Excel applies these rules.
| | 02:04 | So let's say for example that I have the
bottom rule here which is called Top 7.
| | 02:09 | So what it does is it finds the top
seven monthly sales rise within the
| | 02:13 | PivotTable and then it formats the
number within itself as bold and italicized.
| | 02:20 | So now let's say that you want
Excel to check that rule first.
| | 02:23 | To do that you can click the rule to
select it as I've done and then click the
| | 02:28 | Move Up arrow and I'll move
it to the top of the list.
| | 02:31 | So we have one, two, and three clicks and
then that has moved to the top of the list.
| | 02:37 | If you want to move a condition down
then you can click the condition and click
| | 02:41 | the Move Down button.
| | 02:42 | So that let's say that Excel has
discovered a value that's in the top seven and
| | 02:47 | I only wanted to apply this formatting
as opposed to the color fill down below.
| | 02:52 | Now I can check the Stop If
True box and then click OK.
| | 02:57 | When I do, Excel re-applies the
conditional formats as I edited them.
| | 03:02 | You see that the top seven values in
the PivotTable which include 130, 140, 128,
| | 03:07 | and so on, don't have the color fills as
the other cells do; instead they simply
| | 03:12 | have their text in bold and italic type.
| | 03:15 | Managing conditional formats is a
little complicated but that's the price you
| | 03:19 | pay for a lot more flexibility.
| | 03:21 | If you take the time to work with your
rules will discover many benefits to the
| | 03:24 | additional visualizations
offered by Excel's conditional formats.
| | Collapse this transcript |
| Deleting a conditional formatting rule| 00:00 | Conditional formats help you make
judgments about your data quickly, but you
| | 00:04 | might find that one or more of the
rules you created that are no longer useful.
| | 00:07 | If that's the case you can delete
rules individually from the Conditional
| | 00:11 | Formatting Rules Manager or get rid of
them all by using the controls on the
| | 00:15 | Home tab of the Ribbon.
| | 00:16 | If you want to delete a single rule
then on the hometown of the Ribbon click
| | 00:20 | Conditional Formatting
and then click Manage Rules.
| | 00:24 | Then in the Conditional Formatting
Rules Manager dialog box you can select the
| | 00:28 | rule that you want to delete.
| | 00:30 | So for example if we wanted to delete
the rule that identifies the top seven
| | 00:35 | values then I could click it
and then click Delete Rule.
| | 00:39 | You should always verify that any
remaining rules operate the way you want them to.
| | 00:43 | For example, if the rule you got rid
of, how to Stop If True condition, you
| | 00:47 | might have disrupted the rules logic
and cause Excel to apply rules it shouldn't.
| | 00:52 | When you are done managing the rules
inside this dialog box you can click OK
| | 00:55 | to save your changes or Cancel if you
want to close the dialog box without
| | 01:00 | saving your changes.
| | 01:01 | If you want to remove a particular set
of conditional formats then you can do so
| | 01:05 | by once again on the Home tab clicking
Conditional Formatting and then pointing
| | 01:10 | to Clear Rules and when you
do a number of choices come up.
| | 01:15 | Clearing rules from just the
selected cells works as advertised.
| | 01:19 | It removes conditional formats from
just those cells that you've selected.
| | 01:23 | Clearing rules from the entire sheet
removes all conditional formats in the
| | 01:26 | worksheet, not just the PivotTable.
| | 01:28 | When the active cell lies within an
Excel table then clearing rules from this
| | 01:32 | table option deletes
conditional formats from that Excel table.
| | 01:35 | In this case the active cell isn't
within an Excel table so that option
| | 01:39 | is grayed out here.
| | 01:41 | Finally, clearing rules from this
PivotTable removes rules from the PivotTable,
| | 01:45 | but doesn't affect any rules
elsewhere on the worksheet.
| | 01:48 | So for example if I were to click Clear
Rules from this PivotTable, then Excel
| | 01:53 | would remove those rules but wouldn't
affect anything else within the worksheet
| | 01:57 | if other rules were applied.
| | 01:59 | Getting rid of conditional formats you
no longer need reduces the visual input
| | 02:02 | you have to process when you view a PivotTable.
| | 02:05 | If you find the conditional format is
more of a distraction than help, don't
| | 02:09 | hesitate to get rid of it.
| | Collapse this transcript |
|
|
6. Creating and Manipulating PivotChartsCreating a PivotChart| 00:00 | PivotTables help you
summarize large datasets efficiently.
| | 00:03 | But it can be difficult to interpret data
when all you have to go on are the raw numbers.
| | 00:08 | Charts summarize data visually, making
it easier to distinguish groupings and
| | 00:12 | trends in your data.
| | 00:13 | Just as you can create charts based on
regular worksheet datasets, you can create
| | 00:17 | dynamic charts called PivotCharts
from the data contained in PivotTables.
| | 00:22 | There are two ways to create a PivotChart.
| | 00:24 | You can either create a PivotTable and a
PivotChart at the same time or you can
| | 00:28 | create a PivotChart
from an existing PivotTable.
| | 00:31 | To create a PivotTable and a
PivotChart at the same time you need to make sure
| | 00:35 | that your source data such as what I
have here is laid out as a data list or
| | 00:40 | preferably an Excel table.
| | 00:42 | Then on the Insert tab, click the
PivotTable button's down arrow and click PivotChart.
| | 00:50 | When you do, the Create PivotTable
with PivotChart dialog box opens.
| | 00:54 | You can then verify that Excel has
identified the range properly and in this
| | 00:58 | case it is a table named Table 1
and you can choose where to create the
| | 01:03 | PivotTable and PivotChart and I
will create it on a new worksheet.
| | 01:07 | With those selections in place, click OK.
| | 01:09 | After you create your PivotChart, you
can arrange the fields using the controls
| | 01:15 | in the PivotTable Field List task pane
just like you would for a PivotTable.
| | 01:19 | So for example if I want to display
yearly revenues, I can create a column
| | 01:25 | chart doing exactly that.
| | 01:26 | Now I'll show you how to create a
PivotChart based on an existing PivotTable.
| | 01:31 | To do that, you display a
sheet that contains a PivotTable.
| | 01:33 | So I go to Sheet3, click any cell in the
PivotTable, and then you simply create a
| | 01:40 | chart as you would normally in Excel.
| | 01:42 | You click the Insert tab and then
select the type of chart you want to create.
| | 01:46 | In this case I will create a column
chart, so I'll click the Column button and
| | 01:51 | then just create a simple clustered
column, and when I do Excel creates a chart
| | 01:57 | that reflects the organization of the
PivotTable. And again you can pivot the
| | 02:02 | PivotTable to change the PivotChart.
| | 02:04 | So for example, if I pulled the Month
field out of the Axis category's area then
| | 02:10 | I would have a column chart with data
for FirmA and FirmB in 2009 and 2010.
| | 02:16 | Now there are some differences between
regular Excel charts and PivotCharts.
| | 02:20 | The most important ones are that you
can't switch the row and column orientation
| | 02:24 | of a PivotChart by using the
Select Data Source dialog box.
| | 02:28 | That so much of a problem though
because you can always rearrange your data by
| | 02:32 | pivoting the PivotChart.
| | 02:33 | Second, you can't create xy scatter
charts, stock charts or bubble charts, and
| | 02:40 | finally refreshing a PivotChart
removes trend lines, data labels, error bars,
| | 02:44 | and a few other less common settings.
| | 02:47 | If you'd rather have your PivotChart
reside on a separate worksheet from the
| | 02:50 | PivotTable, you can click the
PivotChart and then on the Design contextual tab
| | 02:55 | click the Move Chart button and then
select where you want the chart to go.
| | 03:00 | In this case we'll put it on a new
chart sheet and we'll just leave the names
| | 03:04 | Chart1 and click OK.
| | 03:07 | I personally prefer a charge sheet
because the PivotChart takes up an entire
| | 03:11 | sheet and the larger area makes
the chart easier to understand.
| | 03:15 | Pivot charts enable you to summarize
your data visually providing an overview
| | 03:18 | of your data and opening the door to
insights you might not discover from
| | 03:22 | looking at the raw numbers.
| | 03:23 | You'll find that Pivot charts are
powerful tools that help you analyze your
| | 03:26 | enterprise's data effectively.
| | Collapse this transcript |
| Pivoting a PivotChart| 00:00 | The real power of a PivotChart comes to the
fore when you rearrange your data dynamically.
| | 00:04 | A task that would take several minutes
if done by hand takes just a few seconds
| | 00:08 | when you summarize your data using a PivotChart.
| | 00:10 | So now let's say that you have a
PivotChart and you want to pivot it.
| | 00:14 | You can pivot either the PivotChart
itself or the PivotTable behind it.
| | 00:18 | So let's start out by pivoting the
PivotTable, which is on the left side of the screen.
| | 00:23 | I currently have the data laid out by
Year and Month along the rows, which is
| | 00:27 | along the horizontal axis on this line
chart, and then Company which provides
| | 00:33 | the FirmA and FirmB lines.
| | 00:35 | So there are two separate lines.
Each one represents a single company.
| | 00:40 | If I were to remove Company from the
Column Labels area by dragging it out
| | 00:45 | then Excel creates a chart where it
summarizes all of the data instead of
| | 00:49 | separating it out by company.
| | 00:51 | Now let's say that I want to
create a separate line for each year.
| | 00:56 | So in other words I want to have January,
February, and March and then I want to
| | 00:59 | have two separate lines
representing the years 2009 and 2010.
| | 01:03 | To do that I can drag Year from the
Row Labels area to the Column Labels area
| | 01:08 | and Excel creates a graph
with those two separate lines.
| | 01:11 | If you have your PivotChart on a
separate sheet within your workbook, then you
| | 01:15 | can pivot the PivotChart on its own.
| | 01:18 | So let's say for example that I click
the PivotChart and when I do we still see
| | 01:22 | the PivotTable Field List but now
the names of the areas have changed.
| | 01:26 | So instead of the Column area we have
what's called Legend Fields and then we
| | 01:31 | have Axis Fields, which was
previously the Row Labels area.
| | 01:35 | Every unique value in any field
within the Legend Fields area will have a
| | 01:39 | separate line or bar or
column in the body of the chart.
| | 01:43 | So for example here we have Year and
because I have data from 2009 and 2010,
| | 01:49 | I have two lines, one red, one blue,
summarizing data for 2009 and 2010.
| | 01:55 | Now if I want to pivot the
PivotChart I can do so normally.
| | 01:57 | So for example, if I want to take a
year and put it back in the Axis Fields area,
| | 02:03 | I can do so and create a line
chart which summarizes all revenue for 2009
| | 02:09 | and 2010 by month, and then I can add
Company back to the Legend Fields area to
| | 02:15 | create two separate lines,
one for FirmA and one for FirmB.
| | 02:19 | Also as with a PivotTable you can defer
the layout update so if you have a large
| | 02:24 | PivotChart or a large PivotTable and
it takes time to summarize all the data
| | 02:28 | then you can check it Defer Layout
Update and then make your changes and I'll
| | 02:32 | just change back by removing Company and
putting Year in the Legend Fields area.
| | 02:38 | You'll notice that when I made
those changes the PivotChart and the
| | 02:41 | PivotTable did not change, but when
I click Update, everything updates to
| | 02:46 | reflect my changes.
| | 02:47 | If you want to go back to live
updating to have the PivotChart and
| | 02:51 | PivotTable update as you make your changes
then you can uncheck the Defer Layout Update box.
| | 02:56 | Changing the PivotChart's arrangement
shifts the data's emphasis, enabling you
| | 03:01 | to examine the data from
different perspectives quickly and easily.
| | Collapse this transcript |
| Filtering a PivotChart| 00:00 | PivotCharts can
summarize huge data collections.
| | 00:02 | But many times you want to limit the
data displayed in a given category.
| | 00:06 | Just as you can limit the data displayed
in a PivotTable, you can also limit the
| | 00:10 | data summarized by a
PivotChart by creating a filter.
| | 00:13 | There are three main ways you can
filter the contents of a PivotChart:
| | 00:17 | by selection, by rule and by using a search filter.
| | 00:21 | Filtering by selection means that you
display a list of the values in a field
| | 00:25 | and select the values you want to display.
| | 00:27 | Filtering by rule means that you
create criteria that Excel uses to select
| | 00:31 | which values to display.
| | 00:33 | Finally, creating a search filter
means that you type in a character string
| | 00:37 | that you want to appear in all of the labels
that appear in the body of the PivotChart.
| | 00:42 | You can create filters for a
PivotChart by using the PivotTable Field List
| | 00:46 | and that procedure is exactly the same as if
you were creating a filter for a PivotTable.
| | 00:50 | So let's say for example that I wanted
to create a filter where I only displayed
| | 00:55 | values for January, April, July, and November.
| | 01:00 | To do that I can move the mouse
pointer over the Month field, click the
| | 01:05 | down arrow, and then using the selection list,
I can select then the months that I want.
| | 01:11 | So off Select All and do it for
January, April, July, and November.
| | 01:21 | With those selections in place I can
click OK and Excel updates my PivotChart
| | 01:25 | to use just those values.
| | 01:27 | If I want to remove the filter then I
can move back into the PivotTable Field
| | 01:31 | List, click the down arrow, and
click Clear Filter From "Month".
| | 01:35 | Now let's say that I want to filter by
rule. Say that I want to display results
| | 01:41 | only for quarters three and four.
| | 01:43 | In other words, any quarter
with a number greater than two.
| | 01:45 | To do that, I will pivot the PivotChart
by removing the Month field and adding
| | 01:52 | the Quarter field to the Axis Fields area.
| | 01:56 | So now I have my results by quarter
for each of the years 2009 and 2010.
| | 02:01 | To create that rule I will click the
Quarter field header's down arrow and then
| | 02:06 | point to Label Filters, click Greater
Than, and then I want any quarter with a
| | 02:12 | value greater than 2, so I'll type 2 in
the box to the right, verifying that "is
| | 02:17 | greater than" appears in the Comparison
Operator box. Click OK and Excel updates
| | 02:22 | my PivotChart based on my filter.
| | 02:25 | I will once again clear that filter,
going into the PivotTable Field List box and
| | 02:29 | clicking Clear Filter.
| | 02:30 | Now let's say that I want
to create a search filter.
| | 02:34 | To do that I will bring the month back in.
| | 02:36 | So I'll take Quarter out and add the
Month field back into the Axis Fields area.
| | 02:42 | Now let's say that I want to filter for
any month that includes the letters ER
| | 02:48 | in the name of the month.
| | 02:49 | So to do that, I will click the
Month header's down arrow and then in the
| | 02:54 | search box I'll type ER.
| | 02:57 | When I do Excel updates the
contents of the search list, indicating that
| | 03:01 | September, October, November, and December
will all appear when I create my filter.
| | 03:06 | Everything looks good so I can click OK
and Excel filters the PivotChart, and
| | 03:12 | if you want you can
always create multiple filters.
| | 03:14 | So let's say that I was only
interested in the values for 2010.
| | 03:16 | I can go up to Year, click the down
arrow, and clear the Select All button, and
| | 03:26 | check 2010, click OK, and I see the
results for September, October, November,
| | 03:32 | and December of 2010.
| | 03:34 | Creating filters gives you control over
the values that appear in your PivotChart.
| | 03:38 | When you want to narrow your focus
and examine a subset of your data,
| | 03:41 | PivotChart filters enable you to do just that.
| | Collapse this transcript |
| Formatting a PivotChart| 00:00 | Charts, including
PivotCharts, summarize data visually.
| | 00:03 | So you should pay careful attention
to the appearance of your chart and the
| | 00:07 | elements it contains.
| | 00:08 | If your organization mandates which
color schemes you need to use for external
| | 00:12 | or internal documents, you should also
ensure your PivotCharts conform to those
| | 00:16 | production standards.
| | 00:18 | If you want to change the look of your
PivotChart, then first you should click
| | 00:22 | the PivotChart so that it's active
and then you can make a number of changes.
| | 00:26 | If you want to change your PivotChart's
chart style, you can do that by clicking
| | 00:30 | the Design contextual tab and then
clicking the More button in the Chart Styles
| | 00:35 | gallery and then selecting
the style that you want to apply.
| | 00:38 | In this case, I will select the
Style here, which is called Style 42.
| | 00:44 | When I click it, Excel updates
the formatting of the PivotChart.
| | 00:48 | You can also format individual chart elements.
| | 00:50 | So let's say for example that I
want to change the formatting of the
| | 00:53 | title, which is Revenue.
| | 00:55 | I move my mouse pointer over that
element and when I do, Excel displays the
| | 01:00 | Chart Title screentip indicating that
I'm currently hovering over that item and
| | 01:05 | when I click it, it's selected and then
I can go to the Format contextual tab,
| | 01:10 | and then use the controls to change
the formatting of this particular item.
| | 01:15 | So for example, I can make it a WordArt
style by clicking the Styles gallery's
| | 01:19 | More button and then selecting one of
the existing styles, and I will select the
| | 01:24 | Fill - White with a Drop Shadow.
| | 01:27 | If you want, you can also select a
chart element using the Chart Elements list.
| | 01:32 | To do that you go to the Format
contextual tab and then in the Current
| | 01:36 | Selection area click the Chart Elements
box's down arrow, and select the item you want.
| | 01:42 | In this case I'll click the chart area.
| | 01:45 | Now to change the formatting of the
chart area I can use the controls again on
| | 01:48 | the Format contextual tab. And in this
case I will change the Shape Style so
| | 01:53 | that it has a red border and a white interior.
| | 01:56 | With that in place, I'll
click the style to apply it.
| | 01:59 | Now let's say that you want to format a
single data point within your chart, so
| | 02:04 | let's say that I want to format this
column, which is for FirmB 2009 April.
| | 02:11 | The first thing I'll do
is click the element once.
| | 02:13 | Doing so selects all of the
data within that data series.
| | 02:18 | If I click it again, then Excel selects that
item by itself and I can change its formatting.
| | 02:25 | So let's say that I want to
change the color of the fill.
| | 02:28 | To do that I can click the Shape Fill
button and then select a new color and
| | 02:33 | in this case I will make it yellow.
| | 02:36 | Now one thing you should note is
that like other formatting elements the
| | 02:39 | colors you've selected might be affected if you
apply a different Office theme to your document.
| | 02:43 | So for example, the color that I
selected was yellow and if I reopen the Shape
| | 02:47 | Fill palette, you'll see that
yellow is in the Standard Colors area.
| | 02:52 | That means that it won't be
affected if I change the Office theme.
| | 02:55 | If I'd selected one of the colors from
the Theme Colors area, then it would change.
| | 02:59 | So let me show you what happens when I
change the Office theme for this workbook.
| | 03:03 | To do that, I go to the Page Layout tab,
click the Themes button, and then select
| | 03:09 | the new theme that I want, and
in this case I'll call it Equity.
| | 03:13 | You can see that the rest of the data
columns have changed, but the yellow has not.
| | 03:20 | Formatting PivotChart elements helps
communicate important trends clearly.
| | 03:24 | Whether you use formatting to provide
an overall look and feel or to call out
| | 03:27 | individual data points, you should
take the time to experiment with different
| | 03:31 | formatting options and see which
works the best for you and your audience.
| | Collapse this transcript |
| Changing a PivotChart's layout| 00:00 | When you create a PivotChart, Excel
uses a basic layout to determine when and
| | 00:04 | where to display elements.
| | 00:06 | Those elements can include the chart
title, a legend representing the data
| | 00:09 | series, and axis titles and labels.
| | 00:13 | The basic layout Excel uses is
probably good enough for a quick look at your data,
| | 00:16 | but you will most likely want to
change how your PivotChart's elements are
| | 00:20 | arranged before you share
your chart with your colleagues.
| | 00:23 | For example, Excel comes with a gallery
of chart layouts from which to choose.
| | 00:27 | You can display those layouts by
clicking your PivotChart and then on the
| | 00:32 | Design contextual tab, clicking the
More button at the bottom right corner of
| | 00:36 | the Chart Layouts gallery.
| | 00:38 | Then you can click the
layout that you want to apply.
| | 00:41 | For this example I'll just click the
top left, which is layout number one, and
| | 00:46 | when I do Excel changes the chart's layout.
| | 00:49 | Even if the layout you choose includes
elements such as a title or legend, those
| | 00:54 | items either won't appear or will
display a placeholder, in this case Chart Title,
| | 00:59 | if the PivotChart's arrangement
doesn't supply a value for that element.
| | 01:03 | In this example, I'm summarizing
the data using a column chart and have
| | 01:07 | company in the Axis Fields area in here
in the Legend Fields area, the default
| | 01:11 | PivotChartLayout won't display a chart title
unless I created it myself. So how to do that?
| | 01:17 | Well, to edit your chart element, you
can click it to select it and then you can
| | 01:23 | move the mouse pointer over the edge of
the item and then drag it to any place
| | 01:26 | that you like on the PivotChart, and
then if you want to undo something, you can
| | 01:30 | just press Ctrl+Z and I'll press Ctrl+Z
again to put the chart title back in its
| | 01:35 | original place, and then you can edit
the text by selecting the text and then
| | 01:41 | changing it to Company by Year, and then
if you click the item to select it you
| | 01:48 | can use the tools on the Home tab
to change the formatting of the text.
| | 01:53 | So let's say for example that I want to make
the text a little bit larger. Make it 24 point.
| | 01:58 | I can do that using the tools that you're
familiar with from your regular use of Excel.
| | 02:03 | You can also delete a chart element and
while I have my chart title selected if
| | 02:07 | I press the Delete key, it goes away.
| | 02:10 | However, if I reapply the layout I
had before by clicking Chart Layouts in
| | 02:15 | the Layout 1, then the chart title element
comes back, but the previous value is lost.
| | 02:21 | If you want more fine-grained control
over your PivotChart's layout, then you
| | 02:24 | can click the PivotChart and then click
the Layout contextual tab on the Ribbon,
| | 02:29 | and doing so allows you to change
many different aspects of your chart.
| | 02:33 | For example, you can add
axis titles to the PivotChart.
| | 02:37 | To do that you click Axis Titles button,
which is here, and then select whether
| | 02:42 | and how to display the
Horizontal or Vertical Axis Title.
| | 02:46 | So we'll look at the Horizontal Axis
and then you can either display it as None
| | 02:51 | or the Title Below the Axis.
| | 02:53 | I'll choose that second option, and
you see that we have an Axis Title and in
| | 02:57 | this case that is the year.
| | 02:59 | So I will select the text inside
the chart element and type year.
| | 03:05 | You should take the time to experiment
with the built-in layouts available for
| | 03:08 | you to use with Excel PivotCharts.
| | 03:10 | You might discover that most of
your design work has already been done.
| | Collapse this transcript |
| Changing a PivotChart's chart type| 00:00 | Excel lets you create PivotCharts that
summarize your PivotTable data visually.
| | 00:04 | You can often use more than one type
of chart to summarize a dataset so you
| | 00:08 | might want to change your PivotChart's type.
| | 00:10 | I'll show you the procedure to
change a PivotChart's chart type and in
| | 00:14 | doing so, I'll give you an overview
of the different chart types that are
| | 00:17 | available within Excel 2010.
| | 00:19 | First, I should point out that you
can't create an XY scatter chart, a stock
| | 00:23 | chart or a bubble chart using PivotTable data.
| | 00:27 | This first chart type, which is the
column chart, displays data as vertical
| | 00:31 | columns organized by category and
these charts are useful for showing data
| | 00:36 | changes over a period of time or for
illustrating comparisons among items.
| | 00:40 | Now let's say that I want to
change this chart to a line chart.
| | 00:43 | To do that I'll click the PivotChart and
then on the Design contextual tab click
| | 00:49 | the Change Chart Type button, and then
I can click the type of chart I want to
| | 00:53 | create and I'll click the first line
subtype, and with that selection in place
| | 01:00 | I'll click OK, and Excel creates
a new graph based on my choice.
| | 01:06 | Line charts display continuous data
over time which is perfect for showing
| | 01:09 | trends in data as long as the
data was captured at equal intervals.
| | 01:13 | Comparing a month sales to a year sales doesn't
make sense within the context of a line chart.
| | 01:18 | Pie charts show the share of a total
contributed by individual data values in
| | 01:22 | a single data series.
| | 01:24 | So let's change this chart to a pie
chart. So on the Design contextual tab
| | 01:29 | Change Chart Type, click Pie, and I'll
use the first pie subtype, and click OK.
| | 01:36 | Pie charts show the share of a total
contributed to by the individual data
| | 01:40 | elements in the series.
| | 01:41 | If you have more than one data series
in your PivotTable, Excel displays just
| | 01:45 | the first one in your PivotChart.
| | 01:47 | So let's say for example that I have
two companies, FirmA and FirmB, and I will
| | 01:51 | add those companies to the Legend Fields area.
| | 01:55 | In this case, Excel only shows FirmA.
| | 01:57 | If I were to add the Company field to
the Axis Fields area though by dragging
| | 02:02 | from the Legend Fields to the Axis
Fields area, then Excel would create four
| | 02:06 | different divisions: 2009 FirmA,
2009 FirmB, and the same for 2010.
| | 02:13 | Now I'll remove company from the
PivotChart to go back to our simple data type
| | 02:18 | and I'll create a bar chart.
| | 02:20 | So click the PivotChart and the then on
Design tab, Change Chart Type and then click Bar.
| | 02:26 | I'll use the first type and click OK.
| | 02:29 | Bar charts which display values as
horizontal bars are perfect for summarizing
| | 02:33 | data when the axis labels are long and
the values that are shown are durations.
| | 02:38 | So even though I have sales data here,
most of the time you'll use bar charts
| | 02:43 | for times, so for
example in project management.
| | 02:46 | The next type of chart I'd like to show
you is what's called an area chart, so I
| | 02:51 | will change the PivotChart to that type,
click OK, and what you see is an area
| | 02:58 | chart that instead of a line chart, the
entire body of the graph is filled in.
| | 03:04 | So in this case we have
the total from 2009 to 2011.
| | 03:07 | Now let me show you what it looks
like when I add company back to the
| | 03:10 | Legend Fields area.
| | 03:13 | So I'll drag the Company field there.
| | 03:15 | What this area chart does is
emphasize the magnitude of change over time,
| | 03:19 | and also how much each data element
contributes to the total for a given measurement.
| | 03:24 | Now the final two chart types that I'd
like to cover don't work extremely well
| | 03:28 | with the data that I have in this
PivotChart, so bear with me and I'll just
| | 03:31 | explain the concepts.
| | 03:33 | The next type of chart is
what's called a surface chart.
| | 03:35 | So I'll change the data type to Surface
and I'll just select the first one.
| | 03:41 | Surface charts are used
mostly for scientific data.
| | 03:44 | You can use surface charts to find the
optimum combination of two datasets such
| | 03:48 | as comparing rainfall and the crop production.
| | 03:51 | Now finally, radar charts enable
you to compare the aggregate values of
| | 03:55 | several data series.
| | 03:56 | So I will change the type to a radar
chart and click OK and then I will add
| | 04:05 | Company to the Axis Fields area, so
you can see the relative contribution for
| | 04:11 | 2009 from these revenue, 2010 FirmA,
FirmB in 2010, and FirmA in 2009.
| | 04:19 | Selecting the best PivotChart type to
summarize your data will add clarity
| | 04:22 | to your presentations.
| | 04:23 | You should take the time to study the
type of data you're analyzing and use that
| | 04:27 | information to select the
most appropriate PivotChart.
| | Collapse this transcript |
| Adding a trendline to a PivotChart| 00:00 | When you analyze data using a
PivotChart, you must pay close attention to the
| | 00:03 | individual values in your datasets.
Even so, it's often beneficial to take a
| | 00:08 | step back and look at the
overall trends in your data.
| | 00:11 | You can enhance your analysis by
projecting future values, assuming current
| | 00:15 | trends stay constant, by adding
a trend line to your PivotChart.
| | 00:19 | To create a PivotChart trend line, you
can click the PivotChart and then on the
| | 00:24 | Layout contextual tab, click the
Trendline button and then select the type of
| | 00:29 | trend line that you want to create.
| | 00:31 | So in this case I'll click a Linear
Trendline, which creates a line that best
| | 00:37 | fits the data that's
displayed within the PivotChart.
| | 00:41 | Now the trend line only exists within
the chart and the Excel program memory.
| | 00:44 | It doesn't actually add data to your
worksheet, and I should also point out that
| | 00:49 | Excel uses linear regression
techniques to create the trend line.
| | 00:52 | These techniques are also
implemented in the Forecast function.
| | 00:55 | So you can generate the same results for
data you don't summarize within a PivotChart.
| | 00:59 | Now one other note is that you should
always use Linear for your Forecast type,
| | 01:05 | unless you know that you need
to use another type of trend line.
| | 01:08 | Most of the time you'll only use the
other types of regression for scientific
| | 01:12 | and engineering work and you'll
know when you need to use them.
| | 01:15 | If you'd like more control of your
trend line, make sure that the chart is still
| | 01:18 | selected and then on the Layout tab
click the Trendline button again, and then
| | 01:24 | click More Trendline Options.
| | 01:27 | When you do, the Format
Trendline dialog box opens.
| | 01:30 | One of the more common changes that I
make is to extend the trend line by four
| | 01:34 | periods to take it out into the future.
| | 01:36 | So let's say for example that I have a
Linear Trendline, which I do, and I want
| | 01:41 | to create what's called a Forecast to
look forward, and in this case I want to
| | 01:46 | go from zero periods to four periods,
and in this case each of my periods is,
| | 01:52 | if you look back at the chart, 1 month.
| | 01:56 | So with that change in place, I can
click Close and a trend line looking ahead
| | 02:00 | four months appears in the body of
the PivotChart. Please note though, that
| | 02:04 | the farther out you make your
forecasts, the more likely they are to be
| | 02:07 | inaccurate, but even so, a
trend line gives you a basis for comparison.
| | 02:12 | Trend lines help you
visualize future trends in your data.
| | 02:15 | A PivotChart can't replace detailed
analysis, but they do help to set the stage
| | 02:19 | for your presentations regarding that data.
| | Collapse this transcript |
|
|
7. Printing PivotTables and PivotChartsPrinting a PivotTable| 00:00 | When you create a PivotTable position
that illustrates a point well, you can
| | 00:03 | print the PivotTable and
include it in a physical document.
| | 00:07 | In this movie I'll show you
how to print your PivotTables.
| | 00:10 | First you can preview how your
PivotTable will look when you print it by going
| | 00:14 | to the Backstage view.
| | 00:15 | To do that click the File tab which
displays the Backstage view and then click
| | 00:21 | the Print item to display the Print page and on
the right side of that page is a print preview.
| | 00:27 | So you can see the PivotTable and also
the data comment that is over to the side,
| | 00:33 | which would also be printed.
| | 00:34 | So let's say that I just want to print the
PivotTable and not the comment over on the side.
| | 00:39 | To do that, I click the Home tab at the
Ribbon, then click the Options tab, and
| | 00:46 | then click the Select button,
and click entire PivotTable.
| | 00:50 | So what I've just done is
selected the entire PivotTable.
| | 00:54 | Now I can go to the Page Layout tab, click
the Print Area button, and click Set Print Area.
| | 01:02 | When I do, and I'll just click away
from the selected cells to release the
| | 01:05 | selection, you can see that Excel has
dashed lines around the area that was
| | 01:10 | selected, indicating it's a print area.
| | 01:12 | In fact if we go back to the Backstage
view and the Print page, you can see that
| | 01:18 | the comment text over on the side
will no longer be printed because it was
| | 01:22 | outside of the print area.
| | 01:23 | So now let's switch back into the
document proper and I'll just click the Home tab.
| | 01:28 | Now let's say that I want to have the
titles appear on each printed page and let
| | 01:33 | me show you what I mean.
| | 01:34 | First I'll create a page break here on
row 18 and my goal is to split the values
| | 01:40 | from 2009 and 2010 onto
two separate printed pages.
| | 01:45 | So I've clicked the row header and then
I'll go to Page Layout, click Breaks, and
| | 01:50 | then click Insert Page Break. And when I
do you can see that there will now be a
| | 01:55 | page break between December 2009 and
the summary for 2010 and if we go to the
| | 02:01 | Backstage view, File > Print, you can see
that we have the values on page 1, that's
| | 02:08 | 2009, and then if I go to the next
page, you can see the values for 2010.
| | 02:13 | But what you also see are that there
are no headers for 2010. So for example, I
| | 02:20 | might not remember that the first column
is FirmA and the second column is FirmB
| | 02:24 | because of those labels don't appear.
| | 02:26 | So what I'll do now is set up my
worksheet so that those labels will appear when
| | 02:31 | I print the PivotTable.
| | 02:33 | To do that I'll go back into the main
document and I'll just click the Home tab
| | 02:37 | and then right-click any cell in the
PivotTable and click PivotTable Options.
| | 02:43 | Then on the Printing page of the dialog box
check the Set print titles box and then click OK.
| | 02:52 | Now when we go back to the
Backstage view and preview our print job to come,
| | 02:56 | we'll see that on the first
page we have our headers and then on page 2
| | 03:01 | they appear as well.
| | 03:03 | Now there's one other option I'd like
to show you when it comes to printing
| | 03:06 | PivotTables and that is that you can turn
the expanding contract controls on or off.
| | 03:11 | In other words you can print them or not.
| | 03:13 | So I'll click the Home tab again to go
back into the PivotTable, right-click any
| | 03:18 | cell within the PivotTable, click
PivotTable Options again, and then on the
| | 03:23 | Printing page you can select whether to
print to the expand and collapse buttons
| | 03:27 | that are displayed on the PivotTable.
| | 03:29 | In this case it's turned off, which is
usually the better choice, but I'll just
| | 03:32 | show you what they look like
when you have them turned on.
| | 03:35 | So I'll check that box, click OK, and
then go back to print preview and you can
| | 03:42 | see that the button will
appear when you print the worksheet.
| | 03:47 | Printing a PivotTable seems like a
straightforward operation and it usually is.
| | 03:51 | That said, you do have a number of options
when it comes to printing your PivotTable.
| | 03:55 | You should experiment with the
different ways you can control how your
| | 03:58 | PivotTables print, so you can
get exactly the output you want.
| | Collapse this transcript |
| Printing each item on its own page| 00:00 | When you create a PivotTable, it's
likely that you'll have data for many
| | 00:03 | categories grouped
together into a single display.
| | 00:06 | If that's the case, it might make sense to
print your data on a number of worksheets.
| | 00:10 | There are two ways that you can
separate your PivotTable data.
| | 00:13 | The first is by using the fields in
the Row Labels area and the second is by
| | 00:17 | using fields in the Report Filter area.
| | 00:20 | If your PivotTable has more than one
field in the Row Labels area, you can have
| | 00:24 | Excel print each group on a new page.
| | 00:26 | For example, in this worksheet I have
data from 2009 and 2010 and let's say that
| | 00:33 | I want it to print 2009 and
2010 on separate worksheets.
| | 00:37 | To do that, you right-click any cell in
the label field by which you want to separate.
| | 00:43 | In other words I want to separate by
year so I'll click one of my two year
| | 00:46 | fields, in this case right clicking
2009, and in the shortcut menu that appears
| | 00:51 | click Field Settings.
| | 00:54 | In the Field Settings dialog box the
setting that we're interested in is on the
| | 00:58 | Layout & Print page and at the bottom of
this page you can check the Insert page
| | 01:04 | break after each item box and then click OK.
| | 01:08 | You don't see anything on the screen,
but if you go to Backstage view by
| | 01:12 | clicking the File tab and then click
Print, you can see that 2009 will be printed
| | 01:18 | on one page and then if we scroll to
the next page, 2010 will be printed there.
| | 01:24 | Now click any tab on the Ribbon to switch back.
| | 01:26 | Now let's say that you create a filter
using the Report Filter area, which I'll
| | 01:31 | do by dragging the Quarter field
down to the Report Filter area.
| | 01:35 | So now I have the ability to filter
the data that appears in the PivotTable.
| | 01:40 | In this case, however, what I want to do
is to print data from quarter number one
| | 01:44 | on one page, quarter number
two on another page, and so on.
| | 01:48 | So the first thing I'll do, and this
is just personal preference, but I'm
| | 01:52 | going to remove the print area that's been set,
so that I have 2009 and 2010 on the same page.
| | 02:00 | In other words I don't want to
have two settings at the same time.
| | 02:03 | So I will right-click a value in the
Year field, click Field Settings, go to
| | 02:08 | the Layout & Print page, and clear
the Insert page break after each item
| | 02:13 | checkbox and click OK.
| | 02:15 | Now with any cell in the PivotTable
selected, I can go to the Options contextual
| | 02:20 | tab and then in the PivotTable group I
click the Options button's down arrow and
| | 02:26 | then click Show Report Filter pages.
| | 02:29 | In the dialog box that appears I verify
that the quarter field appears and that
| | 02:33 | is the report filter page
that I want and click OK.
| | 02:38 | When I do, Excel creates
four different worksheets,
| | 02:40 | one displaying data from quarter one,
the second quarter two, three and four, and
| | 02:45 | you can see those new worksheets down
here on the tab bar with worksheets one,
| | 02:49 | two, three and four.
| | 02:50 | Now let's say I want to
print all of those worksheets.
| | 02:53 | To do that, I select sheet number one and
then holding down the Shift key I click
| | 02:58 | the tab for sheet number four.
| | 03:00 | Then if I go to the Print page of the
Backstage view and print my workbook, these
| | 03:06 | four worksheets will all be printed.
| | 03:08 | The built-in Excel PivotTable
functionality enables you to separate your data
| | 03:12 | when you print without necessarily
separating the data in the workbook.
| | 03:16 | Creating new worksheets for each
item in the Report Filter area does add
| | 03:19 | worksheets to your workbook, but
setting the Insert page break after each item
| | 03:23 | option enables you to separate those
categories of data without affecting your
| | 03:27 | PivotTable's organization.
| | Collapse this transcript |
| Printing a PivotChart| 00:00 | When you are ready to print a PivotChart,
you can use the same skills you have
| | 00:03 | used to print regular charts.
| | 00:05 | In this lesson I will review some of
the techniques that you can use to make
| | 00:08 | printing your PivotChart go smoothly.
| | 00:10 | There are two scenarios for printing a
PivotChart. The first is if the PivotChart
| | 00:14 | is part of a worksheet and the
second is if it's on its own chart sheet.
| | 00:19 | If the PivotChart is part of your
worksheet then click the PivotChart and then
| | 00:24 | click the File tab to get to
Backstage view and click Print.
| | 00:28 | When you do, the Print page of the
Backstage View appears and you will see under
| | 00:32 | Settings that Print Selected Chart is
automatically selected and in fact if you
| | 00:39 | click the button, that allows you to
select which part of the work we should print.
| | 00:42 | The only one that's
available is Print Selected Chart.
| | 00:45 | So now you can select your printer and
click the Print button. But now let's say
| | 00:51 | that the PivotChart is on its own
chart sheet. So I will click the Home tab on
| | 00:56 | the Ribbon to move back and then I
will shift to the chart sheet named Chart1
| | 01:02 | and here we see another
PivotChart that's on its own sheet.
| | 01:05 | Now if you click the File tab and click
Print you will see that have a few more options.
| | 01:11 | For example, if you click the Settings
button, you see that you can print the
| | 01:17 | entire workbook and not just the chart.
| | 01:20 | As you can see in the Print Preview
there is a difference between printing a
| | 01:23 | PivotChart on a worksheet and a
PivotChart on its own chart sheet.
| | 01:27 | In the first case, the chart prints at
the same size at which it's displayed.
| | 01:31 | On a chart sheet the chart takes up the
entire sheet and prints as a full page.
| | 01:36 | Printing charts to paper produces copies
you can include in annual reports, news
| | 01:40 | releases, and correspondence.
| | 01:42 | You can also choose to print to an
Adobe Portable Document Format file or other
| | 01:46 | electronic file format, which enables you
to take a copy of the chart with you as
| | 01:50 | you travel, without taking the
original workbook from which you printed it.
| | Collapse this transcript |
|
|
8. Manipulating PivotTables Using MacrosRecording and reviewing a macro| 00:00 | When you work with PivotTables you will
often find that you want to recreate a
| | 00:03 | particular configuration.
| | 00:05 | You could write down the fields in the
order in which they appear, but it's far
| | 00:08 | easier to record a macro that
configures a PivotTable for you.
| | 00:12 | So I have a PivotTable that contains
sales data for FirmA and FirmB and it's
| | 00:17 | monthly for the years 2009 and 2010
and I also have the Quarter field up here
| | 00:24 | in the Report Filters area and my goal is to
recreate this configuration by using a macro.
| | 00:29 | So the first thing we need to
do is start recording the macro.
| | 00:32 | To do that we click the View tab on the
Ribbon, click the Macros button's down arrow,
| | 00:38 | and then click Record Macro and
I will type in a macro name and I will
| | 00:43 | just call it FirstPosition and now
I will click OK and Excel will start
| | 00:51 | recording my actions.
| | 00:53 | The first step to recording a
PivotTable macro in Excel is to clear the entire
| | 00:57 | PivotTable and to recreate it from
scratch. So in this case, with a cell in
| | 01:02 | the PivotTable selected, I will click the
Options tab, click Clear, and then click Clear All.
| | 01:10 | Now I can recreate the PivotTable from
the start. So I will add the Year field
| | 01:15 | to the Row Labels area, Month below
that, Company in the Column Labels area,
| | 01:23 | Quarter in the Report Filter area, and
then Revenue in the Values area, and I have
| | 01:29 | the same configuration
that I had when I started.
| | 01:31 | Now to stop recording, click the
View tab on the Ribbon, click the Macros
| | 01:36 | button's down arrow, and click Stop Recording.
| | 01:39 | So now we have a macro that will
recreate this PivotTable position.
| | 01:44 | If you want to view the macro, you can do
so by opening it in the Visual Basic Editor.
| | 01:48 | So to do that from the View tab of
the Ribbon, click the Macros buttons down arrow,
| | 01:53 | click View Macros and then in
the Macro dialog box make sure that the
| | 01:59 | macro you want to see is displayed
and highlighted and then click Edit.
| | 02:05 | When you do, the macro opens
inside of the Visual Basic Editor.
| | 02:10 | So I will double click to maximize this
window within the Visual Basic Editor.
| | 02:15 | So as you can see there is a lot going
on when you create a PivotTable.
| | 02:20 | This first line, Range("B6").Select,
isn't actually necessary. It is just that I
| | 02:25 | clicked another cell inside the
PivotTable to ensure it was active. It won't
| | 02:28 | hurt the running at all.
| | 02:30 | Then secondly, you can see all of the
actions that I took with regards to the
| | 02:34 | PivotTable. So first I cleared the
table and then I added the field here to the
| | 02:40 | RowField area in position 1, the field
Month to the RowField area in position 2,
| | 02:46 | then I added Company to the
ColumnField in position 1 and then Quarter in
| | 02:52 | the PageField area in position 1, and
then the Revenue field in the Revenue area,
| | 02:58 | and it uses the SUM summary operation.
| | 03:02 | So there is a lot going on with
PivotTables and anytime that you want to work
| | 03:06 | with PivotTables and use macros, you
should never try to create these things by hand.
| | 03:10 | There is just too much going on
and it will take you too long and it's way
| | 03:14 | too likely that you will make a mistake.
| | 03:16 | Always record your macro.
| | 03:18 | What I can do, now that I have recorded
the macro, is go back into Excel and to
| | 03:23 | do that just go up to the File menu
and click Close and Return to Microsoft
| | 03:28 | Excel and then because there is macro
inside of this file, we need to save it as
| | 03:33 | a macro-enabled file.
| | 03:35 | To do that click File and then Save
As and then we can keep the same
| | 03:41 | RecordMacro name, but we need to change
the tip. So click the Save As type button
| | 03:46 | and then from the list that appears, click
Excel Macro Enabled Workbook and then click Save.
| | 03:55 | Now your macro will be
ready whenever you want it.
| | 03:57 | The macro recording skills I have
taught you in this movie apply to any Excel
| | 04:01 | operation, not just PivotTables.
| | 04:03 | But because PivotTable macros are
extremely complicated, it is easiest to record
| | 04:07 | and use them instead of
writing them from scratch.
| | Collapse this transcript |
| Running a macro| 00:00 | Excel macros let you perform
complicated tasks such as manipulating PivotTables
| | 00:05 | quickly by recording the steps
and playing them back when needed.
| | 00:09 | In this movie, I will show you several
ways run your macros so you can pick the
| | 00:12 | one that works best for you.
| | 00:14 | I have started with just a blank
workbook open and the reason for that is that I
| | 00:19 | want to show you what can happen if you
open a workbook that contains a macro.
| | 00:24 | To do that, I will trust Ctrl+O to
display the Open dialog box and then I will
| | 00:30 | navigate to my Exercise Files folder,
which is on my Desktop, and then under
| | 00:35 | Chapter 8, I will double-click Running, which
is the sample file we will use for this movie.
| | 00:41 | When I double-click the file to open it,
you might see a security warning bar,
| | 00:45 | indicating that macros have been disabled.
| | 00:48 | You then have two options: either to
enable the content or to click the Close
| | 00:52 | button to close the message
bar without enabling macros.
| | 00:56 | In this example I do want to use the
macros so in a moment we'll click of the
| | 01:00 | Enable Content button.
| | 01:02 | But before that, if you don't see this
bar I want to tell you how you can enable
| | 01:06 | macros from within the Trust Center
dialog box, because what probably happened,
| | 01:12 | if you didn't see the security warning
bar come up, it means that macros have
| | 01:16 | been turned off entirely for this
installation of Microsoft Excel and if that's
| | 01:21 | the case and you want to
run macros here's what you do.
| | 01:24 | You click the File tab to go to the
Backstage View and then click Options.
| | 01:30 | Then in the Excel Options dialog
box, click Trust Center and then click
| | 01:35 | Trust Center Settings.
| | 01:37 | In the Trust Center dialog box, click
Macro Settings and here you can select what
| | 01:43 | you want to do with your macros.
| | 01:44 | At present I have my Macro Security
Setting at Disable all macros with
| | 01:50 | notification. As we saw earlier that
gives me the option to allow macros to run.
| | 01:55 | If you didn't see the security warning
bar and you're having trouble running
| | 01:59 | macros, then your macro's security
might be set to Disable all macros without
| | 02:03 | notification or Disable all
macros except digitally signed macros.
| | 02:08 | So if you didn't see a message bar
then you need to come into this dialog box
| | 02:13 | and set your Security to
Disable all macros with notification.
| | 02:17 | If you're in a corporate or enterprise
environment then please make sure that
| | 02:21 | making that change is
okay with your IT department.
| | 02:24 | Let them know that you are working
through this course and the workbook contains
| | 02:29 | macros and is known to contain macros.
| | 02:31 | If they're okay with it or if they will
do it for you, go ahead and change your
| | 02:35 | Macro security setting.
| | 02:37 | So I have mine where I need it so I
will click Cancel twice to close out of
| | 02:42 | the Excel Options dialog box and I can click
the Enable Content button to enable macros.
| | 02:49 | Now there are several ways to
run a macro in Microsoft Excel.
| | 02:52 | The first is from within the Macros dialog box.
| | 02:56 | To view the Macros dialog box click the
View tab and then at the far right end
| | 03:01 | you'll see the Macros button.
| | 03:03 | Click the down arrow and then click View Macros.
| | 03:07 | When you do, you see the macros that
are available inside of your workbook.
| | 03:11 | If you want to run a macro then
just click the name and then click Run.
| | 03:17 | The macro that I just created
rearranged the contents of this PivotTable.
| | 03:22 | Another way to run a macro
is to assign it to a shape.
| | 03:26 | So let me go to the Insert tab,
click Shapes, and then I'll just create a
| | 03:31 | rounded rectangle and I will make a
small button here and then I'll type some
| | 03:37 | text to put in the middle, "Macro launch", and
then right-click the Shape and click Assign Macro.
| | 03:45 | This displays the Assign Macro
dialog box and you can select any of the
| | 03:49 | macros that you want.
| | 03:51 | In this case I want to do Company by
Quarter and Year and then click OK.
| | 03:57 | Now if I click away from the button to
release the selection and move my mouse
| | 04:01 | pointer over the button, you'll see that
the mouse pointer changes to a pointing hand.
| | 04:06 | It's the same pointing hand you might
see if you hover your mouse pointer over a
| | 04:10 | hyperlink such as one that leads to a
webpage. That's because clicking this
| | 04:14 | button or a hyperlink will lead to an action.
| | 04:17 | So the pointing hand means that
something is about to happen, whether it's going
| | 04:21 | to another webpage or running a macro.
| | 04:23 | In this case if I click the button,
Excel runs the macro that I assigned to it.
| | 04:29 | Now the final way that I'm going to
show you to run a macro is by creating a
| | 04:32 | button for it on the Quick Access Toolbar.
| | 04:34 | To do that click the Customize Quick
Access down arrow, then click More Commands.
| | 04:41 | Then on the Quick Access Toolbar page
of the Excel Options dialog box in the
| | 04:45 | Choose Commands From list, click
its down arrow and then click Macros.
| | 04:51 | Doing so will list the macros that
are available to you in this workbook.
| | 04:55 | In this case I want to do Year_Month_Company.
| | 04:59 | So I will click it and click Add and
then after you add the button you can
| | 05:03 | change its appearance.
| | 05:05 | Make sure that the macro is selected
over on the Quick Access Toolbar side of
| | 05:09 | the pane, click Modify, and then
select the button that you want.
| | 05:14 | In this case I will apply a Play button.
| | 05:18 | Everything looks good and I will leave
the display name the same and click OK.
| | 05:23 | Now when I click OK, Excel adds the
button to the Quick Access Toolbar.
| | 05:28 | When I click it, Excel runs the macro.
| | 05:31 | If you run a macro occasionally or you
don't want your colleagues running it out of
| | 05:35 | curiosity, you might find the best
solution for your application is to run the
| | 05:39 | macro from the Macro dialog box.
| | 05:41 | If you build a worksheet that relies on
a macro to update the values or to write
| | 05:45 | values in other worksheet, then
attaching the macro to a shape might be best.
| | 05:49 | If you run several macros frequently,
then adding them to the Quick Access
| | 05:53 | Toolbar makes them available
without cluttering your display.
| | Collapse this transcript |
| Creating a simple PivotTable presentation kit| 00:00 | PivotTables make it easy to
emphasize aspects of your data whether that's
| | 00:04 | monthly performance or sales to a customer.
| | 00:06 | The problem with PivotTables is
that it can be hard to remember how to
| | 00:09 | reconstruct a position during a presentation.
| | 00:13 | Having detailed notes helps, but
you still have to take the time to
| | 00:16 | manipulate your worksheet.
| | 00:17 | In this movie I will show you how to
create a simple Excel presentation kit that
| | 00:21 | uses macros to recall positions.
| | 00:23 | I have decided to run my macros by
clicking shapes that I have positioned in row one
| | 00:27 | but you can just as easily add
macro buttons to the Quick Access Toolbar.
| | 00:32 | I've already recorded and
assigned macros for the Year_Month and
| | 00:36 | Quarter_Company shapes.
| | 00:38 | So now what I want to do is record a
macro for the company by month and year
| | 00:42 | shape and then assign it.
| | 00:44 | To record that macro I'll go to the View
tab and then at the right end, click the
| | 00:50 | Macros button's down arrow
and then click Record Macro.
| | 00:55 | Then in the Record Macro dialog box I
can type in the macro name and in this case,
| | 01:00 | I will make it exactly the same as
what appears in the shape. So Comp_Mnth_Year.
| | 01:09 | Now I can click OK and start recording.
| | 01:13 | My first step will be to clear
everything from the body of the PivotTable.
| | 01:18 | So I will click Options and the
Options contextual tab appears because a cell
| | 01:23 | within the PivotTable is currently selected.
| | 01:25 | then click Clear and Clear All.
| | 01:28 | Now with the PivotTable back to zero I
can create the position I want and in
| | 01:33 | this case that will be Company then
Month below that in the Row Labels area,
| | 01:40 | then Year in the Column Labels
area and Revenue in the Values area.
| | 01:47 | That's the position I want.
| | 01:49 | So I can stop recording and to do
that, click the View tab, click the Macros
| | 01:54 | button's down arrow, then click Stop Recording.
| | 01:58 | Now I can attach the macro to the shape.
So I will right-click the shape I want,
| | 02:04 | click Assign Macro ,and then click
the macro name that I want to apply and
| | 02:08 | that's the top one, Company by Month
and Year, and when I click OK the shape is
| | 02:14 | assigned to the macro.
| | 02:15 | Now when I am giving my
presentation and I want to recall a particular
| | 02:19 | PivotTable position I can just
click the shape that it applies to.
| | 02:23 | So for example, if I want to display the
Year by Month position I can just click
| | 02:27 | that shape and it appears. Quarter
Company, there it is, and Company by Month and
| | 02:33 | Year and also there is nothing
about this position that's magical.
| | 02:38 | In other words, I can manipulate the
PivotTable and then click any of the
| | 02:42 | macros and because I have Excel
clear the PivotTable first, then it will
| | 02:47 | re-create the position exactly.
| | 02:49 | So let's say that I create a filter
on the Month column that displays only
| | 02:54 | months that contains the letters JU.
| | 02:56 | So I will type in JU and I see June and July.
Those are the only two months and I'll click OK.
| | 03:04 | Now if I were to click the Year_Month
shape to run that macro, Excel clears the
| | 03:10 | PivotTable and then
recreates the position that I wanted.
| | 03:13 | Recording your PivotTable positions as
macros makes it much easier for you to
| | 03:17 | display the data you want. Rather than
worry about how to recreate a position you
| | 03:21 | can focus on your message.
| | Collapse this transcript |
|
|
9. Getting Started with PowerPivotIntroducing PowerPivot| 00:00 | If you've ever tried to create a
PivotTable from a file containing hundreds of
| | 00:03 | thousands of rows of data, you might
have noticed some performance issues.
| | 00:07 | PowerPivot, a new technology introduced
alongside Excel 2010, uses advanced data
| | 00:12 | handling techniques to let you
manipulate large data sets more efficiently.
| | 00:16 | In this movie, I will give you an
overview of how to analyze data in PowerPivot
| | 00:20 | and then later in this chapter we will dive
into specifics of bringing data into PowerPivot.
| | 00:25 | So this is what a
PowerPivot datasheet looks like.
| | 00:29 | I have several different columns of data.
As you can see at the bottom left-hand
| | 00:33 | corner this PowerPivot data
set contains over 964,000 rows.
| | 00:38 | Now normally in Excel that would take a
lot of time to work with, but let's say
| | 00:41 | that I want to filter it so that
I only show orders of Quantity 14.
| | 00:47 | So I can click the filter arrow and
create my filter. Click OK and Excel filters
| | 00:56 | the rows and you can see
we're down to just under 50,000.
| | 01:00 | Creating another filter, say for store
number one, limits the data even further.
| | 01:07 | Now we are down under 500.
And then I will just clear the filters.
| | 01:12 | As you can see, working with large
data sets within PowerPivot happens much
| | 01:16 | more quickly than it would if you were just
using a regular PivotTable and an Excel worksheet.
| | 01:21 | So now let's create a PivotTable off of
this data, and I'll just create it here
| | 01:27 | in my workbook, and then just as
filtering that data happens much more
| | 01:31 | quickly in PowerPivot than it would in
Excel, I can create a PivotTable even
| | 01:35 | with this massive data set
much more quickly within PowerPivot.
| | 01:39 | So I'll just put my StoreID in Row Labels,
the Year in the Column Labels, and Total
| | 01:47 | order in the Values area, and there is my data.
| | 01:50 | Again, over 960,000 rows
summarized just that quickly.
| | 01:54 | The technology behind PowerPivot lets
you manage large datasets much more
| | 01:58 | efficiently than is possible
using conventional PivotTables.
| | 02:01 | In the next couple of movies I'll show
you how to install PowerPivot, bring your
| | 02:06 | data in, and then work with it.
| | Collapse this transcript |
| Downloading and installing PowerPivot| 00:00 | When you're ready to start using
Microsoft PowerPivot for Excel you can download
| | 00:04 | it from the web site powerpivot.com.
| | 00:07 | Once you're on that site, click the
Download link and then follow the
| | 00:14 | download instructions.
| | 00:16 | I won't read the prerequisites or
register to download the trial, because I
| | 00:19 | already have Office 2010 installed.
| | 00:21 | So I'll go down to what is currently
the third link and click the "see details"
| | 00:26 | item next to download PowerPivot for Excel 2010.
| | 00:30 | In this case, we are using the 32-bit
version of Excel 2010 so we will use that
| | 00:35 | version of PowerPivot, and I'll click
the PowerPivot link and we go to the
| | 00:40 | Microsoft Download Center.
| | 00:41 | And if I scroll down using my mouse's
scroll wheel, I can select the version I
| | 00:46 | want to download, which is the x86 version,
and then I will click the Download button.
| | 00:52 | I am asked if I want to save the file or run it.
| | 00:56 | in this case I will just save it, and I will
save it to my Downloads folder and click Save.
| | 01:04 | Once the download is complete you can
close the folder and then close out of
| | 01:08 | your web browser and now I will open
my Downloads folder and double-click
| | 01:14 | PowerPivot_for_Excel_x86.
| | 01:16 | I will verify that I do want to run
the file and step through the Excel
| | 01:22 | PowerPivot Setup Wizard.
| | 01:24 | So I will click Next to go past the
first screen. I will accept the terms in the
| | 01:28 | license agreement, click Next, and
I'll just type in a company, lynda.com.
| | 01:34 | click Next and then click Install.
And now that it's done, I can click Finish.
| | 01:43 | I'll close my Windows
Explorer window and run Excel.
| | 01:49 | As Excel starts you'll see that it has
loaded the PowerPivot Add-in and then
| | 01:54 | when you run the program for the
first time after you install PowerPivot,
| | 01:57 | a warning box appears indicating that it
has installed a customization and asking
| | 02:03 | you if you want to keep it.
| | 02:04 | You can either select not to install it
if you don't recognize the publisher or
| | 02:08 | you can choose to keep the
installation by clicking the Install button.
| | 02:12 | So this case, we are familiar with the
program and we did want to install it so
| | 02:16 | we can click Install.
| | 02:18 | When we do, Excel installs PowerPivot and
the PowerPivot tab appears on the Ribbon.
| | 02:23 | Now I'll click it to make sure all the tools
are there and everything looks to be in order.
| | 02:29 | Now that PowerPivot is installed we can
start downloading data and working with
| | 02:33 | it quickly and efficiently.
| | Collapse this transcript |
| Importing PowerPivot data| 00:00 | PowerPivot helps you analyze large
datasets efficiently but you do have to
| | 00:04 | import data into PowerPivot to get started.
| | 00:07 | In this movie I will show you how to bring
data from an Excel workbook into PowerPivot.
| | 00:11 | First thing you do is click the
PowerPivot tab on the Ribbon and then in the
| | 00:16 | Launch group click the PowerPivot Window button.
| | 00:20 | Then inside of PowerPivot on the Home
tab click the Ctrl that will let you get
| | 00:25 | the data from the type of source that
you want, and you have many different
| | 00:29 | sources available. For example you
can get your data from a database, SQL
| | 00:32 | Server, Access, Analysis Services,
or even another PowerPivot workbook.
| | 00:38 | You can also get your data from a
report, and that would be something like a
| | 00:42 | Microsoft SQL Server Reporting Services report.
| | 00:46 | You can also download data from the
Azure DataMarket, from RSS data feeds, from
| | 00:51 | text files, or in this case we're going
to use Other Text Sources because at the
| | 00:56 | bottom of this list you'll find
Microsoft Excel and that is the type of file
| | 01:02 | that we want to bring our data in from.
| | 01:04 | So I'll select Excel File and click Next.
| | 01:08 | Now I can indicate the file that I want
to use and I know that the file I want
| | 01:13 | has the first row as column headers.
| | 01:15 | So I will check that box and now I
can click the Browse button and use it to
| | 01:21 | find the file that I want.
| | 01:23 | In this case the file that I
want is called WholesaleData.
| | 01:26 | So I'll click it and click Open.
| | 01:29 | Everything looks good, so I can click
Next to move to the next page of the
| | 01:33 | wizard and when I do Excel looks
inside the file to see if there are any
| | 01:37 | tables that it can use.
| | 01:38 | This second table is called Data.
| | 01:42 | That is the one I want and to
make sure I'll click Preview & Filter.
| | 01:47 | That way I get a short preview of the
data that's in that file and it will take
| | 01:51 | just a moment to come up.
| | 01:53 | After the data comes up, you can preview
to make sure everything looks right and
| | 01:56 | in this case I have OrderID,
StoreID ,and all the data I expect.
| | 02:00 | So if that looks good. I can click OK
and then within the Wizard I can click
| | 02:06 | Finish to import the data into PowerPivot.
| | 02:09 | There are over 900,000 rows of data
in this file, so it may take a while to
| | 02:13 | import, but to get started I will click Finish.
| | 02:17 | After I do, PowerPivot indicates
that the import is in progress and in a
| | 02:22 | moment or two we should see that it
has imported about 100,000 rows then 200
| | 02:27 | then 300,000 and so on.
| | 02:28 | So now we are starting to get in our data.
| | 02:31 | It looks like we're a little bit
more than halfway through and coming up
| | 02:35 | on 900,000. Almost there, and I
should point out that at anytime during the
| | 02:40 | transfer I could have clicked the Stop
Import button to stop the import from occurring.
| | 02:44 | But now we were successful. 964,260
rows were transferred into PowerPivot, so
| | 02:51 | I can click Close, and the
data appears within PowerPivot.
| | 02:55 | Now I want to save this data model,
so I'll click the Save button.
| | 02:59 | After I click Save, the
PowerPivot window disappears.
| | 03:03 | In this movie I showed you how to
bring Excel data into PowerPivot.
| | 03:06 | But the steps are almost exactly the
same for most other data sources.
| | 03:10 | Just select the type of source you want
and go through the steps to bring it in for analysis.
| | Collapse this transcript |
| Managing table columns| 00:00 | After you bring your data into
PowerPivot you can manage the data within both
| | 00:03 | the PowerPivot datasheet and
also within your PivotTables.
| | 00:07 | In this movie I will show you how to
work with PowerPivot columns of data.
| | 00:11 | So to display the data that I have
in this worksheet, I will click the
| | 00:15 | PowerPivot tab and then open the
PowerPivot window by clicking the PowerPivot
| | 00:20 | Window button in the Launch section of
this tab on the Ribbon, and when I do my
| | 00:25 | data appears in PowerPivot, and to
indicate how much data we're working with,
| | 00:30 | at the bottom left corner you can see that
we are currently looking at Row 1 of 964,260.
| | 00:38 | In PowerPivot as in Excel you can
manipulate the columns inside of
| | 00:43 | the PowerPivot window.
| | 00:44 | So let's say for example that you
want to hide a particular column and in
| | 00:48 | this case we will just say that we
want to hide the Product column, to do
| | 00:52 | that you can right-click the column
header and then click Hide Columns and
| | 00:57 | then select whether you want to hide
the column from the PowerPivot window
| | 01:01 | and the PivotTable,
| | 01:02 | just from the PivotTables,
or just from PowerPivot.
| | 01:07 | In this case I just want to hide it
from PowerPivot but I want to leave the
| | 01:10 | column available for my PivotTables.
| | 01:13 | So then I will click from PowerPivot.
| | 01:16 | When I do the column disappears. Now the
data hasn't been deleted. It's still there.
| | 01:20 | It's just that I don't have to
see it within the PowerPivot window.
| | 01:25 | If I want to bring it back I can right-
click any column header, click Unhide
| | 01:30 | Columns to display the Hide and
Unhide Columns dialog box, and then scroll
| | 01:35 | down and I see that the Product table isn't
checked in the In PowerPivot window column.
| | 01:42 | So I can check the box to
bring it back and click OK.
| | 01:47 | If you want to display the Hide and
Unhide Columns dialog box and manipulate
| | 01:51 | columns more than one at the time, you
can click the Design tab, click the Hide
| | 01:56 | and Unhide button, and the dialog box appears.
| | 02:00 | If you do want to delete a column for
the PowerPivot model permanently, then
| | 02:04 | you right-click the column header in the
PowerPivot datasheet and then click Delete Column.
| | 02:10 | So I won't go all the way through
with us, but let's say that I wanted to
| | 02:13 | delete the Total column.
| | 02:16 | To do that I would right-click the
column header and then click Delete Columns.
| | 02:21 | And then after PowerPivot asked if I
were serious about deleting that column I
| | 02:26 | would say yes. But in this
case I don't want to get rid of it.
| | 02:29 | So I'll just click away from the
Shortcut List to remove the selection.
| | 02:34 | If you remove a column from the data
model, it is permanent. You would have to
| | 02:38 | create a new data model to bring the data back
but the data still exists in the source table.
| | 02:44 | One other way that you can manipulate
columns inside of the PowerPivot window is
| | 02:49 | to freeze columns at the
left side of the worksheet.
| | 02:51 | So say that I go back to the Home tab
and I will resize the PowerPivot window
| | 02:57 | just so we don't see the right edge.
| | 02:59 | so you can see what happens when we scroll.
And now I will click any cell in the first column.
| | 03:06 | That's the OrderID column. Then I will
click the Freeze Buttons down arrow on
| | 03:11 | the Home tab of the PowerPivot
window and then click Freeze.
| | 03:16 | When I do you see this thin black
line appear. That's because the OrderID
| | 03:20 | column is now frozen to the left side,
and it won't disappear when I scroll past.
| | 03:25 | So if I scroll to the right to
display the hidden rows at the right edge of
| | 03:30 | the sheet, then you see that the
OrderID column stays regardless of how far to
| | 03:35 | the right I scroll.
| | 03:36 | Now if I click the Freeze button's down
arrow again and click Unfreeze then Excel
| | 03:41 | removes the selection.
| | 03:43 | You won't need to use every column of
data you bring into PowerPivot so you can
| | 03:47 | either delete the columns you don't want
or if you think they might be useful in
| | 03:51 | the future hide them from the
datasheet or the PivotTable.
| | 03:54 | Any of these actions reduces crowding
within PowerPivot, making it easier to
| | 03:58 | understand your data at a glance.
| | Collapse this transcript |
| Adding tables to a PowerPivot model| 00:00 | PowerPivot lets you manipulate large
datasets efficiently, but you should only
| | 00:04 | import the tables you need.
| | 00:06 | Importing unnecessary data can slow
performance and make it harder for you to
| | 00:09 | find the data you want.
| | 00:11 | If you discover that you didn't import
a table you actually need, you can add
| | 00:15 | that table to your data model in a few steps.
| | 00:17 | So to start out I'll open PowerPivot
and to do that I'll click the PowerPivot
| | 00:22 | tab on the Ribbon and then
click the PowerPivot Window button.
| | 00:27 | Now to bring my new data into
PowerPivot I will need to import it and in this
| | 00:32 | case the data that I want to bring in
is in an Access database, so I will click
| | 00:37 | the From Database button on the Home tab of
the PowerPivot Window and then click From Access.
| | 00:44 | The Table Import Wizard dialog box
appears and I can click Browse and then the
| | 00:50 | data that I want is in the MonthTable database.
| | 00:53 | so I'll double-click it and when I do
the database name and full path appear
| | 00:58 | appear in the database name field.
| | 01:00 | If this database were password-
protected then I could type in my username and
| | 01:04 | password and also save my password
by checking this box if I wanted to.
| | 01:09 | In this case the database is not
password-protected so I can just click Next.
| | 01:15 | Now I can either select from a list of
tables and views or I can write a query.
| | 01:19 | Writing a query is beyond the scope of
this course so I will leave the Select
| | 01:23 | from a list of tables and views option
selected and click Next, and then I get a
| | 01:28 | list of tables and views.
| | 01:30 | And in this case the only table in the
database is one called Months. That is the
| | 01:34 | one I want so I can make sure
that it is selected and click Finish.
| | 01:40 | There are only 12 rows of data in this
table so the import happens very quickly.
| | 01:45 | Now that it's done I can click Close.
| | 01:48 | After I close the Wizard PowerPivot
displays the table in Datasheet view and you
| | 01:53 | can see the two columns, MonthID and MonthName.
| | 01:57 | Also as in an Excel workbook you can
see here at the bottom left corner of the
| | 02:01 | Program window that we have two tabs,
the Months tab and the Data tab.
| | 02:07 | Clicking the tabs moves between them
so that you can see your data and work
| | 02:10 | with them as desired.
| | 02:12 | So don't worry if you overlook
the table you wanted to import.
| | 02:15 | Just reopen the data source by using the
controls in the Get External Data group
| | 02:20 | and bring the table in when you want to add it.
| | 02:22 | In the next movie I'll show you how
to create a relationship between these two tables.
| | Collapse this transcript |
| Creating relationships between tables| 00:00 | When you work with large data
collections, especially those stored in relational
| | 00:04 | databases such as Access or SQL Server,
it's likely that your data will be spread
| | 00:08 | amongst several tables.
| | 00:10 | For example you can have a table of
sales data that lists each month by its number.
| | 00:13 | January is one,
February is two, and so on.
| | 00:17 | If you have the month's number in
one table and a list of which month
| | 00:20 | corresponds to which number in
another you can often create a relationship
| | 00:24 | between those two tables and in this case
use the month's name instead of its number.
| | 00:29 | As an example let's take a look at the
data model within this PowerPivot file.
| | 00:34 | So to do that I will click the
PowerPivot tab on the Ribbon and click the
| | 00:39 | PowerPivot Window button to display PowerPivot.
| | 00:42 | When I do, the PowerPivot for Excel
window opens and I have two different tables
| | 00:48 | in this data model.
I have months and I have data.
| | 00:52 | Let's take a look at the months table first.
| | 00:54 | In this case I have 12 rows and those
are the month names in English with the
| | 00:59 | corresponding MonthID number.
| | 01:02 | January is one, February is two, and so on.
| | 01:05 | In this table the MonthID field
contains a unique value in each row and it
| | 01:10 | distinguishes that row from
all other rows in the table.
| | 01:13 | That means that it can be called a key
field because you can use it to identify
| | 01:18 | a single row within the data collection.
| | 01:20 | Now let's take a look at the
same data over in the data table.
| | 01:26 | In this table we have the OrderMonth
field and as you can see, not every row
| | 01:30 | contains a unique value. Some orders are
from January, which is month one, others
| | 01:35 | from February, month two, and so on.
| | 01:37 | However, we do know from the way that
the calendar is composed that the values
| | 01:43 | in the OrderMonth field will always be
the numbers 1 through 12 and that means
| | 01:48 | we can create a correspondence or a
relationship between the column OrderMonth
| | 01:52 | in this table and the column
MonthID in the other table.
| | 01:56 | In other words we can create a relationship.
| | 01:59 | To do that on the Design tab, in the
PowerPivot window we click the Create
| | 02:04 | Relationship button.
| | 02:07 | Then in the Create Relationship
dialog box we can select the table in the
| | 02:11 | columns to identify the two
fields that contain the same data.
| | 02:16 | So in this case the Data table
contains the OrderMonth field and then the
| | 02:22 | Related Lookup Table is in the Months table
and the Related Lookup Column is the MonthID.
| | 02:30 | So again just dropping this down a
little bit so we can see the numbers in
| | 02:34 | the OrderMonth column.
| | 02:36 | The OrderMonth column contains the
numbers 1 through 12, corresponding to the
| | 02:39 | month in which the order was placed.
| | 02:42 | In the Months table, the MonthID field
contains the numbers 1 through 12 and
| | 02:47 | those correspond to the months
January, February, March, and so on.
| | 02:51 | So with this relationship defined we
can click Create and PowerPivot creates a
| | 02:57 | relationship between those two tables.
| | 03:00 | So how does this affect us?
| | 03:01 | Well, what it allows us to do is
to create a PivotTable and instead
| | 03:06 | of displaying month numbers we can display
the month names by using the relationship.
| | 03:11 | So to do that we click the Home tab on the
Ribbon and then click the PivotTable button.
| | 03:18 | That puts us back into Excel and we
can create the PivotTable on the new
| | 03:23 | worksheet and click OK.
| | 03:25 | So now we have a new worksheet with a
PivotTable and I'll just create a very
| | 03:30 | simple PivotTable based on this data.
I will have the StoreID in the Row Labels
| | 03:35 | area and then the OrderMonth below it,
and yes, this will show a number.
| | 03:41 | I am going to change that in a bit. I just
wanted you to see what it would look like
| | 03:45 | before we use the related table.
| | 03:47 | So we have Store ID, OrderMonth, and
then I can put Total in the Values area.
| | 03:53 | So now let's see what happens when I use
the MonthName field from the Months table.
| | 03:58 | So I will remove OrderMonth from the
Row Labels area and now I will click the
| | 04:04 | expand button next to the Months table,
and then drag MonthName to below Store
| | 04:10 | ID on Row Labels area.
| | 04:12 | When I do the month names appear
in the PivotTable but they're sorted
| | 04:16 | into alphabetical order.
| | 04:18 | However, if I click one of the cells
that contain a month name and then on the
| | 04:24 | Home tab click the Sort&Filter button
and click Sort A to Z, Excel knows to sort
| | 04:31 | them into proper month order
because it recognizes the series.
| | 04:35 | Creating a relationship between two
tables lets you expand the types of data you
| | 04:39 | can include in your PivotTable.
| | 04:40 | In this example we used the
relationship between the Data and Months tables to
| | 04:45 | change a month's number to its name,
which made the summary easier to understand.
| | Collapse this transcript |
|
|
10. Working with DAX ExpressionsIntroducing the DAX language| 00:00 | When you create a PowerPivot data
model, you can sort, filter, and create a
| | 00:03 | PivotTable to summarize your data.
| | 00:06 | You can also create your own summaries
using Data Analysis eXpressions or DAX.
| | 00:11 | DAX expressions are similar to
Excel worksheet formulas that you use to
| | 00:15 | summarize values in Excel tables.
| | 00:17 | So just for a quick comparison, if I
wanted to find the 2008 sales in the
| | 00:22 | table to the left, I would type =sum( and then
we're looking at the values in D3 through D8.
| | 00:32 | So type a right parentheses and press Enter
and there is the formula for just those cells.
| | 00:40 | If I wanted to find a total of all
sales in the Sales column of the table then
| | 00:44 | I would type =sum(, and then the
name of the table, which is Table1, so
| | 00:50 | T-A-B-L-E1 and then a left square
bracket so that I can start naming the
| | 00:57 | column I want to use, and in this
case that is Sales, so I'll type in S and
| | 01:02 | then in the formula AutoComplete list I
see that sales is highlighted, so I can
| | 01:07 | press Tab to accept it.
| | 01:09 | Type a right square bracket to close the
table column reference and then a right
| | 01:15 | parentheses to close the sum formula.
| | 01:17 | Press Enter and I get my result.
| | 01:20 | And if I highlight that cell again, I
can see that I have the table reference
| | 01:25 | followed by the column reference all as
an argument inside of the SUM formula.
| | 01:30 | Now let's shift over to PowerPivot and
create a calculated column so that you
| | 01:34 | can see how it compares.
| | 01:36 | So I'll click the PowerPivot tab on
the Ribbon and then click the PowerPivot
| | 01:40 | Window button, and here I
have my PowerPivot data model.
| | 01:45 | What I want to do is create a new
column that finds the total for each line item,
| | 01:50 | so I will be
multiplying Quantity by Price.
| | 01:54 | So I will click in the first cell in
the Add Column column, and then type equal.
| | 02:00 | ,
You'll see the typing here in the
Formula Bar as opposed to in the cell.
| | 02:04 | So type equal and then a left square bracket and then
I can use the Down Arrow key to select the first field I want,
| | 02:11 | and that's Quantity. Press Tab and
then in PowerPivot the program encloses
| | 02:19 | the field name in square brackets, so
you don't need to type it in yourself.
| | 02:23 | Now type an asterisk for multiply,
then a left square bracket and we're
| | 02:28 | multiplying by Price, so I'll type a P
and the highlighted item is Price so I'll
| | 02:34 | press Tab. Everything looks good.
| | 02:36 | And when I press Enter you'll see the
PowerPivot creates a calculated column
| | 02:41 | called CalculatedColumn1 and its
values are Quantity times the Price.
| | 02:47 | DAX lets you extend the basic
capabilities of PowerPivot by creating expressions
| | 02:51 | that summarize your data.
| | 02:53 | In the rest of this chapter I'll go
into more detail on how to use DAX to
| | 02:56 | summarize your data.
| | Collapse this transcript |
| Using DAX operators| 00:00 | Like Excel, the DAX language uses
operators to summarize and compare values.
| | 00:05 | You'll probably recognize the
Arithmetic operators from your regular Excel
| | 00:08 | formulas, but some of the other
operators might require some explanation.
| | 00:12 | In this movie, I'll start out with an
overview of the operators you can use and
| | 00:16 | then show you some examples of
expressions with a few of them.
| | 00:20 | DAX operators are divided
into several different groups.
| | 00:22 | The first is the Arithmetic and
it's almost certainly familiar to you.
| | 00:27 | You have the plus sign for addition,
minus for subtraction, or to indicate
| | 00:31 | a negative number, the asterisk for
multiplication, and the forward slash for division.
| | 00:37 | Then there are the Comparison operators,
and you probably would have used these
| | 00:41 | if you created a filter or a
conditional format inside of Excel.
| | 00:46 | Equal tests for Equal To, then there is
Not Equal To, Greater Than, and so on.
| | 00:52 | Then the last two groups are
the Text and Logical groups.
| | 00:56 | So for Text you have the ampersand
which you can use to concatenate two strings
| | 01:01 | and you can use that in Excel as well.
| | 01:04 | The last group is the Logical group and
those allow you to test for conditions.
| | 01:09 | So for example, you have
two ampersands back to back.
| | 01:12 | That is the AND condition, which returns a
value of true if both conditions are true.
| | 01:18 | So for example, if you are looking at a
value and you want to test if the sale
| | 01:22 | was from 2008 and the month of January,
then you could use the double ampersand
| | 01:28 | operator to create an AND condition.
| | 01:30 | Two vertical bars create an OR
condition and it is an inclusive OR which
| | 01:36 | means that either one of the
expressions or both may be true for the OR
| | 01:41 | expression to return true.
| | 01:43 | Then finally you have the
exclamation point which is the NOT operator.
| | 01:49 | The NOT operator checks to see
whether something is not true.
| | 01:52 | In other words if you were looking for
sales that were not in 2008, you could
| | 01:57 | type an exclamation point and then 2008.
| | 02:00 | Now, I'll switch back to my DAX
Operators workbook and launch the PowerPivot
| | 02:04 | module so you can see me working
with these operators in a few formulas.
| | 02:09 | Now that I'm back in the workbook I'll
click the PowerPivot tab on the Ribbon
| | 02:14 | and then PowerPivot Window to launch PowerPivot.
| | 02:18 | Now we'll create a few examples.
| | 02:20 | So for example, I will create a formula
in the Add Column area. Type an equal sign.
| | 02:26 | And in this case, I'll do a
multiplication of multiply quantity by price.
| | 02:31 | So I'll type a left square bracket,
then a q, and then press Tab to accept the
| | 02:36 | Quantity field, then an asterisk to
multiply, left square bracket, and we'll
| | 02:42 | multiply it by Price and
that is the highlighted field.
| | 02:46 | So I'll press Tab and then press Enter.
| | 02:48 | Now, let's say that I want to use
concatenation to create a statement of the
| | 02:53 | form that store blank
ordered a quantity of bottles.
| | 02:59 | So, I'll be using Store, StoreID and Quantity.
| | 03:02 | To do that I will scroll over to the
right, and then in the new Add Column
| | 03:08 | column type an equals sign, and then I'll
create the following formula in the Formula Bar.
| | 03:14 | Double quote, Store, and then a space
followed by another double quote, space,
| | 03:21 | ampersand, space, then a left square
bracket, and St which gives me StoreID.
| | 03:30 | So I will press Tab to accept that field.
| | 03:33 | Then an ampersand, space, double quote,
space, ordered, space, double quote,
| | 03:42 | space, ampersand, and then
left square bracket after a space.
| | 03:48 | Then we have Quantity, so I'll type a
q and then Tab to accept the Quantity field,
| | 03:52 | a space, ampersand, space, double
quote, space, bottles, period, and then
| | 04:01 | a double quote.
| | 04:02 | So my goal was to have the literal
text string of Store followed by a space,
| | 04:08 | then the StoreID followed by a space,
then the word ordered, then a space, then
| | 04:13 | the Quantity ordered, then a space, and
then the word bottles with the period.
| | 04:19 | So hopefully, if I've created my
formula correctly and I press Enter, that is
| | 04:23 | what will appear in the new column.
| | 04:26 | So I'll just adjust the width of the
column and we can see that I created the
| | 04:32 | expression correctly.
| | 04:33 | With these examples in mind, I think
you'll have a good idea of how to use DAX
| | 04:37 | operators in your PowerPivot expressions.
| | 04:39 | Feel free to play around, but always
do so in a backup copy of your data.
| | Collapse this transcript |
| Surveying DAX functions| 00:00 | When you want to summarize your
PowerPivot data you can use the built-in
| | 00:03 | DAX functions to do so.
| | 00:05 | There are two ways to
find the functions you need.
| | 00:07 | The first is to use the Help system to
look up the function you want and the
| | 00:11 | second is to start typing the
function's name and select the proper function
| | 00:16 | from the IntelliSense list that appears.
| | 00:18 | You're probably familiar with Formula
AutoComplete and IntelliSense from using
| | 00:22 | Excel, so I'll show you how to use the
PowerPivot Help system to get information
| | 00:27 | on the available functions.
| | 00:28 | To begin, in Excel we'll click the
PowerPivot tab on the Ribbon and then click
| | 00:34 | the PowerPivot Window button.
| | 00:36 | And when we do, our data model
appears inside of the PowerPivot window.
| | 00:41 | Now to get help in PowerPivot you can
click the Help button and it's the blue
| | 00:46 | button with a question mark at
the top right corner of the Ribbon.
| | 00:50 | So click that and the SQL Server PowerPivot
for Microsoft Excel Help dialog box appears.
| | 00:57 | Then inside the dialog box click the
Search tab in the left-hand pane and
| | 01:02 | then type function reference,
and then press Enter.
| | 01:13 | When you do you see list of Topics.
| | 01:15 | And in my topic list, topic
number 14 is the Function Reference.
| | 01:21 | I've highlighted it to make it call
out and I'll double-click it to make it
| | 01:25 | appear in the right-hand pane of the dialog box.
| | 01:29 | I'll expand the window so that it's
much wider and now within the window I can
| | 01:35 | click the DAX Online Function Reference link.
| | 01:39 | When I do that displays the Function
Reference page on Microsoft TechNet.
| | 01:44 | And I'll drag the left-hand side of
this window to make it even larger.
| | 01:49 | And now within the reference article you
can go to any different section that you want.
| | 01:54 | So let's say for example that
you wanted to get information about
| | 01:57 | statistical functions.
| | 01:58 | To do that you can click the
Statistical Functions link and you'll see all of
| | 02:03 | the functions that are listed and
that you can use here in the article.
| | 02:08 | If you want to go back to the previous
page you can click the Back button on the
| | 02:12 | toolbar and it'll take
you back to the main page.
| | 02:15 | And just to see one other section we'll
click the Date and Time Functions link,
| | 02:20 | and on that page we get a list of
the functions that are available to us.
| | 02:25 | The DAX language doesn't include all of
the functions you'll find in Excel but
| | 02:29 | it does offer a wide variety of
functions that let you build calculations to
| | 02:32 | summarize your data effectively.
| | 02:34 | I strongly encourage you to read
through the DAX Function Reference to become
| | 02:37 | familiar with the functions at your disposal.
| | Collapse this transcript |
| Adding calculated columns and measures| 00:00 | After you import data into your Excel,
you might find you want to summarize your
| | 00:04 | data in a way that's not
included in the original table.
| | 00:07 | For example, your table might include
a Price column and a Quantity column,
| | 00:11 | but not a column that multiplies those
values together to find the total for each row.
| | 00:16 | If you do want to multiply those
values together, you can create what's called
| | 00:19 | a calculated column.
| | 00:21 | You can also summarize your data using a
measure, which you can use within a PivotTable.
| | 00:26 | In this movie I'll show you how to
create both calculated columns and measures.
| | 00:30 | To begin I'll jump into PowerPivot,
so I'll click the PowerPivot tab on the
| | 00:35 | Ribbon and then the PowerPivot
Window button to display my data within
| | 00:40 | PowerPivot and now to create a
calculated column I click over in the Add Column
| | 00:46 | column and then I can start typing my formula.
| | 00:49 | In this case I'll type an equal sign
and you can see the formula here on
| | 00:54 | the Formula bar, and then I'll type the names
of the fields that I want in my calculation.
| | 00:59 | So in this case I want the price, so
I'll type a (P. The Price field's name is
| | 01:07 | highlighted in blue, so
I'll press Tab to accept it.
| | 01:10 | Then type an * and then a left square bracket
and then I want the Quantity field, so I'll type Q,
| | 01:19 | Press Tab to accept the
quantity and press Enter.
| | 01:24 | When I do PowerPivot adds a
calculated column to my table.
| | 01:28 | I can rename that column by double-
clicking the column name and then when it's
| | 01:33 | highlighted in blue typing a new name
and in this case I will call it Total
| | 01:38 | Order and press Enter.
| | 01:42 | One thing to note is that calculated
columns summarize data at the row level
| | 01:47 | within your PowerPivot data.
| | 01:48 | So as you can see from the formula I'm
multiplying the Price column's value by
| | 01:54 | the Quantity column's value for
each row to derive a new value.
| | 01:58 | If you want to summarize your data at
a high level, you can create what's
| | 02:01 | called a measure and you
create measures within PivotTables.
| | 02:06 | So on the Home tab of the PowerPivot
window's Ribbon, I'll click the PivotTable
| | 02:11 | button and then the select new worksheet in the
Create PivotTable dialog box and then click OK.
| | 02:19 | Now I'm back in Excel in the
worksheet and I can use the controls on the
| | 02:23 | PowerPivot tab of the
Ribbon to create my measure.
| | 02:26 | So I'll go over here in the Measures
group and click the New Measure button and
| | 02:31 | now I can create my measure.
| | 02:34 | The table name is Data. That's the table
from the PowerPivot data model and now I
| | 02:40 | can type in the name for this
measure and I'll make it AverageSale.
| | 02:51 | Now down in the Formula box I can
create the formula and in this case the
| | 02:56 | formula will be =Average( and then the
name of the column and that column is
| | 03:03 | from the Data table, left
square bracket, and TotalOrder.
| | 03:08 | That's the calculated
column that I just created.
| | 03:11 | So I'll highlight it in the list, press
the Tab key to accept it, type a right
| | 03:16 | parenthesis, everything looks good
and now I can click OK. And when I do,
| | 03:22 | PowerPivot creates a PivotTable with
the AverageSale field in the Values area
| | 03:28 | and then within the body of a
PivotTable you can see that the AverageSale value
| | 03:32 | for all sales was a bit over $131.4.
| | 03:36 | Now let's start adding some divisions
into the PivotTable. So let's say that I
| | 03:40 | only care about years.
| | 03:42 | So I'll drag the OrderYear field
down to the Row Labels area and you can
| | 03:47 | see that the average sale for 2007, 2008,
2009 and 2010 appear inside of the PivotTable.
| | 03:55 | Creating a calculated column doesn't
add a column to your original data source,
| | 03:59 | but it does let you summarize your
data using calculations you might not have
| | 04:03 | in the original table.
| | 04:04 | Calculated columns generate their
values on a row by row basis, but a measure
| | 04:09 | summarizes all the data
referred to in a PivotTable cell.
| | 04:12 | That value could be from a single
table row, but more than likely it
| | 04:16 | summarizes part of your data.
| | 04:17 | In that case you have to use a
measure instead of a calculated column.
| | Collapse this transcript |
| Using aggregate functions| 00:00 | When you create measures in PowerPivot
you can run into unexpected problems when
| | 00:04 | you use functions such as SUM or COUNT.
| | 00:07 | In this movie I'll show you how to
avoid those issues by using aggregate
| | 00:10 | functions such as SUMX and COUNTX.
| | 00:14 | So to begin let's go into PowerPivot.
| | 00:17 | Click the PowerPivot tab on the Ribbon
and then the PowerPivot Window button and
| | 00:22 | our data reappears inside of PowerPivot.
| | 00:26 | Now in the previous movie I showed you how
to create a calculated column and a measure.
| | 00:31 | The difference between the two is that
a calculated column uses values within
| | 00:34 | a table row to generate its result while a
measure summarizes the values for an entire column.
| | 00:40 | This data model has two columns, Price and
Quantity, that I can use to calculate a total.
| | 00:45 | I can if I want to create a calculated
column such as by adding a formula equal
| | 00:50 | and I'm just repeating what I did from
the previous movie. [Price]*[Quantity].
| | 01:01 | Press Tab to accept it and press Enter.
| | 01:04 | So there is my calculated column and
I won't bother renaming it this time.
| | 01:08 | Now if I want to create a measure from
those two columns, I need to create a
| | 01:11 | PivotTable and then create the measure.
| | 01:13 | So to do that I click the PivotTable
button and put it on the worksheet, click
| | 01:19 | okay and I get my new PivotTable.
| | 01:21 | Now let's say that I want to
create a new measure called OrderTotal.
| | 01:27 | To do that I click the New Measure
button and then I'll change the name of the
| | 01:32 | measure to OrderTotal. And you don't
have to start with a capital letter. I just
| | 01:37 | do it out of convention. And
then we can create the formula.
| | 01:42 | So I'll click in the Formal box and
this formula will generate an error.
| | 01:47 | So it's =sum(Data, left square bracket, Price.
So I'll start with the P and select that total.
| | 01:57 | Type an asterisk and then type in data
and a left square bracket and then Q for
| | 02:04 | quantity, press Tab to accept the field,
right parenthesis to close formula, and click OK.
| | 02:10 | Now you can see what I did that I got
an error; in other words it says the
| | 02:16 | calculation has been aborted and the
error message says that the SUM function
| | 02:20 | only accepts a column reference as an argument.
| | 02:23 | So that means that I need to
go in and to change the formula.
| | 02:27 | So I will right-click the OrderTotal
field name here in the field list and
| | 02:33 | click Edit Formula.
| | 02:35 | So the formula that I'm going to
create is called an aggregate formula and
| | 02:40 | the syntax is the function name followed by
an X then the table name and then the expression.
| | 02:47 | So in this case the formula that I want
create is =sumX( and then Data, which is
| | 02:55 | the name of the table, then a comma,
then I can create the expression, and the
| | 03:00 | expression will be what I created
before. So the Data table, left parenthesis,
| | 03:06 | and then price, typing a P and then
pressing Tab to accept it, then an asterisk and
| | 03:13 | then now we want the Quantity column
from the data table, so that would be
| | 03:17 | Data( and then a Q for quantity and
then press Tab to accept it and then a
| | 03:25 | right parenthesis to close the formula.
| | 03:27 | Now when I click OK, Excel finds the
value and the formula operates normally.
| | 03:33 | So once again let's go back into the
measure. So with the measure selected, you
| | 03:39 | can click Measure Settings to display
the Measure Settings dialog box and the
| | 03:43 | formula reads sumx and then the name of
the table and then the expression that
| | 03:49 | uses values from within that.
| | 03:51 | I don't want to make any
changes so I'll click Cancel.
| | 03:55 | In addition to the SUMX formula, you
can create COUNTX, COUNTAX, AVERAGEX, MAXX
| | 03:59 | and MINX functions, which correspond to
the COUNT, COUNTA, AVERAGE, MAX and MIN
| | 04:10 | worksheet functions.
| | 04:11 | Aggregate functions let you summarize
data by creating measures using values
| | 04:15 | from multiple columns within a data model.
| | 04:17 | If you can't get the calculation you
want by using a regular function such as sum,
| | 04:21 | consider using its aggregate counterpart.
| | Collapse this transcript |
| Using filters in aggregate functions| 00:00 | When you create a measure using an
Aggregate function, PowerPivot calculates
| | 00:04 | that measure's value for every row
summarized in the PivotTable field.
| | 00:08 | You can filter your PivotTable to
limit your data but if you find yourself
| | 00:11 | creating the same filter many times,
you should consider creating a measure that
| | 00:16 | incorporates that filter into its formula.
| | 00:18 | In this movie, I'll show
you how to do exactly that.
| | 00:22 | So, we will jump into PowerPivot,
clicking the PowerPivot tab on the Ribbon
| | 00:26 | and the PowerPivot Window button, and
we will create a measure that includes
| | 00:31 | the filtered formula.
| | 00:33 | So to do that, we will click the
PivotTable button to create a PivotTable andwe
| | 00:37 | will put it on the new
worksheet in Excel and click OK.
| | 00:41 | Now remember that we need to use an
Aggregate function such as SUMX for
| | 00:45 | the filter to work.
| | 00:46 | So, I'll create a new measure
and I'll name it LargerOrders,
| | 00:52 | and I will go down to Formula box.
| | 01:00 | In an Aggregate function, you have
the name of the function followed by the
| | 01:04 | table and then the expression.
| | 01:06 | And the table argument can be a real
table or it can be a virtual table that's
| | 01:11 | created using another function, and
in PowerPivot you can use the Filter
| | 01:15 | function to create a virtual table
that's a subset of the original table.
| | 01:20 | To give you an example, I'll create
the formula =sumx(Filter( and now the
| | 01:31 | Filter function takes two arguments:
the table that you're referring to and
| | 01:35 | then the actual filter.
| | 01:37 | In this case, the table's name is
Data. Type a comma and now I create a rule
| | 01:42 | that does the filtering.
| | 01:44 | And in this case, I only want to see
orders with quantities greater than 18, so
| | 01:49 | in other words 19 or more items.
| | 01:52 | So, for that I will type in Data, a square bracket,
and then we are referring to the quantity field
| | 01:58 | so I will type a Q then a Tab to
accept that field then a greater than
| | 02:03 | sign and then the number 18.
| | 02:05 | Now I type a right parentheses to
close the Filter function and now I'm back
| | 02:11 | in the SUMX function.
| | 02:12 | Now I will type a comma and
now I can type the expression.
| | 02:16 | So, first we'll have the data quantity field. So
Data, left square bracket, q for quantity, press Tab,
| | 02:25 | and I'm going to multiply that by the price field.
So type an asterisk then data, left square bracket,
| | 02:34 | and p for price. It's the highlighted
items so I can press Tab to accept it then
| | 02:39 | a right parentheses.
| | 02:41 | And the formula looks good, so I can click OK.
| | 02:44 | So, you can see from the body of the
PivotTable that the total of my larger
| | 02:48 | orders appears in cell B4.
| | 02:50 | Now, let's compare that
to the total of all orders.
| | 02:54 | So, to do that, I will drag the
OrderTotal field to the Values area and you can
| | 03:01 | see that the OrderTotal is
greater than LargerOrders.
| | 03:06 | The opposite function of the Filter
function is called ALL and that ignores any
| | 03:10 | filters in the data model or in the PivotTable.
| | 03:14 | To illustrate that, I'll create
another new measure and I will name this one
| | 03:19 | AllStoreSales. Then in the Formula box
I will create the formula SUMX. Again we
| | 03:28 | need to use an Aggregate function, left
parentheses, the keyword All, then a left
| | 03:35 | parentheses, and then I want every row
in the data table so I will type Data,
| | 03:40 | right parentheses, a comma, and now I
can type my expression, which will be once
| | 03:46 | again that the Price field
times the Quantity field.
| | 03:50 | So, Data, left square bracket, quantity,
type q, and then Tab to accept the
| | 03:55 | highlighted field, asterisk, and then
data, left square bracket, p for price.
| | 04:02 | It's highlighted, press Tab to accept it,
then a right parentheses, and click OK.
| | 04:09 | When I do, Excel adds that field
to the data area of the PivotTable.
| | 04:13 | Now, we will see what happens when I create
a report filter based on the StoreID field.
| | 04:19 | So, I'll drag the StoreID field down to
the Report Filter area and then I will go
| | 04:24 | up and click the Filter arrow.
| | 04:28 | Click the expand control next to
All and then I'll check the Select
| | 04:33 | Multiple Items box, clear the check
box next to All, and then we will just
| | 04:38 | look at the values for Store 1.
| | 04:40 | So, I will click OK and
Excel filters the PivotTable.
| | 04:44 | So, you'll notice that the
LargerOrders field changed its value, the Sum of
| | 04:50 | OrderTotal field changed its value,
but the AllStoreSales field did not change
| | 04:55 | its value and that's because the
measure used to produce its value ignores any
| | 05:00 | filters applied to the table.
| | 05:02 | Building a filter into an aggregate
function saves you time when you summarize
| | 05:05 | data in PowerPivot.
| | 05:07 | Be sure to name any filtered
measures clearly to assure your PivotTable
| | 05:11 | summarizes exactly the data you want it to.
| | 05:13 | Alternatively, if you always want to
measure to display the total of every row
| | 05:18 | in the data model, use the All
function in your measure's formula.
| | Collapse this transcript |
|
|
ConclusionAdditional resources| 00:00 | I hope you've enjoyed
working through Excel 2010.
| | 00:03 | If you're looking for more information
about PivotTables and about PowerPivot,
| | 00:06 | then I've got a couple of resources for you.
| | 00:09 | The first is the Excel 2010 Essential
Training course available from lynda.com
| | 00:14 | and also if you're looking into
PivotTable automation you should look into my
| | 00:19 | course Excel: VBA in Depth.
| | 00:22 | Both of those courses are available in
the lynda.com Online Training Library and
| | 00:27 | also, you can go into a lot
more depth on PowerPivot.
| | 00:31 | The best book that I found on the
subject is Microsoft PowerPivot for Excel 2010:
| | 00:35 | Give Your Data Meaning that was
published by Microsoft Press and then you
| | 00:39 | can also go to www.powerpivot.com.
| | 00:43 | That's where you download PowerPivot
and you can also find other tutorials and
| | 00:48 | information about how to use the software.
| | 00:50 | Thanks again for working through the
course and I hope you get a lot out of
| | 00:53 | PivotTables in Excel 2010.
| | Collapse this transcript |
|
|
