IntroductionWelcome| 00:00 | (music playing)
| | 00:06 | Hi! I am Curt Frye.
Welcome to Excel 2013:
| | 00:07 | Pivot Tables in Depth.
| | 00:09 | In this course, I'll show you how to
use PivotTables to gain valuable insights
| | 00:13 | from your organization's data.
| | 00:15 | I'll begin by showing you how to create
a PivotTable from data already in your
| | 00:19 | Excel workbook, then using that
knowledge as a base, I'll demonstrate how to
| | 00:23 | create PivotTables using
data from an external source.
| | 00:26 | I'll enhance your ability to analyze
your data by showing you how to reorganize,
| | 00:30 | or pivot, a PivotTable.
| | 00:31 | After you've organized your PivotTable's
data, you can sort and filter its
| | 00:36 | contents to develop useful
insights into your business.
| | 00:38 | I'll also show you how to create
conditional formats that highlight subsets of
| | 00:43 | your data, control your PivotTable
using macros, and summarize huge data sets
| | 00:48 | using the PowerPivot add-in.
| | 00:49 | PowerPivots capabilities have been
extended with the new Power View add-in.
| | 00:54 | In short, I'll show you how to summarize
and analyze data using one of Excel's
| | 00:59 | most powerful and overlooked features.
| | Collapse this transcript |
| What you should know before watching this course| 00:00 | Before I get started with PivotTables
in Excel 2013, I'd like to go over a few
| | 00:05 | things that you should know to
get the most out of this course.
| | 00:08 | The first is that you should be
familiar with Excel 2007 or later.
| | 00:12 | Microsoft introduced the ribbon user
interface in Excel 2007, and because Excel
| | 00:18 | 2013 uses that interface, it would help
if you've had some experience with it.
| | 00:23 | Many skills from working with Excel
tables carry over to PivotTables, so if
| | 00:27 | you've worked with them
before, that's a big help.
| | 00:29 | You should also be familiar with creating
formulas, sorting and filtering data,
| | 00:34 | and also creating and manipulating charts.
| | 00:36 | I'll cover all these topics throughout
the course, but if you had experience
| | 00:41 | with them in the context of regular
worksheets, you'll pick up the PivotTable
| | 00:44 | applications much more quickly.
| | 00:46 | And finally, I hope to demystify
PivotTables. They aren't hard; they're
| | 00:50 | just different.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you are a premium subscriber to the
Lynda.com Online Training Library, or if
| | 00:05 | you've purchased this course on DVD,
then you have access to the exercise files.
| | 00:10 | I have downloaded the exercise files
onto my system, and put them in a folder
| | 00:15 | on my Desktop called Exercise Files.
| | 00:17 | You can navigate within the archive by
opening the Exercise Files folder, and
| | 00:21 | then opening folders based on
the chapter that you're working in.
| | 00:26 | Each of the files is clearly named, and
there is at least one separate file for
| | 00:30 | each movie within a chapter.
| | 00:32 | If you don't have access to the
sample files, then you can create your own
| | 00:36 | assets, or just follow along.
| | Collapse this transcript |
|
|
1. Creating and Pivoting PivotTablesIntroducing PivotTables| 00:00 | I wanted to use the first movie of
this course to introduce PivotTables as a
| | 00:04 | concept, and to give you an
overview of what you can do with them.
| | 00:08 | I will use the Introducing sample file,
which you can find in the Chapter01
| | 00:12 | folder of your Exercise Files archive.
| | 00:14 | With that said, don't worry about
following along move by move. Instead what I
| | 00:19 | would like you do is watch this movie
just to get a general idea of what's
| | 00:23 | possible with PivotTables, and
what they let you do with your data.
| | 00:26 | A PivotTable has this basic structure.
| | 00:29 | You can see that there are row
labels; in this case, five different
| | 00:34 | departments for a company, and then
also column labels, which in this case
| | 00:38 | represent quarters.
| | 00:39 | So, I have Balance, Dance, Play, Sport,
and Work, and along the top, I have
| | 00:44 | Quarters 1 through 4, and you can see
that this organization is mirrored here in
| | 00:50 | the PivotTable Fields pane.
| | 00:52 | The PivotTable contains three fields;
Department, Quarter, and Sales, and you can
| | 00:56 | see that their arrangement -- Quarter for
the Columns, Department for the Rows,
| | 01:00 | and Sales data in the Values area -- represents
the organization of the PivotTable on the left.
| | 01:07 | If I want to change the organization
of the PivotTable, I can do so by moving
| | 01:11 | the fields around here in the
PivotTable Fields task pane.
| | 01:15 | So that it's easier for you to
follow what's going on in the body of the
| | 01:18 | PivotTable, I am going to click cell D5,
the one that contains the value of
| | 01:23 | 38,092, and give it a yellow fill color.
| | 01:27 | That way you can hopefully better
follow how that cell moves within the
| | 01:31 | PivotTable as I rearrange it.
| | 01:33 | So, the first thing I'll do is to
rearrange the PivotTable to put the Quarter
| | 01:37 | field in the Rows area below Department.
| | 01:41 | So, I'll drag the Quarter field header
down to Rows, and you can see that the
| | 01:46 | PivotTable rearranges,
| | 01:48 | so now I have each Department; Balance,
Dance, and so on, and the data for each
| | 01:54 | Quarter beneath that.
| | 01:55 | So, it's the same for Balance,
for Dance, for Play, and so on.
| | 01:59 | And you can see that the data that was
previously in cell D5 is now in cell B7.
| | 02:05 | So, that's quarter three results for
the Balance Department, but because I
| | 02:09 | applied the formatting to a cell
within the PivotTable, the formatting moved
| | 02:12 | along with it, and didn't
stay here in cell D5.
| | 02:16 | Now let's say that I want to
filter the data in the PivotTable.
| | 02:19 | There are number of ways to do that,
and again, I'll go into more detail later,
| | 02:23 | but if I want to filter the data, say,
by Department, I can go over to the
| | 02:27 | PivotTable Fields pane, hover over
Department, click the down arrow that appears,
| | 02:32 | and I display a filter interface
that's probably familiar to you from using
| | 02:37 | Excel, and filtering either
data lists or Excel tables.
| | 02:41 | So, let's say that I only want
to display values for Balance.
| | 02:45 | To do that, I can clear the Select All
checkbox, then check the Balance box, and click OK.
| | 02:50 | Doing so filters the PivotTable,
so only data for the Balance
| | 02:54 | Department appears.
| | 02:56 | I can also filter based on the values in
other fields, but that's a topic for another movie.
| | 03:01 | So, hopefully now you have a basic
idea of what's possible with PivotTables.
| | 03:06 | They allow you to reorganize and summarize
your data dynamically, so that you can
| | 03:10 | focus on exactly what you need
for the decisions you need to make.
| | Collapse this transcript |
| Formatting data for use in a PivotTable| 00:00 | If you want to summarize Excel data
using a PivotTable, you need to make sure
| | 00:04 | that data is formatted properly.
| | 00:06 | In this movie, I will
show you what you need to do.
| | 00:09 | I'll use the FormatData sample workbook,
which you can find in the Chapter01
| | 00:13 | folder of your Exercise Files archive.
| | 00:16 | The best way to format your data
for use in a PivotTable is as a list.
| | 00:21 | A list is a series of columns; in this
case Department, Quarter, and Sales, and
| | 00:26 | each row represents a particular fact.
| | 00:29 | So, for example, we know that for the
Balance Department, Quarter number 1, Sales
| | 00:33 | were $51,992, and so on for Quarter 2,
Quarter 3, Quarter 4, and so on, and then
| | 00:40 | for the Dance, Play,
Sport, and Work Departments.
| | 00:43 | And if I scroll down using my mouse
wheel, you can see the rest of the data.
| | 00:46 | Some important things to note about this
data list; the first is that there are no gaps.
| | 00:51 | As I scroll down through the list again,
you can see that there are no blank rows.
| | 00:55 | If for some reason I were to have a
blank row, so I'll just insert one here, and
| | 01:00 | I were to click cell B4, and create
the PivotTable, then the PivotTable data
| | 01:06 | would stop with what is
currently the data in row 5.
| | 01:10 | So, the first thing you should realize is
that you must have no gaps in your date list.
| | 01:14 | I'll just press Ctrl+Z
to undo the insertion.
| | 01:17 | The second thing you should watch out
for is that there's no extraneous data
| | 01:21 | around the list you want to
summarize as a PivotTable.
| | 01:24 | So, for example, scrolling down again,
you can see that I have no excess data in
| | 01:29 | the column D of the worksheet; there
is also nothing below it that I don't
| | 01:33 | want to be included.
| | 01:35 | So, that means that if I click any
cell within this data list, as I've done
| | 01:38 | here with cell A6, creating the
PivotTable means that it would include all
| | 01:42 | that data, and only that data.
| | 01:44 | So again, the basic idea is you want
to have no gaps, no blank rows in the
| | 01:49 | list, and you also want to have no
data around the data that you want to
| | 01:53 | summarize using a PivotTable.
| | 01:54 | The best way to format your
data is as an Excel table.
| | 01:58 | To do that you click any cell in the
data list, and then on the Home tab, click
| | 02:03 | Format as Table, and then pick a table
style, and I'll just click the first one
| | 02:08 | here. Verify that all your data
appears; A1 to C21, which I know is correct.
| | 02:13 | My table has headers, and click OK.
| | 02:16 | When you define a data list as an
Excel table, you can add rows to the
| | 02:20 | table, and have that data appear automatically
in the PivotTable the next time it refreshes.
| | 02:25 | So, if I were to go down, scrolling
again with my mouse wheel, and click cell
| | 02:30 | C21, and press the Tab key, Excel
adds a new row to my Excel table.
| | 02:36 | So, if I were to add another row of
data -- let's say, for example Shoes -- press
| | 02:41 | Tab, Quarter, it would be Quarter 1, and we
will give it $20,000 of revenue, and press Enter,
| | 02:47 | then that data would appear again in
the PivotTable the next time it refreshes.
| | 02:52 | So, to sum up, you should have your data
as a data list with column headers, and
| | 02:57 | no blank rows, but the best way to
format your data is as an Excel table.
| | Collapse this transcript |
| Creating a PivotTable| 00:00 | Once you have your Excel data arranged
in either a list, or even better, an Excel
| | 00:05 | table with columns of data, as we have
here, and rows, with each row representing
| | 00:12 | particular fact in the data list,
then you can create a PivotTable.
| | 00:17 | To demonstrate how to do that, I'll
use the Create sample file, which you can
| | 00:21 | find in the Chapter01 folder
of your Exercise Files archive.
| | 00:25 | So again, my goal is to create a
PivotTable from this Excel table.
| | 00:30 | To do that, I click any cell in the body
of the Excel table or the data list, and
| | 00:35 | then on the Insert tab, click PivotTable.
Doing so displays the Create
| | 00:40 | PivotTable dialog box.
| | 00:42 | I want to create the PivotTable using a
table or range, so I'll leave this radio
| | 00:48 | buttons selected, and I'll verify
that the proper table is selected.
| | 00:52 | I happen to know that my table is
named Table2, so that looks right.
| | 00:57 | Then down here, I can select whether to
put the PivotTable on a New Worksheet or
| | 01:01 | an Existing Worksheet.
| | 01:02 | I pretty much always a decide to put my
PivotTables on new worksheets; that way
| | 01:06 | I have plenty of room to spread out, and
there won't be any graphics, or any other
| | 01:11 | elements of a worksheet
impinging on the PivotTable itself.
| | 01:15 | Those are the only basic
choices that I need to make.
| | 01:18 | So, to create the PivotTable,
I'll click OK.
| | 01:21 | Doing so adds a new sheet to the workbook,
and you can see the PivotTable outline
| | 01:25 | that appears on the left
edge of the worksheet.
| | 01:28 | You'll also notice the PivotTable
Fields pane, which appears over on the right
| | 01:33 | side. You can use the controls in the
PivotTable Fields pane to create, and then
| | 01:38 | also to manipulate your PivotTable.
| | 01:40 | For this movie, I'll just show you
how to set up the PivotTable, and in later
| | 01:44 | movies, I'll show you
how to manipulate it.
| | 01:46 | There are two main parts of
the PivotTable Fields pane.
| | 01:49 | The first is the field list, and it
shows the fields that are available to you;
| | 01:53 | in this case, Department, Quarter, and
Sales, and those mirror the columns that
| | 01:58 | were in the data list or the Excel
table that I used to create the PivotTable.
| | 02:03 | And at the bottom of the task pane,
you have areas that you can use to
| | 02:08 | create your PivotTable.
| | 02:09 | So, you have the Columns area, which
creates column headers, the Rows area
| | 02:14 | for row headers, and the Values area, which
displays the data in the body of the PivotTable.
| | 02:19 | So, let's say that I want to put Departments
in the Columns area, Quarter in the
| | 02:26 | Rows area, and Sales in the Values area.
| | 02:30 | Doing so creates a PivotTable
that you can see on the left.
| | 02:33 | Those are the basics of
creating a PivotTable in Excel 2013.
| | Collapse this transcript |
| Creating a Recommended PivotTable| 00:00 | If you don't have a lot of experience
working with PivotTables, you might be
| | 00:04 | uncertain as to what
arrangement to start with.
| | 00:06 | It's all well and good to create the
PivotTable, but where do want to put your
| | 00:10 | headers, and how do you
want the data laid out?
| | 00:12 | Well, in Excel 2013, the Excel
programming team has added what are called
| | 00:16 | Recommended PivotTables.
| | 00:18 | You click inside of a data list or
Excel table, click Recommended PivotTables,
| | 00:22 | and Excel suggests some layouts for
your data, and in this movie, I'll show you
| | 00:26 | how to take advantage of that new feature.
| | 00:29 | I will use the Recommended sample file,
which you can find in the Chapter01
| | 00:33 | folder of your Exercise Files archive.
| | 00:36 | This workbook contains a single sheet,
and I have an Excel table with quarterly
| | 00:40 | sales data for a bunch of
different departments of a company.
| | 00:43 | To create a recommended PivotTable
from the data, I click any cell in the
| | 00:48 | list, and then on the Insert tab,
click Recommended PivotTables.
| | 00:52 | Doing so displays the
Recommended PivotTables dialog box.
| | 00:56 | I can scroll down the list of recommendations,
and the one that's selected is
| | 01:00 | previewed here in the right-hand pane.
| | 01:02 | You can see right off that some of
those suggestions don't make a lot of sense.
| | 01:07 | For example, Counting Sales by Department,
well, there are four quarters, so of
| | 01:11 | course count of sales will always be 4.
| | 01:13 | Same thing for Sum of Quarter by Department;
Quarters 1 through 4 add up to 10,
| | 01:18 | so that doesn't really
give you any good information.
| | 01:20 | But if you scroll down by dragging
the vertical scrollbar, you can see one
| | 01:25 | that does make sense, and that is the
first one with a label Sum of Sales by
| | 01:29 | Quarter and Department.
| | 01:31 | So, if I click that preview, I can see
it over here in the preview panel. So, I
| | 01:35 | have the Quarters for the row labels,
and the Departments for the column labels.
| | 01:40 | That looks like something that I would
like to create, so I'll click OK, and
| | 01:44 | doing so creates a
PivotTable with exactly that layout.
| | 01:47 | If you look in the PivotTable Fields pane,
you can see that Department, Quarter,
| | 01:52 | and Sales are in the Columns, Rows,
and Values areas respectively.
| | 01:57 | So, that's the basics of
creating a Recommended PivotTable.
| | 02:00 | It's very quick, and like I said,
it's new in Excel 2013, and quite useful.
| | Collapse this transcript |
| Pivoting a PivotTable| 00:00 | After you've created a PivotTable with
the fields arranged to give you the data
| | 00:05 | arrangement that you want,
| | 00:06 | you can modify the PivotTable to rearrange
your data dynamically. That process
| | 00:11 | is called pivoting, and in this movie, I
will show you how to pivot your data to
| | 00:15 | get exactly the result that you want.
| | 00:17 | I will use the Pivoting sample file,
which you can find in the Chapter01 folder
| | 00:22 | of your Exercise Files archive.
| | 00:23 | Let's take a quick look at the PivotTable
that's already in this workbook. It
| | 00:28 | counts the number of customers that were
added by a company in quarters 1, 2, 3,
| | 00:33 | and 4, for the calendar years
2012 and 2013.
| | 00:36 | But let's say that I want to rearrange
the data, for example, by having the year,
| | 00:41 | and then each quarter within
the year laid out vertically.
| | 00:44 | To do that, I can drag the Quarter field from
the Columns area, below Year in the Rows area.
| | 00:50 | Doing so changes the
arrangement of the PivotTable,
| | 00:53 | so now I still have the years 2012 and
2013, but now I've the quarters, instead
| | 00:59 | of being laid out horizontally as
column headers, they are also laid out
| | 01:04 | vertically under Rows.
| | 01:05 | Now let's say that I wanted to compare
quarters between 2012 and 2013; so in
| | 01:10 | other words, I want to have quarter 1
of 2012 adjacent to quarter 1 of 2013,
| | 01:16 | instead of separated into different years.
| | 01:18 | To do that, I can drag the Year field
header in the Rows area below Quarter, so
| | 01:24 | I'll just click, and drag it below Quarter,
and you can probably see a green line
| | 01:30 | that indicates where Year will end up
when I release the left mouse button.
| | 01:35 | So, I'll do that, and you can see that
the arrangement of the PivotTable changed.
| | 01:39 | Now I have quarter 1 of 2012 and 2013;
quarter 2, 3, and 4 follow the same arrangement.
| | 01:46 | I can also add fields that are available
in the original data to the PivotTable,
| | 01:50 | even though they're not currently displayed.
| | 01:52 | If you look again in the PivotTable
Fields pane, you'll see that Year, Quarter,
| | 01:57 | and Customers all have checkmarks in
their boxes, but Region is not checked;
| | 02:02 | it's also not highlighted in bold.
| | 02:05 | That means that the Region field, which
does exist in the original data -- I'll
| | 02:10 | click the Sheet1 sheet tab, and
you see that I have a Region column,
| | 02:13 | then I'll go back to Sheet2, so the
data is available, but it doesn't appear
| | 02:19 | inside of the PivotTable at the moment.
| | 02:21 | Let's say that I want to add
Region to the Columns area.
| | 02:24 | To do that, I just drag the Region field
header from the Fields area down to the
| | 02:29 | Columns area, and now I have my data
for North, South, East, and West, again,
| | 02:34 | broken down by Quarter, and by Year.
| | 02:37 | If I wanted, I could also drag the
Region field header below Year in the Rows
| | 02:42 | area, so then I'd see quarter 1 for 2012,
and then each of the regions, and then
| | 02:46 | quarter 1 for 2013, and so on. Or if I
wanted to, I could drag Year above Quarter
| | 02:52 | in the Rows area, and
rearrange the PivotTable that way.
| | 02:56 | So, once again, pivoting a PivotTable
lets you change the order and structure of
| | 03:01 | the headers within the PivotTable to
emphasize the aspects of the data that are
| | 03:05 | most important to you.
| | 03:06 | Don't be afraid to experiment, because
you can always press Ctrl+Z to go back,
| | 03:10 | and undo whatever change you just made.
| | Collapse this transcript |
| Configuring a PivotTable| 00:00 | Excel 2013 PivotTables come with a
number of built in tools that you can use
| | 00:04 | to manipulate them,
| | 00:06 | and in this movie, I'll show you how
to use some of them, including the Fields
| | 00:10 | pane, and also expand and contract
buttons within the body of the PivotTable.
| | 00:15 | I will use the Configuring sample
file, which you can find in the Chapter01
| | 00:20 | folder of your Exercise Files archive.
| | 00:22 | This PivotTable contains four fields
of data; you can see them here in the
| | 00:26 | PivotTable Fields task pane, and they
are Year, Quarter, Region, and Customers.
| | 00:31 | So, the PivotTable summarizes the
number of customers acquired by region for
| | 00:36 | quarters 1 through 4, for
the years 2012 and 2013.
| | 00:40 | You can see that data represented
here in the body of the PivotTable.
| | 00:44 | The PivotTable Fields pane
contains a number of controls.
| | 00:46 | For example, you can drag fields
between areas to pivot the PivotTable.
| | 00:51 | For example, if I wanted to put
Region above Quarter in the Columns area, I
| | 00:56 | could just drag that field header over,
and I would rearrange my PivotTable.
| | 01:00 | To undo that, I would just press
Ctrl+Z. If you want to get rid of the
| | 01:04 | PivotTable Fields pane, you can do so by clicking
the Close button here at the top right corner.
| | 01:10 | If you want to bring it back, make
sure that you have clicked any cell inside
| | 01:14 | the body of the PivotTable, and then on
the ribbon, click the Analyze contextual
| | 01:19 | tab, and then at the far right corner of
this tab, in the Show group, click Field
| | 01:24 | List; doing so brings it back.
| | 01:26 | And of course, clicking the Field List
button on this ribbon tab hides the Field
| | 01:30 | List, and clicking it
again brings it back.
| | 01:33 | You can also show and hide the expand
and contract, or show and hide buttons.
| | 01:38 | If you are not sure what they are, in
the body of the PivotTable, here next to
| | 01:42 | 2012, you can see that there is a
button that contains what looks like a minus
| | 01:46 | sign; that's actually a hide detail
button; the same thing here for 2013.
| | 01:52 | And the reason that they appear for
2012 and 2013 is that you have rows
| | 01:56 | of detail beneath it.
| | 01:58 | So you have, in this case, Regions;
East, North, and South, and West.
| | 02:02 | If I were to click the hide detail
button next to 2012, then Excel updates the
| | 02:08 | PivotTable to show only the total for
2012, and you'll also notice that the hide
| | 02:13 | detail button is now a show detail button,
it went from having minus sign to
| | 02:18 | having a plus sign.
| | 02:19 | So, if I want to show the detail, all I
need to do is click that button, and the
| | 02:23 | regional data comes back.
| | 02:25 | If for some reason you want to hide these
show and hide detail buttons, all you need
| | 02:29 | to do is once again go to the Analyze
tab, and in the Show group, click the +/-
| | 02:35 | Buttons control to hide them.
| | 02:38 | To bring them back, just
click that ribbon control again.
| | 02:41 | You can also show and hide field headers,
and as you have seen before, it's just
| | 02:45 | a matter of going to the Show group on
the Analyze contextual tab, clicking the
| | 02:49 | Field Headers button; doing so gets
rid of the column labels and row labels.
| | 02:54 | If you want to bring them back, just
click Field Headers, and they reappear.
| | 02:58 | So, those are some of the basic ways
that you can configure a PivotTable.
| | 03:02 | The main reason to get rid of the
PivotTable Fields pane is so that you have
| | 03:06 | more room to display your
PivotTable in the body of your workbook.
| | 03:10 | The other tools, specifically Field Headers,
and the show and hide detail buttons,
| | 03:14 | come down to a matter of aesthetics.
| | Collapse this transcript |
| Connecting to an external data source| 00:00 | If you work in a corporate environment,
then it's likely that you'll want to
| | 00:03 | create a PivotTable using data that
is not stored in your active workbook.
| | 00:08 | In this movie, I'll show you how to
connect to an external data source, and
| | 00:11 | create your PivotTable based on that data.
| | 00:14 | I'll start out with the Connect sample
file, which you can find in the Chapter01
| | 00:18 | folder of your Exercise Files archive.
| | 00:20 | To create your PivotTable click the
Insert tab on the ribbon, and then click
| | 00:24 | PivotTable. That displays the
Create PivotTable dialog box.
| | 00:28 | Now choose the Use an external data
source option, and then click the Choose
| | 00:34 | Connection button. Doing so displays
the Existing Connections dialog box.
| | 00:38 | Now click Browse for More; that
displays the Select Data Source dialog box.
| | 00:43 | Now you can navigate to the folder
that contains the external data workbook,
| | 00:47 | and that happens to be on my Desktop, in
my Exercise Files folder, in Chapter01,
| | 00:54 | and I'll double-click the ExternalData file,
and doing so displays the Select Table dialog box.
| | 01:00 | There is only one table in that file;
it's called ExternalData, so I'll leave it
| | 01:05 | selected, and the first row of the
data does contain column headers, so I'll
| | 01:09 | leave that box checked, and click OK.
| | 01:11 | Now, back in the Create PivotTable
dialog box, I can select where to create the
| | 01:15 | PivotTable; in this case, I'll leave it
on the Existing Worksheet, Sheet1, and it
| | 01:20 | will start at cell A1, which is
currently selected, and I'll click OK.
| | 01:25 | Doing so creates the PivotTable, and I
can add fields using the controls in the
| | 01:30 | PivotTable Fields pane.
| | 01:31 | So, for example, I could display each
Department in a column, put Month in Rows,
| | 01:37 | and put Sales in the Values area.
| | 01:40 | The techniques that I showed you for
connecting it to an Excel workbook are very
| | 01:44 | similar to the steps you would use
to connect to other data sources.
| | 01:47 | Play around with the Select Data
Source dialog box, and I'm sure you'll
| | 01:51 | find your way quickly.
| | Collapse this transcript |
| Consolidating data from multiple sources| 00:00 | Most of the time when you create a
PivotTable in Excel 2013, you'll use a data
| | 00:05 | list, or an Excel table.
| | 00:07 | There might be some older workbooks
that you have in your collection with data
| | 00:11 | arranged differently, but you'll
still want to create a PivotTable.
| | 00:14 | If the data is arranged
properly, then you can do that.
| | 00:17 | In this movie, I will use the Consolidating
sample file, which you can find in
| | 00:22 | the Chapter01 folder of
your Exercise Files archive.
| | 00:25 | The Consolidating workbook contains
three worksheets, and all those worksheets
| | 00:30 | are data collections that you can
use to combine into a PivotTable.
| | 00:34 | On the SupportCalls worksheet, you can
see that there is a rectangular region of
| | 00:39 | data; it starts here in cell
B3, and runs down the cell F7.
| | 00:43 | It's very important that your
data be in a perfect rectangle.
| | 00:47 | So, if I were to have Region in cell
B3, and Year in cell C2, the data values
| | 00:54 | would not be perfectly rectangular,
and therefore, the technique I'm going to
| | 00:58 | show you wouldn't work.
| | 00:59 | You also need to make sure that there are
no blank cells anywhere in your data set.
| | 01:04 | This data summarizes regional sales for
4 years; the years are along the top and
| | 01:10 | the row labels are along the side, and
the worksheets OrderCalls, and ReturnCalls
| | 01:16 | contain similar data,
just for different sets.
| | 01:19 | I'll switch back to the SupportCalls
worksheet, and now I can open the tool that
| | 01:23 | I need to combine these
three datasets into a PivotTable.
| | 01:27 | That is the PivotTable Wizard; it's no
longer available through the ribbon, but
| | 01:31 | you can open it using keyboard shortcuts,
| | 01:34 | and the key sequence you need to use is
to press Alt+D, and then press P. Doing
| | 01:40 | so opens the PivotTable
and PivotChart Wizard.
| | 01:43 | The data we want to analyze is in
multiple consolidation ranges, and those are
| | 01:48 | defined as ranges that have the same
shape, and the same labels; that's the
| | 01:53 | third option here, so I'll check the
Multiple consolidation ranges option.
| | 01:58 | I want to create a
PivotTable, so I can click Next.
| | 02:01 | On the next page of the Wizard, I can
choose whether to have Excel create a
| | 02:05 | single page field for me, or
whether I want to create page fields.
| | 02:09 | Page fields are fields that are used to
filter the contents of a PivotTable, and
| | 02:14 | for the purposes of this procedure,
select the I will create the page fields
| | 02:18 | option, then click Next.
| | 02:20 | Now you can identify the
ranges that you want to consolidate.
| | 02:24 | To do that, click the collapse dialog
button at the right side of the Range box,
| | 02:29 | and make sure that the insertion point
is flashing inside the Range box. Once it
| | 02:33 | is, click the collapse dialog
button, and select the first set of cells.
| | 02:38 | Those are on the SupportCalls
worksheet, so I'll select from B3 to F7.
| | 02:43 | Expand the dialog box,
and then click Add.
| | 02:47 | Doing so adds that range
to the All Ranges pane.
| | 02:50 | And now I can do the same
thing for the other two worksheets.
| | 02:53 | I'll click the collapse dialog box
button, click the OrderCalls sheet tab,
| | 02:58 | select cells B3 to F7, expand
the dialog box, and click Add.
| | 03:02 | Now I can do it for the ReturnCalls
worksheet; collapse the dialog box,
| | 03:06 | ReturnCalls, select B3 to F7,
expand the dialog box, and click Add.
| | 03:12 | It doesn't matter if the data collections
on the different worksheets are in
| | 03:17 | separate cells as long as the cell
ranges they are in are the same size, and
| | 03:22 | the exact same shape.
| | 03:23 | Now that I've identified my consolidation
ranges, I can specify how many
| | 03:28 | page fields I want.
| | 03:29 | In this case, I'll select 0. I
don't want to create any filter fields.
| | 03:32 | I'll click Next, and I can decide where
to put the new PivotTable. I'll put it on
| | 03:37 | the New worksheet, and now
that I'm done, I can click Finish.
| | 03:41 | Doing so creates a PivotTable with the
values that are all consolidated into a
| | 03:45 | PivotTable from my three sources.
| | 03:47 | If you look over in the PivotTable
Fields pane at the right edge of the
| | 03:51 | program window, you'll see that I have three
fields; they're called Row, Column, and Value.
| | 03:56 | Value is fine, but Row and
Column aren't terribly descriptive,
| | 04:00 | so I will change the name of those fields.
| | 04:03 | To do that, I will click a value in the
Row Labels area, and then on the Analyze
| | 04:09 | contextual tab of the ribbon, which is
already selected, I will edit the value in
| | 04:14 | the Active Field box.
| | 04:15 | It currently says Row, and clicking in
the box selects it; these are the regions,
| | 04:20 | so I'll type in Region, and press Enter.
| | 04:23 | Doing so changes the name of the
field in the PivotTable Fields pane.
| | 04:27 | I'll do the same thing for Column.
| | 04:29 | So, I'll click one of the Column Labels,
and again, on the Analyze tab, I will
| | 04:33 | edit the value in the Active Field box,
so it says Year. Type that, and press
| | 04:38 | Enter, and the value changes.
| | 04:40 | And finally, I'd like to show you a
way to get your data from your PivotTable
| | 04:45 | into a data list, or an Excel table.
| | 04:47 | Now, remember that you created this
PivotTable from three separate consolidation
| | 04:51 | ranges, and they look like this range
here on the SupportCalls worksheet.
| | 04:56 | Now click the Sheet1 sheet tab
to go back to the PivotTable.
| | 05:00 | You can create an Excel table based on
this data by using a drill down technique.
| | 05:06 | The first thing you need to do is
remove all field headers from the Rows and
| | 05:10 | Columns area, so that you're left with
just the Grand Total in the Value area,
| | 05:16 | so it should be a single cell.
| | 05:18 | Now, to create the Excel table, double-click
that cell; in this case, it's cell A4.
| | 05:23 | Doing so creates an Excel table that
contains a list of all of your data. So, you
| | 05:28 | have North for the year 2010, the
value; North 2011, with a value, and so on.
| | 05:34 | The techniques that I've shown you in
this movie let you combine data from
| | 05:38 | separate ranges, as long as they are
the same shape, into a PivotTable, and
| | 05:42 | then once you have created your
PivotTable, you can create an Excel table
| | 05:46 | based on that data.
| | 05:47 | The PivotTable is a very flexible
tool, and the Excel table is as well.
| | Collapse this transcript |
| Managing PivotTables| 00:00 | When you create a PivotTable, you can
rearrange the data to emphasize exactly
| | 00:05 | the elements that you're interested in.
| | 00:07 | In this movie, I will show
you how to manage PivotTables;
| | 00:10 | that includes tasks such as renaming
a PivotTable, selecting the entire
| | 00:14 | PivotTable, moving it, and deleting it.
| | 00:17 | As my sample file, I'll use the Managing
workbook, which you can find in the
| | 00:22 | Chapter01 folder of your
Exercise Files archive.
| | 00:25 | It's possible to use PivotTable in
formulas, and I show you how to do that
| | 00:30 | elsewhere in the course.
| | 00:31 | And every PivotTable has a name; that's
one way that you know which PivotTable
| | 00:35 | you're referring to when you
create a formula based on its data.
| | 00:38 | You can find a PivotTable's name by
clicking any cell in the PivotTable, and
| | 00:42 | then on the Analyze tab, you can look
over in the PivotTable group at the far
| | 00:47 | left, and look in the PivotTable Name box.
| | 00:51 | And in this case, the name of
this PivotTable is PivotTable3.
| | 00:55 | That's not very informative;
I mean, it could be anything.
| | 00:58 | What you can do is edit this value by
clicking in the box to select the entire
| | 01:02 | value, and then type in something that
describes the data in the PivotTable, and
| | 01:07 | in this case, I'll call it
Department, Dept, Quarter, and press Enter.
| | 01:15 | Now the PivotTable is named DeptQuarter,
so that I know, at least in my own head,
| | 01:20 | that the Row Labels are the departments,
and the Column Labels are the quarters.
| | 01:26 | Now let's say that I
want to select a PivotTable,
| | 01:28 | for example, if want to copy
it, or perhaps even delete it.
| | 01:31 | To select an entire PivotTable,
again, click any cell in the body of the
| | 01:34 | PivotTable, and again on the Analyze
tab, click the Select button, and then
| | 01:41 | click Entire PivotTable. Doing so
selects the entire PivotTable. Now you can
| | 01:45 | copy it, or delete it.
| | 01:47 | If you want to copy it, with the PivotTable
selected, just press Ctrl+C to copy,
| | 01:53 | and you can see that it
has the marquee outline.
| | 01:55 | Now you can past the PivotTable on
another worksheet. Say, for example, if I
| | 02:00 | click the PTPaste sheet tab here, and
display that worksheet, I can press Ctrl+V
| | 02:07 | to paste the PivotTable, and you can
see that my new PivotTable appears.
| | 02:11 | And with the PivotTable selected,
click the Analyze tab, and it's called
| | 02:15 | PivotTable1, and again, I
could rename it as before.
| | 02:19 | I want to emphasize that this is a
second PivotTable, so if I were to click back
| | 02:23 | on Sheet2, the original PivotTable is
still there, and it is independent of this
| | 02:28 | PivotTable on this worksheet.
| | 02:30 | Now let's say that want
to delete this PivotTable.
| | 02:33 | With the entire PivotTable selected --
and again, that procedure is to click any
| | 02:37 | cell within the PivotTable, click
Select, and click Entire PivotTable --
| | 02:41 | I can delete it by selecting it as I
just showed you, and pressing the delete key.
| | 02:45 | So, now the PivotTable is gone, and I
just have the one PivotTable left in
| | 02:49 | this workbook on Sheet2.
| | 02:51 | Now, let's say that I have a PivotTable,
and I want to reset it; in other words, I
| | 02:56 | want to remove all of its fields, any
filters, and so on. I just want to go back
| | 02:59 | to a blank worksheet,
with an empty PivotTable.
| | 03:03 | To do that, click any cell in the body
of a PivotTable, and on the Analyze tab,
| | 03:08 | click Clear, and click Clear All.
| | 03:11 | Doing so resets the PivotTable, and if
I click here, that refers you to here.
| | 03:17 | Doing so resets the PivotTable, and if
I want to bring it back, and recreate
| | 03:21 | it, I just click here on the graphic,
indicating where the PivotTable will go.
| | 03:25 | That brings back my PivotTable Fields pane,
and I can add any fields that I want back to it.
| | 03:31 | So, for example, I can put Department in
the Rows area, Order in the Columns area,
| | 03:36 | and Sales in the Values area.
| | 03:38 | So, those are the basics of managing a
PivotTable; showing you how to rename it,
| | 03:42 | how to select it, how to move to it, and
also, if you want to, how to delete it.
| | 03:47 | All those skills are very useful, and
you'll probably use them more than you
| | 03:50 | think when you're working
with PivotTables in Excel 2013.
| | Collapse this transcript |
|
|
2. Summarizing PivotTable DataManaging subtotals and grand totals| 00:00 | One extremely useful aspect of PivotTables
in Excel 2013 is the way that they
| | 00:05 | let you summarize your data.
| | 00:07 | In this movie, I will show you how to
work with subtotals and grand totals inside
| | 00:12 | the body of a PivotTable.
| | 00:13 | I'll use the Totals sample workbook,
which you can find in the Chapter02 folder of
| | 00:18 | your Exercise Files archive.
| | 00:20 | Let's take a quick look at
the contents of this PivotTable.
| | 00:24 | As you can see, there is regional customer
data; the number of customers acquired
| | 00:29 | for four regions: East, North, South,
and West in alphabetical order, and it's
| | 00:33 | also broken out by year, and by
quarter, for the years 2012 and 2013.
| | 00:38 | Most relevant is the fact that we have
subtotals for the year 2012 here for East,
| | 00:44 | and also for a grand total
for that year 2012.
| | 00:48 | In other words, if we look at the sum
of all customers acquired in these four
| | 00:52 | rows here, we can see that
the total is 7,872.
| | 00:57 | We have a similar arrangement
for the year 2013 at the bottom.
| | 01:01 | We can also see grand totals for each of
the regions; you can see that down here.
| | 01:06 | So, we have a grand total for customers
from the East, North, South, and West, and
| | 01:11 | of course, a grand total of all customers
acquired for the years 2012 and 2013.
| | 01:17 | When you create a PivotTable, this is
the default way that subtotals and grand
| | 01:21 | totals appear, but you can control
how they appear inside your PivotTable.
| | 01:25 | To do that, click the Design tab on
the ribbon, and you must have clicked a
| | 01:30 | cell inside of the PivotTable to have access
to the Analyze and Design tabs on the ribbon.
| | 01:35 | With that selection in place, you
can go over to the Layout group of the
| | 01:39 | Design tab, and you can use these
Subtotals and Grand Totals controls to
| | 01:44 | affect your PivotTable.
| | 01:45 | For example, let's change
how subtotals work.
| | 01:48 | To do that, I'll the click the Subtotals
button, and I have three selections:
| | 01:53 | don't show subtotals, show them at
the bottom of the group, or show them at
| | 01:56 | the top of the group.
| | 01:58 | As the PivotTables currently configured, we're
showing subtotals of the top of the group.
| | 02:02 | I personally prefer to
have subtotals at the bottom.
| | 02:05 | So, I'll make that choice now, and you
can see now that I have a year 2012, and
| | 02:10 | then my subtotal for 2012
is at the bottom.
| | 02:13 | This arrangement moves, as I said, the
subtotals to the bottom, and it also gives
| | 02:18 | you a blank line here, or at least an
empty line, instead of containing data.
| | 02:23 | It provides a little bit more space, and
I tend to read from top to bottom, so I
| | 02:28 | prefer to see my subtotals at the
bottom of a group, as opposed to the top.
| | 02:32 | It's an individual preference, so
just pick whatever works best for you.
| | 02:36 | But just to give you an idea of what a
PivotTable looks like if you turn off
| | 02:39 | subtotals entirely, I'll to click the Subtotals
button, and click Do Not Show Subtotals.
| | 02:44 | When you do, the subtotals go away, and
also notice that you still have this blank
| | 02:49 | line here at the top,
| | 02:51 | so you have a little bit of space
between your labels and the data.
| | 02:54 | You can also turn grand totals on or off.
| | 02:56 | To do that click the Grand Totals
button, again, in the Layout group, and you have
| | 03:01 | four different options.
| | 03:02 | Basically, you're turning grand totals
off or on for rows and columns, and there
| | 03:08 | are four combinations of how you can do
that for rows and columns, on and off, and
| | 03:12 | those options appear here
under Grand Totals.
| | 03:15 | So, let's say that I want to
turn all grand totals off.
| | 03:18 | To do that, I'll click Off for Rows
and Columns; just click back in the body
| | 03:22 | of the PivotTable to get my controls
back, and if I want to have grand totals
| | 03:27 | for both rows and columns, I'll click
Grand Totals, and click On for Rows and Columns.
| | 03:32 | So, we're back to seeing our
summary by quarter, and also by region.
| | 03:36 | But notice that we don't have our
subtotals for the years 2012 or 2013.
| | 03:41 | So, that means we don't have a
grand total for an individual year.
| | 03:46 | So, if I were turn subtotals back on, and
have them appear at the top of the group,
| | 03:50 | then we get the subtotals for each of
the regions, four a year, and we also get the
| | 03:54 | Grand Total for each year.
| | 03:56 | So, as with everything regarding a
PivotTable, take the time to configure it,
| | 04:00 | change it the way that you want,
and don't be afraid to experiment.
| | 04:03 | Keep working until you find
exactly the layout that works for you.
| | Collapse this transcript |
| Changing the data field summary operation| 00:00 | When you create a PivotTable in Excel 2013,
Excel finds the sum of all of the data.
| | 00:06 | The good news is that you're
not limited to that one operation.
| | 00:09 | You can find averages, running totals, and
a lot of other different types of summaries.
| | 00:14 | In this movie, I will show you how to
change both the display, and the summaries
| | 00:18 | that occur inside of your PivotTable.
| | 00:21 | I will use these Summary sample file,
which you can find in the Chapter02 folder
| | 00:25 | of your Exercise Files archive.
| | 00:27 | Let's take a quick look at the
data inside of the PivotTable.
| | 00:31 | The individual values which are here for
each of four regions, North, East, West,
| | 00:37 | and South, and it's the number of
customers that were acquired per quarter for
| | 00:40 | each of those regions, for quarter one
through four, and the years 2012 and 2013.
| | 00:46 | And you can see here in the Grand Total
column the number of customers for 2012,
| | 00:52 | and then again for each of the
quarters, and the same for 2013.
| | 00:55 | What I'd like to do is change how the
data is summarized inside of the PivotTable,
| | 01:00 | and then also how it's displayed.
| | 01:02 | If I want to change the summary operation,
all I need to do is click any data cell;
| | 01:07 | in other words, one that contains, in this
case, a numeric value in the body of a PivotTable.
| | 01:12 | So, I click the cell that contains the
number 399, and then from the shortcut menu
| | 01:17 | that appears, I'll point to Summarize
Values By, and then I can select any one of
| | 01:23 | several other options: Sum, Count, Average,
Maximum, Minimum, and also a Product.
| | 01:29 | In this case, I'll find the average value,
| | 01:31 | so I'll click that from the list, and you
can see that the PivotTable summary changes.
| | 01:35 | Now, note that the values in the
body of the PivotTable didn't change.
| | 01:39 | So, 399 is still 399; 156 is still 156.
| | 01:43 | That's not surprising,
because those are the base values.
| | 01:46 | What changed were the summaries.
| | 01:48 | So, you can see here for 2012, for the
East region, which is here, you can see
| | 01:53 | that the average number of customers
per quarter was 545, for North it was 572;
| | 01:59 | South, 426; West, 425.
| | 02:03 | You can also look at the average for
each quarter, or for the entire year, and you
| | 02:07 | can see those summaries here.
| | 02:09 | If you want to undo a change, then all
you need to do is either click the Undo
| | 02:13 | button on the quick access toolbar, or
do as I'll do right now; press Ctrl+Z. Now
| | 02:17 | let's say that you want to change
how the data is displayed inside of a
| | 02:21 | PivotTable, and I'm not
talking about number formatting;
| | 02:24 | that's a subject for another movie.
| | 02:26 | But let's say that I wanted to find
a running total, or I wanted to find a
| | 02:31 | percentage running total for each
individual value for a row or column.
| | 02:35 | To display data in that manner, I
right-click any data cell in the body of the
| | 02:40 | PivotTable, point to Show Values As,
and doing so displays a list of the ways
| | 02:46 | that I can summarize or display the data.
| | 02:49 | So, for example, I currently have no
calculation check, but let's say that I want
| | 02:53 | to display each quarter for each
region as a percentage of the overall total;
| | 02:59 | the grand total of all customers acquired.
| | 03:02 | To do that, I would just click % of
Grand Total, and when I do, the data inside of
| | 03:08 | my PivotTable changes.
| | 03:09 | So, I can see at a yearly level that I
acquired 51.27% of my new customers in
| | 03:15 | 2012, and 48.73% in the 2013, with the
data breaking down by quarter in the
| | 03:21 | body of the PivotTable.
| | 03:23 | Again, if I want to undo my change, I can
press Ctrl+Z, and go back to what I had before.
| | 03:29 | I'll show you couple of the other ways
that you can summarize your values in the
| | 03:33 | body of a PivotTable, just so you get
an idea of what's available to you.
| | 03:37 | So, I'll right-click a data cell, point
to Show Values As, and in this case, I'll
| | 03:42 | make it a percentage of a column total.
| | 03:45 | So, point to and click
% of Column Total.
| | 03:49 | When I do, you can see that the data is
now summarized based on values in the column.
| | 03:54 | So, I can see that for the East region
in 2012, I had 62.91% of my new customers
| | 04:00 | acquired, whereas in 2013, that year
only accounted for 37.09% of new customers
| | 04:08 | for the East region, and you can see
similar breakdowns for each of the other
| | 04:12 | three regions as well.
| | 04:13 | Now, one last thing; let's see how it looks
when we compare by row instead of by column.
| | 04:18 | So, I'll right-click a data cell;
Show Values As>% of Row Total.
| | 04:23 | When I do, you can see that my summary
has changed from being column based to
| | 04:28 | being row based, and you can tell that
because the grand total in the column
| | 04:32 | summarizing all the rows is 100%.
| | 04:34 | Now you can see that the East region
was 27.8%, North was 32.96, and so on, all
| | 04:42 | adding up to 100% of the
values in this row.
| | 04:45 | You can also display running
totals, as opposed to individual totals.
| | 04:49 | So, for example, if I right-click a data
cell, point to Show Values As, and then
| | 04:55 | click % Running Total In.
| | 04:57 | I want my Base Field to be quarter.
| | 04:59 | It can also be year or region, but in
this case, I'll make it quarter; click OK,
| | 05:04 | and you can see that my running totals
for 2012, quarter number 1 accounts for
| | 05:09 | 22.24% for the East region; 1 and 2
together account for 47.41%, quarters 1
| | 05:17 | through 3 80.83%, and so on.
| | 05:20 | That's how you change the summary
operation, and how your data is displayed
| | 05:24 | inside of a PivotTable.
| | 05:26 | You can make all of the changes very
quickly, and if you want to go back to what
| | 05:30 | you did before, click the Undo button,
or press Ctrl+Z, and you'll be there.
| | Collapse this transcript |
| Summarizing more than one data field| 00:00 | So far in this course, all of the PivotTables
I have shown you have had a single
| | 00:04 | data field in the values
area of the PivotTable.
| | 00:08 | In other words, the body of the
PivotTable has only contained a single type of
| | 00:12 | data, such as number of
customers acquired, sales, and so on.
| | 00:16 | In this movie, I will show you how to
add a second data field to the Values area
| | 00:21 | of your PivotTable.
| | 00:22 | I'll use the MoreThanOne sample
file, which you can find in the Chapter02
| | 00:26 | folder of your Exercise Files archive.
| | 00:28 | This PivotTable contains data about
new customers acquired for each of four
| | 00:33 | regions for the years 2012 and 2013, with
the yearly data broken down by quarter.
| | 00:39 | If you look over the PivotTable Fields
pane, you can see that I have a fifth
| | 00:43 | field, Sales, which is not currently
displayed in the body of the PivotTable.
| | 00:47 | Now, Sales data takes on
the same role as customers,
| | 00:51 | so I could pull out the Customers
field, and put Sales in in its place,
| | 00:56 | but instead of what I'd like to do is display
both of them in the body of the PivotTable.
| | 01:00 | To do that, all I need to do is drag
the Sales field from the choose fields to
| | 01:06 | add to report area, and put it in the Values
field here in the bottom part of the task pane.
| | 01:13 | So, I have it below the Customers
field right now.
| | 01:16 | I'll release the left mouse button, and
you can see that the PivotTable expands
| | 01:20 | to include the new data.
| | 01:21 | So, I have Sum of Customers, and Sum
of Sales for the East region, again,
| | 01:25 | broken down by quarter.
| | 01:26 | Then I have it for North, South, and
then West is over to the right, and again,
| | 01:32 | the data is broken down by
quarter, and by year for 2012 and 2013.
| | 01:37 | If I want, I can change the
order of those two fields.
| | 01:40 | So, I could drag Sales above Customers,
and doing so would rearrange the data
| | 01:45 | inside the body of the PivotTable.
| | 01:47 | So, that's all there is to it.
| | 01:49 | If you find that your PivotTable
works well summarizing two data fields at
| | 01:52 | once, then simply add a second
numerical field to the Values area, and work
| | 01:58 | with your data normally.
| | Collapse this transcript |
| Creating a calculated field| 00:01 | PivotTables let you analyze the
data in your original data source.
| | 00:04 | They also let you add calculated fields that
can create calculations based on that data.
| | 00:10 | In this movie, I will show you how to
create a calculated field using the
| | 00:14 | Calculated sample file, which you can
find in the Chapter02 folder of your
| | 00:18 | Exercise Files archive.
| | 00:20 | This PivotTable has four fields.
| | 00:22 | They are Department, Quarter, Revenue,
and Sales, and currently Department,
| | 00:27 | Quarter, and Revenue are displayed
within the body of the PivotTable.
| | 00:30 | In this case, the PivotTable is based on
data in the same workbook, but for the
| | 00:34 | moment, let's assume that I don't
have access to the original data;
| | 00:39 | say it's stored in an external source,
or it's a file that I'm not allowed to
| | 00:43 | change for some reason.
| | 00:44 | If I want to perform a separate calculation
on the data, such as by combining
| | 00:48 | values into fields produce another value,
then I can do so by adding a calculated field.
| | 00:54 | To do that, I go up to the
Analyze contextual tab, and then in the
| | 00:59 | Calculations group, click the Fields,
Items, & Sets button, and from list that
| | 01:05 | appears click Calculated Field.
| | 01:07 | Doing so displays the Insert
Calculated Field dialog box.
| | 01:10 | Now I can type a name for the field.
| | 01:12 | I'll call it average sale, and then I
can click in the Formula box, and Backspace
| | 01:19 | over the 0 to start creating my formula.
| | 01:22 | In this case, I want to divide my
total revenue by the number of sales.
| | 01:26 | To add those fields to my formula, I
click the first field, in this case, Revenue
| | 01:31 | and then click the Insert Field button.
| | 01:33 | Then I'll type a forward slash
for divide, and then I want to divide by
| | 01:39 | Sales. Click Insert Field again.
| | 01:41 | So, my formula will divide my total
Revenue by the number of Sales. I'll click
| | 01:45 | Add to add the calculated field, and you
can see that average sale now appears in
| | 01:50 | the field list, and I'll click OK.
| | 01:53 | When you click OK and create your
calculated field, Excel automatically adds it
| | 01:57 | to the body of the PivotTable.
| | 01:59 | So, let me drag the Sum of Revenue field
out of the Values area, so we can focus
| | 02:04 | on just the calculated
field that I just created.
| | 02:07 | When I do, you can see that the data
summary shows the average sales for each
| | 02:12 | department in each of the
four quarters that are covered.
| | 02:16 | Most of the data is in the $30-$50 range, but
you'll notice that there are some large values.
| | 02:21 | For example, in the Play and Sport
departments in the first quarter, the average
| | 02:25 | sale is quite low, whereas for
Work, the average sale is quite high.
| | 02:29 | It's that type of insight that you
can get from creating calculated fields.
| | 02:33 | One thing I should point out is that
the calculated field that I just created
| | 02:38 | only exists in Excel's memory.
| | 02:41 | It doesn't exist in the original data source.
| | 02:43 | If I click Sheet1, which contains the
original data source, you can see that
| | 02:48 | there is no average sale
column. Go back to Sheet2.
| | 02:51 | However, Excel does store the new field
that I just created inside of the pivot
| | 02:56 | cache, which is the internal
representation of the PivotTable.
| | 03:00 | The ability to create calculated fields
means that you can perform all sorts of
| | 03:04 | terrific calculations, and gain
insight into your business data.
| | Collapse this transcript |
| Grouping PivotTable fields| 00:00 | When you create a PivotTable that
contains multiple fields in the Rows area,
| | 00:05 | you're able to show or hide details
within the body of your PivotTable.
| | 00:09 | In this movie, I'll show you how to
extend that capability using grouping.
| | 00:13 | As my sample file, I'll use of the
Grouping workbook, which you can find in the
| | 00:17 | Chapter02 folder of your
Exercise Files archive.
| | 00:20 | So, like I said, this PivotTable contains
data that's arranged by Month, and then
| | 00:26 | by Week for the row area, then by
Department for the column area, and it contains
| | 00:32 | sales data in the body of the PivotTable.
| | 00:35 | Because I have a two level organization --
month and week -- in the row area, I can
| | 00:40 | click this hide detail button beside
month, and when I do, Excel hides the
| | 00:46 | detail underneath January.
| | 00:48 | So, instead of having to the weekly data,
I just see the subtotal for January.
| | 00:53 | I could do the same thing for February,
and also for March. I'll click the
| | 00:58 | show detail button now for those three
months to bring back the data. But now
| | 01:03 | let's say that I want to hide
detail inside of a PivotTable.
| | 01:07 | For example, let's say that I'm only
really interested in weeks 1 and 2 for
| | 01:12 | January, and I want to hide the data for
weeks 3 through 4. And note that
| | 01:17 | that data is displayed in
spreadsheet rows 8, 9, and 10.
| | 01:22 | To add hide detail and show detail
buttons to those rows, all I need to do is
| | 01:27 | select rows 8 through 10, and then
on the Data tab, in the Outline group,
| | 01:32 | click the Group button.
| | 01:34 | Doing so adds a grouping level to the
worksheet, and you can see to the left of
| | 01:40 | rows 8, 9, and 10 I have an
outline bar, and a hide detail button.
| | 01:46 | If I click that button, then
the rows 8, 9, and 10 are hidden.
| | 01:50 | They're still in the worksheet;
| | 01:51 | they're just not currently displayed.
| | 01:53 | If I want to bring them back, I can click
the show detail button, and they are there.
| | 01:58 | Now, notice that the Grand
Total for January is 550,258.
| | 02:03 | If I hide the detail for rows 8 through
10, the Grand Total doesn't change.
| | 02:08 | So, even though you're just seeing the
data for weeks 1 and 2, the summary
| | 02:11 | operation still encompasses all of the
data that's contained in the PivotTable,
| | 02:16 | even if it's not displayed.
| | 02:17 | If you want to remove the grouping
level from those rows, then you just select
| | 02:21 | the row headers again, and on the
Data tab, in the Outline group, click the
| | 02:26 | Ungroup button, and the
grouping level is removed.
| | 02:29 | That is how you get more control over
which rows are displayed within a PivotTable.
| | 02:34 | You can use grouping to change which
rows are displayed, without changing
| | 02:38 | your summaries.
| | Collapse this transcript |
| Using PivotTable data in a formula| 00:00 | PivotTables are extremely flexible
and powerful tools inside of Excel.
| | 00:05 | In fact, many times you'll probably find
a summary value or calculation inside of
| | 00:09 | a PivotTable that you'd
like to use in a formula.
| | 00:12 | In this movie, I will show you how to use
a PivotTable values inside of a formula
| | 00:17 | using the GetPivotData function.
| | 00:20 | I'll use the Formula sample file, which
you can find in the Chapter02 folder of
| | 00:25 | your Exercise Files archive.
| | 00:26 | So, again, my goal is to create a
formula that uses a value from a PivotTable,
| | 00:32 | and it's a values that I'll be able to use
regardless of how I rearranged my PivotTable.
| | 00:37 | In this case, what I'd like to do is
display the grand total of all customers
| | 00:43 | that have been acquired.
| | 00:44 | So, my PivotTable data shows customer
acquisition figures by region, and by
| | 00:49 | quarter for the years 2012 and 2013,
and in cell G1, I want to have the formula
| | 00:55 | that displays the grand total.
| | 00:57 | To do that, I start entering a formula
the way that I would enter any formula: by
| | 01:02 | typing equals sign, and then all I
need to do is click the cell that contains
| | 01:07 | the value that I want to use.
| | 01:08 | That cell is currently cell F15,
| | 01:11 | so I'll click it, and you can
see that when I do, Excel inserts a
| | 01:16 | GetPivotData function.
| | 01:18 | It has two arguments, in this case.
| | 01:20 | The first one is Customers, in quotes, and
that is the name of the PivotTable, then a
| | 01:25 | comma, and then A3.
| | 01:27 | So, A3 refers to the cell at the top
left corner of the PivotTable as it's
| | 01:34 | currently displayed, and you
can see that cell A3 is outlined.
| | 01:38 | So, now what I need to do is press
Enter, and the value 15,355 appears in that
| | 01:44 | cell as a result of the formula that I
entered, which I'll display on the formula
| | 01:49 | bar by clicking cell G1 again.
| | 01:52 | One thing I should point out is that
the GetPivotData formula that I created
| | 01:56 | relies on the value currently
in cell F15: the grand total.
| | 02:00 | Now, I can rearrange the PivotTable, for
example, by removing Quarter and putting
| | 02:07 | up in the field area.
| | 02:08 | You can see that even though the grand
total is now in a new cell, its value
| | 02:12 | still appears in cell G1.
| | 02:14 | I'll press Ctrl+Z to undo the
change I made to the PivotTable.
| | 02:18 | But let me show you what happens if I were
to hide grand totals for this PivotTable.
| | 02:23 | To do that, I would like the Design
contextual tab on the ribbon, and then click
| | 02:28 | the Grand Totals button, and
click Off for Rows and Columns.
| | 02:33 | Doing so hides the grand total.
| | 02:34 | So, the cell that I was relying on for
this formula no longer appears inside of
| | 02:39 | the PivotTable, so that means that
the formula returns a reference error.
| | 02:43 | In other words, it can't find the
cell that contains the data to which the
| | 02:47 | formula is referring.
| | 02:48 | If I were to bring back the grand
totals, then the value is redisplayed inside
| | 02:52 | the PivotTable, and the formula
returns the value as expected. But there's
| | 02:56 | nothing special about the
grand total of a PivotTable.
| | 02:59 | For example, if I wanted to display the
2012 Quarter 2 value for the West region,
| | 03:05 | all I would need to do is create a
new formula referencing that cell.
| | 03:09 | So, clicking on cell G1, type equals,
and then quarter 2 for 2012, click there,
| | 03:16 | and you can see that I now
have a much fuller reference.
| | 03:19 | Again, I have the name of the PivotTable,
the cell, and now we're referring to the
| | 03:24 | year 2012, quarter 2, and the West region.
| | 03:28 | So, in other words, the GetPivotData
function has all of the arguments that it
| | 03:32 | needs to identify the specific cell
that you've clicked, and are adding to your
| | 03:37 | formula, and it's great that Excel does
all that work for you, because it would
| | 03:41 | be a pain to try to get everything
in the proper order, and get all of the
| | 03:45 | references on your own.
| | 03:47 | Just to complete the demonstration, when
I press the Enter key, you will see that
| | 03:51 | the data for quarter 2 of 2012 for
the West region appears in cell G1 as a
| | 03:57 | result of the formula.
| | Collapse this transcript |
| Drilling down to the underlying data| 00:00 | PivotTables are extremely powerful tools.
| | 00:02 | They let you summarize the datasets
that contain tens of thousands, or even
| | 00:06 | hundreds of thousands of rows of data.
| | 00:09 | The problem is that you might not be
able to see all of the detail that you want
| | 00:13 | within the body of the PivotTable.
| | 00:15 | In this movie, I will show you how to
drill down to display the rows inside of
| | 00:19 | the original data source that provide a
value for a cell inside of a PivotTable.
| | 00:23 | I will use the DrillDown sample file,
which you can find in the Chapter02 folder
| | 00:29 | of your Exercise Files archive.
| | 00:31 | This data contains quarterly sales
for five different departments of
| | 00:35 | a particular company.
| | 00:36 | Let's say that I want to see the row
from the data source that provides the
| | 00:39 | value for the Play
department for quarter number 3.
| | 00:43 | To do that, all I need to do is double-click
the cell that contains the data for
| | 00:48 | which I want to see the details.
| | 00:49 | I'll double-click cell D7, and when I
do, Excel creates a new worksheet inside
| | 00:56 | of my workbook, and I'll go ahead and
release the selection, and zoom in, so that
| | 01:02 | you can see the data.
| | 01:03 | It's displayed in an Excel table, and
you can see that there's only a single row,
| | 01:09 | and that is for the Play department,
quarter number 3, and Sales.
| | 01:13 | That's the table row that
provides data for that PivotTable cell.
| | 01:17 | Now I'll just go ahead and delete this
worksheet by right-clicking the sheet tab,
| | 01:21 | and clicking Delete, and click
Delete again to confirm the operation.
| | 01:26 | I can do the same thing for
a grand total, or subtotal.
| | 01:30 | So, for example, let's say that I
wanted to see all the rows that contribute
| | 01:34 | to the value, the grand total, for the
Dance department for quarters 1 through 4.
| | 01:40 | To do that, I'll double-click cell F6, and
when I do, zooming in again, Excel creates
| | 01:45 | a new worksheet that contains all of
the rows from the original data source for
| | 01:50 | the Dance department for
quarters 1, 2, 3, and 4.
| | 01:53 | Once again, if you're summarizing a
large dataset inside of your PivotTable, and
| | 01:57 | you want to see the original source
rows that provide a value for an individual
| | 02:01 | cell or summary, you can do so by
double-clicking the cell, and drilling down
| | 02:06 | into the original data source.
| | Collapse this transcript |
|
|
3. Sorting and Filtering PivotTable DataSorting PivotTable data| 00:00 | When you create a PivotTable, Excel sorts
the data into an order that makes sense
| | 00:05 | according to its internal rules.
| | 00:07 | For example, if you have monthly data,
then the program will sort the data in
| | 00:11 | order of month; January, February,
and so on, and text values tend to be
| | 00:16 | sorted in alphabetical order.
| | 00:19 | In this movie, I will show you how to
change the sorting order, both for numerical
| | 00:23 | fields in the body of the
PivotTable, and also for the labels.
| | 00:27 | In this movie, I'll use the Sorting
sample file, which you can find in the
| | 00:32 | Chapter03 folder of your
Exercise Files archive.
| | 00:35 | This is a fairly simple PivotTable,
and it contains sales data for the
| | 00:40 | months of January and February for
a given year, and also the sales are
| | 00:45 | divided out by department.
| | 00:46 | You'll notice that the top level of
organization is month, and months are sorted
| | 00:51 | in sequential order: January, and then
February; and the departments are sorted in
| | 00:57 | alphabetical order; Balance,
Dance, Play, Sport, and Work.
| | 01:00 | But let's say that I want to sort the
PivotTable, so the numerical data is in
| | 01:05 | either ascending or descending order.
| | 01:07 | To do that, I right-click any cell that
contains data, and not a summary, but any
| | 01:13 | cell that contains data in
the body of the PivotTable.
| | 01:16 | Then in the shortcut menu that appears,
point to Sort, and then I can select Sort
| | 01:22 | Smallest to Largest, Largest
to Smallest, or more options.
| | 01:26 | In this case, what I want to do is
display the largest values on top,
| | 01:29 | so I'll click
Sort Largest to Smallest.
| | 01:32 | Doing so causes Excel to sort the
PivotTable data within the existing structure.
| | 01:37 | So, in other words, I had a second level
of January, and then the bottom level,
| | 01:42 | the first level is the department, so
Work, Dance, Sport, Play, and Balance, and
| | 01:47 | you'll notice that the sorting only occurred
within one of those major units; in this case month.
| | 01:52 | So, I have the monthly data sorted
in descending order for January by
| | 01:57 | department, and I have a similar effect
here under February, the data goes from
| | 02:03 | largest to smallest.
| | 02:05 | If you want to undo a sort that you've
applied, you can either click the undo
| | 02:08 | button, or do as I'll do now, which is
pressing Ctrl+Z. Doing so undoes your last
| | 02:15 | action. In this case, it was the sort.
| | 02:17 | Now let's say that you want to sort
based on label data instead of the data in
| | 02:22 | the body of the PivotTable.
| | 02:23 | For example, let's say that I
wanted to have my departments listed in
| | 02:27 | reverse alphabetical order.
| | 02:29 | To do that, I can go over to the PivotTable
Fields pane, and I want to sort by department,
| | 02:35 | so I'll hover the mouse pointer
over the Department field header.
| | 02:38 | You can see that it turns green, and
at the right edge, there is a downward
| | 02:43 | pointing black triangle.
| | 02:45 | I'll hover the mouse pointer over that
triangle, and when it's in position, I'll
| | 02:49 | click it, and doing so gives me a
shortcut menu very like the one that I got when
| | 02:54 | I right-clicked the data cell.
| | 02:56 | I can Sort A to Z, Sort Z to A; that is
either ascending or descending order, or
| | 03:01 | I can use more options.
| | 03:03 | In this case, I want to sort
in reverse alphabetical order,
| | 03:06 | so I'll click Sort Z to A, and my
department names are now sorted in reverse
| | 03:12 | alphabetical order: Work, Sport, Play,
Dance, and Balance. And as before, I can
| | 03:17 | click the Undo button to undo
the sort that I just applied.
| | 03:21 | Those are the basics of
sorting data inside of a PivotTable.
| | 03:24 | The only tricky part is remembering how
to sort by the data in the value field,
| | 03:29 | and again, that technique is to right-click
any cell, point to Sort, and select
| | 03:33 | your sorting option.
| | 03:35 | Other than that, use the controls in
the PivotTables Fields pane, and you can
| | 03:38 | rearrange your data as you desire.
| | Collapse this transcript |
| Creating a custom sort order| 00:00 | When you create a PivotTable, Excel
sorts the data inside of each field in an
| | 00:04 | order that makes sense for that type of data.
| | 00:07 | For example, in this case we have a
set of data for sales for the months of
| | 00:12 | January, and February, and
also for five departments.
| | 00:16 | The months are sorted in sequential
order; January, and then February, and the
| | 00:20 | department names are
sorted in alphabetical order.
| | 00:24 | If you'd like to define a custom sort
order, you can do so by creating a custom
| | 00:28 | list, and in this movie
I'll show you how to do that.
| | 00:32 | I will use the Custom sample workbook,
which you can find in the Chapter03 folder
| | 00:37 | of your Exercise Files archive.
| | 00:39 | So, once again, my goal is to create a
custom list, so that instead of having
| | 00:43 | departments sorted in alphabetical order, I
would have them in an order of my choosing.
| | 00:48 | So, I need a custom list of
values to use as my sorting criteria.
| | 00:53 | To create the list, I'll click the File
tab, and then click Options to display
| | 00:58 | the Excel Options dialog box.
| | 01:01 | Click Advanced to show the Advanced
Options page, and then I'll scroll down, and
| | 01:06 | actually, if you drag the slider all the
way down, about halfway down the screen,
| | 01:10 | you'll see the Edit Custom List button.
Go ahead and click it to display the
| | 01:15 | Custom List dialog box.
| | 01:17 | You can see that Excel comes
with four predefined lists;
| | 01:20 | there are two each for days of the
week, with abbreviation, and the full names,
| | 01:26 | and similarly for months you have abbreviations,
and also the full names of the months.
| | 01:31 | To create a new list, make sure that
New List is highlighted inside the Custom
| | 01:35 | List pane, and then click
in the List Entries pane.
| | 01:39 | Now you can type in the list in the order
that you want to use as your sorting criteria.
| | 01:44 | So, for example, let's say that I
wanted my five departments to be sorted in
| | 01:48 | this order: Play, then press Enter;
then Balance Enter; Dance, and Enter; then
| | 01:56 | Work, Enter; and Sport.
| | 01:59 | I'll just take a moment to verify the
spelling is correct, and it looks like it
| | 02:03 | is, and I'll click Add
to create the list.
| | 02:07 | If for some reason you want to get
rid of a custom list, you can do that
| | 02:10 | by clicking the List over here in the Custom
List pane, and then clicking the Delete button.
| | 02:15 | I'm not done with this list yet, so
I'll click OK, and OK again to close the
| | 02:20 | Excel Options dialog box.
| | 02:22 | Now I can sort by
department using my custom list.
| | 02:25 | To do that, I'll move the mouse
pointer over into the PivotTable Fields pane,
| | 02:29 | point to Department, and then when I
highlight the Department Field header, I'll
| | 02:34 | point to the downward pointing black
arrow, and then I'll click it to display
| | 02:37 | the shortcut menu, and the
item I want is More Sort Options.
| | 02:42 | Doing so displays the Sort dialog box.
| | 02:46 | I want to sort using my custom list, and
I want to sort in ascending order, so in
| | 02:50 | other words, the order in
which I entered the items.
| | 02:53 | So, I'll click Ascending (A to Z) by
Department, so that's correct, I don't need
| | 02:58 | to change it, and now I'll
click More Options.
| | 03:01 | Now, in the More Sort Options dialog
box, I'll clear the Sort automatically
| | 03:06 | checkbox, which I need to do to
activate the First key sort order list box.
| | 03:11 | I want to click that list box's down
arrow, and select the custom list that I
| | 03:16 | just created, so Play,
Balance, Dance, Work, and Sport.
| | 03:20 | With that selected, I'll click OK,
and click OK again to close the Sort
| | 03:24 | dialog box, and when I do, the PivotTable is
sorted based on the values in my custom list.
| | 03:30 | So, within each month, the Departments
Play, Balance, Dance, Work, and Sport
| | 03:34 | define the order of
the items in the PivotTable.
| | 03:37 | Custom lists are an
extremely valuable and powerful tool.
| | 03:41 | If you find that a particular order of
items within a field communicates a lot
| | 03:45 | of information, then you can define
that order as a custom list, and use it to
| | 03:49 | rearrange your data in your PivotTable.
| | Collapse this transcript |
| Filtering a PivotTable field by selection| 00:00 | PivotTables are extremely useful for
summarizing large data sets. There will
| | 00:05 | be times, however, when you want to limit the
data that is displayed within a PivotTable.
| | 00:10 | You can limit the data that appears by
using a filter, and in this movie, I will
| | 00:15 | show you how to create one type of
filter, which is a selection filter.
| | 00:18 | I will use the Selection sample workbook,
which you can find in the Chapter03
| | 00:23 | folder of your Exercise Files archive.
| | 00:26 | This PivotTable contains quarterly
sales data for five different departments:
| | 00:31 | Balance, Dance, Play, Sport, and Work.
| | 00:33 | Let's say that I that I want to filter
the data, so that I only see values for
| | 00:38 | the Balance and the Sport departments.
| | 00:41 | To do that, I'll click any cell in the
body of the PivotTable, and then in the
| | 00:45 | PivotTables Fields pane, I'll hover the
mouse pointer over the Department field
| | 00:50 | header, and then move the mouse
pointer over that header's downward pointing
| | 00:54 | black triangle, and click it.
| | 00:57 | And then in the shortcut menu that
appears, I have a Filter option here at the
| | 01:02 | bottom that allows me to select items.
| | 01:04 | So, I can Select All items, and then I
also have checkboxes for each of the five
| | 01:10 | values, or departments within that field.
| | 01:12 | If I want to remove all the selections,
then I can clear the Select All box by
| | 01:17 | clicking it. Doing so removes all
of the checks from the checkboxes.
| | 01:22 | I can now check the box of
any department I want displayed.
| | 01:24 | So, I'll check the Balance box, and the
Sport box, and if I scroll back up, you can
| | 01:31 | see that the Select All button
now has a black square inside of it.
| | 01:34 | The black square indicates that some
items were checked, and some aren't. The
| | 01:39 | Select All box has three states.
| | 01:41 | The first is check, which indicates
that all of the checkboxes are checked, so
| | 01:46 | all values will be displayed.
| | 01:48 | A black square indicates that some
values are checked, but some aren't, so not
| | 01:52 | every value will be displayed, and if
the Select All checkbox is cleared, that
| | 01:56 | means that nothing is selected.
| | 01:58 | But in this case, I have Balance
and Sport selected, so I'll click OK.
| | 02:03 | Doing so filters the data inside
the PivotTable, so I only see results
| | 02:07 | for Balance and Sport.
| | 02:09 | And you'll also notice that the Grand
Total is changed to match the filter as well.
| | 02:14 | You can also sort by
values in more than one field.
| | 02:17 | So, if I want to sort by quarter, I'll
just click the Quarter field header's
| | 02:21 | down arrow, clear Select All, and let's say that
I only want to see results for quarter number 3.
| | 02:26 | I'll check that box, click OK, and my
PivotTable is filtered, so that it only
| | 02:31 | displays quarter 3 results for
the two departments I selected.
| | 02:35 | To undo the filter, I can click the
Undo button on the quick access toolbar,
| | 02:40 | which reverses the last change that I
made, or I can use the Filter interface, so
| | 02:45 | I'll click Department again, and
I can click the Select All box.
| | 02:49 | Doing so checks the value; all the
values now have checks next to their
| | 02:53 | names, so I'll click OK.
| | 02:55 | Doing so removes the filter, and all
the values are now displayed within the
| | 02:59 | body of the PivotTable.
| | 03:00 | Filtering by selection is an extremely
powerful tool, because it allows you to
| | 03:04 | identify exactly the fields that you want.
| | 03:07 | It's a technique that I'm sure you
will use quite a bit when you get down to
| | 03:11 | specific analysis for your business.
| | Collapse this transcript |
| Filtering a PivotTable by rule| 00:00 | If you want to limit the data that
appears in a PivotTable, you have many
| | 00:03 | different ways to do it.
| | 00:05 | For example, you can select which values
you want to appear. If you have a lot
| | 00:09 | of values, that can be kind of tedious.
| | 00:11 | If possible, you should try to define
a rule that allows you to tell Excel
| | 00:15 | quickly which values to include, and which to
exclude from the PivotTable in your filter.
| | 00:20 | In this movie, I will show you how to
create rule-based filters, so that you can
| | 00:24 | effectively limit the data
inside of your PivotTable.
| | 00:27 | I will use the Rule sample file, which
you can find in the Chapter03 folder of
| | 00:32 | your Exercise Files archive.
| | 00:34 | This table summarizes the number of
new customers acquired for quarters 1, 2,
| | 00:39 | 3, and 4, for the years 2012 and 2013, for
four regions: North, East, South, and West.
| | 00:45 | Let's say that I want to limit the
data to only quarters 3 and 4, so in
| | 00:51 | other words, what I want to do is
define a rule that causes the PivotTable
| | 00:56 | to only display quarters with numbers greater
than 2, so I can define a rule to that effect.
| | 01:02 | To do that, I'll click any cell in the
PivotTable, as I've done here, and then
| | 01:06 | over in the PivotTable Fields pane,
I'll point to the Quarter field header, and
| | 01:10 | then move the mouse pointer over its
downward pointing black triangle, and when
| | 01:15 | I click it, I get a shortcut menu,
with the number of filtering tools.
| | 01:19 | In this case, I want to filter by the
label, and the Label is the value that
| | 01:24 | appears here for 3, 2, and 1 at the edge
of the PivotTable, defining its contents.
| | 01:29 | So, I'll point to Label Filters, and
then from the list that appears, I'll
| | 01:34 | select Greater Than.
| | 01:35 | And you can see that there are quite a
few other rules that you could create,
| | 01:39 | but in this case, I'll just
use Greater Than as an example.
| | 01:43 | So, when I click it, the Label Filter
dialog box appears. The Show items for which
| | 01:48 | the label box says is Greater Than, so
that's my comparison operation, and now I
| | 01:54 | can define the value that I
want to use for the comparison.
| | 01:57 | In this case, I want to show values for
all quarters that are greater than 2, so
| | 02:02 | I'll type 2, and click Ok.
| | 02:05 | Doing so applies the filter to the
body of the PivotTable, and now I only see
| | 02:09 | results for quarters 3 and 4.
| | 02:12 | And as always, if I want to remove that
filter, I can click the Undo button on
| | 02:16 | the quick access toolbar, and doing
so takes away my last action, which was
| | 02:21 | applying the filter.
| | 02:22 | Rules are extremely powerful
ways of filtering your PivotTable.
| | 02:26 | If you can define one,
you can save a lot of work.
| | Collapse this transcript |
| Filtering a PivotTable using a search filter| 00:00 | Many companies use combinations of
letters and numbers to identify customers,
| | 00:05 | projects, and departments.
| | 00:07 | In this movie, I will show you how
to use search filters to filter your
| | 00:11 | PivotTables based on characters
that appear inside of those strings.
| | 00:15 | I will use the SearchFilter sample file,
which you can find in the Chapter03
| | 00:20 | folder of your Exercise Files archive.
| | 00:22 | This PivotTable contains data about a
number of products, listed here by their
| | 00:27 | product code, and also shows how many
of those products have been ordered for
| | 00:32 | three states: California,
Oregon, and Washington.
| | 00:34 | What I'd like to do is use filters that
allow me to identify the products that
| | 00:40 | I want to display inside of the PivotTable
without having to create complicated rules.
| | 00:45 | I can do that by creating
what's called a search filter.
| | 00:49 | I want to filter based on the
values in the Product field,
| | 00:52 | so I'll go over to the PivotTable
Fields pane, position the mouse pointer over
| | 00:57 | the Product field header, click the
downward pointing black triangle that
| | 01:01 | appears, and then click
in the Search box.
| | 01:05 | Now, inside the Search box, I can start
typing characters that appear in sequence,
| | 01:09 | and the values that I want to display.
| | 01:11 | So, let's say, for example, that I only
wanted to display products that are part of
| | 01:16 | the Play department.
| | 01:17 | Those products start with the letters
PL, so I'll start typing P, and then L,
| | 01:23 | when I do, you can see that the selection
list has been narrowed down to only
| | 01:27 | items that contain the
letters PL, in that order.
| | 01:31 | Two things to note; the first is that
case doesn't matter, so in other words,
| | 01:35 | even though I typed lowercase
pl, I have uppercase PL here.
| | 01:39 | And secondly, although it's not apparent
from this selection, the characters PL
| | 01:43 | could appear anywhere in these values;
it doesn't have to be at the start.
| | 01:47 | But in this case, I have the
values for PL, 003, 004, 009, 12, and 30.
| | 01:55 | If I want to apply this filter, I'll
click OK, and now I only see items from
| | 02:00 | the Play department.
| | 02:01 | I'll go ahead and click
Undo to remove that filter.
| | 02:04 | Now let's say that I only want to
display items that have the character
| | 02:08 | string 001 in them.
| | 02:11 | Again, I want to filter based on the
values in the Product field, so I'll click
| | 02:16 | the Product field header's down arrow
in the PivotTable Fields pane, click in
| | 02:20 | the search box, and now, just to show
you what could happen, I'll type 1.
| | 02:25 | So, now I see BA001, but also PL012.
| | 02:31 | So, that's not what I want to do; I only
want to display products that have 001 in them.
| | 02:37 | So, I'll type Backspace, and type in 001,
and now you can see that I have products
| | 02:42 | BA001, SP001, and WK001, and when I
click OK, Excel uses the search filter to
| | 02:51 | limit the data that appears in the
PivotTable, so now it is only products with a
| | 02:55 | string 001 somewhere in their name.
| | 02:59 | And again, to remove that filter,
I'll just click the Undo button, and Excel
| | 03:03 | restores the PivotTable to its full display.
| | 03:06 | Search filters are extremely powerful
tools; just remember that the characters
| | 03:10 | you type into the search box can occur
anywhere within the value, not just at the
| | 03:14 | beginning. That's the most common
mistake of using search filters.
| | Collapse this transcript |
| Filtering a PivotTable using slicers| 00:00 | When you apply a filter to a PivotTable,
Excel limits the data that appears in
| | 00:05 | the body of your worksheet.
| | 00:06 | The problem is that there is no clear
indication of which values are included,
| | 00:11 | and which values are excluded from
the current state of the PivotTable.
| | 00:15 | In this movie, I will show you how to
use slicers, which do give a strong visual
| | 00:19 | representation of values that are
either in or out of the PivotTable.
| | 00:24 | I will use the Slicer sample workbook,
which you can find in the Chapter03
| | 00:28 | folder of your Exercise Files archive.
| | 00:31 | This PivotTable contains quarterly
customer acquisition data for a company, and
| | 00:36 | it's based on a year and region.
| | 00:39 | Let's say that I just want to focus on
the East region. I can create filters
| | 00:43 | using the tools that I've shown in
other movies that you can access form the
| | 00:48 | PivotTable Fields pane.
| | 00:49 | But let's say that I want to have a
strong visual representation of which
| | 00:53 | regions are displayed in the PivotTable.
| | 00:56 | To do that, I can click any cell in
the PivotTable, then click the Analyze
| | 01:00 | contextual tab on the ribbon, and then
in the Filter group, click Insert Slicer.
| | 01:06 | Doing so displays the
Insert Slicers dialog box.
| | 01:10 | I want to filter based on Region, so I
will check the Region box, and just a
| | 01:15 | note; you can see that I could create a
slicer for anyone of my fields, but in
| | 01:20 | this case, I'll just do region. Click OK,
and doing so inserts a slicer in the worksheet.
| | 01:26 | I'll drag the slicer over to the side, so
that you can see you can see all of the
| | 01:31 | data inside of the PivotTable.
| | 01:32 | Every region is currently displayed
inside the PivotTable, so they're all
| | 01:35 | highlighted here inside the slicer.
| | 01:37 | If I only want to display data for
the East region, then I can just click
| | 01:41 | the East button here, and doing so removes the
other regions from the body of the PivotTable.
| | 01:47 | You can select Regions, or any other
values that you want using the familiar
| | 01:50 | techniques of either clicking, so for
example, I can display West by clicking it.
| | 01:55 | If I want to display multiple items,
I can click the first one -- in this case,
| | 01:59 | West -- then hold down the Ctrl key, and
click the second one; I'll do North.
| | 02:04 | When I do, both North and West are
selected, and because I held down the Ctrl
| | 02:09 | key, the individual items that I
selected appear inside of the slicer, and
| | 02:14 | therefore, in the PivotTable.
| | 02:16 | If you want to display items that are
next to each other inside of the slicer,
| | 02:20 | then you can click the first item, and
then click the last item in the sequence,
| | 02:25 | holding down the Shift key.
| | 02:26 | So, for example, if I want to show the
regions East through South, I can click
| | 02:30 | East, then hold down the
Shift key, and click South.
| | 02:34 | So, if I'd held down the Ctrl key, I would
just have East and South, but because I
| | 02:38 | held down the Shift key, when I click
South, I now have East, North, and South,
| | 02:43 | with only West excluded.
| | 02:45 | If I want to clear a filter from the
slicer, I can click the Clear Filter button,
| | 02:50 | which is here at the top right corner,
or I could Alt+C, but in this case, I'll
| | 02:55 | just click that button.
| | 02:56 | Doing so removes the filter, and displays all
of the items inside the body of the PivotTable.
| | 03:02 | When I'm done using the slicer, I can
right-click its body, and then click
| | 03:06 | Remove Region. Doing so gets rid of
the slicer, and it would remove any
| | 03:11 | filter that was applied.
| | 03:12 | So, if you have room on your
worksheet when you're working with your
| | 03:15 | PivotTable, and you want to have a
clear indication of which values are
| | 03:19 | included, and which values are
excluded from the current state of the
| | 03:22 | PivotTable, slicers are the way to go.
| | Collapse this transcript |
| Formatting slicers| 00:00 | In the previous movie, I showed
you how to filter your PivotTable
| | 00:03 | visually using a slicer.
| | 00:06 | In this movie, I'll show you how to work
with slicers to change their format, so
| | 00:09 | that they fit more
effectively in your worksheet.
| | 00:12 | I will use the FormatSlicer sample
file, which you can find in the Chapter_03
| | 00:17 | folder of your Exercise Files archive.
| | 00:19 | As a quick review, a slicer is a visual
representation of a filter that's been
| | 00:25 | applied to a PivotTable field.
| | 00:27 | In this case, I'll just create
a slicer for the Region field,
| | 00:31 | so I'll check that box, and click OK.
| | 00:33 | And just for demonstration purposes,
I'll filter so that only the South
| | 00:38 | region is displayed.
| | 00:40 | When the slicer is selected, the
Options contextual tab appears on the
| | 00:44 | ribbon, and I have a number of tools that I
can use to change the slicer, and its formatting.
| | 00:49 | For example, over on the right side in
the Size group, I can change its Height,
| | 00:53 | and Width, so if I wanted to make it
smaller, say, take it down to 1.5 inches, I
| | 00:59 | can just type 1.5 in
the Height box; type Enter.
| | 01:02 | I can also resize the
slicer by dragging its handles,
| | 01:06 | so if I want to make it taller, I can
just go to the bottom edge, and drag the
| | 01:10 | middle handle. Drag that down.
| | 01:11 | It will resize this.
| | 01:12 | Same thing here; if I want
to make it wider, I can do so.
| | 01:16 | If I want to change its height and
width at the same time, then I can drag one
| | 01:20 | of the corner controls.
| | 01:21 | You can also change
the size of the buttons.
| | 01:24 | To do that, just go up to the Buttons
group, and let's say I want to make the
| | 01:28 | buttons a little bit narrower.
| | 01:29 | They are currently 0.26 of an
inch, so just over a quarter.
| | 01:33 | Let's say that I want to make
it 0.15, so 15/100th of an inch.
| | 01:38 | I will type that in the Height box,
press Enter, and now the buttons are smaller.
| | 01:42 | If I want to make them thinner, I can
change the value in the Width box, say,
| | 01:46 | from 1.79 inches to 1.4, press
Enter, and the buttons are resized.
| | 01:53 | If a field contains a lot of values,
you can also increase the number of
| | 01:56 | columns in the slicer.
| | 01:58 | So, if I wanted to make the slicer two
column, I can do that just by clicking the
| | 02:03 | up arrow on the spin
control next to the Columns field,
| | 02:07 | and doing so adds a second column
of buttons to the Region slicer.
| | 02:12 | You can also change the appearance of
the slicer using a style, and they appear
| | 02:17 | here in the Slicer Styles gallery.
| | 02:20 | You can display the entire
gallery by clicking the More button,
| | 02:23 | and that displays all the styles that
are available to you, or if you want, you
| | 02:27 | can also create a New Slicer Style.
| | 02:29 | Let's say that I want my slicer to
stand apart from this dark blue theme that I
| | 02:34 | have here for the PivotTable.
| | 02:36 | So, I'll use an orange and gray theme.
| | 02:39 | So, I'll click that style, and
doing so applies it to the slicer.
| | 02:44 | And if I make the buttons larger, a
quarter of an inch, then you can see the
| | 02:50 | impact of the formatting
much more clearly.
| | 02:53 | Those are the basics
of formatting Slicers.
| | 02:55 | Change them any way you need to to
get your message across effectively.
| | 02:59 | Don't be afraid to use a little bit of
color, so it's easy for your audience
| | 03:02 | to see which values are in the
PivotTable, and which are currently out
| | 03:05 | because of a filter.
| | Collapse this transcript |
| Filtering with report filter fields| 00:00 | So far in this chapter, all of the
filtering techniques that I've shown you use
| | 00:04 | fields that are included in
the PivotTable's organization.
| | 00:08 | In this movie, I will show you how to
use report filter fields that allow you to
| | 00:12 | limit the data displayed in a PivotTable
without changing its organization.
| | 00:16 | I will use the ReportFilter sample
file, which you can find in the Chapter_03
| | 00:20 | folder of your Exercise Files archive.
| | 00:23 | This is a fairly simple PivotTable.
| | 00:25 | It contains new customer data based on
quarter 1, 2, 3, and 4, for four regions.
| | 00:32 | If you look over in the PivotTable
Fields pane, you can see that there are
| | 00:35 | actually four fields available to me.
| | 00:38 | The one that isn't
currently displayed is Year.
| | 00:41 | I'll just show you what happens
when I add it to the PivotTable.
| | 00:44 | So, I have Year, and I'll drag it down
above Regions, in the Rows area, and you
| | 00:50 | can see that my data is broken out by
Year, in addition to being broken out by
| | 00:55 | Region, and by Quarter.
| | 00:56 | But let's say that I don't
care about the year.
| | 00:59 | I know that the data is from 2012 and
2013, and all I care about are the totals.
| | 01:04 | Well, the first thing I can do
is remove Year from the Rows area,
| | 01:08 | but where do I put it?
| | 01:09 | There are two options.
| | 01:10 | The first is that I could drag it
back up to the top to remove it from the
| | 01:15 | PivotTable, or I can also drag the Year
field header from the Rows area to the Filters area.
| | 01:21 | Doing so changes the PivotTable back
to its original organization, but it also
| | 01:25 | adds the Year field here at the
top left area of the PivotTable.
| | 01:30 | This is called the report filter area,
or just the filter area in Excel 2013.
| | 01:35 | If I click this filter field's down
arrow, Excel displays a filter interface.
| | 01:41 | I can create a Search filter,
or I can click individual items.
| | 01:45 | So, let's say that I only
want to see values for 2012.
| | 01:47 | If I click 2012, and click OK, Excel
changes the data inside of the PivotTable,
| | 01:53 | and the value 2012
appears here in cell B1.
| | 01:56 | I'll click the down arrow again, click 2013,
click OK, and you can see that the values change.
| | 02:03 | If you want to show multiple values,
then click the filter arrow, check the
| | 02:07 | Select Multiple Items box, and you get
a selection interface like you would do
| | 02:12 | if you're creating a filter
for a regular field.
| | 02:15 | In this case, I only have two items,
| | 02:16 | so checking the 2012 box
also checks the All box.
| | 02:21 | So, I can just click OK, and now I
have essentially no report field filter.
| | 02:26 | So, that by itself is pretty neat.
| | 02:28 | You can filter the items that appear
inside the body of a PivotTable without
| | 02:32 | changing its organization.
| | 02:34 | There's one other thing you can do with
filter fields, which you might not know
| | 02:37 | about if you haven't worked with PivotTables
extensively, and that is, you can
| | 02:41 | create separate worksheets -- in fact,
separate PivotTables, that summarize only
| | 02:45 | the data for each item
in your filter field.
| | 02:48 | Let me show you how to do that.
| | 02:49 | First thing you need to do is click
any cell inside the PivotTable, and then
| | 02:54 | click the Analyze contextual tab.
| | 02:57 | Then, in the PivotTable group, which
is all the way to the left, click the
| | 03:01 | Options button's down arrow, and
then click Show Report Filter Pages.
| | 03:06 | Doing so opens the Show
Report Filter Pages dialog box.
| | 03:10 | Every field currently positioned in the
Filters area appears here in the list,
| | 03:15 | and you can then click any or all of the
fields that you want to use to divide up
| | 03:20 | the values in your PivotTable.
| | 03:21 | So, for example, if I had both Year,
and Region, then I can click one, or both.
| | 03:27 | One thing to watch out for, though, is
that Excel would create a new PivotTable,
| | 03:32 | and a new worksheet for every combination.
| | 03:35 | In this case, I have 2 years and 4
regions, so that would be 8 separate
| | 03:39 | PivotTables, so be very careful
when you're doing it, because the numbers
| | 03:43 | can grow very, very quickly.
| | 03:46 | But in this case, all I have is one
field, which contains two values. That's Year;
| | 03:50 | I'll make sure it's selected. Click OK.
| | 03:52 | Now I have two new sheets with PivotTables;
one for 2012, and if I click, one for 2013.
| | 03:59 | And you can see that in fact
they are normal PivotTables.
| | 04:04 | The difference is that a filter has
already been applied for the year 2013 here,
| | 04:09 | and the year 2012 here.
| | 04:11 | So, that's two useful ways to apply
filters using the report filters field.
| | 04:16 | You can limit the data that appears in
your PivotTable without changing its
| | 04:20 | organization, and you can create
separate worksheets and PivotTables for
| | 04:24 | subsets of your data.
| | Collapse this transcript |
| Clearing and reapplying PivotTable filters| 00:00 | When you apply a filter to a
PivotTable, you limit the data that's
| | 00:03 | displayed within it.
| | 00:05 | In this movie, I will show you how to
remove filters, both individually, and
| | 00:09 | removing all your
filters at one time.
| | 00:12 | I will use the Clear sample file, which
you can find in the Chapter_03 folder of
| | 00:16 | your Exercise Files archive.
| | 00:18 | This workbook contains a single
PivotTable with three fields;
| | 00:23 | Department, Quarter, and Sales,
| | 00:25 | and I currently have filters applied to
both the Department and Quarter fields.
| | 00:31 | You can tell that a filter is applied,
because the Column Labels and Row Labels
| | 00:36 | buttons within the body of the PivotTable
have a filter icon next to the arrow,
| | 00:41 | and also, you can see in the
PivotTable Fields pane that the Department and
| | 00:47 | Quarter field headers have
filter icons next to them as well.
| | 00:51 | If you want to remove a filter from a
field within a PivotTable, then go over
| | 00:56 | to the PivotTable Fields pane, and
click the down arrow of the field that you
| | 01:01 | want to remove the filter from, and then
click Clear Filter from Department, and
| | 01:06 | Department, in this case,
is the name of the field.
| | 01:09 | If I were removing the field from Quarter,
it would say Clear Filter from Quarter.
| | 01:13 | So, I will go ahead and click that,
| | 01:15 | and when I do, all of the
departments appear in the PivotTable.
| | 01:18 | And I could do the
same thing for Quarter.
| | 01:20 | If I want to reapply the filter, I
just need to undo my last action.
| | 01:24 | So, to do that, I'll go up to the quick
access toolbar, and click the Undo button.
| | 01:29 | Doing so reapplies the filter.
| | 01:31 | I could have also pressed Ctrl+Z. If
you want to remove every filter from a
| | 01:35 | PivotTable, you don't need to
get rid of them individually.
| | 01:39 | Instead, what you can do is click any
cell in the Pivottable, then on the
| | 01:43 | Analyze tab on the ribbon, click the
Clear button, and then click Clear Filters.
| | 01:50 | Doing so removes all the filters from
the PivotTable, and displays all of the
| | 01:54 | data contained within the fields, with
the organization that you've set in the
| | 01:58 | PivotTable Fields pane.
| | 02:00 | And that is how you remove and reapply
filters from PivotTables in Excel 2013.
| | Collapse this transcript |
|
|
4. Formatting PivotTablesApplying a PivotTable style| 00:00 | Excel PivotTables let you
analyze your data effectively.
| | 00:04 | They're also great for presentations,
| | 00:06 | which means that you need to
pay attention to formatting.
| | 00:09 | In this movie, I will show you how to
apply and manage existing PivotTable styles.
| | 00:13 | I will use the ApplyStyle sample file,
which you can find in the Chapter_04
| | 00:19 | folder of your Exercise Files archive.
| | 00:21 | This PivotTable has the bare
minimum of formatting applied to it.
| | 00:26 | The format doesn't use any color, and
you can also see that there are only thin
| | 00:30 | border lines between the
labels and the data.
| | 00:32 | If I want to change the style that's
applied, all I need to do is click any cell
| | 00:36 | in the PivotTable, and then click the
Design contextual tab on the ribbon.
| | 00:41 | The Design contextual tab has a
gallery in the Pivot Table Styles group.
| | 00:46 | You can see the first seven
styles here in the ribbon.
| | 00:49 | If you want to display the entire
gallery, then click the More button, which is
| | 00:53 | here at the bottom right corner of
the in ribbon gallery, and you get a
| | 00:56 | scrollable list, which contains
all of the styles available to you.
| | 01:01 | If I want to apply a style, all I need
to do is click it, and in this case, I'll
| | 01:06 | click Pivot Style Light 1.
| | 01:08 | Doing so applies it to
the body of the PivotTable.
| | 01:11 | Now, you probably didn't see much of a
difference, but what happened was that
| | 01:15 | the labels are now formatted in bold.
| | 01:19 | There is one other change that I can
make, and that is to apply banding.
| | 01:22 | Banding is when you have a
different color for alternate rows.
| | 01:25 | For example, the first row might be
light, and the second row would be darker,
| | 01:30 | say, perhaps with a gray.
| | 01:31 | So, with my style selected, and
again, on the Design contextual tab of the
| | 01:35 | ribbon, I'll go to PivotTable Style
Options group, and click Banded Rows.
| | 01:41 | Doing so applies banding, which makes it
easier to distinguish between the data
| | 01:46 | in the odd and even rows
within the body of the PivotTable.
| | 01:49 | If your style also allows banded
columns, then you can check that box.
| | 01:53 | So, for example, I'll clear the Banded
Rows checkbox, and click Banded Columns.
| | 01:58 | And when I do that, all of the odd
numbered columns are now colored with a
| | 02:02 | darker color, and all of the even
numbered columns are left blank.
| | 02:06 | So, they just have no coloring whatsoever.
| | 02:09 | If you select both of them at the same
time, then you get a crosshatch pattern.
| | 02:13 | I personally find this much
formatting to be confusing,
| | 02:16 | and if I'm going to use banding at all,
I'll do either Banded Rows, or Banded
| | 02:20 | Columns, and most of the time, I stay with
Banded Rows, and read the columns as best as I can.
| | 02:26 | And that is how you apply and
manage existing PivotTable styles.
| | 02:30 | Of course, if you don't find a style
that's exactly what you want, you can
| | 02:34 | always create your own.
| | Collapse this transcript |
| Creating a PivotTable style| 00:00 | As with any spreadsheet object, you
should pay careful attention to your
| | 00:03 | PivotTable's formatting.
| | 00:05 | The better your formatting, the
more effectively your PivotTables will
| | 00:08 | communicate your data.
| | 00:09 | In this movie, I will show you how to create
and apply your own custom PivotTable style.
| | 00:14 | I'll use the DefineStyle sample file,
which you can find in the Chapter_04 folder
| | 00:19 | of your Exercise Files archive.
| | 00:21 | You can apply an existing style by
clicking any cell in the PivotTable, and
| | 00:25 | then on the Design contextual tab, clicking
any style in the PivotTable Styles gallery.
| | 00:32 | If you want to create your own PivotTable
style, you can go to the bottom of
| | 00:35 | the gallery, and click New PivotTable Style.
| | 00:38 | Doing so displays the New
PivotTable Style dialog box.
| | 00:42 | You can use the controls in this
dialog box to define your style.
| | 00:46 | And in this case, I'll type a name for it.
| | 00:49 | And instead of PivotTable Style 1,
I'll call it Corporate Comm, short for
| | 00:54 | Communication, and now I need to
decide which elements I want to change.
| | 00:59 | In this case, I'll change the header row.
| | 01:01 | So, I'll click Header Row,
| | 01:03 | then I'll click the Format button.
| | 01:05 | Doing so displays the Format dialog box.
| | 01:08 | I can use the controls in the Format
Cells dialog box to change the formatting
| | 01:12 | of the header row cells.
| | 01:13 | I am on the Font tab.
| | 01:15 | So, I'll change aspects of the font
or the type that appears in the cell.
| | 01:20 | So, I'll change the font
color, so that it's white.
| | 01:23 | You can see the Preview here.
| | 01:25 | It shows the white text on a
white background, so it's invisible.
| | 01:28 | I'll leave borders alone for the moment,
but just to show you what's available,
| | 01:32 | if you've worked with borders in the
regular spreadsheet, then I am sure these
| | 01:36 | controls would be familiar with you.
| | 01:37 | You can select predefined border outlines,
or inside, or change individual borders,
| | 01:42 | and of course, the style.
| | 01:44 | Let's say though that all I want
to do is change the background fill.
| | 01:47 | To do that, I'll click the Fill tab,
| | 01:49 | and now I can select the fill
color for the header row cells.
| | 01:53 | And let's say that I want
to make it a medium blue.
| | 01:57 | So, I'll go down to the bottom row, just
above the Fill Effects, and More Colors
| | 02:01 | buttons, and I'll click
the blue swatch here.
| | 02:04 | So, I see the sample here, and when I
click OK, the Format Cells dialog box
| | 02:09 | closes, and I go back to
the New PivotTable Style box.
| | 02:13 | And you can see over here in the
Preview pane that the header row is now that
| | 02:18 | medium blue with white text.
| | 02:20 | Now, let's say that I want to apply banding.
| | 02:22 | So, I want either the first row or the
second row to have formatting applied.
| | 02:26 | I'd like to have the first row be white;
in other words, have no background color,
| | 02:30 | so I'll work with the second row.
| | 02:32 | So, for that, I need to
use the Second Row Stripe.
| | 02:36 | So, I'll click that element inside of the
table element list, and click Format again.
| | 02:42 | The last tab I worked with was the Fill tab,
so it appears when I open the dialog box.
| | 02:47 | Now I can select a color for the banding.
| | 02:50 | I don't want to change the color
of the type; I want it to be black,
| | 02:54 | so I'll go for very light blue.
| | 02:57 | So, I'll click the fifth color swatch.
| | 02:59 | I've got my sample here,
and I'll click OK.
| | 03:03 | When I click OK, the Format Cells
dialog box disappears, and I go back to the
| | 03:08 | New PivotTable Style dialog box.
| | 03:09 | And you can see that my Preview has
been updated here on the right to show that
| | 03:14 | I now have a light blue background,
with dark text for every second row.
| | 03:19 | Those are all the
changes I want to make for now.
| | 03:22 | So, to save it, I can click OK.
| | 03:24 | But if I wanted to make the style the
default for every new PivotTable that
| | 03:28 | I created, I could check the Set
as default PivotTable style for this
| | 03:31 | document checkbox here.
| | 03:33 | I do want to make that change for this
document only, so I'll check the box, and click OK.
| | 03:38 | Excel doesn't apply the PivotTable
style immediately, but I can see it here on
| | 03:44 | the top row of the PivotTable Styles gallery.
| | 03:46 | And when I hover the mouse pointer
over it, it displays a tooltip showing the
| | 03:51 | name of the style, which is Corporate Comm.
| | 03:53 | When I click the style, Excel applies it.
| | 03:56 | If I want to apply the banding, or the
second row stripe that I defined in the
| | 04:01 | style, then I'll just go over to the
PivotTable Style Options group, and check
| | 04:05 | the Banded Rows box.
| | 04:07 | When I do, the style alternates
between a white background, and a light blue
| | 04:11 | background for odd and even rows.
| | 04:14 | That's how you create a PivotTable
style inside of Excel 2013.
| | 04:18 | You can affect many different PivotTable
elements, so I encourage you to
| | 04:22 | experiment, and create exactly the
format that's right for your work.
| | Collapse this transcript |
| Changing the PivotTable layout| 00:00 | When you work with PivotTables in
Excel 2013, you can select from a number
| | 00:05 | of different layouts.
| | 00:06 | Each layout handles data and labels
slightly differently, and you can pick the
| | 00:09 | one that works best for
your particular presentation.
| | 00:12 | For this movie, I will use the Layout
sample file, which you can find in the
| | 00:17 | Chapter_04 folder of
your Exercise Files archive.
| | 00:20 | In this movie, I will show you how to
find the different layouts available to
| | 00:24 | you in Excel 2013, and demonstrate how
they affect the presentation of your
| | 00:30 | PivotTable within the workbook.
| | 00:31 | To apply a layout to a PivotTable,
click any cell in the PivotTable, and
| | 00:36 | then on the Design contextual tab, in the
Layout group, click the Report Layout button.
| | 00:42 | Doing so displays the
layouts that are available to you.
| | 00:45 | This PivotTable is currently displayed
in compact form, which means that the
| | 00:49 | layout is as compact
or as small as possible.
| | 00:53 | You can see that there are no blank rows
within the body of the PivotTable, and
| | 00:57 | also Sum of Customers is on
the same level as Column Labels.
| | 01:02 | If I want to apply a different layout,
I'll just click the Report Layout button,
| | 01:06 | and I'll click the next one,
which is Show in Outline Form.
| | 01:09 | The Outline Form layout has a
slightly different set of characteristics.
| | 01:14 | You can see, for example, that we now
have blank lines underneath 2012, and 2013.
| | 01:21 | That's because Quarter has been moved
over one level; instead of being in the
| | 01:26 | same column as the Year,
it's been moved over.
| | 01:29 | One interesting aspect of Outline Form
is that now the columns, East, North,
| | 01:35 | South, and West, in this
case, are all the same width.
| | 01:38 | If I were to click the Undo button to
go back, you can see that because East is
| | 01:43 | in the same column as Column Labels,
that the East cells and values are much
| | 01:49 | larger, much wider than are the
cells for North, South, and West.
| | 01:54 | The width doesn't really matter if
you're just looking at numbers, but if you
| | 01:58 | apply conditional formatting, such as
data bars, the length of which depend on the
| | 02:02 | width of a cell,
then it can make a difference.
| | 02:05 | So, if you're applying conditional
formatting, especially data bars, then I
| | 02:10 | recommend clicking the Report Layout
button, and clicking Show in Outline Form.
| | 02:14 | That way, the width of the cells in
your columns that contain the data in the
| | 02:18 | body of the PivotTable
will all be the same width.
| | 02:21 | The other layout that's available to
you if you click the Report Layout button
| | 02:24 | again is called Tabular Form.
| | 02:27 | So, if you click Show in Tabular Form,
you get a slightly different layout.
| | 02:32 | Tabular Form is almost
the same as Outline Form.
| | 02:35 | The difference is that instead of
having the subtotals at the top of a group,
| | 02:39 | you have them at the bottom of a group.
| | 02:41 | So, previously, the subtotal was here on
the top row with 2012; now it's at the
| | 02:46 | bottom in a separate row
marked 2012 Total.
| | 02:48 | And while I am in Tabular Form,
I'll show you some other aspects of the
| | 02:52 | layout you can affect.
| | 02:53 | So, clicking the Report Layout button
again, you'll see that there are two
| | 02:57 | options at the bottom, and those
are Repeat All Item Labels, and Do Not
| | 03:02 | Repeat Item Labels.
| | 03:04 | If I click Repeat All Item Labels,
you'll see that now instead of having 2012
| | 03:09 | only at the top level of the PivotTable,
I have 2012, 2012, 2012, all the way
| | 03:15 | down, so I have that
value for each quarter.
| | 03:18 | If you're wondering why that's important,
let me rearrange the PivotTable
| | 03:21 | slightly by taking Region, and putting
it underneath Quarter in the Rows area.
| | 03:27 | Now I have data by Year, by Quarter, by
Region, and I have the sum of my customers.
| | 03:32 | I also have the subtotals here for 2012,
quarter number 1, quarter number 2, and so on.
| | 03:38 | If I turn off subtotals by clicking
the Subtotals button, and clicking 'Do not
| | 03:43 | show subtotals, then I
have my original data list.
| | 03:47 | So, if I were to click Sheet 1, which
has the source data for this PivotTable,
| | 03:51 | see that I have 2012 Quarter
1, North Region, Customers.
| | 03:55 | If I click back on Sheet 2, I have
2012, Quarter 1, East Region, and so on.
| | 04:02 | You can use this technique to reconstruct
the original data source, in case
| | 04:05 | someone wants to see it, and
you don't have access to it.
| | 04:09 | So, for example, if the source file
were on another computer, perhaps over a
| | 04:13 | network, and you can't open it
directly, you can reconstruct the list by
| | 04:17 | arranging the data inside your PivotTable,
so that you have everything in the
| | 04:22 | Rows area, and you can do your best to
approximate the order of the fields.
| | 04:26 | In this case, Year, Quarter, and Region
seems like a good way to go, so that's
| | 04:30 | how I rearranged them.
| | 04:32 | And with all changes to a PivotTable,
if you want to undo what you've done
| | 04:36 | before, just click the Undo button, or press
Ctrl+Z, and Excel will reverse your changes.
| | 04:42 | That's a brief overview of the different
layouts available to you. Each one has
| | 04:46 | its own strengths and weaknesses,
| | 04:48 | so feel free to experiment, and look
for the best way to present your data.
| | Collapse this transcript |
| Changing the data field number format| 00:00 | Most PivotTables summarize numerical data.
The problem is that when you create a
| | 00:05 | PivotTable in Excel 2013, as you can
see here, Excel doesn't apply any number
| | 00:10 | formatting to the contents
of the value field.
| | 00:13 | In this movie, I will show you an
efficient way to apply number formatting to
| | 00:17 | the body of your PivotTable.
| | 00:19 | I'll use of the number formatting
sample file, which you can find in the
| | 00:23 | Chapter04 folder of your
Exercise Files archive.
| | 00:26 | Unformatted numbers are hard to read,
and you can see that this first value,
| | 00:31 | 98677, isn't that difficult to read,
because it's different length than the
| | 00:37 | surrounding numbers,
| | 00:38 | but when you're trying to read 138920,
and 137099, unless you're intimately
| | 00:44 | familiar with the data, seeing all those
numbers of the same length, and with the
| | 00:48 | same starting number together
can make it confusing.
| | 00:51 | It would be much easier to read those
numbers with some formatting applied.
| | 00:55 | Here's how you do it.
| | 00:57 | Right-click any data cell from the body
of the PivotTable. So, in other words, not
| | 01:01 | a summary, and certainly not one of the
labels; you want one of the data cells
| | 01:06 | from within the body of the PivotTable.
| | 01:09 | So, go ahead and right-click that cell,
and then from the shortcut menu that
| | 01:13 | appears, click NumberFormat.
| | 01:14 | Doing so displays the Number tab
of the Format Cells dialog box,
| | 01:19 | and you can use these controls, which
are probably familiar to you, if you have
| | 01:23 | used Excel for any length of time, to
change the formatting of the values inside
| | 01:27 | of the Values field in your PivotTable.
| | 01:30 | So, let's say that I want to apply
Currency formatting, so I'll click Currency.
| | 01:34 | Doing so displays a sample of
what the data will look like.
| | 01:38 | In this case, I know all the
values are whole dollar values.
| | 01:41 | I don't have any cents.
| | 01:43 | So, I'll click the down arrow on the
Decimal places spin control twice to
| | 01:46 | move the value from two decimal places
to zero, and you can see the sample has
| | 01:51 | changed, so that now there are no
values to the right of the decimal point.
| | 01:55 | The negative number settings look
good, and I am dealing with US dollars,
| | 01:59 | so I'll leave the Currency Symbol as
the dollar sign, but if I were working
| | 02:03 | in Euros, Pounds, Yen; any other
currency, then I could click the down arrow,
| | 02:09 | and I have a wide range of
currencies to choose from.
| | 02:13 | But now everything looks good,
| | 02:14 | so I'll click OK, and when I click OK, Excel
applies that style to the body of the PivotTable.
| | 02:20 | That's all there is to it.
| | 02:21 | My PivotTable is much easier to read,
I know that the values are currency
| | 02:26 | values, and I have saved space by not having
any values to the right of the decimal point.
| | 02:31 | The values were all in whole
dollars, so I was able to adjust my
| | 02:34 | format appropriately.
| | Collapse this transcript |
|
|
5. Applying Conditional Formats to PivotTablesHighlighting cells by applying a rule| 00:00 | PivotTables are flexible tools that you
can use to present and analyze your data.
| | 00:05 | There might be times where you want
to apply a format if a value within the
| | 00:09 | PivotTable takes on a certain
value, or follows a certain rule.
| | 00:13 | In this movie, I will show you how to
apply rule-based conditional formats, so
| | 00:17 | that you can change the appearance of
cells within the PivotTable, based on the
| | 00:21 | values that the cell contains.
| | 00:23 | I will use the FormatRule sample file,
which you can find in the Chapter05
| | 00:27 | folder of your Exercise Files archive.
| | 00:30 | This PivotTable contains a selection
of data; it's monthly sales for five
| | 00:34 | different departments within this
company, and what I want to do is highlight
| | 00:39 | cells that contain monthly
sales values of at least $75,000,
| | 00:44 | so $75,000 or greater.
| | 00:47 | To create that rule, I'll click any
cell in the body of the PivotTable, and
| | 00:52 | then on the Home tab of the ribbon, in the
Styles group, I'll click Conditional Formatting.
| | 00:57 | In the Conditional Formatting list,
I'll point to Highlight Cells Rules.
| | 01:02 | Doing so displays a list of all the rules
that are available for this type of data.
| | 01:06 | This PivotTable contains numerical
data, so I have rules available, including
| | 01:10 | Greater Than, Less Than,
Between, and Equal To.
| | 01:14 | If I had text or date values, then I
could have Text that Contains, A Date
| | 01:18 | Occurring -- that gives you a
date range -- or Duplicate Values.
| | 01:22 | But in this case, because it's a number, I
have the top four options available to me.
| | 01:26 | I want to identify cells
that contain $75,000 or more,
| | 01:29 | so I'll click Greater Than.
| | 01:31 | Doing so displays the
Greater Than dialog box.
| | 01:34 | I can now type in my comparison value
in the Format cells that are GREATER
| | 01:38 | THAN: box here, where $11,730
is currently highlighted.
| | 01:42 | I'll make that number $75,000, and
then I can click in the With box, which
| | 01:47 | describes the format.
| | 01:49 | The default format is light
red fill with dark red text.
| | 01:53 | If I click the box's down arrow, I see
a list of other pre-made formats, or I
| | 01:57 | can click Custom Format, the bottom item,
to display the Format Cells dialog box.
| | 02:02 | I can now use the controls in the dialog
box to change the formatting for the cell.
| | 02:06 | I can use the controls on the Font tab
to change the font, such as applying Bold
| | 02:11 | formatting to any text that meets my
rule that I am creating. I can also change
| | 02:17 | the number format by clicking the Number
tab, border by clicking the Border tab,
| | 02:22 | and fill by clicking the Fill tab.
| | 02:23 | In this case, I will change the fill
color for cells that meet my rule by
| | 02:27 | clicking the orange
swatch here on the bottom row.
| | 02:30 | See the sample here.
| | 02:32 | I've created all of the rule
elements that I want, so I'll click OK.
| | 02:36 | Doing so redisplays the Greater Than
dialog box, with my custom format selected.
| | 02:40 | I'll click OK, and Excel
applies the rule to the selected cell.
| | 02:44 | Now, you notice that to the right of
the cell, there is an action button, and
| | 02:49 | that is the formatting
options button. I will click that.
| | 02:52 | I can use the controls here to
apply the rule in one of three ways;
| | 02:56 | to only the Aelected cells, to All cells
showing "Sum of Sales" values, or to All
| | 03:01 | cells showing 'Sum of Sales"
values for "Department" and "Month".
| | 03:05 | So, let me show you what those
three different applications look like.
| | 03:09 | First, I'll click All cells
showing "Sum of Sales" values.
| | 03:12 | Doing so applies the format to any
cell that contains a value greater than
| | 03:15 | $75,000, but that includes the Grand Total
cells here in this column, and also in this row.
| | 03:21 | Because those values are going to be
significantly larger in most cases, that's
| | 03:25 | usually not the best way to apply that rule.
| | 03:27 | If I click the button again, you can
see that I have All cells showing "Sum of
| | 03:31 | Sales" values for
"Department" and "Month".
| | 03:35 | Doing so limits the application of the
conditional formatting rule to only cells
| | 03:39 | within the body of the PivotTable,
excluding the Grand Total rows, and any
| | 03:43 | subtotals, which aren't
present in this PivotTable.
| | 03:46 | That's the basics of creating a
rule-based conditional format.
| | 03:49 | You can create pretty much any format
that you'd like, and you have a wide variety
| | 03:53 | of rules available to you within
the Conditional Formatting menu.
| | Collapse this transcript |
| Highlighting the top or bottom values in a PivotTable| 00:00 | When you summarize values using a
PivotTable, you might want to identify the
| | 00:04 | largest and smallest values it contains.
| | 00:07 | In this movie, I will show you how to
identify sets of top, or largest, and bottom,
| | 00:12 | or smallest, values by
creating top and bottom 10 rules.
| | 00:16 | I'll use the TopValues sample file, which
you can find in the Chapter05 folder of
| | 00:21 | your Exercise Files archive.
| | 00:23 | This PivotTable contains monthly sales
data, and what I want to do is identify,
| | 00:28 | say, the top five values in
the body of the PivotTable.
| | 00:33 | To do that, I click any data cell, and
then on the Home tab of the ribbon, in the
| | 00:37 | Styles group, click the Conditional
Formatting button, and point to Top/Bottom
| | 00:42 | Rules, which is the second item in the list.
| | 00:45 | Doing so displays a list of top and
bottom rules that I can create. So, I can
| | 00:49 | identify the Top 10 Items, the Top 10%
Items, same for the Bottom 10, or Bottom
| | 00:54 | 10%, and also Above
Average, or Below Average values.
| | 00:58 | I'll show you how to create a Top 10
Item conditional format, a Bottom 10%
| | 01:03 | conditional format, and also an Above
Average conditional format, so you'll get
| | 01:08 | one of each type in this movie.
| | 01:09 | Let's start out by looking at the Top
10 Items, so I'll click that in the list,
| | 01:14 | and doing so displays the
Top 10 Items dialog box.
| | 01:18 | This dialog box contains two fields.
| | 01:21 | The first is the number of items, so
it's called a Top 10 conditional format,
| | 01:26 | but you can actually
change the number of items.
| | 01:28 | So, in this case, let's changed from 10
to 6, so I'll click in the box, backspace
| | 01:33 | over 10, type the number 6, and the
second box the one to the right lets you
| | 01:38 | select or create a format.
| | 01:40 | If you click the formats down arrow,
you can select from a list of predefined
| | 01:45 | formats, or you can click Custom Format.
| | 01:47 | The Format Cells dialog box appears, and
because the last tab that I used in this
| | 01:52 | dialog box was Fill that
is the tab that appears.
| | 01:55 | So, if doesn't show up in your copy
of Excel, please go ahead and click the
| | 01:59 | Fill tab to display it.
| | 02:01 | Let's say that I want to fill a cell that
meets my criteria for this rule with yellow.
| | 02:06 | So, the top six values in the body of the
PivotTable will be formatted with a yellow fill.
| | 02:12 | So, I'll click the yellow swatch here,
and my sample appears below. I'll click OK
| | 02:17 | to close this dialog box, and then
I'll click OK to apply the format.
| | 02:22 | Now, note that this cell is highlighted
in yellow even though the number 11,730
| | 02:29 | is nowhere near being in the top six
values inside this PivotTable. That's
| | 02:33 | because when you create a conditional
format, it only applies the format to the
| | 02:37 | selected cells, so if I'd wanted, I
could have selected cells B5 to D9, but
| | 02:44 | instead, I just applied the format to a
single cell, and now I can determine which
| | 02:48 | cells I want to apply the format to.
| | 02:51 | To do that, I'll click the Formatting
Options action button, which appears to the
| | 02:55 | right of the selected cell.
| | 02:56 | I have three options: Selected cells,
which is the default, and is what Excel used
| | 03:01 | when I created this conditional format;
all cells containing Sum of Sales
| | 03:06 | values, which applies the rule to all
cells within the PivotTable, including
| | 03:10 | grand totals and subtotals.
| | 03:12 | Those values will always be larger than
individual values within the body of the
| | 03:17 | PivotTable, so we don't want to use them.
| | 03:19 | The last one, All cells showing "Sum
of Sales" values for "Department" and
| | 03:23 | "Month" is what we want to use,
because it limits the conditional formats to
| | 03:27 | cells within the body of the PivotTable.
| | 03:29 | So, I'll go ahead and click that option,
and Excel identifies the six cells with
| | 03:35 | the largest values; in other words, the
top six values in the PivotTable, and as
| | 03:39 | per the rule, those cells
are highlighted in yellow.
| | 03:42 | I'll go ahead and remove the rule by
clicking the Undo button on the quick
| | 03:47 | access toolbar. You could also press
Ctrl+Z. Now let's say that I want to
| | 03:52 | identify the bottom 25% of
the values in the PivotTable.
| | 03:55 | I know I want to apply that format to
the values in cells B5 through D9, so
| | 04:01 | I'll go ahead and select those cells.
That will save me a step later. And then
| | 04:04 | on the Home tab, click Conditional
Formatting, point to Top/Bottom Rules, and
| | 04:10 | I'll click Bottom 10%.
| | 04:12 | And just like I had before, I can
identify the percentage of values that I
| | 04:16 | want to apply the format to, and
instead of 10%, I'll edit the value in the
| | 04:21 | left-hand box, so it's 20.
| | 04:23 | And because I have already shown you
how to create a format earlier in this
| | 04:27 | movie, I'll just stay with the default,
which is light red fill with dark
| | 04:31 | red text, and click OK.
| | 04:32 | Doing so displays the bottom 20% of values,
which in this case is three, and just
| | 04:37 | by coincidence, they all happen
to be in the Dance department.
| | 04:42 | So, sales from the Dance department
are definitely lower than sales for all
| | 04:46 | the other departments.
| | 04:47 | I have one more format to apply, so I'll just
click the Undo button to get rid of this one.
| | 04:52 | I'll leave the same cell selected, and
again on the Home tab, click Conditional
| | 04:56 | Formatting, point to Top/Bottom
Rules, and I'll click Above Average.
| | 05:00 | Doing so identifies cells that are above
average within the body of the PivotTable.
| | 05:05 | Because I'm identifying above average
values, I don't need to create any sort of
| | 05:09 | rule to do that. Selecting this type
of conditional format does that for me.
| | 05:14 | Just for variety, I will select a
different format from one of the prepared
| | 05:18 | formats, so I'll click the Format
box's down arrow, and I'll do Red Text.
| | 05:24 | I'll just click the Red Text format,
click OK, and when I click away from
| | 05:29 | the selection, you can see that the
cells that contain above average values
| | 05:33 | now contain red text.
| | 05:35 | Those are the basics of creating top and
bottom rules. You can go by a number of
| | 05:39 | items, you can go by percentage, or you
can also identify items that are above or
| | 05:44 | below average within the
body of the PivotTable.
| | Collapse this transcript |
| Formatting cells using data bars| 00:00 | PivotTables let you summarize and
analyze large data collections, and in
| | 00:04 | this movie, I will show you how to
supplement that analysis by adding
| | 00:07 | conditional formats that show the
relative magnitude of different values
| | 00:11 | within your PivotTable.
| | 00:13 | The conditional formats I'll
cover are called data bars.
| | 00:15 | As my sample file, I'll use the DataBars
workbook, which you can find in the
| | 00:20 | Chapter05 folder of your
Exercise Files archive.
| | 00:23 | This PivotTable contains monthly sales
data for five different departments, and
| | 00:27 | three months; January, February, and March,
and what I like to do is add data bars
| | 00:33 | to these cells here, B5 through D9, so
that I can tell the relative magnitude of
| | 00:39 | the values without focusing
on the individual numbers.
| | 00:42 | To do that, I select the cells, and then
on the Home tab of the ribbon, click the
| | 00:47 | Conditional Formatting button,
and then point to Data Bars.
| | 00:50 | When you point to Data Bars, you see
two different categories of data bars
| | 00:54 | that you can create.
| | 00:55 | The first one is called Gradient Fill,
and the second is called Solid Fill.
| | 00:58 | I'll show you what a gradient fill data
bar looks like. I'll click the Red Data
| | 01:03 | Bar here on the side.
| | 01:05 | As you can see, gradient fill data bars
start with a solid color at the left side
| | 01:09 | of the cell, and continue on, fading to
the right as they move through the cell,
| | 01:14 | with a border around the bar to
indicate where it begins and ends.
| | 01:18 | Data Bars were first introduced in
Excel 2007, and at that time, the only type
| | 01:23 | available to you were gradient data
bars, so they're included here for
| | 01:27 | backward compatibility.
| | 01:29 | In most cases, however, what you want
to do is use solid fill data bars.
| | 01:34 | You want to do that, because there's no
falloff in the color, so the length of
| | 01:38 | the data bar is easier to discern.
| | 01:40 | So, I'll click Undo to undo the data
bars there, and then with the cells still
| | 01:46 | selected, go Conditional Formatting >
Data Bars, and this time, in the Solid Fill
| | 01:52 | group, I'll click blue.
| | 01:53 | Doing so applies the data bar conditional
formatting, and as you can see, there is
| | 01:58 | no gradient, and no dropoff. The
length of the lines is much easier to tell in
| | 02:02 | relation to other lines, or other
data bars within the PivotTable.
| | 02:07 | Now, one element that you probably
noticed is that the January column is much
| | 02:13 | wider than the other two columns, February,
and March, and the reason for that is
| | 02:17 | the PivotTable's layout.
| | 02:19 | I can change the layout so my three
January, February, and March columns are
| | 02:23 | all the same by clicking any cell on
the PivotTable, then clicking the Design
| | 02:27 | contextual tab, and then clicking Report Layout;
that button is over here in the Layout group.
| | 02:33 | At present the PivotTable is shown
in compact form. What I want to do is
| | 02:38 | display it in outline form,
| | 02:40 | so I'll click the Show in Outline Form item,
and when I do, Excel rearranges the PivotTable.
| | 02:47 | It takes away the Row Labels and Column
Labels markers, and instead, I now have
| | 02:53 | three columns, January, February, and
March, that are all the same width.
| | 02:57 | When you add a data bar conditional
format to the body of a PivotTable, make
| | 03:01 | sure you only select cells that
contain individual data units.
| | 03:06 | In other words, you want to avoid
grand totals and subtotals, because those
| | 03:10 | values will generally be much
larger than what is in the body of the
| | 03:13 | PivotTable, and it could skew the lengths
of the data bars by comparing them to
| | 03:17 | much larger values.
| | 03:18 | Those are the basics of using
data bars in Excel 2013 PivotTables.
| | 03:23 | They help you see the relative magnitude
of data, without getting in the way of
| | 03:25 | the numbers in the PivotTable.
| | Collapse this transcript |
| Formatting cells using color scales| 00:00 | PivotTables let you display and
analyze large data collections.
| | 00:04 | In this movie, I will show you how to
use conditional formatting, so that you can
| | 00:08 | use color to indicate the relative
magnitude of values within your PivotTable.
| | 00:12 | I will use the ColorScales sample
file, which you can find in the Chapter05
| | 00:17 | folder of your Exercise Files archive.
| | 00:20 | This PivotTable contains monthly sales
data for five different departments, and
| | 00:24 | what I'd like to do is use color to
indicate how large values in the PivotTable
| | 00:29 | are in comparison to other values.
| | 00:32 | Specifically, the values that I want to
work with are in cells B5 through D9.
| | 00:37 | Those are the individual data
elements within the PivotTable.
| | 00:41 | I'm avoiding the grand totals here in
column E, and also in row 10. That means
| | 00:49 | all the values that are being compared
to each other are monthly sales values, as
| | 00:53 | opposed to summaries, which
will usually be much larger.
| | 00:56 | To create the color scale conditional
format, I'll select the cells, and then
| | 01:00 | on the Home tab of the ribbon in the
Styles group, I'll click Conditional Formatting.
| | 01:05 | From the menu that appears, I'll point
to Color Scales, and now I can click the
| | 01:09 | color scale that I want to use.
| | 01:11 | The first six color scales are three-color
color scales, and the last three are
| | 01:16 | two-color color scales.
| | 01:17 | So, let's start out simple, and work with
one of the two-color scales, and down at
| | 01:22 | the bottom, I'll start
with green and white.
| | 01:26 | So, I'll click that from the palette,
and click outside of the selection, so that
| | 01:29 | you can see it without any highlighting.
| | 01:32 | This two-color color scale displays the
largest values with a green background,
| | 01:37 | or a green fill color for those cells, and
you can see the largest value -- 130,415 --
| | 01:43 | has a dark green background.
| | 01:45 | By contrast, the cell with the smallest
value -- 11,730 -- has a white fill, or white
| | 01:51 | background, and various cells in
between, those two values, show various
| | 01:56 | saturations of green.
| | 01:58 | Basically, the darker the green, the
larger the value. That's what a two-color
| | 02:01 | color scale looks like.
| | 02:03 | If you want to create a three-color
color scale, you can do so.
| | 02:06 | I'll remove this one by clicking the
Undo button, and then with the cells
| | 02:10 | selected again, click Conditional Formatting,
point to Color Scales, and now I'll
| | 02:15 | select a three-color color scale, and
I'll click the green, white, and red.
| | 02:21 | So again, the largest values will be
green, but this time middle values will be
| | 02:25 | white, and low values will be red.
| | 02:28 | So, I'll click that button, and you can see
how the conditional format has been applied.
| | 02:32 | This time the largest value -- 130,415 -- has
a green fill, the smallest value -- 11,730 --
| | 02:41 | instead of being white as it was before
now has a dark red background, and middle
| | 02:46 | values -- 65,561 -- is one about in the
middle of this range is in white.
| | 02:52 | Values on the lower side of average
but not quite at the bottom are tinted in
| | 02:56 | red and various shades of pink,
whereas values that are slightly higher than
| | 03:01 | average have various tints of green.
| | 03:05 | And that's how you add a color scale
conditional format to your PivotTables.
| | 03:08 | They are a great way to indicate the
relative magnitude of different values
| | 03:12 | using color, and you can use either two
colors, or three color scales; whichever
| | 03:17 | best suits your purpose.
| | Collapse this transcript |
| Formatting cells using icon sets| 00:00 | If you've ever worked with a business
dashboard, or a scorecarding application, you
| | 00:05 | might have seen icons, such as traffic
lights in red, yellow, and green, used to
| | 00:09 | indicate levels of performance.
| | 00:11 | In this movie, I will show you how to
add those indicators to your Excel 2013
| | 00:16 | PivotTables using icon sets.
| | 00:17 | I'll use the IconSets sample workbook,
which you can find in the Charter05 folder
| | 00:23 | of your Exercise Files archive.
| | 00:25 | I have this PivotTable set up, so that it
shows summary sales data for five departments;
| | 00:30 | Balance, Dance, Play, Sport, and Work.
| | 00:33 | What I would like to do is add an icon
to cells B4 through B8 to indicate the
| | 00:41 | level of performance based on some
criteria that I'll define in a moment.
| | 00:45 | To do that, I select the cells, and
then on the Home tab of the ribbon, in the
| | 00:50 | Styles group, I'll click Conditional
Formatting, point to Icon Sets, and then
| | 00:54 | select the shapes that I want to use.
| | 00:56 | You have different types of sets that
you can use. Some of them have three
| | 01:00 | items, some of them have
four, and some of them have five.
| | 01:05 | In this case, I'll go with what I
consider to be one of the more useful icon
| | 01:09 | sets, which is the three
icon bordered traffic lights.
| | 01:14 | So, I'll go ahead and click that icon
set inside to the palette, and doing so
| | 01:20 | adds it to the PivotTable.
| | 01:22 | And you can see, based on the default
icon set that Excel created, I only have
| | 01:27 | two different colors; I have green for
three of the cells, and red for the other two.
| | 01:32 | If I want, I can redefine the criteria
that decides which of the three red,
| | 01:37 | yellow, and green icons to display.
| | 01:40 | To do that, I'll select the cells,
then on the Home tab, click Conditional
| | 01:44 | Formatting, and click Manage Rules.
| | 01:47 | Doing so displays the Conditional
Formatting Rules Manager, and I can click
| | 01:51 | the rule that I want to edit; in this
case, it's the only one in the dialog
| | 01:55 | box, and click Edit Rule.
| | 01:57 | In the Edit Formatting Rule dialog box, you
can see the details of the rule that you created.
| | 02:03 | In this case, you have two separate rules;
| | 02:05 | the first one tells Excel when it
should display a green traffic light.
| | 02:10 | The rule type is Percent, you can see
that here in the Type box, and any value
| | 02:15 | that is greater than or equal to 67%
of the highest value gets a green icon,
| | 02:21 | anything less than 67% but greater
than or equal to 33% of the largest value
| | 02:28 | gets yellow, and anything
below 33% gets red.
| | 02:32 | Let's say that I want to use an
absolute number instead of a percentage.
| | 02:36 | To do that, I can change the Type from
Percent to Number, so I'll click the down
| | 02:42 | arrow next to the first
Type box, and click Number.
| | 02:44 | When I do, the number in the Value box
resets to 0, and in this case, I want to
| | 02:50 | assign green to any sales
value that is greater than 275,000.
| | 02:57 | So, I'll select the value in the
green icon's rule, and type 275000.
| | 03:04 | Now I can do the same thing for yellow,
so I'll click the second criterion's
| | 03:09 | Type box's down arrow, click Number,
and then in the second row's Value box,
| | 03:14 | I'll type in 100000.
| | 03:16 | So, now with the rule set up the way
it is, any cell that contains a value
| | 03:20 | greater than 275,000 will get a
green icon, any cell with a value below
| | 03:25 | 275,000, but greater than or equal to 100,000
will get yellow, and everything else will get red.
| | 03:31 | I can apply the rule by clicking OK.
That gets me out of the editing dialog box.
| | 03:35 | Click OK again, and when I do, Excel
applies the rule to the PivotTable.
| | 03:40 | Now I have two cells those over 275,000
that get a green icon, anything between
| | 03:47 | 275,000 and 100,000 gets yellow, and
anything below 100,000 gets red.
| | 03:51 | Icon Sets are a terrific
conditional format for summarizing data.
| | 03:56 | You can use them for PivotTables, but
you'll also find that they're extremely
| | 03:59 | useful for dashboarding and scorecarding
worksheets, even outside of PivotTables.
| | 04:04 | I encourage you to learn as much as
you can about them, and to work with
| | 04:08 | them whenever possible.
| | Collapse this transcript |
| Editing a conditional formatting rule| 00:00 | After you create a PivotTable conditional
format, you're not stuck with it.
| | 00:05 | You can change any aspect of it
that you want by editing the rule.
| | 00:08 | In this movie, I will show you how to
add as a conditional format that you've
| | 00:13 | applied to a PivotTable.
| | 00:14 | As my sample file, I'll use the
EditingRules workbook that you can find in the
| | 00:20 | Chapter05 folder of your
Exercise Files archive.
| | 00:23 | In this workbook, my PivotTable contains
a conditional format in cells B4 to B8.
| | 00:30 | If I want to change the rules that
I've applied, I can select the cells,
| | 00:34 | and then on the Home tab of the
ribbon in the Styles group, click the
| | 00:38 | Conditional Formatting button, and
then at the bottom of list that appears,
| | 00:42 | click Manage Rules.
| | 00:44 | Doing so applies all the rules that
have been applied to the selected cells.
| | 00:48 | In this case, is there's only one;
| | 00:50 | that is the Graded Color Scale.
| | 00:53 | So, I click it highlight it in blue,
and then click the Edit Rule button.
| | 00:58 | Doing so displays the rules in
the Edit Formatting Rule dialog box.
| | 01:02 | Here you can change any
aspect of the rule that you want.
| | 01:04 | For example, you could change the cells
to which the rule is applied out of the
| | 01:09 | Selected Cells, All cells showing "Sum
of Sales" values, which would include any
| | 01:15 | grand totals or subtotals, or just
All cells showing "Sum of Sales" values for
| | 01:20 | "Department", which in this case is
exactly the same as the selected cells.
| | 01:25 | Those would just be the values in the body
of the PivotTable; namely, cells B4 through B8.
| | 01:31 | You can also change the rule Type.
| | 01:33 | In this case, the type is to
Format all cells based on their values.
| | 01:36 | You could also Format only cells
that contain certain types of values.
| | 01:40 | These are the rule-based formats
that I spoke about in another movie.
| | 01:44 | So, you can find values that are
greater than a certain value, less than a
| | 01:48 | certain value, and so on.
| | 01:50 | You can also create top and bottom
rules, format cells that are above or
| | 01:54 | below average, and so on.
| | 01:56 | In this case we'll stay
with the format based on value.
| | 01:58 | Then, in the bottom section, the Edit
the Rule Description section, you can
| | 02:03 | select what type of format
you want to apply.
| | 02:06 | These cells currently
have a 3-Color Scale applied.
| | 02:09 | As you can see, the controls in this
section allow me to affect what colors are
| | 02:14 | applied, and when to apply them.
| | 02:16 | So, for example, for the
lowest value, we can have red.
| | 02:20 | For the midpoint, in other words, its
50th percentile, would apply yellow, and a
| | 02:24 | maximum, which is a highest
value, would be green.
| | 02:27 | Just this is an example of how I can
change it, I'll change the maximum color
| | 02:32 | from green to a medium blue, and I'll
click OK, just to show you what it looks
| | 02:37 | like in the body of the PivotTable.
| | 02:39 | Click OK there, click OK to close the
Rules Manager, and that's how it would
| | 02:44 | look if I applied that particular
3-Color Scale to this PivotTable.
| | 02:48 | Now I'll click Undo to undo my change,
and then, with the cells still selected,
| | 02:54 | click Conditional Formatting, and
Manage Rules, Graded Color Scale, and Edit.
| | 02:59 | I can also change the type
of format that's supplied.
| | 03:02 | To do that, I'll click the Format Styles
button's down arrow, and you see that I
| | 03:07 | can select a 2-Color Scale,
3-Color Scale, Data Bar, or Icon Sets.
| | 03:12 | In this case, I'll switch to 2-Color
Scale, and the scale takes the lowest and
| | 03:18 | highest value colors.
| | 03:19 | So, in this case, red and green. And
let's say that I want to change it from
| | 03:23 | yellow, and go up to green.
| | 03:25 | To do that, I'll click the Color
field's down arrow on the left side for the
| | 03:29 | minimum value, and click the yellow
swatch here in the Standard Colors area of
| | 03:33 | the pane that appears, and click OK,
and click OK again to apply the change.
| | 03:39 | That's how the change of the conditional
format would look in this PivotTable.
| | 03:43 | Editing a conditional format
is very much like creating a new
| | 03:46 | conditional format.
| | 03:47 | All you need to do is select the rule
that you want change, apply the settings
| | 03:51 | that you want, click OK twice,
and you're done.
| | Collapse this transcript |
| Controlling how multiple rules are applied| 00:00 | Applying a conditional format to your
PivotTable lets you emphasize certain
| | 00:04 | aspects of your data
using visual elements.
| | 00:07 | If you want to format your PivotTable
with multiple conditional formats, you
| | 00:10 | can, and you can control
how and when they're applied.
| | 00:14 | In this movie, I will show you
techniques to manage how and when you're
| | 00:17 | conditional formats are applied. I will
use the Multiple sample file, which you
| | 00:22 | can find in the Chapter05 folder
of your Exercise Files archive.
| | 00:26 | The first thing you need to do is
select the cells that contain the formats
| | 00:31 | you want to work with.
| | 00:32 | So, I'll select cells B4 through B8, and
then on the Home tab of the ribbon, click
| | 00:38 | the Conditional Formatting
button, and click Manage Rules.
| | 00:42 | Doing so opens the Conditional
Formatting Rules Manager dialog box.
| | 00:46 | The first rule is the Color
Scale that goes from white to yellow;
| | 00:50 | lowest value to highest value, and the
second rule is one that displays any cells
| | 00:55 | value below 175,000 in red text.
| | 00:59 | As you can see from cells B5,
and B6, both rules are applied.
| | 01:05 | If you're upgrading from Excel
2003 or an earlier version, then you've
| | 01:09 | worked with a system that only allowed one
conditional format to be applied at the time.
| | 01:13 | Starting with 2007, and moving up to
Excel 2013, you can have multiple conditional
| | 01:18 | formats applied to specific cells.
| | 01:21 | Now, let's say that I want to change the
order in which these rules are applied.
| | 01:26 | To do that, I can click the rule
that I want to move up or down.
| | 01:30 | In this case, I'll move the
Cell Value less than 175,000 rule.
| | 01:35 | So, I'll click it, and then along the
button bar here, or the toolbar inside the
| | 01:40 | Conditional Formatting Rules
Manager dialog box,
| | 01:43 | I can click Move Up, or Move Down.
| | 01:47 | In this case, the Move Down button is
grayed out, because there's no rule below
| | 01:50 | the Cell Value less than 175,000 rule.
| | 01:54 | But with that rules selected like that, I
can click the Move Up button, and doing so
| | 01:58 | moves it up in the order,
| | 01:59 | so Excel will apply that rule first.
| | 02:02 | When I click OK, you can
see that there's no change.
| | 02:05 | That's because the rules
aren't in conflict.
| | 02:07 | Excel applies the color scale to all of
the cells that were selected, and it also
| | 02:12 | applies red formatting to cells B5 and B6.
| | 02:15 | Now, if I select all the cells again,
and reopen the Conditional Formatting
| | 02:20 | Rules Manager, I can choose to have
Excel stop looking to see if conditional
| | 02:25 | formats apply by checking the rules Stop If
True checkbox, which is over here on the right.
| | 02:31 | So, I'll do that for the first row,
which is the red text formatting rule.
| | 02:35 | So, what will happen now is that
Excel will check to see if the cell value
| | 02:40 | is less than 175,000.
| | 02:42 | If it is, it will format that cells
text in red, and then it will stop.
| | 02:47 | So, if I did this correctly, it will not
apply the Graded Color Scale from white
| | 02:52 | to yellow to cells B5 and B6.
| | 02:54 | I'll go ahead and click
OK, and click to the side,
| | 02:57 | and you can see that cells B5 and
B6 do not contain any yellow at all.
| | 03:04 | Before, there was almost none in B5, and
a little bit in B6, and this time there
| | 03:09 | is absolutely none.
| | 03:11 | That's how use the tools in the Conditional
Formatting Rules Manager dialog box
| | 03:15 | to affect how and when individual
rules are applied your PivotTable.
| | Collapse this transcript |
| Deleting a conditional formatting rule| 00:00 | Conditional formats help make your
PivotTable data easier to understand,
| | 00:04 | but there will probably be times
when you want to delete one or more
| | 00:07 | conditional formats.
| | 00:09 | In this movie, I will show
you how to do that.
| | 00:11 | I will use the Delete sample file, which
you can find in the Chapter05 folder of
| | 00:16 | your Exercise Files archive.
| | 00:18 | This PivotTable contains two conditional
formats, which you can see by selecting
| | 00:22 | the cells in the body of the PivotTable,
| | 00:25 | those are cells B4 through B8, and
then on the Home tab of the ribbon, in the
| | 00:30 | Conditional Formatting
group, clicking Manage Rules.
| | 00:34 | Doing so displays the Conditional
Formatting Rules Manager dialog box, and you
| | 00:38 | can see the two rules: a color scale,
and also conditional format that displays
| | 00:44 | in red any the cell value
that's less than 175,000.
| | 00:48 | If you want to delete a rule, all you
need to do is select it, like I'll do here
| | 00:53 | by clicking the less than 175,000 rule,
and then on the toolbar in the dialog
| | 01:00 | box, click the Delete Rule button.
| | 01:02 | When you do, Excel removes the rule, and
then you can either keep the change by
| | 01:06 | clicking OK, close the dialog box
without deleting the rule by clicking Cancel,
| | 01:12 | or click Apply to leave the
dialog box open, and delete the rule.
| | 01:16 | In this case, we don't have
anything else to do in the dialog box,
| | 01:19 | so I'll go ahead and click OK.
| | 01:20 | When I do Excel, removes the rule.
| | 01:23 | Now let's say that you want to remove
every conditional format applied to
| | 01:27 | the current PivotTable.
| | 01:28 | To do that, click any cell in the
PivotTable, and then on the Home tab of the
| | 01:33 | ribbon, click the Conditional Formatting
button, and then point to Clear Rules.
| | 01:38 | It's the next to last item.
| | 01:39 | Then in the flyout list that appears,
click Clear Rules from this PivotTable.
| | 01:45 | So, clicking this item, Clear Rules form
this PivotTable, would remove every rule
| | 01:49 | that was applied; not just one.
| | 01:52 | So, I will go ahead and click it, and you
can see that Excel removed the remaining
| | 01:56 | conditional format from the PivotTable.
| | 01:58 | So, just to recap, if you want to
remove a single conditional format for a
| | 02:02 | PivotTable, select the cells in the
PivotTable, and then use the Conditional
| | 02:06 | Formatting Rules Manager dialog box to
select the rule you want to delete, or if
| | 02:11 | you want remove every conditional
formatting rule form a PivotTable, go to the
| | 02:15 | Home tab, click Conditional Formatting,
point to Clear Rules, and then select
| | 02:19 | all rules from this PivotTable.
| | Collapse this transcript |
|
|
6. Creating and Manipulating PivotChartsCreating a PivotChart| 00:00 | So far in this course, I have covered
PivotTables, which arrange numerical data
| | 00:05 | inside of a flexible analytical tool,
| | 00:08 | but you can also create PivotCharts,
which, as the name implies, are dynamic
| | 00:12 | charts that you can use to
summarize your data visually.
| | 00:15 | In this movie, I'll show you how to create a
PivotChart to summarize your data visually.
| | 00:20 | I'll use the CreatePivot sample file,
which you can find in the Chapter06 folder
| | 00:25 | of your Exercise Files archive.
| | 00:27 | To create a PivotChart, your data should
be laid out in a list, or ideally, in an
| | 00:32 | Excel table, exactly as you
would do for a PivotTable.
| | 00:37 | You can see that the data here
is laid out in an Excel table.
| | 00:40 | I have columns with labels,
and then also rows of data.
| | 00:45 | If I scroll down using my mouse's scroll wheel,
you can see that there are no blank rows.
| | 00:50 | That means all of the data in this
Excel table will be included in the
| | 00:54 | PivotChart when I create it.
| | 00:56 | To create the PivotChart, just click any
cell in the body of your data source, and
| | 01:01 | then on the Insert tab,
click the PivotChart button.
| | 01:05 | Doing so displays the
Create PivotChart dialog box.
| | 01:09 | You can make sure that Excel
has identified the proper range.
| | 01:13 | In this case, it's Table2, which is the
name of the table that I have here on the
| | 01:17 | left, and I want to create
it on a new worksheet,
| | 01:20 | so I will leave the New
Worksheet button selected.
| | 01:23 | With all that in place, I can click OK,
and Excel creates a new worksheet with a
| | 01:28 | PivotTable, and an associated PivotChart.
| | 01:31 | Now I can us the techniques that I used before
for a PivotTable to add fields to the PivotChart.
| | 01:36 | To do that, I'll use the controls in the
PivotChart Fields pane, which you see on
| | 01:41 | the right side of the screen.
| | 01:43 | I have three fields:
Department, Quarter, and Sales.
| | 01:46 | I will add Department to the Legend or
Series area; that corresponds to the columns.
| | 01:52 | I'll add Quarter to the Axis or
Categories area; that corresponds to the rows.
| | 01:59 | Then I'll add Sales to the Values area.
| | 02:02 | Doing so adds the Sales data to the
body of the PivotChart, and you could see
| | 02:06 | that the chart, by default, is a bar
chart, and each of the five departments,
| | 02:11 | because they're in the Legend (Series), is
identified by a vertical bar or column
| | 02:16 | within each quarter.
| | 02:18 | So, we have quarters 1, 2, 3, and 4,
and then the Departments are identified
| | 02:22 | here in Legend by color.
| | 02:24 | That's how you create a PivotChart.
| | 02:26 | It's a very similar process
to creating a PivotTable.
| | 02:29 | You just make sure that you click
the PivotChart button, as opposed to
| | 02:32 | the PivotTable button.
| | Collapse this transcript |
| Pivoting a PivotChart| 00:01 | PivotCharts, like a PivotTables, let
you dynamically rearrange your data.
| | 00:05 | In this movie, I will show you how to
pivot a PivotChart, so that you can change
| | 00:09 | how the chart summarizes your data.
| | 00:12 | I will use the Pivoting sample workbook,
which you can find in the Chapter06
| | 00:16 | folder of your Exercise Files archive.
| | 00:19 | When you're first open the file,
you'll probably see that cell A1 in the
| | 00:24 | PivotTable is selected.
| | 00:26 | That means that instead of seeing
the PivotChart controls, you'll see the
| | 00:30 | PivotTable Fields pane over on the
right side of the program window.
| | 00:34 | If you pivot the PivotTable that supplies
the values for PivotChart, then the
| | 00:40 | PivotChart changes as well.
| | 00:41 | For example, if you drag the Quarter
field from the Columns area to the Rows
| | 00:46 | below Department, then the PivotChart
changes. I'll press Ctrl+Z to undo the change.
| | 00:53 | In this case, because I'm focusing on
PivotCharts, I'll click the PivotChart, so
| | 00:58 | that I get the PivotChart tools
contextual tabs on the ribbon, and also the
| | 01:02 | PivotChart Fields pane on the
right side of the program window.
| | 01:07 | Let's say that I want to rearrange the
PivotChart, so that it displays Department
| | 01:12 | as the Series; in other words, where the
Quarters currently are, and to with the
| | 01:17 | Quarters along the horizontal Axis.
| | 01:19 | To do that, all need to do is drag the
Quarter field to the Axis area, and drag
| | 01:25 | the Department field to
the Legend or Series area.
| | 01:30 | Doing so displays the departments as
bars within each of the four quarters.
| | 01:34 | Of course, I can remove a field.
| | 01:36 | For example, if all I cared about was
total sales by quarter, then I could remove
| | 01:41 | the Department field by dragging it
from the Legend area, up to the PivotChart
| | 01:49 | Fields area, and Excel rearranges the
PivotChart, so that it only displays sales
| | 01:56 | data in total for each of the quarters.
| | 01:59 | If I want to put the Department field
back in the Legend area, all I need to
| | 02:04 | do is drag it down, and Excel re-creates
the PivotChart, based on the new arrangement.
| | 02:09 | Pivoting a PivotChart lets you rearrange
your data visually, and it can make for
| | 02:13 | some very striking comparisons.
| | 02:15 | As with every aspect of PivotTables and
PivotCharts, take the time to experiment
| | 02:20 | for the look that
explains you data as best it can.
| | Collapse this transcript |
| Filtering a PivotChart| 00:00 | PivotCharts let you summarize
large data collections visually.
| | 00:03 | That's great, because it gives you a way
to look at the numbers, without focusing
| | 00:08 | on individual values.
| | 00:09 | But the problem is that
your charts can be very busy.
| | 00:12 | That means that there will be times
when you want to filter or limit the
| | 00:16 | values that appear inside of a PivotChart, and
in this movie, I will show you how to do that.
| | 00:21 | As my sample file I'll use the
Filtering workbook, which you can find in
| | 00:25 | Chapter06 folder of your
Exercise Files archive.
| | 00:27 | So, this PivotChart, which I'll click,
summarizes quarterly sales data for five
| | 00:33 | different departments.
| | 00:34 | Lets say that I only want to
display values for the Balance and Dance
| | 00:39 | departments for quarters 2 and 3.
| | 00:41 | If I want to filter PivotChart,
I have two different options.
| | 00:45 | The first is that if I have the
PivotChart Fields pane displayed at the
| | 00:49 | right side of the program window, I can
move the mouse pointer over one of the headers;
| | 00:55 | in this case, the Department field.
| | 00:57 | Click the downward pointing black
arrow, and then use the filter interface to
| | 01:01 | limit the values that appear.
| | 01:03 | So, let's say that I want to
display the Balance and Dance fields.
| | 01:08 | To do that, I'll clear the Select All
checkbox; that removes the checks from all
| | 01:14 | five of the fields, and then I'll check the
boxes next to Balance, and Dance. Then click OK.
| | 01:20 | Doing so filters the values in the charts,
| | 01:22 | so I only see sales for all
quarters for those two departments.
| | 01:27 | You can also see that a filter indicator
appears here next to the name of the
| | 01:32 | Department field in the
PivotChart Fields task pane.
| | 01:36 | The other way that you can filter values
inside of a PivotChart is by using the
| | 01:40 | controls on the PivotChart itself.
| | 01:42 | So, for example, if you're PivotChart
takes up your entire screen, and
| | 01:46 | you've had to hide the PivotChart Fields
pane, then you can filter using this technique.
| | 01:51 | All you need to do is click the control
on the PivotChart with the label of the
| | 01:55 | field you want to filter.
| | 01:56 | In this case, it's Quarter.
| | 01:58 | So, click it, and I got exactly the
same filter interface that I did in the
| | 02:02 | PivotChart Fields pane.
| | 02:03 | I want to display the
values for quarters 2 and 3,
| | 02:07 | so as before, I'll clear Select All,
check the box next to quarter 2, the box
| | 02:12 | next to quarter 3, and click OK.
| | 02:14 | Now I have filters applied to both
the Department and Quarter fields.
| | 02:18 | All of the data is still there; it's
just I have limited what's displayed.
| | 02:21 | If I want to remove the filter, I can
click the control that I used to create it.
| | 02:25 | In this case, I'll remove the
filter from the Quarter field.
| | 02:28 | To do that, just click the control, and
then click Clear Filter from Quarter, or
| | 02:33 | whatever the field is.
| | 02:34 | I can also remove the
filter from Department,
| | 02:37 | so I click the Department control,
and click Clear Filter from Department.
| | 02:41 | When I do, Excel restores the PivotTable,
displaying all of this data, and
| | 02:45 | removing both filters.
| | Collapse this transcript |
| Formatting a PivotChart| 00:00 | PivotCharts let you
summarize your data visually,
| | 00:03 | so that means that color and
layout are extremely important.
| | 00:06 | In this movie, I will show you how to
change the formatting of your PivotChart
| | 00:11 | elements, so that you can
best communicate your data.
| | 00:14 | I will use the Formatting sample file,
which you can find in the Chapter06 folder
| | 00:19 | of your Exercise Files archive.
| | 00:21 | The first thing you need to do is click
your PivotChart if it isn't already selected.
| | 00:25 | Doing so in Excel 2013 displays two new
items, and those are action buttons here
| | 00:32 | at the top right corner
of the PivotChart.
| | 00:34 | The first one is the Chart Elements
button, which I'll cover in another movie, and
| | 00:38 | the second is the Chart Styles button.
| | 00:41 | That's the one we will work with here.
| | 00:43 | Clicking the Chart Styles button
displays a pane with two different tabs.
| | 00:48 | The first one is Style.
| | 00:49 | The second is Color.
| | 00:50 | On the Style tab, you can see that
there are different styles that you can
| | 00:55 | apply to your PivotTable.
| | 00:56 | For example, you can scroll down through
the list, or the gallery, and see if
| | 01:00 | there is a style you'd like
to apply to your PivotChart.
| | 01:03 | Just for contrast, I'll click the
Sstyle that has a black background.
| | 01:07 | So, I will click that.
| | 01:09 | Doing so it applies that
new style to the PivotChart.
| | 01:11 | If I want, I can also
select a different color scheme.
| | 01:15 | To do that, I'll click Color, and I
can select any one of these palettes.
| | 01:19 | So, I have Colorful, I
have Monochromatic, and so on.
| | 01:23 | If I want to see what it would look like
to apply one of those new color schemes,
| | 01:27 | all I have to do is hover the mouse
pointer over one of them, and if I were to go
| | 01:32 | down here, I would have Color 3,
and hover down here Color 4.
| | 01:35 | I can also go down to the Monochromatic
area, and I can see what it would look
| | 01:40 | like with various shades of gray, various
shades of yellowish orange, blue, and so on.
| | 01:46 | In this case, I won't apply any of the
new color schemes, and that means that I
| | 01:50 | can click the Chart Styles
button to close the panes.
| | 01:53 | You can also format
individual chart elements.
| | 01:56 | To do that, click any element, say, for
example, the legend area, and then on the
| | 02:03 | PivotChart Tools contextual
tabs, click Format.
| | 02:05 | Now you can use the tools here
to change the formatting of that
| | 02:09 | particular element.
| | 02:10 | So, let's say, for example, that I want to
apply a different fill to the legend.
| | 02:15 | To do that, with the Legend selected,
and I am on the Format tab, I click Shape
| | 02:19 | Fill, and in this case, I'll give it a
slightly different color of gray, just to
| | 02:25 | make sure it stands out.
| | 02:26 | If you want to select a chart element,
but you're not sure what it is, or what
| | 02:30 | you need todo to click it, then you can
click the Format tab, and then in the Current
| | 02:35 | Selection group, click the Chart
Elements list box's down arrow.
| | 02:39 | Doing so displays a list of all
the items that you can select.
| | 02:43 | For example, if you want to change the
formatting only of series 1, you can click that.
| | 02:49 | Doing so causes Excel to select all of
the items in series 1, which is quarter 1
| | 02:56 | for the Balance, Dance, Play,
Sports, and Work departments.
| | 03:02 | Now you can use the tools on the Format
contextual tab to change the formatting
| | 03:06 | for this data series.
| | 03:07 | So, for example, if you wanted to select
a different color of blue, or different
| | 03:11 | shade of blue, you could click the Shape
Fill button, and then select a slightly
| | 03:16 | different color of blue.
| | 03:18 | Like I said, you can do that for
any element in your PivotChart.
| | 03:22 | Formatting PivotCharts is very
much like formatting regular chart.
| | 03:25 | You can format the entire chart at
once using the Chart Styles control, or you
| | 03:30 | can select an individual chart element,
and you can use the controls on the Format
| | 03:34 | contextual tab of the ribbon
to make any changes you want.
| | Collapse this transcript |
| Changing a PivotChart's layout| 00:00 | When you create a PivotChart in Excel
2013, Excel creates a column chart, and
| | 00:05 | also applies a standard layout.
| | 00:08 | In this movie, I will show you how to
change that chart's layout, both for the
| | 00:12 | entire chart as a whole, and
also for individual elements.
| | 00:16 | I'll use the Layout sample file, which
you can find in the Chapter06 folder of
| | 00:21 | your Exercise Files archive.
| | 00:23 | This workbook contains a PivotChart,
which I'll click now to activate, and also
| | 00:28 | an associated in PivotTable.
| | 00:30 | This chart has a particular layout,
meaning that it has a legend over here on
| | 00:35 | the right, and it also has labels
on the horizontal and vertical axes.
| | 00:40 | If you want, you can change the overall
layout for the PivotChart by clicking the
| | 00:44 | chart, and then clicking the
Design contextual tab on the ribbon.
| | 00:48 | Then over in the Chart Layouts
area, clicking the Quick Layout button.
| | 00:54 | Doing so displays a list of predefined
layouts that you can use for this style of chart.
| | 00:59 | Any time that you hover your mouse
pointer over one of the layouts, it displays
| | 01:04 | a live preview for what it would look
like if you were to apply that particular
| | 01:09 | layout to your chart.
| | 01:11 | I particularly like Layout number 7.
| | 01:13 | It has the minor axes within the
background, and it also displays axis titles
| | 01:18 | that you can modify.
| | 01:20 | So, I'll go ahead and click Layout 7
in the dropdown menu that appears.
| | 01:25 | Doing so it closes the menu, and
applies that layout to the chart.
| | 01:29 | Now I can edit various chart elements.
| | 01:31 | For example, if I want to edit the
Axis Title, all I have to do is click it,
| | 01:37 | and then select it.
| | 01:38 | I'm working here in the horizontal axis.
| | 01:41 | These are departments,
| | 01:42 | so I'll just go ahead and type in Department,
and click outside of the Axis Title box.
| | 01:51 | I can do the same thing
for the vertical axis.
| | 01:53 | So, I'll just click the box here, and
select the text within it, and those are sales,
| | 01:59 | ao I'll just type in Sales, and click
outside of the Axis Title box to release
| | 02:05 | the selection, and to finalize my change.
| | 02:07 | I can also display or
hide various chart elements.
| | 02:11 | To do that, with the PivotChart
selected, I point to the Chart Elements button,
| | 02:17 | which is new in Excel 2013, and click
it to display a list of chart elements
| | 02:22 | that are available for me.
| | 02:23 | Any element that has a check in the
box next to its name is displayed.
| | 02:28 | Any that is blank is not displayed.
| | 02:30 | So, let's say, for example, that I
wanted to display a chart title.
| | 02:34 | For that, I can check the Chart Title box,
and then click the right pointing arrow
| | 02:40 | that appears next to its name.
| | 02:42 | I can select where I want to chart title
to appear; above the chart, centered on
| | 02:46 | the overlay, or open the associated
task pane by clicking more options.
| | 02:51 | In this case, I'll stay with having the chart
title above the chart title above the chart,
| | 02:56 | so I'll just click it, and then click
the Chart Elements button again to release
| | 03:00 | the selection to hide the elements list.
| | 03:02 | Now I can edit the chart title the same
way that I edit the axis title, and for
| | 03:06 | this, I'll just call it Departmental
Sales, and click outside to stop editing.
| | 03:11 | Changing a PivotChart's layout lets
you optimize the chart to display and
| | 03:15 | summarize the data within it.
| | 03:17 | In most cases, the default layout
will be fine, and you can always add any
| | 03:21 | individual elements that you want using
the chart elements tool at the top right
| | 03:25 | corner of the PivotChart itself.
| | Collapse this transcript |
| Changing a PivotChart's chart type| 00:00 | When you create a PivotChart in
Excel 2013, Excel creates a column chart
| | 00:04 | like the one shown here.
| | 00:06 | In this movie, I will show you how
to change the type of a PivotChart in
| | 00:10 | your Excel workbook.
| | 00:11 | I'll use the ChartType sample file, which
you can find in the Chapter06 folder of
| | 00:17 | your Exercise Files archive.
| | 00:19 | This PivotChart summarizes data
based on quarterly sales for five
| | 00:23 | different departments.
| | 00:24 | A column chart summarizes this data
perfectly well, but it's time series data.
| | 00:28 | In other words, it does quarter by
quarter along the horizontal axis,
| | 00:33 | so a line chart, which works well for
time series data ,could also be used.
| | 00:38 | Let's say that I do want to change the PivotChart's
type from a column chart to line chart.
| | 00:43 | To do that, I click any part of the
PivotChart, and then on the Design contextual
| | 00:48 | tab, in the Type group, click
the Change ChartType button.
| | 00:53 | Doing so displays the
Change Chart Type dialog box.
| | 00:58 | Now I can select the type
of chart that I want to use.
| | 01:01 | In this case, I will make it a Line
chart, and I can select the chart subtype.
| | 01:05 | For example, I can have a centered line
chart, I can have a stacked line chart,
| | 01:09 | which shows how the data
adds up to a total, and
| | 01:13 | I can also add markers to
any one of the other types.
| | 01:16 | Let's say that I want to
create a line chart with markers.
| | 01:20 | I'll click that in the subtype
list at top, and I see my preview here.
| | 01:25 | If I hover the mouse pointer over
the preview, I see that in a larger size.
| | 01:29 | Everything looks good,
| | 01:30 | so I'll go ahead and click OK.
| | 01:33 | Doing so displays my data in a line chart.
| | 01:37 | Just like in the column chart, each one
of these colors here in the legend refers
| | 01:40 | to a different line, in
this case, within the chart.
| | 01:43 | So, I can see that the Balance department,
which has the blue circles, goes along this line.
| | 01:49 | Dance, which is dark orange here,
Sport, is yellow here, and so on.
| | 01:55 | If your line chart's a little
bit too busy, you can filter it.
| | 01:58 | For example, if I want to filter down
to just show the Play department, I can
| | 02:02 | click the Department control's down
arrow, clear Select All, click Play, click
| | 02:07 | OK, and my data is displayed
within the body of the PivotChart.
| | 02:10 | If I want to remove the filter, I can
add a press Ctrl+Z, or click the Department
| | 02:15 | Control, and click
Clear Filter from Department.
| | 02:18 | That's how you change the chart type
of a PivotChart in Excel 2013.
| | 02:22 | It's very easy to do,
| | 02:24 | so feel free to switch among various
chart types to see which one works best
| | 02:27 | for your data.
| | Collapse this transcript |
| Adding a trendline to a PivotChart| 00:00 | When you summarize time series data in
the PivotChart, you might want to add a
| | 00:04 | trend line to see what your data would look
like in the future if current trends continue.
| | 00:08 | In this movie, I will show you how to
add a trend line to your PivotChart, so
| | 00:12 | that you can make that type of forecast.
| | 00:14 | I'll use the Trendline sample file, which
you can find in the Chapter06 folder of
| | 00:18 | your Exercise Files archive.
| | 00:20 | This PivotChart summarizes quarterly
sales data for a number of departments in
| | 00:24 | the company, but I have filtered the
PivotChart, so that it only displays data
| | 00:28 | for one department; in this case, the
Sports department, just to simplify the
| | 00:32 | chart that we will create.
| | 00:34 | To add a Trendline to the PivotChart,
first you click the PivotChart, and then
| | 00:38 | click the Chart Elements button.
| | 00:40 | Doing so displays the Chart Elements
list, and you can go down to the Trendline
| | 00:45 | item, and check this box to display the
Trendline in the body of the PivotChart.
| | 00:50 | This Trendline shows what sales
would have looked like, if they continued
| | 00:53 | according to the average.
| | 00:55 | So, in other words, sales would have
increased, but you wouldn't have the
| | 00:58 | fluctuations between the quarters.
| | 01:00 | If you want to hide a Trendline,
then just clear the check box.
| | 01:03 | Now let's say that you want
to forecast into the future.
| | 01:06 | To do that, again, click Chart Elements to
show the list, go in the TrendLine, and
| | 01:11 | then click the arrow that
appears to the side of it.
| | 01:14 | Then in the flyout list that
appears, click More options.
| | 01:18 | Doing so displays the Format Trendline
dialog box, and you can see it adds a
| | 01:22 | Trendline to the body of the PivotChart.
| | 01:25 | The first thing we need to do is to
set the Trendline Options, and that means
| | 01:29 | choosing the data type.
| | 01:31 | Unless you're working with scientific
data, and you know that your data follows
| | 01:35 | one of the other distributions, such as
exponential, or power distribution, you
| | 01:39 | should leave linear selected.
| | 01:41 | It's by far the most common, and
pretty much all business data will follow a
| | 01:45 | linear distribution.
| | 01:47 | Then you can add forecast
elements to the Trendline.
| | 01:51 | To do that, scroll down, and in the
Forecast group, you can select how many periods
| | 01:56 | you want to look forward.
| | 01:57 | Remember that in this
PivotChart, each period is a quarter,
| | 02:00 | so let's forecast two
quarters into the future.
| | 02:03 | To do that, you edit the value in the
Forward box from 0.0 to 2, and press Enter.
| | 02:11 | You can see the change in the
PivotChart on the left side of the screen.
| | 02:15 | Now you can change the
Trendline's formatting.
| | 02:17 | To do that, move up and click the Fill
and Line icon just below Trendline Options.
| | 02:23 | Now you can make all sorts of changes
to the Trendline that you've added.
| | 02:27 | In this case, I'll just change the color.
| | 02:29 | So, I'll click the Color control,
and I'll change it to a bright red.
| | 02:34 | You can see that change in the body
of the PivotChart, and when I click the
| | 02:37 | Format Trendline pane's close
button, you can see the entire Trendline
| | 02:42 | extending out two quarters, and showing
what sales would look like if current
| | 02:45 | trends were to continue.
| | 02:46 | Like most forecasting tools, Trendlines
are best to give you a rough idea of
| | 02:51 | what your data might look
like if current trends continue.
| | 02:54 | There are no guarantees, of course, but
it's one way to look at your data, and it
| | 02:58 | might give you some useful insights.
| | Collapse this transcript |
|
|
7. Printing PivotTablesPrinting a PivotTable| 00:00 | PivotTables let you
reorganize your data dynamically,
| | 00:03 | so they are most useful when you're
displaying the data inside of a workbook, and
| | 00:08 | changing it around to
emphasize certain points.
| | 00:11 | That said, if you find a particular
arrangement that you like, you can print it
| | 00:15 | out, so that you have that arrangement
with you, even if you don't have the file.
| | 00:19 | In this movie, I will show you how
to print a PivotTable, both using the
| | 00:23 | standard printing method, and also to define
a print area, so all you print is the PivotTable.
| | 00:29 | I'll use the PrintPivot sample file,
which you can find in the Chapter07 folder
| | 00:34 | of your Exercise Files archive.
| | 00:36 | Printing a PivotTable, especially if it's
the only thing on the worksheet, is a very
| | 00:40 | straightforward process.
| | 00:41 | All you need to do is press Ctrl+P to
display the print page of the backstage view.
| | 00:48 | You can see the preview on the
right side of the screen over here.
| | 00:52 | You can change your various settings,
the printer you want to use, the number of
| | 00:56 | sheets, and so on, the number of
copies, and then click the Print button.
| | 01:00 | But let's say that there is something
else on the worksheet, and all you want
| | 01:04 | to print it is the PivotTable itself.
| | 01:05 | I'll show you how to do that now.
| | 01:07 | To go back to the worksheet, I'll click
the Go Back button at the top corner
| | 01:12 | of the backstage view, and now I'll
just add some text below the PivotTable in
| | 01:16 | cell B10, and say updated 31, 2013,
and press Tab.
| | 01:22 | Now if I were to press Ctrl+P, Excel would
also try to print that note in cell B10.
| | 01:28 | If all I want to do is print the
PivotTable, I can define a print area.
| | 01:32 | To do that, I'll click any cell in the
body of the PivotTable to activate it, and
| | 01:37 | now to select the entire PivotTable --
remember, there is a cell in the body of the
| | 01:42 | PivotTable currently selected --
| | 01:43 | I'll go up to the Analyze contextual
tab, and then in the Actions group, click
| | 01:48 | Select, and click
select Entire PivotTable.
| | 01:52 | Now I can go up to the Page Layout
tab of the ribbon, click the Print Area
| | 01:56 | button, and click Set Print Area,
| | 01:59 | and when I click away from this
selection, Excel has identified a print area
| | 02:04 | that consists of only the PivotTable
| | 02:07 | The marking doesn't stand out very
much, because the PivotTable has its own
| | 02:10 | border, but you'll see it in the moment
when I go to the backstage view again.
| | 02:13 | I'll do that by pressing Ctrl+P, and
you can see that, once again, Excel has
| | 02:19 | identified only the
PivotTable as the item to be printed.
| | 02:23 | If I click Go Back, and click Print Area,
and then click Clear Print Area,
| | 02:28 | undoing what I did before,
| | 02:30 | now when I press Ctrl+P, you can see
the Excel will print the text that was
| | 02:35 | underneath the PivotTable, even though I
didn't intend to print it, in this case.
| | 02:39 | That's the basics of
printing a PivotTable.
| | 02:41 | If it's the only thing on the
worksheet, you can print the worksheet.
| | 02:44 | If there are other worksheet elements
you don't want to print, just select the
| | 02:47 | entire PivotTable, set a print area
based on that selection, and you're good
| | 02:52 | to go.
| | Collapse this transcript |
| Printing headers at the top of each printed page| 00:00 | PivotTables let you analyze and
summarize large data collections.
| | 00:04 | That's great, it's makes your job much
easier, but it also means that if you
| | 00:08 | print a PivotTable, you might
need to use more than one page.
| | 00:11 | In this movie, I will show you how to
repeat headers at the top and left of a
| | 00:15 | PivotTable, so that your
printout is easier to understand.
| | 00:18 | I will use the PrintHeaders sample
file, which you can find in the Chapter07
| | 00:23 | folder of your Exercise Files archive.
| | 00:26 | This PivotTable is fairly large, and I
would have to print it on more than one page.
| | 00:30 | To show you what that looks like, I'll
press Ctrl+P to go to the print page of
| | 00:36 | the backstage view, and you can see that
I have my first page here, and if I use
| | 00:41 | my control at the bottom, I can go to
the next page, which is Page 2, and you can
| | 00:46 | see that the data continues,
but I don't have any headers.
| | 00:49 | So, if I didn't have the department
order memorized, I wouldn't know that the
| | 00:53 | first column was Balance, the second
was Dance, and so on. I'll click the Previous
| | 00:57 | Page button to go back.
| | 00:59 | What I'd like to do is to repeat the
header row here -- Balance, Dance, Play,
| | 01:04 | Sport, and Work -- on the
second page as well.
| | 01:07 | To do that, I'll click the Go Back button,
go back to the workbook, and then on the Page
| | 01:12 | Layout tab, I'll click
the Print Titles button.
| | 01:16 | Doing so displays the Sheet
tab of the Page Setup dialog box.
| | 01:20 | What I want to do is identify rows to
repeat at the top of each page, and in
| | 01:26 | those cases, as you can see in the
background, those would be rows 3, and 4.
| | 01:30 | To do that, I'll click in the Rows to
repeat at top box, and then click the
| | 01:35 | collapse dialog button here on the
right side of that box, and then I'll select
| | 01:40 | the rows 3 and 4 by clicking their row
headers, and dragging down, and you can see
| | 01:45 | that I have $3:$4 in the Page Setup box,
and I'll click the expand the dialog control,
| | 01:53 | and you can see that rows 3 and 4
appear in the Rows to repeat at top box.
| | 01:58 | In this case, I don't have any
columns that I need to repeat, so I can just
| | 02:02 | go ahead and click OK.
| | 02:04 | There's no obvious change in the body of
the worksheet, but if I press Ctrl+P to
| | 02:08 | go back to the Print page or the
backstage view, you can see that page 1 looks
| | 02:13 | as it did before, but now when I use
the next page control to go forward, you
| | 02:17 | can see that my print titles have
been repeated on the second page.
| | 02:21 | That means that my PivotTable data will be
much easier to understand when I print it out.
| | 02:26 | I don't have to remember the order of the
columns, because the labels are right there.
| | 02:30 | Repeating titles at the top or the left
of any printed page makes your data much
| | 02:35 | easier to understand.
| | 02:37 | If you need to print your PivotTable on
more than one page, I highly recommend
| | 02:40 | you add titles to make
PivotTable easier to read.
| | Collapse this transcript |
| Printing each item on its own page| 00:00 | Most of the data that you summarize in a
PivotTable can be divided into easy categories.
| | 00:06 | For example, it can be months, it can be
weeks within a month, or it can be departments.
| | 00:10 | In this movie, I'll show you how to print
individual items or categories on their own page.
| | 00:16 | I will use the PrintEachItem sample
file, which you can find in the Chapter07
| | 00:21 | folder of your Exercise Files archive.
| | 00:24 | This PivotTable contains weekly sales
data for a number of months for five
| | 00:29 | different departments.
| | 00:30 | What I'd like to do is print each
month's data on a separate page.
| | 00:34 | So, in other words, on the first
printed page, I want January, second printed
| | 00:38 | page, February, and so on.
| | 00:40 | Let's take a look at what the worksheet
would look like if I were to print it
| | 00:44 | out as it is right now.
| | 00:46 | To go the Print page, or the backstage
view, I'll press Ctrl+P, and you can
| | 00:50 | see the first page here, and when I click the
next page control, you can see the second page.
| | 00:56 | I should note that I used the technique
that I thought in the previous movie,
| | 01:00 | which was adding print titles, so that
the labels here -- Balance, Dance, Play,
| | 01:05 | Sport, and Work -- appear at
the top of each page.
| | 01:08 | So, if you're not sure how to do that,
I recommend you watch the preceding
| | 01:11 | movie in this chapter.
| | 01:13 | Now to go back to the task at hand,
which is to print each month on a separate
| | 01:17 | page, I'll click the Go Back button,
and now I'll right-click any one of the
| | 01:22 | values that I want to
print on a separate page.
| | 01:25 | For example, I want to print by month;
in this case, have each month's weeks on
| | 01:29 | its own printed page, so I'll right-click
the January value, which is a month, and
| | 01:34 | then from the shortcut menu
that appears, click Field Settings.
| | 01:38 | In the Field Settings dialog box ,
there is the Layout & Print tab, which I'll
| | 01:43 | click, and at the bottom of this tab,
you can see that there is an insert page
| | 01:49 | break after each item checkbox,
which I will select, and click OK.
| | 01:53 | You can probably see that there is a
slightly thicker line underneath week 5 for
| | 01:59 | January, slightly darker line
underneath week 4 for February, and so on.
| | 02:04 | And when I press Ctrl+P to go to the
backstage view, you can see that my first
| | 02:08 | printed page displays the weeks for
January. Click the next page control, which
| | 02:13 | will take me to pages 1 through 6.
| | 02:15 | Next page is February, March, April, May,
and June, and at the bottom of page 6 is
| | 02:22 | the grand total for all sales.
| | 02:25 | Using the Field Settings dialog box is a
terrific way to separate out your data.
| | 02:29 | You can print one element at a time,
so you can focus on exactly the data
| | 02:33 | you want to work with.
| | Collapse this transcript |
| Printing a PivotChart| 00:00 | PivotCharts summarize your data visually,
and once you find an arrangement that
| | 00:04 | you think communicates your meaning
clearly, you can feel free to print it out,
| | 00:08 | perhaps for use in an annual report, or
just to show if you don't have the file
| | 00:12 | with you when you're on the road.
| | 00:14 | In this movie, I will show you how to
print the PivotChart by itself, without
| | 00:18 | anything coming in from
the surrounding worksheet.
| | 00:20 | I will use the PrintChart sample file,
which you can find in Chapter07 folder of
| | 00:25 | your Exercise Files archive.
| | 00:27 | Printing a PivotChart is a
very straightforward process.
| | 00:30 | All you need to do is click the chart,
and then press Ctrl+P to go to the Print
| | 00:36 | page of the backstage view.
| | 00:38 | When you go to the Print page of the
backstage view, you will see that the chart
| | 00:42 | is by itself in Print Preview.
| | 00:44 | That's because over in the Settings
area, you can see the Print Selected
| | 00:47 | Chart is highlighted.
| | 00:50 | That means, as I said here, that Excel
will only print the selected chart.
| | 00:54 | And in fact, if you click this particular
settings item, you can see that you
| | 00:59 | can only print the selected chart.
| | 01:01 | You can't print the entire workbook, and
you can't print the selection; you are
| | 01:05 | only able to print the selected chart.
| | 01:07 | And when you're ready to do that, all
you need to do is click the Print button,
| | 01:11 | and you'll get your print
out on your selected printer.
| | Collapse this transcript |
|
|
8. Manipulating PivotTables Using MacrosRecording and reviewing an Excel macro| 00:00 | When you find a PivotTable configuration you
like, you should find a way to reconstruct it.
| | 00:05 | There are a number of ways to do that.
The most straightforward is to write down
| | 00:09 | the field order that you can
see in the PivotTable Fields pane,
| | 00:12 | but you can also record the actions of
creating that configuration using a macro,
| | 00:18 | and I'll show you how to
do that in this movie.
| | 00:20 | I'll use the RecordMacro sample file,
which you can find in the Chapter08 folder
| | 00:25 | of your Exercise Files archive.
| | 00:28 | So, let's say that I want
to record this position.
| | 00:32 | I have the Department field in the
Columns area, Month in the Rows area, and the
| | 00:37 | sales column in the Values area. So,
Department, Columns; Months, Rows; Values, Sales.
| | 00:44 | To record a macro in Excel 2013, click
the View tab, and then click the Macros
| | 00:51 | button's down arrow,
and click Record Macro.
| | 00:54 | Doing so displays the
Record Macro dialog box.
| | 00:58 | You can then type in
a name for your macro;
| | 00:59 | I'll call it MonthRows, and then
D-e-p-t for department, C-o-l-s.
| | 01:06 | So, what I've done is I've created a
position called Month Rows Department
| | 01:10 | Columns, and because there is only one
data field, I'll just assume that sales
| | 01:14 | would go in the values area.
| | 01:15 | I am ready to begin
recording my macro, so I'll click OK.
| | 01:19 | From now on, everything that I do will be
recorded as steps in Visual Basic for Applications.
| | 01:25 | The first thing I need to do with
the PivotTable selected is to click the
| | 01:30 | Analyze contextual tab, click
Clear, and then click Clear All.
| | 01:35 | Doing so clears everything from inside
the PivotTable, and I can now reconstruct
| | 01:40 | it from the ground up.
| | 01:41 | My configuration was the Month field in
the Rows areas, the Department field in
| | 01:47 | the Columns area,
and Sales in the Values area.
| | 01:50 | I've created the configuration I want,
so I can stop recording the macro.
| | 01:54 | To do that, I'll click the View tab,
click the Macros button's down arrow, and
| | 01:58 | click Stop Recording.
| | 02:00 | Now that I've recorded the
macro, I can take a look at it.
| | 02:03 | To do that, I click the Macros button,
and I see my macro's name; it's highlighted
| | 02:09 | here in blue, and I can click Edit.
| | 02:11 | PivotTable macros are complicated, and
that's to be understood; a PivotTable is
| | 02:15 | a complicated object, so to create a
specific configuration, especially if you go
| | 02:21 | beyond simply arranging
fields, takes a lot of steps.
| | 02:24 | What I recommend doing
is reading over the macro.
| | 02:26 | For example, remember that I put Month
in the Rows area, and it's in the first
| | 02:31 | position, and then I put the Department
column in the Column field area, and it's
| | 02:38 | also in the first position.
| | 02:39 | What's more, the PivotTable is summarizing
using the sum operation data from the
| | 02:45 | sales column of the source table.
| | 02:48 | Now that I'm done examining my macro,
I can press Alt+F11 to return to the
| | 02:52 | main Excel workbook.
| | 02:53 | Recording a macro that recreates a
PivotTable position is a terrific way to recall
| | 02:59 | a particularly informative or
useful configuration quickly.
| | 03:02 | Always remember, though, that the first
step after you start recording should
| | 03:06 | be to clear everything from the existing
PivotTable, so you can build it from
| | 03:09 | the ground up.
| | Collapse this transcript |
| Running an Excel macro| 00:00 | After you record a macro that reproduces
a PivotTable position, you can run that
| | 00:05 | macro, and in this movie, I will show
you several different ways of doing that.
| | 00:09 | I'll use of the RunMacro sample file,
which you can find in the Chapter 08
| | 00:13 | folder of your Exercise Files archive.
| | 00:16 | Once again, my goal is to run a macro
that I've previously recorded, so that I
| | 00:20 | can re-create the
PivotTable positions that I want.
| | 00:24 | The first way to run a macro is to
do so from the Macros dialog box.
| | 00:28 | So, I'll click the View tab, and then
in the Macros group, click the Macros
| | 00:33 | button, and then all I need to do is
click the Macro I want to run, so it's
| | 00:38 | highlighted in blue, and then click Run.
| | 00:40 | Doing so runs the macro, which re-creates
the PivotTable positions that I want.
| | 00:44 | Now I will drag the PivotTable
Fields out, so I am back to my
| | 00:49 | original configuration.
| | 00:50 | Opening the Macro dialog box, and
running a macro from within it is perfectly
| | 00:54 | fine, but it takes a fair number of steps.
| | 00:57 | If you anticipate running the macro
multiple times, you can add a button to the
| | 01:01 | quick access toolbar that
you can click to run it.
| | 01:04 | I'll show you how to do that now.
| | 01:06 | First thing you need to do is hover
your mouse pointer over the button at the
| | 01:10 | right edge of the quick access toolbar --
the name of a button is Customize
| | 01:14 | Quick Access Toolbar --
| | 01:16 | and from list that appears,
click More Commands.
| | 01:18 | Doing so displays the Quick Access
Toolbar page of the Excel Options dialog box.
| | 01:23 | Now you need to display the macros
that are available in your workbook.
| | 01:27 | To do that click the Choose commands from
box's down arrow, and then click Macros.
| | 01:33 | Doing so lists all the macros
that are available in your workbook.
| | 01:36 | In this case, there is only one, so I'll
click it, and then click Add to add it
| | 01:41 | to the quick access toolbar.
| | 01:42 | This is a custom button, so if I want
I can change the button that actually
| | 01:47 | appears on the quick access toolbar.
| | 01:49 | So, I'll click my macro, and then click
Modify. When I do, the Modify Button dialog
| | 01:55 | box appears, and I can select the button
that I want, and for this one, I'll make
| | 02:00 | it a forest green square, and click OK.
| | 02:03 | The button icon changes, and I can click
OK to make the change, and you see the
| | 02:07 | button here on the quick access toolbar.
| | 02:10 | When I hover the mouse pointer over it, the
name of the macro appears beneath it as a tooltip.
| | 02:14 | Now to run the macro, all I need to
do is click that button, and Excel runs
| | 02:20 | it, and I'll just remove the fields
to reset the PivotTable back to its
| | 02:24 | original configuration.
| | 02:25 | One final way to run a macro in Excel 2013
is to assign the macro to a shape or an image.
| | 02:31 | To add a shape to your worksheet,
click the Insert tab, and then in the
| | 02:35 | Illustrations group, click the Shapes
button, and click the shape that you want.
| | 02:40 | In this case, I'll make it a
rectangle with rounded edges.
| | 02:44 | Then, in the body of the worksheet, click
and drag to create the shape you want,
| | 02:48 | and with the shape still selected, start
typing text that will indicate what the
| | 02:54 | button's function will be, and in this
case, I'll just call it PivotRecall, and
| | 02:59 | I'll resize the shape, so all
the text fits, and there we are.
| | 03:02 | Now, to assign a Macro to a shape, right-click
the shape, and from the shortcut menu
| | 03:07 | that appears, click Assign Macro.
| | 03:10 | In the Assign Macro dialog box, click the
name of the macro you want to assign to
| | 03:13 | it; in this case, this is the
only one, and then click OK.
| | 03:17 | Now click away from the shape, and when
you want to run your macro, all you need
| | 03:20 | to do is move the mouse pointer over
the shape, and you can tell that it's
| | 03:24 | active -- in other words, that it has a macro
assigned to it -- because the mouse pointer
| | 03:29 | changes to a pointing hand.
| | 03:30 | When you click the button,
Excel runs the macro for you.
| | 03:34 | Just as a final note, if you want to
edit a button that has a macro assigned to
| | 03:38 | it, instead of clicking it, right-click it.
| | 03:41 | When you do, you get the shortcut
menu with all of your editing options.
| | 03:45 | To release the selection, just press the
Escape key. Those are the ways that
| | 03:49 | you can run amMacro inside of Excel 2013.
| | 03:53 | You can run the macro from the Macros
dialog box, add a button to the quick
| | 03:57 | access toolbar, or assign
the macro to a shape.
| | 04:00 | Use the method that works best
for your particular situation.
| | Collapse this transcript |
| Creating a simple PivotTable presentation kit| 00:00 | After you've recorded one or more
macros that re-create desired PivotTable
| | 00:04 | positions, you can add elements to your
workbook that let you run those macros
| | 00:09 | quickly, and in the desired order.
| | 00:11 | In this movie, I will show you how to
create a very simple presentation kit,
| | 00:15 | so you can use your PivotTables as a story
telling device, and not just for data analysis.
| | 00:20 | I will use the PresentationKit sample
file, which you can find in the Chapter 08
| | 00:26 | folder of your Exercise Files archive.
| | 00:28 | I have recorded three separate macros
for this workbook, and I'll show them to
| | 00:33 | you by clicking the View tab, and
opening the Macros dialog box, and clicking
| | 00:38 | View Macros, and you can see that I have
three different macros here: Department
| | 00:44 | Totals, Month and Department,
and Month Totals.
| | 00:47 | I want to run them in the order Department
Totals, Month Totals, and Month and Department.
| | 00:53 | So, I'll click Cancel to
close the Macro dialog box.
| | 00:57 | I could run the macros from within the
Macro dialog box, although that takes
| | 01:01 | a quite a few clicks.
| | 01:02 | I can add shapes that I can click in
the body of the worksheet, or, the method
| | 01:07 | that I'll use here is that I could use a
series of buttons on the quick access toolbar.
| | 01:13 | To do that, I click the Customize Quick
Access Toolbar button, which is at the
| | 01:17 | right edge of the quick access toolbar,
and then click More Commands. That
| | 01:22 | displays the Quick Access Toolbar
page of the Excel Options dialog box.
| | 01:25 | Now I can click the Choose commands
from the down arrow, and click Macros to
| | 01:31 | display the three macros
available in this Workbook.
| | 01:34 | The first one I want to add is Department
Totals, so I'll click it, and click the
| | 01:39 | Add button, so it appears in the Quick
Access Toolbar list, then I'll do the
| | 01:43 | same for Month Totals,
and Month and Department.
| | 01:46 | Elsewhere in this chapter, I showed
you how to change the icons that are
| | 01:50 | associated with those macros on the
quick access toolbar, so I won't do it here;
| | 01:55 | I'll just leave them
with the default image.
| | 01:57 | We see I have Department Totals,
Month Totals, and Months and Department.
| | 02:00 | Great; that looks good.
| | 02:01 | So, I can click OK, and when I do, my
three macros are represented on the quick
| | 02:07 | access toolbar with buttons.
| | 02:10 | You can see that when I hover the mouse
pointer over a particular button on the
| | 02:13 | quick access toolbar, it displays the
command behind it; in this case, it is a
| | 02:18 | macro named Month and Department.
| | 02:21 | To start the presentation, I might say
something like, welcome, I want to briefly
| | 02:25 | go over sales data for our
departments, broken down by Month.
| | 02:30 | First, the departmental totals, which
you can see here. They're pretty even
| | 02:33 | across the entire company, and
our five different departments.
| | 02:37 | If you look at monthly sales, you see
that, once again, there is a pretty good
| | 02:40 | spread. There is a bit of a letdown
in January, because it's right after the
| | 02:44 | holiday season, but February
and March were consistently good.
| | 02:47 | When you break the amounts down by
month and department, you'll see that, once
| | 02:52 | again, January lagged a bit, except in the
Work category, where I guess people were
| | 02:56 | getting ready for a new year of work,
and their New Year's resolutions of trying
| | 03:00 | to get promoted, and
dressing for the job they want.
| | 03:02 | That's one way you can use macros to
re-create PivotTable positions, and use your
| | 03:07 | workbook as a storytelling device.
| | 03:08 | It's s a very powerful tool, and once
you've tried it a few times, I think you'll
| | 03:13 | like it.
| | Collapse this transcript |
|
|
9. Starting with PowerPivot and the Data ModelEnabling PowerPivot in Office 2013 Professional Plus| 00:00 | If you run Office 2013 Professional
Plus, then as part of Excel 2013, you're
| | 00:06 | able to use PowerPivot and PowerView.
| | 00:09 | In this movie, I will show you how
to enable PowerPivot in Excel 2013.
| | 00:13 | I'm using the Import sample file,
| | 00:15 | but we'll just be working in
the Excel Options dialog box,
| | 00:18 | so no sample file is necessary;
| | 00:20 | you just need to have Excel
2013 open on your Desktop.
| | 00:24 | To enable the PowerPivot
add-in, click the File tab.
| | 00:27 | Doing so displays the backstage view,
and then click Options to open the
| | 00:33 | Excel Options dialog box.
| | 00:35 | Then click the Add-Ins category,
| | 00:37 | and at the bottom of that page of
the Excel Options dialog box, click the
| | 00:42 | Manage Lists down arrow, click COM
Add-ins -- that's COM for Common Object
| | 00:47 | Model -- and click Go.
| | 00:50 | Doing so displays the COM add-ins
that are available to you.
| | 00:53 | Depending on your installation of
Office 2013, you might not see all of these
| | 00:58 | add-ins, but the one you should see, and
the box you need to check, is by what is
| | 01:03 | the second option in this list:
Microsoft Office PowerPivot for Excel 2013.
| | 01:08 | And with that box checked, click OK, and
you'll see a PowerPivot tab on the ribbon.
| | 01:15 | If PowerPivot doesn't appear when you
follow this procedure, then open the
| | 01:18 | dialog box, and click the Add button.
| | 01:21 | Doing so displays another dialog box,
within which you can click the PowerPivot file.
| | 01:27 | Click OK, and then click OK again to add the
file to the dialog box, and also to the ribbon.
| | 01:33 | When you click that tab, you can see
the controls that are available to you,
| | 01:37 | and I'll spend the rest of this
chapter telling you how to use it.
| | Collapse this transcript |
| Introducing PowerPivot| 00:00 | I wanted to take a few moments to
introduce PowerPivot, and the data model,
| | 00:05 | just by showing you some of the things that
you can do with PowerPivot inside of Excel 2013.
| | 00:11 | This demonstration will not be exhaustive.
| | 00:13 | I just want to give you a
flavor for what you can do.
| | 00:16 | I will use the Introducing sample file,
which you can find in the Chapter 9
| | 00:20 | folder of your Exercise Files archive.
| | 00:22 | First, I'll get into PowerPivot by
clicking the PowerPivot tab on the ribbon,
| | 00:26 | and then click Manage.
| | 00:28 | Doing so displays Powerpivot,
which contains two tables;
| | 00:32 | the first is SalesSummary,
and the second is MonthNames.
| | 00:36 | So, SalesSummary is currently displayed,
and I can click the MonthNames
| | 00:40 | table's tab to display it.
| | 00:42 | I'll go back to SalesSummary.
| | 00:44 | One of the great things about
PowerPivot is that it uses something called
| | 00:48 | vertical database technology to
manage extremely large datasets.
| | 00:52 | If you look at the bottom left corner
of the program window, you can see that
| | 00:55 | this collection only contains 100 rows.
| | 00:58 | So, it's not large,
even by Excel standards.
| | 01:01 | I've kept my sample file's small, so
that you can download them more easily.
| | 01:06 | But, unlike Excel, which has a
limit of 1,048,576 rows per worksheet,
| | 01:12 | PowerPivot can handle millions, or even tens of
millions of rows, and it does so pretty quickly.
| | 01:18 | Just like in an Excel worksheet,
especially an Excel table, you can filter the
| | 01:21 | values that appear in PowerPivot.
| | 01:23 | So, for example, if I wanted to only
display values for 2013, I could click the
| | 01:28 | Year column's down arrow,
and select 2013, and click OK.
| | 01:33 | And if I want to clear the filter,
I can click Clear All Filters.
| | 01:36 | I can also sort, create formulas,
and change the formatting of any of
| | 01:41 | these table columns.
| | 01:42 | The two tables that I've added to
this workbook's data model are related.
| | 01:47 | So, for example, in SalesSummary, I
have a Month column, and the month is
| | 01:52 | displayed as a number
instead of a month name.
| | 01:56 | And then in the MonthNames table, I
have MonthNumbers in the first column, and
| | 02:01 | MonthName in the second column.
| | 02:03 | The MonthID column here in MonthNames
matches up to the Month column in
| | 02:09 | the SalesSummary table.
| | 02:11 | That means that I can create a relationship
between those two table columns, so
| | 02:15 | that I can relate the tables.
| | 02:17 | To show you what I mean, I will
switch from Data View to Diagram View.
| | 02:22 | And these controls are in the
Home tab of the PowerPivot ribbon.
| | 02:26 | So, when I click Diagram View, I
see my layout, and my two tables:
| | 02:31 | SalesSummary, and MonthNames,
| | 02:34 | and I also see that there is a relationship
between SalesSummary and MonthNames.
| | 02:40 | That's what this line indicates.
| | 02:41 | And if I switch back to Data View, I'll
show you one way that this relationship
| | 02:47 | helps when you have multiple tables
that are related inside of PowerPivot.
| | 02:51 | Specifically, I will create a PivotTable
based on these two tables, instead of just one.
| | 02:56 | To do that, I'll click the PivotTable button,
and again, this is on the Home tab in PowerPivot.
| | 03:01 | I will insert the PivotTable on
the New worksheet, clicking OK,
| | 03:05 | and when I do, I get my PivotTable
Fields task pane that contains two items;
| | 03:10 | MonthNames, and Table1,
which is my SalesSummary data.
| | 03:14 | So, let me show you what happens
if I just add values from Table1.
| | 03:17 | I'll put Month in the Rows area, and
I'll put Department in the Columns area,
| | 03:24 | and Sales in the Values area.
| | 03:26 | And note that my row labels, which
are the months, are listed by number, as
| | 03:30 | opposed to month name.
| | 03:32 | Now, I'll pull Month out of the Rows
area, and scroll up in the PivotTable
| | 03:37 | Fields task pane, display the MonthNames
table's fields, and then drag MonthName
| | 03:43 | down to the Rows area.
| | 03:44 | Doing so displays the months in
alphabetical order, but I want them in
| | 03:48 | chronological order,
| | 03:49 | and because month names are a custom
list in Excel, I can right-click any cell
| | 03:55 | that contains a month name, point to
Sort, like Sort A to Z, and Excel sorts the
| | 04:00 | names into chronological order.
| | 04:02 | If I want to go back to PowerPivot,
from Excel, I can just click the Manage
| | 04:06 | button, and when I'm ready to go back
to the Excel workbook from PowerPivot, I
| | 04:10 | can go up to the title bar, and
click the Switch to Workbook button,
| | 04:14 | and that takes me back into Excel.
| | 04:16 | That's just a quick overview of just a few of
the features that you can use with PowerPivot.
| | 04:22 | The ability to work with large data
collections makes PowerPivot worth its
| | 04:25 | weight in gold, and for anyone lucky
enough to have access to Office 2013
| | 04:29 | Professional Plus, I think you'll agree.
| | Collapse this transcript |
| Importing PowerPivot data| 00:00 | There are two main ways to bring data
into the PowerPivot data model in Excel 2013.
| | 00:05 | The first is to include Excel
tables from the current workbook in that
| | 00:09 | workbook's data model, and the second is
to import data from an external source.
| | 00:15 | In this movie, I will show you
how to bring an outside data,
| | 00:18 | and I'll start with the Import sample
workbook, which you can find in the
| | 00:22 | Chapter09 folder of
your Exercise Files archive.
| | 00:25 | To bring in the data into PowerPivot, I
need to click the PowerPivot tab on the
| | 00:29 | ribbon, and then click
Manage to display PowerPivot.
| | 00:33 | The screen here at the bottom is blank because
there's no data currently in the data model.
| | 00:38 | I will bring in my data from an Access
database, but the skills that I show you
| | 00:43 | here will transfer to pretty
much every other type of file.
| | 00:46 | You might need to enter a password, and
perhaps a username for some SQL server
| | 00:50 | databases, or other sources, but it
should be pretty obvious what you need to do
| | 00:55 | from within the dialog box.
| | 00:57 | To bring in data from an Access database
with no password, I click the From
| | 01:02 | Database button on the Home tab of the
PowerPivot ribbon, and then click From Access.
| | 01:07 | Doing so displays the
Table Import wizard.
| | 01:10 | I need to look for the file,
so I'll click the Browse button.
| | 01:14 | That displays the Open dialog box,
and I can navigate to the folder that
| | 01:18 | contains the file
I want to connect to.
| | 01:20 | In this case, I went right to it; it's
the DataSource file in the Chapter09
| | 01:25 | folder of the Exercise Files archive.
You'll get a copy as well, and I've
| | 01:29 | selected it, and I'll click Open.
| | 01:31 | Now, I'm back in the Table Import
Wizard dialog box, and if I had to log on
| | 01:35 | to the database, I could enter my username and
password, and save my password if I wanted to.
| | 01:41 | If your file is over a network,
then you can test the connection.
| | 01:45 | I'll click Test Connection, just to
show you what happens, and it succeeded. I
| | 01:49 | click OK, and now I'll click Next.
| | 01:51 | The next screen lets me
choose how to import the data.
| | 01:54 | If I want, I could write a custom SQL query
to specify a subset of the data to import.
| | 02:00 | In this case, I just want to look at
the list of tables and views that are in
| | 02:04 | the Access workbook.
| | 02:06 | So, with that option selected, I'll click Next,
and I see the table that's in the workbook.
| | 02:11 | There's only one.
| | 02:12 | Its name is Orders, and it
is the one I want to use,
| | 02:16 | so I will click Finish.
| | 02:18 | Doing so brings the data in, and I see
that the operation was successful, and
| | 02:22 | that 100 rows were transferred.
| | 02:24 | That's how many I expected,
so I will click Close.
| | 02:27 | When I do, the dialog box disappears,
and my data is inside of the data model.
| | 02:32 | One thing I do want to point out is
that I've created an external data
| | 02:36 | connection, which can have
security implications.
| | 02:38 | So, when I switch back to the Excel
workbook by clicking the Switch to Workbook
| | 02:43 | button on the title bar, I see a message
bar with a security warning, indicating
| | 02:48 | that external data
connections have been disabled.
| | 02:50 | The data that I've already
added to the data model is there,
| | 02:54 | but unless I have an external connection,
then any updates to that data source
| | 02:58 | will not be reflected
inside of my collection.
| | 03:01 | If I want to enable it, all I need to
do is click the Enable Content button.
| | 03:06 | If for some reason I didn't want to, I
can dismiss the message bar by clicking
| | 03:09 | the Close button here on the
right edge of the message bar.
| | 03:13 | But in this case, I do want to enable
the content, so I will click the Enable
| | 03:18 | Content button, and now any changes
to the original data source will be
| | 03:21 | reflected in the data model.
| | Collapse this transcript |
| Adding tables to the data model| 00:00 | PowerPivot lets you work with
extremely large data collections,
| | 00:03 | so it's likely that you will import
data from other sources, such as SQL server
| | 00:08 | databases, for use in Excel 2013.
| | 00:12 | That said, if your workbook contains an
Excel table that you would like to add
| | 00:16 | to the data model, then you can do so,
and in this movie, I will show you how.
| | 00:21 | I will use the AddToModel sample file,
which you can find in the Chapter09 folder
| | 00:26 | of your Exercise Files archive.
| | 00:28 | To add an Excel table to the data model,
which enables you to work with that data
| | 00:33 | inside of PowerPivot, all you need to
do is click any cell inside the Excel
| | 00:37 | table, and then on the PowerPivot tab,
click the Add To Data Model button.
| | 00:44 | Doing so adds that table to the data
model, and you can now work with it
| | 00:48 | using timelines, slicers, and all of the other
tools that are available to you in PowerPivot.
| | 00:53 | It's a very straightforward process,
and one that you'll have no trouble
| | 00:56 | doing immediately.
| | Collapse this transcript |
| Managing table columns| 00:00 | When you import data into PowerPivot in
Excel 2013, or include an existing Excel
| | 00:05 | table in PowerPivot, you can manage that
data inside of PowerPivot, just like you
| | 00:09 | would any other Excel worksheet,
| | 00:11 | and in this movie, I will show you how
to use the ManageColumns sample file to
| | 00:16 | show you some of the
techniques that you can use.
| | 00:19 | The first thing I need
to do is open PowerPivot,
| | 00:21 | so I will click the PowerPivot ribbon tab, and
then at the left edge of the tab, click Manage.
| | 00:27 | Doing so opens PowerPivot,
and displays the existing data.
| | 00:31 | The first thing you notice just looking at
the data is that the Date field is very narrow;
| | 00:37 | the Date column here.
| | 00:38 | To resize it, I will hover the mouse
pointer over the left edge of the header.
| | 00:43 | So, you can see here that the mouse
pointer has changed to a two-way
| | 00:47 | pointing horizontal arrow,
| | 00:49 | and all I need to do is drag that column
edge to the side, dragging it to the
| | 00:55 | right, and when I release it, you can
see that the entire value inside the
| | 00:59 | field is displayed.
| | 01:00 | The second thing you notice, I'm sure, is
that the date is followed by a time, and
| | 01:05 | that time is midnight; 12 a.m.
| | 01:08 | Because the times are all the same,
they don't add any information, so we can
| | 01:12 | reformat the field, so that
it displays only the date.
| | 01:15 | To do that, I'll click any cell inside
of the Date column, and then on the Home
| | 01:21 | tab of PowerPivot, I'll
click the Format control.
| | 01:25 | You can see it highlighted here.
| | 01:26 | And I'll select the
format that I want,
| | 01:29 | and in case, it's the second one down;
the one that says 3/14/2001. That's the
| | 01:34 | pattern we're looking for,
| | 01:36 | so I'll click that,
| | 01:37 | and you can see that the date is now just
the date, and the time has been eliminated.
| | 01:42 | And I can also resize the column by
dragging its right edge, so that it's no
| | 01:48 | wider than it needs to be.
| | 01:49 | The final thing I'd like to show
you is how to freeze a column.
| | 01:53 | If you've worked with Excel, you know
that sometimes it can be handy to freeze
| | 01:57 | either rows or columns, so that they
stay either at the top, or at the left edge
| | 02:01 | of the screen when you scroll.
| | 02:03 | That's a very handy technique for working
with either very long or very wide worksheets.
| | 02:08 | In PowerPivot, if you want to freeze a
column, all you need to do is click any
| | 02:13 | cell in that column, and then
on the Design tab, click Freeze.
| | 02:18 | Doing so adds a black line to the
column or columns that you selected, and
| | 02:23 | now, regardless of how far you scroll to the
right, the OrderID column will stay visible.
| | 02:28 | So, if I resize the PowerPivot window
by dragging its right edge, so that not
| | 02:33 | everything is displayed, and I click
the horizontal scrollbar, so that we get to
| | 02:37 | the right, you'll see that the OrderID
column remains, even though I've scrolled
| | 02:42 | beyond it, if I hadn't frozen it.
| | 02:45 | So, just keep going over to Total, and so on,
and when I scroll back, it remains in place.
| | 02:50 | And let's just resize the window again.
| | 02:52 | If you want to unfreeze a column, click
any cell in the column, then click the
| | 02:57 | Freeze button's down arrow,
and click Unfreeze.
| | 03:00 | Now if I were to scroll to the right,
the OrderID column would disappear
| | 03:05 | instead of staying put.
| | 03:06 | Those are the basics of managing
columns inside of PowerPivot in Excel 2013.
| | 03:11 | These techniques are very similar to
managing columns inside of an Excel worksheet,
| | 03:15 | but there are just enough differences
that I wanted to show, so you know
| | 03:19 | what to do immediately.
| | Collapse this transcript |
| Creating relationships between tables| 00:00 | When you add columns to your
PowerPivot data model, you can create a
| | 00:05 | relationship or link between those
tables if they have a column in common.
| | 00:09 | In this movie, I will show you how to
create those relationships using the
| | 00:13 | CreateRelationship workbook, which you
can find in the Chapter09 folder of your
| | 00:18 | Exercise Files archive.
| | 00:20 | This workbook contains two separate tables,
both of which I've added to the data model.
| | 00:25 | The first table is sales data, and you
can see that it has a number of columns.
| | 00:30 | The one that I'm interested in is Month.
| | 00:33 | The reason I'm interested in it, at least
for the purposes of this exercise, will
| | 00:37 | be apparent when I show you Sheet2 with
the second table, and that has MonthID,
| | 00:42 | and the associated MonthName.
| | 00:44 | Month 1 is January, 2 is February, and so
on, down to number 12, which is December.
| | 00:49 | So, even though my original table on
Sheet1 only displays months with the
| | 00:55 | number, not the name, I can create a
relationship between these two tables, so
| | 00:59 | that I have access to the month
name, in addition to the number.
| | 01:03 | To do that in PowerPivot, I'll click
the PowerPivot tab on the ribbon, and then
| | 01:08 | at the left edge of the ribbon,
click Manage.
| | 01:10 | Doing so opens my data in PowerPivot,
| | 01:13 | and you can see that I have two
tabs in this PowerPivot book; I have
| | 01:17 | SalesSummary, and MonthNames, and they
correspond to the data that I showed you earlier.
| | 01:23 | If I want to define a relationship
between these two tables, then I just click
| | 01:28 | Design, and then click Create Relationship.
| | 01:30 | Doing so displays the
Create Relationship dialog box,
| | 01:35 | and I can now define a relationship based on
the fields that the two tables have in common.
| | 01:40 | In the SalesSummary field, which is the
one that has the order data, and that is
| | 01:44 | the top table displayed here, I
need to select the month column.
| | 01:48 | So, I'll click the top Column control, and
from the list of fields, and click Month.
| | 01:54 | Now I need to identify the Related
Lookup Table, and that is MonthNames.
| | 01:59 | So, I'll click the Related Lookup Table
control's down arrow, click MonthNames,
| | 02:03 | it's the only option,
| | 02:04 | and now I need to identify the lookup
column, and that column is the column that
| | 02:09 | contains exactly the same
data as the Month column.
| | 02:12 | So, if I drag the Create Relationship
dialog box down, you can see that the Month
| | 02:17 | field contains numbers, not names;
| | 02:19 | that means I need to select the Month ID field
from the lookup table instead of Month name.
| | 02:25 | Now my two columns, Month, and MonthID,
share values, and we can use one to look up
| | 02:30 | to the other, and that's what
makes for a successful relationship.
| | 02:33 | I'll click Create to define the relationship,
and PowerPivot adds it to the data model.
| | 02:38 | After you define a relationship,
you can manage or delete it.
| | 02:42 | To do that, again, on the Design tab of
the ribbon in PowerPivot, click Manage
| | 02:47 | Relationships, and you see here that you
can either create a relationship, which
| | 02:52 | opens the Create Relationship dialog box,
or you can edit an existing relationship.
| | 02:56 | To do that, click the relationship you
want to work with, click Edit, and you get
| | 03:00 | that relationship inside of the Edit
Relationship dialog box, which is familiar.
| | 03:05 | It's just a new name on the
interface that you use for other tasks.
| | 03:08 | I'll click Cancel, and if you want to
delete the relationship, just click the
| | 03:13 | relationship, click Delete, and if you're
sure you want to get rid of it, click OK.
| | 03:18 | If not, click Cancel,
which is what I'll do here.
| | 03:20 | When you're done managing the
relationships in PowerPivot, click Close to
| | 03:25 | close the dialog box.
| | 03:26 | And that is how you create and
manage relationships in PowerPivot for
| | 03:30 | Excel 2013.
| | Collapse this transcript |
| Creating a PowerPivot PivotTable| 00:00 | After you've brought data into PowerPivot
for Excel 2013, you can summarize that
| | 00:05 | data in the PivotTable.
| | 00:07 | To demonstrate the process, I will use
the CreatePivot sample file, which you
| | 00:11 | can find in the Chapter09 folder
of your Exercise Files archive.
| | 00:15 | To create a PivotTable in PowerPivot,
you need to open up PowerPivot,
| | 00:20 | so I'll click the PowerPivot tab on
the ribbon, and then click Manage.
| | 00:26 | My PowerPivot data model contains two
tables, and you can see their names here:
| | 00:30 | SalesSummary, and MonthNames.
| | 00:33 | So, what I want to do is to
summarize all of that data in a PivotTable.
| | 00:37 | One benefit of working in PowerPivot
is that I have created a relationship
| | 00:42 | between the SalesSummary
and MonthName tables,
| | 00:45 | and if I click the Design tab on the
ribbon, and click Manage Relationships, you
| | 00:50 | can see in the dialog box that there is
a relationship between the Month field
| | 00:54 | of the SalesSummary table, and the
MonthID field of the MonthNames table.
| | 01:00 | That will come in handy when I show
you how to work with the PivotTable
| | 01:04 | inside of PowerPivot.
| | 01:05 | So, I'll click Close to close dialog
box, and then on the Home tab of the
| | 01:10 | PowerPivot ribbon, I'll
click the PivotTable button.
| | 01:14 | Doing so displays the Insert Pivot
dialog box, and I want to create my PivotTable
| | 01:20 | on the new worksheet,
| | 01:21 | so I'll leave that
option selected, and click OK.
| | 01:24 | When the PivotTable appears, you can
see a blank PivotTable here on a new
| | 01:28 | worksheet, which in my workbook
is named Sheet4. It might be named
| | 01:32 | something different in yours.
| | 01:34 | And you'll also see over in the
PivotTable Fields pane that there are two tables
| | 01:39 | instead of just one, or rather, a list
of fields like you would see in a single
| | 01:44 | table, if you created a PivotTable
inside of the regular Excel program.
| | 01:49 | When you create the file, you'll see the
files area show MonthNames, and Table1.
| | 01:55 | Table name is the name of the table in
the original Excel file from which the
| | 01:59 | values are derived, and that's
why that name appears there.
| | 02:03 | See, here we have MonthNames, which if
I click it, you can see that there's
| | 02:06 | MonthID, and MonthName, and there's also
a second table name SalesSummary, which
| | 02:11 | has OrderID, Date, Year, and so on.
| | 02:14 | And those table names and list of fields
reflect the two source data tables that
| | 02:19 | I used from PowerPivot
to create this PivotTable.
| | 02:22 | Now I can add fields to the body of the
PivotTable to display and manipulate its data.
| | 02:27 | So, I'll add Department to the Columns
area, and then I'll scroll down, and add
| | 02:33 | Total to the Values area.
| | 02:35 | Doing so displays sales totals
for the five different departments.
| | 02:39 | If you want to format this field, you
can do so using the techniques that I have
| | 02:43 | shown elsewhere in the course.
| | 02:44 | But now let me show you what
happens if I add the Month field, from the
| | 02:48 | SalesSummary table to the Rows area.
| | 02:51 | Doing so displays all the months that
have sales, and those are 1, 2, 3, and 11 to
| | 02:56 | 12; January, February, March,
and November, and December.
| | 03:00 | But let's say that I want to display the
month names instead of the month numbers.
| | 03:05 | I can do that by dragging Month back to the
Fields area to remove it from the PivotTable.
| | 03:11 | Then I'll scroll up, and from the
MonthNames table, I'll drag the MonthName
| | 03:16 | field to the Rows area.
| | 03:18 | Doing so displays the months, but
you'll notice that instead of being in
| | 03:22 | chronological order, the
months are in alphabetical order.
| | 03:26 | To fix that, I need to sort
the data inside of that field.
| | 03:30 | So, I'll hover the mouse pointer
over the MonthName field, over in the
| | 03:33 | PivotTable Fields pane, click the
downward pointing black triangle that
| | 03:37 | appears, and click Sort A to Z. Now,
because MonthNames are a known custom list
| | 03:44 | inside of Excel, when I click Sort A
to Z, instead of staying in alphabetical
| | 03:48 | order like they are now, the months
are sorted January, February, March,
| | 03:52 | November, and December, into the known
order, and once you have your data inside
| | 03:56 | of your PowerPivot PivotTable, you can work
with it just as you would any normal PivotTable.
| | 04:02 | If you are data collection is less than
a few thousand rows, then you probably
| | 04:06 | won't need to use PowerPivot,
| | 04:07 | but if your data collection is quite
large, perhaps numbering in the hundreds of
| | 04:11 | thousands, or even millions of rows, then
the technique that I showed you allows
| | 04:15 | you to create a PivotTable that you can
manipulate using the skills that you've
| | 04:19 | learned elsewhere in this course.
| | Collapse this transcript |
| Filtering data using a timeline| 00:00 | When you create a PivotTable using
tables that have been added to the Excel data
| | 00:04 | model, you can then filter that
PivotTable using what's called a timeline.
| | 00:09 | Timeline is a new feature that's
available for PivotTables in Excel 2013, and
| | 00:14 | I'll demonstrate how to create them
and use them by using the Timeline sample
| | 00:19 | file, which you can find in the Chapter09
folder of your Exercise Files archive.
| | 00:24 | I created this PivotTable in PowerPivot,
so the tables that provide its data are
| | 00:29 | part of Excel's data model.
| | 00:31 | To filter a PivotTable using a
timeline, you have to have a field that is
| | 00:35 | formatted as a date, and if I scroll up
in the PivotTable Fields pane, you can
| | 00:41 | see that there is a Date field.
| | 00:43 | So, even though the Date field isn't
currently displayed in the PivotTable, I
| | 00:47 | can use its contents as
the basis for my timeline.
| | 00:50 | To add the timeline filter, click any
cell in the PivotTable, and then on the
| | 00:54 | Insert tab, click Timeline.
| | 00:57 | When I do, the Insert Timelines dialog
box appears, and on the Active tab, I can
| | 01:03 | see the name of the table providing the
data that I'll use as my sort value, and
| | 01:09 | then within that table,
there is the Date field.
| | 01:12 | So, I'll go ahead and check
that field's box, and click OK.
| | 01:16 | Doing so hides the PivotTable
Fields pane, and adds a timeline.
| | 01:21 | If a timeline looks a lot like a slicer,
that's not a coincidence; they are related tools.
| | 01:26 | Now I can use the timelines
controls to filter my data.
| | 01:29 | So, let's say I only want to
display data for January 2013.
| | 01:33 | To do that, I need to scroll back in
the timeline, and you can see that I'm
| | 01:37 | currently in the year 2013; January,
February, March, going to the right, and
| | 01:41 | then for 2012, you've got
December, November, and so on.
| | 01:45 | So, let's say I want to see the data
for January 2013. I'll just click the
| | 01:49 | timeline segment below January,
and doing so filters the PivotTable.
| | 01:54 | If I want to include another month,
then I can drag the handle at the edge of a
| | 02:00 | particular line segment, or whatever
the current filter is, and drag it to the
| | 02:04 | left, so that I include
December 2012 in with January 2013.
| | 02:09 | If I want to reduce the time displayed,
then I can just drag it to the left,
| | 02:14 | such as here for January, and
now I only see December 2012.
| | 02:18 | Unfortunately, one thing you can't do is
select multiple segments using the Ctrl key.
| | 02:24 | So, for example, here I have December
selected; if I wanted to also select
| | 02:28 | February 2013, if I hold down the Ctrl
key, and click the February segment, all
| | 02:33 | I do is change the filter,
so it displays February.
| | 02:37 | You can also change the time
period by which you filter.
| | 02:40 | So, for example, right now the
timeline is based on individual months.
| | 02:44 | If I want, I can click this time element
or a segment control here, and you can
| | 02:50 | see that I can filter by
Years, Quarters, Months, or Days.
| | 02:53 | I'll go ahead and click Quarters, and
you can see that I have Q1 through Q4 of
| | 02:58 | the years 2013 and 2012.
| | 03:00 | So, if I click Q4 of 2012, I'll see the
data for November and December. There is
| | 03:06 | no data in the source
table for October of 2012.
| | 03:10 | And as before, if I want to extend the
selection, I can drag the right edge of
| | 03:15 | the timeline indicator, so it covers Q1
of 2013, and I get my sales for January
| | 03:21 | through March of that Year.
| | 03:23 | If I want to clear the filter that
I've applied using timeline, I can just
| | 03:27 | click the Clear Filter button, and
again, that's exactly like you do when you
| | 03:31 | work with a slicer, and if I want to
remove the timeline, I just right-click it,
| | 03:36 | and click Remove Timeline.
| | 03:38 | Timelines let you filter
your table based on time data.
| | 03:42 | So, if you have a field in one of
your source tables used in the PivotTable
| | 03:46 | that is formatted as a date, you can
create a timeline, and filter your data
| | 03:49 | visually, so you can see exactly which
time segments are included, and which
| | 03:54 | aren't.
| | Collapse this transcript |
|
|
10. Introducing Data Analysis Expressions (DAX)Introducing the DAX language| 00:00 | When you create formulas to summarize
values in PowerPivot for Excel 2013, you
| | 00:05 | use the Data Analysis Expressions
language, which is called DAX.
| | 00:09 | In this movie, I will give you a quick
look at how the DAX language operates.
| | 00:13 | I will use the Introducing sample
file, which you can find in the Chapter10
| | 00:18 | folder of your Exercise Files archive.
| | 00:21 | To go over to PowerPivot, I'll click
the PowerPivot tab on the ribbon, and then
| | 00:26 | over on the left side of the tab,
I'll click the Manage button.
| | 00:30 | Doing so opens my data in PowerPivot, and
you can see that I have a number of columns.
| | 00:35 | There are two columns that I'll use
here on the right edge of the sheet.
| | 00:39 | I have Sales, which is the number of
sales in a day, and Total, which is the total
| | 00:44 | value of those sales.
| | 00:45 | So, in the Add Column area, I'll
click the top cell, and then to start
| | 00:50 | creating my formula, I'll type an
equal sign, and you can see the formula
| | 00:54 | on the formula bar, but not in the
body of the cell, so that's a little
| | 00:56 | different from Excel.
| | 00:58 | Now I need to type in the first field,
providing a value, and I want to find the
| | 01:02 | average of those sales for a
given day, for a given department.
| | 01:05 | So, I need the total value first,
and divide it by the number of sales.
| | 01:09 | So, I have already typed the equal sign;
type a left square bracket, and from
| | 01:14 | the IntelliSense list that appears, I
will use the down arrow to highlight
| | 01:20 | Total, and press Tab, and you can see
that Total is enclosed in square brackets
| | 01:25 | up on the formula bar.
| | 01:27 | Now to divide, I'll type a forward
slash, then a left square bracket, and I
| | 01:32 | want the number of sales,
which is in the Sales field,
| | 01:36 | so I'll press Tab again to select it
when I've highlighted it, and I now have
| | 01:40 | the formula =Total/Sales, and that
formula will calculate the value for each
| | 01:46 | row, and when I press the Enter key, PowerPivot
adds that calculated column to my dataset.
| | 01:52 | Now, I can reformat the column by
clicking any cell in the column, and then on
| | 01:56 | the Home tab in PowerPivot, clicking
the Apply Currency Format button, and then
| | 02:01 | clicking English (United States), which
is the top entry, and it will give me a
| | 02:06 | dollar sign, with two values
to the right of the decimal point.
| | 02:09 | I can also rename the calculated
column by double-clicking its header cell,
| | 02:14 | and typing in a new name, and in this case,
I'll call it Average Sale, and press Enter.
| | 02:20 | Those are the basics of creating DAX
expressions in PowerPivot for Excel 2013.
| | 02:25 | I'll go into a lot more
detail elsewhere in the chapter.
| | Collapse this transcript |
| Using DAX operators| 00:00 | When you create a calculated column in
PowerPivot for Excel 2013, you combine
| | 00:05 | two different elements;
| | 00:06 | the first are column names, which provide
the values for the calculation, and the
| | 00:10 | second is one or more operators that
tells PowerPivot how to combine the values.
| | 00:14 | So, for example, you can use the plus
sign to add values together, minus
| | 00:19 | to subtract them, and so on.
| | 00:21 | And in this movie, I will give you an
overview of the operators that you can use
| | 00:25 | in PowerPivot for Excel 2013.
| | 00:27 | The operators you can use are
divided into a number of categories.
| | 00:31 | The first is the arithmetic operators,
and these are probably familiar to you,
| | 00:35 | if you've worked in
Excel for any length of time.
| | 00:38 | The plus sign is addition, minus
sign is either subtraction or a minus
| | 00:42 | sign, the asterisk is used for
multiplication, the forward slash
| | 00:47 | for division, and the caret, which you
type by typing Shift+6, indicates exponentiation.
| | 00:54 | So, for example, if you want to indicate
two to the third power, you would type
| | 00:58 | 2, then a caret, and then the number 3.
| | 01:00 | Next are the comparison operators.
| | 01:02 | These are most likely
also familiar to you.
| | 01:05 | You have equal, greater than,
less than, greater than or equal,
| | 01:09 | less than or equal, or not equal to.
| | 01:11 | Some languages use an exclamation point
| | 01:14 | followed by an equal sign to
indicate not equal to, but in DAX, which
| | 01:18 | you use in PowerPivot for Excel 2013,
you use the less than sign, followed by
| | 01:22 | greater than sign.
| | 01:24 | The final group is the
text and logical operators.
| | 01:27 | The first operator is the ampersand,
which combines two arguments into
| | 01:31 | a single text string.
| | 01:33 | The logical operators allow you to
define a condition with more than one part.
| | 01:37 | For example, you can use the double
ampersand, which returns true, if both
| | 01:42 | conditions are true,
so it's an and condition.
| | 01:45 | And the statement here, if the date is greater
than -- that is, after -- 3/14/2013, and
| | 01:51 | the department equal dance, then
that statement would evaluate to true.
| | 01:56 | The double vertical bar, which is
sometimes called the double pipe symbol,
| | 02:00 | because of its use in the UNIX
operating system, is the or operator, so that
| | 02:05 | means it returns true, if at
least one condition is true.
| | 02:09 | So, that means the statement Date > 3/14/2013
or if the Department = "Dance"
| | 02:16 | would return true, if the date were after March
14, 2013, if the department equaled dance, or both.
| | 02:23 | Now I'll switch back over to PowerPivot
to show you a quick example of how to
| | 02:27 | use the text operators, which
you might not be as familiar with.
| | 02:30 | I'm working in a PowerPivot data model
that is saved within the Operators.xlsx
| | 02:36 | sample file, which you can
find in the Chapter10 folder.
| | 02:40 | Let's say that I want to create a
column that displays the text two sales
| | 02:45 | accounted for $370, or whatever the number
of sales is for a particular row, and
| | 02:51 | the total dollars
accumulated by those sales.
| | 02:54 | To do that, I click in the top cell
of the Add Column column, and then I can
| | 02:59 | start typing my formula.
| | 03:00 | So, like in Excel I type an equal sign,
and you can see the equal sign
| | 03:04 | on the formula bar; it doesn't actually
appear in the cell in the sheet. And now
| | 03:09 | I type a left square bracket, which
displays the columns in the table, and in
| | 03:14 | this case I want to use sales.
| | 03:16 | So, I'll just type in S, and then
press Tab to accept Sales, and PowerPivot
| | 03:21 | extends the value for me.
| | 03:23 | Then type Space and Ampersand,
which again is the text concatenation
| | 03:27 | operator, and now I want to put in a
literal string, that is a text string.
| | 03:32 | So, I'll type a double quote,
then a space, and then the word Sales
| | 03:37 | accounted for, then a space, and a
dollar sign, then double quotes, a
| | 03:43 | space, an Ampersand, followed by a
left square bracket, and I want to use
| | 03:49 | the Total column, so I'll type a T, and
the IntelliSense list highlights Total,
| | 03:54 | so I'll press Tab, and
I'll just verify my formula.
| | 03:58 | I have the number from the Sales
column, followed by a space, then sales
| | 04:02 | accounted for, and a dollar sign, and then
the number from the Total column. That looks good.
| | 04:08 | So, I'll press Enter, and you can see
that in my new CalculatedColumn1, I have
| | 04:14 | items such as 2 sales accounted for $370,
3 sales accounted for $219, and so on.
| | 04:20 | And if I want to rename the column, I
can just double-click its header cell,
| | 04:24 | and I'll type in a name, such as Summary,
and press Enter. Those are the basics
| | 04:29 | of using operators to create DAX
expressions in PowerPivot for Excel 2013.
| | Collapse this transcript |
| Surveying DAX functions| 00:00 | Many of the DAX functions that you'll
use in PowerPivot for Excel 2013 are
| | 00:05 | similar to those from the
Excel spreadsheet. That said,
| | 00:08 | many of them are different,
| | 00:09 | so I wanted to show you how to look
inside of the PowerPivot help system to find
| | 00:14 | information on the
functions that are available to you.
| | 00:16 | As my base, I will use the Survey
sample file, which you can find in the
| | 00:21 | Chapter10 folder of your Exercise
Files archive, but you can use any Excel
| | 00:25 | workbook that has a
PowerPivot data model already engaged.
| | 00:28 | To get into the PowerPivot help system,
I'll switch to PowerPivot by clicking
| | 00:33 | the PowerPivot tab on the
ribbon, and then clicking Manage.
| | 00:37 | Doing so displays the
PowerPivot data model.
| | 00:40 | Now, to get help on functions, I'll
click the Help button, which is a small blue
| | 00:44 | circle with a question mark
| | 00:46 | in it at the top right
corner of the program window.
| | 00:49 | So, it's right here, hover over it, and
click to display the Excel Help dialog box,
| | 00:54 | with information on working
with PowerPivot in Excel 2013.
| | 00:57 | I'll search the Help system by clicking
in the search box and typing Function
| | 01:04 | reference, function,
space, reference, and press Enter.
| | 01:10 | I'll look through the series of articles,
and the third one down DAX function
| | 01:14 | reference, is the one that I want,
so I'll click it to display it.
| | 01:18 | This article has a number of
sections that I can display by clicking
| | 01:22 | the appropriate header.
| | 01:23 | The first thing I'll take a look at
are these statistical functions, which is
| | 01:27 | here at the bottom of the window, and
doing so displays all of the functions that
| | 01:31 | are available to me
in the statistical category.
| | 01:34 | And like I said before, many of these will
be familiar to you from the Excel workbook.
| | 01:39 | Now just scroll down, and as an
example, I'll click the MAX Function.
| | 01:44 | Here you can see a summary of the
article, the statement syntax, and any
| | 01:49 | parameters, as well as
what the function returns.
| | 01:52 | I can go back to my previous page by
clicking the Back button, and I'll click it
| | 01:56 | again to go back to the article homepage.
| | 01:59 | Now, just to give you a look at one
more section, I'll click Filter Functions,
| | 02:03 | and you can see the functions that are
available here, and I'll click ALLEXCEPT
| | 02:09 | to display that article, and its
contents, again, explaining how to use the
| | 02:14 | function in PowerPivot.
| | 02:16 | I'll click the Close box
to close the Excel Help system.
| | 02:19 | If you want to look up how to use
specific functions in PowerPivot for Excel
| | 02:23 | 2013, just go into the Help
system, and type function reference.
| | 02:28 | Within the results, you should see an
article titled DAX function reference.
| | 02:33 | That's the one you want.
| | Collapse this transcript |
| Adding calculated columns| 00:00 | When you bring data into PowerPivot for
Excel 2013, you might not have all the
| | 00:05 | summary columns that you want
in the original data source.
| | 00:08 | You can add a new summary by
creating a calculated column;
| | 00:12 | I'll show you how to do
that in this movie.
| | 00:14 | I will use the CalculatedColumn sample
file, which you can find in the Chapter
| | 00:18 | 10 folder of your
Exercise Files archive.
| | 00:21 | So, I'll click the PowerPivot tab on the
ribbon, and click Manage to open the data model.
| | 00:28 | This table in the data model
contains sales data, and I am specifically
| | 00:32 | interested in the number of sales, and
the average sale for a given department
| | 00:37 | on a particular day.
| | 00:38 | What I want to do is add a column that
finds the total of all sales made on a day.
| | 00:44 | To do that, I click the top cell in
the Add Column area, then type an equal
| | 00:49 | sign to start the formula, and you'll
see the formula on the formula bar, as
| | 00:54 | opposed to within the body of the cell.
| | 00:57 | Now I'll type a left square bracket, and
I want to multiply the average sale by
| | 01:02 | the number of sales.
| | 01:03 | So, AverageSale is selected in
the formula auto complete list,
| | 01:08 | so I'll press the Tab key to accept it,
and I see that AverageSale appears. Type
| | 01:13 | an asterisk, which is the multiplication
operator, and then a left square
| | 01:18 | bracket, and from the list that appears,
I want Sales, which is the number of
| | 01:24 | sales on a given day.
| | 01:25 | So, I'll use the down arrow key to highlight
Sales, press Tab, and I'll check my
| | 01:31 | formula; make sure it's right.
| | 01:32 | AverageSale times Sales; looks good, so
I'll press Enter, and doing so creates a
| | 01:37 | calculated column, which
displays the total of the sales.
| | 01:41 | I can format that column by clicking
any cell in the column, and then on the
| | 01:46 | Home tab of the ribbon inside PowerPivot,
click the Apply Currency Format
| | 01:50 | button, select my format which is
United States dollars, and if I want, I can
| | 01:56 | also rename the column by double-clicking
its header, and typing a new name, such
| | 02:02 | as Total, and pressing Enter.
| | 02:04 | And that's how you add a new summary
using a calculated column in PowerPivot
| | 02:09 | for Excel 2013.
| | Collapse this transcript |
| Adding calculated fields| 00:00 | There are two basic ways that you
can summarize a column of data in
| | 00:04 | PowerPivot for Excel 2013.
| | 00:07 | The first is to create a calculated
column, which does a row by row calculation.
| | 00:12 | The second is to create a calculated
field, which works with all of the values in
| | 00:17 | a particular column of data.
| | 00:19 | In this movie, I will show you how to
work with calculated fields, and I will use
| | 00:23 | the CalculatedField sample file, which
you can find in the Chapter10 folder of
| | 00:27 | your Exercise Files archive.
| | 00:29 | If you worked with PowerPivot in Excel 2010,
you might remember something called measures.
| | 00:34 | Calculated fields are measures;
they changed the name in Excel 2013.
| | 00:39 | To create a calculated field, you need
to go into PowerPivot, so click the
| | 00:43 | PowerPivot tab on the ribbon, and then
click Manage to open the data model.
| | 00:48 | To create a calculated field, you need a
PivotTable, so I'll click the PivotTable
| | 00:53 | button, and then in the Insert Pivot
dialog box, I'll ensure that New Worksheet is
| | 00:58 | selected, and click OK.
| | 01:00 | Doing so displays a new
worksheet that contains a PivotTable.
| | 01:03 | Now, on the PowerPivot tab, I can click
the Calculated Fields button, and click
| | 01:09 | New Calculated Field.
| | 01:11 | When I do, the Calculated Field
dialog box opens, and again, if you used
| | 01:15 | PowerPivot in Excel 2010, you'll
see that it's exactly the same as the
| | 01:19 | Measures dialog box.
| | 01:20 | Now I can create my calculated field.
I just need to verify that the proper
| | 01:25 | Table name appears; Table1 is correct,
and then I need to type a name for the
| | 01:29 | calculated field, so I'll select the
value in the Calculated field name box, and
| | 01:34 | I'll call it SummaryTotal.
| | 01:37 | Now I can click in the Formula
pane, and start entering the formula.
| | 01:41 | The first thing I want to do is type in
my summary function, and that is sum, so
| | 01:47 | s-u-m, then a left parenthesis,
and now I can type in the column name.
| | 01:51 | I'll type a left square bracket, and from
the IntelliSense list that appears, I'll
| | 01:56 | use the down arrow key to highlight
Total, and press tab to accept it, then type
| | 02:01 | a right parenthesis to finish the formula.
| | 02:04 | I know that the value in the Total
field is a currency value, so from the
| | 02:07 | Category list, I'll click Currency, and
I'll verify that the symbol is a dollar
| | 02:12 | sign, and I'll leave
the two decimal places there.
| | 02:15 | And with everything in place,
I'll click OK, and when I do,
| | 02:19 | PowerPivot creates my new calculated field, and
adds it to the Values area of the PivotTable.
| | 02:25 | So, I can see that my summary total,
the total of all sales, is $26,601.
| | 02:31 | Now I can change the organization of
the PivotTable using the controls in the
| | 02:35 | PivotTable fields pane.
| | 02:37 | So, let's say that I want
to see sales by department.
| | 02:40 | I'll display the fields in Table1 by
clicking Table1 in the field list, and then
| | 02:46 | I'll drag the Department field down to
the Rows area, and when I release it, the
| | 02:52 | body of my PivotTable shows me sales
for Balance, Dance, Play, Sport, and Work.
| | 02:56 | I could add further divisions either
to the Columns area or to the Rows area.
| | 03:00 | Regardless, the PivotTable would
summarize my data using the summary found in
| | 03:05 | my calculated field.
| | Collapse this transcript |
| Creating aggregate calculations| 00:00 | There are two major ways to create custom
summaries of data in PowerPivot for Excel 2013.
| | 00:06 | The first is to create a calculated
column, which calculates row by row results.
| | 00:11 | The second is to create a calculated
field, which was called a measure in
| | 00:15 | PowerPivot for Excel 2010; that
summarizes the values in one or more columns.
| | 00:20 | It looks at those values as a
whole, instead of row by row.
| | 00:24 | In this movie, I will show you how to
create aggregate functions, which allow you
| | 00:28 | to combine the values from two fields
in your data model instead of just one.
| | 00:33 | I will use the Aggregate sample file,
which you can find in the Chapter10 folder
| | 00:38 | of your Exercise Files archive.
| | 00:40 | This Excel table, which I've already
added to the data model, contains two columns
| | 00:45 | that will give me my total sales data.
| | 00:47 | The first is the AverageSale; that's in
column D in the worksheet. And then the
| | 00:53 | number of customers that made
purchases; that's in column E.
| | 00:56 | So, what I want to do is multiply the values
in column D by column E to get my total sales.
| | 01:02 | Rather than go row by row, I want to
work with it in an aggregate by creating
| | 01:07 | a calculated field.
| | 01:08 | I've already added this table
to the PowerPivot data model.
| | 01:12 | To create the calculated field I want,
I'll click the PowerPivot tab on the
| | 01:16 | ribbon, click the Calculated Fields
button and click New Calculated Field.
| | 01:21 | In the Calculated Field dialog box, the
Table name SalesSummary appears in the
| | 01:25 | Table name box, That's correct, and I
want to edit the Calculated field name from
| | 01:30 | Calculated field 1 to TotalSales.
| | 01:34 | So, I'll do that, I'll leave out the
Description for now, and I'll drag the
| | 01:37 | Calculated Field box over to the side,
so you can see the entire Excel table.
| | 01:42 | So, I want this calculated field to
find the total of all sales by multiplying
| | 01:47 | average sale by customers.
| | 01:49 | First I'll show you the wrong way to do
it; I will generate an error on purpose.
| | 01:53 | Then I'll show you the right way to go.
| | 01:56 | So, let's say that I want to find the
sum, s-u-m, of all values in AverageSale
| | 02:01 | multiplied by all values in Customers.
| | 02:04 | To do that, I'll type sum, then a left
parenthesis, then a left square bracket,
| | 02:09 | and AverageSale is the first item; it
was highlighted, so I could press Tab to
| | 02:14 | accept it. Then an asterisk for
multiply, then a left square bracket, and
| | 02:19 | Customers is the second item, I'll
press the down arrow key to select it.
| | 02:24 | So, I have AverageSale times Customers,
and I am taking the sum, and I've just
| | 02:28 | typed a right parenthesis.
| | 02:30 | That closes out my formula, and to make
sure it's working properly, I'll click the
| | 02:34 | Check formula button.
| | 02:36 | Doing so indicates that there is an
error, and the error is that the sum function
| | 02:40 | only accepts a column
reference as an argument.
| | 02:43 | So, in other words, you can't combine
values for multiple columns; you can
| | 02:46 | only use a single column.
| | 02:47 | There is a version of the sum
function that lets you combine values from
| | 02:51 | multiple fields, and that is sumx.
| | 02:54 | So, I'll just click in my formula, and to
the right of the m, I'll type an x, so I
| | 03:00 | have the sumx function.
| | 03:02 | After I type the x, the tooltip for the
sumx function appears at the bottom, and
| | 03:07 | it says that I need two things: the name of
the table, followed by the expression.
| | 03:11 | I already have the expression in the
formula, so what I need to do is position
| | 03:16 | the cursor so that it is between the
left parenthesis and the first left square
| | 03:21 | bracket, and I need to type in the
name of the table, which is SalesSummary.
| | 03:26 | It appears in the formula auto complete
list, so I'll press Tab to accept it,
| | 03:31 | and then type a Comma.
| | 03:33 | Now when I click Check formula, PowerPivot
indicates that no errors were found.
| | 03:37 | I'll click OK, and my calculated field
is ready for me to use in a PivotTable, or
| | 03:43 | some other calculation.
| | 03:45 | That's how you create an aggregate
function in PowerPivot for Excel 2013.
| | 03:50 | Aggregate functions are used any time
you want to combine values for multiple
| | 03:53 | fields into a single calculated field.
| | 03:56 | The aggregate functions have
an x at the end of their name,
| | 03:59 | so sum becomes sumx, average becomes
averagex ,and count becomes countx,
| | 04:05 | and so on.
| | Collapse this transcript |
| Creating filtered calculations| 00:00 | When you summarize data using
PowerPivot for Excel 2013, you can create
| | 00:05 | calculated fields that find the
aggregate value of an entire column.
| | 00:09 | In this movie, I will show you how to
summarize a subset of data by adding a
| | 00:13 | filter to your calculated field.
| | 00:15 | I will use the Filtered sample workbook,
which you can find in the Chapter10
| | 00:20 | folder of your Exercise Files archive.
| | 00:22 | I've already added this table to the
PowerPivot data model, so I'll click any
| | 00:27 | cell inside the table, and then click the
PowerPivot tab on the ribbon, click the
| | 00:32 | Calculated Fields button, and click
New Calculated Field, and I'll drag the
| | 00:37 | Calculated Field to the right, so
that you can see the entire table.
| | 00:40 | My goal for this calculated field is
to create a filtered field that only
| | 00:44 | summarizes values for weeks where there
were more than 250 customers who placed orders.
| | 00:50 | So, in other words, I only care about rows
where the Customers value is greater than 250.
| | 00:56 | The name of the table that contains my
data is SalesSummary, so that's correct,
| | 01:00 | and for the Calculated name field
I'll name it Over250Sales. I'll leave the
| | 01:06 | Description blank, and now I can start
creating my formula in the Formula pane.
| | 01:11 | I am creating an aggregate function;
in other words, one that combines values
| | 01:16 | for more than one column, so I need to
use an aggregate function that has an x
| | 01:20 | on the end of it's name, and in this case, I'll
use sumx, then I'll type a left parenthesis.
| | 01:26 | Now I need to nest another function
within it, and that function is FILTER,
| | 01:31 | F-I-L-T-E-R, and when I select it from
the auto complete list, and press tab,
| | 01:37 | PowerPivot includes the name
FILTER, and a left parenthesis.
| | 01:41 | Now I need to type in the name of the table,
which is SalesSummary, and then a comma.
| | 01:46 | Now I need to create my filter rule, and
that is using the SalesSummary table's
| | 01:51 | Customers field, which I will use the
down arrow key to highlight, and press tab,
| | 01:56 | and I want greater than 250.
| | 01:59 | So, in other words, I only care about rows
where the Customers number is greater than 250.
| | 02:05 | Type a right parenthesis to close
out the filter arguments, then a comma,
| | 02:10 | and now I can type the expression
that I want to evaluate in my aggregate
| | 02:14 | function. That is SalesSummary AverageSale
multiplied using the asterisk by
| | 02:21 | SalesSummary Customers,
and press tab again,
| | 02:25 | and type a right parenthesis
to close the sumx function.
| | 02:28 | I'll click the Check formula button
to ensure I didn't make any mistakes.
| | 02:33 | Everything looks right, so I'll click OK.
| | 02:35 | Now I can use this filtered
calculated field in a PivotTable, or other
| | 02:39 | summaries within PowerPivot.
| | Collapse this transcript |
|
|
11. Visualizing Power View Data Using Matrices, Cards, and TilesStarting out with Power View| 00:00 | If you're running Office 2013
Professional Plus, and you have Microsoft
| | 00:04 | Silverlight installed on your
system then, you can use Power View.
| | 00:08 | In this movie, I will show you how to
enable the Power View add-in, which also
| | 00:13 | enables PowerPivot, because Power
View runs on top of PowerPivot.
| | 00:16 | I will use the Enable sample workbook,
which you can find in the Chapter11
| | 00:21 | folder of your Exercise Files archive.
| | 00:23 | Creating a Power View sheet from
within a workbook is very straightforward.
| | 00:27 | All you need to do is click the Insert
tab, and then in the Reports group, click
| | 00:33 | the Power View button.
| | 00:34 | Doing so displays Microsoft Excel Add-ins
dialog box, and it tells you that
| | 00:39 | when you enable Power View, you also enable
PowerPivot> That is the goal of this exercise,
| | 00:45 | so I'll click Enable.
| | 00:46 | Doing so opens Power View, and adds the
data from the Excel table that was active
| | 00:52 | when I created my Power View sheet.
| | 00:54 | You can see on the ribbon that
there are three new tabs; the one that's
| | 00:57 | currently displayed, it's called
Design, and then you also have the Power View
| | 01:01 | tab, which lets us you interact with
Power View at a larger level, and then you
| | 01:06 | have PowerPivot, which allows you
to work with data in the data model.
| | 01:10 | If for any reason you want to disable
PowerPivot and Power View, you can do so
| | 01:15 | by going to the Excel Options dialog box.
| | 01:18 | To do that, you need to click a
regular worksheet, and just to demonstrate why
| | 01:22 | that's the case, if you click the
File tab now with a Power View sheet
| | 01:26 | displayed, then the Excel
Options dialog box is not available.
| | 01:30 | So, I'll go back to the workbook, and
click the Sheet1 tab, and now I can click the
| | 01:36 | File tab, and click Options.
| | 01:39 | In the Excel Options dialog box, I'll
click Add-ins, and I can see in the list of
| | 01:43 | Active add-ins that I have
both PowerPivot and Power View.
| | 01:47 | To turn them off, I'll click the
Manage Lists down arrow, and click COM
| | 01:52 | Add-ins, and then click Go.
| | 01:55 | Doing so displays the COM Add-ins
dialog box, and in this case, I want to
| | 01:59 | disable both Power View, and PowerPivot,
and when I click OK, those tabs
| | 02:05 | disappear from the ribbon
| | 02:07 | That's how you turn on Power View in
Excel 2013. Just remember you need
| | 02:11 | Microsoft Silverlight and Office
2013 Professional Plus to use it.
| | Collapse this transcript |
| Installing Microsoft Silverlight| 00:00 | When you're ready to start working with
Power View in Excel 2013 as part of the
| | 00:06 | Office 2013 Professional Plus program
suite, you need to make sure that you have
| | 00:11 | Silverlight installed on your system.
| | 00:13 | In this movie, I will show you how to
run Power View from within Excel 2013, and
| | 00:18 | also to install Silverlight from
within the program if you need to.
| | 00:22 | I will use the BaseData sample file,
which you can find in the Chapter11 folder
| | 00:27 | of your Exercise Files archive,
| | 00:30 | but you can use any file that you
want. All that matters is that you're
| | 00:33 | running Excel 2013, and that it is part of the
Office 2013 Professional Plus program suite.
| | 00:39 | To start Power View, click the Insert tab on
the ribbon, and then click the Power View button.
| | 00:47 | Doing so starts Power View, and
opens a Power View report sheet.
| | 00:51 | If you don't have Silverlight installed
on your system, you'll see a message bar
| | 00:55 | indicating that you need to install
Silverlight, and then click Reload.
| | 01:00 | To do this from within the program,
just click Install Silverlight.
| | 01:04 | When you do, Windows displays your
downloads that are available to you.
| | 01:08 | Look for these silverlight.exe file
that's specific to your operating system.
| | 01:12 | I am running 64-bit Windows, so I see
x64.exe, and I want to run the program, so
| | 01:18 | I'll go ahead and click Run.
| | 01:20 | I get my user account control dialog box
asking me to confirm that I do want to install it.
| | 01:25 | So, I'll click Yes.
| | 01:26 | I'll click Install Now to accept the
License Agreement, because otherwise I
| | 01:29 | don't make progress, and Windows
installs Silverlight on my computer.
| | 01:33 | I get the Installation successful message.
| | 01:35 | I have to restart my browser
for the changes to take effect.
| | 01:38 | From within Excel, that means I need to
click Reload, so I'll click Close, close
| | 01:43 | the installation box, click Close again
to close my download list, and then from
| | 01:47 | within Excel, I'll click Reload.
| | 01:49 | After a moment, Excel 2013 displays a
blank Power View sheet, which I can now
| | 01:55 | populate using the data that I
added to the data model earlier.
| | Collapse this transcript |
| Creating a table or matrix| 00:00 | After you've enabled the Power View in
Excel 2013, you can create a Power View sheet.
| | 00:05 | Once you create the sheet, you can add
tables and matrices to store and display
| | 00:10 | your data, and in this movie, I will
show you how to create those items; both
| | 00:14 | tables, and matrices.
| | 00:15 | I will use the Table sample file, which
you can find in the Chapter11 folder of
| | 00:20 | your Exercise Files archive.
| | 00:22 | One thing I want to point out about
this data is that the month names are a
| | 00:27 | little bit different than you might
expect. Rather than just saying January,
| | 00:30 | February, and March, I've said
01_January, 02_February, and 03_March.
| | 00:37 | The reason I did that is because
there's no such thing as a custom list for
| | 00:41 | sorting in Power View,
| | 00:43 | so rather than sort month names in
alphabetical order, which makes no sense, or
| | 00:48 | reverse alphabetical order,
which also makes no sense,
| | 00:51 | if you want to sort the month names in
order, you need to add a number to the
| | 00:55 | front, and that's the reason that I
have 01_January, 02_February, and 03_March.
| | 01:01 | With that explained, I can
create a Power View sheet.
| | 01:04 | To do that, I'll click the Insert
tab on the ribbon, and click Power View.
| | 01:09 | Doing so creates a Power View sheet, and
also adds the table that I had selected.
| | 01:14 | You can resize your table inside of
your Power View sheet by hovering over one
| | 01:19 | of the handles, and you can see here
that I have a vertical two-way arrow, so
| | 01:23 | I'll just drag it down, and when I do,
all of the data in the table appears.
| | 01:28 | If I were to resize it again, you can
see that there is a vertical scrollbar,
| | 01:33 | so I can move up and down, but in this
case, I want to have all of my data, and
| | 01:37 | the total row, displayed.
| | 01:39 | If I want to turn off the total row, I
can go to the Design contextual tab on
| | 01:43 | the ribbon, click Totals,
and then click None.
| | 01:46 | When you bring data into a Power View
table, you'll notice that it works as a sum.
| | 01:51 | So, for example, we have the sum of the
week numbers for January for the balance
| | 01:55 | department, which is 15 weeks.
| | 01:58 | In other words, it counts week1 as one,
week2 as two, and so on and 1+2+3+4+5 is
| | 02:05 | 15, and you can see that for February ,
which only has four weeks, 1+2+3+4 is 10,
| | 02:11 | and if you look over in the Fields area,
you'll see that week is treated as a
| | 02:15 | sum, because it's a numerical field.
| | 02:18 | For that reason, I'll go ahead and hide
the Week column by clearing its checkbox.
| | 02:23 | Also, just to keep the display a
little bit simpler, I will clear the
| | 02:27 | checkbox next to AverageSale, so
that my table now just has Month,
| | 02:32 | Department, and Customers.
| | 02:33 | If I want to turn this table into a
matrix, all I need to do is ensure it is
| | 02:38 | selected, and anytime it's outlined, it has
the grab handles around the side, then it
| | 02:42 | is selected. Then on the Design tab,
click Table, and then click Matrix.
| | 02:47 | Doing so changes the objects, so that
you can have columns and row headers.
| | 02:52 | So, it's a little bit like a PivotTable,
instead of an Excel table, which is
| | 02:56 | column based; you can have both row
and column headers, as in a PivotTable,
| | 03:01 | versus an Excel table.
| | 03:03 | You also notice that the interface
over in the Power View Fields area changed.
| | 03:08 | Now I have Tile By, which
I won't talk about here;
| | 03:10 | Values, which provides the values for
the interior of the matrix, then I have
| | 03:15 | row headers, Month and Department,
| | 03:17 | and now I have a Columns area.
| | 03:19 | So, I can add a field to the Columns area,
so that I have a matrix, or what used
| | 03:25 | to be called a crosstab
in older versions of Excel.
| | 03:28 | So, I'll drag down the Week field to the
Columns area, and then resize the matrix,
| | 03:35 | so that you can see all of the data.
| | 03:37 | Now you can see that rather than using
the week numbers as a sum, it's instead
| | 03:42 | using the values, so you can see sales
for week 1, week 2, week 3, week 4, and
| | 03:47 | week 5 for January and March.
| | 03:49 | So, that's how you add data to
the body of a Power View sheet.
| | 03:53 | You can arrange it as a table, which is
strictly a column, or you can work with it
| | 03:58 | in a matrix, which allows you to
have both column and row headers.
| | Collapse this transcript |
| Creating a card| 00:00 | You have several different options for
how to display data in Power View, and in
| | 00:04 | this movie I will show
you how to create cards.
| | 00:07 | I will use the Card sample file, which
you can find in the Chapter11 folder of
| | 00:12 | your Exercise Files archive.
| | 00:14 | This workbook already has a
Power View sheet inside of it,
| | 00:17 | so I'll click its tab, Power View2, to
display the sheet, and I'll just type
| | 00:22 | a quick title for the sheet; I'll call it
Sales Cards, and click outside of the title box.
| | 00:30 | On the right side of the program window,
I have the Power View Fields pane, and
| | 00:34 | just in case the SalesSummary tables
fields weren't displayed, I'll collapse the
| | 00:39 | list by clicking the black downward
pointing arrow, and then if I want to expand
| | 00:44 | the list, I can click the right pointing
white triangle or arrow, and that's what
| | 00:49 | displays the fields
in the SalesSummary table.
| | 00:51 | Now I can start adding fields to the Fields
area down here in the bottom half of the pane.
| | 00:57 | So, for example, if I want to add
Department as my first level, I can add it, and
| | 01:02 | doing so creates a table
inside of the Power View sheet.
| | 01:06 | I can change that to a card by ensuring
the table is selected, and then on the
| | 01:11 | Design contextual tab, click the
Table button, and then click Card.
| | 01:16 | Doing so changes the table to a card,
and I'll resize the card control here to
| | 01:22 | display its entire contents.
| | 01:24 | So, basically I have a display
area for each of my five departments.
| | 01:28 | If I want, I can change the card style
by making sure the card is selected, and
| | 01:32 | again, on the Design tab, clicking the
Card Style button, and then changing the
| | 01:37 | Style from Card to Callout.
| | 01:39 | Doing so changes the formatting of the
card, so it's all a matter of preference
| | 01:43 | which one you prefer.
| | 01:45 | In my case, I prefer the previous style,
the Card Style, so I'll click the Card
| | 01:49 | Style button, and click Card.
| | 01:51 | Now I can add additional fields to the cards.
| | 01:53 | So, let's say that I want to display the total
number of customers added to those departments.
| | 01:58 | For that, I'll just drag the Customers
field below Department in the Fields box
| | 02:02 | at the bottom, and I'll
resize the card control.
| | 02:06 | Now you can see that I have the
number of customers for each of my fields.
| | 02:11 | So, I have one for Balance,
Dance, Play, Sport, and Work.
| | 02:14 | If I want to format the text, such as by
making it larger, I can click any text
| | 02:20 | box that I want, and you can see that
the customers number is selected, and then
| | 02:26 | in the Text group, click
the Increase Font Size button.
| | 02:29 | Then if I want, I can resize the card
control by dragging any of its grab handles,
| | 02:35 | or I can make it smaller.
| | 02:37 | So, for example, if I wanted to make it
very short, and only display one card at a
| | 02:41 | time, I can do that.
| | 02:43 | If you're building a dashboard out of
Power View, then you'll probably want to
| | 02:47 | get as many items as you can on to the
page, at least reasonably, and that means
| | 02:52 | that resizing a control, such as I've done here
with the card control, wouldn't be unreasonable.
| | 02:57 | When there are more items in a
control than can be displayed, you'll get a
| | 03:01 | vertical or horizontal scrollbar. So,
here I have a vertical scrollbar, and if I
| | 03:05 | click the down arrow, I can scroll down,
and each click takes me down to another
| | 03:11 | department, and clicking the up
arrow does the same thing, going up.
| | 03:15 | That's how you add a card to a
Power View sheet in Excel 2013.
| | 03:20 | Cards show values in a small space,
and allow you to focus on exactly the
| | 03:24 | data that you want.
| | 03:26 | If you want to display more than one
value at a time, you can always resize
| | 03:29 | the control.
| | Collapse this transcript |
| Creating a tile| 00:00 | When you're ready to summarize your
data in Power View, you can use a number
| | 00:04 | of different controls.
| | 00:05 | In this movie, I will show
you how to use tiles.
| | 00:08 | There are two separate types: a tab
strip, and a flow tile. I will use the
| | 00:13 | Tiles sample workbook, which you can
find in the Chapter11 folder of your
| | 00:17 | Exercise Files archive.
| | 00:19 | To move over to Power View, I'll click the
Power View2 sheet at the bottom of the workbook.
| | 00:24 | Doing so switches us over to Power View.
| | 00:27 | Now in the Power View Fields pane, I
can add fields from the SaleSummary
| | 00:32 | table to the Fields area.
| | 00:34 | To do that, I'll click the expand
control next to the SaleSummary table name,
| | 00:39 | and now I can add a field to the
Fields area, and I'll start with Department.
| | 00:44 | Adding that field creates a table
in the body of the Power View sheet.
| | 00:47 | If for some reason you can't drag the
field header to the Fields area, then
| | 00:52 | check the box next to
its field to add it.
| | 00:54 | Now, with the Table selected -- I can tell
it's selected, because I can see its outline --
| | 00:58 | on the Design tab,
I'll click the Tiles button.
| | 01:01 | Doing so displays this data as a
series of tiles, and when I hover the mouse
| | 01:07 | pointer over the edge of the control,
its edges and grab handles appear.
| | 01:11 | So, I'll just go ahead and resize it, so
that I can see Balance, Dance, and Play.
| | 01:18 | Now I can add more fields to the control
by adding those fields to the Fields area.
| | 01:23 | So, I'll drop down Month by dragging it
from the Fields area to the Fields box,
| | 01:29 | and then I'll drag down Customers.
| | 01:32 | And as before, if you can't drag the
Field header down to the Fields area, check
| | 01:36 | the box next to its name to
add it to the Fields area.
| | 01:39 | Doing so creates a tab strip that displays
the department, also shown here, and
| | 01:46 | the month, and the number of customers.
| | 01:48 | If I resize the control vertically by
grabbing the center handle for the entire
| | 01:54 | tab strip, I can drag it down, and see
all of my data, so now I can see the
| | 02:00 | department, the individual months, and
the number of customers, plus the total.
| | 02:03 | I can display a different
department by clicking its name.
| | 02:06 | So, if I click Dance, I switch to
the Dance department, Play to the Play
| | 02:11 | department; I can also scroll within the
tab by clicking these scroll buttons, so
| | 02:15 | that displays Sport, and one more time
to display Work, and going to the left, it's
| | 02:20 | just a matter of clicking the
triangle at the other end of the tab strip.
| | 02:24 | I can change the tile's type by clicking
the tab control, and then again on the
| | 02:29 | Diesgn tab, clicking the Tile Type
button, and instead of Tab Strip, click Tile
| | 02:35 | Flow, and again, I'll resize the control,
just so I can see its entire contents.
| | 02:40 | You'll see that instead of having the
navigation controls around the top, I now
| | 02:45 | have my department names at the bottom.
So, I am currently on the Play tab; if I
| | 02:49 | click Sport, it changes the display.
If I click Work, it changes there, and go
| | 02:54 | back to Play, and if I want, I can also
drag using the slider on the bottom, so I
| | 03:00 | can move from Balance, to Dance,
to Play, to Sport, and to Work.
| | 03:04 | Using tiles to summarize and
display your data allows you to shift from
| | 03:08 | department to department quickly, and it
also adds an interesting visual element.
| | 03:12 | Whether you use a Tab Strip tile or a
Tile Flow tile is up to you, and is a
| | 03:17 | matter of aesthetics.
| | Collapse this transcript |
| Filtering Power View objects| 00:00 | When you summarize data visually using
Power View, you might want to limit the
| | 00:04 | data that appears on your Power View sheet.
| | 00:07 | In this movie, I will show you how to limit
the data that appears by creating filters.
| | 00:12 | I will use the Filter sample workbook,
which you can find in the Chapter11 folder
| | 00:16 | of your Exercise Files archive.
| | 00:19 | This workbook already has a Power
View sheet in it, so I'll click the Power
| | 00:23 | View2 sheet tab on the Tab bar, and
doing so displays the Power View sheet.
| | 00:30 | This sheet contains a table,
and it shows three fields;
| | 00:34 | Department, Month, and the
number of Customers for that month.
| | 00:37 | If I want to limit the data that appears
in Power View, then I can drag a field
| | 00:41 | from the Power View Fields
pane to the Filters area.
| | 00:45 | So, let's say that I
want to filter by month.
| | 00:47 | Then I would just drag the Month field
to the Filters area, and doing so displays
| | 00:53 | the Filters interface for that field.
| | 00:55 | I have a series of checkboxes,
which are like a selection filter in
| | 00:58 | normal PivotTables.
| | 01:00 | So, if I want to display only
values for January, I can check that box.
| | 01:04 | If I want to show for both January and
March, I can also check the 03_March box.
| | 01:10 | If I want to remove the filter, and
display all values, then I can uncheck the
| | 01:16 | All box, so that all of
the checkboxes are clear.
| | 01:20 | I can also create an advanced filter.
| | 01:22 | To do that, I click the Advanced
Filter button, which is here, and doing so
| | 01:27 | displays the Filters interface, so
that I have a little bit more control.
| | 01:31 | It's a lot like the advanced filter dialog
box that you find in the normal Excel interface.
| | 01:36 | So, for example, I can create my
comparison rule, so I can show items for
| | 01:40 | which the value contains a certain
string, doesn't contain, starts with,
| | 01:44 | doesn't start with, and so on.
| | 01:46 | So, let's say, for example, that I want to
stay with contains, so I'll check it, and
| | 01:50 | I only want to display months that
end in a-r-y, so January and February.
| | 01:55 | I have contains as my rule, so I'll type a-r-y,
and then at the bottom, click Apply Filter.
| | 02:02 | Now, through what, I admit, is a fairly
contrived example, I only see results for
| | 02:06 | January and February.
| | 02:08 | I can also create compound
filters that have two conditions.
| | 02:11 | So, for example, if I want two things to
be true, then I can use an And condition,
| | 02:16 | or create an And rule.
| | 02:18 | On the other hand, if I want only one or
both rules to be met, then I can click
| | 02:24 | Or. In this case, I'll stay with And,
| | 02:27 | and I'll create another contains rule.
| | 02:29 | So, let's say that I'm only interested
in months that contain a-r-y, and also
| | 02:34 | contain the letter f. When I click
Apply Filter, Power View limits the data in
| | 02:39 | my table, so it only displays the month
of February, which is the only month with
| | 02:44 | a name that contains both the
letter f, and the letters a-r-y.
| | 02:49 | If I were to change this to an Or rule
by clicking Or, and then clicking Apply
| | 02:53 | Filter, I'd see the months of January
and February, because January contains
| | 02:58 | a-r-y, and even though it doesn't
contain the letter f, the rule still holds.
| | 03:03 | If you want to clear a filter without
removing it, then you can click the Clear
| | 03:08 | Filter button, and you
retain the controls here.
| | 03:12 | If you want to switch from the advanced
filter mode to the regular filter mode,
| | 03:16 | what I call selection mode, then you
can click the List Filter mode button, and
| | 03:21 | if you want to remove the filter field
entirely, you can click the Delete Filter button.
| | 03:26 | And that's how you limit the data
that appears inside of your controls in
| | 03:29 | Power View.
| | Collapse this transcript |
| Formatting Power View objects| 00:00 | When you add items to a Power View
sheet, Excel applies default formatting.
| | 00:05 | In this movie, I will show you how to
change the formatting of the elements that
| | 00:09 | you add to your Power View sheets.
| | 00:11 | I will use the Format sample file, which
you can find in the Chapter11 folder of
| | 00:16 | your Exercise Files archive.
| | 00:18 | This workbook already contains a Power
View sheet, so I'll click the Power View
| | 00:23 | sheet tab to display it.
| | 00:25 | This is a fairly simple sheet. It only
displays a card control, and a title, and I
| | 00:31 | haven't added any text to the title
yet, so I'll go ahead and click to add the
| | 00:36 | title, and in this case, I'll make it
Data Formatting, and click to the side.
| | 00:42 | I can format the text in the text control
by selecting it, and then on the Text
| | 00:47 | tab, I can change Alignment, and I can
also change the Font, and Font Size, plus
| | 00:53 | Bold face, Italic, and Underline.
| | 00:55 | So, to change the Alignment, I can
align it to the center by clicking the
| | 01:00 | Align Text Center button.
| | 01:01 | I can also change the font, for example,
changing from Segoe UI Light to Arial
| | 01:07 | Black, and if I want,
I can underline the text.
| | 01:10 | I could do similar things
for the card control.
| | 01:12 | If I click the card control, now I can
use the controls on the Power View tab of
| | 01:17 | the ribbon to change its
theme; that is, this color scheme.
| | 01:21 | So, for example, if I scroll down to the
bottom, or near the bottom, and click the
| | 01:25 | Elemental theme, doing so changes the
font, the color, and also any effects
| | 01:30 | that are applied to it.
| | 01:31 | I can also use controls here. For
example, if I want to change the font of the
| | 01:36 | Department field, then I can click it, click
the Font, and then I'll change it to Calibri.
| | 01:43 | If I want to resize a control, I can do
that by grabbing and dragging one of its
| | 01:48 | handles, like I do here for the card.
| | 01:50 | And if I make the card smaller, then
it needs to be to display all of its
| | 01:55 | contents, then you'll see a
scrollbar here on the side.
| | 01:58 | You can size it horizontally as well.
| | 02:01 | You can also add other visual
elements; for example, a text box.
| | 02:05 | For that, you just click Text Box,
and one appears, and now you can add
| | 02:09 | any additional text.
| | 02:10 | So say, for example, Data current as of
3/14/2013, and like any other control, you
| | 02:19 | can use the handles to resize it.
| | 02:21 | And clicking outside of the control returns
you to the main Power View tab on the ribbon.
| | 02:26 | You can also add pictures to a Power
View sheet by clicking the Picture button
| | 02:30 | here; that allows you to
add an individual photo.
| | 02:33 | It's very similar to what you do in any
of the other Office programs, including
| | 02:37 | Excel, so I won't go into detail here.
| | 02:39 | Those are the basics of changing the formatting
of your items within a Power View sheet.
| | 02:45 | You don't have all of the flexibility
that you have in the main Excel program,
| | 02:49 | but you can make things
look the way you want them to.
| | Collapse this transcript |
|
|
12. Visualizing Power View Data Using Charts and MapsCreating a column or bar chart| 00:00 | After you add a table to a Power View
sheet, you can summarize that table's data
| | 00:05 | by creating a chart,
| | 00:06 | and in this movie, I will show you how to
create and work with both column and bar charts.
| | 00:12 | I will use as my sample file the ColumnAndBar
workbook, which you can find in the
| | 00:17 | Chapter12 folder of your
Exercise Files archive.
| | 00:20 | I've displayed the Power View2 sheet,
which contains a Power View sheet and a table.
| | 00:27 | To create a chart based on this table,
I'll click anywhere in the body of the
| | 00:30 | table, and then click the Design tab,
and in the Switch Visualization area, I
| | 00:36 | can select the type of chart that I
want to create, and in this case, I will
| | 00:40 | click a Column Chart.
| | 00:41 | I have three options: Clustered Column,
Stacked Column, and 100% Stacked Column.
| | 00:47 | The most common version is the Clustered
Column, so I'll go ahead and click that
| | 00:51 | to show you what the chart looks like,
and I'll resize it, so it's more visible.
| | 00:55 | You can see that Power View created
the chart with the department along the
| | 01:00 | bottom, sales levels along the top, and
each one of the bars represents a month;
| | 01:05 | blue for January, red for
February, and orange for March.
| | 01:09 | The order of the fields in the Power
View Fields pane also changed. So, for
| | 01:12 | example, you can see that the Values
area stayed the same, but we now have an
| | 01:18 | Axis, which is Department,
and Legend, which is Month.
| | 01:21 | If I drag the Month field to the
Axis area, doing so creates a dual axis.
| | 01:26 | I have Month and Department together,
and what I'll do now is drag Department
| | 01:30 | down to Legend, and doing so gives me
three horizontal divisions, one for each
| | 01:36 | of the three months, and now each of the
vertical bars or columns represents a department.
| | 01:41 | I can resize a chart, like
any other object in Power View.
| | 01:44 | I can just grab one of the handles;
in this case, I'll grab the bottom right
| | 01:47 | corner, and I can resize it, and you see
that the contents of the chart object
| | 01:52 | are resized. If I go vertically, it looks
like that,;if I go horizontally, it looks like that.
| | 01:58 | As I get to the horizontal limit, in
other words, where Power View doesn't display
| | 02:02 | everything within the body of the object,
then you can see that first thing it
| | 02:07 | get scrunched up on the horizontal
axis, and then the legend values, if the
| | 02:12 | legend is to the right, start
to be grayed out at the edges.
| | 02:16 | So, Balance, Dance, and so on, and I'll
just move it out, so that everything fits
| | 02:21 | the way it did before, with a
little bit of room to spare.
| | 02:24 | If I want to change the charts formatting,
while it's selected, I can click the
| | 02:28 | Layout contextual tab, and I can use
the controls here to affect different
| | 02:33 | parts of the chart.
| | 02:34 | For example, if I want, I can hide or
display the chart title. I can also change
| | 02:40 | the presence or position of the legend.
| | 02:44 | So, for example, if I want the legend to
appear at the top, I can do that, or I can
| | 02:48 | also select to have it
appear at the bottom.
| | 02:51 | You can play with those different
variations to see which one you like best.
| | 02:55 | You can also add data labels.
| | 02:57 | So, for example, if you want to see the
exact value for a particular column inside
| | 03:01 | of this chart,
I could click, say, Center.
| | 03:03 | I don't find data labels to be very
useful for Power View charts, simply because
| | 03:08 | I'm usually creating them for a dashboarding
application, and in that case, the
| | 03:12 | chart is so small, every square inch
matters, so most of the time I will turn
| | 03:17 | data labels off by clicking None.
| | 03:19 | If you want to display this data using a
bar chart, which is a horizontal version
| | 03:23 | of a column chart, while the chart is
selected, click Design, and then in the
| | 03:28 | Switch Visualization area, click Bar
Chart, and then click the type that you want.
| | 03:33 | In this case, I'll make it Clustered Bar.
| | 03:34 | When I do, you can see that instead
of having a column chart I now have a
| | 03:39 | clustered bar chart
which runs horizontally.
| | 03:42 | I personally prefer the
column chart, so I'll switch back,
| | 03:45 | but as with everything,
it's a matter of taste.
| | Collapse this transcript |
| Creating a pie chart| 00:00 | If you have a Power View data set
that summarizes data by category, such as
| | 00:05 | number of new customers acquired by
department, then you can summarize the
| | 00:09 | data in a pie chart.
| | 00:10 | In this movie, I will show you how to
create a pie chart in Power View for Excel 2013.
| | 00:15 | I will use the Pie sample workbook, which
you can find in the Chapter12 folder of
| | 00:20 | your Exercise Files archive.
| | 00:22 | I've already displayed the Power View2
sheet, which contains my Power View page.
| | 00:28 | Now I can click the table that contains
the data I want to summarize, and then I
| | 00:33 | can click the Design tab, click
the Other Chart button, and click Pie.
| | 00:38 | Doing so creates a pie chart that I
will resize, so it's easier for you to see.
| | 00:44 | This pie chart has five wedges, and they are
identified by color; one for each department.
| | 00:49 | I can format the chart while it is
selected by clicking the Layout tab on the
| | 00:53 | ribbon, and then I can use the controls
in the Legend group to control where the
| | 00:58 | legend appears, if at all, and the
Title group to indicate the chart's title;
| | 01:04 | either take it away by clicking None,
or put Above Chart to have it displayed
| | 01:08 | above chart, as it is here.
| | 01:10 | You can also create subslices
within your pie chart.
| | 01:14 | To do that, you drag a field to the
Slices area, which is below the Color area in
| | 01:20 | the Power View Fields pane.
| | 01:21 | So, for example, let's say that I want
to create a subslice for each month.
| | 01:25 | To do that, I would drag Month to the
Slices area. You can probably see that
| | 01:31 | there are white whites inside
each wedge in the body of a chart.
| | 01:35 | If I hover my mouse pointer over particular
slice within a wedge, then I get the
| | 01:39 | department, the month, and the number of
customers for that particular segment.
| | 01:43 | I can do the same thing for the second
segment for February, and the third for
| | 01:47 | March, and it's the same for
each of the other departments.
| | 01:51 | And that's how you create
a pie chart in Power View.
| | 01:54 | I think you'll find that adding slices
to each wedge of your pie chart allows
| | 01:59 | you to move through your data more
quickly, and also to explain in a flowing,
| | 02:03 | fluid manner, as opposed to
simply pointing at a static chart.
| | Collapse this transcript |
| Creating a line chart| 00:00 | When you have data that is based on a
time series, such as the number of new
| | 00:04 | customers acquired over a series of
months, then you can represent that data in
| | 00:09 | Power View using a line chart.
| | 00:11 | In this movie, I will use the Line
sample file to show you how to create line
| | 00:15 | charts from an existing table
in Power View for Excel 2013.
| | 00:19 | This table has three columns: Department,
Month, and Customers, and my goal is to
| | 00:25 | create a line chart that
summarizes that data over time.
| | 00:29 | To do that, I make sure the table is selected.
I can see the outline, so I know it is.
| | 00:34 | And then on the Design contextual tab of the
ribbon, I click Other Chart, and then click Line.
| | 00:40 | And when I do, Power View creates a
line chart that's about the same width as
| | 00:45 | the table was before, and I'll drag the
bottom right corner handle to make it
| | 00:50 | larger, and easier to see.
| | 00:51 | This chart's current configuration
doesn't make sense for a line chart, and the
| | 00:56 | reason is that the Month field -- January,
February, and March -- is used to provide
| | 01:00 | values for each of the
lines, and the Departments --
| | 01:03 | Balance, Dance, Play, Sport, and Work --
are along the horizontal axis, which is
| | 01:08 | usually used for time.
| | 01:09 | So, all I need to do is switch them to
make the chart make a little bit more sense.
| | 01:13 | I can do that over in the Power View
Fields area. I will drag Month up to
| | 01:18 | the Axis area below Department, and that gives
me a summary of monthly sales by department.
| | 01:24 | Now I can drag Department down to the Legend
area, and this gives me a graph with the months,
| | 01:30 | January, February and March, along the
horizontal axis, and five lines, one each
| | 01:34 | for the Balance, Dance, Play,
Sport, and Work departments.
| | 01:37 | You can see that four of the five
departments follow the same pattern, with a
| | 01:42 | large number of new customers in January,
a significant falloff in February, and
| | 01:47 | an increase from February in March.
| | 01:50 | The Work department, represented by the
purple line, follows a different pattern.
| | 01:53 | It shows growth from a low in January,
to about 1182 new customers in February,
| | 02:00 | and a similar increase in March.
| | 02:03 | If I want to change the formatting of a
chart, I can do so by clicking the Layout tab.
| | 02:08 | The Layout tab gives me tools to
change the Title, whether or not it appears,
| | 02:12 | the Legend whether and where
it appears, and also Data Labels.
| | 02:17 | So, for example, if I want to add labels
for each individual data point, I can do
| | 02:22 | so by clicking Auto, Center,
or any of the others.
| | 02:25 | If I want them to go away, which I
usually prefer to do, I click None.
| | 02:29 | Line charts are terrific for working
with time series data, so if you have sales
| | 02:33 | or other data over days, months, or
years, feel free to use line charts to
| | 02:38 | summarize it within Power View.
| | Collapse this transcript |
| Creating a map| 00:00 | One fun and useful aspect of Power
View in Excel 2013 is its integration with
| | 00:06 | Bing maps, a Microsoft mapping service.
| | 00:09 | In this movie, I will show you how to
use geographic data that you might have in
| | 00:13 | a Power View table in a map
control on your Power View sheets.
| | 00:17 | I will use the Map workbook, which you
can find in the Chapter12 folder of your
| | 00:22 | sample files archive.
| | 00:24 | This table contains a list of states, and the
number of orders from each of those states.
| | 00:29 | I'd like to create a map, so that I can
get a visual representation of how the
| | 00:34 | sales are distributed.
| | 00:35 | To do that, I click the table, and then
on the Design tab, I click the Map button.
| | 00:41 | Doing so creates a map
control on my Power View sheet.
| | 00:45 | The first time you create a map
inside of Power View, Excel will display a
| | 00:50 | message box just above the Power View
sheet, and below the ribbon, indicating that
| | 00:54 | you have to enable a connection to
Bing maps, so that it can geocode the data.
| | 00:58 | I've already done that once, so
the message didn't appear for me.
| | 01:02 | If it does, all you need to do is click
the Enable button, and you'll keep on working.
| | 01:06 | My map appears in my Power View sheet.
| | 01:09 | I can resize my map control by dragging
any of the handles, and you can see that
| | 01:14 | I was zooming in as
I was resizing the control.
| | 01:17 | To change the zoom level inside of the
control, I'll just move the mouse pointer
| | 01:21 | over the control, and I can either
use the zoom out or zoom in buttons.
| | 01:26 | So, if I zoom in, I get more
detail; if I zoom out, I get less.
| | 01:30 | I can also pan using the pan control
here, so I can pan up, I can down, pan
| | 01:37 | right, pan left, and so on.
| | 01:39 | I can also control the zoom level
using the scroll wheel on my mouse.
| | 01:42 | So, if I scroll up, I zoom in, and
if I scroll down, then I zoom out.
| | 01:48 | The blue circles in each of the states
indicates the relative number of orders.
| | 01:52 | So, for example, California has the most
by far, and a state such as Arizona had
| | 01:57 | less, and Idaho had fewer still.
| | 02:01 | You can work with the
map control by itself
| | 02:03 | if you click the pop out button at the
top right corner of the control; doing so
| | 02:08 | expands the map control, so it fits the
entire sheet, and when you're ready to
| | 02:13 | take it back to its regular size, just
click the pop in button, and it returns.
| | 02:18 | Maps are terrific for helping you
visualize data, and this is actually my
| | 02:22 | favorite feature in Power View.
| | 02:23 | The integration of Bing maps with
Excel 2013 gives you great new ways to
| | 02:28 | summarize your data
visually in Power View.
| | Collapse this transcript |
| Creating maps with multivalue series| 00:00 | In the previous movie, I showed you how
to create a map using a two column table
| | 00:05 | in Power View for Excel 2013.
| | 00:08 | In this movie, I will show you how to
create a similar map, but this time for
| | 00:12 | a three column table.
| | 00:13 | I will use the MultiSeries sample file,
which you can find in the Chapter12
| | 00:17 | folder of your Exercise Files archive.
| | 00:20 | This table contains three columns: the
first is the State, the second is the
| | 00:24 | Department, and the third is the number of
orders for that department, for that state.
| | 00:29 | So, rather than just having state names
and a number of orders, this table will
| | 00:33 | have that data broken
down by department as well.
| | 00:36 | So, let me show you what happens.
| | 00:38 | First I click the table to make sure it's
active, and then on the Design tab, click Map.
| | 00:43 | After you click Map, the map control
appears, and you can resize the control, so
| | 00:49 | that everything appears within it, and
you can see that stretching or resizing
| | 00:54 | the control changes the zoom level.
| | 00:56 | You can zoom in or out using your mouse
or scroll wheel. In this case, scrolling
| | 01:01 | up moves in, and scrolling out zooms out.
| | 01:05 | This data collection tracks sales for
five states, and the size of a circle
| | 01:09 | within the border of each of those
states indicates the relative amount of
| | 01:13 | orders that originated from there.
| | 01:16 | So, Virginia had the most, followed by
West Virginia, then probably Tennessee,
| | 01:21 | North Carolina, and Kentucky.
| | 01:22 | And you notice that when I hover the
mouse pointer over one of the pie charts,
| | 01:27 | it expands, and it indicates the number
of orders for a particular department.
| | 01:33 | So, if I hover over the purple, for
Kentucky, I see that there are 258 orders,
| | 01:37 | over the blue for Balance is 80, red
for Dance is 41, orange for Play is 58, and
| | 01:44 | green for Sport is 176.
| | 01:47 | And you can see the same thing
for the other states as well.
| | 01:50 | One interesting thing to note is that
if you zoom out, the pie charts appear to
| | 01:54 | get larger, and if you zoom in,
the pie chart gets smaller.
| | 01:57 | I'm not sure why that choice was made,
but don't be surprised if you see it.
| | 02:01 | It is acting as it's supposed to.
| | Collapse this transcript |
| Creating chart multiples| 00:00 | Some of the data that you summarize
using Power View in Excel 2013 will come
| | 00:04 | from a very simple source.
| | 00:06 | For example, you might have a list of
sales by state, and simply display that in
| | 00:12 | a chart, or perhaps within the table.
| | 00:14 | It's more likely, however, that your data source
will contain multiple columns, as this one does.
| | 00:19 | My chart displays Department Sales by
State, and that there is a value axis as well.
| | 00:24 | So, I have three columns:
Department, State, and Sales.
| | 00:28 | In this movie, I will show you how to
create multiple charts, so that you can
| | 00:32 | represent each of your departments, or
perhaps states, in a separate chart in
| | 00:36 | your chart control.
| | 00:37 | I will use the Multiples sample file,
which you can find in the Chapter12 folder
| | 00:42 | of your Exercise Files archive.
| | 00:44 | So again, I am representing
this data source in a single chart,
| | 00:48 | but if I want, I can
create multiple charts,
| | 00:50 | and to do that, I will use the controls
in the Power View Fields pane at the
| | 00:55 | right edge of the program window.
| | 00:56 | I am going to drag the border between
the top and bottom areas of the Power View
| | 01:00 | Fields pane, so that you
can see what I'm doing.
| | 01:03 | Below the Legend area, there
are two areas called Multiples.
| | 01:07 | The first is Vertical Multiples, and
the second is Horizontal Multiples.
| | 01:11 | You'll see the distinction
between the two of them in a moment.
| | 01:14 | Let's say that I want to create a
separate chart for each department.
| | 01:18 | Then I can drag the Department field from
the Legend area to either of the two multiples;
| | 01:24 | in this case, let's make it Vertical.
| | 01:25 | Doing so creates separate
charts for each of the departments.
| | 01:29 | You can see that I have Balance,
Dance, Play, Work, and Sport represented
| | 01:34 | in separate charts.
| | 01:36 | If I wanted to create horizontal
multiples, then I could drag Department to
| | 01:40 | the Horizontal area, and doing so
displays each of the states and each of the
| | 01:44 | departments in a separate chart, and
because the five charts won't fit on a
| | 01:49 | single screen, I can use the horizontal
scrollbar to move back and forth, so I
| | 01:53 | see Work, Sport, and Play, and if I go back to
the other side, I see Balance, Dance, and Play.
| | 01:59 | If you want, you can also create both
horizontal and vertical multiples, so if I
| | 02:04 | drag the State field from the
Axis area to the Vertical Mulitples area,
| | 02:08 | then I would get a separate chart
for each Department, for each State.
| | 02:12 | And I can scroll up and down to see the
different states, and side to side to see
| | 02:17 | the various departments.
| | 02:18 | Most of the time, however, I would probably
stay with a single set of multiples,
| | 02:23 | such as we have here.
| | 02:24 | Creating multiple charts is very useful,
but be aware that they do take up space
| | 02:28 | within your Power View sheets.
| | 02:30 | If you're creating a dashboard, it
might be useful to create multiples for a
| | 02:34 | moment, and then return the
control to its original configuration.
| | Collapse this transcript |
|
|
ConclusionNext steps| 00:00 | Thanks again for joining me in
Excel 2013: Pivot Tables in Depth.
| | 00:04 | I hope you've learned a lot, and that
you've been able to use PivotTables to
| | 00:08 | enhance your business analysis.
| | 00:10 | I did want to recommend two other
resources that you can use to further your
| | 00:13 | education on PivotTables.
| | 00:15 | The first is a book by Bill Jelen and
Michael Alexander called Excel 2013 Pivot
| | 00:20 | Table Data Crunching.
| | 00:21 | It covers many of the same topics that
I cover in this course, but it gives you
| | 00:25 | a different perspective.
| | 00:26 | The second book I recommend deals with
PowerPivot, and it's called DAX Formulas
| | 00:31 | for PowerPivot by Rob Collie.
| | 00:33 | Rob is a former Microsoft employee, he
was on the PowerPivot team, and now he is
| | 00:38 | a Microsoft MVP for Excel, and he does
his own consulting, and also he's an author,
| | 00:43 | as you can see by the book.
| | 00:45 | He does terrific work, he has great
insights, and if you want to work with
| | 00:49 | PowerPivot seriously,
that's the book to have.
| | 00:51 | Thanks again, and best of luck!
| | Collapse this transcript |
|
|
