While Power Pivot is a part of Excel, you don't need to know much about Excel in order to take advantage
of it.
In fact, everything you need to know can be covered in about 10 minutes as the three things that are
necessary are data tables, pivot tables and to a certain extent, understanding how lookup functions
work.
Let's start with data tables.
You might think that all data in Excel is organized and tables, but data tables are a special construct
that contain data and have some special properties that are important to us.
Let's create one and learn about what that means.
So looking at this data set, you might think that it's a data table, it's got Columb names, rows
of data, and it's definitely organized in a table like structure, but it's not actually a data table
to turn it into a table.
We select all of the data that we have and we can either go to the insert tab and choose table or press
control t on the keyboard, which is my preference.
Upon doing this, a small window will pop up and ask for the data range, which will be populated with
our current selection and whether the columns have headers or not.
If your data doesn't have headers, it's good practice to give them headers prior to doing this.
Once you've done that, you can make sure that the box is checked and press OK.
The default formatting for a table will appear with alternating banded blue rose.
Additionally, the table will function a little bit differently in terms of behavior.
I won't go into all of the details regarding that as it's not important with respect to power.
It what is very, very important is that tables have their own names.
Whenever you have a cell within a table selected the tables dynamic ribbon tab will appear.
Within this ribbon tab is a box to the far left that contains the table name to use power, pivot well
and make it as effective as it can be.
It's imperative to give your tables informative names.
In this case, I'll name this table sales, since that's what it contains.
The real strength in tables is that they can be referenced as a combination of table and column names
rather than strictly by row and column references for this table.
I can get the sum of sales by taking equal sum writing sales bracket sales.
And as I enter the formula, you can even see that Excel gives me autocomplete suggestions based upon
the table.
Due to the structure that we've imposed, I could spend a whole ten minutes or more on data tables and
the benefits that they bring.
But for this course we just need to have our data and tables and have them named in order to take advantage
of them.
Next up, let's discuss pivot tables.
Pivot tables are one of those features in Excel that offers an incredible amount of functionality with
very little understanding, necessary pivot tables.
Take a dataset like our sales table here and crunches, aggregates and groups data quickly and easily.
It's hard to exactly describe what a pivot table does.
So let's go make one and play with it using our sales data.
To create a pivot table, it's as easy as selecting our table, going to insert and choosing pivot table.
This will bring up the create pivot table window, which asks for a table or range.
In this case, we can see our sales table is selected as well as where we want our pivot table to be
replaced.
Also light on an existing worksheet and then I'll specify a location next to our existing data, and
once I've done this, I can press OK to create a blank pivot table for us to play with.
Unlike our data table, our pivot table is empty and requires user intervention in order to become useful
to the far right.
We have a task pane that has appeared that's titled Pivot Table Fields, and I'll phase it in here for
viewing.
As you can see, it has four fields, one for each of our data table columns, it also has four different
categories for placement.
The first section to look at is the value section.
This is where you'll generally place numeric information to calculate totals, averages, maximums,
minimums, really anything that you can think of in terms of an aggregation.
The default will usually be either some or count based upon what you put there.
Clicking on the down arrow and choosing value field settings will bring up another window that will
let you customize that computation.
Here, for example, I'll change my sum to an average to get the average daily sales computation.
These aggregations will be context sensitive to the other specifications in a pivot table.
So I'll switch this back to some to explain what I mean.
I'm going to drag the department field to the rows section.
This will populate all of the departments from our data in our rows, and then the sum of sales by department
will appear to the right of them.
This is what context is for each cell for sales.
The context is the department for which the cell belongs to.
We could make the context more complex by moving our date to our column section for a two dimensional
table.
Now each cell has a specific department and month in which it belongs to, so the sum is calculated
based upon the month and department.
We can complicate this somewhat further by dragging store into the filter section.
Here I can click on the dropdown and specify a specific store or some combination of stores.
Now, each cell on our table has a context that refers to the specific department month and store combination
before it calculates the sum.
As you can see, pivot tables are very handy for quickly creating computations across data sets.
Another important feature we'll use in testing our pivot tables in this course is to drill down command.
To drill down to a pivot tables data is to look at the specific data that makes up each cell and a pivot
table.
This can be done by double clicking on a specific cell in the pivot table.
So if I double click on the Dairy March combination cell, I can see all of the data that makes up that
cell's computation.
This action created a new table with a list of data entries, and we can see that they're all dairy,
they're all Kroger and they're all from the month of March.
That's the combination of context that our self contained for standard pivot tables.
This isn't hugely impressive, but it will be very useful when we're working with power pivot and we
need to understand what data has been pulled.
The last bit to know as some basics about data lookups.
A data lookup basically means how do you find a given piece of data in one table, given some kind of
descriptive information?
For example, let's say I wanted to get value for produce for Tom Thumb from this table.
I could do this with a V lookup that takes Tom Thumb as the lock up value the range B2 through efore
as my lookup table and then retrieve the fourth item from that table with a false parameter to make
sure I get a perfect match.
Understanding of the function itself isn't particularly important for this course, though.
If you work with Excel much, you'll want to learn about this and look up at your earliest convenience.
But the concept is what's important.
The concept here is that you have one list of items that you need to filter through and find a specific
item.
In this case, the list we're searching through is our list of stores.
Then you need to capture a specific piece of information associated with that column.
In this case, we want to produce value.
We look up function, doesn't use field names, but instead use a strict integer relationship values
part of the wonder of power.
A pivot is that we will be defining many relationships such as this, where we need to relate the store
list and sells B to be three and before to a separate store list.
And in this case, the single store item listed in Celsi eight.
At this point you should be able to convert data into a data table, create and manipulate a pivot table
and at least conceptually understand what I mean when I say that we need to look up the produce value
for Tom-Tom from a given target table.
And as for power pivot, that's really all you need to know in order to continue with this course.
So now that we're done reviewing the supporting Excel concepts, let's go dive into power.
Pivot.