Advanced Pivot Tables Tutorial in Excel - 2.5 Hour Pivot Table Excel Course

Subscribe and click the bell icon to turn on notifications hello everyone and welcome to this course on advanced pivot tables my name is deborah ashby and it's absolutely lovely to be with you today now i'm a microsoft i.t trainer and i've been doing this for about 25 years now and i'm going to be guiding you through this course on advanced pivot tables Now as i mentioned i've been using microsoft applications including excel for an extremely long time over 20 years now and if i remember correctly i was first asked to put together a pivot table

report by my manager in roughly the early 2000s and between then and now the functionality and the things that you can do with pivot tables has come on leaps and bounds making Pivot tables the single most useful tool available in excel for analyzing data now this is an advanced course so we will be focusing on some of the more advanced features but i will be doing a recap at the beginning for anyone who maybe hasn't used pivot tables for a while and i'm going to be using the office 365 version of excel so that is

the latest version but i will say that if you have 2013 or 2016 then this course is going to be fine for you and even if you have excel 2010 you'll pretty much be able to follow along with most things you just might find there are a couple of functions or tools that aren't available in your version now this course is split down into sections and there are 12 sections for you to work through i'm going to start out right at the Beginning in section 1 by doing a recap of some of the more basic

features of pivot tables we're going to work our way all the way through from cleaning data importing data creating pivot tables and utilizing all of the pivot table options in order to create some really nice analysis of our data and we're going to work all the way through and End up by creating a interactive dashboard which is really going to pull together everything that you've learned throughout this course and really show you what's possible if you want to take your pivot table skills to the next level now within each module of this course there is

a video demonstration which will be roughly 10 to 16 minutes long give or Take a few minutes and in the video demonstrations i'll be using a number of different files if you want to work along with me you'll find these files in the course files folder so please download these to your pc and keep them somewhere safe you can see here the course files on the right hand side they are divided down into sections so if i click on section 4 You can see there are all the course files that i use within section 4

which you can use to work along with me i'll also be setting an exercise at the end of each section so that you can practice the skills that you've learned and those exercise files are available in the exercise files folder which you can see here on the right hand side so again make sure you download these and within each of these folders you'll Find all of the files that you need to complete the exercise so aside from those files all you need to do now is fire up your copy of excel grab a drink and

then spend the next few hours with me exploring the wonderful world of pivot tables and in the first module we'll be running through a recap of some of the more basic pivot table skills so i'm going to head over there now So please join me when you're ready hello everyone and welcome back to this course on advanced pivot tables my name is deb and we are down in section one in module five and in this module we're going to do a quick pivot tables recap now as you're probably aware this course is an advanced pivot

tables course but i didn't really want to just dive straight into those advanced Features without first just recapping the basics of creating a pivot table from scratch from some data that you might have and need to analyze so that's what we're going to cover in this module so if you haven't used pivot tables for a long time so maybe you used to use them fairly frequently but it's been a while then this would be a good little recap for you too or if you'll come to this Course and you want to learn some of the

more advanced functions in pivot tables but you're not overly familiar with how to create them from scratch then this is going to be helpful to you as well and what it's also going to do is it's just going to give us a nice jumping off point in order to practice and get into some of those more advanced skills so let's take a look at creating a Pivot table now what you can see on the screen here is just some basic sales information now i have quite a lot of information in here if if i press

control down arrow that's going to jump me all the way down to the last row of my data so you can see how many rows of data i have i have 21 576 so a fair amount of data that i want to analyze in a pivot table control up arrow is going to jump me Back up to the top of my data now if we just read across the columns we have the month we have the store so this is sales data for two different stores we have the town that that store is located in

we have a store code the country the manager of that store the category and then we have the sales amount and what i want to do is i want to Analyze this data in different ways so it might be that i want to see a total of how many sales each of the managers has accumulated or maybe i want to see how many sales in each category or how many sales by store i could even choose to analyze this by the date so i might want to say how many sales between certain dates and all

of these things or a pivot table is going to be really Helpful to you now i have actually jumped ahead a little bit so this data that i have in this spreadsheet that i'm going to use is already what we would classify as clean now we are going to go into cleaning data when we get a little bit further into this course but suffice to say that cleaning data just means that you're starting off With data that's consistent and that doesn't have any anomalies so we don't have things like blank rows in our data we

don't have blank cells we don't have inconsistent case we don't have duplicates and everything is formatted correctly now the final step i would take here before putting this data into a pivot table is that i would put this data into a Regular table in excel and that's a fairly simple process you can do it a few ways i could choose to do control a to highlight all of my data and then control t which is going to allow me to create a table alternatively what i can do is i can go up to that home

ribbon and i can go across to the styles group the format as table button and i can Choose any one of these different table formats it's going to ask me where is my data and because i was clicked within my data it selected everything around my selection i'm going to say yes my table does have headers and i'm going to click on ok and now my data is essentially in a table now how can you tell the difference between data that's in a table and data there isn't because it Doesn't look a great deal different

to what it was prior to putting in the table well there's a couple of things the first thing is if you're clicked within your data if your data is in a table you're going to see the table design contextual ribbon and that is a ribbon in excel that only appears when it's needed so whilst i'm clicked in my data i can See i have a table design ribbon and i have all of my options on here for formatting my table if i was to click my mouse outside of that table you'll see that that ribbon

disappears and that's why it's called contextual it's only there when we need access to it so if you're clicking your data and you don't see that table design ribbon then it's more than likely that your data hasn't been put inside a table Another thing that will happen when you create a table from data is that you will get these handy filter buttons listed at the top of each column so if i just wanted to filter by a specific town i could deselect everything and maybe i'm only interested in the data for aberdeen and barnsley i

can select those two click on ok and it's going to filter my data for me And i now only have data for those two particular towns now i'm going to clear that filter like so to put everything back again so those are the kind of two ways that you would know that your data has been put inside a table now why is this so important when you're creating pivot tables well what i'm going to do here is from this table design ribbon is i'm going to Name my table and you'll see the first group there

it's called properties it says table name and by default excel is given my table the name of table one so i'm going to call this sales data and really important when you are naming tables or even if you're naming ranges of cells you can't have any spaces in between the two words so i couldn't Have sales space data it needs to be one word or separated with an underscore super important point here remember to hit enter once you've named your table so that that table name sets so now when i create my pivot table everything's

going to be a lot easier for me to read it's going to be a lot easier for me to understand and if we're now pulling anything from that table we can utilize the table name As opposed to the cell references of the table so now i've done that final step i'm going to summarize my data or analyze my data using a pivot table again a few different ways that you can do this the one that's probably easiest for me at the moment based on the ribbon that i'm currently clicked on which is the table design

ribbon is to utilize the summarize with pivot tables option just here Alternatively if you jump to the insert ribbon you do have a pivot tables button it's the first one in that tables group clicking either of those is going to take you to exactly the same dialog box so now i get this box it says choose the data that you want to analyze and you can see that excel has intuitively picked up that i probably want to use the sales data range and you can see those Marching ants around the outside so that's showing me

that it's picking up all of my data if i had my data saved off somewhere else so maybe in another excel spreadsheet or maybe i wanted to import it from another system or application i could also choose to use an external data source the second thing i need to tell excel is where i want this pivot table report to be placed do i want it on a brand new Worksheet or an existing worksheet in my workbook now one little tip here i would always suggest that you do keep your raw data separate from your pivot

tables so in this instance i always like to choose new worksheet and i'm going to click on ok and what you'll see now if you look down the bottom of the screen i have a brand new sheet called sheet2 i'm going to right click and i'm going To rename that to pivot table and now what you see is that on the left hand side of the screen i have my empty pivot table reports and on the right hand side of the screen i have this pivot table fields pane now for some reason you don't see

this pane it might be that you're not actually clicked in the pivot table report on the left hand side For example if i click in another cell you can see that that pane disappears so if you can't see that make sure you are clicked in that pivot table report if you still can't see it it might be that you've accidentally closed it down and all you need to do to get that back is go up to your pivot table analyze ribbon in the show group on the end you have a field list button Click that

and that little pane is going to pop back out again now what this pivot table pane shows you are all of the column headings from your raw data so you can see there i have month store town store code country manager category and sales and then underneath that i have these four different areas filters columns rows and values and the simple fact of dragging any of these fields Into any of these four areas that's what's going to build my pivot table now this sometimes is a little bit of trial and error it really depends the

kind of information you're trying to extract so let's do a very basic example maybe i want to see all of these sales broken down by manager what i would do is i would grab my sales field i'm going to drag that down Into the values area i'm then going to grab my manager field and i'm going to drag that down into rows and what you'll see on the spreadsheet is that as i drop that i now have all of the managers listed in the rows and i can see the total sales for each of those

managers and just to show how you can manipulate your table and get it to display how you Want if i was to move the manager field up into the columns area you're going to see it display in a slightly different way so we now have the running across in the columns and we have the sales underneath now that's not a particularly nice way of displaying this data so i'm going to move manager back to rows now what if i Wanted to do a slightly different analysis maybe i wanted to analyze the sum of all the

sales by category again i can get rid of this manager field simply by clicking and dragging it outside of the pivot table i can grab the category field and drag that down and drop it into rows and now i have the sum of sales performed by Category if i wanted to do something a little bit different maybe if i move category up into filters you'll see now at the top i get this little drop down which is showing me all of the categories and currently i still just have the sum of sales so that's not

particularly meaningful although i could if i wanted to see all of the sales for the accessories category Click on ok like so but if i wanted to break that down even further i could maybe choose to grab the town field drag that down into rows so now i'm seeing a list of all of the towns the total sales and i can filter by specific categories so i could say all to see everything alternatively if i was just interested in maybe games And if i want to select multiple i have a little checkbox at the bottom

i'm interested in games laptops and memory cards just the sales for those three categories click on ok and my data is going to update so that is what that little filter area is up there i'm going to go in and i'm going to say all and click on ok i could drag the Category field into columns and get my data to display a completely different way so now i have my towns listed in the rows my categories in the columns and my sum of sales in the middle and that is a very basic pivot table

how you create one and how you can utilize those fields move them around pivot the data and really get out of the data the analysis that you require Now of course there is so much more to this which we're going to go into throughout this course but i just wanted to get you started off with that basic level understanding if it's been a while since you've put together a pivot table that's it for this module i will see you in the next one hello everyone and welcome back to this course in advanced pivot tables we're

down into section two and in this Section we're going to be discussing importing data into excel in order to put it in a pivot table and in this first module we're going to talk specifically about importing data from a text file now i have a text file just saved to my desktop you can see it here it's this one sales data.txt if i double click to open this particular file because it's a txt file that's going to Open in the application notepad now everything looks a little bit chaotic and disorganized in here but what you

can just about make out is that we have various different columns worth of data so i have a country column product units a sold price manufacturing price so on and so forth you can clearly see those headings running across the top of this particular text file And then underneath we have all of the data which relates to those column headings now the thing you'll probably notice here is that it's not particularly in line it's not in nice neat columns like you would find in a table and that's absolutely no problem when you're importing this data

into excel one important thing to note in here if you do have a text file that You've created is how these columns are separated some my columns in this particular file are separated with a tab now it might be that the text file that you create the columns are separated by a comma or maybe a semicolon or even another special character it doesn't necessarily have to be a tap it's just important to keep that in the back of your mind when you're importing a file like this Into excel now what we're going to do with

this file is we're going to import all of this data into excel we're going to clean it up a little bit and get it looking nice and organized in columns before we analyze it with a pivot table this will also give me a really good opportunity to show you where all of the import utilities are kept within excel so i'm going to close this notepad file Down just for one moment and i'm going to jump into excel so i've just created a new blank workbook and what i'm going to do is i'm going to jump

straight up to the data ribbon because this is where you're going to find all of the tools and utilities available when it comes to importing data into your worksheet and it's this first group here the get And transform data now i'm using office 365 i have the latest version of excel if you're using a slightly older version then this section has gone through quite a few changes over the different versions of excel so you might find that it's not called exactly getting transform data depending on the version that you're using now in this group we

