[Upbeat music]
How can you save time and easily summarize data in Microsoft Excel?
Create pivot tables. Hi, I'm Dawn Bjork, The Software Pro.
I'm a Microsoft Certified Trainer and certified Excel Expert.
In this online training, we'll look at the key steps to get started to create,
format, and modify pivot tables in Excel.
A pivot table is an interactive table that summarizes, organizes,
and compares large amounts of data in a worksheet. With it,
you can quickly slice and dice your data.
You can examine the data for similarities, differences, highs, and lows.
The data a pivot table is based on is called the source data and each column in
the data represents a field or a category of information which you assign to
different parts of the pivot table to determine how the data is assigned.
With a pivot table, you simply drop the data into one of four areas.
You don't need to use all of these, but they include row labels, values,
which is something that you can quantify, often it's dollars or units,
but we can also count data that's not in a numeric format.
When we want to work with multiple fields,
we might want to add one of those fields to column labels,
and we can filter the entire pivot table with a report
filter. Before you create a pivot table,
it's important to make sure that your data is set up properly.
If you already have a data source that you are successfully sorting and
filtering, then you're good to go.
But here are some of the main guidelines that you want to look at for data that
you are either putting into a pivot table or sorting and filtering.
Of course you want to organize the data and rows and columns,
make sure that you don't have any blank, rows or columns.
It's okay to have blank cells.
To successfully work with pivot table features,
you should have similar data in each column. And to help Excel,
those unique column headings should be formatted differently than the data.
This helps Excel to automatically recognize your data set rather than having to
highlight what could be hundreds or thousands of rows.
Formatting differences might be something as simple as making it bold or
centered. Essentially what we want to do is create an island of data.
And so if there are title rows or other introductory parts of that worksheet,
make sure that there's at least one blank row to separate that from the data
that you want to analyze with the pivot table.
Here's the sample data that we'll use for a pivot table. To get started,
I just simply need to be in one of the cells of the worksheet. Next,
choose insert. Here we have Recommended PivotTables.
Now this is something that you might want to explore once you've created some of
your own pivot tables.
There might be some helpful suggestions and it can be a way to quickly get
started with a pivot table. However,
let's go ahead and build a pivot table from scratch.
I'll simply choose PivotTable. Now, if your data is formatted properly,
Excel will be able to recognize what your data source is.
And so look for what I call the dancing ants or that marquee that
surrounds the data. In this case,
I'll simply confirm that Excel is highlighting the
correct range of data that you want to analyze.
And we'll put the pivot table in a new worksheet. From here, simply choose OK.
Now we're ready to build the structure of our pivot table.
On the right-hand side, we have our pivot table field list,
and you'll start to see the pivot table will begin to build over in the
left-hand side. To begin, I'll simply choose REGION,
which is one of the key fields.
And already the pivot table is starting to come to life.
We see a summary of each of those regions.
Well what do I want to see with those regions?
What I'd like to summarize, my total sales.
I'll simply click TOTALS. Our pivot table shows up on the left-hand side.
But what has happened to those fields in the bottom here,
we see that our REGION has moved into the Rows quadrant and because
our TOTALS have a numeric value, they automatically moved into Values.
So we already have a pivot table with just a few clicks.
One of the first things I'm often asked about has to do with the
formatting of the values.
The original data source was formatted with an accounting format. However,
that is not showing here.
And that's actually an advantage because now you have the ability to customize
how you want to display these values. They do not have to be the same.
To easily format these values,
it might be tempting just to go to the Home tab,
to use the standard formatting options.
Those won't always stay with the pivot table as you continue making changes.
Here's the quick trick, simply move to one of those cells, right-click.
And here is Number Format. From here,
I'll choose Currency and change to zero decimal places.
Notice I didn't even have to select all of those cells.
So that format now is associated with the values.
But let's look at where we start to get the real power and
value of a pivot table.
We're going to add another field in the fields list. I'll choose department.
Now that automatically drops into the rows.
And we now see a pivot table where we have subtotals for each one of those
regions with more specific information for those departments.
Now here's where we can see the real magic of a pivot table that is turning the
data on its side.
Although this might be exactly how you want to be able to see your data.
We can format it a bit differently. Once again,
I'll move to my pivot table fields and move to where the department is.
Notice as you hover over the field, that you will see the four headed arrows.
In this case, I could, if it made sense, drag this above the region,
but instead I'm going to drag and drop it into columns.
And now our data is presented in a different way.
We're able to compare or summarize this data broken out by the
regions and the departments.
How could you use this with your complex Excel worksheets.
Notice as well when you're working with pivot tables,
that there are two contextual ribbon tabs,
the Analyze tab and the Design tab.
These offer you additional options as you're building your pivot tables.
And it's good to know that you can have more than one pivot table against one
data source.
Each one of these pivot tables is created as a separate worksheet,
which you can easily rename and move within the workbook.
Let's add a little bit more complexity to this pivot table.
And here I'll add the LOCATION field. In this case,
it has dropped in below the region, which is where I want to see it,
but always keep in mind that these fields can be moved around based upon the
hierarchy you have within your data. So within a few minutes,
I've created a pivot table that summarizes my information in a way that
might have taken hours to do if I was trying to sort and filter
the data directly. If you create any pivot tables,
here's the most important thing you need to know. And that is about Refresh.
One of the things that we kind of take for granted in Excel is that if a
value changes,
then that will change the results of any calculations,
any formulas or functions that refer to that value in pivot tables.
It's a little different because refresh does not take place automatically.
And so you could have a great looking pivot table that gives you the results
you're looking for, but they may not be up to date.
So if you leave with nothing else,
definitely know the importance and the necessity of refresh.
Here's the idea here. I have a value for Cleveland gadgets.
What happens if that data changes significantly?
I'm going to move back to the original data.
And here I have my Cleveland gadgets and know that with the pivot table,
it might be summarizing or pulling the data for multiple rows.
I don't make a dramatic change here.
And let's say as well that we also have someone
who has perhaps moved or is now part of that
location. And so they're also contributing to the Cleveland gadgets.
But what happened to our pivot table? Not a thing.
That value has not updated.
So we don't want to be making assumptions or decisions based upon data that is
not current. Here's what you need to know. In the PivotTable Analyze ribbon,
here's an option for Refresh. Now, the keyboard shortcut. ALT+F5.
If you have multiple pivot tables, instead of Refresh,
choose Refresh All.
This should be part of your procedures prior to making decisions or
finalizing this project.
You don't have to know which values have changed,
just that you want to refresh the pivot table based upon those values.
And so I'll refresh all and notice now that this value
for the Cleveland Gadgets has been updated and we no longer have
values for the other location.
So make sure that refresh as part of your process when you are working
with pivot tables.
It's also good to know that if you have a data source that continues to change,
you continue adding data to it, or adding rows to it,
in the analyze ribbon here is Change Data Source.
This is where you can then change the range that Excel is
using for that data source to make sure that your data is up to date.
Here's one of my favorite tricks with pivot tables.
Have you ever had a need to maybe extract just certain rows
from your data source?
We can do that by copying the worksheet and then sorting and
deleting those rows we no longer need.
But it's so easy once you have a pivot table. What if we're asked,
where did the data for Chicago come from? What are those data records?
All we have to do is move to one of those values and double-click.
We now have a brand new worksheet with all of the records that
contributed to the Chicago values. That's all we have to do.
These aren't linked back to the original data source and so they would not
reflect any changes.
But this is another way that a pivot table could save you a huge amount of time
because you're able to extract this data to a separate worksheet that you can
even move to another workbook.
What are some other tips and techniques that will help you to take advantage of
working with pivot tables? One of the things that we see here,
because we have multiple rows in our pivot table, we also now have groups.
Notice the minus next to our regions.
This gives you the option to collapse by simply clicking on the minus.
We're able to collapse that particular section.
If we don't need to see that detail, and we can do that individually,
I'll go back to the plus to expand it. Or if you have that highlighted,
in the Analyze tab,
we have an option on the left-hand side here,
that it may show differently with your resolution. And that is,
I'll simply collapse the field and all of the regions will be collapsed.
This gives us a different way to see the information while maintaining the
structure of the current pivot table.
And we can go back and expand that as well.
Another feature to explore are those options under the Design tab.
For instance, you can choose different pivot table styles,
perhaps something a little bit more dramatic,
or you could go to very basic options as well.
They're also choices related to how you want to see those subtotals,
perhaps at the bottom of the group, rather than the top of the group.
Do you want to see grand totals?
And there are a variety of different options for the report layout.
Let's wrap up by looking briefly at some of your options for sorting and
filtering.
Notice for each of these pivot table fields that there is now a dropdown.
And so for this option, for instance,
for my regions can simply select that and choose for instance,
that I want to see only certain regions.
And so now we've easily been able to filter that information.
The same thing could be true as well for my columns.
Here, I'll clear the filter. We also have some options for sorting.
Currently it's sorted by the regions and then the locations within those
regions. But instead if we wanted to sort by the values,
simply move over to that field, right-click. And here is sort,
and maybe I want to see this from the smallest to the largest.
And so within those groups,
we would see the data then sorted based upon those values,
rather than alphabetical. Let's finish by looking at the report filter.
In this case,
I'm going to move over to my pivot table fields and uncheck the region.
So I still have a pivot table.
It's simply showing the information by the locations and by the departments,
but I also could have simply moved the region. And in this case,
I'll simply move the region to the report filter.
And now we have a filter. That's not part of the pivot table,
but it can be used to filter how we see the pivot table.
As you continue working with pivot tables,
know that filtering options are also available through
slicers and timelines.
You can create custom calculations and even build pivot
charts.
And that's our look at getting started with the power and flexibility of
Excel pivot tables.
Now you've seen how to save time and effort by using the powerful pivot table
features in Excel. How can you use pivot tables in your next Excel project?
For more tips on being productive with Microsoft Excel,
visit TheSoftwarePro.com/Excel. If this online training was helpful,
please like this video and subscribe to this channel. This is Dawn Bjork,
The Software Pro. Thanks for watching.
[Upbeat musci]