IntroductionWelcome| 00:03 | Hi, I'm Curt Frye.
| | 00:05 | Welcome to Excel 2008 for Mac
Pivot Tables for Data Analysis.
| | 00:09 | In this course, I'll show you how to use
Pivot Tables to make sense of your organization's data.
| | 00:13 | I'll begin by showing you how to create a
Pivot Table from data already in your Excel workbooks.
| | 00:18 | Then, using that knowledge as a base,
| | 00:20 | I'll demonstrate how to create Pivot Tables
using data from an external source.
| | 00:24 | We'll enhance your ability to analyze your data
by showing you how to reorganize, or pivot, a Pivot Table.
| | 00:29 | After you organize your Pivot Tables data,
you can sort the data to show relative importance and relevance,
| | 00:34 | or create filters that enable you to focus on the data
you need to develop useful insights into your business.
| | 00:39 | In short, I'll show you how to summarize and analyze data
using one of Excel's most powerful and overlooked features.
| | 00:45 | Let's get started with Excel 2008 for Mac
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 a DVD-ROM,
you have access to the exercise
| | 00:08 | files used throughout this title.
| | 00:10 | There are four chapters in this title
and within each chapter there are a number
| | 00:14 | of exercise files that you use.
| | 00:17 | Those are all Excel workbooks, except
for one file in Chapter01, and that is the
| | 00:22 | OutsideSource.csv file.
| | 00:25 | CSV just stands for Comma Separated
Values, and you will be importing those
| | 00:29 | values into an Excel worksheet.
| | 00:31 | If you are a monthly subscriber or
annual subscriber to lynda.com, you don't
| | 00:35 | have access to the exercise files, but
you can follow along from scratch or
| | 00:38 | create your own assets.
| | 00:39 | Let's get started.
| | 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 and columns of
| | 00:11 | data, labels, sub totals and grand totals.
| | 00:14 | But what Pivot Tables enable you to do
is change the structure of your data to
| | 00:18 | emphasize different aspects of that data.
| | 00:20 | As an example, take a look at the
Pivot Table that I have in the worksheet
| | 00:24 | currently on your screen.
| | 00:26 | You'll see that I have
revenue data for two companies:
| | 00:28 | FirmA and FirmB, broken out by
month over two years: 2008 and 2009.
| | 00:34 | A Pivot Table's structure
consists of four separate areas.
| | 00:38 | On the left side we have the rows area, which
in this case contains the year and month fields.
| | 00:43 | The Pivot Table uses year and month to
determine which values appear in which
| | 00:46 | rows inside the Pivot Table.
| | 00:48 | There is also the columns area.
| | 00:50 | The field Company is
currently in the columns area.
| | 00:52 | We have FirmA and FirmB.
| | 00:54 | Those two values determine the
organization of data within the Pivot Table's columns.
| | 00:59 | The third major area of your Pivot Table
is the data area, and that is the body
| | 01:03 | of the Pivot Table, and it contains
the values from the source data table.
| | 01:07 | The fourth area of the Pivot Table, which I
am not using here, is the Page Fields area.
| | 01:11 | You use the Page Fields area to filter
your Pivot Table using values that do not
| | 01:15 | change the organization of your Pivot Table.
| | 01:18 | The real value of a Pivot Table is that it
enables you to reorganize your data dynamically.
| | 01:23 | So, for example, let's suppose that I
wanted to see each company's performance by
| | 01:27 | year, broken out by month.
| | 01:29 | Right now, I have it by year
and month, and then by company.
| | 01:32 | If I want to change the emphasis of my
data, I can drag the Year field below
| | 01:37 | Company and the Pivot Table reorganizes
the data with this different emphasis.
| | 01:42 | Now if I want to see how FirmA and FirmB did
in 2008 by month, I can do so fairly easily.
| | 01:49 | I can also limit the data that
appears inside of a Pivot Table.
| | 01:52 | So, for example, if I want to show
data only for FirmA, I click a cell that
| | 01:57 | contains FirmA or FirmB.
| | 01:59 | As long as I select a value from the same
field by which I want to filter, I am fine.
| | 02:04 | I'll go up to the PivotTable toolbar.
| | 02:06 | Click the PivotTable button.
| | 02:09 | Click Field Settings, and then within
the Pivot Table Field dialog box I can
| | 02:13 | select which value I want to hide.
| | 02:14 | In this case, I want to see values
only from FirmA, so I will hide values for
| | 02:20 | FirmB and then I'll click OK.
| | 02:23 | The Pivot Table in this new
configuration only shows values for FirmA.
| | 02:26 | I hope this brief demonstration
has given you a feel for Pivot
| | 02:29 | Table's capabilities.
| | 02:31 | In the remaining lessons in this course,
I'll go into much more detail on how to
| | 02:34 | 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 enables you to summarize
and reorganize your data dynamically, but
| | 00:04 | you can't summarize just any data set.
| | 00:06 | In most cases, your data needs
to be laid out as a data list.
| | 00:10 | The source data must be laid out as
a table, with column headers and no
| | 00:13 | extraneous data surrounding the table.
| | 00:15 | The data I have in this
worksheet fits that description.
| | 00:18 | I have five columns:
| | 00:20 | Year, Quarter, Month, Company and Revenue.
| | 00:25 | Also notice that I have no other values in
the column to the right of the data list.
| | 00:30 | So, for example, if I were to type just
anything, even just a stray value, when
| | 00:35 | I create the Pivot Table, Excel will
try to include this entire column of blank
| | 00:39 | values except for the
number 50 into the Pivot Table.
| | 00:42 | That's not what I want.
| | 00:43 | So, I will delete the value.
| | 00:46 | And now, Excel will create the Pivot Table
using only the values from these five rows.
| | 00:51 | Also when you create a Pivot Table
from data contained in the data list, you
| | 00:54 | should make certain that the headers, in
this case Year, Quarter, Month, Company
| | 00:58 | and Revenue are formatted differently
from the values in the body of the list.
| | 01:02 | My favorite formatting to use is Center
and Bold, so you'll notice that I have
| | 01:06 | these values centered within the
cell and formatted using bold face.
| | 01:10 | You can also create your
Pivot Table from an Excel list.
| | 01:13 | To create a list, click any
cell in your data collection.
| | 01:16 | Go up to Insert and click List.
| | 01:20 | You can use the controls in the
List Wizard to create your list.
| | 01:23 | First you should verify whether Excel
correctly identified the cell range that
| | 01:26 | contains the data for your list.
| | 01:28 | In this case we have A1 through E49,
which I know is correct, and because
| | 01:33 | I've formatted my headers differently,
Excel correctly identifies that my
| | 01:36 | list does contain headers.
| | 01:37 | I am not using an external data source. And
I want to create the list or in existing sheet.
| | 01:42 | There are other options for creating a
list, but I don't need to use them in
| | 01:46 | this case, so I would just click Finish.
| | 01:49 | Excel creates my Excel list and
it also displays the List toolbar.
| | 01:53 | I won't go into every aspect of what you
can do with List, but there are two
| | 01:57 | items that I would like to tell you about.
| | 01:59 | The first is filtering the data
that appears within your Excel list.
| | 02:02 | So lets say, for example, that I only wanted
to see values relating to the month of January.
| | 02:08 | To display just those values, I could
go to the Month header, click the filter
| | 02:13 | arrow, and select the
values that I want to display.
| | 02:16 | In this case, I was only
interested in let's say January.
| | 02:20 | When I click that item, Excel
displays only those values from the month of
| | 02:23 | January, both from FirmA and
FirmB, and for the year 2008 and 2009.
| | 02:29 | You can also display
what's called the Total Row.
| | 02:31 | You'll see that button here on the List toolbar.
| | 02:34 | If I click Total Row, Excel includes
a Total Row at the bottom of the list.
| | 02:39 | By default the Total Row finds the
arithmetic sum of the visible values in
| | 02:43 | the column above it.
| | 02:44 | So, for example, in this case,
these values total up to 414.
| | 02:48 | If you want, you can change the
summary operation that the Total Row applies.
| | 02:52 | If I click the cell in the Total Row and
then click this arrow, I can select the
| | 02:56 | summary operation that I want to apply.
| | 02:58 | So if I wanted to find the average, I could
click Average, and I see that the new value is 103.
| | 03:04 | Changing it back to Sum. I get my value of 414.
| | 03:07 | If I want to get rid of the Total row,
I can just click the Total Row button on
| | 03:11 | the List toolbar and it goes away.
| | 03:15 | Once you have your source data arranged in
a data list, you can create a Pivot Table.
| | 03:19 | I cover the other case in the
lesson entitled 'Consolidating Data from
| | 03:23 | Multiple Sources,' 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 lets you rearrange, sort
and filter a dataset on the fly, so you
| | 00:08 | can analyze it from several different
perspectives with a minimum of effort.
| | 00:11 | In this lesson, I will show you how to
create a Pivot Table from a data list
| | 00:14 | that's stored in the same workbook.
| | 00:16 | To create a Pivot Table, click any cell
in the data list from which you want to
| | 00:20 | create the Pivot Table and then on the
Data menu, click PivotTable Report to
| | 00:25 | display the PivotTable Wizard.
| | 00:27 | In this case, the currently selected
option that I want to create the Pivot Table from
| | 00:32 | an Excel list or database is correct,
| | 00:34 | so I can click Next.
| | 00:37 | The next Wizard screen identifies the
data that Excel thinks that I want to
| | 00:40 | include in the Pivot Table.
| | 00:41 | In this case, that is the range from
A1 to E49, which I know is correct,
| | 00:46 | so I can click next.
| | 00:49 | On the last page of the PivotTable
Wizard, you can choose whether to put the
| | 00:52 | Pivot Table on a new
sheet or on an existing sheet.
| | 00:55 | I prefer to put my Pivot Tables on new
sheets, so that it's not juxtaposed with
| | 00:59 | the data from which I created the Pivot Table.
| | 01:02 | To me it feels more crowded and I would
rather have the Pivot Table on it's own
| | 01:05 | worksheet, so I can view it on isolation.
| | 01:08 | If you prefer, you can select the
Existing sheet option and type in the name
| | 01:12 | of the worksheet or select the worksheet using
the RefEdit Control where you want to create it.
| | 01:16 | But in this case, I
still want to do a new sheet.
| | 01:18 | I will select that option
and I will click that Finish.
| | 01:21 | I will bring up the zoom level a bit
so you get a better view of the data.
| | 01:25 | So here I have a blank Pivot Table.
| | 01:27 | I have five fields of values
available for me to structure the Pivot Table.
| | 01:31 | I have Year, Quarter, Month, Company,
and Revenue, which mirror the fields that
| | 01:34 | I have in my data list.
| | 01:35 | So to create the Pivot Table, I can
drag the fields from the PivotTable toolbar
| | 01:40 | to the body of the Pivot Table.
| | 01:41 | I will start off by adding the Year field.
| | 01:45 | Then I will add the Month field.
| | 01:48 | Notice that the I-bar, this gray bar,
appears to the right of the Year field header.
| | 01:53 | That means when I add the Month field
to the PivotTable Report it will be at a
| | 01:57 | lower level of organization than the Year field.
| | 02:00 | So I will drop it, and along
the top I will place Company.
| | 02:04 | Those will go in the column fields area.
| | 02:06 | So I will drop it here. And now I can add my
Revenue, which will go into the Data Items area.
| | 02:13 | In my Pivot Table, I have the data
arranged by year, broken out in detail by Month.
| | 02:18 | And in the columns,
| | 02:19 | it's broken out by FirmA and FirmB,
which are the only two companies that appear
| | 02:24 | in the Company field.
| | 02:25 | Now that you have learned how to create
a Pivot Table, you can move forward and
| | 02:28 | discover how to rearrange your data dynamically.
| | Collapse this transcript |
| Pivoting a PivotTable report| 00:00 | The real power of the Pivot Table comes out
when you want to rearrange your data dynamically.
| | 00:04 | A task that would take several minutes
if done by hand takes just a few seconds
| | 00:08 | when you summarize your data in a Pivot Table.
| | 00:10 | The row and column headers provide the
basic outline of your Pivot Table data.
| | 00:14 | In this case, we have Year,
and then we have companies:
| | 00:17 | FirmA and FirmB. And the data in this cell
represents FirmA sales for the year 2008.
| | 00:24 | If you change the row and column header
positions, you change the organization
| | 00:27 | of data in your Pivot Table.
| | 00:29 | For example, if I pivot the Company
field header beside Year, then we'll see a
| | 00:33 | different arrangement.
| | 00:34 | To do that, I move the mouse
pointer over top of the field header.
| | 00:38 | You see the grab-hand.
| | 00:39 | Press down the left mouse button and
drag the field until you see the vertical
| | 00:44 | I-bar here to the right of Year.
| | 00:46 | When I drop it, we're
organized by year and now by company.
| | 00:49 | If I wanted to organize the data by
company and then by year, I would drag the
| | 00:52 | Company field header to the left of Year.
| | 00:55 | And you see half of an I bar there,
right at the edge of the Pivot Table.
| | 00:58 | So dropping it, we now have our data
arranged by company and then by year.
| | 01:03 | I'll put the Company field
header back up to the top.
| | 01:07 | If you add a second row or column header,
you create subdivisions within your data.
| | 01:11 | So, for example, if I were to bring
down Quarter, which is a period of three
| | 01:15 | months, put it to the right of year, and
drop it, you now see your data arranged
| | 01:20 | by year and by quarter, and the
values are further broken down.
| | 01:23 | So we have FirmA's revenue for quarter
number 1 of 2008, and so on, with the
| | 01:27 | same value selected for 2009.
| | 01:30 | You can continue adding subdivisions, such
as by adding month to the right of quarter.
| | 01:35 | Now you see quarter number one, total
there, broken down again by individual
| | 01:40 | month within quarter number one.
| | 01:42 | If you want to remove a field from a
Pivot Table, you can't just drag the field
| | 01:46 | header to the PivotTable toolbar.
| | 01:48 | You end up dropping it in the Page Fields area.
| | 01:51 | Instead, what you need to do is select
any cell within the field and then, on the
| | 01:57 | PivotTable toolbar, click
the Field Settings button.
| | 01:59 | Verify that the field you're going to
hide appears in the Name box and click Hide.
| | 02:05 | Changing the Pivot Table's arrangement
shifts the data's emphasis, enabling you
| | 02:09 | to examine the data from
different perspectives quickly and easily.
| | Collapse this transcript |
| Importing data from an external source| 00:00 | Excel 2008 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 your current data source resides in a
file outside your current workbook, you
| | 00:10 | can import the data into Excel.
| | 00:12 | To bring data into Excel, you start
on a blank worksheet and then on the
| | 00:17 | File menu click Import.
| | 00:20 | In the Import dialog box, you can select
the type of file that you want to bring in.
| | 00:24 | You can have a CSV file,
which is Comma-Separated Values.
| | 00:28 | You can also bring in data from a
FileMaker Pro database, from an HTML file,
| | 00:33 | or from a Text file.
| | 00:35 | In this case, the data I want to bring
in is in a comma-separated values file.
| | 00:39 | The option is selected, so I will click Import.
| | 00:42 | I can now choose a file.
| | 00:44 | In the Exercise Files folder under
Chapter01, I have OutsideSource.csv.
| | 00:52 | I'll click it and click Get Data.
| | 00:56 | When I do, Excel
displays the Text Import Wizard.
| | 00:59 | The first thing you should do is
select the type of file that you're
| | 01:02 | bringing in, whether the data you're
bringing in was Delimited, meaning that
| | 01:06 | there are characters such as commas
or tabs that separate each field's
| | 01:09 | values, or Fixed width.
| | 01:11 | In a Fixed width file, the fields
are aligned in columns and there is a
| | 01:14 | definite number of spaces or
characters between each field.
| | 01:17 | For example, the first field could be five
characters long, the second could be nine and so on.
| | 01:23 | In this case, the file is Delimited,
| | 01:25 | so I will continue on.
| | 01:26 | I do want to start the import at row 1.
| | 01:29 | The file origin is on my Macintosh and
the Data preview appears to be normal.
| | 01:34 | So I'll click Next.
| | 01:35 | Now you can identify the
Delimiters contained within your dataset.
| | 01:39 | In this case, the data contains Commas,
which is selected, and then Tab is also selected.
| | 01:45 | Tab is another common delimiter.
| | 01:47 | There are no tabs in our dataset, so we
can leave it selected or we can clear it.
| | 01:50 | It doesn't really matter either way.
| | 01:52 | Looking again at the Data preview
pane, we see that Excel has identified
| | 01:56 | the columns correctly:
| | 01:58 | Year, Quarter, Month, Company and Revenue.
| | 02:00 | So we can click Next.
| | 02:02 | You can use the controls on this screen
of the Text Import Wizard to assign data
| | 02:05 | types to each of the columns.
| | 02:07 | In this case, we don't need to assign
any data types because Excel would do that
| | 02:11 | when we create our Pivot Table.
| | 02:12 | So I can just once again ensure that
the different columns are correctly
| | 02:16 | outlined and click Finish.
| | 02:19 | The Import Data dialog box appears
asking where I want to put the data.
| | 02:22 | I'll put it on the existing sheet,
in cell A2, which is the one currently
| | 02:27 | selected and click OK.
| | 02:30 | And there is the data.
| | 02:31 | Now that I've created my data list,
I want to format the column header
| | 02:34 | separately so Excel knows that they are
column headers when it creates the Pivot
| | 02:38 | Table from this data.
| | 02:39 | So I'll increase my zoom level a little
bit, just to make things easier to handle.
| | 02:46 | Drag the toolbar out of the way.
| | 02:49 | Select those cells and then on the Formatting
palette, Bold and Center Alignment. There we are.
| | 02:56 | Click there, and save my work.
| | 02:59 | Now that I have my data list in
place, I can create my Pivot Table.
| | 03:02 | Again, it's Data>PivotTable Report
and I've already worked through the
| | 03:07 | PivotTable Wizard before and I know that
all the default selections are what I'm
| | 03:11 | going to use anyway.
| | 03:12 | So I'll just click Finish.
| | 03:14 | There is my Pivot Table.
| | 03:15 | I can go back to the familiar
configuration of Year and Month in the Row area,
| | 03:21 | Company in the Column Fields
area, and Revenue in the middle.
| | 03:28 | It's important for you to understand
that when you import a data file, you
| | 03:32 | create a snapshot of the data
source at the time you downloaded it.
| | 03:35 | If the data in that file might change,
be sure to download a current copy of the
| | 03:39 | file before starting your analysis.
| | Collapse this transcript |
| Consolidating data from multiple sources| 00:00 | Most of the time you will create a
Pivot Table from a data list, but you can
| | 00:03 | also summarize multiple data ranges
using a Pivot Table if the data is
| | 00:07 | formatted appropriately.
| | 00:09 | To consolidate a series of data ranges
into a Pivot Table, the ranges must be
| | 00:12 | laid out in cross tabular format
and have exactly the same structure.
| | 00:17 | As an example, consider the
worksheets that I have in my
| | 00:19 | Consolidating.xlsx worksheet.
| | 00:22 | On the SupportCalls worksheet, I have
data reflecting the number of support calls
| | 00:25 | received in the years 2007, 2008,
2009 and 2010 for the four regions
| | 00:31 | North, South, East and West.
| | 00:33 | I have the same data for order
calls on the OrderCalls worksheet.
| | 00:40 | And I have the same data for
return calls for product returns on the
| | 00:44 | ReturnCalls worksheet.
| | 00:46 | Please note that the Region and Year column
and row identifiers occur within the same cell.
| | 00:51 | The reason you need to do that when you
are consolidating different ranges into
| | 00:55 | a Pivot Table is that the data
source must be a perfect rectangle.
| | 00:59 | In other words, if I were to have
region here and year here in C2, Excel would
| | 01:04 | try to bring in data from B2 to F7
because it would include these two cells in
| | 01:10 | the region, which would extend the
definition all the way from B2 to F7.
| | 01:15 | So now that my data is laid out
properly, I can bring it into a Pivot Table.
| | 01:19 | To do that, I click Data>PivotTable Report.
| | 01:23 | On the first page of the
PivotTable Wizard I can select Multiple
| | 01:27 | consolidation ranges. Click Next.
| | 01:31 | On Step 2, I can decide how
many page fields I want to create.
| | 01:35 | Page fields are fields that you can
use to filter your Pivot Table without
| | 01:39 | changing its arrangement.
| | 01:41 | In this case, I don't want to create any,
so I will create the page fields I want.
| | 01:45 | I'll select that option and I'll click Next.
| | 01:50 | Here on Step 2b of the wizard, I can
select the ranges that I want to include
| | 01:53 | in the Pivot Table.
| | 01:54 | I'll just drag this a bit over to the
side to uncover the entire data range that
| | 01:58 | I'm going to be including.
| | 02:00 | So now that I have the cursor here in
the Range box, I can select the cells
| | 02:06 | that I want to include,
| | 02:08 | verify that they appear
correctly in the Range box, and click Add.
| | 02:13 | I can now do the same
thing for my other two fields.
| | 02:17 | So I'll go to OrderCalls. Select that range.
| | 02:23 | It's correct.
| | 02:24 | Click Add. And then do the same thing
on the SupportCalls worksheet. And Add.
| | 02:33 | So I have the same ranges from
OrderCalls, ReturnCalls, and SupportCalls.
| | 02:37 | Now that they are on place, I can
verify that I'm creating zero page fields,
| | 02:41 | which is what I want. Click Next.
| | 02:44 | Verify that I want to create the Pivot
Table on a new sheet, and click Finish.
| | 02:48 | I'll increase the zoom, so you
have a better look at the data.
| | 02:52 | So now I have a Pivot Table.
| | 02:53 | What's different about this Pivot
Table is that I do not have the names of
| | 02:58 | the fields that I want.
| | 02:58 | So, for example, all I have are column
and row, when, in fact, the row values
| | 03:03 | are regions and the column values are years.
| | 03:06 | To change that, I click the Row field
header and then on the PivotTable toolbar,
| | 03:11 | I can click PivotTable>Field Settings.
| | 03:15 | Inside the PivotTable Field dialog
box I can assign a new name to my field.
| | 03:20 | I'm looking in the rows and these are
the regions, so I'll type Regions and OK,
| | 03:26 | and the field name changes.
| | 03:27 | I can do the same thing for the Column.
| | 03:30 | Click the Column field header, PivotTable>
Field Settings and I'll change that to Year.
| | 03:37 | Unfortunately, you can't change the
Value field name, so I'll leave it as it is.
| | 03:42 | You probably won't create many Pivot
Tables by consolidating multiple data
| | 03:45 | ranges, but it's nice to know the
capability is available if you're working with
| | 03:49 | legacy workbooks that have data
laid out in cross-tabular formats.
| | Collapse this transcript |
| Updating and refreshing data sources| 00:00 | Companies generate new sales,
budgetary and operations data all the time,
| | 00:04 | so Pivot Table data
sources really remain static.
| | 00:07 | In this lesson, I will show you how to
manage your data connection to ensure your
| | 00:10 | Pivot Table contains the
most recent data available.
| | 00:13 | You can display your Pivot Table's
data connections by clicking any cell on
| | 00:16 | the Pivot Table and then on the PivotTable
toolbar clicking the PivotTable Wizard button.
| | 00:23 | In the PivotTable Wizard, click the
Back button and here in the Range field
| | 00:28 | you can see the cells from
which Excel is drawing its values.
| | 00:31 | Now let's see what happens if I edit a
value in the data source for this Pivot Table.
| | 00:35 | I will click Cancel. And the data
source is on Sheet2, so I will go there.
| | 00:41 | The first value in the list is for 2008,
Quarter 1, January sales for FirmA. And
| | 00:47 | I will change that value
to 10. And press Return.
| | 00:51 | If I go back to the Pivot Table on
Sheet1, the value is still 67, but on the
| | 00:57 | PivotTable toolbar, if I click the
Refresh button, Excel fills in the new
| | 01:01 | value, updates the Grand Total, and also
updates the Total for 2008 for FirmA and for FirmB.
| | 01:07 | But now let's suppose that you
add a new row to the data source.
| | 01:11 | What happens in that case?
| | 01:13 | On Sheet2. And add a row to my data list.
| | 01:17 | So let's say this will be the first
value for 2010, Quarter 1, January, FirmA.
| | 01:24 | And I will type in a value 47. And hit Return.
| | 01:31 | If I go back to the Pivot Table on
Sheet1 and click the Refresh data button on
| | 01:36 | the PivotTable toolbar, nothing
happens, and the reason nothing happened is
| | 01:40 | because the Excel did not
update the data range definition.
| | 01:43 | You will need to do that yourself.
| | 01:45 | So again, click the PivotTable Wizard
button on the PivotTable toolbar. Click
| | 01:50 | back to go to the second page of the
PivotTable Wizard. And then edit the range.
| | 01:56 | In this case, I added one row,
which means that the bottom cell in the
| | 02:00 | data source is now E50.
| | 02:02 | So I will erase 49. Make it 50.
| | 02:05 | That's the only change I want
to make, so I will click Finish.
| | 02:09 | And you can see that Excel added
the data for 2010, January, FirmA.
| | 02:14 | Whenever I view a Pivot Table, I always
click the Refresh button to include any
| | 02:18 | updates to the data source.
| | 02:19 | If you create Pivot Tables based on a
data list that might have more or fewer rows
| | 02:23 | than it did when you last examined your
Pivot Table, you should examine the data
| | 02:26 | source list to identify any new rows
and edit the data source in the PivotTable
| | 02:31 | Wizard to reflect the change.
| | Collapse this transcript |
| Managing PivotTable reports| 00:00 | Pivot Tables behave a bit differently
than other Excel workbook elements, so
| | 00:03 | you'll need to use a few slightly
different techniques to select, copy and
| | 00:07 | delete them within your workbook.
| | 00:08 | You can rename a Pivot Table by
clicking any cell on the Pivot Table, and then
| | 00:12 | on the PivotTable toolbar, clicking
Table Options and typing a new name for the
| | 00:17 | Pivot Table in the Name box.
| | 00:19 | This Pivot Table summarizes corporate
revenue so I will call it 'CorpRevenue.'
| | 00:24 | When you're done, click OK or
press Enter and you have changed your
| | 00:27 | Pivot Table's name.
| | 00:28 | If you want to move a Pivot Table to a
different location within a workbook, you
| | 00:32 | can do that by clicking any cell on the
Pivot Table, and then on the Pivot Table
| | 00:36 | toolbar, clicking the Pivot Table Wizard button.
| | 00:39 | The last page of the Pivot Table Wizard
displays the controls that you can use
| | 00:42 | to relocate your Pivot Table.
| | 00:44 | If you want to put it on an existing
sheet, you can click the RefEdit control at
| | 00:49 | the right side of this range box.
| | 00:52 | Select the destination.
| | 00:53 | In this case, I will select Sheet 3.
| | 00:55 | Click the cell at the top left
corner where you want it to end up.
| | 00:59 | Click the RefEdit control
to re-expand the dialog box.
| | 01:02 | Verify that the destination
is correct and click Finish.
| | 01:06 | I want to use Sheet 3 again for a later
operation, so I'll click Undo and just
| | 01:11 | get rid of the data on Sheet 3.
| | 01:14 | If you want to create a second Pivot
Table from the same data source, you can do
| | 01:17 | that by copying the Pivot
Table you've already created.
| | 01:21 | To do that, click any cell in the Pivot
Table and then on the PivotTable toolbar
| | 01:25 | click the PivotTable button>Select>Entire Table.
| | 01:29 | Then on the toolbar click Copy.
| | 01:32 | Click your destination.
| | 01:33 | I will use Sheet 3.
| | 01:35 | Cell A1 is still selected, so I'll press Paste.
| | 01:38 | And I have a second Pivot Table
based on my existing data source.
| | 01:42 | Once again, I will use Sheet 3 for
later operation, so I'll click Undo and have
| | 01:46 | Excel remove the Pivot Table.
| | 01:47 | I am going back to the Pivot Table on Sheet 1.
| | 01:51 | Press Escape to remove the selection.
| | 01:52 | What I'll demonstrate now is how to
copy a Pivot Table arrangement without
| | 01:56 | creating a new Pivot Table.
| | 01:58 | To do that, once again, you
select the entire Pivot Table.
| | 02:01 | PivotTable>Select>Entire Table. Click Copy.
| | 02:06 | Click the worksheet where
you want to paste the cells.
| | 02:08 | In this case, I will use Sheet 3 again.
| | 02:10 | And then on the Edit menu, click Paste Special.
| | 02:13 | And then in the Paste Special dialog box,
select the Values option. And click OK.
| | 02:19 | Excel pastes the values but
does not create a Pivot Table.
| | 02:23 | If you want to delete a Pivot Table,
you can select the Pivot Table.
| | 02:27 | Again, PivotTable>Select>Entire
Table and press the Delete key.
| | 02:31 | Excel does retain the
formatting, but the Pivot Table is gone.
| | 02:35 | With these techniques in mind, you
will be able to manage your Pivot Tables effectively.
| | Collapse this transcript |
|
|
2. Summarizing PivotTable DataAdding and removing subtotals and grand totals| 00:00 | Excel Pivot Tables display your data in
as much or as little detail as you prefer.
| | 00:04 | Pivoting and filtering the Pivot Table
changes how the individual data elements
| | 00:08 | are displayed, but you can also show or
hide the subtotals and grand total rows
| | 00:12 | and columns to summarize your data as desired.
| | 00:14 | Subtotals appear at the bottom of each group.
| | 00:17 | In this case, we have the year 2008.
| | 00:19 | Month is the lowest level of
organization in the Pivot Table, so we don't have
| | 00:24 | any subtotals for it.
| | 00:26 | At the bottom of 2008, we have a
subtotal for all the months for FirmA and all
| | 00:30 | of the months for FirmB.
| | 00:32 | To control subtotals for a field, you
click any cell on the field, in this
| | 00:35 | case 2008 for year, and then on the
PivotTable toolbar click the Field Settings button.
| | 00:42 | From here, you can calculate how
Excel calculates your subtotals.
| | 00:45 | For example, if you wanted to hide
them entirely, you could click None.
| | 00:49 | Click OK and they go away.
| | 00:53 | To make another change, click the
Field Settings button. And if you want to
| | 00:56 | change to another calculation
operation, you can click Custom.
| | 01:00 | The default operation is Sum, where
you find the arithmetic sum of all the
| | 01:03 | values within a group.
| | 01:05 | If you wanted to change it to say
Average, you could click Custom, click
| | 01:08 | Average and then click OK.
| | 01:11 | Now instead of the arithmetic sum,
Excel calculates the average value, based on
| | 01:15 | the inputs in this group and also in this group.
| | 01:18 | If you want to change it back, click the
Field Settings button, click Automatic,
| | 01:22 | click OK and there you have it.
| | 01:24 | You can also control your
Pivot Table's grand totals.
| | 01:27 | To do that, click any cell on the
Pivot Table and then on the PivotTable
| | 01:31 | toolbar, click the PivotTable
button and click Table Options.
| | 01:35 | In the PivotTable Options dialog box,
you can check or uncheck these two
| | 01:39 | boxes so that you can show or hide grand
totals for columns and or grand totals for rows.
| | 01:44 | Checking the boxes mean they are displayed.
| | 01:45 | Clearing them means they are not.
| | 01:47 | So if I close them both and
click OK, the grand totals go away.
| | 01:51 | If I want to bring them back,
just open the dialog box again.
| | 01:56 | Check the boxes and back they come.
| | 01:59 | You can also use other controls in the
Table Options dialog box to determine
| | 02:02 | whether Excel includes
hidden values in subtotals.
| | 02:05 | Usually the idea of filtering a Pivot
Table is to hide values you don't want to
| | 02:08 | include in your summaries, so I
usually leave that box unchecked.
| | 02:11 | The box I am talking about is in Table Options.
| | 02:15 | It's right here, just below the Grand
totals for columns and the Grand totals
| | 02:19 | for row options and the Autoformat table option.
| | 02:22 | If you do want your subtotals to
include hidden page items, check the box.
| | 02:26 | You usually don't, so
you can leave it unchecked.
| | 02:28 | I didn't make any changes I want
to keep, so I will click cancel.
| | 02:31 | Subtotals and grand totals provide
easy-to-read summaries of the values in your
| | 02:35 | Pivot Table's rows and columns.
| | 02:36 | If you find those extra rows and
columns are a distraction, or if you just want
| | 02:39 | to emphasize the values in the body of
the data area, you can hide them quite easily.
| | Collapse this transcript |
| Changing the data field summary operation| 00:00 | Most of the data lists you summarize
using Pivot Tables contain some sort of
| | 00:03 | financial or personnel data, such as
sales or hours worked, which means that
| | 00:07 | you'll usually want to find the
sum of the values in the list.
| | 00:10 | You are not limited to
adding values together though.
| | 00:12 | You can choose from several summary
calculations and also have the ability to
| | 00:15 | change the type of value shown in the data area.
| | 00:18 | For example, you can express individual
values as a percentage of the column or
| | 00:22 | row total or compare values
to those of the previous year.
| | 00:25 | To change the summary calculation used
in the Pivot Table, click any cell in the
| | 00:29 | PivotTable data area. And
then, on the PivotTable toolbar,
| | 00:32 | click the Field Settings button.
| | 00:34 | Then in the Summarize by list, click
the operation that you want to use.
| | 00:38 | So if I wanted to change the summary
operation to average, I click Average,
| | 00:43 | click OK and Excel now
displays the average. For 2008,
| | 00:47 | the monthly average would
have been 80.25. For 2009,
| | 00:52 | that number there, and so on.
| | 00:54 | You'll notice that the grand
total values also take on the average.
| | 00:56 | So 67 and 128 averages out to 97.5.
| | 00:59 | 69 and 131 averages out to 100 and so on.
| | 01:05 | I will show you one other
operation, in this case, Count.
| | 01:08 | The Count operation counts the
number of occurrences for each data point.
| | 01:11 | In other words, how many measurements
we had for January of 2008 for FirmA,
| | 01:15 | for 2009, same thing.
| | 01:17 | So if I click Count and click OK, I get one
measure for each for a grand total of two.
| | 01:23 | Again, it doesn't work that well in
this circumstance, but you should use
| | 01:26 | Count later on in case your Pivot Tables contain
data that doesn't communicate discrete elements.
| | 01:31 | So I will click Undo.
| | 01:33 | Another way to summarize data inside
your Pivot Table is to change how Excel
| | 01:37 | calculates the values, not only by
operation but the way it displays the value
| | 01:41 | in relation to other values in the Pivot Table.
| | 01:43 | I will give you an example.
| | 01:45 | Suppose that I wanted to display the
2009 values as a percentage difference from
| | 01:49 | the values from 2008.
| | 01:51 | To do that, I would click
any cell in the data area.
| | 01:53 | Click Field Settings
button and then click Options.
| | 01:57 | Then in the Show data as area, instead of
Normal I would display it as % Difference From.
| | 02:05 | I can then select the
base item from my comparison.
| | 02:08 | In this case, I want to compare 2009 to
the previous year, which is 2008, so I
| | 02:12 | will click Previous.
| | 02:14 | If I had more data, for example, if I
had a column for 2010, clicking previous
| | 02:18 | would compare that data to 2009.
| | 02:20 | If I were to select 2008, then the Pivot
Table would compare the data in 2009 to
| | 02:25 | 2008 and then it would also
compare the data in 2010 to 2008.
| | 02:30 | So make sure that you have your
basis for comparison set properly.
| | 02:33 | But in this case, I am comparing percentage
different from the previous year, so I can click OK.
| | 02:39 | 2008 disappears because there is no
earlier year to which to compare it and 2009
| | 02:45 | is displayed as a
percentage difference from 2008.
| | 02:48 | Just by way of example, I'll
show you another comparison.
| | 02:50 | Go back to the Field Options button.
| | 02:53 | Suppose I wanted to show
the data as a running total.
| | 02:56 | So in this case, I would have a running
total in. And let's see a running total by month.
| | 03:01 | When I click OK, Excel changes the Pivot Table.
| | 03:05 | And here's what's going on.
| | 03:06 | You'll notice that the values in
each column go up continuously.
| | 03:11 | The reason that happens is because
Excel adds the value for February 2008 for
| | 03:15 | FirmA to the value from
January of the same year.
| | 03:18 | It then does the same thing for
March, for April, for May and so on.
| | 03:22 | You'll notice that the subtotal row is blank.
| | 03:24 | That's because the value in
December for 2008 and 2009 is the subtotal.
| | 03:30 | You should take the time to experiment
with the summary operations and settings
| | 03:33 | available for use in the data area.
| | 03:35 | You will probably find one or more
operations, other than addition, that provide
| | 03:39 | meaningful information about your data.
| | Collapse this transcript |
| Summarizing more than one data field| 00:00 | When most users think of a Pivot Table,
they usually visualize it with a single
| | 00:04 | data set in the data area.
| | 00:06 | While that's certainly the most common
data area configuration, you can also
| | 00:09 | summarize more than one data field at a time.
| | 00:12 | In this worksheet, for example, I have
Company and Month in the row area and
| | 00:16 | Year in the column area.
| | 00:17 | There is a single data
field and that is Revenue.
| | 00:21 | If I want, I can add a second copy of
the Revenue data field to the data area.
| | 00:25 | To do that, I go to the PivotTable
toolbar and drag the Revenue field to the data
| | 00:30 | area. And Excel adds a second
version of the Revenue field.
| | 00:34 | It's titled Revenue2.
| | 00:36 | If I want to make any changes to that
second Revenue field, I can do so by
| | 00:39 | clicking any cell in that field and
then, on the PivotTable toolbar, clicking the
| | 00:44 | Field Settings button.
| | 00:46 | I can then click Options and use the
second data field as a means to compare the
| | 00:52 | value in the first data field to
other values in the Pivot Table.
| | 00:56 | So, for example, if I wanted to show the
difference between 2009 and 2008, I could
| | 01:01 | go into Show data as, click
Difference From and than click (previous).
| | 01:07 | When I click OK, Excel displays the
difference between 2008 and 2009 for each month.
| | 01:13 | In this case the difference between 67
and 128 is, in fact, 61. Difference between 69
| | 01:18 | and 131 is 62, and so on.
| | 01:21 | That just provides a visual
reference so that you can make informed
| | 01:24 | decisions about your data.
| | 01:25 | If you were to show the difference
as a percentage, which you can do by
| | 01:29 | clicking Field Settings, and using
% Difference From. Click OK and the
| | 01:35 | difference appears as a percentage.
| | 01:36 | Another way to use multiple data
fields would be to have a column in the
| | 01:39 | data source that contains the number
of sales and display it next to the
| | 01:42 | Revenue for each month.
| | 01:44 | Pivot Tables can get a bit crowded when
you display more than one data field in
| | 01:47 | the data area, but if you use the space
wisely you can gain a lot of insights by
| | 01:51 | viewing two or more data status simultaneously.
| | Collapse this transcript |
| Creating a calculated field| 00:00 | Pivot Tables enable you to analyze
your data efficiently, but some questions
| | 00:03 | can only be answered by performing calculations
on the data used to create your Pivot Table.
| | 00:08 | To perform that type of analysis, you
can create custom fields that summarize
| | 00:11 | Pivot Table data using a formula.
| | 00:13 | These calculations are called Calculated Fields.
| | 00:16 | Without Calculated Fields, you would
have to copy the data from your Pivot Table,
| | 00:19 | paste it onto another worksheet,
and create the formulas there. That's a pain.
| | 00:24 | Rather than copy your data outside the
Pivot Table, you can create a Calculated Field
| | 00:28 | within the Pivot Table.
| | 00:29 | To do that, you click any cell in the
Pivot Table, and then on the PivotTable
| | 00:32 | toolbar, click the Pivot Table button.
| | 00:35 | Point to Formulas, and
then click Calculated Field.
| | 00:39 | You can use the controls in the Insert
Calculated Field dialog box to create
| | 00:43 | your Calculated Field.
| | 00:44 | I will name the Calculated Field 'AveragePerSale.'
| | 00:50 | I will use two fields from my data source.
| | 00:52 | The first is Revenue,
| | 00:53 | the total amount of money earned, and
Sales, which counts the number of sales
| | 00:56 | used to earn that revenue.
| | 00:58 | So first I will edit the formulas so that
I Backspace over the zero and now in the
| | 01:02 | Fields list I will click Revenue. Click
Insert Field to add it to the formula.
| | 01:06 | Now I will type a Forward Slash,
which in Excel means division.
| | 01:10 | I can now add the number of Sales. Click
Insert Field. And there is my calculation.
| | 01:15 | Revenue divided by number
of Sales. And I can click OK.
| | 01:19 | Excel added my field to
my Pivot Table immediately.
| | 01:22 | So now I have two fields in the data area.
| | 01:24 | One of them gives me the total amount
of revenue earned for a given month and
| | 01:27 | the second gives me the
average revenue per sale.
| | 01:30 | It's important to note that the Calculated
Field does not appear in the source table.
| | 01:34 | It only exists in Excel's program memory.
| | 01:37 | The results in this
Calculated Field are hard to read.
| | 01:39 | You can get more information on formatting
Pivot Table fields in Chapter 4, Lesson 1,
| | 01:43 | 'Changing the Data Field Number Format.'
| | 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 and
the column area and the row area.
| | 00:07 | As an example, in this Pivot Table,
which has a column of data arranged by
| | 00:10 | Company, Year, and Month, you can hide the
details for each year or for each company.
| | 00:14 | For example, if I wanted to hide all of
the values for 2008 for FirmA and just
| | 00:19 | express it as a subtotal, I can double click
that header cell and get rid of the values.
| | 00:23 | They are still there.
| | 00:24 | They are just not displayed.
| | 00:25 | If I double-click the same
cell, the values come back.
| | 00:29 | In exactly in the same way, I can
double-click the FirmA cell and Excel
| | 00:34 | collapses all of the FirmA values
and just gives me a subtotal for FirmA
| | 00:38 | for Year 2008 and 2009.
| | 00:41 | Double-clicking brings them back.
| | 00:43 | In this Pivot Table, the Months are at the
lowest level, so you can't hide them individually.
| | 00:48 | As the Pivot Table stands now, you can
only hide the monthly results by hiding
| | 00:51 | every month for the entire year.
| | 00:53 | If you do want to show or hide groups of
months, you can do so by creating a group.
| | 00:56 | A group is a user-defined set of rows
that you can expand or collapse as desired.
| | 01:02 | To create a group, select one set of
Pivot Table rows that contains the values
| | 01:05 | you want to include in the group and then
click Data Group and Outline and click Group.
| | 01:10 | So I will select January,
February and March, as my example.
| | 01:14 | Then on the menu bar click
Data>Group and Outline>Group.
| | 01:19 | So Excel just created the group that I
wanted for January, February, and March.
| | 01:23 | And also note that it did the same
thing for every other occurrence of
| | 01:26 | January, February and March.
| | 01:28 | So, for example, I have it here in
2008 for FirmA and I also have it in 2009.
| | 01:33 | And I have the same further down the
Pivot Table for FirmB in 2008 and 2009.
| | 01:38 | Also note that Excel added a
second Month field to my Pivot Table.
| | 01:43 | I have Month, and I also have a Month2.
| | 01:46 | Excel uses Month2 to retain the
original values in the Month field while
| | 01:50 | creating the groups in the second Month field.
| | 01:54 | If I want to rename my groups, such as
First Quarter, I can type a new value in
| | 01:59 | the header cell. Just call it Q1.
Return. And Excel changes the name.
| | 02:05 | If I want to expand or
collapse the group, I can, as before,
| | 02:08 | double-click the header cell,
double-click to bring it back or on PivotTable
| | 02:13 | toolbar I can use the Hide
Detail and Show Detail buttons.
| | 02:17 | If I want to collapse a
group, I click Hide Detail.
| | 02:19 | If I want to bring it back,
I can click Show Detail.
| | 02:23 | If you are done using the group and
you want to get rid of it, you can click
| | 02:25 | a header cell and then, on the Data menu,
point to Group and Outline and click Ungroup.
| | 02:32 | Grouping Pivot Table cells enables
you to hide or display rows of data that
| | 02:36 | belong together without creating
a new field and new data source.
| | 02:39 | Be sure to change the name of the
group to something more descriptive than Group1.
| | 02:42 | And when you are done with your
analysis consider ungrouping the fields
| | 02:45 | just to make the Pivot Table's layout simpler.
| | 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:10 | If you want to display the data row that
provides the value displayed in a Pivot
| | 00:13 | Table cell, double-clicking that cell
causes Excel to drill down into the data
| | 00:18 | source and create a new worksheet that
contains a copy of the appropriate row.
| | 00:21 | For drill down to work, you must
ensure that the proper option is turned on.
| | 00:25 | To do that, click any cell on the Pivot
Table and then on the PivotTable toolbar
| | 00:30 | click Table Options.
| | 00:32 | In the Table Options dialog box ensure that
the Enable drilldown checkbox is selected.
| | 00:37 | If it is, click OK and you are ready to go.
| | 00:40 | Drilling down to the underlying
data source is simply a matter of
| | 00:42 | double-clicking the cell
that you want to display.
| | 00:44 | For example, if I wanted to display the
row providing the value for cell D5, I
| | 00:49 | could double-click the cell and
have Excel create a new worksheet that
| | 00:54 | contains only that row. So for year 2008,
Quarter number one, Month, Company, and Revenue.
| | 01:02 | Unfortunately, this drilldown
capability only works for one cell at a time.
| | 01:06 | That said, it is possible to drill
down into a data source even if the Pivot
| | 01:10 | Table draws its data from an external source.
| | 01:12 | You can do so because Excel draws
the row it displays from the program's
| | 01:15 | internal representation of the data
source, called the Pivot Cache, instead of the
| | 01:19 | source table itself.
| | 01:20 | After you have displayed the row
providing the value for Pivot Table cell, you
| | 01:23 | can click Edit>Delete Sheet
to get rid of the worksheet.
| | 01:27 | Confirm you want to get
rid of it, and it's gone.
| | 01:31 | You can also drill down into
Grand Total or Subtotal rows.
| | 01:35 | So, for example, if I wanted to
display all of the rows providing values
| | 01:38 | summarized in this cell, which finds
a subtotal for FirmB in 2008, I can
| | 01:43 | double-click it and Excel
provides those rows as well.
| | 01:48 | Once again, Edit>Delete Sheet, OK and it's gone.
| | 01:53 | Drilling down into the original data
source provides context for the data that
| | 01:56 | appears in your Pivot Tables.
| | 01:58 | Rather than finding the right rows
among hundreds or even thousands of rows in
| | 02:01 | the source table by hand, you can
display just the ones you want by
| | 02:04 | double-clicking the cell that
contains the values of interest.
| | Collapse this transcript |
| Using data in a formula| 00:00 | When you create a formula, it's usually
a good idea to draw the formula's values
| | 00:04 | from the original data source.
| | 00:05 | That way, if the source changes, your
formula's result will update the next time
| | 00:09 | Excel recalculates your workbook.
| | 00:11 | But what do you do if your Pivot
Table contains data drawn from an outside
| | 00:13 | source and you can't get
out the original tables?
| | 00:16 | In that case, you can refer to a
cell within the Pivot Table using a
| | 00:18 | GetPivotData formula.
| | 00:20 | I will work my first example on the
same worksheet as the Pivot Table so you
| | 00:23 | can follow along more easily, and then
I will create a more realistic example
| | 00:27 | on another worksheet.
| | 00:29 | A GetPivotData formula uses two arguments.
| | 00:32 | First I will type in the function
name and left parenthesis, and the two
| | 00:36 | arguments that the GetPivotData
function takes are Pivot_Table and the name.
| | 00:40 | Pivot Table refers to any
cell within the Pivot Table.
| | 00:43 | I always use the cell at the top left
corner of the Pivot Table, in this case
| | 00:47 | cell A3, because I know
it's not going to change.
| | 00:50 | Regardless of how I pivot and
rearrange the body of the Pivot Table, cell A3
| | 00:54 | will remain constant, and will always
be within the area of the Pivot Table.
| | 00:58 | So that's what I type: A3.
| | 01:02 | Name is a little more complicated, so
I will just walk you through it.
| | 01:06 | Name is a string, which you identify
by enclosing in double quotation marks.
| | 01:10 | The Name argument contains the values
in the row and column areas that Excel
| | 01:14 | uses to identify a particular
cell within the Pivot Table.
| | 01:17 | So say, for example, that I wanted to
find the value in C5, which has 2008,
| | 01:24 | January, and FirmA, as the
values that you used to hone in on it.
| | 01:29 | So, I type in 2008, a space - no
comma just a space - January, FirmA,
| | 01:37 | Double quotes. Close the parentheses. Return.
And there you have the value, 67 and 67.
| | 01:47 | You are not limited to creating
GetPivotData functions that point to cells at
| | 01:50 | the lowest level of
organization within a Pivot Table.
| | 01:53 | Say, for example, that I wanted to
find the total for 2008 in January for
| | 01:57 | both FirmA and FirmB.
| | 01:59 | To do that, I can edit my formula
to remove the reference to FirmA.
| | 02:03 | And I am left with 2008 January,
which corresponds to this row here, with
| | 02:09 | a Grand Total of 197.
| | 02:12 | When I hit Return, the same value
appears as the formula's result.
| | 02:15 | And just to conclude the example string,
if I wanted to find the total for 2008,
| | 02:21 | I can just create a function
that looks like that. Hit Return.
| | 02:25 | And I have the value of 1887, which is
the 2008 total, also found here in cell E17.
| | 02:32 | One nice thing about creating
GetPivotData functions is that you don't need to
| | 02:37 | have the fields in the same order in which
they appear in the body of the Pivot Table.
| | 02:41 | So, let's say, for example, that I
wanted to find the value for 2008, January,
| | 02:46 | FirmA, but I want to put
them in a different order.
| | 02:49 | I can do that, just so long as Excel is
able to identify which columns and which
| | 02:53 | rows point to the cell that I am interested in.
| | 02:56 | So for example, if I put in
"FirmA January 2008", hit Return, I get the same
| | 03:04 | result, 67. Just like it's supposed to be.
| | 03:07 | You can also use GetPivotData
functions to provide arguments for formulas.
| | 03:11 | As an example, let's look at the
summary worksheet that I have created
| | 03:14 | here called Dashboard.
| | 03:16 | On the summary Dashboard, I have created
functions that find the total sales for
| | 03:20 | FirmA in 2008, and the same for FirmB
in 2008, and FirmA and FirmB in 2009.
| | 03:28 | One thing I would like to point out, in
this formula, is that because the Pivot
| | 03:32 | Table that contains the data I am
using in this function comes from another
| | 03:35 | worksheet, I had to put Sheet1 at
the front of the cell identifier.
| | 03:40 | So I have Sheet1, which is the name
of the worksheet where the Pivot Table
| | 03:44 | resides, an exclamation mark
indicating that I have stopped typing in the
| | 03:48 | name of the worksheet.
| | 03:49 | I am about to type the name of the cell,
and then A3, which is the name of the cell.
| | 03:53 | And then the second argument gives me
FirmA 2008, which reflects the data that I
| | 03:57 | want to use in this cell.
| | 03:59 | Hit Escape to stop editing the function.
| | 04:01 | If I wanted to create a function that
used more than one Pivot Table data point,
| | 04:05 | I can do so by
separating the values with commas.
| | 04:07 | So let's say that I wanted to find the
average in 2009 of the sales for FirmA and FirmB.
| | 04:13 | To do that, I would type = to start the
formula, type in Average, left parenthesis.
| | 04:19 | And then as the first argument I will
type in GetPivotData, left parenthesis,
| | 04:26 | and then Pivot Table identifier:
Sheet1, exclamation mark, A3, the cell at
| | 04:32 | the top-left corner.
| | 04:33 | And then I am looking for FirmA 2009.
| | 04:40 | Close the parenthesis, but this is just for
the first value used in the average formula.
| | 04:44 | Type a comma, now I have GetPivotData,
left parenthesis, Sheet1, A3, FirmB
| | 04:56 | 2009, double quotes.
| | 04:59 | Close the parenthesis.
| | 05:00 | Close the parenthesis again, so that
now I've created an average formula
| | 05:03 | with two arguments. Hit Return.
| | 05:05 | And I get the correct answer of 898.
| | 05:08 | The GetPivotData function makes it easy to
summarize Pivot Table data in your worksheets.
| | 05:12 | If it's not practical to draw the data
from the original source, you can always
| | 05:15 | use the Pivot Table reference instead.
| | Collapse this transcript |
|
|
3. Sorting and Filtering PivotTable DataSorting data| 00:00 | When you display your data in a Pivot
Table, you might want to display all of
| | 00:03 | the rows in the Pivot Table but change
the order in which they are displayed.
| | 00:07 | In this Pivot Table, for example, I
have revenue for two companies, FirmA and
| | 00:10 | FirmB, broken out by year and by month.
| | 00:13 | At present, the Pivot Table is sorted
by Year and then by Month and in the
| | 00:17 | column areas we have FirmA and FirmB,
which is the alphabetical order for
| | 00:20 | those two company names.
| | 00:22 | The data in the body of the Pivot
Table occurs in the order mandated by the
| | 00:26 | Year, the Month and the Companies.
| | 00:28 | There is no internal order to it. But
let's say that you wanted to display the
| | 00:31 | highest monthly revenue values
within the body of the Pivot Table.
| | 00:35 | To do that, you can click any cell in
the column by which you want to sort.
| | 00:39 | In this case, I will make it FirmA
for 2008, and then on the toolbar click
| | 00:44 | either Sort in ascending order, which
puts the lowest values on top and the
| | 00:49 | highest values on the bottom, or in
descending order, which puts the highest
| | 00:52 | values on top and the
lowest values on the bottom.
| | 00:55 | I want to see the highest values on top,
so I will click in descending order and
| | 01:00 | Excel reorganizes the Pivot Table data.
| | 01:02 | Now notice that it only sorted for FirmA.
| | 01:05 | I have 128, 119, 108 and so on.
| | 01:08 | All the values in this column
for 2008 are in descending order.
| | 01:12 | The values for FirmB are not.
| | 01:14 | That's because this column, the column
that was clicked when I sorted the data,
| | 01:18 | controls the sorting operation.
| | 01:20 | One other interesting thing to
note is that the Month order:
| | 01:23 | March, December, September, and so on,
has been carried down to the 2009 group
| | 01:27 | where we have March,
December, and September as well.
| | 01:30 | It's just the nature of how Excel
2008 sorts data within a Pivot Table.
| | 01:35 | Let's suppose that I wanted to sort the
data in ascending order, which has the
| | 01:37 | lowest values on top of a column.
| | 01:40 | For this, I can click the top of the
FirmB column for 2008 and then on the
| | 01:45 | toolbar, click Sort in ascending order.
| | 01:48 | And Excel sorts the data as I asked.
| | 01:51 | If you want to undo a sorting operation, you
can just go up to the toolbar and click Undo.
| | 01:56 | If you wanted to return this Pivot
Table to its original order by month, you
| | 02:00 | need to create a custom list.
| | 02:01 | I'll teach you how to do that in the
next lesson, which is called Creating
| | 02:04 | a Custom Sort Order.
| | 02:06 | Sorting a Pivot Table moves the data
you want to highlight to the top of the
| | 02:09 | Pivot Table enabling you to concentrate
on the values you want to focus on and
| | 02:12 | make better decisions.
| | 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
| | 00:03 | methods to sort the values in
alphabetical or numerical order.
| | 00:07 | There will be times though when you
want to define a custom list of values and
| | 00:10 | sort a Pivot Table using those values.
| | 00:12 | For example, if your company
has stores in four regions:
| | 00:15 | North, South, East and West, and you
want the regions to show in that order
| | 00:19 | within a Pivot Table instead of an
alphabetical order, you can create a custom
| | 00:22 | list and use that list
to sort your field's values.
| | 00:25 | As an example, here is the Pivot Table
that contains sales broken up by region:
| | 00:30 | North, East, West and South,
except that the regions are sorted into
| | 00:34 | alphabetical order starting with
East and going on through West.
| | 00:37 | If you want to create a custom list
of values and sort using those values,
| | 00:40 | first you go to Excel>Preferences and
in the Excel Preferences dialog box,
| | 00:47 | click Custom lists.
| | 00:49 | You can use the Custom lists page of
the Excel Preferences dialog box to create
| | 00:52 | your new custom list.
| | 00:54 | To do that you start typing in the List
Entries box and I will type North. Hit Return.
| | 01:01 | South. Return.
| | 01:02 | East and West, which is my desired order.
| | 01:06 | If I already had a vertical group of
cells somewhere in the worksheet that
| | 01:10 | contained the values in the order that
I wanted for my list, I could import the
| | 01:13 | list from those cells.
| | 01:15 | To do that, I would use this
RefEdit control. I would click it.
| | 01:19 | Go out into there worksheet.
| | 01:20 | Select the cells that contain the values.
| | 01:22 | The address of those cells
would appear in this box.
| | 01:26 | And then I could click Import and those
values would appear here in the List Entries box.
| | 01:30 | But I already have the values I want,
so I can just click Add. And my new list
| | 01:35 | appears here in the Custom lists pane.
| | 01:36 | I am done, so I can click OK.
| | 01:39 | To sort the Pivot Table using the
custom list I just created, I can click
| | 01:42 | any cell in the regions field and then on the
Data menu, click Sort, and then click Options.
| | 01:51 | In the Sort Options dialog box, I
can select my First key sort order.
| | 01:55 | Right now the First key sort order is
set to Normal, which is alphabetical
| | 01:58 | because I have text values in my region column.
| | 02:01 | If I click this arrow, I can
select one of my custom lists.
| | 02:06 | The list I just created is
North, South, East and West.
| | 02:09 | So when I click that, I'll be able to use it.
| | 02:10 | I would also like to point out the
other existing custom lists that you can use.
| | 02:14 | You have abbreviations for weekdays, the
weekdays spelled out, abbreviations for
| | 02:19 | months and the months spelled out.
| | 02:21 | If you sort the months using either of
these two lists then you'll get the order
| | 02:25 | in which the months occur within the
year as opposed to an alphabetical sort.
| | 02:29 | But in this case, I want to use my new list,
North, South, East and West. Click that.
| | 02:33 | It appears in the Sort Order box.
| | 02:35 | Click OK and click OK again,
to sort using that list.
| | 02:40 | And Excel has now sorted my Pivot
Table data in the Region column in by
| | 02:43 | North, South, East, West.
| | 02:45 | Custom lists help you arrange your
Pivot Table data to emphasize the elements
| | 02:48 | you feel are most important, making
sorting a more powerful and useful tool
| | 02:51 | than ever before.
| | Collapse this transcript |
| Filtering a field by selection or by rule| 00:00 | You can use Pivot Tables to summarize
huge data collections, but many times you
| | 00:04 | will want to limit the data
displayed in a given category.
| | 00:07 | For example, if your company's
operations are divided into four regions, you
| | 00:10 | might want to display results
for just one of the regions.
| | 00:14 | You can limit the data displayed in
a Pivot Table by creating a filter.
| | 00:17 | There are two main ways you can
filter the contents of a Pivot Table field:
| | 00:21 | by selection, or by rule.
| | 00:24 | Filtering by selection means that you
display a list of values in the field and
| | 00:27 | select the values you want to display.
| | 00:29 | So, for example, let's say that I
wanted to display only those results for the
| | 00:33 | East and North regions.
| | 00:35 | To display those, I click any
cell in the Region column.
| | 00:38 | On thePivotTable toolbar,
click the Field Settings button.
| | 00:42 | And then in the Pivot Table Field dialog
box, I select the items that I want to hide.
| | 00:47 | In this case, I want to hide South
and West. And I selected South and West by
| | 00:52 | clicking South and then Command+Clicking West.
| | 00:55 | Those are the items that I want to hide.
| | 00:57 | Now I can click OK. And Excel
reconfigures the workbook showing results only
| | 01:02 | for East and for North.
| | 01:04 | I can remove the filter by clicking Undo.
| | 01:08 | Filtering by selection in Excel 2008 is
a little bit quirky and I'd like to show
| | 01:11 | you some things that can happen.
| | 01:13 | For example, here I am in Region.
| | 01:15 | Go up to the PivotTable toolbar
and click the Field Settings button.
| | 01:20 | If I hide East and North,
and click OK, they disappear.
| | 01:26 | If I bring back the dialog box, and
I Command+Click East first - that's
| | 01:30 | important - and then North, it looks
like all of the items should come back
| | 01:35 | into the worksheet.
| | 01:36 | When I click OK, only East comes back.
| | 01:40 | In other words, the last field
that you click will remain hidden.
| | 01:44 | So again, to undo the filter you go up
to the toolbar and click the Undo button
| | 01:48 | until all of your fields come back.
| | 01:50 | You can also filter data
within a Pivot Table by a rule.
| | 01:53 | In Excel 2008, you can create what are
called Top 10 filters to identify the top
| | 01:57 | or bottom values in a field.
| | 01:59 | To use a more meaningful filter as an example,
I am going to add Month to my Pivot Table.
| | 02:05 | So you'll get an idea of how
effective a Top 10 filter can be.
| | 02:08 | To create a Top 10 filter, click any cell
in the field by which you want to filter.
| | 02:12 | And note that you're clicking the label,
January, February, March as opposed to the values.
| | 02:18 | This is the field you're actually filtering by.
| | 02:20 | And then on the PivotTable toolbar,
click the Field Settings button.
| | 02:24 | And then click Advanced.
| | 02:26 | In the Advanced button, in the
AutoShow options area, you can select
| | 02:31 | an Automatic Filter.
| | 02:32 | You can then determine whether you want to
show the Top or Bottom values. I'll show the Top.
| | 02:37 | And rather than the Top 10, I will
edit that number to just show the Top 5.
| | 02:42 | The idea is that I want to show the top
5 months based on the value in the Sum
| | 02:48 | of Revenue field, which is here.
| | 02:50 | I have all those parameters in place.
| | 02:53 | And I'll click OK. And OK again to
close the PivotTable Field dialog box.
| | 02:58 | And Excel displays the top
values for each region, for each year.
| | 03:02 | So, for example, for the East region in 2008,
| | 03:06 | May, July, August, and September, and
December were the highest months. For North in 2008,
| | 03:11 | January, June, July, August and
November, and so on down the line.
| | 03:15 | Filtering by selection gives you
pinpoint control over the values that appear
| | 03:18 | in your Pivot Table.
| | 03:19 | You should use this type of filter
when you want to display or exclude a few
| | 03:23 | values from the display.
| | 03:24 | Filtering by rule, by contrast,
enables you to identify the top or bottom
| | 03:28 | values in the field.
| | Collapse this transcript |
| Filtering using report filter fields| 00:00 | Whenever you add a field to a Pivot
Table's column area, or row area, you change
| | 00:04 | 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:11 | Table using the values in a 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 filter the Pivot
Table using the Quarter field?
| | 00:23 | The answer is that you add the Quarter
field to the Page Field area, and create
| | 00:27 | the filters as normal.
| | 00:29 | In this Pivot Table, I have the data
arranged by Year, and then by Month.
| | 00:33 | I have the Quarter field available to
me, but I am not using it to change the
| | 00:36 | structure the Pivot Table.
| | 00:38 | If I want to filter the Pivot Table
using the values in Quarter, I can do so by
| | 00:42 | moving the Quarter field
to the Page Fields area.
| | 00:45 | I just added the Quarter field to the
Pivot Tables arrangement, but I did not
| | 00:49 | change the organization of the
data in the body of the Pivot Table.
| | 00:52 | Instead by adding the Quarter fields to
the Page Fields area, I can now create
| | 00:56 | filters using those values.
| | 00:58 | For example, if I were to
click the Filter arrow button here.
| | 01:03 | I can select either to show 1, 2, 3, or 4, which
is the number of a Quarter, or I can Show All.
| | 01:10 | I haven't created the filter
yet, so Show All is selected.
| | 01:13 | Let's say that I wanted to see the
values just for the third quarter.
| | 01:16 | To do that, I click in the number 3
and Excel filters the Pivot Table, so
| | 01:19 | now all I see are month 7, 8, and 9,
July, August, and September, which
| | 01:23 | reflects quarter number three.
| | 01:25 | To clear the filter, I click the
Filter arrow, and select Show All.
| | 01:30 | It's important to note that you can
only select one Page Field at a time.
| | 01:33 | There is no way to create a compound
filter using a field in the Page Field area.
| | 01:38 | One terrific aspect of using fields
in the Page Field area is that you can
| | 01:42 | create separate worksheets with Pivot
Tables based on the values in that field.
| | 01:46 | For example, if I wanted to create four
Pivot Tables, one for each of the four
| | 01:50 | values in quarter: 1, 2, 3 and 4.
| | 01:54 | To do that you go up to the PivotTable
toolbar, click the PivotTable button,
| | 01:58 | and click Show Report Filter Pages.
| | 02:01 | In the Show Pages dialog box, you verify
that the Quarter field is the one that
| | 02:05 | you want, and that it is
selected. And click OK.
| | 02:09 | And Excel creates four new worksheets.
| | 02:11 | Each one is a Pivot Table, and each one
has a value from the Quarter field, and
| | 02:15 | has a filter applied to reflect that value.
| | 02:18 | So for example, here on Sheet 1, we have
Quarter 1, on Sheet 2, Quarter 2, Sheet
| | 02:25 | 3, the same, and on Sheet 4, Quarter 4.
| | 02:28 | If there is an existing worksheet with
the same name as one of the field values
| | 02:32 | that Excel uses to create the new
worksheets, then Excel names the new sheet as
| | 02:37 | the value and follows it with the
parenthesis to and a close parenthesis.
| | 02:41 | So you will never have a duplicate worksheet
name, and also the operation will not fail.
| | 02:46 | Instead, Excel will just rename
the worksheet so that it can work.
| | 02:49 | Filtering Pivot Tables using the
fields in the Page Field area is a
| | 02:52 | powerful capability.
| | 02:54 | Not only can you limit the data that
appears in your Pivot Table without
| | 02:57 | changing its structure, you can create
separate worksheets for each value in the
| | 03:00 | Report Filter field.
| | Collapse this transcript |
|
|
4. Formatting and Printing PivotTable ReportsChanging 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 other
| | 00:08 | formatting doesn't really matter.
| | 00:10 | But when you add those values in the
subtotal or grand total cell, the lack of
| | 00:13 | 1000s separators makes the
values harder for humans to process.
| | 00:17 | You can make your data easier to read by
changing the data field's Number format.
| | 00:21 | To change the Number format of a Pivot
Table data field, click any cell in the
| | 00:24 | field and then on the Pivot Table toolbar,
| | 00:27 | click the Field Settings button.
| | 00:29 | In the Pivot Table Field dialog box,
verify that you have the proper field
| | 00:33 | selected and then click Number.
| | 00:35 | Now you can use the controls on the
number page of the Format Cells dialog box
| | 00:40 | to change the Number's format.
| | 00:42 | In this case, I'll go with Number,
which is a good generic format for any
| | 00:45 | sort of numeric data.
| | 00:47 | My values don't have any numbers to
the right of the decimal point, so I will
| | 00:50 | set the number of Decimal places to
zero, and because my grand totals and
| | 00:55 | subtotals can go up above a thousand,
I will have a 1000 Separator.
| | 01:01 | Negative numbers can just have a minus
sign and to stay black. That's fine.
| | 01:06 | I can click OK.
| | 01:07 | Click OK again, and Excel applies my formatting.
| | 01:10 | You can see here that my subtotal
for 2008 for the East region is now
| | 01:15 | formatted with a comma.
| | 01:17 | Makes it much easier to process.
| | 01:19 | You should always change the Pivot
Table data fields Number format if you have
| | 01:22 | any values including subtotals
and grand totals that go over one thousand.
| | 01:26 | In general, it's a bad idea to use
the currency or accounting formats
| | 01:29 | mainly because the currency symbols
takes up space within the cells, and
| | 01:32 | can be distracting when you're trying to
read the numbers in the body of the Pivot table.
| | 01:35 | If you're summarizing currency values,
use a Number format that displays two
| | 01:39 | places to the right of the decimal point.
| | Collapse this transcript |
| Applying formats| 00:00 | When you create a Pivot Table, Excel
applies some basic formatting so you can
| | 00:03 | distinguish the labels and the
organizational layers from the data in the body
| | 00:06 | of the Pivot Table.
| | 00:08 | Excel 2008 also comes with a
collection of built-in autoformats from which to
| | 00:12 | choose, so you can change your
Pivot Tables overall formatting if you want to.
| | 00:15 | Unlike in previous versions of Excel
for the Mac, it is possible to apply
| | 00:19 | formatting to a Pivot Table's cell and
have the formatting move with the data.
| | 00:22 | In earlier versions of the program,
Excel applied the formatting to the
| | 00:25 | worksheet cell that currently
displayed the value, and the formatting did not
| | 00:29 | move when you pivoted the Pivot Table.
| | 00:31 | As an example, if I were to change the
text color for this cell to red and then
| | 00:40 | pivot the Pivot Table, moving Region
below Year, you see that the data moved and
| | 00:47 | the formatting moved with it.
| | 00:48 | If you apply formatting to a Pivot Table
cell and it doesn't move with the data,
| | 00:52 | the option to preserve
formatting might not be turned-on.
| | 00:55 | You can check by clicking a Pivot
Table cell, clicking the Pivot Table
| | 00:58 | button, clicking Table Options, and ensuring
that the Preserve formatting option is checked.
| | 01:04 | If it is, you are fine.
| | 01:05 | If it's not, check it and click OK.
| | 01:09 | If you would like to change the
appearance of your entire Pivot Table, you can
| | 01:12 | apply an autoformat, which is a pre-made
style included with the program.
| | 01:16 | To apply an autoformat, you
click any cell on the Pivot Table.
| | 01:19 | And then on the Format menu, click AutoFormat.
| | 01:22 | And then select the autoformat
you want to apply.
| | 01:25 | As an example, I would
choose Classic 3, and click OK.
| | 01:29 | One important thing to note is
that Excel did not overwrite the
| | 01:32 | existing formatting.
| | 01:33 | The value in the cell was
formatted in red before, and it still is.
| | 01:37 | One other aspect of applying autoformats is
that you can select which elements to apply.
| | 01:41 | For example, I can go to Format>AutoFormat,
with the autoformat applied, and I
| | 01:47 | have Classic 3 still there.
| | 01:49 | If I want to select which aspects of the
AutoFormat to apply, I can click Options.
| | 01:55 | I can now select the Number, Font,
Alignment, Width and Height, Patterns, and Border.
| | 02:00 | In this case, I don't want to have any cell
fills, so I will clear Patterns. Click OK.
| | 02:06 | And now my cells do not have the
fill colors previously applied.
| | 02:09 | If I want to bring them back, I can
either go into the AutoFormat and select the
| | 02:14 | option again, or I can go
to Undo and undo my change.
| | 02:18 | Applying an autoformat does not
overwrite any formatting you applied manually,
| | 02:21 | so you have a great deal of control
over your Pivot Table's appearance.
| | Collapse this transcript |
| Highlighting 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
| | 00:12 | its relative value.
| | 00:13 | Formats that change the appearance of
a cell's contents by applying rules are
| | 00:17 | called 'conditional formats.'
| | 00:18 | Excel 2008 lets you apply conditional
formatting rules to Pivot Table cells, but
| | 00:23 | unfortunately the formatting disappears
when you pivot the Pivot Table.
| | 00:26 | Creating a conditional format
is a lot like creating a filter.
| | 00:29 | In both cases, you create rules to
indicate which cells you want to be
| | 00:33 | affected by your action.
| | 00:34 | To apply a conditional format to Excel
Pivot Table cells, you select the cells,
| | 00:40 | and then click Format>Conditional
Formatting, and Excel displays a dialog box
| | 00:46 | indicating that your conditional
formats will not be preserved.
| | 00:49 | Anytime that you refresh the Pivot
Table data or change this layout, all the
| | 00:53 | formatting will go away.
| | 00:54 | I am using this as an example to
show what you can do if you want to do
| | 00:58 | this sort of analysis.
| | 00:59 | So I will click OK, and go right on ahead.
| | 01:02 | You can use the controls in the
Conditional Formatting dialog box to define your
| | 01:06 | first conditional format.
| | 01:07 | You can have up to three
conditional formats per cell in Excel 2008.
| | 01:11 | In this case, I want to have any cell
that contains a value greater than 975 to
| | 01:16 | be formatted in green, any cell with a
value between 850 and 975 inclusive to be
| | 01:23 | formatted in yellow, and any cell with a
value below 850 to be formatted in red.
| | 01:28 | So to create the first conditional
format, that's based on the cell value, Cell
| | 01:33 | Value is greater than 975.
| | 01:38 | So there is my rule.
| | 01:39 | I can now define the
format that I want to apply.
| | 01:41 | Click the Format button.
| | 01:43 | Click Patterns, which has the
fill colors, and select green.
| | 01:47 | Click OK and I have created my first rule.
| | 01:50 | To create a second rule for the same
cell, I just click the Add button and go
| | 01:53 | through the same procedure.
| | 01:55 | In this case, I want to apply yellow to
any cell with the value between 850 and 975.
| | 02:02 | Format>Patterns is still selected,
and I click yellow and OK. Final rule,
| | 02:08 | click Add, and I am looking for any
value below, less than, 850 and I will
| | 02:15 | apply the format of red.
| | 02:20 | While I have the Conditional Formatting
dialog box open, I would like to show a
| | 02:23 | few other things that you can
do with conditional formats.
| | 02:26 | If you want to edit a conditional
format, you can just change the rule by
| | 02:30 | clicking a new comparison operation and
changing the operators. There can either be one,
| | 02:35 | in the case of greater than or
less than, or two in the case of between.
| | 02:40 | Then to edit the format, just click the
Format button and make any changes you like.
| | 02:45 | If you want to delete a conditional
formatting rule, you can click anywhere in
| | 02:48 | the rule and click Delete, but now that
I have created my conditional formatting
| | 02:53 | rules for these cells, I
will click OK to apply them.
| | 02:56 | And there they are.
| | 02:58 | One real world example of how to use
conditional formats with Pivot Table data
| | 03:01 | is to create a dashboard that
draws data from the Pivot Table using
| | 03:04 | GetPivotData formulas.
| | 03:06 | Then instead of applying the
conditional formatting directly to the Pivot
| | 03:09 | Table, you can apply the conditional
formatting rules to cells that draw data
| | 03:13 | from the Pivot Table.
| | 03:14 | So regardless of how you reorganize
your data within the Pivot Table, those
| | 03:17 | conditional formats and
summaries will remain in place.
| | 03:20 | Now just to show what will happen if I
pivot the Pivot Table, I will move Year
| | 03:25 | to here, and the
conditional formatting goes away.
| | 03:28 | Applying a simple rule based
conditional format can make it much easier to
| | 03:32 | interpret your Pivot Table data.
| | 03:33 | All you need to do is determine the
criteria that reflect the data you want
| | 03:37 | to highlight.
| | Collapse this transcript |
| Printing a PivotTable report| 00:00 | Printing a Pivot Table is just
like printing any other worksheet.
| | 00:03 | You can print the entire worksheet,
print just the Pivot Table, or divide the
| | 00:07 | Pivot Table's data into several
worksheets and print them separately.
| | 00:11 | The most straightforward way to print
a Pivot Table is to print the worksheet
| | 00:14 | that contains the Pivot Table.
| | 00:15 | To do that, display the worksheet.
| | 00:18 | Click File>print, and use the commands in
the print dialog box to print your worksheet.
| | 00:26 | If you want to print the Pivot Table by
itself without anything else that might
| | 00:29 | be present on the worksheet, you can
select the entire Pivot Table by going to
| | 00:33 | the PivotTable toolbar, pointing to
Select>Entire Table, and now on the File
| | 00:38 | menu click Print Area>Set Print Area.
| | 00:41 | And you have just defined the Pivot Table
itself as the print area, so whenever
| | 00:47 | you print, Excel will only print the Pivot
Table in those areas that you have defined.
| | 00:52 | If you change your Pivot Table's
organization, which will change its shape on
| | 00:55 | the worksheet, then you should update
the print area to reflect its new layout.
| | 00:59 | Finally, you can create a separate
worksheet for each value found in the field
| | 01:03 | represented in the page field area.
| | 01:05 | To do that, you place a field in the page
field area, and for this I will drop in Region.
| | 01:10 | Now to create a separate worksheet
for each of the regions, you can go to
| | 01:14 | PivotTable>Show Report Filter Pages.
| | 01:18 | Verify that Region is selected, and
click OK, and Excel creates a new worksheet
| | 01:25 | for each of the regions.
| | 01:27 | Now if you Shift+Click these worksheets, go to
File and print, you can print the active sheets.
| | 01:36 | Printing a Pivot Table seems like a
straightforward operation and it usually is.
| | 01:40 | That said, you do have a number of options when
it comes to printing your Pivot Table.
| | 01:44 | Experiment with the different ways you
can control how your Pivot Tables print,
| | 01:47 | so you can get exactly the output you want.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Thanks a lot for spending time with
Pivot Tables in Excel 2008 for the Mac.
| | 00:04 | I hope you get a lot out of the lessons
that you have learned, and that you can
| | 00:07 | use the techniques that I have covered
to analyze your data more effectively.
| | 00:11 | Best of luck!
| | Collapse this transcript |
|
|