have lots and lots of different options this first one Here the get data drop down this is where we can choose to import data from a file so if you have your data stored off in another excel workbook you could import it from there we can import from a text csv file which is what we're going to do in a moment from xml and we have some other file types in here as well we can also import from a database so if you have maybe some data stored in an Access database you can also import

that into excel and we're going to be looking at that in the next module you can import data from azure and also from many other different sources so for example you can import from a table or a range from the web from a query or you could set up an odbc link to an external Database so if you use some kind of system maybe an hr system or a financial system you can create a connection between excel and that system and import data that way now that's out of the scope of this particular course we're

going to focus on two of the most common ways of importing data and that is from a text file and also from an access database So aside from that drop down we then have some other little options now these are basically repeats of what you see underneath this get data so really the most popular ones have been listed here to make them easy for you to access so we're going to import from a text file and i'm going to select from text slash csv so let's open up file explorer i have the import data dialog

box just here And it's asking me to browse for the file that i want to import now i have mine saved off onto my desktop so this is going to be pretty easy for me to find and there it is sales data dot txt select it and click on import now what excel is doing is it's establishing a connection and you can see now i have this get and transform window open up now what you can see here Is there's some information on the top here about the file origin but it's also picked up the

delimiter so how are my columns separated and if you remember i said at the beginning when we were looking at the notepad file that my columns were separated by a tab and that's exactly what excel has picked up if we click the drop down there you'll see i have a whole host of different Options so if for some reason it picks up the wrong one you can just jump into here and you can very easily change that now it says data type detection and it says based on the first 200 rows so what the get

and transform utility is trying to do here is detect what type of data i have in each of these columns based on the first 200 rows of my data and what i can also see is that it's Divided it down very nicely so because the delimiter is a tab every time there's a tab it separated the next item into a different column and if i scroll across and just do a quick visual check to make sure everything looks okay i can see that that actually looks pretty good i can then choose what i want to

do so i can choose to load the data which Is an option i have at the bottom or i can choose to transform the data and this is where we're moving into more of the power query options in excel and again i'm not going to spend too much time on this but i will touch on it now because this is quite helpful if we click on transform data it's going to open up a power query editor window and it's importing my data into this window and i now have the opportunity to Do some cleaning up

of this data now again if you remembered in one of the previous modules i told you that it's really important to have your data clean and consistent before you start trying to analyze it using a pivot table and there's lots of ways that you can do that in excel but if you're importing data in this little utility kind of allows You to clean that data as part of the import process now i'm not going to run over too many things here but one thing i will take a look at is i want to make sure

that excel has determined the format of each column correctly and i can see that at the top there in the country column it has a b c if i click that this is the format of the data within that column now that happens to be text in this case and i Can see that it's picked up the correct formatting of text so i'm going to go through all of my columns just making sure that it has the correct formatting applied so product again is text that looks fine units sold now when i click on units

sold the thing i want here is i actually want this to be currency i'm going to say replace current and that's going to change that to a Currency format i can see that the next three columns manufacturing price sale price and gross sales those are all correct they have the currency format now discounts i'm going to leave as text for the moment because we don't have anything in there i'm going to change sales to currency this one to currency and also profit to currency as well now we get to the date column let's Click and

i'm going to change this to date format this one is fine as it is text and year is also fine because that is text as well so i can very quickly go through and make those formatting changes and there's a whole host of things that you can do in here you can remove any columns that you don't want to analyze you can do all kinds of things now i'm Not going to go too much into this past that basic level formatting but once you're happy with this you can then select the close and load button

and what that's going to do is it's going to import all of that data into your excel worksheet and what it's helpfully done is that it's also automatically put this data in a table for me and it's given it the name sales Underscore data and what i can now do is i can just go through and i can check to make sure everything looks pretty good which it does i have a column on the end here which i don't actually need so i'm going to right click and i'm going to say delete but now my

data is pretty much in a good format for me to then go in and summarize it using a pivot table so hopefully that's showing you how Straightforward it is to take a text file import it into excel do some basic cleaning up using power query and import it into your worksheet ready for analysis that's it for this module in the next module i'm going to show you another way of importing data and that is from an access database so please join me for that hello everyone and welcome back to this Course on advanced pivot tables

we're down in section 2 where we're talking about importing data into excel in order to use that to create a pivot table and in the previous module we saw how we can take information that's stored in a text file and we can import that into excel and then we can go on and create a pivot table from that data And this module i just want to extend that idea and just show you one more example of how you can import data from an access database into excel and then create a pivot table now if you've

never used access before don't worry too much all access is is basically a database application and it can get quite complex you can have all kinds of different tables and databases linking together but fundamentally in this example all You really need to see is how you can import data that you do have stored in access into excel and then create a pivot table from that so let's get started so you can see here on my desktop i have the access file that i'm going to use it's this one just here and i'm basically using a

file from the northwind traders database and northwind traders is actually a database provided by Microsoft it's a free database that you can utilize to practice your access skills it just gives you a nice starting point so you don't have to create the data yourself so i'm going to jump to excel and again i just have a blank workbook open and we're going straight up to that same tab that we were in last time the data tab and again we want to select one of The options from the get and transform data group now when it

comes to importing information from access you're going to need to click on the get data drop down and because access is a database you'll find the option that you need underneath the from database menu item and one of the options that we have in there is from a microsoft access database once again it's going to open up file Explorer and i need to navigate to the folder where i have the access file stored that i want to use now i've placed mine on my desktop for ease of access but for you guys just navigate to

whichever folder you have it stored in select the database and click on the import button at the bottom now again excel is going to establish a connection with the access database it's going to Analyze what you have in that file and then it's going to pull back the results now this does look a little bit complicated i have lots of things listed here and that's because the access database i've selected contains a lot of different tables all holding different information now whilst this isn't an access training course it might be that the access database that

you have is a Lot simpler than the ones i have here maybe you only have one table in there that holds some data some sales data some financial data and you want to import that in now what i'm going to do is i'm going to choose one of these tables which i want to put in a pivot table and the one that i'm going to choose is this one here order summary and you can see in the right hand side Of that screen it's just showing me all of the data that i have in that

particular table and i can see that this data is going to be appropriate for a pivot table and if we look across the columns you can see i have lots of different information order id employee id customer id order date so on and so forth i have some shipping dates subtotals fees i have an order total column I have who that product's going to be shipped to i have their address so on and so forth now i don't particularly want all of these columns in my pivot table so this is where transforming the data is

going to be particularly useful because i can get rid of those columns that i'm not going to need so i'm going to click on the transform data button at the bottom which puts me back in my power query Editor and if you remember from the previous module this is where we came before where we came in and we changed those data types to make sure that they were all correct before we imported the data into excel now in this case i'm going to do something a little bit different i'm just going to go through and

remove any columns that are not appropriate or that i don't really need In my pivot table so i'm going to select the order id column just by clicking on the header and then up on the ribbon i have a remove columns option i'm also going to get rid of employee id and customer id as these aren't showing me a great deal of information that's going to be useful in my pivot table now to select two columns just hold down the control key and then you can go in And select remove columns i'm going to keep

order date and ship date i'm going to keep my totals my shipping fee taxes i don't think has anything in it it doesn't so i'm going to remove that column as well and i'm also not really interested in having the shipping address analyzed in my pivot table so i'm going to remove that column but everything else looks pretty good so I'm going to leave that as it is so now that i've tied up my columns i've refined the information that's going to go into the pivot table again i can click on the close and load

button but this time what i'm going to do is i'm going to directly load it into a pivot table now in the previous module we just did the close and load option which basically put all of that Information from that text file into my excel spreadsheet into a table and i could then create a pivot table from that data we're going to do it a slightly different way we're going to create a pivot table straight away so for that i need to go to close and load 2. what you'll see is i now get this

little import data dialog box and it's asking Me what i want to do next so i want to create a pivot table report i want it on a new worksheet i'm going to click ok so now it's creating that connection it's importing those fields i'm going to close down queries and connections because i don't really need that but what you'll see is that i now have my blank pivot table report on the right hand side And i have my pivot table fields on the left hand side ready to create my pivot table and again these

pivot table fields are those column headings and they are my edited column headings so all of those columns that i removed are not showing in this pane and now i can just go ahead and start creating a pivot table as normal so i'm going to grab the order Total field i'm going to drag that down into values i'm going to drag the ship name field and i'm going to drag that down into rows and you can now see my pivot table is starting to build up so i have my ship name so who it's going

to and i have the total of all of their orders i could then choose to add another field and i'm going to take this date field i'm going to drag that down into filters So i'm now easily able to filter by a particular date and see those orders so very simple to take data that you have stored off in an access database use the get and transform to import it into excel you can edit what you're seeing remove columns in the power query editor and then you can build your pivot table as normal so Hopefully

seeing those two examples from this data ribbon gives you an idea of how simple it is to import data from outside of excel in the next module i'm going to set you a very quick exercise to practice the skills that you've learned and then we're going to move on into how you prepare your data for analysis so please join me for that for the next section You'll want to download the course exercise files click the link below in the video description to get these you can also scroll through the details to find timestamps for each

section in this course if you're enjoying this training please leave us a comment hello everyone and welcome back to this course on advanced pivot tables we've made it down to our first exercise and what i want you to do in this Exercise is basically just to practice some of the skills that you've learned in this section so everything related to importing data into excel prior to putting it into a pivot table now you'll find the files that you're going to use in this exercise located in the exercise files folder so make sure that you have

those downloaded off onto your pc so that you Can easily access them and the files we're going to use in this exercise are the files in the folder exercise one and you can see that i have three files sitting in there now the first part of this exercise is i want you to practice importing a text file into excel and just using some of those options within power query in order to transform your data Prior to importing it so let's take a quick look at the text file that we're going to use and it's this

one here import text one dot txt so as you can see this is a fairly straightforward little file we have our headings running across the top we have employee names hire date salary department and job title and this information needs to be imported into excel so that We can create a pivot table now the important things that you should be noting here are how those columns are separated so in this case it is a semi colon and before you do this there's just a couple of things i want to point out to you when you're

transforming this data so i've basically jumped forward a step i've imported into the power query editor and a couple of little points that you should be Aware of if when you import this text file in you find that these headings employee name hire date salary department and job title are not located in the heading so maybe you have these in this first row and you just have listed in this top row column one column two column three so on and so forth if you find that does happen to you when You import this data in

a really good little trick is to click this little excel spreadsheet icon and select the use first row as headers in order to get those headings to appear in that top row now what i want you to do once you've imported this data into the power query editor is i want you to go through and make sure that all of the data types are set correctly Once you've done that i want you to close and load it into a table in your worksheet so i don't want you to create a pivot table at this stage

just load it into the worksheet as a table the second part of this exercise is that i want you to practice importing data from an access database into excel and then creating a pivot table and for this part of the exercise i'd Like you to use the file datasource.accdb which again you'll find in the exercise files folder now what i'd like you to do is to transform the orders table within that database and you can see i have the orders table open here i want you to go through and i'd like you to remove the

first column called order id i want you to double check that all of The data types are correct for each column and then i'd like you to close and load this to a pivot table report and what i'd then like you to do is just to rearrange the pivot table so that it shows the sum of the total by department and i'd like you to add the date as a filter and finally for this exercise and this part is optional if you want a Little bit more practice you'll find that you have another file in

the exercise files folder called base data dot csv so this is a different type of file and if you want a little bit of extra practice i'm going to leave you to work out how to import that data in and you can also use that to practice creating a pivot table as well but again it's entirely optional But do try and work through those first two exercises to make sure that you have a full understanding of importing different types of data into excel and creating a pivot table that's it for this exercise in the next

section we're going to talk about how you can prepare your data for analysis so please join me for that hello everyone and welcome back to this course on advanced pivot tables We're now down into section three where we're going to be discussing everything related to preparing your data for analysis now i've spoken in previous modules about the importance of making sure that your data is clean and consistent prior to analyzing now the consequences if you don't clean your data prior to analysis is that you might end up with inaccurate results If you're not checking your

