Advanced Excel - Creating Pivot Tables in Excel
Summary
TLDRThis tutorial offers a comprehensive guide to mastering pivot tables in Excel, a tool often perceived as complex but simplified in this walkthrough. The presenter uses a synth-pop CD inventory spreadsheet to demonstrate how pivot tables can efficiently summarize and reorganize data. Key features include organizing data into rows and columns, applying filters, and calculating total sales or units sold. The tutorial emphasizes the importance of data organization and formatting before creating a pivot table, and it encourages viewers to explore more advanced pivot table techniques in future videos.
Takeaways
- 😀 Pivot tables in Excel are perceived as complex but can be simplified with guidance.
- 🎵 The tutorial uses a synth-pop CD inventory spreadsheet to demonstrate pivot table functionality.
- 📊 Pivot tables allow for reorganizing and summarizing data in selected columns and rows without altering the original data.
- 🧩 It's crucial to have well-organized data with column headings and no blank rows for effective pivot table creation.
- 🚫 Avoid extra data or notes outside the data set when preparing for a pivot table to prevent inaccuracies.
- 📋 Formatting data as a table in Excel is recommended for easier management and automatic updates in pivot tables.
- 🔍 The pivot table fields panel is key for arranging data into rows, columns, and values, and applying filters.
- 📈 By dragging fields into the rows and values boxes, users can analyze sales data, such as total revenue or units sold.
- 🗓️ The tutorial shows how to filter data by time periods, like quarters, to analyze sales performance over time.
- 🔄 The flexibility of pivot tables is highlighted by demonstrating how to rearrange data and apply different filters.
Q & A
What is the main topic of the tutorial?
-The main topic of the tutorial is learning how to use pivot tables in Excel.
Why does the instructor think pivot tables have a reputation for being hard to use?
-Pivot tables might have a reputation for being hard to use because they can initially seem complicated, but the instructor hopes to show that they are not too difficult after watching the tutorial.
What kind of business scenario is used in the tutorial?
-The tutorial uses a hypothetical scenario of a CD store that sells synth-pop music, tracking inventory and sales data.
What are some of the bands mentioned in the tutorial?
-Some of the bands mentioned include Depeche Mode, Erasure, OMD, and The Killers.
What is the purpose of the spreadsheet created in the tutorial?
-The spreadsheet is created to track important information for the synth-pop CD store, including band details, album names, genre, item numbers, prices, and sales data.
Why is it important to have well-organized data before creating a pivot table?
-Having well-organized data is important because pivot tables require a list format with column headings and no blank rows to function correctly.
What does the instructor recommend doing before creating a pivot table?
-The instructor recommends formatting the data as a table in Excel before creating a pivot table, as it helps maintain consistency and updates when new data is added.
How does the instructor demonstrate the creation of a pivot table?
-The instructor demonstrates the creation of a pivot table by selecting the data, choosing where to place the pivot table, and then using the pivot table fields panel to organize the data into rows, columns, and values.
What is the function of the 'filters' area in a pivot table?
-The 'filters' area in a pivot table allows the user to apply filters to the data, such as showing only specific quarters or genres, to narrow down the data displayed in the report.
How can the pivot table help the instructor analyze the sales data?
-The pivot table can help the instructor analyze sales data by summarizing and reorganizing the data to show total sales, number of units sold, or other metrics for different bands, albums, or quarters.
Outlines
🎵 Introduction to Pivot Tables in Excel
This paragraph introduces the tutorial's focus on learning to use pivot tables in Excel. The speaker acknowledges the common perception that pivot tables are difficult to use but aims to simplify this concept for viewers. A hypothetical scenario is presented where the speaker owns a synth-pop CD store and has created a spreadsheet to track inventory. The spreadsheet includes details such as band names, album titles, genres, item numbers, prices, and sales data. The speaker highlights the challenge of extracting meaningful insights from raw data, such as total sales for a specific quarter or genre, and introduces pivot tables as a solution to these challenges. The paragraph concludes with a brief definition of pivot tables as a tool for reorganizing and summarizing data in a spreadsheet without altering the original data.
📊 Preparing Data for a Pivot Table
The second paragraph emphasizes the importance of organizing data effectively before creating a pivot table. The speaker advises against having blank rows or extraneous data that could interfere with the pivot table's functionality. It's recommended to format the data as a table in Excel, which not only enhances the appearance but also facilitates the inclusion of new data that automatically updates in the pivot table. The speaker demonstrates how to format a dataset as a table and then proceeds to create a pivot table. The process involves selecting the data, choosing where to place the pivot table (either on the existing worksheet or a new one), and acknowledging the pivot table field list that appears, which contains the column headings from the original spreadsheet. The paragraph concludes with a discussion of the four areas in the pivot table field list: filters, columns, rows, and values, which dictate the organization of the pivot table report.
🔍 Creating a Pivot Table Report
In the final paragraph, the speaker demonstrates how to build a pivot table report by dragging and dropping fields into the appropriate areas: rows, columns, and values. The example shows how to find the best-selling bands for the first quarter by arranging bands in rows and sales in values, and then adding quarters to columns to see the sales distribution over time. The speaker also explains how to use the filters area to refine the data shown in the pivot table, such as displaying only the first quarter sales. The paragraph illustrates the flexibility of pivot tables in presenting data in various ways and the ability to include additional fields like album or genre to further customize the report. The tutorial concludes with a teaser for a future video that will cover an alternative, perhaps easier method for creating pivot tables, and an invitation for viewers to engage with the content and explore the music featured in the tutorial.
Mindmap
Keywords
💡Pivot Table
💡Synth-pop
💡Spreadsheet
💡Data Organization
💡Formatting as Table
💡Data Fields
💡Quarter
💡Filters
💡Sales Data
💡Excel Tool
Highlights
Introduction to using pivot tables in Excel, which are often perceived as complex but will be simplified in this tutorial.
Creation of a hypothetical synth-pop CD store spreadsheet to demonstrate the use of pivot tables.
Explanation of the importance of organizing data with clear headings and avoiding blank rows for effective pivot table use.
The recommendation to format data as a table in Excel for easier management and automatic updates in pivot tables.
A step-by-step guide on creating a pivot table, starting with selecting the data source and choosing the report location.
Instructions on how to use the pivot table fields panel to organize data into rows, columns, and values.
Demonstration of how to display total sales or unit sales for each band by dragging the 'sales' or 'copies sold' field into the values area.
Example of filtering data to show only the first quarter's sales for each band using the 'quarter' field in the filters area.
The flexibility of pivot tables to rearrange data, such as moving 'quarter' from columns to rows for a different perspective.
Discussion on the potential complexity of pivot table reports and the ability to include additional fields like 'album' or 'genre'.
Teaser for a future tutorial that will introduce a shortcut method for creating pivot tables.
Encouragement for viewers to like the video, subscribe to the channel, and connect on social media for more technology-related content.
Promotion of synth-pop music and a call to action for viewers to check out the bands mentioned in the tutorial.
Availability of links to the CDs of the featured bands in the video description for those interested in exploring the music.
Transcripts
in this tutorial we're gonna learn how
to use pivot tables in Excel and for
some reason pivot tables have this
reputation as being kind of hard to use
and at first it probably will seem that
way to you but after watching this video
I hope that you'll see them as being not
too complicated to use and for this
tutorial I've created a spreadsheet that
is an inventory of synth-pop CDs let's
say that I own a CD store that sells
exclusively the best kind of music ever
made which is synth-pop I don't have a
store like that maybe I should but I
don't but what I've done here is I've
created this spreadsheet that lists some
of the important information that I want
to track for my hypothetical synth-pop
CD store I have listed here several
bands some of these no doubt you've
heard of like Depeche Mode or perhaps
erasure OMD but there's also some here
that are a little bit lesser-known but I
think are really excellent next we have
albums and these are their most recent
albums from these bands these are albums
that I've recently picked up myself and
listen to and really enjoy next we have
a column for genre it's all really synth
pop but for some reason some of these
get labeled as rock for example The
Killers and they do have a mix of rock
and synth pop and new wave kind of blend
it all together but anyway we have the
genre listed an item number which is
more of an internal number just for my
hypothetical store to use we have the
price of the CD and this incidentally is
the current actual price of the CD on
Amazon next we have the quarter and so
I'm tracking each quarter of the year
and tracking how each of these CDs does
during that quarter next I have the
number of copies sold for each album in
each of these quarters and then I have a
formula here to calculate the total
number of sales in other words the
amount of money brought in and it's a
simple formula number of copies sold
multiplied by the price so this is a
nice useful spreadsheet to help me track
my small business and what are the big
moneymakers for my business the problem
is with all of this data it can be kind
of hard for me to drill down and to
really see certain information like for
example
how did I do in the first quarter all
together with all of these CDs and their
sales how did my business do in the
first quarter well that's a little bit
difficult I would have to maybe
copy-paste each of these first quarter
sales numbers into another spreadsheet
or another part of this spreadsheet and
then I'd have to do a formula to
calculate that number or another example
what if I wanted to know specifically
how well did the darkwave music that I
sold how well did it do or what if I was
selling more than just one CD by Depeche
Mode what if I was selling two different
CDs or three different CDs by them what
if I wanted to calculate the total
number of copies sold of Depeche Mode
CDs regardless of what the album is
could I calculate that on my own
yes I could I could create a report
basically just by highlighting
copy/paste but honestly that could take
quite some time to do and it might be
likely that I would make a mistake and
it's just a lot of work and effort but
fortunately a pivot table can really
help me in this situation just to give
you a quick definition of a pivot table
a pivot table is an excel tool that
allows you to reorganize and summarize
certain data in the spreadsheet and
specifically in selected columns and
rows of data and it not only reorganizes
and summarizes that data but it produces
a report a report that is going to be
helpful to you and one important thing
to recognize about pivot tables is that
they don't really change any of your
data when I create this pivot table in
just a minute it's not going to change
the data in my spreadsheet this is all
going to stay intact nothing's gonna be
changed at all
it just helps me to look at this data in
a new way so let's get started now the
first thing to consider when you're
about to create a pivot table is that
it's very important that your data be
organized well it really does need to be
a list you need to have columns with
headings or titles so that's what I have
Band album genre etc and then a list of
items and as you can see they can be
repeated items that's fine but it needs
to be a vertical list also it's
important that you not have any blank
rows sometimes for whatever reason
people end up with a blank row in their
spreadsheet and that's not good if you
want to use a pivot table so before you
use the pivot table tool make sure that
your data is good that there's no blanks
in the data so I'm going to delete that
row to get it back into a condition that
will work well also you need to be
careful about extra data so for example
in this spreadsheet what if I had over
to the right side just some notes
written here like maybe need to update
maybe that's just a note to myself that
I need to update these numbers or maybe
I have down here the word total and then
I've put in a formula that adds
everything up okay you don't want to
have extra data like that either to the
side or underneath your data you need to
have a nice data set that doesn't have
any extra unnecessary information around
it okay so the next recommendation that
I have before you use the pivot table
tool you don't have to do this but I
highly recommend it and that is you need
to format your data as a table so to do
that all I have to do is click somewhere
inside the data so I'm gonna click here
on the word material and then here on
the Home tab home ribbon in the Styles
group I'm gonna click on format as table
I can pick any of these styles to format
my data as a table I'm gonna go with
this one then it wants me to double
check that I'm getting all of the data
for the table and it looks pretty good
to me but you could change these numbers
if you needed to
yes my table has headers that's these
column titles across the top I'll click
OK and look it formatted my data as a
table I like how that looks so even
though you don't have to do this when
you're using pivot tables it is a good
idea to format your data as a table and
the reason why is because that way now
that it's a table let's say I add
another CD to my inventory okay let's
say Brandon Flowers the desired effect
as I add in this information notice that
it's adding it directly to the table it
recognizes that it's part of this data
set and it formats it along with the
rest of the information and not only
that but when you use the pivot table
the information in the pit
table will be updated when you add
additional in this case CDs to the table
okay so let's create a pivot table for
this table full of amazing CDs to do
this all I have to do is go up to insert
and choose pivot table and right away
Excel wants me to give it some
information about the pivot table and
notice that the first thing that's
asking is if the data is a table or a
range or if I would like to use an
external data source in this case I want
to use a table it is a table and it
guessed that I wanted to use table three
and that is this table Table three if it
guessed wrong you could pick a different
one but in this case it worked well it
guessed the right table most likely
because that's where my mouse was next
I'm supposed to choose where I want the
pivot table report to be placed
somewhere in this existing worksheet if
so I'm gonna have to specify the
location for me I almost always choose
new worksheet that way it just gives me
another sheet and it puts the pivot
table there on the sheet it's just
cleaner that way okay so I'm ready to
create the pivot table I just click OK
and look it created another sheet for me
down here and it gives me a little bit
of instructions to build a report choose
fields from the pivot table field list
and that pivot table field list is over
here at the right you can see that a
panel opened up on the right and this is
the pivot table fields panel or pane and
what we have here is a list of the
column headings or column titles that I
had typed in this original spreadsheet
so band album genre etc band album genre
and then down below I have these four
areas filters columns rows and values
and what this is for is Excel is
basically asking me in this pivot table
report that I'm about to make what do I
want to be arranged in rows what do I
want to be arranged in columns and what
values do I care about in this report
and then finally what filters if any do
I want to apply to this report so let's
say I want to know which bands were my
bestsellers in the first quarter of the
year
well the column for bands is going to be
important so I'm gonna go up here and
click on bands and I'll just drag that
and I'm gonna put that in the Rose box
as soon as I did that look what happened
I got a list of all of the bands now
what data do I care about well I want to
know the total money brought in so I
click on sales and I drop it down in the
values box down here so now I can see
for each band how much money was brought
into my hypothetical small business now
maybe I decide no that's a mistake I
don't necessarily want to know the
amount of money I just want to know how
many units I sold so copies sold would
be the one to drag down there and now
that changes my pivot table it's showing
me different information now honestly
though I think I would rather see the
sales so I'm gonna put that one back but
remember in my example I wasn't
interested in the total sales I was
interested in the total sales for each
band in the first quarter
so quarter is also important to me and
I'm gonna drag quarter this time into
the columns box so now I can see each
band and each quarter how much money was
brought in for selling their CDs now if
I had dragged quarter down two rows
instead of columns what would that have
looked like I'm gonna remove that field
so that you can see I'll drag it down
two rows underneath band and so now the
data is still the same it's just
arranged differently on the screen I
have the first band here black audio and
it's in a row but I also put quarter in
a row so it listed the four quarters
that are associated with black audio
right underneath black audio and so
Excel is really smart about this it
figures out that these quarters in the
original spreadsheet are obviously
referring to quarters for black audio
and so it keeps that data together and
then covenant the sales quarters for
covenant are listed there Depeche Mode
the killers on and on so I just wanted
you to see that that the same data can
be illustrated in different ways based
on the box that you drag the column name
into alright now the last box that we
need to think about is filters so I'm
gonna drag Quarter down to filters as
well now as soon as I do that look what
happened the column title Quarter can't
be in both of these boxes and so it
disappeared out of the
columns box and it moved to the filters
box also look what it did to my data
it's no longer spread out by quarter but
that's okay I've basically applied a
filter here here at the top it says
quarter all but if I click on this
drop-down arrow instead of all I could
try selecting one and now it shows the
first quarter sales for each band it
looks like The Killers was the
best-selling band in first quarter for
my hypothetical company their new album
wonderful--wonderful is pretty wonderful
I think and you should check it out if
you haven't already but anyway I hope
that you can see how useful this is now
of course these pivot table reports can
get pretty complicated if I wanted to I
could include the album down here in the
rows I could include the genre in the
filters so I can filter out let's say
all of the music except for synth pop
okay so that reduced it down
dramatically
I could put price in the columns if I
wanted to and so you can really get some
complicated pivot table reports going
here in a future tutorial I'll show you
another way to create pivot tables and
it's kind of a shortcut a lot of people
find it to be easier but I do think it's
important if you're gonna use pivot
tables to learn how to do it the right
way the old-fashioned way I guess of
manually selecting everything that you
want and organizing your pivot table
report the way you want it to be using
this pivot table fields panel so please
watch for that future video I hope that
you found this video to be helpful if
you did please click the like button
below and consider connecting with me on
my social media websites like Facebook
Twitter and Pinterest and definitely do
subscribe to my youtube channel for more
videos about technology for teachers and
students and watch for another video
from me at least every Monday if you're
interested in learning more about any of
these bands like I said at the beginning
of the video these are the most recent
CDs by each of these bands and I think
it's great music so if you want to check
it out look in the video description
below and you'll find links to each of
these CDs
Weitere ähnliche Videos ansehen
Tabelle Pivot - EXCEL TUTORIAL ITALIANO 50
Interactive Excel Dashboard Tutorial in 3 Steps (+ FREE Template)
How Business Analysts Use Excel - Business Analysis Software Tutorial
Excel GROUPBY & PIVOTBY Functions - All You Need to Know (do they BEAT Pivot Tables? 🤔)
How to Create MIS Report in excel | Impressive and Interactive MIS Report In Excel | Simplilearn
Excel Tutorial for Beginners
5.0 / 5 (0 votes)