Introduction to Power Pivot for Excel

hi there welcome to Shin do not worship in this brief video tutorial I'm going to explain to you what power pivot really is and why should you bother about it in my opinion powerpivot is really the most awesome data analysis feature you will ever come across right inside Microsoft Excel in fact let me restate that it is the most awesome data analysis tool that you can find probably anywhere in data analysis applications maybe there are one or two things that are available out there that can trump our pivot but those are probably too costly and too time-consuming to learn where as power pivot is right there bundled in your excel and just waiting to be unleashed so that it can do a lot of powerful powerful data analysis and give you results that you thought would take forever to generate okay I'm not going to pimp this any further because I know you're probably interested to know what this power pivot is all about and jump into it and see for yourself okay so let me go ahead and show that to you in a minute so let's just jump into the excel and so here we are so here we are in excel what I have prepared for you today is a very simple business related data let us imagine we are selling some sort of condiments like chocolates biscuits and jellies drinks and various other assortments to a bunch of people these are what our customers are so we have data for sales each sale is marked by a sale ID a transaction ID the sale happening at a certain point in time who is the customer who bought that particular sale and what is the product that customer has purchased and how many units what is the quantity of the sale so this is pretty much how any business data would look like you in your recording sales in a shop or or or even a big company like Walmart most of the time the sale data looks like this right now when you want to do analysis of this data what is the typical path that we take we would go ahead and fill up the entire customer data here not just the customer ID but the name of the customer the the gender of the customer the location of the customer everything that is there associated with the customer is part he is it is embedded here likewise the product also we would put the name of the product the price of the product in any category of the product everything will go here so that we can do some sort of analysis so the first step somebody would do is fetch the customer name for the customer ID by looking into the customer database so here we have all the customer names ID number one corresponds to Albert so we would put the name here Albert because if I want to find out how much Albert has purchased I would require that name here right so we go ahead and write a very big vlookup or index and match formula to fetch the customer mean likewise we go ahead and write something else to fetch the gender location and what kind of profession Albert is leading the same applies for products product ITP zero to five corresponds to drinks and the product name is product twenty five the size of the product is large price is two point four dollars and units per pack is one ok so we go ahead and get all these details here so that we could do some sort of meaningful analysis when we are looking at this data here for example we have the quantity we don't know what is the sale value sale value is nothing but quantity multiplied by unit price which is part of the product daily so we get the unit price from here and multiply that with the sales value to get the to multiply that with the quantity to get the sales now this kind of analysis is not really time consuming but it is also too slow because every time you need to do some something you have to first bring it back to the transaction or the list of all the things that we have and then do it so if you have for example in this case we have something like let me just go to the last value we have about three thousand seven hundred and thirty nine sales transactions so that means for each customer if I am bringing something like four different values the name gender profession and location we would need four multiplied by 3739 roughly you know something like 144 fourteen thousand four hundred formulas we have to write to get all these values and that's just for customer what about products similarly how to write similar number of vlookup formulas and this can quickly slow down your Excel because you have just too many vlookup formulas or any other formulas going all over so this is the first area where you would really appreciate the power of power pivot now power pivot is not really meant to minimize the vlookup formulas it is just a bonus thing that you are getting the power your tour code is really powerful and it can do a lot now I won't be teasing you too much with to what it can do and you know sink sink prices - its its capabilities instead let me go ahead and show you how easy it is to analyze data when you have information in three different lists like this one of them is sales transactions that there is customers and finally the other one is products now if you have three different data tables like this and you want to do any meaningful analysis you need to somehow connect them so that you could do the analysis the connection was done using vlookup for some of these formulas earlier whereas with power P but there is no need for you to connect like this with formulas you could set up connections between multiple tables and do the analysis before making any further progress let me just remind you that power pivot and all these additional features that we are going to talk about are going to work only in Excel 2010 and ever so Excel 2010 Excel 2013 will have this feature enabled in Excel 2010 you need to install PowerPivot atom by downloading it separately in Excel 2013 powerpivot comes prepackaged with Excel you just have to enable it okay it's much like the solver or any of those kind of add-ins it is there you just have to turn it on in Excel 2010 you have to download and install that addict give it permission so that you can do the analysis well this is not too difficult it is just an additional step you have to do and I'm just going to go ahead and show you how to do the analysis I am assuming that you have taught we would enter installed if not you just keep this information in mind and go ahead and install PowerPivot and come back and play with this okay so I'll go to power P what the first thing that we want to do is we want to take the information that is right in the work our Excel and we want to put it into the power pivot now power P what comes with its own window because this is a very powerful tool for analyzing data all that analysis cannot be done right inside Excel so they have created a small window where the analysis the setup of data goes in and then once the setup is done the results will still go back to your excel okay so it is much like creating the complex pivot table but just that the creation process happens elsewhere so if you click on power pivot window button it'll open up another window that looks much like Excel but this window is blank because we don't have any data here now you could import data from a database like a sequel server or access or analysis services or power pivot or you could import it from a report or data feeds txt file ODBC connections or there is something called is your data market where open data is available that you can subscribe to and download well in our case we don't need any of these we just need data that is inside our excel file so we'll go to excel and we select any one cell inside the products table remember this is a table right so select any one cell inside the product tables go to the power pivot tab and click on create link a table what we want to say is just take Stata put it in PowerPivot and link that with this data the purpose of this is whenever there is a new product you would just add it here and because it is a link at table PowerPivot will also know about it so just do that and all the products will be listed here as well so this particular screen in the power P but we know this is power pivot we know not your original data looks much like Excel just that you cannot do anything for example you cannot edit these cells or a lot of things that you can do in Excel you cannot do here because this is a place where data is kept for analysis not a place where you would edit the data okay and there is a small link symbol a chain symbol displayed here next to the products indicating that this is a related table right got it so the first thing that we have to do is do a link table for the products the next thing that we will do is do the same for customers so select any cell inside the customers table create a link a table and you would have customers finally do the same for sales so select something like a cell inside the sales table and go and create a link at table so all the data that is in our Excel customers products and sales is now part of our pivot as well products customers and sales now we do know that the customer ID column in the sales table customer ID column in the sales table is linked to the customer ID here likewise Product ID column in the product sales table is linked to the Product ID in the products table so how do we communicate this particular information well we just go to diagram view angle view and all the three tables that we have are shown like this for the sake of convenience I am going to drag this down and click on product ID and drag and drop it here like that so we are just connecting product ID from products to customer sales power that would set up a connection and shows these arrows like this the arrow indicates that product ID here will be looked up in the products table because products table is the source so the arrow points to the products table the next step is to do the same for customer so drag and drop it here and let me show up like that in case you are doubtful about this relationship you could right click on the arrow and the line and you can say edit relationship so you can see what is the relationship that Excel has created it says sales product ID will be looked up in the products product any sales product ID will be look it up in the products product ad this is similar to the language that we are used to if I have a product ID in the sales table and if I want to find out the price what would I do I would go and look up in the products table for the product price the same language is used here sales product ID will be looked up in the related lookup table products product ID okay I won't go ahead and talk about this further now that we have set up the relationship let us go to the date of view again and you can see that a relationship is available on this column in that column and Excel has shown power pivot has shown a special symbol indicating that there is some relationship going on there now that's about it all we have done is linked at the tables and establish in the relationship between the tables now let us go ahead and create our very first power pivot and see how easy it is for us to do any sort of analysis so for that in the power pivot window just click on pivot table and click on that and let us insert this pivot table in a new worksheet click ok so we would get this familiar pivot table interface with the pivot table field list there are few things that are very different here I am just going to collapse ribbon so we have more screen space and I can show you I'll drag this further down like that so I can show you what is the new thing sorry for that so you can see that in a regular pivot table we would only see if I create a pivot table from sales we would only see this money fields these are the fields that are available but because we have created a pivot table to power pivot now all the tables that are available are shown as collapse lists and their fields are available what does this really mean it might look somewhat confusing for those of you who have never seen this so I'm just going to show very simple but a very mind-blowing example for example if I am looking at the sales data this is the transactional data right and if I am looking at this and if I want to know what is the quantity of sale that we are selling to male and female groups that is how many products we are selling to guys versus women how would I do that analysis with a regular pivot table that is not even possible because we only have customer ID we don't know what gender the customer ID belongs to unless we do a lookup formula so the first thing that we would do is add helper column here bring the gender and then put it here and then create a pivot on top of it use the gender field to do the summary whereas now all I have to do is take the gender drag and drop it in the row label area so both genders that are available will be listed here and pick the quantity and drag and drop it in the values instantly power pivot will calculate and tell me that for females we are selling 4711 units of product and for male 6000 396 so you could get a sense that our customers about 60% are males and the rest 44 40 or 41 percent are female what if I want to see the break up by gender but also with product categories well it's very simple you just take the category and add it next to the row label like that or inside the columns when you do that you will see the break up by category so now you can see where the how the product sales are distributed by category so you can see that for example something like biscuits it's almost 1 is to 2 ratio whereas something like chocolate it's almost same maybe 6 is 2 nor seven straight kind of ratio so this kind of information is very easy to obtain once you have linked all these tables because all these tables are available and when you link like this it is instantly telling me the quantity now we only know the quantity what if we want to analyze for example the total sale amount and we know the quantity but we don't know the say lemon so let us go ahead and do that okay to calculate the total sale amount corresponding to these quantities the first thing that we need to do is to create an additional column of course there are many ways to do this for example you could create probably a measure to do this but we will do the easiest way so we'll go to the sales table right now we have quantity and we would ideally like to know what is the corresponding price for each of these products so the simplest thing to do is go to the last column do you see this add column this is where you could type any formula most of the formulas that you use in Excel would work here as well and we can find the price using that well if I am doing it in traditional Excel way what we would do is we would write a vlookup product ID in the products table get the corresponding price column and put it here but that is too lengthy and in PowerPivot we have a simple formula that does all this magic automatically this is called as related related what do we want to relate here for this particular product ID we want to find the product price so you don't have to specify any of these things because already have linked products ID to the products table so all you have to tell Excel is I want to the product price for this particular root row so we will just say related products price related product price you see how simple this one is all we are saying is I want the product price for this particular row whatever is the related price for that row you give it to me when you press Enter Excel will automatically fill up this row and all the prices corresponding to each row are indicated here this is called as calculated column one which is a very ugly name so we can just double click on that and we will just say this is elite price right unit price and instantly unit price column is available now if you go back to the pivot and if you just refresh it a unit price field is also available for us so if I drag and drop it here you would see something for the unit price as well by default it is showing you the sum of unit prices so it tells me that the total unit price for this is 653 well this is not actually correct that is because it is just summing up all the unit prices for females in the biskits so female type of customers product categories biskits only summing up it is not multiplying with quantity so if a person a female customer bought three units of chocolates or biscuits that is not counted as three times it is just counted as one time so to fix that we have to go one extra step how that first let me just remove these things all these processes are similar to how you would read it and deal with pivot tables so to calculate the product price multiplied by units what we just need to do is we need to go back to the power pivot window and just add one more column that just multiplies this value with that value very simple right equal to unit price multiplied by quantity when you press ENTER all the values corresponding to each row for the sales table where the quantity multiplied the unit price are listed here and you would get the complete totals for each row sales totals we can again disc all this as a mode right and then let's come back here and refresh the pivot table so we get the amount here and now you can just drag and drop the amount into the pivot table so that it would show up the sale amount broken up by gender and catkin now this just this thing will work for any type of report or just gender and category but you could put for example profession and category profession and size so you can see that wave by various professions and by various unit sizes how much the sales are coming up for example the small small size is giving us the lowest amount whereas large size is giving highest amount of sales and the grand total is everything so any type of things that you can imagine can be done here in fact you could go one step ahead and you can add a slicer on for example category so all the categories will be shown here as a slicer and you could just filter the report instantly on any particular category for example this is the break up of various professions and various sizes in the snacks category how the sales are biscuits chocolates drinks jelly likewise you could add any number of slices both vertically and horizontally and create a very powerful interactive report that can they take data from multiple tables connect all of it nicely inside the excel memory and show you the results you want without taking much time now just let me remind you that what you have seen is merely the surface of what will power pivot can do we have not even used a very powerful feature called as measure measures can be used to create values here that are based on some calculation okay so I have not shown you how to do that because I think that itself is a tress the world and we will save that for our power pivot course but I hope I have raised your curiosity towards power p110 have have been able to demonstrate to you how powerful this particular feature is and how much time and energy it can save you because you don't have to really worry about any of the formulas or any of those complicated things to create a connected report like this that does analysis automatically in prisons results so I hope you have enjoyed this little video and I hope you are geared up for learning more so what next well what next is very interesting too I am planning to launch a power pivot course in the first week of February so that you can take all these concepts and do something really beautiful and powerful like create an dashboard or an automatic very big report and do a lot of analysis that you are not able to do every year ok so that is what our agenda is so if you are interested go ahead and join our power board waiting list the course is almost in the conceptual stage and I'm really find fine-tuning various modules and other things and I'm hoping to launch this course in the first week of February so that you can learn power pivot and do some pretty awesome and kick-ass things using it thanks again for taking time to watch this and learning something I hope you have enjoyed it do let us know your feedback and remember to join our power to at course waiting list so that I can update you as soon as the course is ready I wish you a great day head Oh bye

Channel: Chandoo

Views: 344,976

Rating: 4.8223209 out of 5

Keywords: Microsoft Excel (Software), education, powerpivot, excel, chandoo.org, chandoo, power pivot, pivot tables, excel pivot, power pivot tutorial

Id: dOJvKz8QWk8

Length: 23min 30sec (1410 seconds)

Published: Mon Jan 21 2013