data beforehand so when we say checking we mean things like removing blank rows making sure our columns are formatted correctly making sure there's consistency with regards to the case and also doing things like removing any duplicate entries and that's what we're going to go through in this module i'm going to Show you a variety of different techniques within excel that can really help you when it comes to preparing your raw data so i'm going to show you two examples now this first one is going to be on a fairly small data set and it's the

one that we've used in the last couple of modules so we're going to Open a text file in excel and then we're going to use some techniques in excel to clean up that data prior to putting it into a pivot table now instead of importing this data in like we've been doing previously from the data ribbon i'm actually going to jump straight to the file menu and i'm going to open the file directly and the file i'm going to open is this One just here import text 2 dot txt now you can see this listed

in my recent files as i have recently used it and this is a text file so you can open other file types aside from dot xlsx files in excel and if it requires excel to convert them then it's going to walk you through that particular process so let's open this text file and see what happens so i've tried to open it and excel has Popped up the text import wizard now wizards are always really great as they generally tend to walk you step by step through a process and many of the options we're going to

go through are very similar to when we use the get data option from the data ribbon now the first thing this wizard is asking me for is to select the file type that best describes my data And by default it's selected delimited and what that means is that characters such as commas or tabs separate each field i can see a preview of my file in the lower half of the screen and you can see here that each field is separated by a semicolon so in this instance delimited would be the correct option for me to

select here i'm also going to say My data has headers and click on next to move on to the next step of the wizard now this is where i get to define what delimiter is being used in my data now by default it's selected tab and that's not correct in this instance i want to select semicolon and you can see as soon as i do that in the data preview window at the bottom excel has split up my data wherever it Finds that semicolon character which is exactly what i want if i click next again

to move on to the final stage of this import wizard this is where i can go through and i can set some column data formats if i want to now i'm actually going to do this once it's been imported into excel so at this stage i'm just going to click on the finish button To load that data into my excel worksheet and there we go so this is what it looks like once it's been imported in so there's going to be a few things i need to do here to get this data into a state

that's conducive to creating a pivot table now the most obvious thing here is that my columns aren't particularly organized in a very neat way so i'm going to want to resize these columns So very simply a quick way to do this is to hover your mouse over the first column click and drag across to highlight all of the columns and in order to resize all of the columns so that they fit all of the data within that column correctly all you need to do is hover your mouse over one of the column dividers until you

get that black double headed arrow double click and it's going to Resize all of the columns in one go so now that makes it a lot easier for me to actually read what data i have in my worksheet now what are the other glaring errors or things i might want to correct in this data well the one that stands out the most to me is that i have blank rows throughout my data now this isn't a particularly large data Set and i would imagine that a lot of the data sets that you're going to be

dealing with are going to be a lot larger than this maybe hundreds or even thousands of rows so whilst it would be quite easy for me to go through and manually delete out all of these blank rows if you have a very large data set that's going to be really time consuming and probably not something that you want to Spend time doing so i'm going to show you a much quicker way of doing this where you can delete all blank rows in one go now the first thing you want to do is make sure that

you have all of the columns selected so again i'm going to click and i'm just going to drag across i'm going to go up to my home tab or my home ribbon all the way across to this editing group on the end and i'm Going to utilize the find and select go to special option and one of the options that we have in go to special is to select all blanks and click on ok and there we go we now have all of those blank rows selected all in one go which means i can very

easily now go in and just delete them all and again i'm going to stay on the home ribbon i'm going to go to my cells group click the delete button drop down And select delete sheet rows so very quickly i have managed to delete all of those blank rows out i haven't had to go in and do them manually now the next thing i'm going to do here is i'm going to go through and make sure all of the items in my columns have the correct formatting applied to them so i'm going to select column

a i'm going to go to my home tab and look in this Number group and i can see here that we currently have general formatting applied so i might want to change that to text i'm going to go to column b now i can see that in column b we have some dates so again i'm probably going to want to change that formatting to short date now column c has salaries so i could go up to here and this is really personal preference Whether you want to choose currency or accounting now i'm going to choose

currency in this case and you can see that my currency is set to us dollars and this really depends what you have your locale set to in excel of course if you do want to change that currency symbol again from that number group you can click the drop down just here and you have some popular currencies that you might want to use But also you could go into more accounting formats and you can go through a whole list of different currency symbols but for now i'm going to leave mine on us dollars and then finally

department and job title they're both text fields so i'm going to do these in one go by highlighting both of them and changing those to text so now i know that i have the Correct formatting applied to all of my columns now the next thing i might want to do is looking at this employee name column i can see that my employee names are all in capital letters now i actually want these to be what we call proper case where only the first letter of each word is capitalized and i also want to split up

this employee name i want to Have one column with the last name and one column with the first name so let's take a look at how we can deal with all of those things so the first thing we're going to deal with is we're going to deal with changing the case and to do this i'm going to add in or insert what i call a helper column so helper columns and you'll hear lots of tutorials refer to helper columns they're columns that you add in In order to maybe do some cleaning up or working out

and you usually delete them after you've finished using them so this is my first helper column and what i'm going to do in this helper column first of all is i'm going to change the case to what we call proper case and you can do this very simply in excel by using one of the text functions now if we jump Up to the formulas ribbon in the functions library underneath text you'll find all of your text functions and the one we're going to use is called proper now if you're more comfortable using the functions dialog

box in order to do these then by all means you can do that alternatively you can just type the function directly into the cell So i'm going to type in equals proper i'm going to open my bracket and you can see there's only one argument for the prop up function and that is the text we want to make proper case and that is the text in cell a2 close off the bracket and hit enter and there we go that's now changed it to proper case and i can easily fill this down simply by using the

autofill handle in the bottom right hand corner that little green Square hover over until you get the little black cross double click and it's going to copy that formula all the way down so now i've done that i essentially have two columns displaying the same information so you might think you can then go in and just delete out this column but you're going to find if you do that you're going to get an error because essentially you've deleted Out the column that that function refers to so we need to go about this in a slightly

different way what i want to do is highlight column b where i have my clean data i'm going to click the copy button on the home ribbon and what i'm going to do is i'm just going to paste the values only directly over the top Now when you click the bottom of the paste command you have lots and lots of different paste options and you have a paste values group now when you're pasting values it essentially will throw away any of the underlying formulas and will literally just paste what you can see in the cell

so if i do paste values hit the escape key you can see now when i click on these Cells if you look up in the formula bar it just shows me the text in that cell it's not showing me that there's any proper formula underneath there so i can now safely delete out that first column and everything works nicely now i'm going to go through and i'm going to do exactly that same process for the department and the job title to make those proper case so once i've done that i will come back And rejoin

you for the next part so there we go so now i've made the information in columns d and e those are also in proper case so things are starting to look a lot better than they were when we first imported in this particular text file a couple of other things i want to do now this employee name as i said i want to split this up so the last name is in one column and The first name is in another so again what i'm going to do is i'm going to insert a column just here

and there is a really cool little tool in excel that's going to make this super quick and easy for you to do and it's called flash fill now flash fill was introduced in excel 2013 so if you have a version of excel that's earlier than that then you're not Going to have access to this utility but if you have excel 2013 2016 2019 or office 365 then you're definitely going to have this button which you will find on the data tab in the data tools group and it's this little one just here flash fill you

can see the keyboard shortcut for that is also control plus e and what flash fill does is it enables You to fill down information really quickly so i'm going to title this column last name and all i need to do is type in the first one so there we go juarez if i click on that cell go to my data tools group and click on the flash fill button like magic it's going to fill all of those last names down for me And i can do exactly the same by inserting another column and this one's

going to be first name and all i need to do is type in the first name and another way that i can utilize flash fill is not by clicking on the flash fill button but i can click in the cell underneath and as soon as i start to type in the second one can you see it ghost fills down the rest of those names for Me so all i have to do is hit enter to confirm and there we have all of those names i can now safely delete column a and i have very quickly

my names separated into two columns now something else that's really important to check for is to check that you don't have any duplicate entries in your data and again excel has a useful utility That's available on the data ribbon it's in the data tools group and it's this one just here remove duplicates and it says it's going to delete duplicate rows from a sheet and you can pick which columns should be checked for duplicate information so you can see here because i'm clicked in my data it's picked up all of my data and i have

all the columns selected so essentially what Excel is going to do it's going to make sure that every single column is the same before it considers it a duplicate so what we mean by that is you can see here that some people have the same job titles the sales executive sales executive it doesn't necessarily mean that the entire row is duplicate information It just means they have the same job title so i'm telling excel make sure that every column which i have ticked just here is a duplicate before you remove it so i'm going to

click on ok and you can see here three duplicate values found and removed so a really nice quick little way to remove duplicates from your data i also might decide that i want to change some of the wording in these columns so For example in the department column you can see that for most of these departments i have the full department listed out but for marketing i just have mktg so i'm actually going to change that to say marketing so i've highlighted my column i'm going up to that home ribbon i'm going to find and

select and i'm going to select the replace option control plus h is the keyboard Shortcut for that it's going to say what do you want me to find so in this case m k tg and replace it with marketing and i'm going to say replace all and they've made one replacement and this time we're going to type in r and d i'm going to replace it with research and development and click replace all and okay now the final couple of things i Might want to do here is i might want to do an overall spell

check of my text so i'm going to jump up to the review ribbon and i'm going to go to spelling now because a lot of these are names i'm probably going to want to ignore most of these until i get to one that i can see is an error so you can see here this is supposed to say terry And this is spelt incorrectly so i'm going to choose terry from the suggestions and say change and my spell check is now complete the final thing i'd want to do here before putting this particular set of

data into a pivot table is to put this into a table so if you remember from before if we do control a to select all of our data and control t will allow us to quickly Put our data into a table make sure that you have my table has headers selected click on ok and there we go the data is in a table i have my table design ribbon if i don't particularly like the colors that have been selected for my table i can choose to change them and we're now ready to summarize with a

pivot table so hopefully that's introduced you to a few techniques that you can use to prepare your data for analysis We're going to continue on this theme we're going to use a bigger set of data in the next module and we're going to move into talking about tabular data so please join me for that hello everyone and welcome back to this course on advanced pivot tables we're studying in section two where we've been discussing preparing your data for analysis preparing it For a pivot table and in the previous module we looked at a very basic

set of data and i showed you some of the skills that you really should know when it comes to cleaning up your data and preparing to put it into a pivot table in this module i'm going to show you a few more techniques for cleaning your data we're going to expand your knowledge on some of those functions that are really useful we're Then going to put this data into a table and i'm going to show you exactly why it's so important to put your data into a table when you're doing things like pivot tables and

pivot charts so let's start out by taking a look at the data set that we're looking at on the screen now this is a much larger data set than the one that we had in the last module so if i jump all the way down to the Bottom by doing control down arrow you can see that this has 707 rows control home is going to take me all the way back up to the top now some of these techniques that we're going to utilize we saw in the previous module we're also going to expand on

that as well so i'm going to move fairly quickly through the skills that we've already looked at and then we'll go into more detail the ones that are newer So starting out i can see again here i have lots of empty or blank rows in my data so we're going to go through that same process of highlighting all of our columns going up to find and select go to special and selecting blanks now interestingly what you'll see here not only has it picked up those blank rows but it's also picked up some blank cells In

my data now i actually don't want to remove these at this stage so i'm quickly going to go through hold down my control key and just click to deselect those now i only have those rows selected i'm going to go to delete delete sheet rows and those ones are gone now just going back to this column column g the discounts column i can see in this column i do have a Number of individual cells that are showing as blank now i would say one little tip when you're creating pivot tables it's always good to have

at least something in a cell even if it's just zero so again if you have lots of cells in a particular column or columns there is a quick way of just filling in a number or even just a zero into all of those Cells in one go so the first thing i'm going to do is i'm going to highlight this column now if you have blank cells in other columns you could highlight all of the columns but i just have them in column g i'm going to go back to find and select go to special

