Excel 2016 - Easy data analysis with pivot tables

Time Length Icon 45:00 Video Icon 9 videos
Like Icon
Button

Agile data analysis in Excel 2016, using automatic formatting and pivot tables.

Teaching Format:

  • Remote e-learning training session
  • Interact live with your trainer throughout the e-learning session. Ask questions and discuss points as they arise

Training aims:

  • Understand pivot tables
  • Use a pivot table for basic data analysis

Training content:

  • Source of pivot table data
  • The four areas for grouped data
  • Filter data
  • Edit summary functions
  • Edit headings and display order
  • Format a pivot table
  • Benefit from Slicers
00:00:11
Hello and welcome to this short training session on Office MOOC training.
00:00:15
This session is from the Get Started with Office training catalog
00:00:18
and this particular session is entitled “Excel 2016: Easily Create Data Syntheses”.
00:00:23
My name is Steven Ryan and I will be your trainer for this short session.
00:00:30
In this session we will look at pivot tables in Excel 2016
00:00:34
and the goal of the session is to help you understand how to use Excel pivot tables to analyze and synthesize your data.
00:00:11
Hello and welcome to this short training session on Office MOOC training.
00:00:15
This session is from the Get Started with Office training catalog
00:00:18
and this particular session is entitled “Excel 2016: Easily Create Data Syntheses”.
00:00:23
My name is Steven Ryan and I will be your trainer for this short session.
00:00:30
In this session we will look at pivot tables in Excel 2016
00:00:34
and the goal of the session is to help you understand how to use Excel pivot tables to analyze and synthesize your data.
00:00:43
Let's first briefly discuss what Excel is used for.
00:00:49
Although Excel has been around since 1985,
00:00:51
its key essential use and purpose hasn't changed much over the years.
00:00:54
It is still essentially used for 2 purposes:
00:00:57
for calculations and formulas for things like invoicing and accounting and so on,
00:01:02
and for the analysis of tables, data analysis and synthesis.
00:01:09
This particular training session deals with the second of those Excel uses: data analysis.
00:01:14
By being able to analyze all the data in your worksheet
00:01:16
it can help you to make better business decisions.
00:01:19
Sometimes it's hard to know where to start especially when you've got lots of data.
00:01:22
Excel can help you by recommending and then automatically creating pivot tables,
00:01:26
which are a great way to summarize, analyze, explore and present your data.
00:01:32
There are different kinds of tables in Excel: value tables, calculation tables, invoices...
00:01:37
You also get data tables which are always composed of these elements:
00:01:42
titles, column headers and value lines.
00:00:43
Let's first briefly discuss what Excel is used for.
00:00:49
Although Excel has been around since 1985,
00:00:51
its key essential use and purpose hasn't changed much over the years.
00:00:54
It is still essentially used for 2 purposes:
00:00:57
for calculations and formulas for things like invoicing and accounting and so on,
00:01:02
and for the analysis of tables, data analysis and synthesis.
00:01:09
This particular training session deals with the second of those Excel uses: data analysis.
00:01:14
By being able to analyze all the data in your worksheet
00:01:16
it can help you to make better business decisions.
00:01:19
Sometimes it's hard to know where to start especially when you've got lots of data.
00:01:22
Excel can help you by recommending and then automatically creating pivot tables,
00:01:26
which are a great way to summarize, analyze, explore and present your data.
00:01:32
There are different kinds of tables in Excel: value tables, calculation tables, invoices...
00:01:37
You also get data tables which are always composed of these elements:
00:01:42
titles, column headers and value lines.
00:01:46
You can see a data table on this slide, which is basically a table with columns and values in it.
00:01:56
Let's have a quick demo at some of the quick analysis options in Excel 2016
00:02:02
and also to copy some data from a sheet and paste it into a blank sheet and then format it as a table
00:02:08
to get us started so we can move on to look at the data synthesis in the next section.
00:02:14
Kick off briefly looking at the flash fill option which can help you to fill in tables of data sometimes.
00:01:46
You can see a data table on this slide, which is basically a table with columns and values in it.
00:01:56
Let's have a quick demo at some of the quick analysis options in Excel 2016
00:02:02
and also to copy some data from a sheet and paste it into a blank sheet and then format it as a table
00:02:08
to get us started so we can move on to look at the data synthesis in the next section.
00:02:14
Kick off briefly looking at the flash fill option which can help you to fill in tables of data sometimes.
00:02:21
This one is part of the sort of the take a tour welcome to Excel worksheet
00:02:25
you get as part of the templates in Excel 2016.
00:02:29
We have to do those start to fill out the table in the right, in the instructions,
00:02:34
in the First Name column, we type the first name Nancy,
00:02:38
press Enter,
00:02:39
then starts typing the next name and you'll see
00:02:41
it picks up straight away, press enter and it will flash fill in the rest of those for you,
00:02:44
showing that the value following is the first part of the name in the email address in the first column.
00:02:54
It can be very useful for filling in the data that's got a common sequence or theme to it.
00:02:59
Let's look at the analysis wizard in here as well.
00:02:21
This one is part of the sort of the take a tour welcome to Excel worksheet
00:02:25
you get as part of the templates in Excel 2016.
00:02:29
We have to do those start to fill out the table in the right, in the instructions,
00:02:34
in the First Name column, we type the first name Nancy,
00:02:38
press Enter,
00:02:39
then starts typing the next name and you'll see
00:02:41
it picks up straight away, press enter and it will flash fill in the rest of those for you,
00:02:44
showing that the value following is the first part of the name in the email address in the first column.
00:02:54
It can be very useful for filling in the data that's got a common sequence or theme to it.
00:02:59
Let's look at the analysis wizard in here as well.
00:03:04
This is a very brief demo part of the take a tour that shows you how to use the quick analysis sheet.
00:03:11
All we have to do as stated in the instructions is select the table on the right.
00:03:14
I can click and drag the whole table
00:03:16
and when I release the mouse button a little graphic pops up,
00:03:20
a little option called "Quick Analysis".
00:03:24
If I click on that it gives us options for formatting.
00:03:28
For example, on this one I have how I want that to be viewed.
00:03:31
If I hover over each, you'll see that it updates
00:03:34
to show me what that sort of formatting style looks like.
00:03:37
Here I have concepts that shows up and down in values,
00:03:41
greater than values, text-based things, we can clear formatting, assign colors and data bars to it...
00:03:47
There are also things for charts.
00:03:49
You can click on charts and it will show you the sorts of charts you can create out of that data,
00:03:54
different styles, scattered charts, bar charts and so on.
00:03:57
There's plenty more as well besides.
00:04:00
It gives you some options for different types of totaling, like sum and average
00:04:03
and as you hover over it, it actually updates
00:04:07
or the preview of that in the table without actually changing anything. It's a preview of that information.
00:04:12
You can add sparklines as well
00:04:15
to your data to show you the movements of data.
00:04:19
The most interested one is the Tables option.
00:04:23
Let's click on Tables.
00:04:24
I get options on the table or pivot tables.
00:04:29
It shows us recommended pivot tables available for our data
00:04:34
and how it would be represented.
00:04:35
you get a preview of that as you hover over.
00:04:37
This one is the sum of the Q1 sales for example,
00:04:40
sum of Q2 and the account of the companies listed in that particular table.
00:04:45
You can also click on More
00:04:48
which shows you other recommended tables, which is the same thing we've already seen,
00:04:53
or you can start with a blank pivot table which opens up in a separate sheet.
00:04:59
If I click on that, it opens on a separate sheet
00:05:01
and allows us to create our own pivot table.
00:05:04
I'm going to switch back to that original chart.
00:05:08
Now we're going to copy this data,
00:03:04
This is a very brief demo part of the take a tour that shows you how to use the quick analysis sheet.
00:03:11
All we have to do as stated in the instructions is select the table on the right.
00:03:14
I can click and drag the whole table
00:03:16
and when I release the mouse button a little graphic pops up,
00:03:20
a little option called "Quick Analysis".
00:03:24
If I click on that it gives us options for formatting.
00:03:28
For example, on this one I have how I want that to be viewed.
00:03:31
If I hover over each, you'll see that it updates
00:03:34
to show me what that sort of formatting style looks like.
00:03:37
Here I have concepts that shows up and down in values,
00:03:41
greater than values, text-based things, we can clear formatting, assign colors and data bars to it...
00:03:47
There are also things for charts.
00:03:49
You can click on charts and it will show you the sorts of charts you can create out of that data,
00:03:54
different styles, scattered charts, bar charts and so on.
00:03:57
There's plenty more as well besides.
00:04:00
It gives you some options for different types of totaling, like sum and average
00:04:03
and as you hover over it, it actually updates
00:04:07
or the preview of that in the table without actually changing anything. It's a preview of that information.
00:04:12
You can add sparklines as well
00:04:15
to your data to show you the movements of data.
00:04:19
The most interested one is the Tables option.
00:04:23
Let's click on Tables.
00:04:24
I get options on the table or pivot tables.
00:04:29
It shows us recommended pivot tables available for our data
00:04:34
and how it would be represented.
00:04:35
you get a preview of that as you hover over.
00:04:37
This one is the sum of the Q1 sales for example,
00:04:40
sum of Q2 and the account of the companies listed in that particular table.
00:04:45
You can also click on More
00:04:48
which shows you other recommended tables, which is the same thing we've already seen,
00:04:53
or you can start with a blank pivot table which opens up in a separate sheet.
00:04:59
If I click on that, it opens on a separate sheet
00:05:01
and allows us to create our own pivot table.
00:05:04
I'm going to switch back to that original chart.
00:05:08
Now we're going to copy this data,
00:05:12
let's copy that across and do this in a different way to show how you can do it manually.
00:05:16
We can copy
00:05:18
and create a new worksheet,
00:05:21
select a cell
00:05:21
and choose Paste to paste the values themselves
00:05:25
and not bring across any formats or anything, just the values.
00:05:28
Then we can resize to make it fit.
00:05:31
We can then select part of that data
00:05:38
and we can choose Format as Table.
00:05:40
Then we can choose different formatting options for our data, let's choose that one for example.
00:05:44
Let's say what's the data source.
00:05:47
I select all of it basically. Has it got headers in the table? It has so I select the option and click OK.
00:05:53
That gives some formatting to our table.
00:05:57
You can choose the style options to change the table style to something else.
00:06:07
I'm going to show you how to quickly create pivot tables from that data.
00:06:12
I'll switch back to the slides for a moment.
00:06:16
You'll see in a moment in the demo how to create a pivot table
00:05:12
let's copy that across and do this in a different way to show how you can do it manually.
00:05:16
We can copy
00:05:18
and create a new worksheet,
00:05:21
select a cell
00:05:21
and choose Paste to paste the values themselves
00:05:25
and not bring across any formats or anything, just the values.
00:05:28
Then we can resize to make it fit.
00:05:31
We can then select part of that data
00:05:38
and we can choose Format as Table.
00:05:40
Then we can choose different formatting options for our data, let's choose that one for example.
00:05:44
Let's say what's the data source.
00:05:47
I select all of it basically. Has it got headers in the table? It has so I select the option and click OK.
00:05:53
That gives some formatting to our table.
00:05:57
You can choose the style options to change the table style to something else.
00:06:07
I'm going to show you how to quickly create pivot tables from that data.
00:06:12
I'll switch back to the slides for a moment.
00:06:16
You'll see in a moment in the demo how to create a pivot table
00:06:21
and get different areas in the pivot table
00:06:23
in the main area which is on the right side.
00:06:25
This one is actually in French, but I'll show you the English one in a moment.
00:06:30
There's a section at the top and this is basically fields table title,
00:06:38
the pivot table fields area.
00:06:41
Then there are 4 other areas on that tab
00:06:45
that lets us do things to assemble an analyze data.
00:06:47
That's the filters columns, rows and values areas that we're going to use
00:06:52
in the demo coming up shortly to show how they are used.
00:06:58
To do that we have to select a cell,
00:06:21
and get different areas in the pivot table
00:06:23
in the main area which is on the right side.
00:06:25
This one is actually in French, but I'll show you the English one in a moment.
00:06:30
There's a section at the top and this is basically fields table title,
00:06:38
the pivot table fields area.
00:06:41
Then there are 4 other areas on that tab
00:06:45
that lets us do things to assemble an analyze data.
00:06:47
That's the filters columns, rows and values areas that we're going to use
00:06:52
in the demo coming up shortly to show how they are used.
00:06:58
To do that we have to select a cell,
00:07:00
any cell in that table,
00:07:02
and in the insert tab
00:07:05
we have a pivot tables or recommended pivot tables.
00:07:07
If I select "Recommended Pivot Tables"
00:07:10
it gives us the same screen as earlier with the recommended tables we can pick from the list
00:07:14
or we can click on "Blank Pivot Table".
00:07:16
That is essentially the same as if I click on Pivot Table here.
00:07:20
It then asks us where we want to create it, what's the range,
00:07:22
do we want to use the range that we've selected or use an external data source,
00:07:25
do we want to put the pivot table somewhere on this existing worksheet or a new worksheet.
00:07:30
I'm going to choose the whole range I've selected and place it on a new worksheet.
00:07:34
Click on OK and it will open up a separate sheet.
00:07:38
That is Sheet3.
00:07:38
We can see this area.
00:07:41
Here's where we going to build our pivot table report on the left
00:07:44
and on the right we can see our pivot table fields section at the top
00:07:48
and we can see these other areas below for the assembly filters: columns, rows and values.
00:07:55
You've got options to choose fields to add to target.
00:07:58
We can either drag or select them.
00:08:02
For example, if I select "Industry"
00:08:05
it adds itself to the rows column over there, Rows area.
00:08:09
We can also drag and drop.
00:08:12
I could grab Q1 sales, drop it into values
00:08:16
and you see it starts to build up our table.
00:08:17
I could do the same for Q2 sales.
00:08:24
You could also modify the actual values.
00:08:26
If I select the sum of Q1 sales
00:08:29
and choose "Value Field Settings"
00:08:31
I got options for how I summarize that data,
00:08:33
the type of calculation used to summarize the data.
00:08:36
If I change that to Max for example, it now says "Max of Q1 sales"
00:08:40
and the column header also has been changed to say that as well.
00:08:43
I'll switch that back to the sum value for the moment.
00:08:50
A couple of things to notice here: if I switch back to the original source data sheet
00:08:55
and have a look at "Industry"
00:08:57
we've got several industries which are repeated or not.
00:09:00
We've got tech, travel, utility (that's 3),
00:09:03
beverage (4), medical (5), miscellaneous (6)
00:09:07
and then education (7) and financial (8).
00:09:10
There's 8 different industries listed there.
00:09:12
If I switch back to our sheet,
00:09:14
we see 8 industries.
00:09:18
That's good information, we know that's right.
00:09:24
If you want to add a new row for example,
00:07:00
any cell in that table,
00:07:02
and in the insert tab
00:07:05
we have a pivot tables or recommended pivot tables.
00:07:07
If I select "Recommended Pivot Tables"
00:07:10
it gives us the same screen as earlier with the recommended tables we can pick from the list
00:07:14
or we can click on "Blank Pivot Table".
00:07:16
That is essentially the same as if I click on Pivot Table here.
00:07:20
It then asks us where we want to create it, what's the range,
00:07:22
do we want to use the range that we've selected or use an external data source,
00:07:25
do we want to put the pivot table somewhere on this existing worksheet or a new worksheet.
00:07:30
I'm going to choose the whole range I've selected and place it on a new worksheet.
00:07:34
Click on OK and it will open up a separate sheet.
00:07:38
That is Sheet3.
00:07:38
We can see this area.
00:07:41
Here's where we going to build our pivot table report on the left
00:07:44
and on the right we can see our pivot table fields section at the top
00:07:48
and we can see these other areas below for the assembly filters: columns, rows and values.
00:07:55
You've got options to choose fields to add to target.
00:07:58
We can either drag or select them.
00:08:02
For example, if I select "Industry"
00:08:05
it adds itself to the rows column over there, Rows area.
00:08:09
We can also drag and drop.
00:08:12
I could grab Q1 sales, drop it into values
00:08:16
and you see it starts to build up our table.
00:08:17
I could do the same for Q2 sales.
00:08:24
You could also modify the actual values.
00:08:26
If I select the sum of Q1 sales
00:08:29
and choose "Value Field Settings"
00:08:31
I got options for how I summarize that data,
00:08:33
the type of calculation used to summarize the data.
00:08:36
If I change that to Max for example, it now says "Max of Q1 sales"
00:08:40
and the column header also has been changed to say that as well.
00:08:43
I'll switch that back to the sum value for the moment.
00:08:50
A couple of things to notice here: if I switch back to the original source data sheet
00:08:55
and have a look at "Industry"
00:08:57
we've got several industries which are repeated or not.
00:09:00
We've got tech, travel, utility (that's 3),
00:09:03
beverage (4), medical (5), miscellaneous (6)
00:09:07
and then education (7) and financial (8).
00:09:10
There's 8 different industries listed there.
00:09:12
If I switch back to our sheet,
00:09:14
we see 8 industries.
00:09:18
That's good information, we know that's right.
00:09:24
If you want to add a new row for example,
00:09:26
we're going to add a new company
00:09:31
and perhaps we are going to use a new industry:
00:09:36
Mandarine BS training
00:09:37
and we can set some values in there.
00:09:47
If we switch back to our pivot table
00:09:50
we have to refresh that information
00:09:52
and right click anywhere, click on "Refresh"
00:09:55
and you see that the right labels have been added for training and the sum of the sales for Q1 and Q2.
00:10:04
We could also add a new column of sales values
00:09:26
we're going to add a new company
00:09:31
and perhaps we are going to use a new industry:
00:09:36
Mandarine BS training
00:09:37
and we can set some values in there.
00:09:47
If we switch back to our pivot table
00:09:50
we have to refresh that information
00:09:52
and right click anywhere, click on "Refresh"
00:09:55
and you see that the right labels have been added for training and the sum of the sales for Q1 and Q2.
00:10:04
We could also add a new column of sales values
00:10:08
so I'll switch back to our original data source sheet.
00:10:12
Let's say we have got a Q3 sales
00:10:15
and then populate that with some values.
00:10:27
We'll copy some of these values down
00:10:29
and then put a different value in for the last
00:10:34
value here.
00:10:37
Similarly, if we switch back to our pivot table,
00:10:41
right-click and click on Refresh
00:10:45
and now we've got Q3 sales and I can select that
00:10:49
and it will be added to our values here.
00:10:51
I could have dragged it rather than check the box, that's the same thing.
00:10:56
Now we've got values for the sum of Q3 sales as well.
00:10:59
If we update our data source sheet with different information
00:11:04
and refresh it
00:11:11
you see that those values are updated.
00:10:08
so I'll switch back to our original data source sheet.
00:10:12
Let's say we have got a Q3 sales
00:10:15
and then populate that with some values.
00:10:27
We'll copy some of these values down
00:10:29
and then put a different value in for the last
00:10:34
value here.
00:10:37
Similarly, if we switch back to our pivot table,
00:10:41
right-click and click on Refresh
00:10:45
and now we've got Q3 sales and I can select that
00:10:49
and it will be added to our values here.
00:10:51
I could have dragged it rather than check the box, that's the same thing.
00:10:56
Now we've got values for the sum of Q3 sales as well.
00:10:59
If we update our data source sheet with different information
00:11:04
and refresh it
00:11:11
you see that those values are updated.
00:11:15
Another thing we could also do on is use the company value/field
00:11:20
drag it to the Filters column
00:11:21
and we will get a filter at the top of the screen.
00:11:24
I could then filter different information on different companies.
00:11:28
I want to look at Mandarine only so I select that one to see that information.
00:11:32
You can also check the "Select Multiple Items" checkbox and select multiple values,
00:11:39
click on OK and we'll see multiple values that way.
00:11:41
Then go back to select all,
00:11:46
choose OK and we're back to original values.
00:11:52
This provides a really simple way of representing your data,
00:11:56
presenting your data a different way or looking at it from different perspectives,
00:11:59
different values and assigning different filters, different field settings,
00:12:06
different calculations to base our summary information on as well.
00:12:16
To summarize:
00:11:15
Another thing we could also do on is use the company value/field
00:11:20
drag it to the Filters column
00:11:21
and we will get a filter at the top of the screen.
00:11:24
I could then filter different information on different companies.
00:11:28
I want to look at Mandarine only so I select that one to see that information.
00:11:32
You can also check the "Select Multiple Items" checkbox and select multiple values,
00:11:39
click on OK and we'll see multiple values that way.
00:11:41
Then go back to select all,
00:11:46
choose OK and we're back to original values.
00:11:52
This provides a really simple way of representing your data,
00:11:56
presenting your data a different way or looking at it from different perspectives,
00:11:59
different values and assigning different filters, different field settings,
00:12:06
different calculations to base our summary information on as well.
00:12:16
To summarize:
00:12:18
pivot tables are way of analyzing and providing automatic syntheses of your data,
00:12:24
including if you've got really large amounts of data, it can be very useful and it's automatic.
00:12:29
You can also customize everything:
00:12:31
you can customize the formulas, formatting, order, sorting and so on.
00:12:35
Remember to right click the mouse and choose refresh after you modify your source data.
00:12:42
Data syntheses with Pivot tables in Excel is really easy to use, there's really no need to be an expert in data since you can work and get some real value from it.
00:12:54
That concludes this short session.
00:12:56
Thank you attention and we hope that you can make the best use of this training going forward.
00:12:18
pivot tables are way of analyzing and providing automatic syntheses of your data,
00:12:24
including if you've got really large amounts of data, it can be very useful and it's automatic.
00:12:29
You can also customize everything:
00:12:31
you can customize the formulas, formatting, order, sorting and so on.
00:12:35
Remember to right click the mouse and choose refresh after you modify your source data.
00:12:42
Data syntheses with Pivot tables in Excel is really easy to use, there's really no need to be an expert in data since you can work and get some real value from it.
00:12:54
That concludes this short session.
00:12:56
Thank you attention and we hope that you can make the best use of this training going forward.

No elements match your search in this video....
Do another search or back to content !

 

Please login to the platform to play this video