Easy Excel Pivot Tables: Learn With Real Company Examples | Javier Polanco | Skillshare

Easy Excel Pivot Tables: Learn With Real Company Examples

Javier Polanco, Engineer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
10 Lessons (48m)
    • 1. INTRODUCTION

      1:55
    • 2. LESSON 1. Format your data before create a Pivot.

      6:36
    • 3. LESSON 2. Create your first Pivot Table!

      4:22
    • 4. LESSON 3. Create custom groups

      6:14
    • 5. LESSON 4. Use powerful filters!

      4:43
    • 6. LESSON 5. Create custom ranges

      3:34
    • 7. LESSON 6.1. Customize the data

      4:54
    • 8. LESSON 6.2. Customize the data.

      8:32
    • 9. LESSON 7. Final tips and tricks

      6:24
    • 10. Conclusion

      0:35

About This Class

(DOWNLOAD THE COURSE FILES HERE  https://drive.google.com/open?id=0B1wy4jpDNlyWZ0gzd0tkRTF0LXM)

Working with large data can be stressful and time consuming, also sometimes you have to dig down too much just to get simple statistics. This course will make you analyze data a lot more quick and efficient using pivot tables. The content of this course will not only make you faster, will make you improve in the decision making process because you will be able to see statistics that are almost impossible to see just using normal tables.

Analyze any type of data using pivot tables with practical examples

  • Format any type of data to create better and cleaner pivot tables.
  • Practice with 6 real life practical examples.
  • Create pivot tables with ease.
  • Group data fields manually to have a better perspective.
  • See your results in percentages, numbers and a lot more formats.
  • Learn how to create and easily use Pivot Charts.
  • Use powerful filters to dig down your data.
  • Customize you original data to create very powerful Pivot Tables.

Quicker than you ever imagine

Pivot Tables are not difficult nor advanced tools, you cannot believe how easy is to create and manipulate them. If you use excel to process any type of data in you personal life, job or any other project, this course will bring you efficient solutions with 6 real life practical examples. 

Contents and overview

Forget theoretical explanations and slow, long and boring examples and learn in just 1 hour, 10 lessons and 6 practical examples everything you need to know about pivot tables. 

The learning method used in this course brings easy and digestible explanations while you work simultaneously with me in every single lesson.  

When you finish the course you will create pivot tables in less than 5 minutes with clean and good quality data, also you will be able to visualize your data with dynamic pivot charts. 

As a bonus you will learn some tips, tricks and hacks, that made me succeed in a lot of problems and situations in my professional experience and will help you more than you imagine in yours. 

Transcripts

1. INTRODUCTION: thank you so much for joining this course on. Welcome to the Intensive People Table course where you will learn everything in one hour. Guaranteed enemies have your political scene. I'm an industrial engineer. I've been working us a precious, an executive. And as in the inventory manager from some time there, I've been working with a lot of itself. Files a lot of data, very low violence with country money. A statistic sells a lot more. So I focused. Used time analysis with tricks. That's what I'm bringing you here. Real life and tips and tricks. What you need to know. You don't need to be an expert. Obviously. Well, you only need some basic stuff like filters like sorting format change colors, filter columns and stuff like that. You don't need formulas at all. In what you will learn, you will learn to create us people tables with very D's control and manipulate them. See the same data from a different perspective and formatted data before creative people table. So what's the people? Table people tables are summarizing system that takes a lot of fields like names, phones, email, sales, inventory and process it for a better in a lot easier. Analysis people table can take very long data and shrinking can help you with repeated data . Can take a lot of fields and group them for better analysis. It can count. Items also can create very cross and dynamics charts. I don't believe in learning by talking, so let's go. Let's see some people teams. 2. LESSON 1. Format your data before create a Pivot.: wait for creating the people table. We must make sure we have clean and good quality data. Let me show you by turning this into good quality. Step one data must be in tabular format. Do not confuse having columns and rows with having real table for excel. This is not a table is just data put together to convert. The table is very easy. First, we must click inside the data here in home tap styles format as table. All these tables are the same. They fit the same purpose. The only difference is colors and some design elements. I like these ones, were clicking. See that Excel. Recognize my data. To convert it to table is always better to check. If this is correct like this, this is not correct because Excel finds some empty rows and empty column. Excelled stopped selecting my data, so I must selected manually selected manually. Just click here. We can do that by using some short cuts along the way. I'll be using a lot of shortcuts, so be sure to take note each time I use one and pay attention. When I put them on the screen right here, I'll teach you how to use it and when using. The first shortcut is if you press shift and then press any of the arrows left right down, up, you can see that you will select induct direction. But if you press control and shift at the same time, you will select until there's a break. If you're selecting data, you will stop until there's empty data. If you are selecting empty Rose, you will stop until there's something there's some later. So let's select if you press control shift at the same time. Press right, right. Let's select all the columns and then keep pressing down until you reach the very end off your data. So we haven't press enter now, Excel asked me. My table has headers. I can confirm going up. Okay, now we have a real table. Excel understands that all these data is related on the road. I'll show you the real benefits off using tables, so let's move on. Steptoe. Delete or fill all blank cells, columns and rows. As you can see, we have a lot of empty sales empty columns and down there empty rows. We must delete them or fill them. So let's delete this. We can confirm this by pressing control and then pressing down. You can see that we go to the very top off the table, so there's nothing you can check this. There's nothing we can delete it to eat. Another cool way is, if you press control and space bar, you will select the whole column. And if you press control and minor sign, you will delete everything. Maybe you and me know that this info was obtained in January. But Excel does not know that. Be very careful not to sort anything before feeling this cells, because when you do, you cannot keep track when you obtain the data. If you do it like this, I don't know that date for all these names. So if you press control, see, this is undue going back. So let's feel this to fill this press control, See, to copy and go down, press shift and control to select all the empty cells down there, um, press control. But keep present shift and then press up toe de selected total and impress Control V. So I will do that for all the monks. Great. We have all the date field Step three, there can only be one header. You can see that we have a lot of headers, since this was an important data from a software off from somewhere. It has a header after each month, and some empty Rosa's well tables must have only one header before Creative People table and not sub totals. This is easily fixed. Using the filters, we can detect that So clicking column filters filter credit card de select all Onley Select these items. Why? Because credit card is no miracle. This is text is easy to spot, so we only select all control minus sign. Delete step for check for values inconsistencies. We need to make sure that data values are unique and not repeated. What this means. Let's check it. You can see that here in credit card names, we have two American Express. These values are repeated, but our type differently. For Excel, these are very different items. This is easily fix. Just select both, and then copy the one do you believe is the correct one or the one you like pasted in all cells. Let's do the same. Let's see if we found some sun empty here. MasterCard D Select All Master can't so do the same for all the items you find repeated. Great. Let's see another thing. Maybe you have data like this. I call in horizontal because you create your data manually or where you exported it has this weird format. Don't panic. This is Sicily Fix. Just select all control. See to copy and then right click where you want to paste it and select transposed option wala The columns were turned to rose and rose into column. Now here we have are clean data. Let's create our first people table. 3. LESSON 2. Create your first Pivot Table!: great. You made it to the lesson to four million data is one of the hardest things in the people table creation process. Create the actual people is extremely easy. Let's create our first people table with our reason for my data. Since our data is in tabular format, we only need to click Insert people Table Excel automatically selects the whole table, and by the fault, it creates a people in a new sheet. You can specify where you want to pivot what I always like. People tables in a new sheet because I like a clean space to work with him. So click OK, Great. There are tons off options in people tables, but I like practical things, so I won't be explaining Ah, lot off. Almost never use options, and I'll stick with everything You will need people tables, take your original data, process it and then lets you create custom tables, summarizing the data for you to see different angles in statistics. So let's dive in. All your fields are stored here. You can see that obtain name credit card. Xterra. The most efficient way to create a people table is to start with the end in mind. It's really fun to drag fails around and very cool way to learn. But you also can get into rabbit holes worse than YouTube spiral in the last hours just fooling around. That being said, I would like to know how many credit cards have each credit card company, So the first thing to do is drag credit card into the rose area. The credit card companies here. Nice here you have all the credit card companies to know the quantity off credit card. Just drag credit card number into the values area, so excel. Just summarize each each value. You can see that it's say this plus this. Plus this. This is incorrect. I need to count. So I only click here, value field settings and said it to count. Great 59 American Express 38 Bankcard Good. So by the full Excel sums numerical data and count text data. But you can customize this why I used credit card and none names because names can be repeated, but credit cards are unique values. But what if I want to know the different currencies that each credit card company has, and the quantity off credit card in each currency. Sounds hard, but it's very easy. Just drag currency below the credit card and there you have it. You can see that we have 59 American Express. One of them are from this currency. This is one from dollar eight from euro, and I can see that this is very overwhelming. So if you press here in analyzed active field in click collapse, you can see that you will collapse and it's a lot easier to see toe work with. You can see I can expand this and collapse it again. Good. So you can see that the order in here matters. If I placed the currency arm top, I can see that credit card companies are inside currency. This is not what I want because you can see that there are a lot of currencies. I prefer to place the field with less items on top and then the one with more items at the bottom. So this was very easy. Let's move on to some cool examples 4. LESSON 3. Create custom groups: thank you so much for getting this far. Now let's take a look. Some cool example. Now we have all employees off some company with their genders department and employee I. D. I would like to know the number off employees by department and the gender percentage in each one. So let's create the paper. If you click. Insert people table New sheet. Great. The first thing to do is directed apartment Feel in the Rose area because I want the employees by department. Then I dragged the I D toe values area. There you go. This is the number off employees in each department. So far, so good. Now let's add the gender. See that ad in the gender in the Rose area will look very overloaded, and the main purpose off people tables is to simplify data. So if I drag it to column area Oh, I can see that now. This is really cool now, so keep this in mind when you have fields with few data like these male female, it's better to place it on the column section because if you place apartment on the comments section, you can see that it's way too long that's changed this again. I always create people tables that all columns fit in the screen. Take that in my how can I visualize employees percentage by department So very easy. Just Drac again the i D. But this is repeated. But now click on Count two. Show values ass and see all these options. Let's see one by one. If you click percentage off grand Total is showing the percentage off each gender in the whole company. So so 4% off. All employees are female in this department. This is not what I want. So let's move on. If I click percentage of cold in total, it shows the percentage off each gender in each department. So 9% off all women art in accounting. 7% marketing. But this is also not what I want. So if I click in percentage off road total, this is what I want. So in accounting we have 51% females and 48% males. This is what I want. Great. See that we drag the I d. Twice. You can not the same feel as many times as you want to see the same that I differently people tables gave a boring and confusing header. Each time we add something, let's change this displaced click here and let's say quantity. Great. Let's put their percentage Great. This header is sometimes very uncomfortable, so I want to remove it in analyze show field headers. Remove this nice. This is much better. So let's just make these a little bit wider. Great. Now let's do something cool. These are the company departments, but maybe there are also divisions. In that case, you'll need new data, but you can do this manually right here. So let's say that engineering, product management, research and development are the same division. So if you press control and click all of them in the right click group, see how we group them. So let's say this is engineering division. Now let's say accounting and legal. Great. Now you can see that we have the Finance Division cell division in genuine Division H R division. Really cool now, but what if I want to know this sub total off each division? Is he just click right? Click sub total. Great. Now I can see that 1 62 employees are from Finance Division 83 from accounting 79 from Legal. Now this Sinus. Really? I don't know really boring. Let's see some cool stuff. So if you click in design and click here, you can see a lot of the signs. I like the signs with visual aids like this one. So I click it Great. This is a lot better. Did you know it is that every time you change anything to column size readjust, This is very annoying. So there's a solution just right. Click people table options and out ofit. Remove the check. Great. Now, if I do this, it doesn't matter how many times I refresh it will stay the same. So let's move on with another cool example. 5. LESSON 4. Use powerful filters!: thank you so much for getting this far in the course. Now let's see a very cool and powerful way to use filters in people tables. Let's create a people table for these data. See that we have some books in for general titles. Authors release date. I would like to group all the authors by general and see the quantity of books for offer. So let's create the people table. Insert people table. Great. Now let's drag the genera and offer to the Rose Field genera in author collapse. Oh, then let's at item I D. So I can count all the items per genre and per author. Nice. Let's change the colors. Let's use some styles. My favorite. Here you go. Now let's use a powerful feature called Timeline. If you go to analyze here, you can find some cool filters. Let's see Timeline. Excel automatically detects the column with date format in it, that is release date. Click OK. With this, you can filter the books by the published year without adding that feel into the people table itself so you can set the ranges for years. You can use quarters monk days. I prefer years. I can select 2017 and I can drag if I want custom range. But there's another way, using slicers again in analyze. If you click the people table first, analyse insert slicers. This filter is very similar to the timeline, but for any type of values, not just date. If I check title and released, that is date now I have this filter without having to add these values in the pivot. This is really cool and really comfortable, but I can see there is too much data in here. It's way too long. I don't like to use slicers with so much data, so I deleted. But also date is displayed day by day. I don't like this is way too long, but there's a cool way to do this, customizing my data. So if you go back to the data, if you use this formula equal sign in year that will click. This formula will return on Lee the year giving a date entry. If I select the date, click enter. Check that out so I rename it to year. Then delete this Onley. Refresh your paper table to see again the new column. I know I've insert slicer insert year. Now we have a cool way to see this the same information, but displayed differently. Now, if you click here in buttons, you can see that you can at some new columns. So we have too much data. Dad is better. You can just select a year. If you press control, you can select various years. Also, you can change the style of the timeline and the style of the slicer right here, slicer tools or timeline tools. And let's change some colors like this one. And for the timeline. I like this one. Let's see this one pretty cool. You cannot ask me Niece Leiser, as you warned. Now let's see how to use custom ranges. 6. LESSON 5. Create custom ranges: thank you so much again for getting this far in the course. Now let's see another awesome way to visualize data with custom ranges. Now let's create a people for these data. We have students by school and by city also the school type and the great off each student in a given final test. So let's create a people table. Now I will like to see the score percentage by age 50% of age 12 guide on 95% guide 100 so on so forth, drag age to the rules area, then drag this course are the grades. Then add the student I d to count the number of students. Now you see that it's summarized them. So remember the drill. Click here. Value field settings set to count. Great. See that this is a very uncomfortable way toe. Analyze this data. Let's create ranges for the ages so I can have a better idea off the scores by age group. So if you right quick in this column in click group, you can see the grouping options. So So the minimum number is five. The maximum is 19 and let's group them by three. Take that out. So I have a judge groups. I can do the same for this course. So that group meaning maximum and by 10 check that out. So let me expand this. Remember how toe remove the boat? If it so, if I want the percentage off this group age, I click show values as road total off the 100% 3% on Lee Got a 40% got be 34% got see so on , so forth. Now you can analyze these data much, much easier. Now let's add some slicers just to have some custom filters. Let's say school category in city elected group when I have various slicers. So if you click control and select another one and then Click Group is easier, so you move them altogether. So now I can see the results in the Bronx in Brooklyn, Manhattan, Long Island and for category elementary, High school secondary school. Now let's go to the course Challenge 7. LESSON 6.1. Customize the data: welcome. Thanks again for getting this far in the course. Now this is a good one. Here. We will finally see people charts, and we will play around with data to analyze it with more details. Now take a look at this data in rehab zone vehicle parts, the supplier part categories, sub categories. Manufacturer monthly purchase and the costs actually have to costs. But this is a challenge for you. I want you to create the people table by yourself, create a people table that gives me number off parts I purchased from each supplier and separate them by category. Also, I want to slicers. I want a slicer for manufacturer in a slice of for model so positive video and do it by yourself. So I hope you got in right. Let's check that out. Let's create the pivot, Create people table. Let's drag supplier category and item I D. This is what I want. I want the number off items I bought from this guy and divided by categories. Great. Now let's add the slicers, manufacturer and model. Now I hope you did the same. So let's see some new stuff now. Now let's change this format first, let's change the design and let's use a new four months If you click design layout. Report layout. See show in tabular form. This way, data is not only in this column is in tabular form, so you have for each item you have a new column like this. If you if I had something, something else, I always recommend to remove the sub totals. Now I have more details, but let's remove. They've since I don't need it and I like this totals for supplier. Now let's create a pivot chart. People Charts are visual presentation off the data displayed in that moment into people table. You cannot create a pivot chart with headers and data that it's not included in the people table. Like manufacturer, this field is a slicer, its external. It's not included in the people table, so creative people charge. Just click the people table Go to analyze. Here, people chart. Just select the chart that you think it's better for this data. I prefer a column or bars chart. Have several items with quantity as values. Pie charts are not that good when you have too much information in many fields in the rose area. So select column charts click OK, and there you Have it. People. Charts looks exactly the same as normal charts. The cool part is they can be filtered just as the people table. Also, I like to move the people chart to a new sheet, so just control X to cut new shit and control V to paste it. Also, let's bring the slicers so you can see the cool stuff control to see, to copy in Control V paste. So if I click to Filter on Lee Honda Civic, take a look at how many parts these guys ship for me on Lee for Honda Civic. So I remove the filters right here. Take a look at those animations that see parts from Chevrolet really cool. Let's see from Yamaha that also you can change the design. Take a look of those school designs. Some designs don't have the same animation, so you'll lose the animation. Let's see some. Another designs is really cool. I love these one, so let's see some hard stuff now 8. LESSON 6.2. Customize the data.: no using the same data I need to analyze something that is hidden. How can I know with ease what items increased in price and decrease and see by supplier? How was the price variation? That way I can negotiate with them and tell them to lower the prices again or something. Let's try to do that. If I create another people, you can create as many people table as as you want from the same data. Let's drag the categories. Let's drag this suppliers. But if I dragged the costs, I'm kind of stuck right here because if I change it to Count IHS, useless is useless for me. To do this, we have to go to the data in customized data here. We will use some really cool formulas, really advanced formal eso. Make sure to pause the video, take note and check that out. So let's go to the data. The first thing to do is to calculate the price difference. So just open a formula by pressing the equal sign, saying the new price, minus the old price pressed Enter automatically get it. That's changing the name to difference. It's formatted to money Now if the result is positive, the price increased, and if the result is negative, the price decrease to negative. The price decreased positive, increased physical, so the price remains the same. Let's see what this price difference represent each month simply by multiplying the difference by the monthly pushes. This is the quantity I purchased each month. Good. So this price variation represent $88 due to this price increase or price decrease. So it's called a monthly. So with this, I know how much I'm paying more. Or am I pain less each month off each product. Now let's add our custom calling. Let's call it comment. Let's automate this column. I want that this column show me in text which items increased in price and decrease in price. Also, the ones that maintain the prices lets you some F formulas. If formulas test a condition, and if it's true, it will return something. But if it's false, it will return something else equal sign if double click. So if this body is positive, so is greater. This is greatest sign, and this is lower sign. If it's greater than zero, that means the price increase. So if this is true using quote marks, I say in creased. But if it's false, I say the crease click Enter. Take a look now if I change the price, so this decrease. But if I say $1 automatically updates. But there's a catch. The items that maintain the prices difference equal to zero. Say that decrease the prices. Take a look. This is incorrect because I only test two conditions if the price is lower than zero in higher than zero. I did not test if is equal to see where, so I can change that manually. I know this sounds a little complex, but trust me, if you take advantage of these formula, there are endless ways to make your data a lot more efficient in a lot more easier to treat . There are three possible outcomes that the results are less than zero equal to zero in greater than zero. We only test two of them to test the third condition. We will need another if inside, if to do this only click inside the formula and in the false area, let's at another if let's see if double click. So I say if the value is greater than zero increase if the value this guy is lower events zero, let's say decrease. But if any off those conditions are true, maintain at another parenthesis because you have to Formulas quick, enter. See that Really cool contested. Great, Great. Now we have all the conditions covered. Let's go to the pivot in Click Refresh, See that we already have our new columns, so remove these costs. This is unnecessary. And now let's at our custom calling comment here and now if I add the monthly difference, remember that I told you before that I will explain later the benefits off using table format Haven't forgot. This is one of the greatest. If you if you add new data and then just click refresh, you know have to added manually. Now let's change this. The money just right. Click number format. That way you can use any format you like. You have all the former I like accounting for money. So this is good for me. Great. I don't I don't need this is necessary for me, actually, so let's remove them. If I click here, maintain remove. Now I know how much this suppliers increased in price and decreasing price and also the total difference if I want by number off items just at I d to values and C. So this item decreased in price and 62 increased in price. This is too overloaded, so I will remove it. Awesome. This is a very powerful information. I can attack directly to the suppliers with more price variation. But also I would like to see these by sub categories if I clicks if I drag sub categories. Also, you can sort if you click here include sort concert from largest to smallest. Great. So I would like to see the three supplies that increased the most in each sub categories. How can I do this easy if you click one of the suppliers and filter top 10. So I want to see the top three. You can use top five top 10 anything you want to see the top three items you can you stop 3%. I want to see the top three items with more with the top price increase. So take a look at those top three. Let's change this to tabular form that changed the colors. Trust me. With this, you will get a race very easy to visualize. Very valuable info. Let's move on to the final lesson 9. LESSON 7. Final tips and tricks: Yes. Congratulations. You made it to the final lesson off discourse. Now let's see some cool tips and tricks in some final thoughts. Let's create a people table for this data. So what 1/2 years. The I D. The first name last name. Email, gender, city, country, country code, the payment and the payment date. This is like some payments sheets, so it's insert. Okay, now let's move the people table fields list. If you click and drag this menu, it will detach from this place. You can drag it whatever you want, and also you can lock it in one of the sites. If you prefer to use toe work with people tables with the menu and in this side, you can use it. It's placing this way. Sometimes I use people tables not as a analyzer but to compact my data. It can remove duplicates but summarizing them. For example, if you want to remove duplicates their sin option here. So if I click data and click remove duplicates, let's say duplicates I d. It eliminates 68 values, but maybe they're not duplicates. Maybe this guy paid me two times. Frankly, control Z undo. Let's create a table clone so I can steal the information so I can do except blown off that information said it to tablet form. Remove all sub totals. Also, I can no the number off payments. So it's counting each time they pay this guy. Pay me once this guy pay me twice also. Now I know the payment total. It's for my destiny. Home to money. Now, this is not only telling me this guy paid twice but telling me the total amount. So if I used the traditional way to remove duplicates, I will lose some info. So now I can copy this and paste it somewhere. Maybe you want to send the people table info, but you don't want people looking around at the regional data First. If you click, refresh and then you delete the regional data. Now you will have a some be people table. That's how we call it. You can use the people table exactly the same. You can remove fields you can not field because people tables tours a catchy like a copy, so you can believe the original and work with a copy. You can send this via email and people cannot look around at your data. Another thing. If you copy this sheet, move or copy, let's say, create a copy to end toe a new sheet. So now I have two people tables, both people Tables are brothers because they share the same copy. Now I can have two different people tables with different information. Let's say I want to see the city and I want to see the gender and people cannot look around at the original data. Okay, let's see the another file the 7.2. Ah, lot of times your data does not have the date format in the date column. How can you check if you selected called him and go to home Tap number format. If you select long date it, you'll change. But if it's not in date format, how can that affect me a lot? For example, if we create a people table, create a pivot. Okay, let's say I d and let's say payment. If you try to use timeline, we can create a timeline because you don't have any dates for Excel. You don't have dates, so let's fix this. If you go to your data, select a column. Go to data tap in click text two column So you click delimited, uncheck everything. And here you select date format. What is the four month here you have Month, Day in year, month, day in year? If your data has other format, you have all the formats here. So month, day in year, so destination in the F column right here. So if I click finish, take a look. Now this is really a date format for Excel. If I say short date updates also, if I go to my people table refresh, analyze timeline. Now I have a real date injury. Great. Also, I can use date filters. If I say payment date, you can use date filters. Let's see from next month this month. Last month. Next quarter. Next year. This year. Ah, lot off dates, filters. 10. Conclusion: Yes, you made it with your self motivation and perseverance scores. But it's not over yet. Have three magic steps for you to become a master people tables, ending anything in your life. Practice, practice and practice. Follow these steps and see how very hard turns into Thank you for your support. Police rate this course and feel free to send any personal project for help or any consulting. Have a great learning.