and select those blanks once again and now if i want to enter a zero into these cells All i need to do is press zero control enter and it's going to fill all of those blank cells with zero so again a really good time saving technique to add to your toolkit now going back to this first column column a looking down here i have some country names now i can see here that the uk is listed as uk whereas we have the usa listed out in full as united states of america So what i'm going

to do here is i'm going to find and replace the word uk with united kingdom so again we saw this in the previous module we're going to highlight our column we're going to go to find and select and choose replace control h if you like your shortcut keys what we're going to do is we're going to say find what uk replace with United kingdom replace all it's made 140 replacements and close that down now i can see here with column b that we have quite a few different issues going on here so the word kensington

is all in uppercase and i actually want everything again to be proper case but i also have a little bit of an issue with some of these individual cells so where we have royal oak You can see that that's been imported in and it's split across onto two lines so i'm going to need to deal with that as well another issue i can see is that for some of these some particular rows these rows just here 18 22 we have some erroneous spaces at the beginning of these words so i'm going to want to utilize

some text functions to deal with all of these issues that we've got going on In column b so i'm going to add in my helper row insert unfortunately what we can do here is we can combine functions together in order to achieve our outcome so as we know the way that we change text from all capitals to proper case is by using the function equals proper now what i could do here is just select the text close up my bracket but if i Copy this down it's only going to fix the capitalization problem it's not

going to fix any of these other issues what i'm trying to get away from is really adding more helper columns in order to deal with each issue individually i want to ideally be able to deal with everything within this one column so one thing that we can do Is if we delete out where it says b2 i can combine my text functions together so i'm going to have equals proper i'm going to open my bracket and then i'm going to add in another text function and the first one i'm going to add in is the

function called clean and you can see there as i type that in the little screen tip says that it removes All non-printable characters from text so if you have non-printing characters in there or if you have manual line breaks in there which is what we have going on in cell b8 where it's split across two lines the clean function is going to remove all of those types of things now when it comes to erroneous spacing so again if we look down into cell b19 for example You can see that we have a lot of space

before the word vermont now there's another text function that's going to get rid of all those erroneous spaces and that might be leading spaces training spaces or weird spacing in the middle of your words so i'm going to add another function so i'm going to open another bracket and that function is the trim function and again you can see it says removes all spaces from a Text string except for single spaces between words so i've combined three text functions into one formula i'm going to open my bracket and now it's asking me for the text

i'm going to select b2 and remember you need to close off as many brackets as you open so i'm going to close off b2 close off trim and close off clean hit enter and now if i copy this Down by double clicking to auto fill you can see there all of those issues have now been fixed we're going to do our old trick of copying and pasting values so i'm going to select column c i'm going to click the copy button i'm going to do paste and i'm going to paste those values directly over the

top so that i can safely then delete out Column b and i'm just going to add that title back in of product so very quickly we've been able to deal with a number of different issues all within one formula what i'm going to do is make sure that all of my columns are formatted correctly so column a i'm going to change that to text format column b is also going to be text now units sold i'm going to put that to Number and you can see here now i've changed that to number it's actually put

in two decimal places after the number which i don't really want so from my number group i have my increase and decrease decimal buttons so i'm going to say decrease those decimals down to zero now manufacturing price sale price gross sales discount sales cogs and profit those are all currency fields so i'm going to select All of these columns i'm going to go up and i'm actually going to apply a counting format this time and as i said the currency format that i'm using is usd this date column column k i have some really weird

things in here and this is fairly common in excel if you ever see a date displaying as a weird looking number all you need to remember is that by default Excel classes a date as a number in the background and the number you see is actually the number of days past the 1st of january 1900. so according to excel 1900 is virtually when the world began and if you are seeing this weird number instead of the day it usually always means that you just don't have the correct number formatting applied so i don't in this

case so i'm going to Change that to short date month name that's the text column that's fine and yeah i'm happy to leave that on text i'm going to do a final check on duplicates so up to data into my data tools group i'm going to click the remove duplicates button i'm checking for complete duplicates so i want to have all of my columns selected Click on ok it's found five duplicates which i'm going to remove and there we go i am pretty happy with how my data is looking so as we've seen the final

step that i always like to do is to put my data into a table ctrl a to select everything and we've seen a couple of different ways you can put your data into a table you can press ctrl t you can also go to the home ribbon And select a style from the format as table drop down you can even go to the insert ribbon and just select the table option but another option that you have when you highlight all of your data if you look right down in the bottom right hand corner you can

see you have this little icon popping up just there now that is the quick analysis tool control plus q is the Keyboard shortcut now if you click this you're going to get a whole host of different options what excel is basically doing is analyzing the data you have selected and then suggesting some things that you might want to do and that's across every different element in excel so formatting i could put it into a chart i could add some totals or work out some totals but one option i have is also tables So i'm going

to click on the table option and that's going to put my data into a table now i'm not overly fussed about the color scheme that we have here but but i am going to change this to a color that matches the overall theme a little bit better now again as i said once you've put your data into a table you're going to have access to the table design Contextual ribbon and as we've seen before i always like to name my tables in this first group so i'm going to give my table a meaningful name i'm

going to call this product underscore data and hit enter now the final thing i want to talk to you about in this module is the importance of putting your data into a table because i think sometimes people get confused on this step they Think well why do i have to put it into a table how is that going to be helpful to me well the way that it's going to be helpful is when it comes time to add new data into your data set most of the time data doesn't remain static forever if i was

to create a pivot table or maybe some kind of chart utilizing this data It might be that next month i add in more data and i want everything to update nicely and i want the update process to be as efficient as possible now the way to do that is to put your data into a table as we've done here so i'm very quickly going to show you why this is helpful now to demonstrate this i'm not going to put this into a pivot table i'm just going to quickly Put it into a pivot chart so

very simply i'm going to go up to the insert ribbon and i'm going to go to pivot chart i want to use my product data data range and i want to put it on a new worksheet and i'm going to click on ok now i'm just going to add a couple of fields in here so i'm going to say gross sales in the values and we're going to do it by product In the categories column so now what i'm going to do is i'm going to add in some new data and show you how easy

it is to update a chart once your data is in a table so let's go back to our data and i'm going to do control down arrow just to jump down to the bottom of my data set now i'm going to cheat a little bit here what i'm actually going to do is i'm just going to copy Some of this data control c and i'm going to paste it onto the bottom of this data set so control v now because my data is in a table it's in tabular format the table expands to accommodate that

new data and because i've named my table product data and the chart is created off of the product data range it means that anything that's included within That range is going to be automatically updated in the chart so let's jump back to our chart click on it and if i want to update this chart so it reflects the new data that i've just added all i need to do is go up to the pivot chart analyze ribbon and there is a refresh button just here now i haven't added that much in so this change might

be Very small but let's click the refresh button and watch the chart for any slight movement and there we go so we did have a very slight movement and numbers have updated in our pivot table and the pivot chart has updated as well now if i didn't have my data contained within a table i wouldn't be able to go in click on that refresh button and have everything update nicely it would have taken me a Lot longer in order to be able to achieve the same thing so that is one of the reasons why putting

your data into a table is super important it just makes your life a lot easier not to mention the fact that you also have these little filter buttons at the top which can really help you organize and sort your data quickly And easily so that's it for this module you've seen a couple more techniques when it comes to cleaning data we've talked through the importance of putting your data into a table and you've seen why that is so important when we've done a quick refresh in order to update anything that we have hanging off or

using this particular set of data in the next module we're going to do an Exercise to practice the skills that you've learned in this section and then we're going to move on to really diving into creating pivot tables manipulating them and going into some of the more advanced functions so please join me in the next module for that for the next section you'll want to download the course exercise files click the link below in the video description to get these You can also scroll through the details to find timestamps for each section in this course

if you're enjoying this training please leave us a comment hello everyone and welcome back to our course in advanced pivot tables we've made it down to exercise two and in this exercise we're just going to practice some of the skills that we've learned in this section about cleaning and Preparing our data for analysis now you'll find the exercise files in the exercise files folder so once again make sure you have those downloaded somewhere and what i want you to do in this exercise is i want you to open the file cleaningdata.xlsx and you'll find that

this file has two worksheets cleaning data which is on the screen right now and another worksheet called 2019 Data and what i want you to do is i want you to utilize the skills that you've learned in this section in order to clean up this data put it in a table ready for analysis so remember some key points that you're looking out for here is you want to make sure that you are spell checking what you have in the worksheet you want to make sure that everything has consistent case You want to remove any blank

rows or blank cells you want to check and remove any duplicate entries in your data and you also want to make sure that you have each column formatted with the correct number formatting style now once you've done all of that i want you to put the data into a table and you can choose any one of the Methods that i've explained to you in the previous modules i'd also like you to name the table and i will leave the name up to you but just remember that if you are using two words to name your

table you're going to need to make it all one word or separate with an underscore now once you have your data in a table i would like you to put that data Into a pivot chart and for that i'm going to show you what my answer looks like so this is what my pivot chart looks like and i want you to try and recreate the same as what you can see in my worksheet so what you can see is that i have the departments listed across the x-axis i have the quarters in the legend area

and the sum of sales is My data so i want you to recreate this chart that i have on the screen as best you can and the final part of this exercise is i want you to add the 2019 data to the original data set so at the bottom there is a worksheet called 2019 data which has the sales figures for each department for the year 2019. i want you to take this data and i want You to paste it into the bottom of the table that we have on the cleaning data tab finally once

you've done that i want you to go back to your pivot charts and i want you to make sure that you update or refresh the chart so that it reflects the new data that's been added see how you go with that exercise and i will see you in the next section Hello everyone and welcome back to this course on advanced pivot tables we're now down into section four where we're going to talk about creating and manipulating pivot tables now in this first module i'm going to show you a couple of different methods when it comes

to creating a pivot table however i would say that if you are attending this course on advanced pivot tables you probably already have a pretty good idea Of how to create a pivot table so my aim here is really just to consolidate that knowledge and show you a couple of different techniques that you might not have used previously and also throw in some extra tips and tricks when it comes to creating a pivot table from scratch so we're going to start out where we left off and that is basically with our clean data set all

of my data is consistent And i've put it into a table and i've also named this table sales underscore data so now i'm at the stage where i'm going to create my pivot table now there are numerous different ways that you can do this as we've seen previously i could as i'm on the table design ribbon already click on the summarize with pivot tables Button i could also jump across to my insert tab and in the tables group the first option there is pivot table now we're going to use those in a moment but the

first thing that i want to point out to you is this second option that we have here recommended pivot tables now what recommended pivot tables does is it suggests to you some pivot tables That you might want to use based on the data that you have in your spreadsheet so excel has essentially analyzed my data and it's now suggesting to me pivot tables that i might find useful and you can see that the top one that's suggesting is sum of profit by country and you normally will find that the most useful one is going to

be nearer at the top or the one that Suits your data the most will be closer to the top of this list so if i was to select this top pivot table what it's going to do is create a pivot table for me which is going to show me all of the countries listed in the rows and then the total sum of profit for those countries so if that's the kind of breakdown that i'm looking for This is a super quick way of me creating this pivot table it means that all the fields are going

to be in the correct positions for me i don't have to do that manually and you can see as we go down we have some other different options in here so i could choose to display this by month and show the sums of profit by month i can show it by the sum of the number Of units sold and manufacturing price and all different kinds of options that i have in here now for the time being i'm going to keep this fairly simple i'm going to select this top one and i'm going to click on

ok and there we go you can see very quickly with one click i've managed to create a pivot table showing the sum of all of the profit for each of the countries And my pivot table fields have been pre-populated by excel based on my selection so i have my country in rows and my sum of profit in the values area now of course i'm not stuck with this if i wanted to add more fields in i could definitely do that so for example i could grab the product field and maybe drag that down into columns

