IntroductionWelcome| 00:00 | (Music playing)
| | 00:03 | Hi! I am Curt Frye. Welcome to Excel
2007 Pivot Tables for Data Analysis.
| | 00:09 | In this course, I'll show you how to
use pivot tables to make sense of your
| | 00:11 | organization's data.
| | 00:13 | I'll begin by showing you how to
create a pivot table from data already in
| | 00:16 | your Excel workbooks.
| | 00:18 | Then, using that knowledge as a base,
I'll demonstrate how to create pivot tables
| | 00:22 | using data from an external source.
| | 00:24 | We'll enhance your ability to analyze
your data by showing how to reorganize or
| | 00:27 | pivot a pivot table.
| | 00:29 | After you have organized your pivot
table's data you can sort the data to show
| | 00:32 | relative importance and relevance or
create filters that enable you to focus on
| | 00:36 | that data you need to develop
useful insights into your business.
| | 00:40 | I'll show you how to create conditional
formats that highlight subsets of your
| | 00:43 | data and to visualize your data using
dynamic charts called pivot charts that
| | 00:47 | are based on your pivot table data.
| | 00:49 | In short, I'll show you how to summarize
and analyze data using one Excel's most
| | 00:53 | powerful and overlooked features.
| | 00:55 | Let's get started with Excel
2007 Pivot Tables for Data Analysis.
| | 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 the disk,
you have access to the Exercise Files
| | 00:08 | used throughout this title.
| | 00:10 | Exercise Files for this
title are arranged by chapter.
| | 00:12 | So for example, I have seven chapters
of lessons, and each chapter folder
| | 00:17 | contains the relevant files for that lesson.
| | 00:20 | So for example, in Chapter05, which
covers conditional formatting, you have
| | 00:24 | multiple conditions, editing conditions, creating
| | 00:26 | conditional rules and so on.
| | 00:28 | I put the Exercise Files folder on my
desktop, but you can put it wherever you
| | 00:32 | want on your computer.
| | 00:34 | If you are a monthly or annual
subscriber to lynda.com, you don't have access
| | 00:38 | to the Exercise Files, but you can
follow along on the screen or create your
| | 00:42 | own resources.
| | Collapse this transcript |
|
|
1. Creating and Pivoting PivotTable ReportsIntroducing PivotTable reports| 00:00 | Before I get into the details of how
to create and manipulate pivot tables,
| | 00:04 | I'd like to demonstrate a few of the ways
pivot tables help you analyze your worksheet data.
| | 00:08 | A pivot table looks a lot like a
regular worksheet with rows of data, subtotals
| | 00:14 | here at the end of each year, I have
2008 and 2009, and also Grand Totals,
| | 00:20 | which in this case I have for each
row and for each column, and also a Grand
| | 00:25 | Total for rows and columns, which
basically summarizes every value here in the
| | 00:30 | main body of the pivot table.
| | 00:32 | What pivot tables enable you to do is
change the structure of your data to
| | 00:36 | emphasize different aspects of that data.
| | 00:38 | So for example, I can rearrange the
data within this pivot table dynamically.
| | 00:42 | Say for example, that I wanted
to put Company at the top of the
| | 00:48 | organizational hierarchy.
| | 00:49 | In other words, where right now I have
2008 the year, and then the individual months
| | 00:53 | within the year, same thing for
2009. I have my companies over here.
| | 00:58 | If I wanted to have the companies over
top of 2008 and 2009, I can simply drag
| | 01:04 | the Company field header,
here so that it's above here.
| | 01:09 | Now I have my results broken out by
company: FirmA 2008 and 2009, and each of
| | 01:14 | the years is broken down by month.
| | 01:16 | In this case, I'm emphasizing the
data from each individual month, organized
| | 01:21 | by Company, and that's a little bit
different from the organization that I had here.
| | 01:27 | This, to me, emphasizes the months broken
down by company, where this organization
| | 01:37 | emphasizes Company broken down by months.
| | 01:39 | Once again, it all comes down to the
way that you want to communicate your
| | 01:42 | information, and the
emphasis you want to give it.
| | 01:45 | When you work with a pivot table you
can use the controls in the Pivot Table
| | 01:48 | Field List to select which
fields add to the pivot table.
| | 01:52 | Say for example, if I wanted to
remove Company, I can drag it from the Row
| | 01:56 | Labels area, and that level of organization
gets taken away from the pivot table itself.
| | 02:02 | If I wanted to reintroduce it, I can
just drag Company to any of the label
| | 02:07 | areas, which I will cover later on,
and it reintroduces that level of
| | 02:11 | organization to the pivot table.
| | 02:13 | Other ways that you can reorganize your data
within a pivot table is to create a filter.
| | 02:17 | So for example, if I only wanted to see
the values for FirmA, I can go up here to
| | 02:22 | the list of fields, click the Company
down arrow, select FirmA. Notice that the
| | 02:32 | box next to FirmB is not checked. Click
OK, and I have limited the data inside
| | 02:38 | of the pivot table, so that it
only shows the results for FirmA.
| | 02:42 | If I want to remove the filter, I can
just go back up to the Company bar, and
| | 02:47 | click Clear Filter from
Company, and all the data returns.
| | 02:53 | You can also apply styles to pivot tables.
| | 02:55 | This style that we have right here
is the default style for Excel 2007.
| | 02:59 | If I want to apply a different style,
I can use the controls under the Design
| | 03:04 | contextual tab of the ribbon.
| | 03:06 | Select any PivotTable
Style from the styles gallery.
| | 03:08 | I will go for one that's a little
bit darker, just for maximum contrast.
| | 03:15 | By applying a style, I can either
conform to my company's graphics guidelines or
| | 03:20 | I can make the report more readable.
| | 03:22 | Just back out of this to demonstrate
one other thing, and that is the way that
| | 03:27 | you can conditionally
format data within a pivot table.
| | 03:30 | Say for example, if I wanted to use a
darker color to indicate the higher values
| | 03:36 | in the body of the pivot table and
lighter colors to indicate the lower values.
| | 03:40 | If I wanted to do that, I can get
to the Home tab, click Conditional
| | 03:44 | Formatting, click Color Scales, and
select a scale that has Red for the higher
| | 03:51 | values, Yellow for the lower, click it, and then
select which cells I want to apply the rule to.
| | 03:57 | In this case, I want to apply the rule to
the cells of the body of the pivot table.
| | 04:01 | So I will select All cells showing Sum
of Revenue for Month and Company, and
| | 04:05 | when I select that option, Excel
applies my pivot table conditional formatting.
| | 04:10 | I hope this brief demonstration
has giving you a feel for pivot
| | 04:12 | tables capabilities.
| | 04:14 | In the remaining lessons in this course
I'll go into much more detail on how to
| | 04:17 | create and manipulate pivot
tables and the data they contain.
| | Collapse this transcript |
| Formatting data for use in a PivotTable report| 00:00 | Pivot tables enable you to summarize
and reorganize your data dynamically, but
| | 00:04 | 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 what do I mean by data list?
| | 00:13 | Well, what I mean is that the source
data must be laid out as a table with
| | 00:17 | column headers and no
extraneous data surrounding the table.
| | 00:20 | That's how I have this data laid
out here in my DataPrep workbook.
| | 00:24 | I have columns of data: Year, Quarter,
Month, Company, and Revenue, and in each
| | 00:30 | row within the table
represents a particular data point.
| | 00:33 | For example, if I wanted to find out
FirmA's sales revenue for January 2008, I
| | 00:39 | can just look here in this row, I see 2008.
| | 00:42 | Data was in Quarter number 1, which is correct.
| | 00:45 | The Month of January, the Company
was FirmA and the Revenue was $67,000.
| | 00:50 | All I need to do is create a data list
that has an individual measure for each
| | 00:55 | item that I want to
represent within the pivot table.
| | 00:58 | It is absolutely vital that you have
all of the information for each row: the
| | 01:04 | Year, the Quarter, in this case, the
Month, the Company and the Revenue.
| | 01:10 | Excel can still create the pivot
table if you have blank cells within the
| | 01:13 | data list. It just won't know how to
reorganize the data when you pivot your pivot table.
| | 01:18 | Say for example, if you had no month
in this case for the value in November,
| | 01:24 | then there is no way that Excel could
assign it to the month of November and it
| | 01:27 | would just put it in a
separate group with blank cells.
| | 01:31 | That doesn't give you any useful information.
| | 01:33 | So make sure that all the
data is filled in your data list.
| | 01:37 | This is a data list and the reason that
I show you this basic data list is just
| | 01:41 | in case you'll be applying some of what
you learned here to Excel 2003 for the
| | 01:46 | PC or perhaps Excel 2008 for the Mac.
| | 01:49 | In Excel 2007, there is a new
construction and that is called an Excel Table.
| | 01:54 | An Excel Table is a much more powerful
and versatile tool, especially when it
| | 01:59 | comes to creating pivot tables, and I'll
show you why that's the case in a few moments.
| | 02:03 | But first, I'll show you how to
create an Excel Table in Excel 2007.
| | 02:08 | To do that you have your
data formatted as a list.
| | 02:11 | You click any cell within the data list,
and then on the Home tab of the ribbon
| | 02:15 | in the Styles group.
| | 02:17 | You can click Format as Table, and
I'll just select the most basic style
| | 02:23 | here, style number one.
| | 02:24 | In the Format As Table dialog box, the
Excel will ask where the data is for your table.
| | 02:29 | It selects the active group
automatically, A1 through E49 in this case, which is
| | 02:34 | correct, and my table does have
headers. That's the Year, Quarter, Month,
| | 02:38 | Company, and Revenue.
| | 02:39 | Now I can click OK and Excel creates the table.
| | 02:45 | Now, what are some of the
advantages of creating an Excel table?
| | 02:48 | Well, first off, Excel, when you create a
pivot table, refers to the table in its
| | 02:53 | entirety, as opposed to data list.
| | 02:56 | If you use a data list as source for a
pivot table, Excel says, "Okay, the data
| | 03:00 | starts in cell A1 and it goes
down to sell E49," in this case.
| | 03:07 | The problem is that if you want to
update the data source when you work
| | 03:11 | from a data list, you need to let
Excel know that you've updated the data
| | 03:14 | source by adding rows to it or
subtracting rows from it, and Excel can't
| | 03:19 | detect that automatically.
| | 03:21 | If you use an Excel table as the data
source for your pivot table, Excel can say,
| | 03:25 | "I'm referring to the data from the
table named Table1." Regardless of how many
| | 03:31 | rows it contains, and how you change
it either by adding or subtracting rows,
| | 03:35 | Excel knows that it just
wants all the data from Table 1.
| | 03:39 | While I am here, I'll show you, just a
few other capabilities of an Excel table.
| | 03:44 | One thing you can do is to
filter the data in a table.
| | 03:48 | Say for example, if I wanted to see
only the results from January, I can click
| | 03:52 | this filter arrow here at the top of
the Month column, clear Select All,
| | 03:57 | select the January box, click OK,
and that limits the data, so only the
| | 04:02 | results from January appear.
| | 04:04 | In the same way, if I want to remove
the filter, I can Clear the Filter from
| | 04:08 | Month again by clicking the filter
arrow and then clicking this menu item, and
| | 04:13 | all the data reappears.
| | 04:14 | Another great thing about an
Excel table is the Totals Row.
| | 04:19 | The Totals Row appears at the bottom.
| | 04:22 | I don't have it turned on, but if I
click any cell in the Excel table and then
| | 04:29 | on the Design contextual tab, in Table
Style Options, I can click Total Row, and
| | 04:35 | Excel provides a summary of the
information in the Excel table.
| | 04:38 | In this case, it gives me a summary of
the values in the Revenue column, and
| | 04:45 | it's finding the sum.
That's what the value 109 means.
| | 04:49 | That means that it's finding a sum.
| | 04:51 | And that is an artifact
of the =SUBTOTAL formula.
| | 04:55 | You can change the summary operation
in the Total row. Say for example, if I
| | 04:59 | wanted to go to Average, I can click
Average, and you get the average value from
| | 05:05 | the column, but in this case I do
want Sum, so I'll switch it back.
| | 05:11 | Once you have your source data arranged in
a data list, you can create a pivot table.
| | 05:16 | I'll cover the other case in the lesson
in title Consolidating Data for Multiple
| | 05:19 | Sources, which is found later in this chapter.
| | Collapse this transcript |
| Creating a PivotTable report| 00:00 | Pivot Tables are powerful and
flexible Excel analysis tools.
| | 00:04 | A Pivot Table let's you rearrange,
sort and filter a data set on the fly so you
| | 00:08 | can analyze it from several different
perspectives with a minimum of effort.
| | 00:12 | In this lesson, I will show you how to
create a Pivot Table from a data list
| | 00:15 | that's stored in the same workbook.
| | 00:16 | So as you can see here in my workbook,
I have created an Excel table, which is
| | 00:21 | just a more advanced form of a data list.
| | 00:24 | When I am ready to create the Pivot
Table I click any cell in the Excel table,
| | 00:29 | and then on the Insert tab of the
ribbon I can click the Pivot Table button.
| | 00:35 | Excel then displays the Create Pivot Table
dialog box and I can verify the data source.
| | 00:41 | In this case, it is Table1 and I do want
the Pivot Table to appear on a new worksheet.
| | 00:46 | I could put it on an existing worksheet if I
had another one in this workbook, I don't.
| | 00:50 | I just have one, which is called Sheet2.
| | 00:53 | But I usually put my Pivot Table on a
completely new worksheet for a couple of reasons.
| | 00:57 | The first is that I don't want the
Pivot Table on the same worksheet as the
| | 01:01 | source data because the page gets
crowded and you have a hard time
| | 01:04 | distinguishing one number from another.
| | 01:06 | So I always put it on a new worksheet,
but if there is an existing worksheet in
| | 01:11 | your workbook that is blank and you
want to put it there, that's perfectly fine.
| | 01:15 | So I verified my settings and I can click OK.
| | 01:19 | So, Excel just created the Pivot Table
and it is on a new worksheet called Sheet1.
| | 01:25 | When you create a Pivot Table, Excel
displays the Pivot Table field list, and
| | 01:30 | this is a Task Pane that contains
controls you can use to manipulate the
| | 01:33 | contents of your Pivot Table.
| | 01:35 | I have the five fields here. I have Year,
Quarter, Month, Company and Revenue,
| | 01:40 | and these fields correspond to the
columns from the data source that I used.
| | 01:46 | I can now add those fields to the
Pivot Table to organize my data.
| | 01:50 | So let's say that I wanted to go by Year,
and I added Year as a Row Header, then
| | 01:57 | I will add Month also as a Row Header.
| | 02:00 | And notice that when I dragged the
Month Header below the Year Header, Excel
| | 02:05 | created this organization here where Year
is the top-level and Month is the second
| | 02:11 | level and it's repeated here.
| | 02:14 | If I were to switch that order, say to
put Year below Month, then you would see
| | 02:18 | January broken down by 2008 and
2009, February 2008, 2009 and so on.
| | 02:25 | But if I switch it back to Year first
and then Month I get this organization,
| | 02:29 | which to me it seems more natural.
| | 02:31 | Now for my Column label,
I will bring down Company.
| | 02:35 | Put that here so I have FirmA and FirmB,
and now I can drag my Revenue to the
| | 02:41 | Values area and it will populate
the data area of the Pivot Table.
| | 02:45 | Now one thing you will notice about the
default Pivot Table that Excel creates.
| | 02:51 | It has its subtotals at the top of each group.
| | 02:54 | Say for example, for 2008 you have the
subtotal 963 for FirmA, 924 for FirmB, and
| | 03:02 | 1887, which is the total of those two values.
| | 03:05 | I prefer for the subtotals to
appear at the bottom of the group.
| | 03:08 | I will show you how to make this change
later but it's something that I always
| | 03:11 | do so I wanted to throw it in here.
| | 03:14 | To display your subtotals at the bottom
of a group, click any cell on the Pivot
| | 03:18 | Table and then on the Design
Contextual tab, in the Layout group, click
| | 03:24 | Subtotals>Show all Subtotals at Bottom of Group.
| | 03:28 | Then you have 2008 and blanks here,
values for the month, total for each
| | 03:34 | row giving you January for FirmA and FirmB,
and then you have the subtotals for 2008.
| | 03:40 | For me, that just makes a lot more sense.
| | 03:43 | Now that you have learned how to create
a Pivot Table, you can move forward and
| | 03:46 | discover how to rearrange your data dynamically.
| | Collapse this transcript |
| Pivoting a PivotTable report| 00:00 | The real power of a Pivot Table comes out
when you want to rearrange your data dynamically.
| | 00:05 | 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 Pivot Table.
| | 00:11 | In a Pivot Table, the Row and Column
Headers provide your data structure.
| | 00:15 | So in this case, I have the companies,
FirmA and FirmB, which provide the Column
| | 00:20 | Headers and then I have
the Month as the Row labels.
| | 00:23 | So I have January, February,
March, April and so on.
| | 00:26 | Changing Row and Column Header
positions changes the data's arrangement
| | 00:30 | within the Pivot Table.
| | 00:31 | Say for example, if I moved Company on
top of Month, then I have my data laid out
| | 00:39 | by Company and then by Month.
| | 00:41 | If I were to move Month to the Column
labels area and make the Month names the
| | 00:47 | Column Headers then the data
would take on this arrangement.
| | 00:51 | Now let's see what happens when I add a
second level of organization to a Pivot Table area.
| | 00:56 | So here I have the Row
labels, Company, and then Month.
| | 01:00 | It's arranged first by
Company and then by Month.
| | 01:05 | What happens if I add a third layer?
| | 01:06 | Say I bring down Year, and put it over Company.
| | 01:13 | Now I have my data laid out first by
Year, then by Company, and then by Month.
| | 01:18 | If I want to rearrange the data to
emphasize the months over the years then I
| | 01:23 | can move Month to the top of this
organizational level, and I see how FirmA and
| | 01:31 | FirmB did in 2008, month of January,
and then I see the results from January of
| | 01:36 | 2009 again for FirmA and for FirmB.
| | 01:39 | I get my January subtotal and it moves
on down for each month using the same
| | 01:44 | structure, first by Month,
then by Year, and then by Company.
| | 01:49 | And, as always, I can drag any element
over to the Column labels area so that we
| | 01:54 | have the data in a cross tabular format.
| | 01:57 | So I'll take over Company, and I get
FirmA and FirmB, January for 2008 and 2009.
| | 02:04 | And if I want to change the
organization on the Row level to emphasize Year
| | 02:08 | over Month, then I can drag Month below
Year and I have this organization where
| | 02:14 | I have each company, with their
results called out first by Year and then by
| | 02:19 | Month within the Year.
| | 02:20 | If you are working with an extremely
large Pivot Table or if your Pivot Table
| | 02:24 | draws data from an outside source,
perhaps over a network, there is one trick
| | 02:28 | that I'd like to show you that
can make your life a lot easier.
| | 02:31 | Large Pivot Tables or pivot tables
that draw data over networks can sometimes
| | 02:34 | take a long time to update
when you make an individual pivot.
| | 02:38 | So if you are going to pivot a Pivot
Table that takes a long time to update, you
| | 02:43 | can defer the Layout Update.
| | 02:45 | To do that you go into the Pivot Table
field list and, at the bottom, you check
| | 02:51 | the Defer Layout Update box.
| | 02:54 | Then you can make any changes you
like, and Excel will not update the
| | 03:00 | organization of the Pivot Table
until you click the Update button.
| | 03:06 | When you do, Excel changes the layout
of the Pivot Table to reflect the changes
| | 03:10 | you made here in the Pivot Table's organization.
| | 03:13 | If you want your changes to
appear immediately, just clear the Defer
| | 03:17 | Layout Update checkbox.
| | 03:19 | Changing a Pivot Table's arrangement
shifts the data's emphasis, enabling you to
| | 03:23 | examine the data from different
perspectives quickly and easily.
| | Collapse this transcript |
| Configuring a PivotTable report| 00:00 | When you create a Pivot Table in Excel
2007, Excel shows a number of Pivot Table
| | 00:04 | elements by default.
| | 00:06 | You can show, hide, or in some cases
move these elements to change the Pivot
| | 00:09 | Table configuration to your liking.
| | 00:12 | Whenever you have an active cell within
the body of a Pivot Table, Excel will, by
| | 00:17 | default, show several items.
| | 00:19 | The first are the Pivot Table
tool's contextual tabs on the ribbon.
| | 00:23 | You have the Options contextual tab,
which allows you to change your Pivot
| | 00:27 | Table's options, and then you have
Design, which allows you to change your
| | 00:31 | Pivot Table's appearance.
| | 00:33 | There are some other items as well.
| | 00:35 | The most prominent is
the Pivot Table Field List.
| | 00:39 | You use the Pivot Table Field List to select
which fields should appear within your
| | 00:43 | Pivot Table report and where those
fields are located within the report.
| | 00:46 | As you'll see in later lessons, you
can also use the Pivot Table Field List
| | 00:50 | controls to create Filters.
| | 00:52 | If you want, you can move the
Pivot Table Field List Task Pane.
| | 00:56 | Say for example, if you didn't want it
on the right side of your screen and
| | 01:00 | instead you prefer to have it on
the left side then you can move it.
| | 01:04 | To do that, you would move the mouse
pointer over the Pivot Table Field List
| | 01:09 | Title Bar until the mouse pointer
changes into a four-way pointing white arrow.
| | 01:14 | When it's in that configuration you
can use the left mouse button to drag the
| | 01:18 | Pivot Table Field List Task
Pane over to the left side.
| | 01:23 | When your mouse pointer hits the left
edge of the Excel program window, that's
| | 01:26 | when the Pivot Table Field List
docks to the left side of the window.
| | 01:30 | You can also move the Pivot Table Field
List within the body of the Pivot Table.
| | 01:34 | Again, I have my four-way arrow and if I
want I can just have it here floating in
| | 01:40 | the middle of my worksheet.
| | 01:42 | When you leave the Pivot Table Field
List Task Pane in the middle of your
| | 01:45 | workbook then it's slightly minimized.
| | 01:48 | I prefer to work with it at full size so I
usually leave it docked on the right side.
| | 01:53 | But if you prefer it on the
left you can always drag it over.
| | 01:57 | If you want to hide the Pivot Table
Field List you can do that by clicking any
| | 02:01 | cell in the body of the Pivot Table,
and then on the Options tab, clear the
| | 02:07 | Field List button in the Show and Hide group.
| | 02:12 | So when I click that it is no longer
highlighted and the Pivot Table Field List
| | 02:16 | Task Pane has disappeared.
| | 02:19 | To bring it back I can just click the Field
List button again and the Task Pane reappears.
| | 02:25 | Two other items that I'd like to mention are
the Expand and Collapse buttons and Field Headers.
| | 02:30 | The Expand and Collapse buttons appear
besides grouping levels that are not the
| | 02:36 | lowest level within a field.
| | 02:39 | I'll show you what I mean.
| | 02:40 | In this case, I have Row Labels
made up for Year and for Month.
| | 02:45 | Month is the lowest level so you can't
collapse that, but you can collapse by Year.
| | 02:51 | So for example, if I have this Expand
and Collapse button here, by 2008 it's
| | 02:56 | currently configured as a minus sign,
which means that when I click it it will
| | 02:59 | hide the detail levels beneath it.
| | 03:02 | If I click, it I only see the subtotal
for 2008. I don't see the individual
| | 03:06 | months like I do with 2009.
| | 03:08 | If I click the button again,
Excel redisplays the levels of detail.
| | 03:14 | If for any reason I don't want to
make the Expand and Collapse buttons
| | 03:17 | available, I can go to the Options
contextual tab on the ribbon and then in the
| | 03:21 | Show and Hide group, I can deselect
the Expand and Collapse buttons control
| | 03:26 | within the Show and Hide group.
| | 03:27 | When I do, the Expand and Collapse buttons
disappear from the body of the Pivot Table.
| | 03:32 | If I want to bring them back, I can just
click any cell on the body of the Pivot
| | 03:35 | Table and then, in the Show and Hide group,
click the buttons and they come back.
| | 03:40 | The final item I'll show
you is the Field Headers.
| | 03:43 | Field Headers are Column Labels and Row
Labels, and the reason that they appear
| | 03:49 | within your Pivot Table is to
give you extra filtering controls.
| | 03:53 | So for example, for Row Labels if I
wanted to filter by Year or by Month, I can
| | 03:58 | select the Year or Month by which I wanted
to filter, and then create the filter here.
| | 04:04 | This functionality duplicates the
functionality in the Pivot Table Field List.
| | 04:08 | Say for example, if I wanted to
filter by Month than I could create my
| | 04:11 | filters by clicking the down arrow on the
Month field's bar in the Pivot Table Field List.
| | 04:16 | For that reason, I usually
hide the Column and Row Labels.
| | 04:21 | To do that, you click any cell on the
Pivot Table and then on the Options
| | 04:24 | contextual tab of the ribbon, you
deselect the Field Headers button.
| | 04:30 | If you prefer to have them back, you
can just click the Field Headers button
| | 04:33 | again and they will reappear.
| | 04:35 | The Pivot Table Field List Task Pane,
Expand and Contract buttons and Field
| | 04:39 | Headers are all useful Pivot Table
components, but you don't have to use them.
| | 04:43 | If you want to remove them from your
Pivot Table's current view, you can do
| | 04:46 | so by using the controls on the Pivot
Table tools Options contextual tab on
| | 04:50 | the ribbon.
| | Collapse this transcript |
| Connecting to an external data source| 00:00 | Excel 2007 enables you to build Pivot
Tables from data found both inside and
| | 00:04 | outside the workbook where
you create the Pivot Table.
| | 00:07 | If you want to create a Pivot Table from
a data source you either can't or don't
| | 00:11 | want to import into Excel directly,
you can create a connection to that data
| | 00:15 | source and work with it in place.
| | 00:17 | To create a Pivot Table from an
external data source, you start with a
| | 00:21 | blank worksheet and then on the Insert tab
of the ribbon, click the Pivot Table button.
| | 00:28 | In the Create Pivot Table dialog box,
you want to select the Use an external
| | 00:32 | data source option, and then to let
Excel know from where to draw the data,
| | 00:38 | click Choose Connection.
| | 00:40 | When you do, the Existing Connections
dialog box appears. The connection that you
| | 00:44 | want to use in this case
doesn't appear in the list.
| | 00:47 | So I will click Browse for More.
| | 00:51 | And I can now select my data source.
| | 00:54 | The data source that I want is on my
desktop, in the Exercise Files folder, in
| | 01:01 | Chapter01, and in this
case, I named it OutsideSource.
| | 01:04 | So to identify it as the source file I want to
use, I click it and then click the Open button.
| | 01:11 | After I do that the Select Table
dialog box appears, and I can identify the
| | 01:17 | table or other data list
within the workbook that identified.
| | 01:21 | In this case, I want to use the data
from Sheet2, and I know, just from having
| | 01:27 | examined the data source before, that the
first row of data does contain Column Headers.
| | 01:32 | So with that source selected, I can
click OK, and the connection name appears
| | 01:38 | inside the Create Pivot Table dialog box.
| | 01:40 | I am working with a blank worksheet so
I can't select existing worksheet and
| | 01:46 | verify that the Pivot Table will appear
starting on Sheet1 in cell A1, which is
| | 01:52 | the top left corner of
the worksheet named Sheet1.
| | 01:55 | Everything looks good, so I can click
OK, and Excel creates my Pivot Table.
| | 02:00 | And as before, I can add my fields
and the Pivot Table is ready to go.
| | 02:11 | One thing you should note is that if
you use an Excel workbook as your data
| | 02:14 | source, you can't open that workbook
while the workbook that contains the
| | 02:18 | Pivot Table is open.
| | 02:19 | What happened is that because we are
using the data in a Pivot Table in the
| | 02:24 | workbook that is currently open,
Excel locks that other file.
| | 02:28 | But once you close the workbook that
contains the Pivot Table you can open the
| | 02:31 | source workbook normally.
| | 02:32 | If possible, you should draw the data
for a Pivot Table from the original source
| | 02:36 | so that any changes to that source
are reflected in the Pivot Table.
| | Collapse this transcript |
| Consolidating data from multiple sources| 00:00 | Most of the time, you will create a
Pivot Table from a data list or Excel table.
| | 00:04 | But you can also summarize multiple
data ranges using a Pivot Table if the data
| | 00:08 | is formatted appropriately.
| | 00:09 | To consolidate a series of data ranges
into a Pivot Table, the ranges must be
| | 00:14 | laid out in cross-tabular format
and have exactly the same structures.
| | 00:18 | In this case, I have three different
worksheets: SupportCalls, OrderCalls and
| | 00:23 | ReturnCalls, and my data is laid out in
cross tabular format. All that means is
| | 00:28 | that I have row headers and the column headers.
| | 00:32 | Now notice that I have Region, which is
the header for this area and Year, which
| | 00:37 | is the header for this area in the same cell.
| | 00:41 | And the reason I am doing that
is because I need this range to be
| | 00:45 | perfectly rectangular.
| | 00:47 | In other words, if I tried to put
Region in this cell and Year in this cell,
| | 00:53 | then regardless of what I did with the
borders or cell formatting, Excel would
| | 00:58 | try to include all of these cells in
the Pivot Table, which would be incorrect.
| | 01:03 | So to avoid that I put Region and Year
in the same cell and just to show you
| | 01:09 | that I have the data laid out the
same way on OrderCalls and ReturnCalls.
| | 01:14 | So, now to consolidate data from
several ranges into a Pivot Table you need to
| | 01:20 | press Alt+D+P. Pressing Alt+D+P
displays the PivotTable and PivotChart Wizard.
| | 01:30 | This was around in Excel 2003 and
earlier versions of the program and you don't
| | 01:35 | need to use it right now because of the
new Pivot Table Creation functionality
| | 01:39 | in Excel 2007, but if you ever want to
display it, again, you can just press
| | 01:43 | Alt+D+P. Now that you have the wizard
displayed, you can select the Multiple
| | 01:49 | consolidation ranges option and click Next.
| | 01:54 | The second step of the PivotTable and
PivotChart Wizard asks if you want to
| | 01:59 | create a page field.
| | 02:00 | A page field I will discuss later when I
get to filtering, but basically what it
| | 02:03 | allows you to do is create filters in a
Pivot Table using a field that doesn't
| | 02:09 | affect the field
organization of your Pivot Table.
| | 02:12 | It's only used for filtering.
| | 02:14 | So in this case, you will create your
own page field, which will be none, but
| | 02:18 | we will select that option later,
then click Next and on this page you can
| | 02:25 | select the worksheet ranges that you
want to consolidate, and you will need to
| | 02:29 | do that individually.
| | 02:31 | To select the first range, you click
the RefEdit Control, which is just short
| | 02:36 | for Reference Edit and that's at
the right side of the Range box.
| | 02:41 | Select the cells that you want to
include in your Pivot Table, which are here, B3
| | 02:45 | through F7 on SupportCalls.
| | 02:48 | Click the RefEdit Control again and click Add.
| | 02:53 | That's there. Now you can add the other two.
| | 02:55 | Clicking the RefEdit Control,
changing to the OrderCalls worksheet.
| | 03:01 | That reference looks right, click the
RefEdit Control and click Add, so that it
| | 03:06 | adds the range to the All ranges area.
| | 03:09 | And we will do our third and final area.
| | 03:12 | ReturnCalls, B3 through F7, looks correct,
click the RefEdit Control and click Add.
| | 03:20 | So all three of the ranges that I
want to summarize in the Pivot Table now
| | 03:23 | appear in the All ranges box.
| | 03:25 | How many page fields do you want? That's zero.
| | 03:29 | So everything else looks good, I will
click Next and we have the final page.
| | 03:36 | And here I just select where I
want to put the PivotTable Report.
| | 03:39 | I personally always choose a New worksheet.
| | 03:42 | That option is selected, so I can click
Finish and Excel creates my Pivot Table.
| | 03:47 | Now that I have created my PivotTable
you see that the PivotTable Field List has
| | 03:51 | appeared and I have three different areas.
| | 03:54 | I have Row, Column and Value.
| | 03:57 | Now those names aren't particularly descriptive.
| | 04:00 | So what I want to do is change the
names of those fields to reflect the data
| | 04:05 | that I have, which in this case are
Years along the top here and Region:
| | 04:10 | North, South, East and West.
| | 04:12 | Unfortunately, I can't change
the name of the Value field.
| | 04:16 | It's just value, I don't know why Excel
doesn't allow us to change it but I can't.
| | 04:21 | To change the name of the Row field, I
will click any cell that provides a row
| | 04:28 | value, in this case North, and here on
the Options Contextual tab, in the Active
| | 04:35 | Field area, I have the name of the field.
| | 04:38 | The name of the field should be Region,
so I will change it to that, press Enter
| | 04:44 | and you see that the value has been
updated here and it's also been updated in
| | 04:48 | the PivotTable Field List.
| | 04:49 | I will do the same for the
Columns, which are the Years.
| | 04:54 | Click a column value and then in the
Active Field box I will type Year, press
| | 05:00 | Enter and the field header name updates
here and also down in the Label areas.
| | 05:08 | You probably won't create many
PivotTables by consolidating multiple data
| | 05:11 | ranges, but it's nice to know the
capability is available, if you're working
| | 05:15 | with legacy workbooks that have
data laid out in cross tabular formats.
| | Collapse this transcript |
| Updating and refreshing PivotTable data sources| 00:00 | Companies generate new sales,
budgetary and operations data all the time.
| | 00:04 | So PivotTable Data sources rarely remain static.
| | 00:07 | In this lesson, I will show you how to
manage your data connection to ensure
| | 00:11 | your Pivot Table contains
the most recent data available.
| | 00:14 | You can display your PivotTables
Data connection by clicking any cell in
| | 00:17 | the PivotTable and then clicking on
the PivotTable tools Options Contextual
| | 00:21 | tab Change Data Source.
| | 00:26 | When you do, the Change
PivotTable Data Source dialog box appears.
| | 00:30 | You can see here that the name of
the table from which this Pivot Table
| | 00:35 | is drawing its data is called
FirmData, which is correct.
| | 00:38 | It's on Sheet2 of this workbook.
| | 00:40 | So I can see that that's correct.
| | 00:43 | If you want to change the data source
you can select a new data source within
| | 00:46 | the workbook, but notice that the
external connection options here, Use an
| | 00:50 | external data source and
Choose Connection, are grayed out.
| | 00:53 | When you create a Pivot Table you can
change the data source to one of the same kind.
| | 00:57 | That is, you can swap one internal data
source for another or one external source
| | 01:01 | for another, but you can't change
from one type of connection to another.
| | 01:06 | Let's get back to the current Pivot Table,
which draws its data from an Excel table.
| | 01:11 | One of the major benefits of using an
Excel table as your data source is that
| | 01:14 | when you add a row to the table and
refresh the data connection, the new data
| | 01:18 | appears in the Pivot Table automatically.
| | 01:21 | So I will show you what that looks like.
| | 01:23 | The Excel table from which we are
drawing or data is on Sheet2, and if I add a
| | 01:29 | new row to the Excel table, which is
our data source, say for 2010, Quarter 1,
| | 01:37 | Month of January for FirmA, and
let's say that they had sales of 100,000.
| | 01:43 | If I press Enter, I have added the data
to the Excel table and I can go back to
| | 01:50 | my Pivot Table on Sheet1 and then, on
the Options tab, click the Refresh button
| | 01:58 | and when I do the data appears
automatically in the Pivot Table.
| | 02:02 | By comparison, if you were to create
your pivot table using a regular data list
| | 02:06 | as opposed to an Excel table then
this automatic updating wouldn't happen.
| | 02:10 | Let me show you what I mean.
| | 02:11 | I will change the data source for this
PivotTable to a data list that I have on
| | 02:15 | Sheet3 and that goes from cell A1 to cell E49.
| | 02:21 | So I will go back to Sheet1 and I will
Change the Data Source from A1 to E49.
| | 02:39 | I will click the RefEdit button to
bring back the PivotTable dialog box and
| | 02:44 | I see that my Range Sheet3 A1-E49 is correct.
I will click OK and Excel updated the data.
| | 02:52 | You will notice that the
row for 2010 is now gone.
| | 02:55 | Now let's see what happens when I add
a row of data here to my data list on
| | 03:00 | Sheet3, the new data source for the Pivot Table.
| | 03:03 | If I type in the same data I had
before 2010, 1st Quarter, January for FirmA
| | 03:11 | and the number 100, representing Revenue and
press Enter, I have a new row in my data list.
| | 03:18 | But when I go back to the Pivot Table
and then on the Options Contextual tab I
| | 03:23 | click Refresh, the new data in the data
list doesn't appear and that's because
| | 03:27 | Excel has no way of knowing that I
added a row of data to the data list.
| | 03:32 | It doesn't update the same
way that an Excel table does.
| | 03:35 | If I am using a data list and I want to
update the data source, I need to go to
| | 03:39 | Change Data Source and edit the
reference here in the Table/Range box.
| | 03:44 | In this case, my data range goes
from A1-E50, so I can update the final
| | 03:50 | reference, click OK and now the data appears.
| | 03:55 | So the question is, why would you use a
data list when Excel table is available?
| | 03:59 | And the answer is that if you are
working with clients or colleagues, who do not
| | 04:04 | have access to Excel 2007,
| | 04:06 | Excel tables are brand-new in Excel 2007
and they are not available in Excel 2003,
| | 04:12 | So if you are working with individuals
who only have access to Excel 2003 or
| | 04:17 | earlier versions of the program then
you should use a data list and update your
| | 04:22 | data sources manually.
| | 04:23 | But if everyone you're working with is
using Excel 2007 or later then you can
| | 04:28 | use Excel Tables and the
data will update automatically.
| | 04:32 | Whenever I view a Pivot Table, I always
click the Refresh button to include any
| | 04:36 | updates to the data source.
| | 04:37 | If you create Pivot Tables based
on Excel Tables, the new data will
| | 04:41 | appear automatically.
| | 04:42 | If your Pivot Table draws its data
from a data list, you should examine the
| | 04:46 | source data list to identify any
new rows and edit the data source to
| | 04:49 | reflect the change.
| | Collapse this transcript |
| Managing PivotTables| 00:00 | Pivot Tables behave a bit
differently than other Excel workbook elements.
| | 00:03 | So you will need to use a few slightly
different techniques to Select, Move,
| | 00:07 | Copy and Delete them from within your workbook.
| | 00:10 | One great thing about Pivot Tables in Excel
2007 is that these pivot tables have names.
| | 00:15 | You can use those names to refer to
Pivot Table Data and formulas and you can
| | 00:20 | also use them to refer to Pivot
Tables in other ways that I will get into
| | 00:24 | later in this course.
| | 00:25 | If you want to rename a PivotTable
from its default name, which would be
| | 00:29 | something like PivotTable 1, you can
click any cell in the PivotTable and then
| | 00:34 | on the Options tab of the menu in the
PivotTable group, just type a new name
| | 00:41 | for the Pivot Table.
| | 00:42 | In this case, I'll call it CorpRevenue
to reflect the fact that the Pivot Table
| | 00:48 | summarizes Corporate Revenue data, press
Enter and now the Pivot Table has a new name.
| | 00:53 | If you want to remove everything from
your pivot table without getting rid of
| | 00:57 | the pivot table, for example, if you
wanted to remove all the fields all the
| | 01:01 | filters and so on, you can do so
with a few clicks of the mouse.
| | 01:05 | You click any cell in the Pivot
Table and again on the Options tab of the
| | 01:10 | ribbon, select Clear and then click
Clear All, and that removes everything
| | 01:17 | from the Pivot Table.
| | 01:19 | But it's an undoable operation, so if
you want to bring everything back you
| | 01:22 | can just click the Undo button on the
Quick Access toolbar or press Ctrl+Z to
| | 01:28 | Undo the last operation.
| | 01:29 | Now let's say that you want to move a
Pivot Table to another Worksheet in your
| | 01:33 | Workbook. To do that you click any
cell in the Pivot Table and again, on the
| | 01:39 | Options tab of the ribbon, click the Move
PivotTable button and you can select a new destination.
| | 01:46 | In this case, I will move it to a New
Worksheet, click OK and you get your new Pivot Table.
| | 01:53 | Again, it's an undoable operation.
| | 01:55 | So I'll click the Undo button and I still
have the worksheet, so I will click undo again.
| | 02:02 | Excel doesn't get rid of
the worksheet you created.
| | 02:04 | It's still there and it's called
Sheet1 in this case, but clicking the Undo
| | 02:08 | button again moved you back to the
worksheet that contained your original Pivot Table.
| | 02:13 | If you want to create a second Pivot
Table from the same data range you can,
| | 02:16 | but not by pasting.
| | 02:18 | Instead, you would go through the
procedure you went through to create the first
| | 02:21 | Pivot Table, but this time you
would put it on the new worksheet.
| | 02:25 | So again, if we were to go to the
SourceData worksheet and click Insert>
| | 02:29 | PivotTable and use the controls
in Create PivotTable dialog box.
| | 02:34 | You can select a New Worksheet and
click OK and you would have a second
| | 02:37 | independent Pivot Table
based on the same data source.
| | 02:41 | Pivoting one pivot table
would not affect the other one.
| | 02:43 | Now I will go back to the Pivot Table.
| | 02:47 | If you want to copy a particular Pivot
Table arrangement to another worksheet,
| | 02:51 | you can do so by clicking any cell in
the Pivot Table and then on the Options
| | 02:57 | tab clicking Select>Entire PivotTable to
select the entire pivot table, then you
| | 03:06 | press Ctrl+C or Copy using
the Controls on the Ribbon.
| | 03:12 | Click the cell where you want to paste
the top left corner of the Pivot Table
| | 03:16 | data, A1 here on the worksheet PTPaste
and then on the clipboard click Paste or
| | 03:25 | you can click Ctrl+V.And here you
have a second copy of the Pivot Table.
| | 03:31 | You can convert a pivot table to a
regular cell range by selecting the entire
| | 03:35 | Pivot Table and then, on the Home tab of
the ribbon, clicking Copy or again, you
| | 03:41 | can press Ctrl+C and then you click
the down arrow at the bottom of the Paste
| | 03:46 | button and click Paste Values.
| | 03:51 | When you do, Excel pasted the entire
contents of the Pivot Table including the
| | 03:56 | headers, the Sum of Revenue, Column
Labels, Row Labels, FirmA, FirmB, Grand
| | 04:01 | Total and so on, but it is
not actually a Pivot Table.
| | 04:04 | It is just a regular data range.
| | 04:07 | Finally, if you'd like to delete a
Pivot Table, you can do so by clicking any
| | 04:13 | cell in the Pivot Table and then
clicking Options>Select>Entire PivotTable to
| | 04:21 | select the entire pivot table
and then just press the Delete key.
| | 04:26 | With these techniques in mind, you'll
be able to manage your Pivot Tables
| | 04:29 | more effectively.
| | Collapse this transcript |
|
|
2. Summarizing PivotTable DataAdding, removing, and positioning subtotals and grand totals| 00:00 | Excel Pivot Tables display your data in
as much or as little detail as you prefer.
| | 00:05 | Pivoting and filtering the pivot table
changes how the individual data elements
| | 00:08 | are displayed, but you can also position
the subtotals and grand totals for rows
| | 00:13 | and columns to summarize your data as desired.
| | 00:15 | When you work with subtotals, which
are these values here, say I have a sub
| | 00:20 | total for the year 2008 and another
subtotal that deals only with the values for 2009,
| | 00:26 | I can reposition those subtotals so that
they appear at the top of the group, at
| | 00:31 | the bottom of the group or I
can turn them off entirely.
| | 00:34 | If I want to reposition the subtotals,
I click any cell in the Pivot Table that
| | 00:38 | I want to change and then on the
Design tab of the ribbon, I can go to the
| | 00:43 | Layout group and use the controls to
change my subtotals and grand totals.
| | 00:47 | So if I want to change the position of
the subtotals, I can click the Subtotals
| | 00:51 | button and select which of
these three options I want.
| | 00:54 | Right now the subtotals are shown at
the top of the group. If I prefer to show
| | 00:58 | them at the bottom of the group, which
I personally do, then I can select the
| | 01:02 | Show All Subtotals at Bottom of Group
option and Excel displays the subtotals
| | 01:07 | beneath the detail lines that
provide the values for the subtotal.
| | 01:12 | If I prefer the subtotals to appear at
the top, I can click Subtotals, Show at
| | 01:17 | the Top of the Group and
Excel places it in there.
| | 01:20 | If I'd rather not have the subtotals
in the Pivot Table or if the data in the
| | 01:25 | Pivot Table doesn't make any sense
when it's subtotaled, for example, if you
| | 01:28 | were summarizing temperature data it
wouldn't make any sense to have a grand
| | 01:31 | total of all the temperatures.
| | 01:33 | So you would get rid of the subtotals.
| | 01:35 | To do that, you can click the
Subtotals button and select the Do Not
| | 01:39 | Show Subtotals option.
| | 01:41 | Grand Totals summarize all of
the values in a row or column.
| | 01:45 | You can't reposition them, but you can
turn them on or off for rows, columns or both.
| | 01:50 | You might want to turn off Subtotals
and Grand Totals to concentrate on the
| | 01:53 | individual values, but I leave
them turned on most of the time.
| | 01:57 | That's said, if you want to get rid
of your grand totals, you can go to the
| | 02:00 | Layout group, again on the Design
Contextual tab of the ribbon, click Grand
| | 02:05 | Totals and then you can
select how you want to affect them.
| | 02:08 | If you want to turn them all the way
off for Rows and Columns, click that and
| | 02:12 | they go away or you can select
any other configuration you like.
| | 02:16 | I like to have them On for Rows and
Columns, so I will turn them back on.
| | 02:19 | If you change the way the Pivot Table
displays data in its data area, Excel
| | 02:24 | applies that change to the
Subtotal and Grand Total fields as well.
| | 02:27 | I will show you how to make those
changes in Chapter 4 lesson 4, Changing the
| | 02:31 | Pivot Table data field number format.
| | 02:34 | Subtotals and Grand Totals provide easy-
to-read summaries of the values in your
| | 02:37 | Pivot Tables rows and columns.
| | 02:39 | If you find those extra rows and
columns or a distraction or if you just want
| | 02:42 | to emphasize the values within the
body of the data area, you can hide them
| | 02:46 | quite easily.
| | Collapse this transcript |
| Changing the data field summary operation| 00:00 | Most data lists you summarize using
Pivot Tables will have some sort of
| | 00:03 | financial or personnel data such as
sales or hours worked, which means that you
| | 00:08 | will usually want to find the
sum of the values in the list.
| | 00:11 | You're not limited to adding
the values together though.
| | 00:13 | You can choose from several summary
operations and also have the ability to
| | 00:17 | change the type of value shown in
the data area of the Pivot Table.
| | 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:27 | I will run you through a
few of his examples here.
| | 00:30 | If you want to change the summary
calculation used in a pivot table, click any
| | 00:34 | cell in the Pivot Table Data area,
again not the headers but actually here in
| | 00:40 | the data area, and then on the Options
Contextual tab, click Field Settings.
| | 00:47 | It's here in the Active
Field group, on the ribbon.
| | 00:50 | Click Field Settings.
| | 00:52 | You can use the controls in this dialog box
to change how Excel summarizes your values.
| | 00:58 | Right now, you see that we have
Summarize by as the selected tab, I will show
| | 01:02 | you the Show values as tab a little
bit later, but we have Summarize value
| | 01:06 | field by, and then we have the operations that
you can use. There is a list of them there.
| | 01:13 | Say for example, that I wanted to
change from sum to average, I could click
| | 01:16 | Average, click OK and now I
have the average of the values.
| | 01:22 | But notice that the main data points
haven't changed. That's because the value is
| | 01:27 | by mathematical definition, its own average.
| | 01:30 | Even though the values in the body of
the data area didn't change, the grand
| | 01:35 | total summary operation did.
| | 01:38 | So for example, we have 69+131 is 200.
| | 01:42 | We have two values.
| | 01:43 | Divided by 2 is 100.
| | 01:45 | So that's the average for
the grand total for this row.
| | 01:48 | And then we have the same calculations
for each individual column within a year.
| | 01:54 | Say for example, we have 2009 for
FirmA, the average monthly revenue was
| | 01:59 | 80.25 and here's the same value for 2009 and
here is the average of those two values again.
| | 02:05 | And the same data occurs below for FirmB,
again for each month for 2008 and 2009
| | 02:13 | with all of the summary totals here,
both the subtotals and grand totals for the
| | 02:17 | entire data collection.
| | 02:18 | I will just show you one other
calculation and that is Count.
| | 02:23 | So we have Field Settings, click Count and
all this tells you is the number of measures.
| | 02:30 | We had one measure for January
2008 for FirmA, we had one for 2009,
| | 02:36 | again January for FirmA.
| | 02:38 | So we have a total of two.
| | 02:40 | Again, this just counts the number
of individual data lines that you had
| | 02:44 | providing data for these values.
| | 02:46 | In the end, it looks like we had 48
total measures, which for two companies, for
| | 02:51 | two years, by 12 months, that multiplies
out to 48, so that is the correct total.
| | 02:56 | And I will switch it back to Sum and
show you a few other ways that you can
| | 03:02 | change the Summary Operations.
| | 03:04 | If you want to summarize your values
in comparison to other values within the
| | 03:07 | Pivot Table, you can do that.
| | 03:09 | When you're ready, on the Options tab,
click Field Settings and here is where you
| | 03:15 | use the control on the Show values as tab.
| | 03:17 | So I will display that.
| | 03:19 | First thing you do is select how you
want to show the values, in other words,
| | 03:22 | what you want to compare them to.
| | 03:24 | So right now we have them as Normal,
they are just displaying the values, but if
| | 03:28 | you want to change that you can make it
the Difference From another value, you
| | 03:32 | can make it a Percentage
of another value or so on.
| | 03:36 | For our first operation, let's look
at the change from the previous year.
| | 03:40 | In that case, it would be Difference
From and then I can have my base field. In
| | 03:45 | this case, I want to compare one year's
results to another year's results, say
| | 03:49 | January 2009 to January 2008. And I
want to make it the previous year,
| | 03:54 | so I'll use, as my Base Item, Previous,
not 2008, in which case everything would
| | 04:01 | be compared to 2008. If, for example, I
had data for 2000 and 2011, all of those
| | 04:07 | years would be shown in comparison to 2008.
| | 04:09 | But using Previous, 2009 is compared to 2008,
2010 would be compared to 2009 and so on.
| | 04:17 | So I have my selections, I can
click OK and there you have the values.
| | 04:21 | Now notice that 2008 is blank, and
that's because there was no data for 2007, so
| | 04:26 | there's nothing to compare 2008 to.
| | 04:29 | But 2009 is compared to 2008
and it shows the difference.
| | 04:34 | The 61 in this cell indicates that
FirmA increased its revenue in January 2009
| | 04:41 | over January 2008 by $61,000.
| | 04:45 | In February, that was an increase of 62
year on year and in March, where we have -94,
| | 04:51 | that was a decrease of 94 between
March of 2009 as compared to March of 2008.
| | 04:58 | Also notice that the Grand Total cells
for the rows are blank because we only
| | 05:02 | have a single value, but we do
have the subtotal for the year.
| | 05:07 | In this case, FirmA for 2009, the
relative difference to the previous year in
| | 05:13 | this case 2008 was -$2,000. And we see
the same data for FirmB, again, comparing
| | 05:19 | 2009 month-to-month versus 2008.
| | 05:23 | Another way the summarize your data is
to determine the amount that each cell's
| | 05:28 | contents contributes to the
values in a column or in a row.
| | 05:33 | So for this I'll change my Field
Settings using again, Show values as, and I
| | 05:38 | will Show values as the % of a row.
When I click OK, Excel divides the values
| | 05:48 | into two columns, in this case for 2008
and 2009 to indicate what percentage of
| | 05:55 | the total each of those columns contributes.
| | 05:58 | So if we are only considering January
for FirmA and we are looking at total
| | 06:01 | sales over 2008 and 2009, January of
2008 contributed 34.36% and January 2009
| | 06:10 | contributed about twice that much or over 65%.
| | 06:14 | And you can see the same values for all
the other months for 2008 and 2009 for
| | 06:20 | FirmA and you see the same thing for
FirmB, 2008 and 2009. If you want to see
| | 06:26 | the same values broken down by column,
| | 06:29 | so for example, here we have these two
percentages add up to hundred for each row,
| | 06:33 | if you'd rather see how much each
firm's monthly sales contributed to the total
| | 06:37 | for the year, you can do that by
clicking Field Settings, Show values as and
| | 06:42 | changing the Show values as
Comparison to % of column.
| | 06:49 | When you click OK, Excel displays the
contribution for each month, but for the entire column.
| | 06:55 | So for example, of the total 100% of all
the revenue to summarized in this Pivot
| | 07:02 | Table, January 2008 for FirmA
accounted for 3.55% of that total and FirmA
| | 07:09 | contributed 51.03% of all sales for 2008.
| | 07:14 | FirmA contributed 51.03%, FirmB
contributed 48.97%, which adds up to a grand
| | 07:21 | total of 100% and the other
columns add up the same way.
| | 07:25 | You should take some time to experiment
with the Summary Operations and settings
| | 07:28 | available for use in the data area.
| | 07:30 | You will probably find one or more
operations, other than addition, that provide
| | 07:34 | meaningful information about your data.
| | Collapse this transcript |
| Summarizing more than one data field| 00:00 | When most users think of the Pivot
Table, they usually visualize it with a
| | 00:04 | single data set in the data area.
| | 00:06 | While that is certainly the most
common data area configuration,
| | 00:09 | you can summarize more
than one data field at a time.
| | 00:12 | As an example, suppose that I have a
Monthly Revenue Worksheet for two firms,
| | 00:17 | and I already have the
Revenue field in the data area.
| | 00:20 | If I want, I can have a second copy of
the Revenue field to the data area, and
| | 00:24 | then reconfigure the calculations used
in that second area, so that I have it to
| | 00:28 | compare to the first copy.
| | 00:31 | I'll show you what I mean.
| | 00:32 | Let's say that I add a Second Revenue
field to the Values area or the data
| | 00:38 | area of the worksheet.
| | 00:40 | When I add it, I just see
the same data repeated twice.
| | 00:43 | I have Sum of Revenue and
then I have Sum of Revenue2.
| | 00:47 | But now, I can change the summary
operation for the second copy of the Revenue
| | 00:51 | field, and use that to
compare to the original values.
| | 00:55 | So let's say that I wanted to
compare the Revenue from 2009 to 2008, as a
| | 01:00 | percentag change.
| | 01:02 | To do that, I would click any cell in
the Sum of Revenue2 column and then, on
| | 01:06 | the Options tab, click Field Settings,
again, verifying that Sum of Revenue2
| | 01:12 | appears in the Active Field box, click Field
Settings, Show values as, and then select % of.
| | 01:22 | Once I select the comparison operation,
I can select my base item, that is the
| | 01:26 | number to which I am comparing it, and
for this I will make it 2008, which is
| | 01:32 | already selected, and then click OK.
| | 01:34 | Now you're probably not surprised that
the values for 2008, here at the top of
| | 01:41 | the Pivot Table, are all 100%.
| | 01:44 | That's because any value for 2008
compared to itself will be 100%.
| | 01:48 | There is no difference.
| | 01:50 | The difference occurs here in 2009.
| | 01:53 | So for example, for Firm A, January
2009 was 191.04% of the same sales revenue
| | 02:02 | from 2008, again for Firm A in January.
| | 02:05 | In February, the comparison
is an increase of 189.86%.
| | 02:10 | Now in March, the value was only 26.
56% of the value from March of 2008.
| | 02:17 | So in other words, you'll never see a
negative value here. Instead, if the two
| | 02:21 | values were equal, the comparison would be 100%.
| | 02:25 | On the other hand, if the value from
2009 is less than the value from 2008, it
| | 02:30 | would be below 100% in the comparison field.
| | 02:33 | So here we have 26.56%, which tells us
that basically Firm A's sales in March of
| | 02:39 | 2009 were about a quarter of the
same sales from the year before.
| | 02:44 | In this case, I added a second copy of
the Revenue field, so that I would be
| | 02:47 | able to compare years-to-years
while retaining the original data.
| | 02:51 | But if your data source has more
than one column that contains numerical
| | 02:54 | values, you can add both of those columns to
the body of your Pivot Table in the data area.
| | 02:59 | So for example, if you had a column in
the data source that counted the number
| | 03:04 | of sales, you could display it
next to the revenue for each month.
| | 03:07 | Pivot Tables can get a bit crowded when
you display more than one data field in
| | 03:11 | the data area, but if you use the space
wisely, you can gain a lot of insights
| | 03:15 | by viewing two or more data sets simultaneously.
| | Collapse this transcript |
| Creating a calculated field| 00:00 | Pivot Tables enable you to analyze
your data efficiently, but some questions
| | 00:04 | can only be answered by performing calculations
on the data you use to create your Pivot Table.
| | 00:08 | To perform that type of analysis, you
can create custom fields that summarize
| | 00:12 | Pivot Table data using a formula.
| | 00:14 | These custom fields are
called Calculated Fields.
| | 00:18 | Without calculated fields, you would have
to copy the data from your Pivot Table,
| | 00:21 | paste it to another worksheet
and create your formulas there.
| | 00:24 | That's a pain, but I'll show you how
to get around that by working within
| | 00:29 | the Pivot Table itself.
| | 00:30 | Now you do have to dig a little to
find the button you clicked to create a
| | 00:33 | calculated field, but you can find it
by clicking any cell in the Pivot Table,
| | 00:38 | and then on the Options contextual tab,
in the tools group, click the Formulas
| | 00:43 | button, and then click Calculated Field.
| | 00:47 | The Insert Calculated Field dialog box
gives you a list of the available fields,
| | 00:51 | and also the controls that you can use
to create the formula, using the values
| | 00:55 | found in those fields.
| | 00:57 | So in this case, I want to
calculate my average sale.
| | 01:00 | To do that, I will calculate
Revenue, and divide it by Sales Count.
| | 01:05 | That will give me an average revenue per sale.
| | 01:09 | I will call my field, not all that
originally, Average Sale, and now I can
| | 01:16 | create the formula.
| | 01:18 | To create the formula, I get rid of
the zero that's there, but I do have the
| | 01:23 | equal sign, which in Excel is the
indicator that you are creating a formula, and
| | 01:28 | I am dividing Revenue.
| | 01:30 | So I click the Revenue field, and then
click the Insert Field button, so it appears.
| | 01:36 | I am finding an average, so I need
to divide by the number of sales, the
| | 01:39 | division sign is the forward slash, and
I am dividing by Sales Count, so I click
| | 01:45 | the Sales Count field, click
Insert Field, and there I have it.
| | 01:51 | Now you'll notice that Excel placed single
quote marks around the field name, Sales Count.
| | 01:55 | That's because there is this space here.
| | 01:57 | If there were no space, for example, as
there isn't in Revenue, which is just a
| | 02:01 | single word, then Excel would
be able to put Sales Count there.
| | 02:05 | However, because there is a space,
Excel needs to indicate that Sales Count,
| | 02:10 | more specifically Sales, space, Count, is all
one string, so it put single quotes around it.
| | 02:15 | So now I am dividing Revenue by Sales
Count, I can click Add, and the new field
| | 02:22 | appears here in the
Insert Calculated Field list.
| | 02:25 | Once the calculation that I want is
in place, I can click OK, and because I
| | 02:31 | click the Add button, Excel
adds that field to the Pivot Table.
| | 02:35 | So I have here the Sum of Revenue for
Firm A for January 2008, and then I also
| | 02:41 | have the Sum of Average Sale.
| | 02:43 | So what that tells me is the
average revenue for each sale.
| | 02:46 | I have 67 sales and I
have the Sum of the average.
| | 02:51 | So it works out to a
little bit over $7,000 per sale.
| | 02:54 | Now this Pivot Table is pretty crowded.
| | 02:56 | What I'd like to do instead is hide the
Sum of Revenue field, and just have the
| | 03:01 | Sum of Average Sale field.
| | 03:03 | To do that, I can go down to the Values area,
| | 03:07 | in the PivotTable Field List Task pane,
and remove the Sum of Revenue field by
| | 03:13 | dragging it up to the choose
fields to add to Report area.
| | 03:16 | When I do, Excel displays
only the Average Sale Revenue.
| | 03:20 | Calculated fields extend the types of
analysis you can perform in Excel, making
| | 03:24 | Pivot Tables even more useful
for your operational analysis.
| | Collapse this transcript |
| Drilling down to the underlying data| 00:00 | One often overlooked aspect of Pivot
Tables is that they make it easier for you
| | 00:04 | to locate individual data points.
| | 00:06 | As an example, suppose you have a Pivot Table
that summarizes sales over a series of years.
| | 00:11 | If you want to display the data row
that provides the value displayed in the
| | 00:14 | Pivot Table cell, double-clicking that
cell causes Excel to drill down into the
| | 00:18 | data source and create a new worksheet
that contains an Excel table with a copy
| | 00:22 | of the appropriate row or rows.
| | 00:25 | So for example, let's say that I wanted
to see the source row that provided the
| | 00:29 | value for Firm A in March of 2008.
| | 00:32 | To do that, I will double-click the
cell. When I do, Excel creates a new
| | 00:38 | worksheet, and it contains a table with only
the row that provides the value for that cell.
| | 00:44 | Also note that the table that was just
created contains every data column from
| | 00:48 | the original data source, not just
those displayed in the Pivot Table.
| | 00:53 | The bad news is that this DrillDown
capability only works for one cell at a
| | 00:56 | time, but it is possible to drill down
into a cell that is a summary operation.
| | 01:02 | So for example, if you wanted to find
all of the data that provided values for a
| | 01:06 | subtotal, you could do that.
| | 01:08 | But first, if you want to get rid of
this worksheet, you can click the Undo
| | 01:12 | button on the Quick Access toolbar, or
you can also press Ctrl+Z, verify that
| | 01:17 | you do want to delete sheet, click
the Delete button, and it goes away.
| | 01:22 | Now let's see what happens when you
click a cell that contains a subtotal, for
| | 01:26 | example Firm B's subtotal for 2008.
| | 01:29 | If I double-click that cell, Excel
creates an Excel table on the new worksheet
| | 01:36 | and the table contains all of the
rows from the source table that provide
| | 01:39 | input to that subtotal.
| | 01:41 | Again, if I want to get rid of the new
table in the new worksheet, click Undo,
| | 01:47 | verify I want to delete the
worksheet, and it goes away.
| | 01:50 | When you double-click a cell to drill
down, Excel creates the underlying Excel
| | 01:54 | table from its internal
representation of the data source.
| | 01:59 | So in other words, you don't have to
worry about any sort of network lag, as
| | 02:03 | long as you've created the Pivot Table on
your Excel worksheet, you can drill down.
| | 02:07 | Drilling down the original data
source provides context for the data that
| | 02:10 | appears in your Pivot Tables. Rather
than finding the right row among hundreds
| | 02:14 | or even thousands of rows in a source
table, you can display just the one you
| | 02:18 | want by double-clicking the
cell you want to investigate.
| | Collapse this transcript |
| Grouping fields| 00:00 | When you summarize your data using a
Pivot Table, Excel organizes the data based
| | 00:04 | on the order of the fields in
the column area and row area.
| | 00:08 | You can then use the Expand and
Collapse controls at each organizational level
| | 00:11 | to hide the details for those rows.
| | 00:13 | For example, in this Pivot Table, I
have FirmA and FirmB, and I have FirmA
| | 00:19 | broken out by 2008 and 2009.
| | 00:22 | If I want to collapse the values for
2009, and only show the subtotal, I can
| | 00:26 | click the Collapse Control, and Excel
changes the Pivot Table, so it only shows
| | 00:31 | the summary operation,
in this case the subtotal.
| | 00:34 | Clicking the Expand button
returns the data to normal.
| | 00:38 | The months represent the lowest level
of organization in this Pivot Table as
| | 00:42 | it's organized right now.
| | 00:44 | So you can't hide the months individually.
| | 00:46 | You can only hide monthly results by
hiding every month for the entire year.
| | 00:50 | If you do want to show or hide groups
of months, you can do so by creating
| | 00:54 | what's called a Group.
| | 00:55 | A group is a user-defined set of rows
that you can expand or collapse as desired.
| | 01:01 | To create a group, you select the Pivot
Table rows that contain the values you
| | 01:04 | want to include in the group.
| | 01:06 | So let's say, for example, that a company
runs a sale from March to April of each year.
| | 01:11 | To select the rows from March and
April, when the mouse pointer is a right
| | 01:15 | pointing black arrow, I click the left
mouse button to select and drag down, and
| | 01:21 | it selects the rows for March and April.
| | 01:23 | Now notice that it did not only select
the rows for 2008 for FirmA. It also
| | 01:28 | selected FirmA 2009 and FirmB for 2008,
and if we scroll down you can see that
| | 01:36 | it also selected the rows for March and
April 2009 for FirmB. With those rows
| | 01:42 | selected, I can create a group.
| | 01:43 | To do that, I go to the Options
contextual Tab on the ribbon and then in the
| | 01:48 | Group>Group, I click the Group Selection button.
| | 01:52 | It's called Group1, and it
includes March and April.
| | 01:57 | So if I want to hide those values, I
can just click the Collapse button beside
| | 02:01 | Group1, and Excel displays only the
title, Group1, which is the name of the
| | 02:06 | group, and the subtotal for the two
months in that group, which were March and
| | 02:10 | April, which add up to 2006.
| | 02:14 | When I am ready to bring it back, I
can click the Expand Control, and Excel
| | 02:19 | displays the details for March
and April, not just the subtotal.
| | 02:22 | The name Group1 isn't all that
descriptive, but the good news is you can edit it.
| | 02:28 | All you need to do is click the
cell in which the label appears.
| | 02:31 | In this case, it is cell A10,
and then type a new value.
| | 02:34 | In this case, I will call it
'SalesMonths', and when I press Enter, the group
| | 02:42 | takes on the new name.
| | 02:43 | If you've grouped a selection and you
want to get rid of that grouping, all you
| | 02:47 | need to do is click the Header cell.
| | 02:49 | That's the one that contains the name
of the group, and then on the Options tab
| | 02:54 | in the Group>Group, click the Ungroup
button and when you do, Excel removes all
| | 02:59 | of the grouping at the month
level for your Pivot Table.
| | 03:02 | Grouping Pivot Table fields enables
you to hide or display rows of data that
| | 03:06 | belong together without creating
a new field in your data source.
| | 03:09 | Be sure to change the name of the
group to something more descriptive than
| | 03:12 | Group1, and when you're done with your
analysis, consider Ungrouping the fields
| | 03:16 | to make the Pivot Table'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 formulas values
| | 00:04 | from the original data source.
| | 00:05 | That way, if the source changes,
| | 00:07 | your formula's result will update the
next time Excel recalculates your workbook.
| | 00:12 | But what would you do if your Pivot
Table contains data drawn from an outside
| | 00:15 | source and you can't get at the original tables?
| | 00:17 | In that case, you can refer to a
cell within the pivot table using a Get
| | 00:21 | Pivot Data formula.
| | 00:22 | To use data from a pivot table cell in
a formula, you start typing the formula.
| | 00:27 | In this case, I'll make it = sum(, and
I can start typing in the values that I
| | 00:36 | want to use in the formula, or I can
select cells and have Excel put the
| | 00:39 | references into the formula itself.
| | 00:42 | So in this case, I want to add the value
for Firm B from March of 2008, which is 140.
| | 00:50 | When I click the cell, Excel creates a
get pivot data formula, which it places
| | 00:56 | inside of the Sum formula.
| | 00:57 | So in other words, it will be adding
the value it finds using this function, to
| | 01:02 | any other values I put in the formula.
| | 01:04 | I'll just quickly walk you through the
arguments in the Get Pivot Data function,
| | 01:08 | so that you have an idea of how it works.
| | 01:10 | The first argument is the
name of the Pivot Table.
| | 01:13 | In this case, the Pivot Table is named Revenue.
| | 01:16 | The second argument is the cell at
the top left corner of the Pivot Table.
| | 01:20 | In this case, that is cell A3.
| | 01:22 | The remaining pairs of arguments
provide the values that Excel uses to identify
| | 01:28 | the target cell within the Pivot Table.
| | 01:31 | So for example, the first pair of
arguments is Year 2008, and in this case, the
| | 01:38 | cell that selected, C8, is within the year 2008.
| | 01:44 | The second pair is a Month of March.
| | 01:47 | Again, the month is March, and finally
the company is Firm B. We have firm B as
| | 01:54 | the company, and if you draw a line
2008 March to Firm B, we end up at cell C8.
| | 02:02 | Now that I have the Get Pivot Data
function inside of my Sum formula, I can add
| | 02:07 | any value if I want to it.
| | 02:08 | So say for example, I wanted to create
a sales target for the next year, if I
| | 02:12 | want to have another $20,000, instead
of the 140,000 that I have right now.
| | 02:17 | To do that, I would just type a Plus sign, add
20, type a right parenthesis to end the
| | 02:24 | formula, hit Return and I would have my
value of 160, which is this value here,
| | 02:30 | plus 20, as required in my original formula.
| | 02:33 | Now you might be wondering what would
happen if I pivot the Pivot Table?
| | 02:37 | Well, as long as I don't change the
fields that are used in the Pivot Table,
| | 02:42 | nothing will happen, because Excel will
still be able to identify the cell from
| | 02:46 | which it is drawing the value.
| | 02:47 | So if I were to put Company on top of
Year, you'll notice that the result of the
| | 02:53 | formula didn't change.
| | 02:54 | That's because I can still use the
values here Year 2008, which is here,
| | 02:59 | Month of March, which is here, Company Firm B,
which is down at the bottom of the Pivot Table.
| | 03:08 | But then I have the year 2008, March,
and my original value of 140, which when
| | 03:13 | added to 20, as in the original
formula, gives me the correct value.
| | 03:19 | One of the limitations of using Pivot
Table Cells and formulas is that you
| | 03:22 | can't create ranges with them, at least not
while keeping the Get Pivot Data functions.
| | 03:27 | So let's say for example, I wanted to find the
sum of these three cells here, B6 through B8.
| | 03:34 | If I type =sum(and I select B6 through B8,
| | 03:43 | when I select cell B6 Excel creates a
Get Pivot Data function, but when I drag
| | 03:49 | to select B7 it transfers
it to a normal data range.
| | 03:52 | If I continue to 8, it
creates the range B6 to be 8.
| | 03:57 | So if I close the formula with a
right parenthesis and hit Return, I get
| | 04:01 | the correct answer.
| | 04:02 | But what happens if I pivot the Pivot Table?
| | 04:04 | Well, in that case if the cell contents
change, I could either get a different
| | 04:09 | answer, or I could get an error.
| | 04:12 | So let's say that I were to
move Month over to the column area.
| | 04:18 | When I do, the results from cell B6 to
B8 changes from 67, plus 128, plus the
| | 04:26 | subtotal of 219, which gives me the answer
for 414, which doesn't make any sense,
| | 04:31 | because I am adding to detail values to
a subtotal, and it is different from my
| | 04:36 | original answer which was 264.
| | 04:40 | The scenario where you will get an
error is if you remove a field that a Get
| | 04:43 | Pivot Data function uses to identify its data.
| | 04:46 | Say for example, this function here uses
Year, Month & Company to derive its value.
| | 04:55 | If I were to remove the Company field
from the Pivot Table by dragging it here
| | 05:01 | to the Choose fields area, the function
I created here, which relied on company
| | 05:07 | to identify the data it used in its
formula, returns a reference error, because
| | 05:12 | it can no longer find the
data within the Pivot Table.
| | 05:15 | If you find that clicking a Pivot Table
cell doesn't generate a Get Pivot Data
| | 05:18 | formula, you or someone else might have
turned off that option that enables you to do so.
| | 05:23 | To make sure you can use Pivot Table
Data in a formula, you click the Office
| | 05:27 | button, click Cell Options, and then
on the Formulas page of the dialog box,
| | 05:34 | make sure that the Use Get Pivot Data
functions for Pivot Table references box is checked.
| | 05:40 | When you check it click OK,
and you'll be ready to go.
| | 05:44 | The Get Pivot Data function makes it easy to
summarize Pivot Table data in your worksheets.
| | 05:49 | If it's not practical to draw the data
from the original source, you can always
| | 05:52 | use the Pivot Table reference instead.
| | Collapse this transcript |
|
|
3. Sorting and Filtering PivotTable DataSorting PivotTable data| 00:00 | When you display your data in a Pivot
Table you might want to display all the
| | 00:04 | rows in the Pivot Table but change
the order in which they are displayed.
| | 00:08 | In this case, I have a Pivot Table, and
according to the order that I have set
| | 00:12 | using the controls in the Pivot Table
Field List, the rows are sorted by Year
| | 00:17 | 2008, and then 2009, and within each
of those years we have the individual
| | 00:22 | months, January through December in both cases.
| | 00:24 | Change in the Pivot Table organization
changes the way the data is presented.
| | 00:29 | But you can also sort the Pivot Table
by the values in a row or column, other
| | 00:32 | than the one at the first level, to
keep the arrangement but change the order.
| | 00:37 | For example, let's say that I wanted to
find the highest values in 2008 for Firm
| | 00:42 | A. To do that, I will click
any cell in the FirmA column.
| | 00:48 | It is very important.
| | 00:49 | That's the column by which I want to sort,
so I need to click a cell in that column.
| | 00:54 | Now I can go to the Options contextual
tab and use the tools in the Sort group.
| | 01:00 | There are three tools we can use.
| | 01:02 | The first is to sort in ascending order,
where the smallest values are on top,
| | 01:07 | and that is this button here, where it
goes from A to Z. When I click it, Excel
| | 01:13 | sorts all of the values by Year,
| | 01:15 | it doesn't cross subtotals, into ascending order.
| | 01:18 | So we have the FirmA sales of 24, which
at the lowest is followed by 65, 67 and
| | 01:24 | so on, all the way up to
the highest value of 128.
| | 01:27 | Notice that the values for
FirmB are not in ascending order.
| | 01:31 | Instead, it's only for
FirmA within the year 2008.
| | 01:34 | The sorting process starts over for
2009, again, for FirmA. So we have 16,
| | 01:40 | which is the lowest, 24 which is the
next lowest and so on all the way up to the
| | 01:44 | highest value of 131.
| | 01:46 | If you want to sort in descending order,
that is with the highest values on top,
| | 01:52 | you can click the Descending
button here in the Sort group.
| | 01:55 | It goes from Z to A. You have the highest
values on top and the lowest values on the bottom.
| | 02:01 | Again, the sort doesn't cross any
subtotals, so you have the highest value for
| | 02:05 | 2009 here, which is February, and it
goes all the way down to value for August,
| | 02:10 | which is the lowest.
| | 02:11 | If you want to undo a sort, you can
either press Ctrl+Z or click the Undo button
| | 02:17 | on the quick access toolbar.
| | 02:19 | Clicking it once undoes the first sort,
and puts us back into the order with the
| | 02:24 | lowest values on top, that's
a sort in ascending order.
| | 02:27 | Clicking again will undo that sort and
put us back in the original order going
| | 02:31 | by year, and then by month.
| | 02:33 | Sorting a Pivot Table moves the data
you want to highlight the top of the Pivot
| | 02:37 | Table, enabling you to concentrate
on the values you want to focus on.
| | Collapse this transcript |
| Creating a custom sort order| 00:00 | Most of the time when you sort Pivot
Table data, you can use the standard methods
| | 00:04 | to sort the values in
alphabetical or numeric order.
| | 00:07 | There will be times that when you might
want to define a custom list of values
| | 00:10 | and sort a Pivot Table using those values.
| | 00:13 | For example, if your company has
stores in four regions: North, South, East
| | 00:17 | and West, and you want the regions
to show in that order, instead of
| | 00:20 | alphabetical order, you can create a
custom list and use that list to sort
| | 00:24 | your field's values.
| | 00:25 | Here is a Pivot Table and I do have
data arranged by region: East, North, South
| | 00:32 | and West, which is in alphabetical order.
| | 00:34 | If I want to sort by North, South, East
and West, I need to create a custom list.
| | 00:39 | To do that I click the Office button,
click Excel Options, and then in the Excel
| | 00:45 | Options dialog box on the
Popular tab, I click Edit>Custom Lists.
| | 00:50 | Once I am in the Custom List dialog box,
I can create new custom lists, or I can
| | 00:56 | delete any custom lists that I have created.
| | 00:58 | The custom lists, which include days of
the week and months, can't be edited, and
| | 01:03 | you can't delete them, they are just built-in.
| | 01:05 | But you can create a new list and
delete any list that you create.
| | 01:08 | So to create a new list, I click New
List, and then in the List entries pane, I
| | 01:15 | type the entries in the order
that I want to use as my sort order.
| | 01:19 | So in this case, it's
North, South, East and West.
| | 01:29 | Verify it once all the spellings are
correct and the order is correct.
| | 01:32 | Click Add, and the list
appears in the Custom List pane.
| | 01:36 | Now you notice here that I have
the Import list from cells box.
| | 01:41 | If I already had that list typed into a
worksheet somewhere, I could click the
| | 01:45 | Ref Edit Control, and then go out into
the worksheet and copy the cells of the
| | 01:50 | column that contains the values in the
order into which I want to sort my lists.
| | 01:54 | Then I click the Ref Edit Control again, and
in the Custom List dialog box, click Import.
| | 02:00 | I don't need to do that this time.
| | 02:02 | I just type the list in directly.
| | 02:05 | North, South, East and West, and I can
click OK, and click OK again to get rid
| | 02:10 | of the Excel Options dialog box.
| | 02:12 | And now I can sort the values in my
Pivot Table by region using the North,
| | 02:17 | South, East and West list that I just created.
| | 02:20 | So to do that I click any cell
that contains a region value.
| | 02:24 | In this case, that would be East, and
then on the Options tab of the ribbon, I
| | 02:29 | click the Sort button in the Sort group,
and here I can select more options.
| | 02:36 | By default, Excel sorts the report
every time the values are updated.
| | 02:41 | For example, if you refresh the data source,
Excel will resort the data based on the new values.
| | 02:46 | You might not see a change, but Excel does
that just in case there has been a change.
| | 02:51 | In this case, I don't want to auto sort. Instead
I want to set the first key sort order myself.
| | 02:57 | To do that, I clear the Sort
automatically check box, which makes the First key
| | 03:02 | Sort Order box active.
| | 03:04 | I click the list boxes down arrow and I
select the list by which I want to sort.
| | 03:10 | So I click North, South, East and West, click
OK, click OK, and Excel sorts my Pivot Table.
| | 03:19 | Custom list help you arrange your
Pivot Table data to emphasize the elements
| | 03:22 | you feel that are the most important,
making sorting a more powerful and useful
| | 03:26 | tool than ever before.
| | Collapse this transcript |
| Filtering by selection| 00:00 | Pivot tables can summarize huge data
collections, but many times you'll want to
| | 00:04 | limit the data displayed
within a given category.
| | 00:06 | For example, if your company's
operations were divided into four regions, you
| | 00:10 | could display results for
just one of those regions.
| | 00:13 | You limit the data displayed in a
pivot table by creating a filter.
| | 00:16 | There are two main ways you can filter
the contents of a pivot table field, by
| | 00:21 | selection or by rule.
| | 00:23 | Filtering by selection, which we'll
cover in this lesson, means that you display
| | 00:26 | a list of the values in the field and
select the values you want to display.
| | 00:31 | In this case, I'll create a filter that
selects the North and West regions only.
| | 00:36 | To create that filter, I go over to the
Pivot Table Field List, and then in the
| | 00:41 | field list area, because I want to limit
the regions, I point to Region and then
| | 00:47 | click the down arrow.
| | 00:49 | When I do, I get a set of
filtering and sorting controls.
| | 00:52 | The sorting controls are similar to
what I covered earlier in the previous two
| | 00:55 | lessons on sorting and
also Sorting by Custom List.
| | 00:59 | But now if I want to create a filter,
I can use the tools here at the bottom,
| | 01:03 | which allows me to select the
values that I want to hide and display.
| | 01:07 | So in this case, I want to only display
values for North and West, so I'll clear
| | 01:11 | the (Select All) box, which deselects
everything in the list, and now I select
| | 01:16 | North and West, the two
regions in which I'm interested.
| | 01:20 | When I click OK, Excel filters the
pivot table so that now I only see data
| | 01:25 | from North and West.
| | 01:27 | If I want to clear the filter, I can go
over here to the pivot table list, and
| | 01:31 | you see the little funnel icon, indicating
that there is an active filter on that field.
| | 01:36 | Click the down arrow, and
then click Clear Filter.
| | 01:40 | It also indicates the field by
which I filtered the pivot table.
| | 01:42 | In this case, it was Region.
| | 01:44 | So I click that item and
Excel removes the filter.
| | 01:48 | Now let's say that I want to edit
a filter that I've already created.
| | 01:51 | To do that, I'll just create the
filter that I had before, North and West, and
| | 01:57 | you do need to click the box.
| | 01:58 | Clicking the value does not activate
or deactivate an item, click OK, and my
| | 02:05 | filter is reapplied.
| | 02:07 | To edit the filter, I click the down
arrow, and I can either click (Select
| | 02:11 | All) to bring everything back, I can
get rid of individual values such as East
| | 02:16 | and West and just do North and South,
click OK and you see the values for
| | 02:21 | North and South regions.
| | 02:23 | Again, I can clear the filter by clicking here.
| | 02:28 | I can reapply the filter by
clicking the Undo button - this is after I
| | 02:33 | have removed a filter.
| | 02:34 | I can undo that operation
just like anything else.
| | 02:37 | The filter comes back. If I want to get
rid of filter again, I can either click
| | 02:41 | Clear or click Redo.
| | 02:43 | I should also point out that
you can create multiple filters.
| | 02:47 | So let's say that I want to filter
again by Region, and in this case I'll
| | 02:52 | just show North, click OK, I can
create another filter to limit the months
| | 02:57 | that are displayed.
| | 02:59 | So for example, let's say that I only
wanted to show results for January and February.
| | 03:03 | I can click (Select All) which removes
the selection. Click February and January,
| | 03:11 | click OK, and Excel applies my filter.
| | 03:15 | The field headers in the Pivot Table
Field List again have the funnel indicator,
| | 03:19 | which tells you that you have
a filter active on that field.
| | 03:22 | So I can either clear the filters
here individually or I can go to
| | 03:26 | Options>Clear>Clear Filters, which removes
every active filter from your pivot table.
| | 03:33 | Filtering by selection gives you
pinpoint control over the values that appear
| | 03:36 | in your pivot table.
| | 03:38 | You should use this type of filter
when you want to display or exclude a few
| | 03:41 | values from the display.
| | Collapse this transcript |
| Filtering by a rule| 00:00 | Pivot tables help you summarize large
amounts of data, but you can always limit
| | 00:04 | the data that appears by creating a filter.
| | 00:06 | In the previous lesson, I
showed you how to filter by selection.
| | 00:09 | However, if the data you want to
display in your pivot table fits a rule,
| | 00:13 | such all values greater than 1,000,
you can define that rule and use it to
| | 00:17 | filter your pivot table.
| | 00:18 | To filter a pivot table using the
values in the field you can either click
| | 00:22 | the Row Label or Column Labels down arrow
and use the controls to create your filter.
| | 00:27 | I personally prefer to use the controls
in the Pivot Table Field List just because
| | 00:31 | if I want to make some other change,
I'm over here in the Pivot Table Field
| | 00:34 | List, everything is very close, and I
don't need to waste a lot of motion moving
| | 00:38 | my mouse back and forth.
| | 00:40 | There are two types of filters that
you can create when you filter by rule.
| | 00:44 | The first is filtering by value,
and the second is filtering by label.
| | 00:48 | I am going to show you how
to create a value filter.
| | 00:51 | Let's say that I wanted to display only
those rows that contain a value over 200.
| | 00:56 | To do that, I would click Month, and
then Point to Value Filters, and then
| | 01:04 | click Greater Than.
| | 01:05 | That's the type of rule that I want to create.
| | 01:07 | So we will create a Greater Than rule.
| | 01:09 | So we are showing items for Sum of
Revenue where any value is greater than 200.
| | 01:16 | I click OK, Excel creates the filter,
and you see that I have values for March
| | 01:22 | where the total is 268, April where
the value is 204, 217, 257, and 219.
| | 01:30 | So the filter that I created works
appropriately and I only see those rows where
| | 01:34 | the Grand Total is greater than 200.
| | 01:36 | If I want to remove the filter, I can
go over to the Pivot Table Field List,
| | 01:41 | click the Month field headers down
arrow, point to Value Filters, and you
| | 01:45 | notice that there is a checkbox next to Value
Filters indicating there is an active filter.
| | 01:50 | I created a Greater Than filter, but
if I want to clear it, I can go to Clear
| | 01:53 | Filter and Excel removes
the filter from my worksheet.
| | 01:57 | So that's a Value Filter.
| | 01:58 | Now I'd like to show you a Label Filter.
| | 02:00 | For that, I'll use Quarter.
| | 02:02 | So here we have the months for quarter
number 1, quarter number 2 and so on.
| | 02:06 | To create the filter I go over to the
Pivot Table Field List, click the down
| | 02:11 | arrow and then point to the Label Filter.
| | 02:14 | Let's say that I want to filter by
quarter number, and I am only interested
| | 02:17 | in seeing the values for quarters greater
than 2, say for example for quarters 3 and 4.
| | 02:24 | To do that, I go to Greater Than, Show
items for which the label, in this case a
| | 02:31 | quarter label, is greater than 2.
| | 02:34 | Click OK, and Excel only shows me
the results for quarters 3 and 4.
| | 02:40 | To get rid of the Filter I click Undo,
or I can go over to the Pivot Table Field
| | 02:44 | List area, click the Quarter field's
down arrow, and click Clear Filter from
| | 02:51 | Quarter and Excel restores my worksheet.
| | 02:53 | There is one other type of filter by
rule that I would like to show you, and
| | 02:57 | that is the Top 10 filter.
| | 02:59 | To show you the Top 10 filter, I am
going to get rid of the Quarter fields.
| | 03:03 | Now that I've removed the Quarter
field I'm going to create just a
| | 03:06 | single column, which will make
more sense when you see the type of
| | 03:09 | filter that I create.
| | 03:11 | To create the single column, I'll move the
Company field header to the top of year and month.
| | 03:16 | So now I just have a single list of
values, sorted first by Company then by
| | 03:21 | Year and then by Month.
| | 03:22 | Now the type of filter that I want to
show you is called a Top 10 filter, and
| | 03:28 | you can use it to display the top
or bottom values from a data list,
| | 03:32 | in this case, the list of monthly revenues.
| | 03:35 | To create that filter, I would go to
Month>Value, because I'll be using the
| | 03:42 | values from the Revenue field, and
then I go down to the bottom of the filter
| | 03:46 | list, and click Top 10.
| | 03:49 | There are several types of
Top 10 filters you can create.
| | 03:52 | Like I said, you can create a filter for Top or
Bottom, you can find the Top or Bottom values.
| | 03:57 | You can select the number values that
you want to identify: 10, 9 , 8, 100.
| | 04:02 | It doesn't really matter.
| | 04:04 | You can sort by Item or
you can also sort by Percent.
| | 04:07 | So for example, if I select Percent, I
can find the Top 10 percent of values or
| | 04:12 | I could find the top half
finding the Top 50% of values.
| | 04:17 | In this case, I want to display the
five months that had the highest sales.
| | 04:20 | So for that, I will select Top, I will
edit the number in the middle box to
| | 04:26 | 5, and now I will change Percent back to Items.
| | 04:30 | I am filtering by Sum of
Revenue, which is correct.
| | 04:32 | I click OK, and Excel shows the five
highest values, and again it's does
| | 04:38 | not crossed subtotals.
| | 04:40 | So for example, for FirmA for 2008,
the five highest months were March, June,
| | 04:45 | September, October, and December.
| | 04:48 | For 2009, January, February,
April, May, and July, and so on.
| | 04:51 | Again to remove the filter, I can
either click the Undo button or use the
| | 04:56 | techniques that I showed you earlier,
clicking the field header over in the
| | 05:01 | Pivot Table Field List, and clicking
the Clear Filter from Company option.
| | 05:06 | Filtering pivot table data helps you
gain insights into your data by focusing
| | 05:09 | the display on the values you want to examine.
| | 05:13 | Experiment again with the many types of
filters available to you, so you'll know
| | 05:16 | which to use to answer
particular questions about your data.
| | Collapse this transcript |
| Filtering with report filter fields| 00:00 | Whenever you add a field to a pivot
table's column area or a row area, you
| | 00:04 | change the pivot table's
structure by adding a layer of detail.
| | 00:07 | But suppose you have a great pivot table
layout and you want to filter the pivot
| | 00:10 | table using the values and the field
that doesn't appear in the arrangement.
| | 00:14 | For example, you might want to filter
monthly sales data by Quarter but without
| | 00:18 | having the Quarter field
change the pivot table's layout.
| | 00:21 | How do you do that?
| | 00:22 | The answer is that you add the Quarter
field to the Quarter field to the Report
| | 00:25 | Filter field area and
create the filters as normal.
| | 00:28 | So here I have pivot table, and although
I don't have it broken down by Quarter,
| | 00:33 | you see that Quarter is a field
that's available to me that I can use as a
| | 00:37 | filter or to change the
pivot tables organization.
| | 00:40 | If I want to be able to filter the
pivot table using the values of the Quarter
| | 00:43 | field, I can drag the Quarter field to
the Report Filter area, but notice that I
| | 00:49 | have a value in cell A1.
| | 00:52 | So when I drag the Quarter field to the
Report Filter area, Excel realizes that
| | 00:58 | it needs to put the Report Filter
field in cell A1 and B1, so it asks, Do you
| | 01:03 | want replace the contents
of the destination cells?
| | 01:07 | The destination cells, in
this case, refer to A1 and B1.
| | 01:10 | There's only a value in A1, but if
there were a value in either A1 or B1,
| | 01:15 | you'd see this message.
| | 01:16 | I'll click OK, because I don't mind.
| | 01:18 | This is just some stray data that is
the title in this case of the pivot table.
| | 01:24 | So I'll click OK, and Excel adds the
Quarter field to the Report Filter area,
| | 01:31 | and it also appears in cell A1 and B1.
| | 01:34 | If I were to have clicked Cancel there,
Excel would not have put the field in
| | 01:38 | the Report Filter area.
| | 01:39 | Now that I have the Quarter field in the
Report Filter area, I can create filters.
| | 01:44 | In Excel 2007, the Report Filter
interface is a little bit different than the
| | 01:48 | filters that you create here
in the body the pivot table.
| | 01:51 | So let's say for example that I wanted
to create a filter using either FirmA or
| | 01:55 | FirmB, I would get Select
All FirmA, FirmB and so on.
| | 01:59 | I'll click Cancel, because I don't
want to filter there right now.
| | 02:02 | When I click the Report Filter
field's area down arrow, you see that I have
| | 02:07 | (All), 1, 2, 3, and 4.
| | 02:10 | I can either select (All), or I can
select one of the individual items.
| | 02:14 | If I want to select multiple items I
need to check the Select Multiple Items
| | 02:18 | box, in which case I get the familiar interface.
| | 02:21 | I either select 1, 2, 3, or 4, any
combination or toggle the selection using
| | 02:26 | (All), in which case I select all or
clearing that, in which case I remove
| | 02:31 | the entire selection.
| | 02:32 | To create the filter, let's say that I
only want to see the results for Quarter
| | 02:35 | number 1, I select the 1 checkbox
and click OK, and Excel limits my data.
| | 02:42 | I now only see months from Quarter
number 1, but again the number of the quarter
| | 02:47 | appears nowhere in the body of the pivot table.
| | 02:49 | It only appears in the Report Filter area.
| | 02:52 | To clear the filter from the field, you
can either click the filter arrow, click
| | 02:57 | (All), which brings
everything back and click OK.
| | 03:00 | One other aspect of using fields in
the Report Filter area is that you can
| | 03:04 | create individual pivot tables based on
each of the values in that report filter field.
| | 03:09 | Say for example, in this case, where I
have four quarters, I can create four
| | 03:14 | different pivot tables, each one on
its own worksheet, and the filter will be
| | 03:18 | pre-applied for each of the
quarters that are available,
| | 03:21 | in this case 1 through 4.
| | 03:23 | To do that, I select any cell in the
pivot table and then on the Options
| | 03:28 | contextual tab, I go to the PivotTable
group, click the Option's buttons down
| | 03:34 | arrow, and click Show Report Filter Pages.
| | 03:37 | In the Show Report Filter Pages dialog
box, Excel shows me a list of the Report
| | 03:42 | Filter fields that I have available,
and yes you can have more than one.
| | 03:46 | However, when you create individual
pivot tables for each of the values in a
| | 03:50 | Report Filter field, you can only
select one field at a time, otherwise if you
| | 03:54 | had a field that had 20 values and
another that had another 20 values,
| | 03:58 | creating Report Filter Pages for both
of those combined would result in 400
| | 04:03 | different worksheets.
| | 04:05 | Probably never something you'd want to do.
| | 04:07 | So now I have the Quarter
field selected and I can click OK.
| | 04:12 | When I do, the Excel
creates four new worksheets.
| | 04:14 | Each of these new worksheets is
named to reflect the filter value.
| | 04:19 | Say for example, this worksheet is
number 1. That's because the quarter it
| | 04:23 | displays is number 1.
| | 04:25 | Quarter number 2 is worksheet number 2,
number 3, and number 4, and so on.
| | 04:31 | If you have a worksheet with the same
name as one of the field values, Excel
| | 04:35 | names the new sheet 'value' and
then, in parentheses, the number 2.
| | 04:40 | So let's see what happens if I Undo
this change. I'll just delete each of the
| | 04:45 | sheets, and let's say that I create
new worksheet, and I make it's name 2.
| | 04:53 | When I create my Report Filter Pages,
clicking Options>Report Filter Pages,
| | 05:00 | Quarter selected click OK, Excel
creates the new worksheets, but because I
| | 05:05 | already had a worksheet that was number 2.
| | 05:08 | The new worksheet is named 2 followed
in parentheses by 2 and that just means
| | 05:12 | it's the second worksheet with that name.
| | 05:14 | Filtering pivot tables using the
fields and the Report Filter area is a
| | 05:18 | powerful capability.
| | 05:19 | Not only can you limit the data that
appears in your pivot table without
| | 05:22 | changing its structure, you can create
separate worksheets for each value in the
| | 05:26 | report filter field.
| | Collapse this transcript |
| Clearing and reapplying filters| 00:00 | There would be little sense in
applying a Pivot Table filter, if you weren't
| | 00:03 | able to remove it later.
| | 00:05 | In Excel 2007, you can remove filters
from individual fields, remove all filters
| | 00:09 | at the same time and re-apply
filters you just got rid of.
| | 00:13 | In this Pivot Table, I have a filter
applied to two fields: Year, as you see by
| | 00:19 | the Indicator, and Month.
| | 00:21 | If I want to clear a filter, I can
click that field's header over here in the
| | 00:25 | Pivot Table field list, and then
click Clear Filter from "Year".
| | 00:31 | When I do, the filter is removed.
| | 00:33 | If I want to clear all filters from a
Pivot Table, I can click any cell on the
| | 00:37 | Pivot Table, and then on the Options
tab of the Ribbon, in the Actions group,
| | 00:43 | click Clear, and then click Clear Filters.
| | 00:48 | If I want to reverse any of my
actions, such as re-applying a filter I just
| | 00:52 | removed or removing a filter I just
applied, I can use the controls on the
| | 00:56 | Quick Access toolbar or press Ctrl+Z. To
undo my last operation, I click the Undo button.
| | 01:02 | To redo it, I click the Redo button.
| | 01:06 | When you're done with the filter, simply
remove it and continue with the rest of your work.
| | 01:10 | You can always re-create the filter,
or if you haven't made any changes that
| | 01:13 | you don't want to lose, you can use
Ctrl+Z or the Undo button to bring the
| | 01:17 | filter back.
| | Collapse this transcript |
|
|
4. Formatting PivotTable ReportsApplying a style| 00:00 | When you create a Pivot Table, Excel
applies some basic formatting so you can
| | 00:03 | easily distinguish the Labels and
Organizational layers from the data in the
| | 00:07 | body of the Pivot Table.
| | 00:09 | Excel 2007 does come with a substantial
number of built-in styles from which it choose.
| | 00:13 | So if you do want to change your Pivot
Table's formatting, you can do so easily.
| | 00:18 | Unlike in previous versions of Excel,
formatting a Pivot Table's cell by hand
| | 00:22 | applies the formatting to the cell
within the Pivot Table, not to the worksheet
| | 00:25 | cell that currently displays the value.
| | 00:27 | For example, I have the value of
140 and it's currently in cell C8.
| | 00:32 | If I want to highlight that cell, I
can change its Fill Color to yellow, and
| | 00:37 | then when I pivot the Pivot Table,
rather than having the formatting stay in
| | 00:41 | cell C8, it moves with the value to its new
location within the Pivot Table, which is cell E8.
| | 00:47 | If you want to format an entire Pivot
Table at the same time, you can apply a
| | 00:51 | Pivot Table Style from the
gallery of available styles.
| | 00:54 | To do that, you click the Design
contextual tab after clicking any cell
| | 00:59 | within the Pivot Table.
| | 01:01 | Click the More button at the bottom-
right corner of the Pivot Table Styles
| | 01:04 | gallery, and select the style you want to apply.
| | 01:08 | In this case, I will select
Pivot Table Style Light 16.
| | 01:12 | Notice that when I apply the Pivot Table
Style it did not override the manual formatting.
| | 01:18 | If you want to apply a Pivot Table
Style and override the manual formatting,
| | 01:22 | rather than clicking the style you
want to apply, you right-click it and then
| | 01:26 | from the shortcut menu that appears
you click, Apply and Clear Formatting.
| | 01:32 | So when you do, Excel applies the
style, and it removes any manual
| | 01:37 | formatting that you've applied.
| | 01:39 | After you apply a Pivot Table Style, you
can turn individual elements on and off.
| | 01:43 | For example, in this case I
applied Pivot Table Style Light 16.
| | 01:48 | It has an element that I've not
turned on and that is Banding.
| | 01:52 | So I will reset the Pivot Table to its
original configuration and it will just
| | 01:57 | make this a little bit easier to see.
| | 02:00 | This Pivot Table Style allows me to
have Banded rows or Banded columns.
| | 02:05 | Banded rows means that there'll be
alternating colors in odd and even numbered rows.
| | 02:10 | To turn that on, on the Design tab,
after clicking any cell within the Pivot
| | 02:16 | Table, I go to the PivotTable Style Options
group, and select the Banded Rows check box.
| | 02:21 | So you can see now I have rows that
are alternating light and dark colors.
| | 02:27 | If you're working with a large Pivot
Table that contains a lot of data then
| | 02:30 | having Banded Rows or Banded Columns
to make your data much easier to read.
| | 02:34 | One final note, Pivot Table Styles
are part of Office themes, which are
| | 02:39 | collections of predefined color schemes.
| | 02:41 | If you apply built-in Pivot Table Style
and then change the Office theme applied
| | 02:45 | to your workbook, the built-in
Pivot Table Style changes as well.
| | 02:48 | For example, I have the standard
Office theme applied to this workbook, if I
| | 02:54 | were to change that by going to the
Page Layout tab, then in the Themes group
| | 02:59 | clicking the Themes down arrow, and
selecting another theme such as Opulent
| | 03:07 | You'll notice that the colors
applied to the Pivot Table have changed.
| | 03:10 | That's because the style changed
when I change the Office theme.
| | 03:14 | Regardless of the style that you apply,
you can always change the formatting
| | 03:17 | of individual cells.
| | 03:19 | You can also create your
own Custom Pivot Table Styles.
| | 03:21 | I'll show you how to do that in the
lesson title Creating a Pivot Table Style.
| | Collapse this transcript |
| Creating and editing styles| 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
Pivot Table style to reflect your personal
| | 00:09 | aesthetic or your company's graphic art
guidelines, you can do so using the new
| | 00:13 | PivotTable Quick Style dialog box.
| | 00:16 | To display the PivotTable Quick Style
dialog box, you click any Pivot Table cell
| | 00:21 | and then click the Design contextual tab,
click the More button, which is at the
| | 00:26 | bottom right corner of the PivotTable
Styles gallery, and then at the bottom of
| | 00:31 | the gallery, click New PivotTable Style.
| | 00:34 | The first thing you should do is type
a name for your New PivotTable Style.
| | 00:38 | If you're creating a style to reflect
your company's graphic standards, you
| | 00:41 | could call it Graphic Standards 2009.
| | 00:49 | Next, you click the name of the table
element you want to change and click the
| | 00:52 | Format button to display the
formatting tools you can apply to that element.
| | 00:56 | For example, if I wanted to format the
Header Row, I will click it and then click Format.
| | 01:04 | When I do, Excel displays
the Format Cells dialog box.
| | 01:07 | I am creating a header so I want it
to be bold to make it stand out from
| | 01:12 | the body of the Pivot Table, and I also
want to change the color, I'll make it purple.
| | 01:21 | It looks good, I don't need any border.
| | 01:24 | I am not going to have a Fill Color
in this case and I will just click OK.
| | 01:29 | You will notice that after I changed the
format it appeared in the Preview Pane here.
| | 01:34 | Because I changed the Header Row, I now have
purple header text here in the Preview Pane.
| | 01:39 | I personally prefer PivotTable Styles
that use banding that is where the odd and
| | 01:43 | even number rows have separate
formatting, so they are easier to distinguish.
| | 01:48 | In this case, I will change the First
Row Stripe, click Format, and now I can
| | 01:55 | select my formatting.
| | 01:57 | So I won't change the Font style, I
will however change the Font color to blue
| | 02:04 | and I will change the Fill, which is
the background color, to a light orange,
| | 02:12 | click OK, and you see the formatting there.
| | 02:16 | If I want to set this style as the
default for every Pivot Table that I
| | 02:20 | create, I can check this box here, Set as
default PivotTable quick style for this document.
| | 02:25 | In this case I don't, so I will just click OK.
| | 02:28 | When you create a custom style it appears
at the top of the PivotTable Styles Gallery.
| | 02:33 | To display it, click the More button and
you'll see it up here in the Custom area.
| | 02:38 | To apply the style, simply click it.
| | 02:40 | After you create a PivotTable Quick
Style you can edit it at any time.
| | 02:45 | To edit your style, right-click the Style
and then click Modify from the shortcut menu.
| | 02:51 | You can now use the controls in the
Modify PivotTable Quick Style dialog box
| | 02:55 | to edit your format.
| | 02:57 | To do that, you would click the element
that you want to edit, click the Format
| | 03:01 | button and then change the style.
| | 03:03 | Let's say that I thought that the
color that I selected wasn't all that
| | 03:07 | attractive and I'd rather go to a
light gray instead of a light orange.
| | 03:10 | To do that I can click the
gray that I want, click OK.
| | 03:16 | Click OK to save the change and
Excel applies the change of the style.
| | 03:21 | To see the style with the banding
turned on I'll click the Pivot Table and on
| | 03:26 | the Design tab select Banded
Rows and Excel applies the style.
| | 03:31 | You can't edit a built-in PivotTable Style.
| | 03:34 | But if you want to use the built-in
style as the basis for a new style, you can
| | 03:38 | right-click the style, click
Duplicate, and then make your changes.
| | 03:44 | You can then save the style under a
new name and use it as a Custom style.
| | 03:49 | If you want to delete a custom
PivotTable Style, you can right-click the style
| | 03:54 | and click Delete, click OK to verify and
Excel removes the Pivot Table style and
| | 04:00 | applies generic formatting to your Pivot Table.
| | 04:03 | Just so I can demonstrate one more skill,
I will add formatting to the Pivot Table.
| | 04:08 | If you want to remove all formatting
from your Pivot Table, you can click any
| | 04:12 | cell in the Pivot Table, click
the More button and click Clear.
| | 04:18 | Excel 2007 gives you the tools to
control exactly how your Pivot Table
| | 04:21 | appears in your workbook.
| | 04:23 | Creating your own formats enables you to
control how your Pivot Table appears in
| | 04:26 | your company's documents and
presentations, enhancing your corporate identity
| | 04:30 | while making the data
easier for viewers to comprehend.
| | Collapse this transcript |
| Changing the layout| 00:00 | Excel Pivot Tables 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 Pivot Table's
layout, choosing whether to display blank
| | 00:11 | rows below each item and selecting
from three report layouts with subtle but
| | 00:15 | important differences that are
useful for you to know about.
| | 00:18 | By default, Excel 2007 Pivot Tables
have no blank rows after an item ends.
| | 00:23 | In this Pivot Table, there is a subtotal
here for yearly sales for 2008 and 2009.
| | 00:30 | But there is a very little offset
and only a slight formatting change to
| | 00:33 | indicate that it's a subtotal.
| | 00:35 | If you switch to a Pivot Table
style that doesn't have these formatting
| | 00:37 | differences, the transition
will be harder to pick up.
| | 00:40 | To display a blank line after each item,
click any cell in the Pivot Table, then
| | 00:45 | on the Design tab, in the Layout group,
click the Blank Rows button and click
| | 00:51 | Insert Blank Line after Each Item.
| | 00:53 | Now you have a more distinct
transition between the years.
| | 00:56 | So you have 2008, a space 2009,
a space, and then the totals.
| | 01:03 | To take the blanks rows out, on the
Design tab, click Blank Rows and then click
| | 01:09 | Remove Blank Line after Each Item.
| | 01:11 | The three Report Layout options
display the Pivot Table data in
| | 01:14 | slightly different ways.
| | 01:16 | Compact Form, which displays the data
using as few cells as possible, is best for
| | 01:20 | displaying large Pivot Tables, perhaps
as part of your presentation where the
| | 01:23 | projector has a lower
resolution than your monitor.
| | 01:26 | In Compact Form, subtotals appear
at the top of the group by default.
| | 01:31 | In Outline Form, which is the default
layout, Excel moves each row label over
| | 01:36 | one column, so there is a
horizontal gap between them.
| | 01:39 | The first entry under each item, in
this case, Months is part of Years, start
| | 01:43 | one row below the Header.
| | 01:45 | This view makes it easier to pick out
divisions within a Pivot Table by scanning
| | 01:48 | down the appropriate column.
| | 01:50 | Finally, notice that Outline Form
put subtotals at the top of each group.
| | 01:55 | Tabular Form is similar to Outline Form,
but Excel aligns the first detail row
| | 02:02 | with the Header and displays the
subtotals at the bottom of each group.
| | 02:05 | Modifying Pivot Table layouts enables
you to present your data as effectively as
| | 02:09 | possible, both for your personal
viewing and as part of a presentation.
| | 02:13 | You should take the time to experiment
with these options, so you can decide
| | 02:16 | which layout you like the best.
| | Collapse this transcript |
| Changing the data field number format| 00:00 | When you create a Pivot Table, 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
| | 00:09 | other formatting doesn't really matter,
but when you add those values in the
| | 00:12 | subtotal or a grand total cell, the
lack of thousand separators makes the
| | 00:16 | values harder for humans to process.
| | 00:18 | You can make you data easier to read by
changing the data fields Number Format.
| | 00:22 | To change the Number Format of a Pivot
Table data field, click any cell in the
| | 00:26 | field and then click Options>Field Settings.
| | 00:32 | At the bottom left corner of the
Value Field Settings dialog box you can
| | 00:36 | click the Number Format button to display
the Number page of the Format Cells dialog box.
| | 00:41 | And from here you can create any
Number Format that fits your data.
| | 00:45 | In this case, I will create a number
format and use the controls to set the
| | 00:49 | number of decimal places and
whether to use the 1000 Separator.
| | 00:52 | The data in this example doesn't
contain any values to the right of the decimal
| | 00:56 | point, so I'll set the
number of decimal places to 0.
| | 01:00 | The Pivot Table does contain
subtotals and grand totals that go over 1000.
| | 01:04 | So I'll check the Use 1000 Separator checkbox.
| | 01:08 | The 1000 Separator change is based on
the default language of your system.
| | 01:11 | In my case, it's American English and
we use the comma as a 1000 Separator.
| | 01:15 | If you are in Europe, your Separator
might be a Space or it could be a Period.
| | 01:20 | Those are the only changes it'll make
right now, so I can click OK twice to
| | 01:23 | accept my changes, and display the
Pivot Table with the Number Format applied.
| | 01:28 | You should always change the Pivot
Table data fields number format, if you have
| | 01:32 | any values, including subtotals
and grand totals, that go over 1000.
| | 01:36 | In general, it's a bad idea to use the
Currency or Accounting formats though,
| | 01:40 | mainly because the currency symbols
take up space within the cells and can
| | 01:43 | be distracting when you are trying to read
the numbers in the body of the Pivot Table.
| | 01:48 | If you are summarizing currency values,
you should use a Number Format that
| | 01:51 | displays two places to the
right of the decimal point.
| | Collapse this transcript |
|
|
5. Emphasizing Data Using Conditional FormattingHighlighting cells by applying a rule| 00:00 | Pivot Tables 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
| | 00:07 | 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
a cell's contents by applying rules are
| | 00:17 | called Conditional Formats.
| | 00:19 | Excel 2007 is the first version of this
program that lets you apply conditional
| | 00:23 | formatting rules to Pivot Table cells
and have the formats change position when
| | 00:27 | you pivot the Pivot Table.
| | 00:29 | Creating a conditional format
is a lot like creating a filter.
| | 00:32 | In both cases you create rules to
indicate which cells you want to be
| | 00:35 | affected by your action.
| | 00:37 | In this case, I'll create a conditional
format that highlights every cell that
| | 00:40 | contains a value over 120.
| | 00:42 | So I'll just click this on the data
area and then on the Home tab of the ribbon
| | 00:48 | in the Styles group, I click Conditional
Formatting, click Highlight Cells Rules
| | 00:54 | and click Greater Than.
| | 00:56 | In the Greater than dialog box, I
can type-in the number that I want my
| | 00:58 | highlighted numbers to be greater than.
| | 01:00 | In this case that is 120,
and I can set my format.
| | 01:05 | Excel comes with a number of built-in
formats like Light Red Fill with Dark Red
| | 01:08 | Text, and so on, but I don't
think they are very interesting.
| | 01:11 | I never use any of these.
| | 01:13 | But I can go to Custom Format and click
it and get the Format Cells dialog box.
| | 01:19 | Let's say that I'll change these
cells' Fill Color to bright-yellow and
| | 01:25 | I'll change the Font so that they are bold,
those are the only changes I want to make.
| | 01:30 | So I'll click OK, I see my Greater
Than rule, greater than 120 with a Custom
| | 01:36 | Format, I can click OK, and Excel
applies the value to the Pivot Table, except it
| | 01:41 | doesn't look like it did.
| | 01:42 | The value in cell C7, 50, is below 120, so
Excel did not apply the style to this cell.
| | 01:49 | It doesn't apply.
| | 01:51 | It also didn't apply the rule to any
of the other cells that do meet the
| | 01:54 | criteria, for example 130, 140, and 128.
| | 01:57 | To apply the rule to other cells you
need to click the Formatting Options button.
| | 02:02 | When you click that button you're
presented with a variety of options on how to
| | 02:06 | apply your conditional format.
| | 02:08 | You can apply it only to the selected
cells, which it tried to do in this case.
| | 02:12 | And it doesn't apply to the entire
Pivot Table on that case, you can click All
| | 02:16 | cells showing "Sum of Revenue" values,
which means that Excel will try to apply
| | 02:20 | the conditional formatting rule to
every cell within the data area, including
| | 02:24 | grand totals and subtotals.
| | 02:27 | It's almost never a good idea to
apply conditional formatting rule both to
| | 02:31 | subtotal grand total cells
plus the cells in the data area.
| | 02:34 | That's because subtotals and grand
totals usually find the sum of the values
| | 02:39 | within the main data area so they will
almost always be larger than the criteria
| | 02:44 | and will provide visual clutter
instead of useful information.
| | 02:47 | Usually the best option to
select is the bottom one:
| | 02:50 | All cells showing "Sum of Revenue"
values for "Month" and "Company".
| | 02:54 | Now these listings will change based
on the names of the fields in your Pivot
| | 02:57 | Table, but in this case what I'm
looking for are All cells showing "Sum of
| | 03:01 | Revenue" in the main body of the Pivot Table.
| | 03:04 | I have FirmA and FirmB, and also the Month and
those are the cells that I want to highlight.
| | 03:09 | So I will select the bottom option, and
Excel applies the conditional formatting
| | 03:14 | rule to the body of the Pivot Table and
you can see, indeed that every cell with
| | 03:18 | a value over 120, not including 120,
but greater than 120, is highlighted with a
| | 03:24 | yellow fill and the number appears in bold.
| | 03:27 | Applying a simple rule-based
conditional format can make it much easier to
| | 03:30 | interpret your Pivot Table data.
| | 03:32 | All you need to do is determine the
criteria that reflects the data you want
| | 03:35 | to highlight.
| | Collapse this transcript |
| Highlighting the top or bottom value in a report| 00:00 | Companies of all kinds are interested
in identifying their best salespeople,
| | 00:03 | their best 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 Pivot Table.
| | 00:13 | You can create two types of top 10
conditional formats in Excel 2007.
| | 00:17 | Formats that identify a certain number
of top or bottom values or formats that
| | 00:21 | identifies the top or bottom
values based on a percentage.
| | 00:24 | For example, if you wanted to identify
the top 10% of your customers by sales,
| | 00:29 | regardless of the total number
of customers, you could create a
| | 00:32 | percentage-based format.
| | 00:33 | So let's see how that works.
| | 00:35 | The first conditional format I'll
create will identify the top seven months
| | 00:39 | listed in this Pivot Table.
| | 00:41 | To do that on the Home tab of the
ribbon, in the Styles group, I click
| | 00:45 | Conditional Formatting, point to Top/
Bottom Rules and then click Top 10 Items.
| | 00:52 | When I do, Excel displays
the Top 10 Items dialog box.
| | 00:56 | I can now select the number of
items that I want to identify.
| | 00:59 | In this case, it will be 7, and
I can create my format as well.
| | 01:04 | I will click the down-arrow and
Custom Format to create my own
| | 01:08 | conditional format.
| | 01:10 | In this case, I want to change the Fill Color
to bright-yellow and change the Font to bold.
| | 01:16 | Those are the only changes I want to
make so I can click OK, and verify that I
| | 01:21 | have the top 7 with a
custom format, and click OK.
| | 01:26 | When the dialog box goes away
you'll see that Excel has applied the
| | 01:29 | conditional formatting rule to what is
a relatively low value. That's because
| | 01:33 | Excel only applied the value to this
cell. It didn't apply to every other
| | 01:38 | cell in the data area.
| | 01:39 | So I am looking for the top seven
values within the data area, not including
| | 01:44 | grand totals or subtotals, to change the scope
with which the conditional format is applied.
| | 01:50 | I click the Formatting Options button
and then select All cells showing "Sum of
| | 01:55 | Revenue" values for "Month" and "Company".
| | 01:58 | What that means is that it will only
apply the conditional format to cells in
| | 02:01 | the detail part of the data
area as opposed to the summary.
| | 02:05 | If I were to select All cells showing
"Sum of Revenue" values that would include
| | 02:08 | grand totals and subtotals, which I don't want.
| | 02:11 | So I'll select that bottom option and
Excel identifies the top seven items
| | 02:16 | in the Pivot Table.
| | 02:17 | Now let's say that I wanted to
create a separate conditional format that
| | 02:21 | identifies the bottom 33% of
the values in the Pivot Table,
| | 02:25 | in other words, the bottom third.
| | 02:27 | To do that, click any cell on the Pivot Table.
| | 02:30 | On Conditional Formatting, point to Top/
Bottom Rules, and then click Bottom 10%.
| | 02:37 | I will edit the percentage
so that I change it to 33.
| | 02:44 | Change the format so it has red text.
I won't bother with a custom format
| | 02:49 | this time, and click OK.
| | 02:52 | Once again, Excel has applied the
conditional format to the selected cell, but I
| | 02:56 | can change the scope so that it is
applied to All cells showing "Sum of Revenue"
| | 03:01 | values for "Month" and "Company", in
other words the detail level, as opposed to
| | 03:04 | subtotal and grand total.
| | 03:07 | And Excel identifies the bottom third or 33%
of the values in the body of the Pivot Table.
| | 03:13 | Conditional formats that identify the
top or bottom values in the Pivot Table
| | 03:17 | field, make it easier for you to
visualize the most and least effective
| | 03:20 | performers in your organization.
| | 03:22 | Remember that you can identify a
specific number of top or bottom values or
| | 03:26 | create a rule that identifies the top or
bottom percentage of the group such as the top 15%.
| | 03:31 | This flexibility enables you to
create exactly the rule you need.
| | Collapse this transcript |
| Formatting cells using data bars| 00:00 | When you summarize numerical data using
a Pivot Table, Excel displays the values
| | 00:04 | with either no formatting, which can
make the numbers difficult to interpret, or
| | 00:07 | using a number format.
| | 00:09 | In both cases, to get an idea of how
two values compare, you must visually
| | 00:12 | estimate the number of digits used to
express the values and if they are the
| | 00:16 | same length, focus on the individual digits.
| | 00:19 | Comparing two numbers isn't difficult, but
comparing a few dozen or more can be confusing.
| | 00:24 | In Excel 2007, Microsoft introduced a
conditional format called The DataBar,
| | 00:29 | which adds color bars to a cell's background.
| | 00:31 | The length of the bar reflects the
relative magnitude of the value in the cell.
| | 00:35 | To create a DataBar you click any
cell in the data area of the Pivot Table,
| | 00:40 | and then in the Styles group of the Ribbon,
click Conditional Formatting and point to DataBars.
| | 00:47 | You can create a data bar using one of
the six built-in colors, or you can click
| | 00:51 | More Rules to create a custom color.
| | 00:54 | When you click More Rules you're
presented with a new formatting rule dialog
| | 00:57 | box, and it sets so that
you will create a DataBar.
| | 01:01 | You can now use the Bar color boxes down
arrow, which appears here at the bottom
| | 01:04 | of the New Formatting rule dialog box
to select the color for your data bar.
| | 01:09 | In this case, I will select Medium Orange.
| | 01:12 | The preview the DataBar and its
color appears in the Preview box.
| | 01:16 | If you'd like to display the bars
without the cells data, you can do so by
| | 01:20 | checking the Show Bar Only box.
| | 01:22 | I personally don't use that option,
because I do want to have the actual data
| | 01:25 | available for viewing it all times,
but you can use it if you want.
| | 01:28 | Now I'll finish creating this rule, and
I will select the Option here at the top
| | 01:34 | to apply the rule to all cell showing
some of the revenue values for month and
| | 01:38 | company, in other words, the detail
area of the data, and click OK, and Excel
| | 01:45 | applies the Conditional Format.
| | 01:47 | One downside to using DataBars is that
because they fill a percentage of the
| | 01:50 | cell's interior, and don't have a fixed
maximum length, two cells with the same
| | 01:54 | value can have DataBars of different lengths.
| | 01:57 | In this Pivot Table, which is laid out
in compact form, similar values in cells
| | 02:01 | B and C have differently sized DataBars.
| | 02:03 | When I apply DataBars to a Pivot Table, I
choose outline form as my report layout.
| | 02:09 | To do that, I click a cell on the Pivot
Table, and then, on the Design Contextual
| | 02:14 | tab of the Ribbon, I go to Report
Layout, and click outline form, so the
| | 02:20 | DataBars provide a meaningful comparison.
| | 02:23 | DataBars provide a quick visual
summary of the relative magnitude of values
| | 02:26 | in your Pivot Table.
| | 02:28 | When you format your Pivot Table, so
the columns are all the same width, the
| | 02:31 | bars provide information
that aids your analysis.
| | Collapse this transcript |
| Formatting cells using color scales| 00:00 | One of the more recent developments
in Data Presentation is the concept of
| | 00:03 | the Heat map, which uses a cell's
value to determine which fill color to
| | 00:07 | assign to the cell.
| | 00:09 | Unlike role-based formats that
either apply a color or not, a 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.
| | 00:20 | So I'll start with an example, and then
show you how to create the format yourself.
| | 00:24 | I apply the yellow to red conditional
formats to this Pivot Table's data field,
| | 00:28 | the lower the value, the more yellow
the cells interior color contains, the
| | 00:32 | higher the value the more red, the
middle values, which in this example are
| | 00:36 | around 50%, are filled with a mix
of yellow and red making orange.
| | 00:40 | So that's the starting point.
| | 00:41 | I will clear the conditional format from
the cells and show you how to make your own.
| | 00:49 | To create a Color Scale using a built-
in format you click any cell in the Pivot
| | 00:53 | Table, and then on the Home tab of
the Ribbon, in the Styles Group, click
| | 00:58 | Conditional Formatting and Point Color Scales.
| | 01:02 | If I wanted to create the Color Scale
that I had before, which went from yellow
| | 01:06 | at the bottom to red at the top, I would
point here, click Red-Yellow, and then
| | 01:11 | I can select the Option that applies
the conditional format to all of the cells
| | 01:15 | that shows some of usage for
workstation and day, which are the detail level
| | 01:19 | cells within the Pivot Table.
| | 01:21 | And Excel creates a conditional format
with a smallest value, such as two, contain
| | 01:26 | a lot of yellow, and the
largest values contain a lot of red.
| | 01:30 | You can choose from two color scales and
three color scales. Yes, the yellow red
| | 01:34 | scale a showed you has orange at the
middle, but the three color scales have a
| | 01:37 | middle color that affects the format.
| | 01:40 | As it happens, all the built in three
Color Scales have yellow as the middle color.
| | 01:44 | For example, if I wanted to change
this conditional format to red, yellow,
| | 01:48 | green, I can click Conditional Formatting,
go to Color Scales, and then point
| | 01:53 | to red-yellow-green.
| | 01:55 | When you apply the red-yellow-green
format you see that the highest values are
| | 02:01 | red, the middle values are yellow,
and the lowest values are green.
| | 02:06 | The values between the middle and the
low are yellowish green, and the values
| | 02:10 | between the middle and the high, yellow
and red, are orange, and I will now get
| | 02:16 | rid of this Conditional Format.
| | 02:20 | If you want to create your own color
scale, you can click any cell in the
| | 02:23 | Pivot Table, then, on the Home tab,
click Conditional Formatting>Color Scales,
| | 02:30 | and click More Rules.
| | 02:31 | Now you can use the Controls in the new
formatting rule dialog box to create
| | 02:35 | your custom Color Scale.
| | 02:37 | First thing we need to do is set the
scope for the Conditional Format. We want it
| | 02:41 | in the data area, but not
including subtotals and grand totals.
| | 02:45 | So select all cells showing some of
usage for Workstation and Day, and now I can
| | 02:51 | go to the bottom, I'll stay with a
two color scale, and select for colors.
| | 02:56 | So for the minimum color, I will make
it a very light gray, and for the highest
| | 03:02 | color, I will make it a medium orange.
| | 03:06 | When I click OK, Excel applies the format.
| | 03:09 | You go through exactly the same
procedure when creating a three color scale, but
| | 03:13 | there you obviously have to
pick the middle color as well.
| | 03:15 | Yellow makes a great choice.
| | 03:17 | It mixes with other colors very nicely.
| | 03:19 | Color Scales provide terrific visual
feedback for resource utilization maps.
| | 03:23 | If you run to the grocery store and want
to track which shelf positions get the
| | 03:26 | most traffic, or if you run an
Internet cafe and track workstation usage, a
| | 03:30 | Color Scale Conditional Format will
indicate which resources are the most and
| | 03:34 | the least popular.
| | Collapse this transcript |
| Formatting cells using icon sets| 00:00 | One of the most popular trends in
corporate management is to use icons to
| | 00:03 | indicate how a company's
performance compares to its goals.
| | 00:07 | If these set up your workbook as a
dashboard that summarizes performance, you
| | 00:11 | can use indicators such as the familiar
red, yellow, and green stoplight images
| | 00:14 | to indicate unacceptable,
acceptable, and excellent results.
| | 00:18 | In Excel 2007, a group of
indicators is called an Icon Set.
| | 00:23 | To apply an icon set conditional format
to a Pivot Table, you click any cell in
| | 00:26 | the Pivot Table data field and then, on
the Home tab of the ribbon, in the Styles
| | 00:31 | group, click the Conditional Formatting
button, and point to Icon Sets and click
| | 00:37 | the icon set you want to apply.
| | 00:40 | In this case, I will use the three
Traffic Lights with the rims.
| | 00:44 | Click it and Excel applies the
conditional format to the selected cell.
| | 00:47 | Now I can select the conditional format scope.
| | 00:50 | If I click the Format Options button,
I have three options to select from.
| | 00:53 | The first is Selected cells, which
applies the rule to any cells that I had
| | 00:57 | selected at the time I created the rule.
| | 01:00 | Also you have All cells showing "Sum
of Revenue" values, which includes every
| | 01:04 | data cell within the Pivot Table
including grand totals and subtotals.
| | 01:08 | And finally, you have All cells showing "Sum
of Revenue" values for "Month" and "Company".
| | 01:13 | That option refers to the data within
the body of the Pivot Table as opposed to
| | 01:16 | the grand totals and subtotals, and that is
the option you almost always want to select.
| | 01:21 | So I'll click it, and Excel applies
in Icon Set formatting rule to the body
| | 01:26 | of the Pivot Table.
| | 01:28 | So here's a quick review of what's going on.
| | 01:30 | When I created this formatting
rule I used Excel's default setting.
| | 01:34 | For an icon set that contains three
icons, that means that Excel formatted the
| | 01:38 | top third of the values in the Pivot
Table with green, the middle third with
| | 01:42 | yellow, and the bottom third with red.
| | 01:44 | But of course, you can control
that if you want to create a custom
| | 01:47 | conditional format.
| | 01:48 | I'll show you how to do that now,
but first I will click Undo to remove
| | 01:53 | that Conditional Format.
| | 01:54 | And now to customize my Icon Set
Conditional Format, once again on
| | 01:59 | Home>Conditional Formatting, go
to Icon Sets and click More Rules.
| | 02:06 | Now I can use the controls in the New
Formatting Rule dialog box to create my
| | 02:09 | Icon Set Conditional Format.
| | 02:11 | First, I will select the scope, and
once again it is All cells showing "Sum of
| | 02:16 | Revenue" values for "Month" and "
Company". I'll select that option.
| | 02:20 | Now I can go down to the Edit the Rule
Description area and customize my format.
| | 02:24 | My Format Style is correct. I do
want an Icon Set, and the Icon Set that
| | 02:29 | I'll actually be using is here
at the bottom, for me to select.
| | 02:33 | I want to use again the 3 Traffic
Lights that are rimmed, so I will select that
| | 02:38 | and the preview appears here in the Icon list.
| | 02:41 | Now it's time to create the rules.
| | 02:43 | You can create four different types
of conditional formatting rules when
| | 02:46 | you create an icon set.
| | 02:48 | The first type of rule that you
can create is called a Percent Rule.
| | 02:52 | When I created the default Icon Set
that highlighted the top third of values in
| | 02:56 | green, the middle in
yellow, and the bottom in red,
| | 03:00 | Tthat's what I created, a Percent Rule.
And you can see those values here.
| | 03:04 | So any value that is greater than
or equal to 67% of the value range is
| | 03:09 | formatted in green, between 67% and
33% is yellow, and below 33% is red.
| | 03:16 | So again, that's the rule we created before.
| | 03:18 | If I wanted to change those percentages
and say I have green to be the top 80%
| | 03:23 | of the values, and yellow to be between
80% and 50%, and of course, red applied
| | 03:31 | to anything below 50%, I could do so.
| | 03:34 | If I click OK, Excel applies that rule.
| | 03:37 | You note that we have a little bit less
green and a lot more red than we had before.
| | 03:42 | I'll remove that rule and show
you how to create another one.
| | 03:45 | So Conditional Formatting>Icon Sets>More Rules.
| | 03:50 | Again, I select the scope so that it
applies to all of the cells in the body of
| | 03:56 | the data area, but without
including the subtotals and grand totals.
| | 04:00 | I'll select my Icon Set and now I
can create what's called a Number Rule.
| | 04:07 | A Number Rule, instead of using a
percentile, uses absolute values to determine
| | 04:10 | which icon to apply.
| | 04:12 | So the first thing you want to do is
change the Type to Number, and Excel
| | 04:17 | modifies the values over here.
| | 04:19 | Let's say that I wanted to display a
green icon in any cell that contains a
| | 04:22 | value of 120 or higher.
| | 04:24 | To do that, I'll just edit this value
so it reads 120, and press Tab, and now
| | 04:31 | any cell that contains a value of
120 or more will get a green icon.
| | 04:35 | And let's say for the second level,
for the yellow, I want any value that is
| | 04:43 | less than 120 but is
greater than or equal to 75.
| | 04:46 | I forgot to change the number.
| | 04:48 | Well, I'll show you what happens because
this is actually an interesting mistake to make.
| | 04:53 | If I click Number, you'll notice
that Excel changes this value back to 0.
| | 04:58 | So that's why you always want to select
Type first because when you change the
| | 05:02 | Type, Excel changes the
value here to its default value.
| | 05:06 | So I have selected Number, I don't
need to worry about it changing again.
| | 05:10 | I wanted to have any value that was
greater than or equal to 75 but less than 120.
| | 05:14 | So I type in 75, press Tab and my rules
are greater than or equal to 120, less
| | 05:21 | than 120, but greater than or equal to
75 and less than 75, when I click OK,
| | 05:28 | Excel applies the rule.
| | 05:29 | I will remove this rule just by
clicking Undo and I'll create the next kind of
| | 05:34 | rule, which is a Formula Rule.
| | 05:37 | A Formula Rule, as the name implies,
uses a formula to determine which icon
| | 05:42 | to apply to a cell.
| | 05:43 | So I'll click any cell on the Pivot
Table, on the Home tab of the ribbon, click
| | 05:48 | Conditional Formatting, click
Icon Sets and then click More Rules.
| | 05:55 | In the New Formatting Rule dialog box, I
can once again select my rule scope and
| | 05:59 | that will be All cell showing "Sum of
Revenue" values for "Month" and "Company".
| | 06:03 | I'll select my Icon Set, which is the
3 Traffic Lights (Rimmed), and now I
| | 06:08 | can create my rules.
| | 06:09 | I want to create a Formula Rule.
| | 06:11 | So I'll set the Type for
the first rule to Formula.
| | 06:14 | I want to apply a green icon to any cell
that contains a value that is more than
| | 06:19 | one-and-a-half times the value of my
target, and my target is in cell G1, hence
| | 06:24 | a target value of 75.
| | 06:26 | So to create that formula, I go to the
Value box and then I type =$g$1, and the
| | 06:37 | reason I'm using dollar signs is that
you cannot use what's called a relative
| | 06:41 | reference when you create a
formula for a conditional format.
| | 06:45 | So if you put dollar signs before the
column letter and the row number, it makes
| | 06:52 | them invariant so they will never change.
| | 06:53 | For example, if you were to copy that
cell reference from one cell to another.
| | 06:58 | So I have the cell G1 as an
absolute reference, multiplied by 1.5.
| | 07:04 | So the rule is now that any cell that
contains a value that is 1.5 times the
| | 07:09 | value in cell G1, regardless of what
that value is, it will get a green icon.
| | 07:14 | Now I can create a similar rule for yellow.
| | 07:18 | I'll change it to Formula, =$g$1.
| | 07:23 | This rule means that any cell that
contains a value that is less than
| | 07:26 | one-and-a-half times the value in G1
but is greater than or equal to the value
| | 07:31 | in G1, so in this case any value
between 132 and 75, will get a yellow icon.
| | 07:38 | I'll press Tab to accept the rule,
and I see that red has been updated so
| | 07:44 | that any value less than this formula value,
which is currently 75, will get a red icon.
| | 07:50 | I click OK and Excel applies
the rule to the Pivot Table.
| | 07:54 | But now let's see what happens if I
change my Target value and this is the
| | 07:57 | benefit of creating a
formula-based conditional format.
| | 08:01 | If I change my Target value to 90 so
that any cell that contains a value of 135
| | 08:07 | or higher gets green, and any
value between 135 and 90 gets yellow.
| | 08:12 | I'll press Enter and Excel
updates the conditional formats.
| | 08:17 | You'll see that there is only one cell
in the entire Pivot Table that has a value
| | 08:21 | that's greater than 135, so it is
the only one that gets a green icon.
| | 08:27 | I will undo my changes to remove the
conditional format and I will show you how
| | 08:32 | to create the final type of Icon
Set Conditional Format and that is a
| | 08:35 | Percentile Conditional Format.
| | 08:37 | So to create a Percentile Rule on the
Home tab of the ribbon click Conditional
| | 08:42 | Formatting, point to Icon
Sets and click More Rules.
| | 08:47 | Once again, I select my scope.
| | 08:49 | All cells showing "Sum of
Revenue" for "Month" and "Company".
| | 08:51 | Again, the detail cells within the Pivot
Table, I will select a rimmed 3 Traffic
| | 08:57 | Light Icon Set and now I can create my rules.
| | 09:00 | I am creating Percentile rules so I
click the Type and select Percent, and I
| | 09:07 | want any value in the top 10%,
which means the 90th percentile.
| | 09:13 | So I set the value in the first
box to 90, and the second rule, half
| | 09:19 | Percentile, and I want yellow to be
applied to any value that's in the 50th to
| | 09:24 | the 90th percentile.
| | 09:26 | So I'll type in 50, press Tab, my rule
has been updated, and now I can click OK.
| | 09:34 | When I do, Excel displays a green
icon in any cell that contains a value in
| | 09:38 | the top 10 percentile.
| | 09:39 | In this case, it's anything
over 130 -- oh, we have 129.
| | 09:45 | So any value over 129 would
be in the top 10 percentile.
| | 09:48 | Anything in the middle 40 percentile,
in other words from 50 to 90, has yellow,
| | 09:53 | those are these middle values here,
and the bottom half of the values in the
| | 09:56 | 50th percentile and below all contain red.
| | 09:59 | Now finally, there's one other feature
that I think you'll like, and that is if
| | 10:02 | you want to reverse the icons.
| | 10:04 | Say for example, instead of these cells
containing revenue, let's say that they
| | 10:08 | contained expenditures, and since
everybody is interested in keeping costs down,
| | 10:12 | lower values would be better than higher values.
| | 10:15 | So to create a rule where you have
lower values getting a better icon, in this
| | 10:20 | case a green icon, you can reverse the
order in which the icons are applied.
| | 10:24 | I'll show you how to do that.
| | 10:26 | First I'll remove this rule by
clicking Undo, then on the Home tab, click
| | 10:30 | Conditional Formatting>Icon Sets>More Rules.
| | 10:35 | Once again, set my scope, select my Icon
Set, and now, I'll show you here at the
| | 10:42 | bottom that there is a
Reverse Icon Order checkbox.
| | 10:45 | If I want to reverse the order in which
the icons are applied, I just check it,
| | 10:51 | and you'll see that now red is on top
and green is on the bottom, and I'll just
| | 10:55 | create Percentile values.
| | 10:56 | So let's say that I want the top 20%,
so click 80, and then set a rule for 50.
| | 11:06 | So any value in the top 80% will get red,
any value between 80% and 50% will get
| | 11:11 | yellow, and anything below 50% will get green.
| | 11:14 | When I click OK, Excel applies rules to
the Pivot Table and you'll note that the
| | 11:18 | lower values, in this case, values such
as 32 and 42 get green icons and values
| | 11:23 | such as 128 or 131 get red.
| | 11:27 | Icon Sets can provide useful
information about large data sets but they really
| | 11:31 | come into their own when you use them to
summarize a relatively small amount of data.
| | 11:35 | If you or your Executive Team Monitor,
company performance using dashboards,
| | 11:38 | you should strongly consider using
Icon Sets to enhance your ability to
| | 11:41 | understand the dashboard summary at a
glance and determine where to reward
| | 11:45 | 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 a new operating
conditions or goals of your company.
| | 00:06 | For example, you might revise your
sales targets to reflect an economic
| | 00:09 | downturn, or you could decide to pay
a bonus to the top 15% of your sales
| | 00:13 | representatives, instead of your
top 10%, because you had a great year.
| | 00:17 | To edit a conditional format, click any
cell that contains the format, and then
| | 00:21 | on the Home Tab of the Ribbon, in the
Styles Group, click Conditional Formatting
| | 00:28 | and then click Manage Rules.
| | 00:30 | When you click Manage Rules, the
Conditional Formatting Rules Manager applies.
| | 00:34 | All you need to do is click the rule
you want to edit, and click the Edit Rule
| | 00:38 | button to display the Edit
Formatting Rule dialog box.
| | 00:43 | You can use the controls in the Edit
Formatting Rule dialog box to change every
| | 00:46 | aspect of your Conditional Formatting
Rule, including the type of rule, the
| | 00:50 | cells to which it is applied,
and the rule's conditions.
| | 00:52 | In this case, I have an icon set that I
am using to summarize the information in
| | 00:57 | my Pivot Table, but let's say that I
wanted to change it to a 2-Color Scale.
| | 01:01 | To do that, I go to the Edit the Rule
Description section, and select a new format style.
| | 01:06 | In this case I will select a 2-Color Scale.
| | 01:09 | The lowest values are in orange, the
highest values are in yellow. That's fine.
| | 01:13 | Although, of course, I can change
those colors if I like, but in this case I
| | 01:16 | won't, and I can change the rule by clicking OK.
| | 01:21 | The rule appears with the preview
here in the Conditional Formatting Rules
| | 01:25 | Manager, click OK, and Excel applies
the new conditional formatting rule to
| | 01:31 | the selected cells.
| | 01:32 | You're not stuck with
conditional format after you create it.
| | 01:35 | You can change any aspect of the rule you like.
| | 01:37 | So feel-free to experiment with the
rule that provides you with the information
| | 01:40 | you and your audience require.
| | Collapse this transcript |
| Controlling how Excel applies multiple conditional formatting rules| 00:00 | Excel 2007 introduced many new types
of conditional formats, but it also
| | 00:04 | presented users with many more
options on how to manage those rules.
| | 00:09 | For example, in Excel 2003 and
earlier, each cell could have up to three
| | 00:12 | conditional formatting rules applied to it.
| | 00:14 | What's more, only one of those
rules could be applied at a time.
| | 00:18 | When Excel discovered that one
rule was true, it stopped checking.
| | 00:21 | In Excel 2007, there is no
practical limit to the number of conditional
| | 00:24 | formatting rules you can apply to a cell.
| | 00:26 | It's also possible for more than one
conditional formatting rule to be applied
| | 00:30 | to the same cell at the same time.
| | 00:33 | If you want to control how Excel
applies multiple conditional formatting rules
| | 00:36 | to the same cell or the same group of
cells, you can do so by displaying the
| | 00:40 | Manage Rules dialog box.
| | 00:42 | To do that, you click any cell in the
Pivot Table, and then on the Home Tab
| | 00:47 | click Conditional
Formatting, and click Manage Rules.
| | 00:52 | When you do, the Conditional
Formatting Rules Manager dialog box appears.
| | 00:56 | The first step you should take is to
ensure that you were working with the rules
| | 00:59 | applied to the Pivot Table.
| | 01:01 | To do that, you make sure that this
Pivot Table appears in the Show Formatting
| | 01:05 | Rules for box, if it doesn't, you can
click the Down Arrow, and select the
| | 01:09 | Pivot Table from the list of available
objects, but it's already there, so we
| | 01:14 | don't have to do that.
| | 01:15 | When you have multiple conditional
formats applied to a Pivot Table, you can
| | 01:18 | change the order in which Excel
attempts to apply the formats.
| | 01:22 | The program starts at the top of the
list of formats found in the Conditional
| | 01:24 | Formatting Rules Manager and works its way down,
| | 01:27 | stopping only if a stop if true
condition is met, or when it runs out of rules.
| | 01:32 | If you want to change the order in
which rules are applied, you can click the
| | 01:36 | Rule so that it's
highlighted, in this case in blue.
| | 01:39 | And then you can change its position by
clicking either the Move Up or Move Down button.
| | 01:45 | So I will move this Conditional
Formatting Rule to the top of the list, to do
| | 01:50 | that I'll click the Move Up
button until it appears there.
| | 01:54 | So now if I apply the Conditional
Format, Excel will check for the top seven
| | 01:58 | values in the Pivot Table, and apply
the Style, and then it will move through
| | 02:01 | the rest of the list, and apply these
Styles if they apply to individual cells.
| | 02:08 | Now when I click OK, you'll notice
that the Pivot Table doesn't change.
| | 02:11 | That's because Excel applies all of
the rules and the way that I had the rule
| | 02:16 | set up, order doesn't really matter.
| | 02:18 | So, if I want to change the behavior of
the Conditional Formats, I can once again
| | 02:22 | open the Manage Rules dialog box, and
let's say that I wanted to change the
| | 02:28 | conditional formatting of the Pivot Table
so that if Excel finds one of the top
| | 02:32 | seven values for a cell, it stops
checking and applying other values.
| | 02:36 | So for example, if we know that the
top seven values in this Pivot Table are
| | 02:40 | all over 110, then applying both of
these two conditional formats, one with
| | 02:45 | bold and italic text, and another with green,
doesn't set those values apart as much as it might.
| | 02:51 | So what I will do is I will have Excel
stop applying conditional formats when it
| | 02:56 | finds one of the top seven
values in the Pivot Table.
| | 02:59 | To do that, I'll select the Rule, and
then I select the Stop If True check box.
| | 03:05 | So now when I click OK, Excel stops
applying conditional formats when it finds
| | 03:10 | one of the top seven values within the
Pivot Table, and instead of applying the
| | 03:14 | green background format for values
over 110, it only applies the white
| | 03:19 | background with the bold and italic
text, and a border for cells that contain
| | 03:24 | one of the top seven values
within the body of the Pivot Table.
| | 03:27 | Managing traditional formats is a
little more complicated in Excel 2007 than
| | 03:31 | it was in a previous version of Excel, but
that's the price you pay for a lot more flexibility.
| | 03:36 | If you take the time to work with your
rules, you'll discover many benefits to
| | 03:39 | the additional visualizations
offered by Excel 2007 Conditional Formats.
| | Collapse this transcript |
| Deleting conditional formatting rules| 00:00 | Conditional formats help you make
judgments about your data quickly, but you
| | 00:03 | might find that one or more of the
rules you created are no longer useful.
| | 00:07 | If that's the case, you can delete
rules individually from within the
| | 00:10 | Conditional Formatting Rules Manager or
get rid of them all using the controls
| | 00:13 | on the Home tab of the ribbon.
| | 00:15 | To delete a single rule you can click
any cell in the Pivot Table and then on the
| | 00:20 | Home tab of the ribbon, in the Styles
group, click Conditional Formatting and
| | 00:26 | then click Manage Rules.
| | 00:27 | From within the Conditional
Formatting Rules Manager you can delete any of
| | 00:30 | the individual rules.
| | 00:32 | So for example, if I wanted to delete
the rule that calls out the top seven
| | 00:36 | values, I can click it and then click the
Delete Rule button to remove it from the lineup.
| | 00:43 | Be sure to verify that any remaining
rules operate the way you want them to.
| | 00:46 | For example, if the rule that you just
deleted had a Stop If True condition,
| | 00:50 | you might have disrupted the rule's logic and
could cause Excel to apply rules it shouldn't.
| | 00:54 | I am done making my change
here and I'll your click OK.
| | 00:58 | And you'll notice that Excel updates the
conditional formatting of the Pivot Table.
| | 01:03 | If you want to remove more than one
conditional formatting rule at a time, then
| | 01:07 | on the Home tab of the ribbon, in the
Styles group, click Conditional Formatting
| | 01:12 | and then point to Clear Rules.
| | 01:14 | When you point to Clear
Rules you have three options.
| | 01:17 | The first is Clear Rules from Selected Cells.
| | 01:20 | In this case, the active cell is C8,
which contains the value 140, and if you
| | 01:25 | were to select Clear Rules from
Selected Cells than Excel would only remove the
| | 01:29 | rules from that cell.
| | 01:31 | The second option is
Clear Rules from Entire Sheet.
| | 01:35 | If you select that option then as the
name implies, Excel would remove every
| | 01:39 | conditional format that is present on
the active worksheet, in this case Sheet
| | 01:43 | 3, that includes the Pivot Table and
anything else you might have set up
| | 01:46 | around the Pivot Table.
| | 01:48 | The final option is Clear Rules from
this PivotTable and again as the name
| | 01:52 | implies, it only removes conditional
formats from the active Pivot Table.
| | 01:56 | If you had a conditional format set in
the cell anywhere outside of the Pivot
| | 02:00 | Table on this worksheet, then Excel
would leave that rule but delete the rules
| | 02:04 | from within the Pivot Table.
| | 02:05 | So I will select Clear Rules from this
PivotTable and Excel removes the rules.
| | 02:10 | Getting rid of conditional formats you
no longer need reduces the visual input
| | 02:13 | you have to process when
you view your Pivot Table.
| | 02:16 | If you find that a conditional format
is more of a distraction than a help,
| | 02:19 | don't hesitate to delete it.
| | Collapse this transcript |
|
|
6. Creating and Manipulating PivotChart ReportsCreating a PivotChart report| 00:01 | Pivot Tables help you summarize
large data sets efficiently but it can be
| | 00:04 | 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:11 | trends in your data.
| | 00:13 | Just as you can create charts based
on regular worksheet data sets, you
| | 00:16 | can create dynamic charts called Pivot
Chart from the data contained in Pivot Tables.
| | 00:21 | There are two ways to create a Pivot Chart.
| | 00:24 | You can either create a Pivot Table and
a Pivot Chart at the same time or you
| | 00:27 | can create a Pivot Chart
from the existing Pivot Table.
| | 00:30 | I should point out that because a
Pivot Chart summarizes the data in a Pivot
| | 00:34 | Table, it's not possible to
create a Pivot Chart by itself.
| | 00:37 | Because your Pivot Chart is based on a
Pivot Table, your data source must be
| | 00:41 | formatted as a data list,
preferably in Excel table.
| | 00:44 | So the program can create the Pivot
Table that powers the Pivot Chart.
| | 00:48 | If you're unclear about the type of
data you can summarize using a Pivot Table,
| | 00:51 | take a moment to review Chapter 1,
Lesson 3, Creating a Pivot Table.
| | 00:56 | To create a Pivot Chart at the same
time you create a Pivot Table, click any
| | 00:59 | cell in the data source and then on the
Insert tab of the ribbon, in the Tables
| | 01:04 | group, click the Pivot Table
button's down arrow and click PivotChart.
| | 01:10 | When you do, excel displays that Create
PivotTable with PivotChart dialog box.
| | 01:14 | In this dialog box, you verify that
Excel identified the correct data source.
| | 01:18 | In this case, it is Table1 and also
that you want to put the Pivot Table and
| | 01:23 | Pivot Chart on the new worksheet.
| | 01:26 | You do, click Ok and Excel creates
the Pivot Table and the Pivot Chart.
| | 01:32 | After you do that you can add your
values to the Pivot Table field list and
| | 01:40 | Excel creates a Pivot
Chart based on those values.
| | 01:42 | If you want to pivot the Pivot Chart,
you can do so the same way you would
| | 01:46 | with a Pivot Table.
| | 01:48 | I'll back out of my creation here so I
can show you how to create a Pivot Chart
| | 01:53 | based on an existing Pivot Table, and
I'll remove the worksheet. There we are.
| | 02:00 | I have an existing Pivot Table on Sheet
3, so I'll click any cell in the Pivot
| | 02:04 | Table and then to create a Pivot Chart
based on this existing Pivot Table, I'll
| | 02:08 | go to the Insert tab of the ribbon and
then in the Charts group, I'll select the
| | 02:13 | type of chart I want to create.
| | 02:14 | In this case I will create a Column chart,
which would be fairly messy because I
| | 02:19 | have some different levels of organization.
| | 02:22 | I create 2-D Column chart and you'll see
that I have FirmA and FirmB broken out by
| | 02:27 | month for the years 2008 and 2009.
| | 02:31 | There are some differences between
regular Excel charts and Pivot Charts.
| | 02:34 | The most important ones are that you
can't switch the row and column orientation
| | 02:38 | of a Pivot Chart report by using
the Select Data Source dialog box.
| | 02:41 | Of course, you can rearrange your
data by pivoting the Pivot Chart.
| | 02:45 | Next, you can't create an X-Y or Scatter
chart, a Stock chart or a Bubble chart.
| | 02:51 | Also, refreshing the Pivot Chart removes
trendlines, data labels, error bars and
| | 02:55 | a few other less common settings.
| | 02:57 | Finally, if you'd rather have your
Pivot Chart reside on a separate worksheet
| | 03:01 | from the Pivot Table, you can click the
Pivot Chart and then on the Design tab
| | 03:06 | of the PivotChart tools contextual tabs,
click Move Chart and you can select a
| | 03:12 | new location for your Pivot Chart.
| | 03:13 | Say for example, you wanted to put it on its
own sheet by itself, we'll call that Chart1.
| | 03:18 | You can select the New sheet option,
click OK and Excel creates a new sheet that
| | 03:23 | contains just the chart you created.
| | 03:27 | Pivot Charts enable you to summarize
your data visually providing an overview
| | 03:30 | of your data and opening the door to
insights you might not discover from
| | 03:33 | looking at the raw numbers.
| | 03:35 | You'll find that Pivot Charts are
powerful tools that help you analyze your
| | 03:38 | enterprise's data effectively.
| | Collapse this transcript |
| Pivoting a PivotChart report| 00:00 | The real power of a Pivot Chart comes to
fore when you rearrange your data dynamically.
| | 00:05 | 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 Pivot Chart.
| | 00:10 | A Pivot Chart, like a Pivot Table, uses
different field areas to determine the
| | 00:16 | organization of the Pivot Chart.
| | 00:18 | In a Pivot Table, you have the Row
Labels, which provide the values for the
| | 00:23 | rows, 2008 and 2009, and also the
months which we have here for Year and Month.
| | 00:29 | And then you have the Column Labels for
Company, where we have here FirmA and FirmB.
| | 00:33 | So there is the Pivot Table.
| | 00:35 | If I click the Pivot Chart, you'll
notice that the name of the areas changes.
| | 00:40 | Instead of having the Row Labels, we
have the Axis Fields, which provide the
| | 00:44 | values for the years and the months
along the axis for this line chart.
| | 00:50 | Year and Month here and
then we also have Company.
| | 00:53 | And this area is called the Legend Fields.
| | 00:55 | The Legend Fields provide values
for the lines in a line charts.
| | 00:59 | So for example, you have FirmA and FirmB.
| | 01:02 | FirmA is the blue line
and FirmB is the red line.
| | 01:05 | There is one line for each value or a
combination of values if you have more
| | 01:09 | than one field in the Legend Fields area.
| | 01:12 | So let's see what happens
when I pivot the Pivot Chart.
| | 01:15 | You pivot a Pivot Chart exactly the
same way that you pivot a Pivot Table.
| | 01:19 | So let's say that I wanted to
take month out of the equation.
| | 01:22 | So I'll just take Month and drag it to
the Fields area and you see that I have
| | 01:28 | total revenue for FirmA
and FirmB over 2008 and 2009.
| | 01:33 | For FirmA it declined slightly, for
FirmB it declined a bit more precipitously.
| | 01:38 | So let's take the Year out. Because I
have no field in the Axis Fields area,
| | 01:43 | there is no data displayed in
the body of the Pivot Chart.
| | 01:46 | But let's say that I add
Month to the Axis Fields area.
| | 01:50 | When I do, Excel finds the total
sales for all Januarys that are contained
| | 01:54 | in the Pivot Table.
| | 01:56 | So for example, for January for FirmA,
we have 195 and that is not just the
| | 02:02 | value for one year.
| | 02:03 | The data source contains data from 2008 and
2009 and 195 is the sum of those two values.
| | 02:11 | So for example, FirmA in 2008 plus
FirmA for January of 2009 gets you 195.
| | 02:18 | For FirmB, those same
two measures add up to 219.
| | 02:21 | Now, let's say that I bring in
Quarter and put it above Month.
| | 02:29 | This just makes the data a little bit
easier to visualize if you're operating as
| | 02:32 | an investor on a quarterly basis.
| | 02:34 | So you have the values for January,
February and March, Quarter 1, and you can see
| | 02:38 | the relative increase or decline.
| | 02:40 | April, May and June in Quarter 2 and so on.
| | 02:44 | Always remember that adding a second
row or column header creates subdivisions
| | 02:47 | within your data and it can change
the organization of your Pivot Chart.
| | 02:51 | The specific way that each pivot affects
the Pivot Chart depends on the chart's type.
| | 02:56 | Changing the Pivot Chart's arrangement
shifts to the data's emphasis, enabling
| | 03:00 | you to examine the data from
different perspectives, quickly and easily.
| | Collapse this transcript |
| Filtering a PivotChart report| 00:01 | Pivot Charts can help you summarize
huge data collections, but many times you
| | 00:04 | want to limit the data
displayed within a given category.
| | 00:07 | Just as you can limit the data
displayed in a Pivot Table, you can also limit
| | 00:11 | the data summarized by a
Pivot Chart by creating a filter.
| | 00:14 | There are two main ways you can
filter the contents of a Pivot Chart, by
| | 00:17 | selection or by rule.
| | 00:19 | Filtering by selection means that you
display a list of values in a field and
| | 00:23 | select the values you want to display.
| | 00:25 | Filtering by rule means that you
create a criteria that Excel uses to select
| | 00:29 | which values to display.
| | 00:31 | Excel 2007 introduced the Pivot Chart
Filter pane, which contains a series of
| | 00:35 | controls you can use to filter
the values in your Pivot Chart.
| | 00:38 | It appears when you click a Pivot Chart.
| | 00:42 | The problem is that the Pivot Chart
Filter pane takes up valuable screen real
| | 00:45 | estate and worse, duplicates the
functionality of the controls in the Pivot Table
| | 00:49 | Field List task pane.
| | 00:51 | Because I can use the controls in the
Pivot Table Field List task pane to pivot
| | 00:55 | the Pivot Chart and to create filters,
I prefer to hide the Pivot Chart Filter
| | 00:59 | pane and use the more familiar controls
in the Pivot Table Field List task pane.
| | 01:03 | To hide the Pivot Chart Filter pane, just
click the close box at the top and it disappears.
| | 01:11 | If you do want to display the Pivot
Chart Filter pane, click the Pivot Chart and
| | 01:16 | then on the Analyze contextual tab,
click the Pivot Chart Filter button.
| | 01:21 | You'll also notice that the Pivot
Table Field List task pane button is here.
| | 01:25 | If I click that Excel hides it, if I
click it again, Excel brings it back.
| | 01:29 | You'll notice that the button is
activated indicating that the Pivot Table Field
| | 01:33 | List task pane is displayed.
| | 01:35 | And again click in the Pivot Chart
Filter button brings back the Pivot Chart
| | 01:39 | Filter pane, but since I don't want to work
with it, I'll click the button again to hide it.
| | 01:44 | And now you can create filters like
you would with any other Pivot Table.
| | 01:47 | Let's say that I want to show
only those results for FirmA.
| | 01:52 | To create that filter, I'll click the
down arrow on the Company field header,
| | 01:57 | clear Select All, select FirmA, which is
the only company I want to show, click
| | 02:02 | OK and Excel updates the chart.
| | 02:05 | To remove the filter, I just click the
down arrow again and click Clear Filter.
| | 02:12 | You can also create filter by rule.
| | 02:15 | So let's say that I wanted to display
only those values for years before 2009.
| | 02:20 | To do that I would go to Year, click
the down arrow, point to Label Filters,
| | 02:26 | click Less Than to create a less than
rule, and I want to show values for years
| | 02:30 | that occurred before 2009.
| | 02:33 | So I have this less than, 2009, click OK and
Excel displays only those values for 2008.
| | 02:41 | Once again, to remove the filter, I
go to the header, click the down arrow
| | 02:45 | and click Clear Filter.
| | 02:47 | You can also filter a Pivot Chart
by using the Report Filter field.
| | 02:51 | So let's say that I only
wanted to see values from Quarter 1.
| | 02:54 | To do that, I would drag the Quarter
field to the Report Filter area, again
| | 02:59 | notice that dragging your field to the
Report Filter area doesn't change the
| | 03:02 | organization of the body of the
Pivot Chart or the Pivot Table.
| | 03:06 | Now to create my filter, I go up here
to the Field List area, click the down
| | 03:11 | arrow and you'll see that I have
my Report Filter controls here.
| | 03:14 | I only want to show values for Quarter 1, so
I'll click 1, making it active and click OK.
| | 03:21 | When I do, Excel updates the Pivot
Charts so that we only see data from Quarter
| | 03:25 | 1 in both years 2008 and 2009.
| | 03:30 | Creating filters gives you control over
the values that appear in your pivot chart.
| | 03:33 | When you want to narrow your focus and
examine just a subset of your data, Pivot
| | 03:37 | Chart filters enable you to do exactly that.
| | Collapse this transcript |
| Formatting a PivotChart report| 00:01 | Charts, including pivot
charts, 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
| | 00:12 | internal/external documents,
| | 00:13 | you should also ensure your pivot
charts conformed to those graphic
| | 00:16 | production standards.
| | 00:18 | If you want to change your Pivot
Chart's chart style, you can do so by
| | 00:22 | selecting either a prefabricated style
that comes with Excel 2007 or by changing
| | 00:26 | individual elements.
| | 00:28 | If you want to apply a built-in chart
style, you just click the Pivot Chart
| | 00:33 | and then on the Design contextual tab,
under PivotChart tools, you can select a
| | 00:38 | style from the Gallery.
| | 00:39 | To display the Gallery, click the More
button and you can select any one of the
| | 00:44 | styles that you like.
| | 00:46 | For demonstration purposes, I'll
use this black and gray style.
| | 00:50 | If you want to format individual chart
elements you can do so by selecting the
| | 00:55 | item, say for example, if I want to edit
the chart title area, I can click that
| | 01:01 | and I can get to Format and I can apply
a style to the element I just selected.
| | 01:06 | So let's say that I want to apply a
WordArt style, I am using black and gray so
| | 01:10 | I won't create anything too outlandish.
| | 01:13 | So I'll just select this setting here,
the WordArt, Gradient Fill, Gray within
| | 01:18 | gray outline, fits my rather gray theme
here, and there I have the chart title
| | 01:23 | with the new formatting.
| | 01:25 | You can also select the chart element
that you want to format using the Chart
| | 01:29 | Element tool, here in the current selection
group on the Format contextual tab of the ribbon.
| | 01:36 | So let's say that I click this down
arrow here the Chart Elements box and
| | 01:40 | I select Chart Area.
| | 01:42 | I can now apply a format to just the
Chart Area instead of the entire chart,
| | 01:46 | and I can also do it without having to
select that element directly, I can just
| | 01:50 | pick it from the list.
| | 01:52 | So let's say that I want to add a black outline.
| | 01:54 | I can click this shape
style and the outline appears.
| | 02:00 | Now let's say that you wanted to format an
individual data element within a data series.
| | 02:04 | Say for example, if I wanted to call out
the largest value in my plot area, and
| | 02:09 | in this case, that value is
in March of 2008, for FirmB.
| | 02:14 | So if you want to format a specific
item within a data series first you click
| | 02:18 | any item in the data series, and doing
so highlights the entire data series.
| | 02:24 | And notice that none of the dark
gray lines are selected. Instead, only the
| | 02:28 | lighter gray ones are.
| | 02:30 | So now I can click the
item that I want to format.
| | 02:34 | I just click here and Excel selects only that.
| | 02:38 | So the first click selects the entire
data series, the second click selects an
| | 02:42 | item within the data series.
| | 02:44 | And now I can change that item's format.
| | 02:46 | So let's say that I wanted
to just put in a red fill.
| | 02:50 | I can click the Shape Fill tool
and select my color from the palette.
| | 02:57 | And now the largest value within the chart
is called out from all of the other values.
| | 03:02 | Formatting Pivot Chart elements helps
you communicate important trends clearly,
| | 03:06 | whether you use formatting to provide
an overall look and feel, or to call out
| | 03:09 | individual data points, you should take
the time to experiment with different
| | 03:12 | formatting options and see which work
the best for you and for your audience.
| | Collapse this transcript |
| Changing the layout| 00:01 | When you create a Pivot Chart in
Excel 2007, Excel uses a basic layout to
| | 00:04 | determine when and where to display
elements such as the Chart Title, a Legend
| | 00:09 | representing the data series,
and Axis titles and labels.
| | 00:12 | The basic layout Excel uses is good
enough for a quick look at your data but you
| | 00:16 | will probably want to change how your
Pivot Chart's elements are arranged before
| | 00:19 | you share it with your colleagues.
| | 00:21 | Excel 2007 comes with a gallery of
chart layouts from which to choose.
| | 00:25 | You can display those layouts by
clicking your Pivot Chart, clicking the
| | 00:28 | Design tab under PivotChart tools, and then
clicking the Chart Layout group's More button.
| | 00:35 | Clicking any of the gallery's layout
supplies that layout to your Pivot Chart.
| | 00:38 | Just as an example I will apply this layout.
| | 00:43 | Even if the layout you choose includes
elements such as the Title or Legend,
| | 00:47 | those items either won't appear or
will display a placeholder value such as
| | 00:50 | chart title as in this case, if the
Pivot Chart's arrangement doesn't supply a
| | 00:54 | value for that element.
| | 00:56 | For example, if you summarize data
using a color chart and have Company in the
| | 01:00 | Axis Fields area and Year in the
Legend Fields area, the default Pivot Chart
| | 01:07 | layout won't display a chart title
unless you create the title yourself.
| | 01:12 | Just as you can do with any other
worksheet elements, you can move and
| | 01:15 | resize chart elements.
| | 01:16 | So for example, if I wanted to move the
Axis Title to the top, I can just drag
| | 01:21 | it so that it was up a little higher,
and again it's all just a matter of
| | 01:26 | personal preference.
| | 01:27 | You can also delete a chart element such
as the Axis Title by selecting the item
| | 01:32 | and pressing the Delete key.
| | 01:35 | However, if I were to reapply that
same format or a different format, Excel
| | 01:40 | would bring the item back.
| | 01:42 | For more fine-grained control over your
Pivot Chart's layout you can click the
| | 01:46 | Pivot Chart and then click
the Layout tab on the ribbon.
| | 01:51 | You can use the controls on this tab to
change all of your Pivot Chart's elements.
| | 01:54 | For example, if the layout you selected
did not contain a Legend, you can turn
| | 01:59 | that Legend on by going to the Labels
group, clicking Legend and then selecting
| | 02:03 | where you want to show your Legend.
| | 02:05 | In this case, I will show it to
the right and align it to the right.
| | 02:09 | And there you have it for 2008 and 2009.
| | 02:11 | 2008 is represented by the blue data
series, and 2009 is represented by the red.
| | 02:18 | You should take the time to experiment
with the built-in layouts available for
| | 02:21 | you to use with Excel 2007 pivot charts.
| | 02:23 | You might discover the most of your
design work has been done for you.
| | Collapse this transcript |
| Changing the chart type| 00:00 | With the exception of XY (Scatter)
charts, Stock charts, and Bubble charts, you
| | 00:04 | can create any type of chart you
want to summarize your Pivot Table data.
| | 00:08 | Before I describe the procedure to
change a Pivot Chart's chart type, I'd like
| | 00:12 | to give you a brief overview of the
chart types available to you in Excel 2007.
| | 00:16 | Column charts, which display data as
vertical columns organized by category, are
| | 00:20 | useful for showing data changes over time,
or for illustrating comparisons among items.
| | 00:26 | Line charts display continuous data
over time, which is perfect for showing
| | 00:30 | trends in data as long as the
data was captured at equal intervals.
| | 00:33 | Comparing a month's sales to a year's
sales doesn't make sense in a Line Chart.
| | 00:37 | Pie charts show the share of a total
contributed by the individual data values
| | 00:41 | within a single data series.
| | 00:43 | If you have more than one data series
in your Pivot Table, Excel displays just
| | 00:47 | the first one in your Pivot Chart.
| | 00:49 | If you want to create a Pie Chart that
summarizes more than one data series, you
| | 00:53 | should create a Donut Chart.
| | 00:55 | Bar charts, which display values as
horizontal bars, this type of chart is
| | 00:59 | perfect for summarizing data when the
Axis labels are long and the values that
| | 01:02 | are shown are durations.
| | 01:04 | Area charts emphasize the magnitude of
change over time and also show how much
| | 01:08 | each data element contributes to
the total for a given measurement.
| | 01:12 | Surface charts are mostly
used for scientific data.
| | 01:15 | You can use Surface charts to find the
optimum combination of two data sets,
| | 01:19 | such as comparing rainfall and crop production.
| | 01:22 | Finally, Radar charts enable you to compare
the aggregate values of several data series.
| | 01:27 | And once again, you can't create
XY (Scatter) charts, Stock charts, or Bubble charts.
| | 01:32 | To change a Pivot Chart's chart type
you click the Pivot Chart and then on the
| | 01:37 | Design contextual tab, in the Type
group, you click the Change Chart Type
| | 01:42 | button to open the
Change Chart Type dialog box.
| | 01:46 | From there, you can use the
Controls to select a new Pivot Chart type.
| | 01:49 | In this case, I will use a Column Chart.
| | 01:51 | It will be a little crowded,
but there is the result.
| | 01:56 | Selecting the best Pivot Chart type
to summarize your data will add clarity
| | 01:59 | to your presentations.
| | 02:00 | Take the time to study the type of
data you are analyzing and use that
| | 02:03 | information to select the most
appropriate Pivot Chart type.
| | Collapse this transcript |
| Adding a trendline| 00:01 | When you analyze data using a Pivot
Chart you must pay close attention to the
| | 00:04 | individual values in your data sets.
| | 00:06 | Even so, it's often beneficial to
take a step back and look at the overall
| | 00:10 | trends in your data.
| | 00:11 | You can enhance your analysis by
projecting future values, assuming current
| | 00:14 | trends stay constant, by adding
a Trendline to your Pivot Chart.
| | 00:18 | A Trendline exists only within the
chart in the Excel program memory.
| | 00:21 | It does not actually add data to your worksheet.
| | 00:24 | To create a Trendline, Excel
uses linear regression techniques.
| | 00:27 | These techniques are also implemented
in the Forecast function, so you can
| | 00:31 | generate the same results for data
you don't summarize in the Pivot Chart.
| | 00:35 | You create a Pivot Chart Trendline by
clicking the Pivot Chart and then on the
| | 00:39 | Layout contextual tab, clicking the
Trendline button and then selecting the type
| | 00:44 | of trendline you want to create.
| | 00:46 | In this case, I will click a Linear
Forecast Trendline, which creates a trendline
| | 00:51 | with a two period forecast.
| | 00:52 | In this case, my data is broken out by
month, so the forecast will be for two
| | 00:56 | months into the future.
| | 00:58 | If I click the Linear Forecast
Trendline item, Excel adds the Trendline.
| | 01:04 | Always use Linear for the Forecast
type unless you know that you need to
| | 01:07 | use something else.
| | 01:08 | Most of the time you will only use the
other types of regression in scientific
| | 01:11 | and engineering work.
| | 01:13 | Also, your data's measurements
should occur at regular intervals so that
| | 01:16 | forecasting a given number of time
periods into the future make sense.
| | 01:20 | If you would like more control over
your Trendline, you can click Trendline>
| | 01:24 | More Trendline Options and then use the
controls in the Format Trendline dialog
| | 01:28 | box to make the changes that you want.
| | 01:30 | You can change your trend or regression
type, again you should only change that
| | 01:33 | if you know for a fact you need to use
a method other than linear, and you can
| | 01:37 | also change your forecast.
| | 01:39 | So for example, that instead of
forecasting two periods into the future, I wanted
| | 01:43 | to make a forecast that was
four periods into the future.
| | 01:46 | To do that, I edit the value in the
Forward box under Forecast, so that I
| | 01:51 | am looking forward four periods and I can
click Close, and Excel extends the Trendline.
| | 01:58 | The farther out you make your
forecast, the more likely they are to be
| | 02:00 | inaccurate, but it helps to
provide a baseline for comparison.
| | 02:04 | Trendlines help you
visualize future trends in your data.
| | 02:06 | A Pivot Chart can't replace detailed
analysis but they do help set the stage for
| | 02:10 | your presentation regarding that data.
| | Collapse this transcript |
|
|
7. Printing PivotTable and PivotChart ReportsPrinting a PivotTable report| 00:00 | Pivot tables enable you to rearrange
your data dynamically, but you will usually
| | 00:04 | find one or two configurations that
you would like to include in an annual
| | 00:07 | report or another hard copy document.
| | 00:10 | When you are ready to commit a
PivotTable to paper you can print it.
| | 00:13 | If you have more than one PivotTable in
a worksheet and you only want to print
| | 00:16 | one of them, you can click any cell in
the PivotTable and then on the Options
| | 00:22 | contextual tab with a ribbon, click
Select and click Entire PivotTable.
| | 00:28 | Then to set the Pivot Table as print area,
you go to Page Layout on the ribbon,
| | 00:33 | click Print Area and then click Set Print Area.
| | 00:38 | When you do, Excel puts a line around
the PivotTable, which is what you selected.
| | 00:43 | So now, regardless of what else is on the
worksheet, Excel will only print this Pivot Table.
| | 00:49 | You can configure your PivotTable to have the
row and column labels printed on every page.
| | 00:53 | To do that, on the Page Layout tab
of the ribbon, click the Page Setup
| | 00:57 | group's dialog expander.
| | 00:59 | That is this small button here to
the right at the bottom right corner of
| | 01:03 | the Page Setup group.
| | 01:04 | When you click it, the
Page Setup dialog box appears.
| | 01:08 | In the Page Setup dialog box, on the
Sheet tab, make sure that the Row and
| | 01:12 | Column Headings box is
cleared, not selected, but cleared.
| | 01:17 | Also make sure that the Rows at
repeat at top and Columns to repeat at left
| | 01:21 | boxes are completely empty.
| | 01:23 | Clearing those boxes prevents data
from outside the Pivot Table from being
| | 01:27 | included when you print it.
| | 01:28 | Finally, you can choose whether to
print Expand and Collapse buttons.
| | 01:33 | To do that, click any cell on the Pivot
Table and then on the Options contextual
| | 01:37 | tab, click the Options
button in the PivotTable group.
| | 01:41 | Then on the Printing tab of the dialog
box, you can either select or clear the
| | 01:47 | Print expand/collapse buttons
when displayed on the PivotTable box.
| | 01:50 | If you want to print them, check the
box and when you are done changing your
| | 01:55 | options, you can click OK.
| | 01:57 | If for any reason you haven't shown the
Expand and Collapse buttons, for example
| | 02:01 | by clearing this option which hides the
Expand and Collapse buttons within the
| | 02:06 | body of the Pivot Table, then the
option I just showed you would be grayed out,
| | 02:10 | but if they are displayed, then
the option will be available to you.
| | 02:14 | Printing a Pivot Table seems like a
straightforward operation and it usually is.
| | 02:18 | That said, you do have a number of options
when it comes to printing your Pivot Table.
| | 02:22 | Experiment with the different ways you
can control how your Pivot Tables print,
| | 02:25 | 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 Pivot Table it's
likely that you will have data from many
| | 00:03 | categories grouped
together into a single display.
| | 00:06 | If that's the case, it might make
sense to print your Pivot Table data on a
| | 00:09 | number of worksheets.
| | 00:11 | There are two ways you can
separate your Pivot Table data.
| | 00:13 | The first is by using the fields in
the Row Labels area and the second is by
| | 00:17 | using the fields in the Report Filter area.
| | 00:19 | If your Pivot Table has more than one
field in the Row Labels area, you can have
| | 00:22 | Excel print each group on a new page.
| | 00:25 | To do that, you click any cell that
contains a label for the first level.
| | 00:29 | In this case, the first level on
the Row Labels area is here and I have
| | 00:33 | two years: 2008, 2009.
| | 00:36 | I will click 2008 and then on the Options
contextual tab, I will click Field Settings.
| | 00:44 | In the Field Settings dialog box, I
can click the Layout & Print tab and from
| | 00:48 | there, at the bottom, I have
Insert page break after each item.
| | 00:53 | If I check that box and click OK, when I
print the Pivot Table, Excel will print
| | 00:58 | the data from 2008 on one page and
the data from 2009 on the next page.
| | 01:04 | The other way to separate out your
Pivot Table data is if you have a field in
| | 01:07 | the Report Filter area.
| | 01:09 | In this case, I have Quarter, so that I
can either filter or break out my data
| | 01:14 | by quarter, but I can also use it for printing.
| | 01:16 | And the way that works when you have a
field in the Report Filter area is that
| | 01:20 | you can create a separate PivotTable on an
individual worksheet for each item in Quarter.
| | 01:25 | So in this case, the Quarter field
contains four values: 1, 2, 3 and 4.
| | 01:31 | If I want to create separate Pivot
Tables for each of those Quarters, I can go
| | 01:35 | up and click the Options button.
| | 01:37 | This is on the Options
contextual tab of the ribbon.
| | 01:40 | Click the Option button's down arrow
and then click Show report filter pages.
| | 01:47 | When I do, Excel displays the Show
report filter pages dialog box and it shows
| | 01:51 | me all of the fields that
are in the Report Filter area.
| | 01:54 | You can only separate the contents of
your Pivot Table based on one field within
| | 01:59 | the Report Filter area.
| | 02:01 | Otherwise, you'd have an explosion of
pages, for example, if you had five values
| | 02:05 | in one field and ten in another, you
would end up with fifty worksheets, each of
| | 02:08 | which have their own Pivot
Table and that's just too many.
| | 02:12 | So, we will select one field at a time,
it's Quarter, I will click OK and Excel
| | 02:17 | creates individual
worksheets for Quarter 1, Quarter 2.
| | 02:22 | You can see we have Quarter 2's data
displayed here, Quarter 3 and Quarter 4.
| | 02:29 | If you want to print those worksheets,
you can select them and to do that you
| | 02:33 | click one worksheet that you want to
select and because these are in a line, I
| | 02:38 | can go to the last one I want
to select, in this case Sheet 1.
| | 02:42 | Hold down the Shift key and click.
| | 02:44 | When I do, Excel selects all the
sheets from 1, 2, 3 and 4 inclusive.
| | 02:50 | Sheets 3 and 2 are not selected.
| | 02:52 | And now I can go up to the Office button,
click Print and print the Active Sheets.
| | 03:01 | The built-in Excel 2007 Pivot Table
functionality enables you to separate your
| | 03:05 | data when you print without
necessarily separating the data in the workbook.
| | 03:09 | Creating new worksheets for each
item in the Report Filter area does add
| | 03:13 | worksheets to your workbook but
setting the Insert page break after each item
| | 03:17 | option, enables you to separate those
categories of data without affecting your
| | 03:20 | Pivot Table's organization.
| | Collapse this transcript |
| Printing a PivotChart report| 00:00 | When you are ready to print an Excel
2007 Pivot Chart, you can use the same
| | 00:04 | skills you have used to print regular charts.
| | 00:06 | In this lesson, I will review some of
the things you can do to make printing
| | 00:09 | your Pivot Chart go smoothly.
| | 00:11 | There are two scenarios
for printing a Pivot Chart.
| | 00:13 | The first is if the Pivot Chart is part
of a worksheet and the second is if it's
| | 00:17 | on its own chart sheet.
| | 00:18 | If the Pivot Chart is part of a
worksheet, you can click the PivotChart, click
| | 00:23 | the Office button and then click
Print to display the Print dialog box.
| | 00:29 | The Selected Chart option will be selected.
| | 00:31 | In fact it's the only one available.
| | 00:33 | Then you can select your printer, set
your printing options and click OK to be
| | 00:37 | on your way, I won't do it in this case.
| | 00:40 | If your Pivot Chart is on its own chart
sheet, then you get a few more options.
| | 00:44 | I have a Pivot Chart here, a
separate one, on this sheet named Chart 1.
| | 00:49 | So, when I click the Office button and
click Print, I get a few more options.
| | 00:55 | For example, you can print the entire
workbook if you like and not just the chart sheet.
| | 01:00 | Clicking Preview also shows the
difference between printing a Pivot Chart that's
| | 01:04 | on a worksheet and a Pivot
Chart that's on its own chart sheet.
| | 01:07 | In this case, with the Pivot Chart on
its own chart sheet, I will click Preview
| | 01:11 | and you will see that the chart
takes up the entire worksheet.
| | 01:14 | If I close preview and go back to the
sheet that contains the Pivot Chart,
| | 01:19 | which is part of our regular worksheet, click
the Office button, click Print and click Preview,
| | 01:27 | you see that the Pivot Chart
takes up a lot less area on the sheet.
| | 01:30 | Printing the charts to paper produces
copies that you can include in annual
| | 01:34 | reports, news releases and other correspondence.
| | 01:37 | You can also choose to print to an
Adobe Portable Document format file or
| | 01:40 | other electronic file format, which
enables you to take a copy of the chart
| | 01:44 | with you as you travel without taking
the original workbook from which you
| | 01:47 | have printed the Pivot Chart.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Thanks for viewing Excel 2007
Pivot Tables for Data Analysis.
| | 00:04 | I hope you have enjoyed the course and
that you have learned a lot and that you
| | 00:07 | will be able to apply the skills that
you have learned to the Pivot Tables you
| | 00:10 | use in your everyday business.
| | 00:12 | If you didn't use Pivot
Tables before, I hope you will now.
| | 00:15 | Thanks again and I appreciate your time.
| | Collapse this transcript |
|
|
