Let's say, you've got a Microsoft Excel
sheet full of data, rows, and rows perhaps.
You may want to organize it to analyze it and make
better business decisions.
You might want to summarize your data by names, dates,
quantities, or other factors.
Well, a PivotTable can help you do just that.
In this video, I'm going to show you
how to create and customize a PivotTable to help you
get the most out of your data.
Let's get started.
[MUSIC PLAYING]
What is a PivotTable?
A PivotTable is a tool available in Microsoft Excel that
gives you a summary of data organized in a chart.
This can come in handy, especially when
you have a large data set, but only need
to see a small portion of it.
It's called a PivotTable because you can do just that, pivot,
and change how the information in your table is displayed.
So it's easier to digest.
How to create a PivotTable?
Step one, start with the data set.
For my example, I'm going to start with a sample data set
I've named Q4 expenses.
When creating a PivotTable, it's helpful to have
column headers that will become PivotTable fields.
On my sheet, I can see companies I've purchased from,
products I've purchased, cost of those products,
and the month I purchased those products.
Now, if I wanted to quickly see how much money I spent
in December, I couldn't do that with the way
the data is displayed.
I'll need a PivotTable.
Before we move on, be sure to create
or sign into your free Indeed account.
There, you can find career tips, company reviews,
and even job opportunities near you.
Step two, create a PivotTable, I'll go to the Insert tab.
From here, I can either highlight
the data I want to analyze or I can click a cell
within the data set.
Since my data set is small, I'm going to highlight it.
Step three, in Insert, I'm presented
with two options on the left, PivotTable and recommended
PivotTables.
Selecting PivotTable allows you to build and customize a table
with the data that you select.
Selecting recommended PivotTable would allow Excel to auto
populate a PivotTable based on what it predicts you
might be interested in seeing.
For this example, I'm going to click PivotTable
to create a new table.
Once I click on PivotTable, the Create PivotTable pop
up appears.
Step four, select the table or range.
Since I already selected the data set,
I'll verify the range then choose where
I want my PivotTable to go.
I know I have the right data set,
because I can see a green dotted line moving around my selected
area.
Step five, choose the placement of the PivotTable.
Now, I can choose where I want my PivotTable
to appear in my workbook.
I can place it in a new worksheet
or within an existing worksheet.
If I want to put my PivotTable in the same sheet,
I'll copy the sheet information from the table and range
and replace the formula after the exclamation point
with the cell and row I want to start my PivotTable in.
If I wanted the PivotTable to be near the original data set,
I would replace the ending formula
with column F and row 1.
For this example, I'm going to select a new worksheet
and click OK.
Step six, select PivotTable fields.
On the left side of this worksheet,
there's a blank PivotTable space where my data will populate.
On the right side is a PivotTable builder labeled
PivotTable fields that includes the headers of my data set
as well as an area to customize our table with filters,
columns, rows, and values.
One of the best ways to build a PivotTable
is to think about the information you want
quick access to, then visualize how
you want your table to look.
Let's say that based on the data I have,
I want to quickly see how much money was spent each month.
To get started, I'm going to drag and drop purchase month
into the row box.
Next, I'll need to add values.
So I'll drag and drop cost into the values area.
This automatically included the sum of each month as values
into a new column.
If I wanted to break it down even further, say, by product,
I can click on the product field name, which will automatically
add it to the row area.
Now, I can see my purchases broken down by month,
including a total for each month, and by product.
I've just created a basic PivotTable.
For advice on how to list Excel skills on your resume,
check out the video I linked at the top.
Now, that the PivotTable is created,
we can organize the data in a few different ways,
so that we can dig deeper.
To sort information in a PivotTable,
I can do so fairly easily.
There's a small arrow next to the PivotTable header
that allows me to sort and filter.
Once I click the arrow, this menu
will appear showing different options
allowing me to sort data by different fields
and in different ways.
So if I want to see my purchases broken down by month
and I want to see those months listed in order of the least
amount of money spent to the most,
I would select Purchase Month in the select field dropdown menu,
then click on ascending so my data is listed from the lowest
amount spent to the highest, then
finally, click sum of cost in this sort by dropdown menu
here.
Filtering a PivotTable.
I can also filter out any data I don't
want to show in my PivotTable by unchecking it
in this field here.
So let's say only wanted to see the amount of money
I paid each month on bills.
I can click on Product in the select field dropdown menu,
then deselect all of the options except for bills.
Now, my PivotTable only shows the bills
that were paid in these months and how much was paid.
PivotTable calculations.
Now with values, the default is to calculate by sum.
But there are also other ways to display the information.
To see the other options, you can either right
click in the sum of cost field and click on summarize values
by or click on this little eye in the values field.
Summarizations like count, which will
show how many bills were paid.
Average, which shows the average amount
that was paid each month.
Max, which shows the maximum that
was paid towards the bills in that month.
And min, which shows the minimum that
was paid towards the bills in that month.
Once the PivotTable has the data I wanted to show,
I can customize its look.
Under the Design tab, I can change things
like the table color and layout.
And if I want to rename the PivotTable headings,
I can click into the cell just as
if I were editing any other cell and update the title
to another name.
To change the font size or style of any of the text,
I'll click on the Home tab, select
the cells I want to edit, then make
any updates I want that'll help showcase
my data in a better way.
You can see that there are many ways to organize and filter
your data.
PivotTables are not only useful for displaying data,
but they can also help you better understand
what your data is telling you.
If you found this helpful, please
hit the like button down below and subscribe to our channel.
For more career tips, I recommend
checking out this playlist.
Get started with your PivotTable today,
and let us know how it goes in the comments.
Thank you so much for watching.
See you next time.