and i now get a different layout for my Pivot table report i could remove country from rows simply by dragging out and maybe replace that with product in the rows but the point being here is that that recommended pivot tables option is a great option to give you a base to start with so i'm going to delete this pivot table sheet And just jump back to my original data source so that's your recommended pivot tables option let's now take a look again at creating a pivot table from scratch i'm clicked within my data make sure

that you're not clicked outside of your data i'm going to go up to the insert ribbon and i'm going to select the pivot table option and again we need to choose The data that we want to analyze and if you've named your table you will see the name of your table or range listed below so i can see sales underscore data is my range again remember you do have other options in here you could choose to use an external data source so maybe a table you have stored off in another excel worksheet and there is

also a third option to use This workbooks data model now we're going to get to that right at the end of this course for the time being i'm going to select my sales data range i can then choose where i want the pivot table report to be placed either on a new worksheet which is always my recommendation to keep your charts and your tables separate from your original data source but if you wanted to you could choose an Existing worksheet and then you could choose any worksheet that you have in this workbook so if i

click the up arrow i can go through and if i had a separate worksheet i could select that one and it will place the pivot table on that particular worksheet now i'm going to say new worksheet and then the final option we have at the bottom is if we want to analyze multiple Tables and again this is something we're going to cover in another module in this section so for the time being i'm happy i've selected my data source i've said new worksheet let's click on ok to create our pivot table and there we go

once again we have our blank pivot table report on the left hand side and we have our pivot table fields on the right hand side i have my pivot table analyze contextual Ribbon at the top which contains all of the utilities i need to analyze my data and we're going to get into most of these throughout the balance of this course and i also have an additional pivot table design ribbon which will allow me to control the formatting or the way that i'm looking at this pivot table so i'm going to add some fields i'm

Going to take my gross sales i'm going to drag that down to values i'm going to grab the country field and put that down into rows and i'm going to grab the product field and put that up into the filters area now remember when you're working with these fields you can have more than one field in each area so what i could do is grab Product and drag it down to rows and you can see when i hover that green line i can either place it above or below where it says country if i drop

it below you'll see now how my pivot table is organized so it's organized by country first and then product country first and then product if i was to move product above country I get it organized in a different way so this time it's organized by product first and then by country i could go a stage further and maybe grab the date and drag that down into columns and now i'm seeing a breakdown of sales between 2018 and 2019. now you may have noticed when i drag that date field down into columns it kind of split

itself into three separate fields so now we have dates Quarters and years and this is a little helpful thing that excel does for you is when you add a date it's going to automatically break it down into smaller parts so if i wanted to i could sort by quarters and also by years so if i click the plus next to 2018 you can see here i have quarter three and quarter four and if i click 2019 i have quarter one quarter two quarter three Quarter four and i could carry on going even further to see

the breakdown by month now if you get to the stage that i've got to where you've expanded lots of different things and you want to very quickly just collapse them all up again if you jump back up to pivot table analyze you have an active field group and this icon just here will collapse the fields for you like so now we're going to get into this A little bit more when we discuss grouping but hopefully that gives you a little bit of an idea it's also worth noting here that with this automatic split so where

it adds years quarters and the date field if you don't like it doing that if you simply just want it to have the date and not break it down to years quarters months days then you can turn that option off so for example i have 2018 selected which is Currently grouped and if i go up to the pivot table analyze ribbon i have a group field option and this is where it's telling you what it's going to display when i drag that date field so if i was only interested in years i could untick both

months and quarters click on ok and now you can see that those other fields have disappeared from columns and i just have date And it's just showing me 2018 and 2019. i can no longer drill down into the individual months or quarters now one thing that's also worth noting when you're dealing with your pivot table is that you can drill down into your data to obtain more information so for example currently i'm clicked on japan for the product burlington and i can see the grand total Just there but if i wanted to see what makes

up this grand total if i double click it's going to open up another worksheet and i get to see the details sitting behind that particular grand total so i can see that the country is japan the product is burlington and i can see the amount of units sold manufacturing price the sales price of Each of those sales and all of this other information so don't forget double clicking on any of these items will allow you to drill down and see the information that sits behind now the final thing i'm just briefly going to mention here

is in this pivot table fields pane right at the bottom you can see there is an option for defer layout update now what that means Is that probably what you'll notice is as i'm moving fields around so if i move country up to filters the pivot table changes it updates as i move these fields around into different parts of the pivot table now that's fine for what i'm doing at the moment but if you have a particularly very large data set every time you move a field you may find that it's taking a little while

for the pivot table to rearrange itself And sort itself out that's a common problem when you have an extremely large data set so it might be to make that process more efficient that you want to organize your pivot table fields first and then do an update all in one go and if that is the case then you would want to select the defer layout update button so if i select this button and then i Drag country back down to rows the fields moved but my pivot table hasn't updated it's not going to update until i

manually click the update button and then any changes that i've made are going to be refreshed and updated in the pivot table so don't forget about this little defer layout update if you deal with large data sets so that's it for this module We've seen how we can utilize recommended pivot tables to make the process a lot easier and give us a good starting point we've created a pivot table from scratch from our data set i've shown you some options when it comes to pivoting or moving your fields around and we've looked a little bit

into grouping and ungrouping dates how you can drill down and see the Underlying data and also how you can defer layout updates that's it for this module i will see you in the next one hello everyone and welcome back to this course on advanced pivot tables this is still deb and we are down in section four and in the previous module we took a look at a couple of methods for creating pivot tables from scratch And also by using that recommended pivot tables option in this module i want to do something slightly different and that

is i want to show you how you can combine data from different tables and utilize it all to build one pivot table so let's jump straight into an example i've got a worksheet open on the screen it's called consolidating.xlsx and what you'll immediately notice is That i have three separate worksheets contained within this workbook the first one you can see on the screen is customer info the second one is order info and the third one is payment info now all three of these worksheets relate to the sale of some products and what you'll notice is

that whilst there is different information on each worksheet there is Also one piece of common information that runs through all of the worksheets so for example on this customer info tab i have a column for order id i have the customer name and i have the state that that customer is in on the order info worksheet again i have the order id i have the type of transaction so if it Was online in store or through a third party and i also have the method that they use to pay for those goods so whether it's

a debit card credit card cash cheque or a voucher and then finally on the final worksheet again i have the order id i have the total and then i have the status so whether it's been shipped or if it's still in the processing stage and what i'm Going to do whilst i'm here is i'm just going to do a quick control shift down and i'm going to add a currency symbol onto these totals now what you will notice here is that running through each of these worksheets is the order id so despite the fact that

the other two columns are different the order information is the same on every single worksheet and this is a really important Point when you're coming to consolidate different worksheets and linking them together it's really important that you have a field on each of the worksheets that we would classify as the key so something that links all of the data together now what we're aiming to do here is we're aiming to create a pivot table which incorporates all of these column headings as fields Into the pivot table and i can then use any of these fields

to build up my pivot table now another important thing to note here is that with these different sets of data on the different worksheets currently these are independent of each other and that's a really important point to note when we come to linking add tables together now i've just mentioned that word tables And again this is something that we've covered in quite a few of the modules previously the first thing we need to do here is put our data in a table and also name those tables so we've seen how to do this before i'm

going to click in my first set of data on the customer info worksheet i'm going to do a quick control a and i'm going to do a ctrl t to quickly create a table I'm going to go up to my table design ribbon and in the table name field i'm going to call this one customer underscore info i'm going to go to the next worksheet and i'm going to do exactly the same thing so this time i'm going to go up to the insert ribbon i'm going to select the table option click on ok and

once again i have my table and this One i'm going to call order underscore info and then finally across to the payment information tab and i'm going to do this one a different way again i'm going to utilize the home ribbon i'm going to go to the styles group and i'm going to select format as table and i'm going to select one of these formats to put that data into a table and this one is going to be called Payment underscore info so now essentially i have my three different tables now these are not linked

together at this stage okay the next stage is once you put your data into a table you've named all of those tables you then want to create your pivot table now you have to do this quite a specific way so i'm on the customer info tab i'm clicked inside my first table I'm going to go up to the insert ribbon and i must utilize the pivot table option in order to do this we cannot use recommended pivot tables we must select pivot table so you should be using this little dialog box it's saying select the

table or range and we have our customer info range selected which is fine at this stage i want to put it on a new worksheet but The important option here is that we want to come to this bottom option where it says choose whether you want to analyze multiple tables i do because i have a separate table on each worksheet so i'm going to select add this data to the data model now what you'll notice is that i have a new blank worksheet and i have my empty pivot table report now whilst i'm here i'm

going to Rename this worksheet i'm just going to call it pivot table so it's a little bit more meaningful and i'm just going to drag this worksheet so that it comes after the other three original worksheets now what you'll notice here is that we have our blank pivot table report on the left hand side and then we have our pivot table fields on the right hand side now this will Look slightly different you can see here it's got customer info which is our table and then we have the three headings or the three fields from

the customer info table and this is in a little collapsible and expandable group so if i have quite a few and i want to just collapse those up to make it easier for me to see other things i can definitely Do that now the reason why we have only the customer info fields here is because that was the table we were clicked in when we created the pivot table and what you'll notice is just above it shows that i am showing the active fields now next to this active area we have an all button so

let's see what happens when we click on all you'll see that what excel has actually Done is that it has imported all of the tables even though i was just on the customer info table when i created the pivot table it's recognized the other tables and it's imported those in for me and i can now expand these and i can see all of those fields from each of the tables so that is pretty cool so now you can probably start to get the idea of how we Can utilize fields from different tables in one pivot

table we can start dragging and dropping down to our pivot table areas down here right well be careful with that because there is one more stage that we need to do before we're able to start utilizing fields from different tables to build one pivot table and that final step is that we need to create Links between these three tables so currently they're three completely separate tables not linked together at all so effectively if i was to drag the customer field down to rows and then decide that i want to use the total field from payment

info because these tables aren't linked in any way excel is not going to know what data it needs to display so we need to set up a link between the Three tables and this goes back to why it's really important that you have one field or one column in your tables that runs through all of the tables and in this case that field is the order id i have the order id column on every single worksheet and if you've been used to or if you've ever used an application like Access which is a database application

you may be more familiar with the term of primary key it's a unique field that runs through all of our tables and we're going to use that field to link our tables together so that we can build our pivot table so how do we go about linking the order id field in the customer info table to the other tables that we have well For this we're going to go up to the pivot table analyze ribbon and you can see we have a little option here for relationships and our screen tip says create or edit relationships

between tables to show related data from different tables on the same report so let's click this little button and see what we have so it opens up our manage relationships Dialog box so the first thing i'm going to do here is i'm going to click on the new button to set up a new relationship so this is where i can start creating my links between my tables so i'm going to click the drop down and you can see it's got my three tables listed just here so i'm going to say payment info and the column

i want to link is the order id column And i'm going to link that to the order info table order id column and i'm going to click on ok so now essentially what i've done is i've linked the order info field in the payment info and order info tables together i've still got one more to do so let's click on new and this time i'm going to link the payment info And the order id to the other table that we haven't yet linked which is the customer info table and the order id and click on

ok so now essentially i've linked together my tables using that order id field if i click on close now what i should be able to do is utilize any of these fields to build up my pivot table so what i'm going to do is from this customer info table i'm going to Take the state field and i'm going to drag that down into rows so now i have all of my states listed in my rows i'm going to go down to the next table and i'm going to take the let's choose the method and i'm

going to drag that into columns and then finally in the payment info table i'm going to take the totals and i'm going to drag that down into values now i don't particularly Like the layout of that so i might choose to move method into filters like so and now i can see all of the states i can see the sum of their totals and i can then use my filter to filter by a specific method and i could carry on doing that so again i could take maybe let's take the customer and i'm going to

drag that and drop it into rows just above so i can now see by customer and Then by state i could drag it underneath to get it to display a different way i could drag it across to the columns to get it into a slightly different format there's lots of different things i can do here but the main takeaway is that we've taken information from three unrelated tables we've created a pivot table and we've linked them together so we can utilize the fields from any of those tables to Build one pivot table hopefully that makes

