Advanced Excel - Creating Pivot Tables in Excel

Summary

This 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

00:00

🎵 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.

05:00

📊 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.

10:00

🔍 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.

Keywords

💡Pivot Table

A pivot table is a powerful data summarization tool in Excel that allows users to reorganize and summarize data in a spreadsheet. It is used to create dynamic reports by placing different data elements in specific areas (rows, columns, values, and filters). In the video, the pivot table is central to transforming a list of synth-pop CDs into an easily analyzable format, helping the user to quickly understand sales data, such as total sales per band or per quarter.

💡Synth-pop

Synth-pop is a genre of music that features the use of synthesizers and is characterized by its electronic sound. The video uses a hypothetical synth-pop CD store as a context for demonstrating the use of pivot tables. Bands like Depeche Mode and Erasure are mentioned, which are well-known within the synth-pop genre, to illustrate how the pivot table can be used to analyze sales data of different music albums.

💡Spreadsheet

A spreadsheet is a digital document used for organizing, analyzing, and storing data in a grid of rows and columns. In the video, the spreadsheet is used to track the inventory of synth-pop CDs, including details like band names, album titles, genre, item numbers, prices, and sales data. The spreadsheet serves as the raw data source for the pivot table, which then organizes this data into meaningful insights.

💡Data Organization

Data organization refers to the process of arranging data in a structured and logical manner. In the video, the importance of having well-organized data before creating a pivot table is emphasized. This includes ensuring there are no blank rows, extra data, or unformatted notes that could interfere with the pivot table's functionality. Proper data organization is crucial for the accurate and effective use of pivot tables.

💡Formatting as Table

In Excel, 'formatting as table' is the process of converting a range of cells into a structured table with predefined styles and features. The video mentions formatting the data as a table before creating a pivot table, which helps in maintaining the structure and making it easier to manage and update the data. When new data is added to a table, it automatically adopts the table's format and is recognized by the pivot table for updates.

💡Data Fields

Data fields in the context of a pivot table refer to the different pieces of information that can be manipulated to create the desired summary report. The video explains how to drag and drop fields like 'Band', 'Album', 'Genre', 'Quarter', 'Price', and 'Sales' into areas such as rows, columns, values, and filters to generate various insights from the data.

💡Quarter

In the video, 'quarter' refers to the division of the year into four three-month periods, which is used to track the sales performance of CDs. The pivot table is used to analyze the sales data on a quarterly basis, allowing the user to see how each band's CDs performed in each quarter. This is an example of how the pivot table can segment data to provide more detailed insights.

💡Filters

Filters in a pivot table are used to narrow down the data displayed in the report based on specific criteria. The video demonstrates how to use the 'Quarter' field as a filter to show only the sales data for a particular quarter. This feature is useful for focusing the analysis on specific time periods or other conditions, such as filtering out all music except for synth-pop.

💡Sales Data

Sales data in the video represents the financial information regarding the number of CDs sold and the revenue generated. The pivot table is used to summarize this sales data, providing a clear view of which bands or albums were the bestsellers. The video shows how to display the total sales amount or the number of copies sold using the pivot table.

💡Excel Tool

Excel is a widely used spreadsheet program that includes various tools for data analysis, one of which is the pivot table. The video script focuses on teaching viewers how to use this Excel tool to analyze sales data from a hypothetical synth-pop CD store. The term 'Excel tool' in this context highlights the broader capabilities of the software beyond just creating and organizing spreadsheets.

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