10 Excel Pivot Table Tips to Make You an Excel Expert | Excel Tutorials

g'day guys jamika Lucas here from Mike's online and I have a special PivotTable video for you these are my top 10 favourite PivotTable tricks and I want to pass them on to you just so you can't become better at Excel and pivot tables now I've worked with pivot tables for a long time I've got an extreme pivot table course which I spent 12 months recording it and perfecting it and that has over 200 videos and then we'll talk about that at the end of this tutorial but throughout that experience I have picked out my top 10 pivot table tricks they're going to help you become more confident at pivot tables okay so let's get started at the bottom of here I've got our data table and then we've got our trip set from pan all the way down to number one okay now in this video if you want to jump on to a pivot table tip then in the description area you can actually click on the tip number to go straight to that so let's get started number 10 Excel tables now every time you are using a pivot table to analyze data your data has to be in an Excel table now this is tip number 10 but it should be tip number one okay so this is our data set here you've got products customer sales person sales region order date sales quantity financial year sales month and sales quarter will go all the way then we'll scroll down we have five hundred and seventy seven records to put it into a Excel table with a click in our data and press ctrl T T foot table and then asks us here does my table have headers it has so let's keep that ticked and then our range it automatically later and he puts it in there for us Excel is smart enough to do that so press okay now it's put into a table we'll know that because one step out of it and when they will step back in it gives us a table tools option here now one thing is our table name is Table one but we can call it our data so this is an Excel table and when you create a pivot table it reads this data as one whole table as our data so it reads it as one whole table and if you add more information at the bottom of this table all you're going to do is refresh the pivot table and then it automatically gets updated in the pivot table because there's create a pivot table so click anywhere now data and go to insert and pivot table now you see here the table range it's automatically picked it up as our data so that's our structured referencing and it looks at our table as a whole so let's go put it into an existing worksheet and we'll go in here and we just click in there and press ok so that's our pivot table in there now let's put in our customers in here and then it's put in our sales in our values area okay we can just change this to sales and said before when we update our Excel table with new data all going to do is come back here and press refresh and it automatically gets updated so let's put in a new row of data for one two three warehousing so we'll go here and let's go all the way up and one two three warehousing let's put in another row of data now I'll click on row five to highlight the whole row I'm going to press control plus to add another row and press control D D for down which copies the information above so we're going to put in a new set of transactional data here and let's make this a large amount so we get to see what happens so let's put in 1 million make this bigger if we go to our salt table this hasn't been updated but because it's an Excel table all the views refresh if it was an Excel table we'll get it clicking our pivot table go to analyze then go to change datasource and then move it all the way down or just so we can include that in your information but not in an Excel table all you're going to do is just right click and then click a refreshing you see that it's been updated day so always use Excel tables it's tip number 10 but it should be tip number 1 okay I want to copy this pivot table by pressing ctrl + S risk and ctrl copy let's go to our next tip which is refresh ok let's put it in here it'll make this a little bit bigger now this tip is automatically refreshing your pivot table when you open the workbook so say that you're sharing this workbook with a colleague of yours and you're both entering data and then you don't know when someone's adding your data and you need to refresh every time you open this work just so you can have the most updated data what you to do is right click in pivot table go to pivot table options and then under data check this box here refresh data when opening the file now you're going to get this message here which says that whenever you have in this workbook the data will be refreshed in the report press ok when we close this and when you reopen this same workbook then any data that's being updated was going to be refreshed automatically so sometimes you forget that's going to fix this problem now let's go put in some data here you - 1 2 3 warehousing one more time ok we'll put 1 millon let's put in 10 million now we'll go in here it doesn't have that because we right-click and refresh now let's just save this let's not refresh let's save this we'll get out of it and we'll come back in and we'll see what happens the 10 million is going to be added into 1 2 3 warehousing let's close this and let's reopen the workbook you see that that's been updated the 10 million dollars has been added here into 1 2 3 warehousing and not didn't have your refresher so that is an amazing tip and one that you should always have turned on whenever you're working with pivot tables let's go on to tip number 8 count to sum now let's copy this pivot table by going to analyze and select and type here the table control copy in here and then press control V so this is our pivot table here let's just take that out for a second let's put in the sales again and we'll see what happens so where the sales are dropping to the values area we'll get a count of sales now I am sure that you come across this situation many times and I get emailed all the time and people saying how come one up with a sales in the values area I get a count now there are two reasons if your sales are grouped within a pivot table next time you put in the sales in the value 0 you get a count but most of the time what happens is in your data set you have a blank cell in your sales column so you have at least one blank cell Excel thinks that it's text and therefore when you drop the sales in the values area it would give you a count of sales so let's go through our data table now a quick way to have a look whether there's any blank cells is to click on here and the drop down filter and then you go to the blacks so let's go there and escape to our blanks and we have three blanks there as said before Excel thinks that this is not a values column it's a text column so what we're going to do is put in zeros in these cells here and then we can go back to our pivot table here first what we're going to do let's take this out of here and we're going to refresh because I'm at a change so every time you make changes to your data refresh let's refresh okay so that's been updated there now let's put the sales again in our values and this time going to give us a sum of sales so that is a good way to fix this little problem which you will probably have encountered when using pivot tables okay let's copy this pivot table and we'll go on to number formatting number formatting now I always love to have commas in my data because I just for far away I cannot make sense of the numbers maybe it's because I'm getting blind in my old age but I'm just an accountant and I love commas in my data now a good trick and one that a lot of people have asked me is can we fix the format and then when we add new data then the formatting stays it or the answer is yes and I'll show you how to do this so click in your pivot table go to analyze and then select and type your the table then select values and then press control 1 on your keyboard and what this going to do is open up the former cells dialog box and the number and category pick number decimal places 0 a thousand separator and I also like to have a negative read and press okay you see that so now we would add in some other information let's put in our sales and drop it in again you see that it's in a formatted style okay let's go to our next one tip number six let's copy this by pressing ctrl asterisk control copy and ctrl V to put it in here let's take that out okay so we have a sum of sales drill-down now this is great if you want to know what is behind the numbers so we see here the one two three warehousing has nearly 11 million dollars of sales and we'll get all these other data here go all the way down now say we want to know what makes up this ten point nine million dollars of sales we can go back to our data table and then the filter but the easiest way is to double click in your pivot table number so let's double click in here and you see this it's opened up a new sheet and it gives us all the transactions for one two three warehouse you see that so it's all the transactions with all the sales now if we make any changes here and then refresh a pivot table it's not gonna change this is just a snapshot of your data so we can just quickly have a look it's okay this is what it makes supple got a big value there we're happy we can just right click in here and delete data get rid of it or use press ctrl-z a couple times and then delete and it just gets rid of that so a great way to drill down into your data is to double click in to your data values in your pivot table let's copy this pivot table control asterisk to select control copy and let's go to so from A to Z all right now I'm sure that in your data I have you have a look here I have a 1 to 10 columns or 10 fields now I've seen when people have 20 30 even a hundred and it gets confusing because say you want to find sales then okay for me it's up there but imagine it was field number 150 you had to scroll all the way down and find it a good way is to pull the pivot table fields in alphabetical order now to do this you can right click in your pivot table and go to pivot table options and then under display you've got here field list it's selected sorting data source order but let's check sort A to Z now enforce okay but when I do that have a look here what happens on the right hand side and a pivot table fields you see that it's put in alphabetical order how cool is that let's go to clip number four let's select our pivot table and let's just put it in here and control-v format errors okay let's take out the some sales out of there and just click back in here so on to put in here now the quantity in the values area and just do your sum and press the sum now we get an arrow we get a div error there go all the way down you see that we have an error now let's go back to our data set all the way here and you see that in the quantity there's a formula there if we press f2 we can see the formula it gives us an error so therefore in the pivot table one were summing this you'll give us an error so let's go back to our sheet this right click here and then go to format errors and press ok it's a quick to go back so we in here now so sometimes you get errors and it happens with your data set and say you want to clear this error out you can go to your data set and clear it out from there but another way is within your pivot table right click in their pivot table options is your friend now for error values show click that and we can put in here zero or you can put in there and a text the values whatever you want I put in zero and press ok and you see that it got rid of it let's go on to tip number three all right format empty cells you have a look here we have some empty cells that's because if we go to our data then there's no transactions for that particular customer let's go back here right click in there and go to data table and we'll see here we have a couple of blank cells there and that's okay maybe you want to keep them blank because there's no transactions if you put 0 that means at 0 transactions but then if you have negative 1 that means that it could be a negative 1 quantity or a refund so having nothing in there may mean that nothing has happened which which is okay in certain data sets now we can go in our pivot table and go in here and then instead of having a blank cell we can have no transactions or we can put in whatever we want let's right click in here pivot table options and then for empty cells show you put in 0 let's put in there and hey not applicable you see that we have our values in there now let's copy this and go to tip number 2 so control asterisk control copy and let's go one two tip number two and say I want to make this you know nice it big just like that you know okay and then our data gets updated and i refresh right click and refresh it goes back the column weeds changed so you've done all this work you want to make it a little pretty and then he changes its annoying we can get rid of this right click pivot table options once again now this here under format auto fit column widths on updates are not that means on refresh we don't wanna to board a fit I want to keep it like that we want to make this as big as we want they want to make it like that so next time we'll press refresh you see it stays fixed all right our final trick let's copy this pivot table and then let's go in here alright so now let's get rid of this I want to put the sales in our values area and then instead of customer I want to have our sales month in there I want to put our financial year in the columns area and I want to put the customers in our filters and let's put in the products in our filters okay so let's highlight these and double click and it puts it all into perfect order and then make it like this okay and I'm just going to movies all the way down here and what I'm going to do is just add a couple of rows and put this there okay so so we have how customer and products in our filters now we can see that now say that every month or every week you're making this report and then you want to make a report for your customers so you've got your customers all there alright and then say you print off let's say you have one three warehouse okay you see them made their sales for the last three years and then you print that off and you go to ABC telecom bang and you print that off now what you can do is actually take all your customers and expand out the report filter in your worksheets here so each customer is going to have their own worksheet with their name and their pivot table now let's just select everything again to do this first of all you're gonna have your customers into your filters area or the metric that you want to show in each sheet it could be salespeople you could be Regent but let's put in our customers now we're clicking our pivot table let's go to analyze and the far left hand side under options let's click on the drop-down and here under show report filter pages click on that all right now he gives us two options the two fields that are in our filters area one is customer and the ones products now we want to show our customers so we want to show all report filter pages of customers so when I press ok you can see at the bottom here we're going to have all our customers gonna be a lot of sheets so watch I'll just okay bang it's thinking it's putting it all they have a look here all right they're all over here bang bang bang if I right click in here you can see them all our customers how cool is that this click there you can see it's put it in to a format that we had before so that's all there so now you can print that off and give it to your boss ok so let's click on 1 2 3 warehousing I want to make this a little bit better I'm all the other shift key and I wanna scroll across all the way to the right until the last customer click there now you see that it's gone into white background and also in the top P SS group I guess it's all group so if I change something here into the active sheet then you change all the other sheets that are groups so what I'm going to do is just click to select on the top left hand side everything and then just double click to make it bigger and then just double click there okay because I want to make it so you can see the numbers all right now let's ungroup this to ungroup right click in any one of your sheets and press on group sheets so now if we go to all these sheets here you see that they are all perfectly aligned all right so that's a bonus tip for you okay guys I hope you enjoyed these top ten tricks and I'm sure you would have learned a couple of tips that you never knew beforehand you know what start using that start using it with your pivot on reports start showing off to your colleagues teach them and the more people they know how to use pivot tables the better the Excel world is going to be now I'm going to talk about my pivot table course but before I do that I want you to like this video and also comment anything that you want also you can subscribe to this YouTube channel so you can receive more videos just like this down the track there's also going to be more videos on formulas on charts on power bi on lots of cool stuff so subscribe comment and also like you can also share this video with your colleagues and friends the more people that know itself the better and also don't forget to subscribe to my blog the link is up on this video now and also in the the description area and we subscribe you get weekly free excel tips on formulas pivot tables charts analysis and many other Excel features you get for tips on one email each and every Friday and also I put in there some great Excel resources that I know is going to help you become better at Excel and stand out from your peers and the crowd so sign up it's free and you're going to get great value there's over 10,000 professionals that have already signed up so what are you ready for now let's go and talk about my awesome pivot table course which is called the extreme pivot table course and a know the Unilever okay so in the course you've learned a lot of things about pivot tables but there are heaps more heaps more now I've got a course called the extreme pivot tables and I've worked over 12 months to get this course out I've read many books I've searched the web I've looked at all the different scenarios and answers and I've come up with a course where I have over 200 videos 217 to be exact and I go through every single aspect of pivot tables also have a lot of tips and also have a lot of business finance cases and some accounting cases as well so I put everything into practice also have some bonus workbooks and I'll show you just quickly here because this is the course here is an online course and it's also downloadable as well so you can view it online and you can also download the videos and you can view them when you are in transit in a train or you're waiting at an airport or you're flying and you've got some time to watch some videos without being connected and the good thing about is that is that all these videos are about three minutes each or just listen three minutes so the quick they get straight at the point and you also get each workbook that pertains to each tutorial okay so I have an introduction here I have a pivot help you cheat sheet which is awesome you can hang on your wall I have some shortcuts my top 20 my favorite ones and there are in animated gif format so you can play those and just sit back and view the pips and also have all the workbooks okay so they're all downloaded there okay now the first chapter is arranging your data set so it just goes a bit about how you should prepare your data hey you refresh your data and how you customize your pivot tables there's a lot of information there now the second chapter is about value field settings and you know how we talked about the sum count average maximum I'll go through all of those now the third chapter is about value field settings show values as the year-to-date or running total in as well as the difference from the previous month I have 14 different scenarios and I have a couple of accounting and financial case studies there now in grouping were grouped dates we group values we'll also grouped texts and you know so group a different scenarios as well okay and I show them in here all right now sorting you can also sort from different scenarios left to right a to Z to a now filter there's a lot of different filters there's heaps of filters now I go through every filter that is available in Chapter seven my favorite chapter is all about slicers are you customize it how you connect it and I've got some great business case studies here and some interactive fun tutorial for you we also have calculated fields and items now we didn't go through that but it's a little bit specialized but in here I'll show you exactly how you create a calculated field and also a calculated item now pivot charts I'll go through pivot charts and how to format them there's 21 different tutorials just on pivot charts so there's a lot of information man now also go about conditional formatting there's a different different ways to conditional format that's a whole course itself but I show you I go through every single conditional format tool within a pivot table and I'll show you how to do that and it just just brightens up your pivot table and he just looks at it just looks much better and then the result is just much more appealing and then your your management team is definitely gonna love you for it okay now they get PivotTable formula a lot of people don't understand that it's a great formula I explained to you how you should apply it on and when you should apply it and it's a great it's a great tool to use now also you talked about macros in here so you can also do pivot tables with macro second automated with a press of a button you can change your pivot table so there's a few tips set a bit of the data management and how you can reduce the size of your data and also different tips and also I've got in there how you can interact with Microsoft's onedrive ok and finally I have bonus videos just for you guys and it goes through sales forecasting I also make a balance sheet interactive balance sheet reconciling customer payments a break-even model tips heaps of information there you get all the videos there this 217 you get all the workbooks and I just show you now let's just go into one video there let us click that and then it goes into the video then okay you see how quick that is and it comes up okay on the left you have the 14 different chapters and then we're in here now okay so it shows the video just make it bigger so you can see there and I you probably don't know whether you can hear it or not but I you know it's it's nice Volume II you can hear everything you can also download the video just press that and then bang look at that you have it shred away you can download it okay you go watch it whenever you want offline at work in the plane now attachments in there I have big workbooks you click on that and then press open the workbook comes up so you have that okay let me go back in here and so we can exit out of that you can also see how you progressing so the percentage of videos that you've seen and what's left as well you can also discuss so you have any questions you can go and discuss with your fellow students and also with myself so if you have an issue just write something there and I'll respond back to you straight away so this is a complete comprehensive course and my fellow excel vloggers have said that this is currently the best course in the world another person so that this is the most comprehensive course that they've ever come across and n it is I spent a lot of hours a lot of time just to make this course remarkable so once you finish that you just become an Excel expert and you just get to another level and you can get those promotions that you want and once again thank you very much for taking time out to take part in this special pivotal webinar hope that you've learned a lot I think I know that you've learned a lot and you've taken the first step into becoming a excel master okay so good luck and I'll see you inside the course

Channel: MyExcelOnline.com

Views: 91,662

Rating: 4.8684211 out of 5

Keywords: pivot table excel, pivot tables, pivot table, pivottable, what is a pivot table, how to create a pivot table, how to use pivot tables, what is pivot table, how to make a pivot table, pivot table tutorial, how to use pivot table, create pivot table, what are pivot tables, how to do a pivot table, pivot table for dummies, how to make pivot table, creating a pivot table, Learn Pivot Tables in 6 Minutes, Excel Pivot Tables EXPLAINED in 10 Minutes, Microsoft excel (software)

Id: dJtwYziFxLs

Length: 33min 0sec (1980 seconds)

Published: Thu Oct 15 2015