sense to you guys that is it for this module i will see you in the next one hello everyone and welcome back to this course on advanced pivot tables we're still down in section 4 where we're taking a look at pivot tables in a little more depth and in this module i just want to briefly introduce you to the concept of Grouping your data together so let's jump into an example straight away i'm currently in that sample sales data spreadsheet and you'll find this in the course files folder if you want to follow along this

one's called sample sales data grouping grouping.xlsx and what i've done here is i've taken the data that we were using in a previous example I've put it into a pivot table and you can see how i have my fields arranged so currently i have the country running across the top in the columns in my rows i have the year followed by the product and then i have my profit displaying in the values area now what grouping can help you do is really just further analyze your data by grouping and Organizing it in a way that

makes it easy for you to see exactly what you're looking for and it's worth noting that you can group numeric fields you can group text fields and you can also group date fields so in this module i'm going to show you an example of each of those it is all fairly straightforward but if you're not sure how to do it then stay tuned so let's start out by grouping by Text fields so as you can see in the rows i have the product list just here so burlington kensington lux mandarin royal oak and vermont now

maybe each of these are part of a different range so maybe the burlington the kensington and the lux are part of the luxury range mandarin and royal oak are part of the premium range And then vermont is part of the standard range now i don't have a range field in my original source data but that doesn't mean that i can't group these by those specified ranges i just need to do it manually so all you need to do if you want to group these together is i'm going to select the fields i want in the

first groups this first group is going to be called Luxury and these three the top three burlington kensington and lux are all part of that luxury range so i've selected them right click and you'll see that within your right click contextual menu you have a group option and you can see here i now have it says group one i have burlington kensington and lux underneath there now i don't want this to say group one So i can very easily just click in that cell and rename this i'm going to call this group luxury and hit

enter now what you'll see is going on in the pivot table fields if you now look down in row i have another field that's automatically been added in there called product two so this is essentially the range that i've just created so i'm going to also rename the field from Product to to range so if i click a little drop down arrow and go to field settings this is where i can come in and give that field a custom name so this is going to be called range and click on ok so that now makes

that field a lot more meaningful so i've grouped those first three now what i want to do is i want to have mandarin and royal oak in the premium Range so again i'm just going to select those two right click click on group and i'm going to rename this group premium and the final one is just the vermont that's part of the standard range so all i need to do is click here because there's only one and change the name to standard so very quickly i've managed to add in some information which now helps me

Organize my products by the range that they belong to and these are of course collapsible and expandable so if i'm just interested in what's in the premium range i can simply expand that one and see my profit figures for those particular items so very simple to do and of course if i wanted to actually remove that because i have this range field now in rows if i drag That outside of rows it's going to put all that information back ungrouped to how it was but it's also worth noting that even if you do remove that

field that field will still remain in your pivot table field so that you can re-select it if you want to so now let's take a look at how we can group when we're dealing with numeric fields So currently let's take a look at our pivot table i have my countries in the columns i have my sum of sales in the values area and i have my years 2018 and 2019 in the rows area now i'm going to rearrange this very slightly i'm actually going to remove year from rows and i'm going to replace it with

sales price so what you can now see is the Sum of all sales by sales price now it might be that i want to group these sales prices together so it displays a range so maybe i'm interested in knowing what the sum of sales or the total sales for all of these countries is when the sales price is between zero and fifty dollars then fifty and a hundred a hundred and hundred and fifty So on and so forth so this is where we can bring in numeric grouping so all i would do here is again

i would select one of the items in the sales price column right click and go down to group so now here i can specify where i want to start and where i want to end and by how much i want to increment by so i'm going to say i want to start at zero dollars It's going to end at 350 because that is the highest amount i have in this data and i want to show a range that goes up in increments of 50. click on ok and there we go you can now see that

this has been grouped together so i can now see the total sum of sales for all products where the sales price is between 0 and 49 100 and 149 and then 300 To 350. now if you're wondering why there is a big gap so why it doesn't go from 150 to the next one that's because if we go back you can see here in my data we basically don't have any data for that particular range so excel has left that one out so again fairly simple to group or create your own numeric groups as well

what you'll also Notice with this one is that it doesn't create a field down in the pivot tables fields area but if i wanted to remove this grouping all i would need to do would be to jump up to the pivot table analyze ribbon in the group section i have an ungroup button and if i click that it's going to ungroup my data and put it back to how it was Originally in this final example we're going to talk through grouping of date fields and we did touch on this in one of the earlier modules

so currently i've rearranged my pivot table again and i just have some very basic analysis happening here i have my countries and the sum of sales for those particular countries now what i'm going to do is i'm going to remove country from rose And i'm going to replace it with one of my other fields and the field that i'm going to use is the date field and i'm going to drag that down into rows now watch what happens in this area when i let go of the date field you can see that not only do

i get my date field down there excel has given me years and quarters as well so it's essentially looked at my raw Data so if we jump back across there this is my date column and it said okay i can see that we have months in here we have years and i could also split this down into quarters and it's automatically giving me that breakdown as separate fields and if you look at the pivot table report you can see that we have some grouped areas 2018 and 2019 those are obviously the years But if i

click the plus next to 2018 it's broken it down into quarter three and quarter four remember this is based on the dates that i have in my raw data i've expand quarter three and i can see i have some information for september quarter four i have some information for the later part of the year and the same with 2019 i have all of my data for the year of 2019. so to me it looks like We started reporting on these sales during quarter three of 2018 because we don't have any information before that so that's

a really nice way for me to see that information broken down automatically now it might be that you don't particularly like this automatic grouping that excel does and that's absolutely fine you can remove whatever you don't want so if i don't want this broken down into quarters If i grab my quarters field in my pivot table fields rows area i can just drag that out and it removes those quarters and i can just see it broken down by the months alternatively if i right click and go to group you can see in this little grouping

area what i actually have selected so when i drag a date field down it's going to give me months quarters and years so again if i'm only interested in Months i could deselect these select months click on ok and now i'm just getting those months they're not even broken down into years if i wanted to add years back in i could again right click go to group and i could also have years displaying click on ok to bring those back and you'll see as i'm doing that it's moving those fields around in the pivot table

fields area for me so hopefully that gives you more Of an idea as to how you can group numeric text and date fields together and also customize the fields make them meaningful and utilize those to get a deeper analysis of your data that's it for this module i will see you in the next one hello everyone welcome back to my course in advanced pivot tables this is still deb and we are still down in section four where we're taking a Look at creating manipulating and also organizing pivot tables and you've had quite a lot of

information so far with regards to how you can organize and lay out your pivot tables but there are still a few more options and that is what i want to go through in this module we're going to take a look at things like subtotals and grand totals And the different style of layout that you can apply to your data however before we jump into that i just want to address something which you may have been wondering as we've been going through the last few modules and that is the data you can see on the screen

just here so the sum of sales hasn't been formatted so there's no currency symbol on this data So currently these numbers are a little bit hard to read now one misconception with pivot tables is that when you want to format your numbers or your values the misconception is that you can simply go in maybe select them all and change the formatting from the home ribbon like you would do with just a normal in-cell value now you definitely could do that i could add some Currency symbols in here but if you do it that way you

may come across problems later down the track a better way of doing this is to click anywhere in your numeric data right click your mouse and go down to value field settings and once you're in the value field settings dialog box you'll see at the bottom you have a number Format option and that's going to take you into your format cells dialog so this should look reasonably familiar if you've worked with excel quite a lot and this is where you want to come in and apply the correct formatting for your values so in this case

i'm actually just going to say currency and i'm going to take the decimal places down to zero click on ok click on ok again and now i've formatted All of my values so don't forget about that option lurking in that right click menu so now we've done that let's get on to talking about subtotals and grand totals first of all now if you look in my data you can see that currently i have my products and i have them broken down by year so 2018 then my products 2019 and then my products underneath and what

i actually have running across the top Here are my subtotals for each of the columns so in cell b5 just here this is the subtotal across all of these products for 2018 for the country of brazil now you may like subtotals to be running across the top for me i'm very much used to seeing a subtotal at the bottom so whenever i have them at the top it tends to confuse me a little Bit so if you're similar to me and you want to move how you're seeing or move the location of these subtotals down

to the bottom you can do that very easily on the pivot table design ribbon and this is the group that we're going to be working with in this module it's the layout group and the first drop down that we have here is subtotals Now i could choose to not show subtotals at all so if i select that you can see that they all disappear or i can say i want to show them at the bottom of the group or at the top of the group so i'm going to select bottom of the group and for

me that's a lot easier to read i can see the 2018 total and it's underneath all of the individual values but again that is very Much personal preference we have a similar kind of deal with the overall grand total so right in that bottom row we have a grand total for each of our columns and again we have a grand totals drop down in that layout group so we can turn it off for both rows and columns we can turn it on for rows and columns so not only do i have Grand totals listed at

the bottom i also have them listed for the rows as well or i can choose to have them on for rows only or on for columns only so i'm actually going to say on for columns only and just have that grand total displaying at the bottom now once we're talking about totals another little useful trick which i recommend you turn on when you're Working in excel is the status bar options so for example if you're somebody who likes to highlight a set of numbers so let's just say these ones here for the united kingdom if

you like to be able to see the average the min the max the sum of all the numbers that you have selected in your status bar then you need to turn on those options within the Status bar so you can see that i have this range of cells selected if you look right down in my status bar at the bottom i can see the average of those numbers i can see the count and i can also see the sum now because i have subtotals turned on i can verify that the sum that's showing in the

status bar is exactly the same as the subtotal Listed in cell e12 so these little metrics are really handy to have turned on when you're working in anything in excel not just pivot tables now if you can't see these when you highlight some numbers if you right click your mouse in the status bar it's this little group just here so you can see i have average count and some turned on but i could choose to turn on minimum And maximum as well so now when i highlight numbers i can also see the minimum value and

the maximum value in that selected range so that's just a little tip i wanted to throw in there because i find that so useful when i'm working in excel and it's also a good way of verifying your subtotals now moving on from subtotals and grand totals the other options that we have in this Layout group are different kinds of report layouts so let's switch into compact form now you can see with compact form what it does there is again it's moved those subtotals up to the top and all compact four means is that you can

have multiple fields displayed in a single column the next one is outline form so let's click that and see how That is different so again here we have a slightly different layout outline form will display your fields in separate columns so you can see here i have a column for year a column for product i then have all of my country columns running across and then the final layout that we have is tabular form so this is kind of similar to the Previous one the outline form the fields are in separate columns still but this

time there are no nested values so it's really up to you which report layout that you choose which one you prefer to work in but sometimes depending on what you're doing and the analysis that you're trying to do you'll find that maybe one is more suitable to what you're trying to achieve or makes it easier to see your information Now a couple of other things that we have in this report layout drop down is this one here repeat all item labels so what this means is if you look at what we have currently in the

year column i have 2018 and then i have my products and i have all of my sales information but what you'll see is that i only have the label 2018 in that first row now if i wanted 2018 to display in every single cell next to each of the Products that is where i would use the repeat all item labels button and it's going to put the year next to each line item and then of course the final option that we have in there is do not repeat item labels if you want to remove them

then you can just select that option as well now i'm going to switch my report layout back to a compact form and you can see here that when i do that In this particular layout repeating those row labels isn't really relevant because of the way that my data is laid out it makes much more sense if i'm working something like tabular form where i have these empty cells here to repeat those row labels but not so much in compact form and then the final option that we have in this layout group is blank rows so

again this is a personal Preference item if you want to organize or lay out your data so it's a little bit easier to read you can choose to insert a blank line after each item so if i select this option it's going to put a blank line in between my two groups in between 2018 2019 and i also have a blank line at the end here I then might decide that i want to put my subtotals back to the bottom of the group so that is kind of what it would look like if i was

creating this report for myself that is my personal preference i like to work in compact form i like to have my subtotals at the bottom and i like to have a blank row separating each section of my data because i just find it less chaotic And a bit easier to read so definitely worth going in having a little play around with these and seeing which one you prefer to work with and just to finish off this module as we're kind of talking about the way things look and styling our pivot tables up very slightly just

a couple of little things in here so at the top we have some of sales and column Labels these aren't particularly meaningful so you might want to rename these now i'm going to click the little drop down arrow just to remind myself of what we have in here so i can see that this is a list of all of the countries so what i might want to do is just to rename this to countries at the top to make that a bit more meaningful i also might want to change some of Sales to sales totals

like so so don't forget that you can just jump in and change any of the labels the titles to make it more meaningful and i can see that we have one more here so this is the year so i'm going to change row labels just by clicking to year and there we go slowly my pivot table is Becoming a lot easier to read for myself and anybody that i share this with that's it for this module i will see you in the next one hello everyone and welcome back to this course on advanced pivot tables

we've made it all the way down to the last module of section four and i really just want to finish off this section by showing you a couple Of other really useful tips and tricks when it comes to managing and organizing your pivot tables now if you look at the example on the screen in front of me and you'll find this file in the course files folder it's the sample sales data error values spreadsheet and what we're going to do in this module is i'm just going to show you how you can format error values

and empty cells In your pivot table now in order to demonstrate this i've kind of had to add a few errors into my source data so let's reevaluate and take a look at what we're currently looking at in this pivot table now you can see here when i'm clicking in my pivot table i'm not getting my pivot table fields list over on the right hand side Now if that does happen to you if you just jump up to the pivot table analyze ribbon in the show group at the end you have a field list button

so you're just going to want to toggle that back on now a small change that i've made since the last module is that i've added in another product so you'll see here for both 2018 and 2019 we now have a new product in The list called markhaser and i've actually added this into the raw data so if you have a look just here there is one of them in row 25 we have marchesa and what you'll see in the raw data is that we don't really have any data but we do have an error in

this sales column so when i click back on my pivot table you'll see that that error that n a Error has been brought through to my pivot table now for 2019 you'll see that we also have marchesa i don't have any errors but i just have no sales data for market for 2019 so how can i deal with both error values and also items that don't have any data well fortunately excel has a couple of little utilities that have been built into the field settings that will allow you to choose how you want to manage

these So the first thing i'm going to do here is i'm going to right click my mouse and i'm going to go up to my pivot table option so the first item that i'm dealing with here is that n a error value now if you're not somebody who likes to make selections from the right click menu and you prefer to utilize the ribbons you also have access to pivot table Options from the pivot table analyze ribbon in this first group you can see there you have options and that's going to take you to exactly the

same place now there's lots of different tabs in here and the one that you want to be clicked on is this one just here layout and format you can see in this format area here the first option is for error values show and we currently have a blank field And we don't have this option turned on so at the moment my error values are just showing as n a which isn't particularly descriptive or meaningful to anybody looking at this spreadsheet or at this pivot table so what i might want to do is say for error

values show the text no data i could also choose a numeric value so if i wanted to i could have zero in there but i'm going to say no data I'm going to click on ok and you can see now that that n a value has been replaced and if i had lots of n a errors in my pivot table all of them would be replaced by me doing that now as i said if we go back into options i could choose to just use a number so zero click on ok and it's going to

give me zero in there so it's entirely up to you how you want to display those error Values now something else you might want to do is you might want to not show any items that have no data so moving down to 2019 we have a different situation here with marchesa there's just no data for 2019 and i can confirm that by jumping back to my original source data i'm going to use my product filter to select only marquesa And i can see there there is my entry that has no data in it but has

an error that's the one that we've just dealt with but for 2019 i don't actually have any data but it's still showing the item listed there because it is part of the data set now you might be happy with just having a blank row in there and again you might want to fill these blank cells with some kind of value so Again you might just want it to say zero so what you could do is go up to pivot table analyze go into options and again in layout and format where it says for empty cells

you could say that you want to show that as a zero as well click on ok and it's going to fill those empty cells with zeros now i'm just going to undo that change the other thing that you could do is you could choose not to display the line Item if it contains no data and for this we go to a slightly different area if i right click my mouse we want to go into field settings and straight away i'm going to jump across to the layout and print group and you'll see right at the

bottom i have a tick next to show items with no data so currently even if there are no values associated With the item it's still going to show in the pivot table just all of the corresponding cells are going to be blank if i just didn't want to show marchesa when it has no data i could just untick this checkbox click on ok and you'll now see that marchesa has disappeared for 2019 because there is no data but i still have it listed for 2018 Because there is effectively something in there and finally just a

quick tip what i've now done is i've gone into my raw data and i've actually got rid of the error and i've added some data in for marquesa so now i just want to very quickly update my pivot table i can click in my pivot table go up to the pivot table analyze Ribbon and in the data group i have a refresh button so when i press refresh just watch what happens to marquez app for 2018. you can see now that i no longer have an n a error it's updated with the total for the

united kingdom only and the reason it's done that is because we only have the marchesa item for sale in the united kingdom it's not for sale in any of these other Areas so again for these ones i might just want to make sure that i have a blank filled in there so i'm going to say zero okay and that just makes it look a lot nicer we don't just have blank cells or error messages floating around in our data so that's just some quick tips on how to deal with errors in your pivot table how

you can customize them How you can choose to display or not display items that have no data and just to finish off something that i haven't done yet is i'm going to name my pivot table so we've seen the concept of naming tables previously it's the same with pivot tables if you want to keep everything consistent and easy to read then you're going to want to make sure that you do Name your pivot tables so again up on the pivot table analyze ribbon in the first group i have the very generic name of pivot table

one i'm just going to change this to global global underscore sales and hit enter and now i have a nicely named pivot table that's it for this module i will see you in the next one hello everyone and welcome back to this Course in advanced pivot tables we've made it down to exercise three where we're going to practice some of the skills that we've learned in this section now you'll find this exercise in the exercise folder and the workbook that you need for this particular exercise is called hr data consolidating.xlsx and in this workbook you

will find three worksheets employee info department info And job info and what i would like you to do is to create a pivot table which consolidates all three of these tables so some things to remember as you're working through this exercise i want you to make sure that you name each of the tables with something meaningful and i want to make sure that you create relationships between the primary key Field within each table and if you remember the primary key field is basically the column of information that links all three of these tables so what

i'm going to do now is i'm going to show you my answer so this is what i want you to end up with now once you've created your pivot table the final thing i'd like you to do is to make sure that you have your pivot table in tabular Format you might also want to make sure that the sum of salary column is formatted correctly and that is it so see how you get on with that and i will see you in the next section hello everyone and welcome back to this course on advanced pivot

tables we're now down into section 5 where we're going to be taking a look at all of the options that you have for formatting Your pivot table so when we're talking about formatting we really mean the the look and feel of your pivot table and that might extend to things like background shading in your cells to formatting header rows or columns and applying some kind of color scheme to your pivot table now all of your pivot table options when it comes to styling you'll find located on the pivot table design Ribbon and it's really these

two groups that we're going to be focusing on in this module pivot table style options and also pivot table styles now these are fairly straightforward but there are a few things i'd just like to point out and highlight to you so that you know exactly what is happening when you're applying your pivot table styles so i'm currently clicked in my pivot table and i'm going to jump straight Up to this pivot table styles group and click on the more button to expand down my styles and you'll see that you have a whole host of different

styles which you can select they are divided into categories light medium and then we have dark at the bottom now if you want to see what each of these styles are going to look like excel helpfully has a live preview so when i hover over Any of these styles you can kind of get an idea as to what that is going to look like if you were to select it and apply it to your pivot table and again this is very much personal preference it might be that you have an overall color scheme that you

like to apply to all of your pivot tables or it might be that you're trying to work within your brand Colors for your company i'm going to speak a little bit more about that later in this module when we talk about creating our own pivot table styles now if i scroll down and just show you some of these darker ones as well so if you prefer a darker color scheme then you might find some of these suit you a little bit better but again as i said very much personal preference now one thing you need

to bear in mind is that prior to Selecting any of these styles if we just take a look at the options that we have in our pivot table style options you can see we have row headers column headers banded columns and banded rows so again this is related to styling now currently i have row headers and column headers selected if i was to deselect row headers you can see that that removes that bold styling from where it says 2018 and 2019. if i was to turn off column headers you can see it removes it from

the sales total countries years and then all of those country headings now the banded rows and banded columns if i turn on banded rows you can see exactly what that does now depending on which ones i've selected in the pivot table style options This then updates in the pivot table styles so now that i have banded rows turned on if i click the drop down you'll see that all of my pivot table styles are now in more of a banded rows format and if you want to you might find an occasion where banded columns is

what you want to utilize and again you can see in pivot table styles that's now updated depending on my selection in the pivot Table styles options now i'm going to select banded rows i'm going to click this drop down and i'm just going to select one of these at the top here so i'm going to go for this one just here light yellow pivot style light 19. so very quickly i've been able to apply some consistent formatting and all that means is that if i create another pivot table in this workbook then i can Very

easily just select the same style and i know that they're going to look exactly the same so it really helps when it comes to consistency between your pivot tables and how they look now another thing with these pivot table styles if you right click your mouse on any of them you'll see that you have a few other options in that right click menu so i can choose to apply and clear formatting apply and maintain formatting So that might be if i already have formatting applied to my pivot table and i just kind of want the

color scheme i can say apply and maintain formatting and that will keep everything as i wanted i can choose to duplicate this pivot table style and i can even set it as my default so that every time i create a new pivot table it's automatically going to create it in the style that i have set as my Default style and then finally at the bottom i can choose to add the gallery to the quick access toolbar so if i was to select that option you can see now up here on my quick access toolbar i

have a quick way of accessing all of those different pivot table styles now i mentioned at the beginning of this module that you can create your own styles Now why would you want to create your own style when you have so many to choose from from this pivot table styles group well it might be that you aren't particularly impressed with the colors that you have in these preset styles or it's more likely the case that possibly if you're doing something as part of an organization it might be that you have your own branding guidelines so

maybe All of the tables you create in excel have to be a certain color now if you have something like that or if you simply just want to create and play around with your own styles the option you need to select is right at the bottom here new pivot table style and this is where you can go in and completely customize and create your own style so i'll just show you a very basic Example because it's not particularly a complex concept to understand so first thing i need to do is name my new style so

i'm going to give this my initials and i'm going to call it custom style i then get to choose each element of the pivot table and i can choose what format i want to use so currently the top one here is whole table if i click on the format button i then have my font options border Options and my fill options so it might be i want the whole table to be a light green color i'm going to click on ok i can choose how i want my report filter labels to look my report filter

values if i want a first column stripe or a second column stripe i then have first row stripe and i'm actually going to format this one and i'm gonna make that A slightly darker gray color i can choose to format my header row so maybe i want the text in my header row to be italics so i can go to my font tab i can select italic and click on ok so as you scroll down you can see basically every single element of the pivot table you can format and apply a style it might also

be if i go back to whole Table and click on format then i want to add a border around the outside of the entire pivot table so from here i'm going to select outline click on ok and there we go now once i'm happy with that i'm going to click on the ok button and you'll see that it hasn't applied that new star to my pivot table but what it's done is if i click the drop down next to pivot table styles right at the top here i now have a Section called custom when i

hover over it it's my d a custom style and then when i click it it's going to apply now it might be a little bit of trial and error here so looking at this i can see that maybe i want to change the font color in some of these areas so that it stands out a little bit more or maybe i want to change the darkness Of these banded rows because they are a little bit dark it makes it a bit hard to read so if i now want to go in and change in the

element of this again in my pivot table styles i just need to right click on my custom style and select modify to take me back to that same screen so i'm going to go to first row stripe i'm going to go to format and i'm Actually going to change the color that i've selected for that to a much lighter gray color and click on ok ok again and you can see now that that is a lot easier to read now one other point i want to highlight here is any expectations that you might have when

it comes to utilizing any custom styles you've created in other workbooks let me show you what I mean so here i have a different pivot table it's located in a completely different workbook now if i click in this pivot table and go up to my design tab and click the drop down you can see that i don't have access to my custom style so that's one important point to note if you create your own custom style whilst you have access to it in all of the worksheets within the Workbook that you created it if you

open up a brand new pivot table you're not going to see that custom style there so how can you get around that well there is a little simple trick that you can do so the way that you would copy a custom pivot table style across to another workbook is reasonably simple now what i like to do is i like To have both of the workbooks open so the one that contains my custom style and also the worksheet that i'm trying to copy it to so i'm just going to open up my second workbook and all

you need to do is essentially drag and drop the worksheet that contains the style across to the new worksheet and you do that by holding down the Control key clicking on the pivot table worksheet and dragging it across to the other workbook and what that essentially does is it imports that pivot table style into that particular workbook so now if i want to apply it to my other pivot table just here i can click in the pivot table and there is my custom style i can then safely delete out what I copied and i'm still

left with that custom style in this particular workbook so that's a nice little workaround for copying styles to other workbooks one of the final things i want to show you here is if you want to delete your custom style you can right click and you have a delete option in there i'm going to say ok and that's going to put my Style back to how it was previously and i'm actually going to remove banded rows and i am going to select a different color for this particular pivot table the yellow pivot style 19. so hopefully

that's given you an idea of how you can utilize those really useful inbuilt pivot table styles how you can combine them with some of these pivot table style options and also how you can create your own custom styles With the colors that you want to use and also utilize any custom styles across different workbooks that's it for this module i will see you in the next one hello everyone and welcome back to this course on advanced pivot tables this is deb when we are down in section five where we're taking a look at formatting our

pivot tables and in the previous module we were focusing More on the design applying pivot tables styles and customizing styles and in this module we're going to switch our focus to custom number formatting something that in my experience tends to strike fear into the heart of most people and i will say that when i was first learning excel it was one of those things which i really struggled to get My head around until i found a basic rule that applies when it comes to custom formatting and i now find it a lot easier to not

only remember but also modify to suit exactly how i want my numbers in my pivot table or even regular numbers that aren't in a pivot table how i want those to look so let's start out by just reminding ourselves of some basic Pivot table number formatting so i'm in the sample sales data custom number formatting worksheet which you'll find in the course files folder if you'd like to follow along and currently looking at my values in my worksheet i can see that i simply have general formatting applied to these numbers so currently there's no currency

symbols i've got two decimal places showing i Have some negative numbers in here as well now this isn't exactly how i want this to look so maybe the person i'm giving this report to wants to see a currency symbol and they don't want any decimal places to be shown so let's do some basic formatting of our numbers now if you remember from one of the previous modules I told you that the way not to do this is to go up to the home ribbon and try and start changing it from number formatting it's only going

to change the current cell that you're clicked on a much better way to change everything is to right click and go down to value field settings and select number format from there so now what i can do is i can come in And i'm going to say accounting format i'm going to leave my currency symbol as us dollars but i'm going to take these decimal places down to zero and you can see in the preview above exactly what that's going to look like when i click ok so let's click ok and ok again and you

can see that that formatting has now been applied i have my currency symbol and i also have no decimal places Now one thing you'll also notice here is that my negative numbers are showing in brackets and we're going to deal with those in a moment because it might be that for your particular pivot table report you want them to maybe have a minus sign in front of them or maybe you want them to show in a completely different color so they really stand out in your pivot table and all of those things we can do

with custom number Formatting so what i'm going to do this time is i'm going to right click again go down to value field settings click on number format and this time in this category list i'm going to go down to custom and i'm going to start to specify exactly how i want my numbers to look and you'll see immediately in here there are so many different custom formats which are set up and of course you can go through and select any of these now as i said in My experience this is where people start to

get a little bit confused because they don't really understand what they're looking at in here we have just a whole range of things with hash symbols zeros brackets square brackets you know what does it all mean well the way that i like to remember this is just by remembering a very simple rule and let's start out with Something more basic i'm going to select this one just here and when i select something i can actually edit the custom formatting by clicking in this type field now the rule that you need to remember is that custom

formatting is essentially broken into four parts and what separates those four parts is the semicolon so you can see here we have This before the semicolon so this is the first part and this first part is basically how you want your positive numbers to look the second part just here is how you want your negative numbers to look now in this particular example that i've selected it's just showing those two parts so it's not giving me the third part Or the fourth part but if i was to scroll down and select another one that's a

little bit longer so something like this so this first part is how i want the positive numbers formatted this is how i want the negative numbers formatted the third part just here is how i want zero values to be shown so if i just have a zero in my pivot table what do i want that to look like i can Control that through this third part and the final part here is how i want text to be formatted so that's all you really need to remember is those four parts separated by semicolons positive values negative

zero values and text now you don't necessarily have to define all four every time you're applying custom Formatting so as we saw at the top here with this second example in this one if i was to select this i'm basically just formatting the positive numbers and the negative numbers now what i'm going to do here is i'm going to do something very simple and it's going back to those negative numbers that currently i have showing in brackets in my pivot table now what i really want to do here is i Want to keep my currency

symbols and i want to show the negative values in red with a minus so what i could do is select one of these custom formats and i'm going to get into a little bit later on exactly what these pound symbols and these zeros mean you can see here that basically if i was to choose this one my positive numbers are going to be Formatted with a thousand separator and my negative values are going to show in red so let's just select this and see what happens i'm going to say okay and okay again so it's

kind of done what i want it to do it's definitely made those negative numbers red but what i've lost are my currency symbols And i also don't have a minus sign in front of my negative values which is how i really wanted this to look so i might have to make some modifications to this particular custom format so i'm going to jump back into value settings and number format again i'm going to go to custom and it's showing me what i currently have applied and i'm going to start to make some changes just here so

what i want to do is i am going to go To my negative values part and i'm going to add a currency symbol just before that first hash sign and what i'm also going to do is just before we have red in brackets i'm going to add a minus symbol and i'm also going to add another dollar sign before the positive values and you can see just above what that's going to look like Now it might be that you want to go a stage further and even just remove the brackets from your negative numbers and

just have them showing in red so again i can come in here and i can modify how that's going to look if i now click on ok and click on ok again you can see that that is pretty much exactly what i wanted to achieve I have my currency symbols back i have my negative number showing in red and they also have a minus in front of them now what if at some stage i send this to my manager and he says well actually we want all of our negative values to appear in blue well

you can change the color that your negative values are displaying in very simply again let's right click go down to Value field settings and number format we're going to jump back into custom and all i need to do is change where it says red to a different color so it might be green it might be yellow but in this case i'm going to say blue click on ok click on ok again and very simply i've changed the color of those negative values now another thing i can see in here is That i do have some

zero values so for marchesa we don't have any data for a few of the countries in fact the united kingdom is the only place where the marchesa range is sold now we've seen how we can format these through pivot table options but i just want to show you another way that you can do it using custom formatting so again i'm going to right Click down to value field settings and into number format so this is what we have currently and if you remember i said the first part is for positive numbers the second part is

for negative so i'm gonna add the third part which deals with the formatting of zero values so remember to do that i need to add a semicolon and i can now type in the Format i want to use anytime there is a zero value in a cell and for this i'm just going to add some text and text needs to go in quote marks and i'm going to say no data click on ok click on ok again and like magic we now have no data listed in those zero value cells now if you're at all

concerned with what this will do for your calculations when you start adding text in I would say don't worry too much about that if i click on any of these cells that currently says no data if you look up in the formula bar you'll see that the underlying value is still sitting there in the background so according to excel that cell essentially still contains zero as the value all you're doing when you're applying formatting is really kind of Masking over the top so what i'm trying to say is essentially i could still use this cell

in a calculation and just to show you i'm just going to do something really quickly i'm going to say equals i'm going to select this cell which puts in this very long formula which we're going to talk about in later modules and all i'm going to say is plus 10 hit enter And the result i get is 10 because this value according to excel is still zero and so my formula still works all i've done is reference this cell which says 0 and added 10. now let's jump back into our custom formatting once again now

it's worth noting that when you do create your own piece of custom formatting it will be saved in the custom Formatting list so right at the bottom you can see there is the one that i've just created so that's quite useful if you want to reuse it again on a different pivot table it just means that you don't have to go in and start recreating your custom format all over again now when it comes to these symbols that we have so particularly the pound And the zeros the only thing you need to remember here is

really these are just placeholders for values and the difference between a hash symbol or the pound symbol and a zero is that the hash symbol is a variable placeholder whereas zero is fixed which is why things like zeros are generally used for decimal places so i just want to illustrate that a bit more by showing you an Example on a different worksheet so i'm going to come out of here and i'm going to jump across to my part numbers worksheet now this isn't any kind of pivot table but it's just a nice quick way of

demonstrating how fixed zeros work so what i have here are some item numbers and then i have some item names and you can see these item numbers we have 1 5 7 10 25 100 and 2 000. now it might be for consistency's sake i want all of the item numbers to be four characters long so a way that i can do that is that i can utilize custom formatting so i'm going to highlight my numbers i'm going to right click and because we're not in a pivot table this works slightly differently i need to

go down to format cells this time And it's going to take me to my number formatting i'm going to go to custom and what i'm going to do is in type i'm just going to do one two three four because remember zeros are fixed they're not variable and you'll see in the sample preview above when i apply this that first one is going to be zero zero zero one which is exactly what i want because i Want all of the item numbers to be four characters long and click on ok and very quickly i've been

able to apply that number formatting without hurting the underlying data if i'd done exactly the same so let's go back into format cells and custom if i'd done four pound symbols instead and clicked on ok i'm not going to get that same result because Pound symbols are variable so hopefully that kind of demonstrates to you a little bit what's going on there now the final thing i want to show you in here is how you can use custom formatting to combine text and values in your pivot table so despite the fact that i have my

negative numbers showing in blue with minus symbols they do stand out quite a bit maybe my manager has come to me and said Well i actually want the word loss next to the negative numbers so a mistake a lot of people make is that they'll come in here and they'll try and edit this up in the formula bar to add the word loss and you'll see that you're not able to do that so again this is where we would use custom formatting so the part of this custom formatting i want to edit is the negative

numbers So that is the second part so it's this little part just here i still want them to show in blue i still want them to have a minus symbol i still want them to have a currency symbol followed by the number but what i want to say is that after the number i want the word loss which i'm going to put in quote marks And i'm still going to keep my zero values showing as no data click on ok click on ok again and you'll see that the word loss has now been added to

those numbers but again remember once i'm clicked on this cell if you look in the formula bar it hasn't added the text loss so i can still perform calculations and this isn't going to upset any of my totals at the bottom It's just essentially putting that formatting on top of the number now if at any point when you are playing around with custom formatting and it can get quite complex if you want to just essentially jump back to how it was originally all you need to do again is go down into your number formatting and

you can just apply general formatting And that's going to take everything back to how it was originally so those are a few tips when it comes to using custom formats in your pivot tables in the next module we're going to do an exercise before moving on to section six so please join me for that hello everyone and welcome back to this course in advanced pivot tables we're now down to exercise four where we're going to practice the skills that We've learned in the formatting pivot table section of this course now you'll find the exercise file

in the exercise files folder and the one for this exercise is called hr data styles.xlsx and you'll see here we have a table of data and what i want you to do is take this table of data and create a pivot table and i want your Pivot table to end up looking something like this so a couple of key things to note here when you're constructing this pivot table once you've got your fields in the correct place what i want you to make sure is that the salaries are formatted correctly and i also want you

to go in and make sure that you have an n a or a zero showing where there are zero Values so you can see in my example i've used n a but you're more than welcome to use xero then once you've done that i'd like you to format your pivot table and what i'd like you to do is to create your own custom style and apply it to your pivot table so this is my answer this is an example of a table with a custom style that i've created applied Now you definitely don't have to

do exactly the same custom style that i've done here just make sure that you do change quite a few elements and then apply that custom style to your pivot table see how you get on with that and i will see you in the next section if you're not a subscriber click down below to subscribe so you get notified about similar videos We upload to get the course exercise files and follow along with this video click over there and click over there to watch more videos on youtube from simon says it