Pivot Tables Excel: Detailed Beginners Pivot Table Tutorial

subscribe and click the bell icon to turn on notifications we've made the files the instructor uses in this tutorial available for free just click the link below in the video details to get these so what exactly are pivot tables and why are they so useful well effectively pivot tables are an interactive way of quickly summarizing large amounts of data so what exactly do we mean by that well we live in a very data-driven world lots of us are collecting data from all different sources to enable us to make better business decisions and whilst we might be gathering all of this lovely data in a big old excel spreadsheet simply by looking at your data it doesn't particularly highlight to you key metrics issues successes failures or trends so we effectively need a way to take these large amounts of data and make sense of them and that is where pivot tables come in so let's take a look at an example i have a data set here if i press ctrl down arrow the last row is 14 412 so it's a reasonably sizable data set and this is sales data for different regions and different countries i can see the region the country the item type the sales channel so whether that was an offline or online sale i can see the order priority the order date the unique order id number the ship date and then i have the number of units sold the unit price the unit cost total revenue total cost and then finally total profit so this is basically transactional data now currently this is just sitting in an excel table in my worksheet and if you're not sure what an excel table is and why they're important we're definitely going to get on to that when we build our pivot tables but for now just know that this data is inside an excel table and simply by looking at this data it's quite hard for me to see the wood from the trees so for example if i wanted to know what the top 10 countries were in terms of total profit i can't really see that too clearly simply by looking at this data what if i want to see how many orders we've had that are high priority again quite difficult for me to see i could possibly use my filter drop-downs and eventually get to the answer that way but what's going to be much more efficient is by putting this information into a pivot table and the difference between a regular excel table and a pivot table is that pivot tables are dynamic so let me show you what i mean so this right here is a very basic pivot table that i've built based off of that data and what this pivot table is showing me are the top 10 countries by the number of units sold and currently i have this list organized alphabetically now if i just quickly do a sort on the units i'm going to say largest to smallest i can now see that croatia sold the most amount of units now if at this stage i decide that i want to do a different type of analysis on this data so maybe i'm like okay croatia is top of the pile here i want to take a look at what this looks like if we do sum of profit well the cool thing with pivot tables is that they are dynamic so simply by moving around some fields i can get to a different set of analysis so i'm going to get rid of units sold and this time i'm going to do total profit drop that into values and now i can see all of the countries by the sum of their total profit i can sort these largest to small list and i can see who the top of the pile is now i could add in other fields so if i wanted to see this analysis broken down in a different way i could maybe drop item type into columns and now i can see the different categories departments whatever you want to call them summarized by the country and again i can see the sum of the total profit if this is too overwhelming for me i might want to apply some filters and maybe just filter for the top five countries to make this data set a little bit more manageable now obviously we're going to go through all of these things i've just very quickly shown you there an overview of how pivot tables work but hopefully you're starting to get the idea that pivot tables are very different from something like this which is just a static excel table we can't switch this data around we can't pivot fields around so it makes it very difficult to analyze once we've got our data into a pivot table and we can pivot it in all different ways we could create more pivot tables and we can then start to create pivot charts based off of the pivot table data and this then opens up a whole world of visual analysis options because most people find it a lot easier to analyze and interpret data if you give them something visual and charts are fantastic for doing that type of thing so you can see here i have my pivot table data which i've actually got grouped and then i've created a pivot chart based off this data to show the units sold by sales channel 2014 to 2017 and i have a nice line chart underneath once we really start getting into our pivot tables and our pivot charts we might want to work up towards creating something like an interactive dashboard now we're not going to cover this in this beginners course but i would say if this is something that interests you we do have a really good interactive dashboards training course on simon says it and also an advanced pivot tables course where we cover creating dashboards but this is an example of the kind of thing that you can achieve a dashboard showing all of the key metrics that contains things like filters to allow you to interact with the data and really pull out of it the results that you're interested in so that is what a pivot table is a dynamic interactive way of summarizing large amounts of data and they're useful because they help us analyze large data sets in a clear and effective way so let's start out this first section by talking about how we can clean up data prior to analyzing it with a pivot table now if you've never heard of the term cleaning data it basically refers to the process in this case the different processes in excel that we can use to tidy up data sets make them consistent format them correctly and effectively present the data in a way that a pivot table can easily analyze and produce accurate results because the consequences of skipping over this step and there is always a tendency to download a data set and then immediately try and analyze it it's very important to run through this cleaning process every time you receive a new set of data this is of particular importance if you're downloading your data from a third party or an external source or maybe a database data doesn't always import into excel in the format that we're expecting or hoping so whilst the data that you're importing might look like it's all organized in a nice table in access when you import it into excel you might find that you have columns all over the place maybe you've got some strange formatting included in the data maybe there are blank rows blank cells that you need to take care of so that your pivot table is going to be accurate and what i'm going to do over the next few lessons is really run through the entire process of cleaning this data set so we're going to see many techniques in action that you can use on any data set it doesn't have to be this one specifically which should ensure that you end up with a really nice clean looking data set ready for analysis so if we take a quick look at the data that we're going to be cleaning this is in the sales analysis workbook and as we've said we have sales information for different regions and different countries now this is a rather contrived example i've simply messed up this data set so that we can clean it but what you'll notice immediately is that we have some inconsistencies in column b with regards to the case we have some words that are in italics some words that are in bold we have some fill colors in the cell background for some of these cells i can also see that we have a few random blank rows throughout our data and there might also be some duplicate entries i don't know without going through the entire data set now all of these things if you were to leave your data as it is now is going to make any analysis that you do inaccurate so that's why it's so important to clean your data first so now we understand why it's important to clean data let's move on to the next lesson and the subsequent lessons where we're going to go through the steps that you need to take in order to clean data in excel in this first section of the course we're going to be focusing on cleaning data and we've already discussed the reasons why it's so important to clean your data prior to putting it into a pivot table and analyzing it so let's dive straight into the first skill we need to learn and that is removing blank rows from our data set if you take a look at my data set on the screen you can see that i have a number of blank rows in my data and this is definitely something that can occur particularly if you're importing your data set in from another system or maybe another spreadsheet or downloading it off the web now why are blank rows a problem well for a couple of reasons firstly they make your data harder to read if you have these random blank rows running throughout it and secondly they're going to cause a little bit of an issue once you create your pivot table and just to illustrate that point i've quickly created a pivot table just based on the data how it is so you can see how blank rows affect that pivot table so if we jump across to sheet 1 take a look here now we are jumping ahead a little bit i haven't yet showed you how to create a pivot table but the thing i want to draw your attention to here is at the bottom of our pivot table we have an entry that says blank and this entry just here is basically those blank rows that it's picking up from the original source data so anything further that i do with this maybe if i decide to create a chart we're going to have this kind of blank field in there not really doing anything or being particularly useful it's just kind of getting in the way so a much better idea is to remove those blank rows from your data prior to analyzing now before we actually jump in and remove these blank rows there is one thing i want to do first and that is widen out some of these columns as you can see if we take a look at column a the region is kind of cut off there isn't enough room in that column to completely accommodate the text within it and the same thing for some of these other columns if you ever see these little hash marks or a fence as they're sometimes referred to this just shows you that there isn't enough room in that cell to accommodate the values so we want to deal with that straight away and it's a very simple process we just need to widen out these columns now we can auto fit the columns automatically to the values contained within each cell by selecting all of the columns i'm just going to do that by hovering my mouse above column a until i get that little black arrow pointing downwards click and drag across all of the columns that you want to widen and then all we need to do is take our mouse and we can hover over any of these column boundary lines and you'll see when you hover over you get that double headed arrow hover over any of them double click your mouse and it's going to widen out those columns to the widest item in the column so now you can see that everything also fits nicely into each column now that we've done that and our data is a little bit easier to read we can now go in and remove these blank rows now my data set is a fairly large data set so what i don't want to be doing is going through and removing them all manually you might think the best way to do this is just to select the row right click your mouse and select delete now you could do that it is going to remove the blank row but that's a fairly tedious process particularly if you have a data set as large as mine so we want to find a way to do this quickly fortunately excel has us covered on this we can either select all of the columns by using that same technique that we used previously or if you don't want to remove say this blank row just here that separates the title from the data you could choose to select just the entire data range and we can do that by clicking anywhere in the data and pressing the ctrl a shortcut key to select all once we have everything selected we want to go up to the home tab and all the way across to the editing group and click the drop down underneath find and select and for this we're going to use go to special and what go to special is going to allow us to do is basically find all of the blanks in the selected cell range so if we go through our options you can see one of them there is blanks so let's make sure we select that and click on ok now if you have a fairly large data set like i do that might take a couple of seconds but take a look at what's happened it has selected all of those blank rows now that i have them selected i can simply go back up to the home ribbon into the sales group click the drop down underneath delete and say delete sheet rows and like magic in a couple of clicks we've managed to remove all of those extraneous blank rows just to show you how that affects that pivot table let's jump back to sheet 1 and what i'm going to do here is quickly refresh this pivot table and you should see that where we have blank at the bottom that's going to disappear so i'm going to click refresh and refresh all and blank has now disappeared so that is how you can quickly delete all blank rows in your data source so now that we've removed all of the blank rows from our data set the next thing we want to check for is duplicates and if we find a duplicate we want to remove it and when it comes to duplicates the way you do this really depends on the type of duplicate that you're looking for for example you can see if we take a look in the sales channel so column d you can see that we have the words online and offline repeated so technically these could be classed as duplicates now do i want to remove this type of duplicate no i do not in this case i only want to remove a record if every single column is an exact duplicate of another column and again this is something that can sometimes happen particularly if you're importing your data sometimes your files can get corrupted or maybe it just comes across with additional duplicated rows so you want to make sure that you are checking for that because again this can cause problems when you go to create your pivot table so the duplicates i'm checking for are exact duplicates where every single piece of row information is technically duplicated so we have two of the same record unfortunately excel has a remove duplicates utility that makes this extremely easy so make sure that you're clicked somewhere within your data set up to the data tab and for this we want to go all the way across to the data tools group and this is the icon that you're looking for just here remove duplicates and you can see in the screen tip what it says delete duplicate rows from a sheet you can pick which columns should be checked for duplicate information so that last paragraph there is extremely important so let's click the icon and this is where we get a choice of all of the columns in our data set and we can essentially define which ones we want to check for duplicates in so if you were just interested in looking for duplicates in one particular column you could deselect everything except the column or columns that you're interested in now i want to check every single column because i'm looking for duplicated rows effectively where the data is exactly the same i want to ensure that i have the my data has headers checkbox checked and click on ok and it's as simple as that and take a look at the message that i'm getting so i can see here that excel has found four duplicate values and it's removed them so all i need to do now is click on ok and those duplicate rows have been removed the next thing we're going to tidy up in this worksheet is all of this weird formatting that we have applied so you can see that some of the cells in this worksheet have this kind of gray shading in the background some have this yellow highlighted shading and i can also see that some of the numbers here have come across in bold and also i have some italics a little bit further down so all of this gives the entire worksheet a very inconsistent feel and again this is something that you'll find frequently happens particularly if you import your data sometimes whatever formatting is applied in the system that you've imported the data from will apply in excel and a lot of the time it's not going to be the type of formatting that you want so having a quick way of being able to remove all of this formatting and just take your data back down to plain text and values is really important once you have that clean slate you can then go in and apply the appropriate formatting if you want to so let's get rid of this shading in the background and the bold and italic formatting once again we just need to click in our data set and press the ctrl a keyboard shortcut to highlight all of our data let's jump up to that home tab and this time we're going all the way across to the editing group and notice in here we have a button called clear and you can see it says when we hover over the screen tip says delete everything in the cell or remove just the formatting contents comments or hyperlinks when i click this drop down i have a number of different options so you can choose which one is most appropriate for your data set but let's just run through each one so you know what they do the first button here clear all that is literally going to delete everything out of your worksheet i don't want that so let's control zed to undo it let's click it again the next one is clear formats and this is going to clear only the formatting that is applied to the selected cells so this is going to be the one that we want to use it's going to remove that background shading remove any bold italics and just take it back to plain text it is worth noting that if you have number formatting applied to certain columns so for example the unit price column or the unit cost column if i had currency format applied to that which i don't currently at the moment just be aware that when you clear formats it's going to remove any number formatting you've applied as well so you might have to jump in and reapply it now this is why i like to do this step before applying number formatting to my columns so i'm not kind of doubling up on the work that i'm doing so let's leave this option for a moment because that is the one that we're going to use because i want to show you what these other ones do now clear contents this is going to clear the contents in the selected cell but it's not going to clear the formatting or any comments so if i click clear contents you can see what that leaves us with it's deleted all of the values and the text but it has still left the formatting and that could be really useful in certain situations let's control z to go back the next one clears comments and notes now i don't have any comments or notes in this data but if i did i could choose to clear them all in one go and then finally we have clear hyperlinks so again this is a great one if you've imported maybe some employee information where you have a list of employees and then you have a list of all their email addresses if those email addresses have imported as hyperlinks and you just want to remove all of the hyperlinks you can choose clear hyperlinks and it will leave the email address but it just won't be a link any longer and again that can be really useful in certain situations so that is what all of those different clear options do let's now select the one that we want which is clear formats and immediately you can see the effect that that has had our data looks a lot more consistent so very quickly we've managed to remove all of that background shading bold italics any formatting that you have applied you can remove by using that clear formats button a great quick way to tidy up your data now that we've gone in and cleared all of that unwanted formatting it's time for us to go into our data and start to apply the formatting that we want and again this is a really important step because it's going to make your data a lot easier to read now the name number formatting is a little bit misleading but because it's not just columns that contain numbers that you can format you can basically format any column so if we start with column a let's take a look at what we have in column a what i have in here is text it's not numbers it's not percentages it's not currencies it is simply text now if i go up to the home tab and into the number group this is where you'll find all of your number formatting options and you can see here we have a drop down and that opens up all of the different number formats that we can apply so we have the 10 most common ones if you want to take a look at some of the other types of number formatting that you have available you can click more number formats at the bottom which is going to open up the format cells dialog box now just as a note there is a keyboard shortcut to quickly jump you into this screen and that is control one that's going to take you to that format cells window and which one of these you apply to each column is really determined by the type of information that you have in that column so for example in column a i have text so i'm probably going to want to use the text formatting so before we do that let's make sure that we have actually selected column a and i'm going to make this a little bit of a quicker process because i can see that columns b c d and e are also text so i'm going to select all of these columns i'm going to press ctrl 1 and let's choose text and click on ok so now when i'm clicked in any of these columns if we take a look in the number group you can see it's telling me that we have text formatting applied let's take a look at some of these other columns now column f this is a little bit of a strange one because we technically have two pieces of information combined into one cell now what we're going to do in a later lesson is i'm going to show you how you can split up this information so that you just have order date in one column and id in the next so for the time being i'm going to leave this column because we're going to format it later on once we've split it up let's move on to ship date now i can see here that this is supposed to be displaying as a date but instead of a day i have this weird number and again this is something you'll see fairly frequently in excel you might expect a date to be in the cell but instead you're getting a number so what exactly is this number well this is what you'll see when you don't have date format applied to a column that contains dates and if you take a look up at the number group you can see currently i have general formatting applied and general formatting is kind of the default it has no specific format so excel doesn't know that i want these numbers formatted as a date so where are these numbers coming from in the context of excel all dates are numbers and the beginning of time again according to excel was the first of january 1900. so that would be day number one and then each day from that point has been assigned a number so if i was to go to let's just click in a random cell over here if i was to type in january the 2nd 1900 and hit enter if i apply general formatting to that it gives me two because it's the second day according to excel the second of january 1900. so all of these numbers in here represent dates but if you don't have date formatting applied it's going to display the actual numeric value the number as opposed to the date so let's deal with that straight away i'm going to select the entire column and this time we're going to go up to our menu and we have a choice here of short date or long date and short date effectively looks like that and long date looks like that so it really depends how you want your dates to look now it is also worth noting that if you want to get quite specific about how your dates look you can go into more number formats and choose to create a custom format for your cells now custom formatting is more of an advanced topic so i'm not going to cover it but i wanted to mention it in case it is something that you need to do and i would encourage you to do a little bit further reading on how to create your own custom date formats for our example we're just going to keep it on short date you can see that that column is a bit too wide so i'm going to double click to auto fit it so that it looks a little bit neater let's move on to the next columns so now we have the number of units sold so this is really just a number so i can highlight the column i'm going to go up to the drop-down and i'm going to set this to number formatting now you can see by default it's given me two decimal places now in this case i don't actually want that this is just the number of units sold so really everything after that decimal place is kind of irrelevant there are a couple of different ways that you can remove decimals from cells and that is by using the increased decimal or the decrease decimal buttons in the number group alternatively again if you press ctrl 1 to jump into format cells you can see here we can take those decimal places down to zero and click on ok and now we're just left with the whole number now the remaining columns in this spreadsheet unit price unit cost total revenue total cost and total profit these all need to be displayed with a currency symbol and for this example i'm going to be working in us dollars so i'm going to select all of these columns i'm going to press ctrl 1 to go into format cells and for this i'm really going to choose between either currency formatting or accounting formatting both of these formats allow me to add a currency symbol and effectively change these into monetary values now when it comes to which one you choose currency or accounting there is a very important difference so the accounting format is basically been created for the accounting industry what it's going to do is it's going to add a currency symbol of your choice and you can go through the list and select the one that's appropriate for your data we can choose how many decimal places we want i'm going to leave mine at 2 but as you can see underneath accounting formatting lines up the currency symbols and decimal points in a column and the reason why this is called accounting formatting is because in the accounting industry where they have to read down long lists of numbers having the currency symbol lined up and the decimal point lined up in a column really adds to the readability of the data in the column whereas currency format will do a similar thing i can select how many decimal places i can select my currency symbol but it doesn't align decimal points in a column and you'll also find that the currency symbol is kind of pushed up right next to the values so let me show you the difference let's apply currency that is what that looks like now my values are all fairly uniform in here so for the most part my decimal points are lined up but take a look at the currency symbol it's right next to that value let's take a look at what happens if we change it to accounting format so ctrl 1 accounting i'm going to leave it two decimal places and click on ok and we get a slight difference here so we have all of our currency symbols are aligned to the left hand side of the cell they're all lined up and so are those decimal places so many times this is a lot easier to read and i would say that i use accounting format a lot more than i use currency format but the choice is up to you effectively they both do a similar thing so there we go we have now applied the correct formatting to each column in our data and we're ready to move on to the next stage the next thing that we're going to take a look at is how to tidy up text by using text functions and again this is something that you might find yourself doing reasonably frequently now if i take a look at my data set i can see most of this looks fairly decent everything's starting to look quite consistent but i can see that i have a few problems going on in column b so if we take a look at these countries can you see we have a lot of inconsistencies in the way this column looks for example i have some of the countries in uppercase some of them in lowercase and some of them in what we call proper case where the first letter of each word is capitalized i also have some weird spacing issues going on so some of these have quite a bit of blank space at the beginning some of them have blank spaces in the middle and what i ideally want to do here is have everything looking nice and consistent so in this lesson i'm going to show you how you can basically use some very simple formulas in excel to change the case of values in your cells now i'm just going to be working on one particular column because everything else appears to be okay but if you have multiple columns that you need to do this for then just repeat this process for the additional columns now there's a number of different ways that you can do this the way that i like to do it is i like to insert what i call a helper column and i'm going to insert it next to the column i essentially want to change the case of so let's go up and select column c right click and say insert and that's going to insert me a little helper column and i'll just name it helper so we don't get confused and this is basically where i'm going to construct my formula and then we'll copy and paste over the top once we're done now if you've never used excel formulas before i would say don't be intimidated don't be scared the ones we're going to use in this lesson are extremely simple now for those of you who have never used an excel formula there are over 500 different formulas contained within excel and you will find all of your formulas on the formulas tab and they're all in here in the function library group categorized into different groups so it really depends what type of formula that you're looking for now the formulas that we're going to be using here is to change the case so these are considered text formulas and you can see all of the different formulas that we have to choose from and i'm going to demonstrate three of these to you now when you're ready to construct a formula the first thing you need to do is type equals into the cells so that lets excel know that you want to construct a formula now what if i wanted to change the case of all of the text in column b to uppercase well i can use a function called upper now in excel you type your function and then you need to open a bracket or a parentheses and what you'll see underneath with the screen tip is it's going to show you what we call the function arguments and this is just some helper information so you know what excel requires in order for this formula to work so i can see here that for this upper formula it's telling me that i need to provide the text that i want to change to uppercase so all i need to do here is select the cell that contains the text in this case b4 and close off my bracket you must always close off as many brackets as you open and that is basically it now if i hit enter look what happens it doesn't apply the formula it's just showing me the text equals upper b4 now why is that well if we go back to the home tab take a look in the number group i can see that basically when i've inserted this column it's taken on the number formatting of the columns that surround it so it thinks that this is text in this column and so it's showing me the text that i've typed now that isn't correct so i want to select the column and i'm just going to put this back to general formatting let's delete that out and try the formula again so we're going to type in equals upper open bracket let's select our cell b4 hit enter and now that's looking a lot better so i've managed to change very quickly cell b4 to uppercase now in this scenario cell b4 was already in uppercase but what i can do now is copy this formula down the entire rest of this data and it will change all of the countries to uppercase now how can we do that quickly well we can use our autofill handle so if you take a look at the cell that we currently have highlighted in the bottom right hand corner we have a very tiny green square and if i hover my mouse over that green square you can see that my cursor changes to a small black cross all i need to do to copy this formula down is double click and you can see it's going to change everything to uppercase now it's not going to do anything about this weird spacing we're going to deal with that in the next lesson but if you want to quickly change the case that is what you would do i'm going to control z out of that because i don't actually want these to show in uppercase let's double click in the cell and go in and edit our formula what if i want to change everything to lowercase we can probably imagine what's coming here all i need to do is change this from upper to lower we have the same argument which is text so let's just hit enter and once again i can double click to fill that down and everything is changed to lowercase now again this isn't what i want so let's control zed what i actually want here is to convert everything into what we call proper case where the first letter of every word is capitalized so as you can imagine we can just edit this formula and use the proper function now this time i'm going to edit the formula up in the formula bar just above because it is a little bit easier to see so i'm going to delete out where we have lower and i'm going to change it to proper the argument is exactly the same we just need to provide the piece of text let's hit enter and then once again i can double click to copy that down the rest of my rows so now this is looking a lot more consistent with regards to the case of this text and i would advise that you do this on any columns that you see that have inconsistencies so now that we've changed the case we effectively have two columns displaying the same information one of them is the what we call the dirty data and one of them is the clean data so you might think that what you can do here is simply go in and delete out column b but take a look what happens if i right click and try and delete this column i'm going to get a whole bunch of reference errors in these cells now why is that well let's control z to go back and let's check out our formula i'm going to double click you can see that our formulas refer to text in column b so if i delete column b excel has nothing to refer to so how are we going to get around this problem we want to keep basically what we see here but we don't want to have this formula underneath well this is where we can use paste special in excel to just paste the values over the top and it's going to throw away those underlying formulas so what i'm going to do here is i'm basically going to cut everything i have in column c and then i'm going to use paste special and paste only the values over the top in column b now for this i don't want to select the whole column because if i cut this column and then paste it over here because i have a cell at the top that has blue shading in it's basically going to apply blue shading in column b as well which i don't want so i just literally won all of this text i don't need the heading row so i'm going to click on cell c4 and i'm going to use a shortcut key which is going to allow me to select everything from this point down and that shortcut key is ctrl shift down arrow so now i'm going to press ctrl c to copy i'm going to jump all the way back up to the top of my data control up arrow and i'm going to select cell b4 because this is where i want to paste it so let's go to the home tab click the lower half of the paste button and you'll see that we have something here called paste values and that essentially will just paste what you see and it will throw away any underlying formulas so let's click paste values it's pasted those across and i can now delete out column c and i'm not getting any errors so changing the case using formulas and also using that paste special values trick is going to be invaluable to you when you're trying to tidy up data in excel so now that we've changed the case and we have a certain level of consistency in column b it's time to deal with these erroneous spaces that we also have in this column if you take a look at for example this first one just here where we have chad you can see that we have a number of blank spaces just before the word we have blank spaces in between czech and republic we have some blank spaces before haiti algeria things like that and again if these aren't removed from your data it's going to mean that any pivot table you create is going to be less accurate and be harder for people to read so it's always a good idea to take a look at your data and remove any weird spaces and i would say that even when i'm dealing with a data set where everything looks consistent so for example if all of my data set looked like the data in column a where i can't really see any weird spaces i would still apply this next technique that i'm going to show you to the column and the reason why is that sometimes again particularly when you're importing data from another system there is erroneous spaces in your data you just can't see them so you might have three blank spaces right at the end like that to the naked eye it looks like there's no weird spacing but if you actually double click in the cell you can see your cursor is all the way over here so technically you do have spaces at the end so we want to have a way of quickly being able to ensure that we don't have any of these issues and again for this we're going to use another excel text formula called trim so for this i'm going to add in another helper column and once again if we just take a look at the formatting we have applied again it's called text so i'm very quickly going to change that to general formatting so that we don't get any issues let's call this helper as well and trim is as you would expect you just type in equals trim notice underneath as i type that in excel is trying to help me by showing me all of the functions that match what i've typed and this is called intellisense in excel and it's really helpful if you're looking for a specific function but you're not exactly sure what it's called and the beauty of this is if i start to type a function so tri it comes up underneath with trim i can see that it's highlighted in blue so to select it all i need to do is press the tab key it will complete the function name and add in that first parentheses for me and this function is very similar to the proper upper and lower in that it only has one argument that we need to provide the formula with and that is the text that we want to trim so again we're going to trim b4 and close the bracket now i could hit enter here and this is another little trick that i would encourage you to get in the habit of doing whenever you hit enter your cursor is going to move down to the next cell now in this case i want to use the autofill handle in the cell above so it means i kind of have to click back up here in order to access that auto fill handle now a much better way of doing this is if you finish typing in your formula and you want to remain in the same cell press ctrl enter instead and it's going to keep you in that cell now i can use my auto fill handle double click to copy that formula down and you can see that it's removed all of that weird spacing from these cells now in addition to running this trim to remove erroneous spaces there is another excel function you can use which will remove non-printing characters and you'll notice them because they appear as kind of tiny little square boxes randomly in your cells that is what we class as non-printing characters it might be that the data set that you've imported had some kind of formatting or maybe some kind of character which excel can't display so instead you get these little square boxes everywhere if you do find that you have something like that there is another excel formula that you can use which will remove them and that formula is called clean and this leads me on quite nicely to talking about how you can combine multiple formulas in one cell so instead of adding another helper column and running clean on column b what i could do here is utilize the helper column that i already have let's double click on the first one and i could combine clean with trim and effectively run both formulas at the same time now you'll notice that i don't actually have any non-printing characters but it doesn't matter too much as long as you get the idea so what i'm going to do here is i'm going to edit the formula and actually let's do this up in the formula bar as it's a little bit easier to see so what i can do here is type in clean open a bracket and you can see that the argument that we have for clean is text so again that's going to be cell b4 now the text in this case is actually already being generated by that trim function we have trim b4 so excel knows which cell we're talking about so all i need to do is go to the end of the function and the only thing we need to add here is a closing bracket why do we need to add that well we must always close off as many opening brackets or parentheses as we open now the cool thing about excel is that if you don't add the correct amount of brackets at the end excel now has a feature that will pop up and say you haven't added enough brackets would you like me to fix this for you so if you just click ok it will add in the relevant number of brackets but i think we've got this right so let's press ctrl enter so that we remain within the same cell and then of course we can use our autofill handle to copy that down so very simple to combine formulas now if you remember in the previous example where we were changing the case we use the proper function as well so instead of doing all of this separately i could even add proper to this formula and basically change the case remove non-printing characters and remove those erroneous spaces all in one go so let's see how that works let's just for this example change everything back to uppercase i'm going to go up and edit the formula in the formula bar and i'm going to use the upper formula let's open brackets again our only argument is text that's already being generated by these two functions so all we need to do is add another bracket on the end hit enter and then i can use the autofill handle to copy down and effectively i've applied three different cleaning techniques in one formula pretty cool and a great way to save time and it just means you're not having to create additional helper columns each time you want to do multiple things now i'm going to change this back to proper hit enter and copy down remember if you have a column that uses formulas as we do in column c and you want to delete out the column that contains all of the mistakes remember because we're referring to column b in this formula we can't just delete the column so we need to do our paste special trick again so i'm going to select ctrl shift down arrow to select all of that data control c to copy ctrl up arrow to jump back to the top let's click in column b go to our paste button and paste just the values and then now i can delete out column c and i have a perfectly cleaned and consistent looking column b something else that you can use the clean function for not only for removing non-printing characters but you can also use it to remove manual line breaks so what do i mean by that well if we take a look at column a on first glance everything looks fairly consistent i've got the names of the different regions but look what happens if i double click on row 9 where we have sub-saharan africa take a look at that the word africa is actually on another line so somebody has put in here a manual line break and if i click somewhere else on the worksheet you can actually see that as it is we have sub-saharan we then have a line break and then we have africa and if i go through this column are probably going to find that i have that reasonably consistently throughout this data now i don't want a line break in there i want to have just all of the regions listed out in one cell on one line so this is something else i can use the clean function for so very quickly let's do that let's insert a helper cell i'm going to change the formatting of this column let's put that back to general and we're going to use the clean function let's select our text a4 close the bracket and now if i double click to copy down you should find that sub-saharan africa is now all on one line so now that we have those line breaks removed we want to get rid of column a now remember because the formulas in column b refer to column a we can't just go in and delete column a because we're going to get some reference errors so we're going to use our little paste special trick once again so let's select cell b4 i'm going to press ctrl shift down arrow to select all of the data control c to copy control up arrow to jump to the top and now we're going to paste those values directly over what we have in column a so up to paste down to paste values and there we go now we can safely delete out column b and we have a perfectly formatted column a without any of those line breaks so now our data is really getting there but there's still a couple of things that we need to do prior to analyzing it with a pivot table now in this lesson i want to focus on column f now what you'll notice about column f is that we basically have two different types of data combined into one column we have the order date which currently is an unformatted date which is why we're seeing that number and then we have an order id which is just a simple number now ideally we want to have these two pieces of information in separate columns we want one column for the order date and one column for the order id so this is going to require us to break up the data that we have in this column so what we're going to do in this lesson is i'm going to show you some techniques you can use to split up data in cells and also do the reverse of that if you ever need to join data together so before we get on to doing it on our data set let's open up a different worksheet and i'll show you a few examples so this worksheet that we're going to use here is available to download in the course files folder and i'm going to show you three different examples two examples of how to split data and one example of merging data and then you can basically choose which one you want to use that's most appropriate for your data and the tasks that you're trying to accomplish so let's start out with this first example at the top here text to columns now text to columns is really useful if you have lots of data combined into one column that you want to break up and you'll find that if you ever import data from maybe a text file or some kind of notepad file quite often it will come across in a format that looks something similar to this so effectively what we have here is the employee name then a comma then their job title then another comma then their department and this in its current format is pretty difficult to read it would be much better if we could break it all up have the name in the first column the job title in the second and the department in the third so one way that we can break this data up is by using text to columns so the first thing i'm going to need to do here is select all of the data that i want to split we're then going to go up to that data tab and in the data tools group there we have a big button called text to columns and you can see as i hover over it the screen tip tells us what this is going to do it's going to split a single column of text into multiple columns for example you can separate a column of full names into separate first and last name columns you can also choose how to split it up fixed width or split at each comma period or other character so let's click text to columns now what this will do is it's going to open up the convert text to columns wizard so it kind of walks you through the process of breaking up your data now the first thing we need to choose here is the file type that best describes our data now you can see by default it's selected delimited now what does that actually mean well it means how your fields are separated so if i take a look at my data i can see each field the name the job title and the department are separated with commas and basically what this will do is it will look for all of the commas and split the data wherever it finds one so in this case this is going to be the option that i want to use so let's keep that selected and click on next we then need to tell the wizard what delimiter we're using and we have a choice of all of these different types of delimiter so if instead of a comma we had our fields separated with a tab i could choose that maybe a semicolon a comma which is what we actually have a space or we have an option for other so if your data is using some other kind of delimiter you can select other and specify the delimiter in this field now we're using a comma so i'm going to deselect tab and choose comma now when i click this take a look at the data preview underneath you can see it's going to give me a quick preview of how my data is going to be split up it's going to be split up wherever it finds a comma and i can see that that looks absolutely perfect let's click on next now there's a couple of things i can do in here the first one is i can choose my column data format so depending on what type of data i have in here i can select the column and we do that simply by just clicking on it until it's highlighted and then i could choose text from here if i wanted to now i will say this step isn't entirely necessary you can just keep it on general format the most important thing here is the destination so where do we want a new broken up text to appear well i don't want it to appear in b8 which is what we currently have selected so let's remove that because i want it to appear in this little table here so i'm going to select d9 which is effectively where the first entry is going to be i can now click on finish and excel is telling me that there's already data here because it's recognizing that i have some column headings just above now that's absolutely fine in this case i'm going to click on ok to replace it and would you take a look at that we have perfectly split up data nice and straightforward let's now take a look at another way that we can break up data and this is really one of those little magic tricks in excel it's a feature that i love and use so often so with the release of excel i think it was 2013 it came a new little feature called flash fill and this makes breaking up your data or even combining data together so much quicker and easier and i would say most of the time these days i tend to use flash fill a lot more than i use text to columns so how does flash fill work well you can see underneath here i have a list of names now it might be that i have these combined into one column that just says name at the top and what i want to do is i want to break up this into first name and last name now i could use text to columns like we just did and specify the delimiter as a space but a quicker way of doing it is to use flash fill so if i click in cell c20 if i type in how i want the first one to look i'm going to press ctrl enter to leave me in the cell and if we go to the data tab in the data tools group you'll see that we have a button just here called flash fill and this does also have a keyboard shortcut of control e to make it even quicker now check out what happens when i click on flash fill it basically fills down the pattern so it recognizes that in the cell the pattern that i'm looking for is i just want the first name and it basically copies that pattern down to the other cells so easy and so quick now there is another way that we can do this if we go to last name this time i'm going to type in the first one go to the cell underneath and instead of clicking the flash fill button if i start to type this second last name you can see it kind of grays down the rest of those surnames if it all looks good i just need to hit enter and it's going to input those into the cell so a super quick way of breaking up your data now we could also do this in the reverse so if i now wanted to basically have the full name let's just copy across this formatting to make everything nice and neat i could do the reverse of this so i'm going to say i want deb ashby in here ctrl enter and this time i'm going to use the keyboard shortcut for flash fill which is control e and you can see that that copies that pattern down so flash fill can be used to break up your data or combine data together a really useful little tool now the final technique i'm going to show you here is using an excel function to combine data together and this way is a little bit more flexible if you want to add additional things into your combined data so effectively what i have here is i have my list of names and i have those people's job titles and i want to combine these together in a cell so it shows their name and their job title but what i want is to have these separated by a hyphen so effectively i want it to look something like this so if we go for this first one i wanted to say deb ashby hyphen i t trainer now the easiest way for me to do this is to use an excel function called concat now in slightly older versions of excel it was called concatenate these days you're going to find the concat function so select whichever one is available in your version of excel so how does concat work well let's click in cell e30 i'm going to type in equals because we are typing a formula i'm going to start to type concat and you can see there it's at the top of the list also notice that further down the list i have concatenate but it has a little warning symbol on it then it's basically telling me that that is a function that i can still use in this version but that it is an old version of the function the one that we want is the top one because it's highlighted i can press tab to select and put in that bracket now the arguments for this function is basically we just need to select the text that we want to join together so my first argument is text one so my first piece of text is what we have in cell b30 now if i press comma it's going to ask me for text 2. so if i now click c30 for the job title and close the bracket if i hit enter it's going to look like that so there's no space and there's no dash so i need to jump in double click to edit the formula and add something else in here so after the first piece of text text 1 i want to add space dash space and because this is effectively text that we're adding to a formula text always needs to go within quote marks so i'm going to add a quote mark space dash space close quote mark comma so effectively i have three pieces of text here b30 which is my name text two is that hyphen and then text three is the job title let's hit enter and that looks perfect i can now use my autofill handle just to copy that down so three different techniques there which are really good for merging and splitting data so with all that said let's jump back to our actual data set and use some of these on that column f so here we need to split up order date and id so the first thing i'm going to do is i'm going to add two new columns now to do this i can highlight two columns right click and select insert and it's going to give me two columns both of these have a general format which is absolutely perfect and i'm going to add title of order date to this one and order i d so now effectively i could use text to columns to break up this data or i could use flash fill and because it's a little bit quicker i'm actually going to use flash fill so for the order date currently we have this weird number we're going to change that in a moment but i'm going to type in the first one so 40570 i'm going to press ctrl enter and then if we go to the data tab i'm going to click the flash fill button and it's going to fill all of those order dates down like magic it is so quick let's do the same thing for order id now i'm going to make this a little bit quicker on myself just by simply double clicking highlighting the text ctrl c to copy i'm going to escape out of there and just paste that in and this time i'm going to use my shortcut key for flash fill control e and that's going to copy all of those down as well and because we're not using formulas in either of these columns i can safely right click and delete out column f the final thing we need to do here is change the formatting on the order date so currently it's displaying in i believe that is general format yes it is let's select the column go to our drop down and i'm going to change this to short date and now we're looking absolutely perfect in this very quick lesson i just want to show you how to deal with numbers in your worksheet that have been stored as text and again this is something that can happen particularly when you're importing your data so let me just show you a quick example i'm going to scroll across just to a blank column outside of our data now if i have a column such as column p and it's formatted as text which it is just here if i then try to type a number into this cell so let's just say one two three four five and hit enter you can see a couple of things here firstly i have that little green triangle in the top corner which denotes that there is an issue or some kind of problem with this cell and also notice that the number is aligned to the left hand side of the cell and in general anything that's aligned to the left is text whereas numbers are always aligned to the right so i can see that this has incorrect formatting so if you import a data set and you find that you have columns where you've got numbers they're all aligned to the left and they have these little green triangles then you're probably going to want to deal with that and convert them back to numbers so i'm just going to auto fill this down just to add a few more numbers in you can see they all have these weird green triangles now there are a couple of different ways that we can deal with columns that look like this so if i select all of these numbers notice at the top i have this little warning symbol now if i click it it's going to give me an idea as to what this little error message or what this warning triangle is trying to tell me and you can see at the top here it's telling me the number is stored as text so something super simple that i can do is simply highlight all of the numbers and go to convert to number immediately that error disappears and you can see that those numbers are now aligned to the right hand side of the cell now there is another way that i could deal with this so let's control z to go back a stage i could also use a formula to convert everything back to a number and the formula here that we want to use is simply called value let's open the bracket select the cell we want to convert hit enter and we get exactly the same result so you can utilize either of these methods in your spreadsheet and make sure that all of your numbers are being stored in the correct format so our data is now starting to look pretty good but there are still a couple of final steps that i want to take prior to putting this into a pivot table and in this lesson i'm going to show you how you can utilize the find and replace utility now this might be something that you've used before in excel it's a fairly popular command and what it allows you to do is basically find a specific text string or piece of data in your spreadsheet and replace it with something else so this is great if you need to change a lot of pieces of information in one go so the example we're going to use here is column b where we have all of these country names now you'll notice that some of these country names such as this one here the democratic republic of the congo is quite a long name now you might want to keep this as it is or you might want to convert it to a shorter version of its name the reason why you might want to do that will become quite clear when we start creating things like charts if we have the country listed on an axis this is quite a lot of text to try and fit beneath the bar of the chart so it could be beneficial for us going forward to make these names a little bit smaller so instead of democratic republic of the congo i might want to replace every instance of that with drc the same thing for the united states of america i might want to find that in my spreadsheet and replace it with usa so let's do exactly that if you're just looking for data in one specific column you can highlight the column before you choose to find and replace if you're looking for something in the entire data set you can just click anywhere in the data set and it will look through all of the columns now in this example i'm just focusing on column b so i'm going to select it and what i can do here is either go up to the home tab go across to the editing group and select find and select and then replace or a much quicker way of doing this is just to press control f keyboard shortcut and then choose the replace tab now one thing with this find and replace dialog box is that it holds in it the last find that you did so you can see here i searched for one two three four five so i still have that listed in there so i'm going to delete that out all i need to do now is specify what i'm looking for and what i want to replace it with so i'm going to start out with the democratic republic [Music] of the congo and i want to replace this with drc now let's just expand our options just here now notice that i was very careful when i typed in democratic republic of the congo and i was being quite careful to make sure that i match it to the case that i can see in the spreadsheet now i didn't really need to be that careful because if we take a look at my options you can see here i don't have match case selected so if i typed in let's say the word of in lowercase it would still find what i'm looking for because i'm not being very specific about matching the case you can also see here that i'm searching within this sheet i'm searching by rows and i'm also looking for this text contained within formulas so all i really need to click here is replace all and you can see here it's made 60 replacements and if i look at my worksheet in cell b7 you can see that the democratic republic of the congo has now been replaced with drc i'm going to do exactly the same for united states of america and i'm going to change that to usa replace all 58 replacements and let's do a final one now just to show you how the case doesn't matter i'm going to type united kingdom all in capital letters and i want to replace this with uk and let's click on replace all it's made 72 replacements and click on close so an extremely quick and useful way to find specific items and replace them with something else all in one go now again it is worth noting if you are a little bit of a formula lover you can do the same thing using formulas so let me show you this very quickly i'm just going to insert a brand new column and let's make sure that we format that as general if i wanted to do exactly the same thing using a formula i could use the substitute formula so if i type in equals and start to type in substitute you can see as soon as i type sub it comes up underneath so i can press my tab key and here i can specify where the text is located the old text and then the new text so my text is going to be located in cell b4 comma to move on to the next argument so let's kind of do the reverse of what we've just done i want to change uk back to united kingdom so my old text is uk comma my new text is united kingdom [Music] close the bracket hit enter and let's double click to copy this down so now wherever we have uk you can see that that's been replaced with united kingdom now remember column c now contains formulas that refer to column b so if you want to utilize this substituted data you're going to need to do the paste special paste values trick again now i'm not going to do that i'm just going to delete out column c because i'm actually perfectly happy having uk in this column but that is how you can quickly find and replace data using the dialog box and also using the substitute formula the final step i'm going to take to clean this data is something you should be doing on all of your worksheets and that is running a spell check again this is so important particularly if you're going to analyze your data because remember if something is spelt wrong the pivot table is going to see that as a completely separate item so for example if in sales channel if i've accidentally spelt one of these wrong so maybe i have an extra e on the end here when i analyze this data the pivot table is going to see this as being completely different to this so we want to make sure that everything is spelt correctly now i'm sure you're probably used to spell checking your worksheets but let's just very quickly run through it you'll find your spell checker on the review tab and it's the first icon here in the proofing group and you'll see that there is a keyboard shortcut for this of f7 so that's nice and easy to invoke whenever you need it so let's press f7 and that's going to pull up the spell checker now first thing to check is the language that you're using now as you can probably tell from my accent i'm actually located in the united kingdom but because a lot of the work that i do is for american clients i tend to have excel set to english united states and believe it or not there is a lot of difference with the way that we spell words we all speak english but sometimes we like to add extra use in words so the first thing to check here is the dictionary language that you're using so if you want to change this to something else make sure you click that drop down and then select the correct language so now you can see it's running through my spreadsheet now something to note here the spell check will start wherever you're clicked in your worksheet so if i was clicked halfway down my data set it's going to start from that point it's not going to start from the top automatically it will loop around once it gets to the end but just be aware of that so i can see here the first word it's found is cancelled and i can see that i have too many ls in this particular word underneath i have some suggestions and i can see that we have the correct spelling just here and i can choose to ignore ignore all i can change or change all or i can add to the dictionary now add to dictionary is a really important thing to note occasionally when you're spell checking the spell check will come across a word that it doesn't necessarily recognize and i find that this happens a lot if you have company names or people names or brand names things like that those things aren't in the standard english dictionary so excel considers it to be a spelling error because it doesn't recognize the word now if the word is spelt correctly and you don't want excel to keep picking up this word as an error you can choose to add it to your dictionary so it will ignore it next time now in this case i want to change this word cancelled and i'm going to say change all because if i have this word anywhere else in this spreadsheet i want to change it to the correct spelling so let's say change all it's now moved on to the next spelling error that it's found and if i have a look i can see where we have baby food yes i have an additional t at the end there it should just say baby so i'm going to make sure i've got it selected in the suggestions and click change all now notice here it's actually got to the end of my data because i wasn't clicked at the top so it's asking me if i want to continue checking at the beginning of the sheet so i'm going to say yes so i don't miss out on anything and a good job i did because i have another spelling error right at the start i've spelt beverages wrong i'm going to make sure i select it change all and now my spell check is complete all i need to do is click ok and what i'm left with is a completely error-free worksheet hi guys and welcome back in this section i want to talk to you about the difference between excel tables and pivot tables because it's really important that you understand the distinction and the fact that they're not the same and once we've established what the difference is between these two it will highlight to you the importance of putting your data into an excel table prior to putting it into a pivot table and i know that sounds a little bit like you're doubling up on your tables but there are many advantages to excel tables which are going to make your life a lot easier when you're working with pivot tables so what i've done here is i've taken our clean data and i've just created a couple of extra tabs at the bottom and the first tab here is called excel table now you'll notice that the data on this particular worksheet looks slightly different from our original sales data and that is because i've put this data into an excel table i'm going to show you exactly how to do that in the next lesson now once you put your data into an excel table a couple of things happen we get some formatting that's automatically applied so i can see here i have some shading in that title row i've got borders applied around each cell and i've also got these little drop down arrows at the top of each column and what these are are little filter and sort buttons so if i want to sort the data in this particular column i can and i can also filter the data to maybe just see the data that i'm interested in now again i don't want to go too much off down this tangent because we are going to cover this later on in the session but just be aware you automatically get these little drop downs when you put your data into an excel table now one of the coolest things about excel tables is that they have auto expand capabilities and what that means is if i jump down to the last row of my data so i'm just going to press the control down arrow you can see i have just over 10 000 rows if i was to add some more information into the bottom of this table it's basically going to expand and that data will become part of this table so if i just very quickly copy and paste something into here notice the table has expanded i've got that formatting applied to this row and this new data is essentially part of the table and what that means is that anything that i have that uses this table data so maybe that might be a pivot table or some kind of chart it means that any new data that i add will automatically be included in the pivot table data or in the pivot chart i just need to click the refresh button to update my pivot tables to include that new data now if i didn't have my data in an excel table it's not going to be automatically included in my pivot table when i create it i would have to essentially go in and reselect the cell range to include the new data so putting your data in a table makes you a lot more efficient and makes the process of adding new data and updating everything that hangs off of this data a lot simpler so this is probably my number one recommendation prior to creating your pivot table another thing you'll notice is that as soon as you put your data into an excel table you get an additional ribbon at the top and that ribbon is called table design now table design is what we refer to as a contextual ribbon it means it only appears as and when it's needed so if i click somewhere outside of my table data just in a blank cell off to the side you can see the table design ribbon disappears as soon as i click inside that table it's available for me to use and on this ribbon i'll find everything i need to format this table apply different options and utilize different table tools i'm going to look at some of these in the next lesson so i highly recommend putting your data into a table prior to creating a pivot table now just as a comparison so you can see how different these are this is an excel table and on the next worksheet i have a very basic pivot table and you can see that it looks completely different to an excel table the main difference between an excel table and a pivot table is that excel tables are essentially static i can't do a great deal with this data i can't analyze it in loads of different ways i'm pretty much limited to either sorting it or filtering it whereas with a pivot table i can move these fields around i can add different fields in to view my data in all different ways now again we're skipping ahead a little bit here but i just really wanted to illustrate the difference between the two pivot tables are a lot more dynamic and are there to help you analyze your data whereas excel tables are a little bit more static so now that we've established the difference and why excel tables are going to be useful to you let's move on to the next lesson and i'll show you how you put your data into an excel table so now that we have a clean data set and we know what the difference is between an excel table and a pivot table it's time for us to put this data into an excel table now what i've done here is i've saved this clean version of the file as sales analysis clean so if you want to pick up the course at this point and start out with a clean data set then you can download this file from the course files folder if you've simply been working on the sales analysis workbook and you've followed through every single step so far you should be at this point as well so how do we put this data into a table well there are a couple of different ways that we can do this as is always the way in microsoft there's always multiple different ways of doing the same thing the first thing you need to make sure is that you are clicked somewhere within your data doesn't matter which cell just anywhere as long as it's within your data set now if i want to put this into a table the first method i could use is to go up to the home tab and then in the styles group i have a drop down here that says format as table and you can see this shows me all of the different table styles now the colors that you're seeing in here these are determined by whatever theme you're using in excel and if we just go off and take a look at the theme that i'm using we can find that on the page layout tab in the first group just here if i click themes i'm using the office theme which is the default for excel if i was using one of these other themes so let's say gallery for example you can see that changes the font that i'm using but it's also going to change the colors of the table styles in this drop down as well so if you're looking at this drop down menu and thinking to yourself well i have completely different colors why is that it's going to be because of the theme that you're using so if you want to make sure that it's exactly the same as mine change your theme to the office theme now with all that said let's go back to our format as table drop down notice that our table styles are divided down into different groups we have custom at the top light medium and dark at the bottom and this is really personal preference whichever one of these you choose it's going to apply that particular style to your data set so if i was to choose something like this one just here light gray table style medium 8 i can say yes my table has headers click on ok and i get a very dark looking table style notice that as soon as i put my data into a table i now get that table design ribbon if i decide i don't particularly like this table design i can simply go to the table styles drop down and switch it for something else and you'll see as i hover over i'm getting a live preview as to what each one of these is going to look like now notice at the top underneath custom i have a table style just here and this is one that i've created for myself is a custom table style and if you have a specific way that you want your table to look you can create your own table style by clicking new table style at the bottom now i'm not going to get into that now and because you're not going to have access to the custom table style that i've created i'm going to choose something that we can all use so let's go for this one just here blue table style medium 6 and click to apply so that is the first way that you can apply a table style by clicking the format as table drop down and selecting one of the options now i'm going to control z just to go back a few steps to our plain data and show you an even quicker way of putting your data into an excel table make sure you're clicked in the data and press the control t keyboard shortcut you get this little create table dialog box yes my table has headers click on ok and it's basically going to apply the default table style and of course if you don't like that formatting you can choose another style from this drop down so let's reapply a blue table style medium 6. so those are the two methods you can use now once you've converted your data and put it into a table as i said you're going to get this additional table design ribbon at the top now from here we have some table style options and i can toggle these off and on and change the way that my table style looks so if i deselect header it's going to remove that header row now that's not particularly useful so i'm going to put that back on if i want to add a total row at the bottom i could toggle that on and it's going to jump me all the way down to the last row it's going to add a total row and i can see if i expand this column it's not quite wide enough it's going to give me the total profit i'm not really interested in that at this stage because we're going to be able to calculate this when we put it in a pivot table so let's just turn off total row control up arrow to jump to the top now currently you can see that this table style includes banded rows which means we have a blue row then a white row a blue row then a white row so on and so forth if i want to turn that off i just need to deselect banded rows and i get something that looks a lot cleaner and i actually quite like it like this i can choose to add a specific style to the first column only and if i scroll across you can see that it's going to make everything in that first column bold i don't particularly like that let's turn it off i could do the same for the last column so it's going to apply some formatting in this case bold formatting to the last column only and if i want to i can choose to have banded columns which is going to give me basically something similar to banded rows except down the columns now i'm going to turn that off because i don't particularly want that and then the final thing we have here is the filter button if you remember i said automatically when you apply a table style you're going to get these drop down filters and i find these really useful if i quickly want to run a sort on my data or maybe filter the items in this particular column i can use this drop-down filter but if you don't want them there if you're not going to do anything like that you can deselect filter button and it's going to remove them from those column headings i'm going to keep mine because i do like to use those now the external table data group we're not going to use in this particular course so i'm going to skip over that because it's not actually relevant to what we're doing the next group the tools group this is where we can come to create a pivot table we can remove duplicates from here we've already done that but we could do it from here and we also have a convert to range button so if you put your data into a table and then you decide that you basically want to take it out of the table and just have it as plain data if you select convert to range that's going to remove the table from your data and then finally we have the properties group and all we really have in here is a couple of options the first one being table name now when you put your data into a table excel is automatically going to give that table a generic name so you'll probably see something up here that says table 1 table 2 table 3 something like that now you can keep those table names but i would advise you to give your table a meaningful name because that makes it a lot easier for you to identify this particular set of data if you can imagine if you have a workbook that has maybe 20 sheets and they all have excel tables on them if you want to quickly jump between your tables or maybe use your table data in a formula when your tables are named just one two three four it's very difficult to know which one you're referencing so i always like to give my table a meaningful name so i'm going to call this sales underscore data now it's worth noting here when you're naming your tables you can't have any spaces in the name so you need to separate words with an underscore or make them all one word remember to hit the enter key so that table name sets and then underneath that we have a resize table option now i don't need to do any resizing just here i am happy with the way that it looks so now that i have my data in an excel table i have it formatted as i like it and i've given my table a meaningful name finally we finish preparing our data ready for analysis with a pivot table and that's exactly what we're going to get on to in the next section hello everyone and welcome back to the course in this section we're going to take a look at how we put together a pivot table yes finally after a whole section on cleaning data we've made it to the part where we actually create a pivot table and we're going to start out in this lesson by taking a look at something called recommended pivot tables now if you notice the worksheet that i'm working in currently is called recommended underscore pivot underscore tables.xlsx and you'll find this file in the course files folder and this file is basically just the clean version of our data so if you haven't followed along all of the lessons in the previous section you can simply open this one up and you're good to go for the next section so now that we have our data in a table let's take a look at recommended pivot tables now you'll find these on the insert tab in the tables group at the beginning notice we have two different options we have pivot table and then we have recommended pivot tables so what exactly is this option well if we take a look at the screen tip it says want us to recommend pivot tables that summarize your complex data click this button to get a customized set of pivot tables that we think will best suit your data so that's exactly what it does if we click this button what we're going to get is this little pop-up window and let's just widen that out a little bit that is going to recommend the pivot tables that excel has determined are best for our data so it's basically taken a look at our data and now it's suggesting different ways that we can organize that data using a pivot table and in general you'll find the one that's most appropriate for your data will be nearer the top of this list and you can see that we have quite a few different recommendations so the first one that i'm clicked on here if i was to select this it's basically going to create a pivot table for me and it's going to arrange my data in the following way so here i can see that it's going to list out all of the regions and then it's going to give me the sum of the unit price so if the analysis that i wanted to do was i wanted to see the unit price broken down by region this would be a really nice quick way for me to create that pivot table if we take a look at the second one down this is summarized in a different way so again it's using the regions as my row labels but this time it's doing a sum of the unit cost the next one is different again this time we have our row labels and i can see that these are grouped now again i don't want to jump too far ahead but just know that when you have these little plus signs next to an item in your pivot table it means that grouping is applied and here if i was to select this one it's going to show me the basically the total profit for each region the total unit cost and the total unit price so if this is the type of information i'm interested in again that would be a great one to select and i can carry on going through so let's take a look at the next one because this is different again this time it's showing me a summary in a slightly different way so this time it's using the item type as the row labels and then we have a sum of the revenue sum of the total cost and then a sum of the total profit and that's basically what each one of these are it's showing me different ways that i could arrange this data and analyze it and which one i choose is very much going to be determined by the type of information that i'm looking to get out of this pivot table so with this one for example if i'm really interested in seeing the sum of the unit price for each of the item types i would choose this one and let's use this as our example if i was to select this and click on ok what excel does is it gives me a new worksheet so you can see it's just got the generic name of sheet2 currently and it automatically creates that pivot table for me so it means that i haven't had to build this table myself from scratch because effectively i've used a little template and that is what recommended pivot tables are now just because you use one of these it doesn't mean that you have to keep it in this particular format i could choose to change the fields that i'm summarizing or move fields around and again we're going to get onto that in the following lessons for the time being i'm going to delete out this worksheet and i just very quickly want to go back into recommended pivot tables because something else that you have in here is you do have the option right at the bottom to create a blank pivot table so if you want to start from scratch and define what fields you're using in your pivot table you can just click blank pivot table at the bottom just here again excel is going to create a brand new worksheet but it's going to show us a blank pivot table which we can then build using our fields on the right hand side and this is pretty much exactly what we're going to do in the next lesson i'm going to show you the different ways that you can create a blank pivot table and then we're going to start adding in fields to analyze our data so now that we've seen how to create a pivot table using recommended pivot tables let's take a look at how we create one from scratch now there's a couple of different ways that you can do this now one of the methods you can use is pretty much what we did in the last lesson we can click in our data in our table in this case jump up to the insert ribbon and in that first group we have an option for pivot table now if we click this it's going to take us to the create pivot table dialog box and there's a couple of things that we need to check in here before proceeding so the first thing you'll see in here is it says choose the data that you want to analyze and then we have a couple of different options we have select a table or range or use an external data source we also have a third option here which is currently grayed out so it's not available to me of use this workbook's data model now notice the first option here has automatically been selected for me by excel and if you notice underneath it says table range and then it says sales data now because i was clicked in my excel table data when i clicked on the pivot table button excel has assumed that the data that i want to use in my pivot table is basically everything that surrounds where i'm clicked and because we named that table sales data it's picked up the sales data table so in this case this is exactly what we want to use however before we move on let's just explore this second option of use an external data source now you would use this option if maybe you have data stored somewhere else so outside of this particular workbook so you might have that data stored in an access table or maybe you have it stored in just another excel file if that is the case if you want to use that data in your pivot table you can click on choose connection browse for more at the bottom and it's going to open up your local drives and you can go in and select the file that you want to import so that is what that second option is all about now we're going to use the first option because we want to use this table data the next thing that we have to tell excel is where we want this pivot table report to be placed and again we have three different options we can choose to put our pivot table on a brand new worksheet we can choose to add it to an existing worksheet and if i was to select this option underneath it says location and this is going to allow me to basically select a specific worksheet in my workbook now i only have one worksheet and that is called sales data but if i had another one listed down here i could choose to place that data on that particular worksheet i could even choose to put my pivot table on the sales data worksheet now i wouldn't recommend that you do that it's always good to get into the habit of separating out your data from your analysis so always have your raw data set your source data on a different worksheet to your pivot tables and your pivot charts so in general the option that i tend to select here is new worksheet now once again there is a third option where we can choose to add this data to the data model now again because this is a beginner's course i'm not going to go down this rabbit hole but this option here basically allows you to combine different data sets into one so if i had four excel tables containing data i could add them all to the data model and then create pivot tables based off of that but for the time being we're going to stick with new worksheet let's click on ok now notice at the bottom straight away excel has created me a brand new worksheet it's called sheet5 what i'm going to do is i'm going to move this worksheet to the right-hand side of my data source just for readability so i just need to click and drag and drop to do that what i'm also going to do is rename this worksheet so let's right-click rename and i'm going to call this let's just call it pivot table for now so that we don't get confused we can always come back at a later stage and make that more meaningful once we know what our pivot table is going to contain and what you're looking at on this worksheet is basically a blank pivot table report you can see over here we just have this sort of little rectangle that says pivot table 13 to build a report choose fields from the pivot table field list and over on the right hand side you can see the pivot table field list i'm going to talk to you a bit more about exactly what this is in the next lesson but that is kind of how you get started creating your pivot table now what i want to do here is just show you another method that you can use so i'm going to right click and let's just delete out this worksheet and go back to our data source now instead of going to the insert ribbon you can also create a pivot table from the table design ribbon and you'll notice here in the tools group we have summarize with pivot table and this is basically going to take you to exactly the same create pivot table dialog box so once again i want to use this sales data table range and i want to place it on a new worksheet so all i need to do is click on ok and i have exactly the same thing i can move my worksheet right click and rename this pivot table and hit enter so very straightforward and simple to start creating your pivot table from your excel table data so now that we have the bare bones of our pivot table let's start to take an in-depth look at what exactly we're looking at on this worksheet now as i mentioned currently when you create a pivot table from scratch you're going to end up with a worksheet that contains a blank pivot table report and i can see that over here on the left hand side currently it's not really displaying anything of use but that's going to change in a moment now the first thing i want you to note is that when you are clicked inside this pivot table reports notice that you have two additional ribbons appear and again these are contextual ribbons that you'll only see when you're clicked inside the pivot table and i can see them now we have pivot table analyze and also pivot table design and these two tabs contain all of the commands that you're going to need to manage organize and change the look and feel of your pivot table if i click my mouse outside of that pivot table report just somewhere else on this blank worksheet you can see that both of those ribbons disappear they don't appear again until i'm clicked inside the pivot table so until they're needed so just bear that in mind because we're going to be dipping into these ribbons constantly when we're working with pivot tables now if we briefly look at the design ribbon first of all and get ourselves familiar with the types of things that we can do now if we start with the group on the end called pivot table styles this is very similar to your table style so if you want to make your pivot table look a little bit nicer you can choose a style from this drop down remember these are all very much controlled by the theme that you're using in excel and we can also create our own pivot table styles if we want to so again very similar to excel tables if we move along we have some pivot table style options so again this really relates to how we want this style to look so do we want row headers column headers banded columns banded rows and then finally the first group we have here is the layout group and this allows us to control if we display subtotals and grand totals in our pivot table report and we can also choose a report layout so quite a few different options in there which we'll go through when we add some data into our pivot table we can also specify if we want to insert a blank line after each item or remove a blank line after each item so all of these relate to the overall look and feel of your pivot table now if we move across to the pivot table analyze ribbon this is where you'll find your more functional options so let's start right at the beginning of the ribbon this time pivot table name now as is with excel tables by default when you create a pivot table excel is going to give it a very generic name so you can see here it just says pivot table 15. and once again a good habit to get into is renaming or giving your pivot tables a meaningful name so that you know exactly which pivot tables you're referring to in your worksheet now i'm going to come back to this and give it a more meaningful name when i've determined what data i'm going to display in my pivot table i have some advanced options underneath which we'll get into a bit later on and other things that i can do on this ribbon i can apply grouping i can do things like insert slices and timelines which are basically visual filters i can refresh my pivot table and update it with new data i can select different parts of my pivot table i can move the pivot table and i can even do things like create pivot charts from my pivot table data and right on the end we have a show group and you can see that all of the buttons in here are toggle buttons i can toggle these off and on depending on if i want to see them or not and one of these that i'm going to use right now is this field list now what you'll notice is that if you click outside of your pivot table report that pivot table field list on the right hand side completely disappears now theoretically when you click back in your pivot table report that list should reappear but occasionally it doesn't and i can't tell you how many calls i used to get about this when i worked on a help desk now if that pivot table fields pane doesn't reappear when you're clicked in the pivot table all you need to do is go to your pivot table analyze ribbon and make sure you have field lists turned on the majority of the time it's because this button here has become toggled off so when you click in the report you don't see that pain so it's just a simple case of clicking that button to bring it back so that was a very brief overview of the options that we have on these ribbons a lot of these will become clearer as we go through the course i want to end this lesson by talking about the main part of pivot tables and that is the pivot table fields so what do we have over here in this pane on the right well if we take a look at all of these different fields you'll notice that these are actually the column headings from our source data so when we created a pivot table based off of that data it imported all of the column headings and then underneath there we have these four currently blank areas filters columns rows and values and what these basically do is determine the layout of our pivot table it really controls the type of analysis that we're doing on our data and the idea here is that you can grab any of these fields above and drag and drop them into any one of these four blank areas and you'll start to build yourself a pivot table report and which fields you choose to drop in which areas down here really determines the layout of your report and the type of analysis that you're doing so i find if i'm going into a pivot table and i have an idea of what i want to analyze that's always quite helpful so if i know i want to find out how many units we've sold in each region that's really going to help me when it comes to building my pivot table because i know that i'm going to need the region field and also the units sold field so i can drag and drop these two fields into any one of these four areas and take a look at how that's going to analyze my data and that's exactly what we're going to do in the next lesson we're going to start adding in some fields moving them around so you can kind of get an idea as to how pivot tables work so now that we have a basic idea of the types of commands that we can find on the pivot table contextual ribbons and we've also had a brief introduction to these pivot table fields let's start building up our pivot table and adding in some data now as i mentioned it's always a good idea to have a little bit of an idea as to the type of analysis that you're looking to do and if i take a look through my different fields here let's start out by doing something reasonably basic so maybe i want to find out the total profit by region so what i can do here is i can grab the region field and i can drag it down into one of these four areas now when it comes to where you place these fields that's really entirely up to you and the beauty of pivot tables is that they're so easy to change or pivot that if you get it wrong the first time it takes two seconds to change it now in general this values area down here this is where you're going to put your values so anything like unit price unit cost total revenue total cost total profit all of those types of fields would go into this values area so if we grab region let's start out by dragging and dropping that down into this rows area now notice when i let go take a look at my pivot table report now what we have are all of the regions listed in the rows notice that it says row labels at the top and then it has grand total at the bottom now still this isn't really displaying a great deal of information so let's go and grab another field and i want to see what the total profit is by region i'm going to grab this field and drag and drop it down into that values area now notice when i do that take a look at what this field label now says it says sum of total profit so the pivot table is essentially aggregated all of the totals to summarize them by region so now i can see the total profit for asia the total profit for australia and oceania so on and so forth and then right at the bottom i've got a grand total of all of these added together now depending on what type of field you drop into this values area most of the time it will try and do a sum there are some cases where by default it will do a count depending on the type of field that you've dropped in there now what else can i do with these fields well maybe i decide that i don't want to have the region showing in the rows i want it in the columns instead so i can grab the field and drag it to columns and my report layout changes to display in a completely different way now for me this isn't the most easy to read but hopefully that illustrates how flexible pivot tables are let's grab regen and just drag that back to rows because i think that is a lot neater now i'm not just stuck looking at my report in this way i can make it a lot more complex so if i scroll up let's take a look maybe i also want to break it down by item type so i'm going to grab the item type field and i'm also going to drag that into the rows area and i'm going to drop it underneath region take a look at how that changes my pivot table now it's summarizing by region and then by item type and it's going to go through each region and item type until we get all the way to the bottom where we have our grand total and notice that next to the regions we now have this little minus so these turn into collapsible and expandable buttons so if i'm just interested in one particular region i could collapse everything up and just zero in on that now it's worth noting that if we do have these little collapsible and expandable buttons if we go to the pivot table analyze ribbon notice in that show group at the end we can choose if we want to display those or not so i can toggle this button off and on and choose if i want to display these buttons also if i have a item that's collapsed up if i right click my mouse in that cell and go to expand collapse i have some further options in here so i can choose expand to expand back out that item so that's really all a pivot table is you choose your fields you drag and drop them to any of these areas until you get the analysis and the layout that you're looking for now one little area down here that we haven't really spoken about is this filters area now what this allows you to do is you can drag and drop a field into this area and it's going to allow you to apply some filters to your pivot table so i'm going to grab the sales channel and i'm going to drag and drop that into filters and check out what i now have at the top it says sales channel i have a drop down and i can choose if i want to view the sum of total profit for these different regions and items just related to offline sales let's click on ok and it's going to update that pivot table data i can click again and choose online and if you want to select multiple items you have a little checkbox just here which will allow you to go through and select all of the items that you want to see so this is very similar to any other filter that you would have used in excel and can be super useful when you're trying to hone in on specific details when you hear people talk about multiple dimensions in the context of pivot tables all that really means is adding in more fields to analyze your data in different ways so if we remove some of these fields and this is a good opportunity to show you how to delete fields from these different areas so i'm going to grab sales channel and all you need to do to remove it is just drag and drop it outside of that pivot table fields area notice i have a red cross when i let go it removes the field let's remove item type as well to take us back down to just the region and the sum of total profit so this would be a one-dimensional pivot table so this would be a one-dimensional pivot table we just have our regions and the sum of total profit as soon as we start adding in other fields it then becomes a pivot table that has multiple dimensions so as we've already seen we can add item type and we could go a stage further and start adding some other dimensions into the columns so for example maybe instead of having it in the filters area i want to display the sales channel in the columns so let's drag and drop it and see what we get so now take a look running across the top i can see my data summarized by region by item type but also by offline and online sales and then we have our grand total at the side and also a grand total running across the bottom i can even grab something else so let's go for order priority and i'm going to drag and drop that underneath the sales channel in the columns area and this is where we start to get really complex so now we're analyzing by essentially four different dimensions we have our region our item type we then have offline and the order priority and then online and the order priority so very quickly i can see the sum of the total profit for the asia region for office supplies that were purchased offline but had high priority this would be my answer just here so simply by adding in fields we create ourselves a multi-dimensional pivot table that can go really deep when it comes to analysis i could then go a stage further and maybe add something like the order date into the filters area so let's drag and drop that down and now at the top here i can go through and i can start filtering this by specific months so let's select february march and april click on ok and all of the data is going to update so that is a multi-dimensional pivot table in this lesson i'm going to talk to you about deleting fields and locking the report layout now i already showed you how to delete fields in the last lesson so let's go ahead and do that because currently this is a little bit of a chaotic pivot table report let's make this a little bit easier to read so i think i'm going to delete the order priority field from columns so again i'm just going to drag it outside of that pivot table fields area let go and it's going to remove it so now that looks a little bit easier to read and it's a little bit easier to manage now one thing that you'll notice when you start working with pivot tables is that quite often depending on the type of data that you have within your cells and also the fields that you choose to use in your pivot table quite often the layout of your report will kind of jump all over the place and that is mainly because the default in excel pivot tables is that they will always auto expand to accommodate the contents of the cell now most of the time that is exactly what you want it to do so if this number grows a bit larger or maybe i had a much smaller number in there i don't want to have a very wide column if i have a very small number in there or a very small column if i have a very wide number so autofit will autofit to the widest contents and that is the default now sometimes you have situations and i would say this tends to come up when people start creating pivot charts next to their pivot tables because what generally tends to happen is if you have a pivot chart just here right next to your pivot table and you start moving the fields around because the report layout is going to change sometimes you find that your pivot table is going to start overlapping your chart and you find that you're constantly moving things around so if you want to have a little bit more control over the width of the columns you can turn off that auto fit so let me show you very quickly where you go to do that make sure that you're clicked inside your pivot table and then up on the pivot table analyze ribbon we're going to go to the first group here called pivot table and for this we need to jump into options now there's lots of different options that you can get your teeth into in here the tab that we want to be on is this one just here layout and format and if you take a look towards the bottom one of the options that's automatically selected is autofit column widths on update and that is currently ticked so in general if i didn't want my columns to change width i would make sure that i set them first to the width that i want them to be and then i'd come in here and i would untick this little checkbox and click on ok so now if i move some of my fields around you'll see this take effect so i'm going to grab sales channel and just drag it down to rows now take a look at column b can you see sum of total profit is slightly overlapping the boundary of that cell and that is because it's not expanding to accommodate the widest item you might be thinking to yourself well why on earth would i want to do that because i always want to accommodate the widest item as i said you will find as you go through excel there are certain situations where you will want to be able to control that particularly if you have data next to your pivot table so just be aware of that i'm going to drag sales channel back to columns and i'm actually going to go back to my options and i'm just gonna turn autofit back on now when i grab sales channel and drag it down you can see that i don't have that problem everything auto fits nicely hello everyone and welcome back in this section we're going to start to talk about aggregation and grouping and we're going to start out in this lesson by discussing the various different methods of aggregation available when you're working with pivot tables now if you're not sure what i mean by aggregation let me make that really clear so currently we're looking at a pivot table and we've summarized it by a number of different fields and if you remember i mentioned that when you drag fields into this values area in the bottom right hand corner excel is either going to sum or count depending on the type of field that you've dragged to this area so currently it says in here sum of total profit and that's exactly what i'm seeing in my pivot table i'm seeing the sum of the total profit for each region broken down by item type sales channel and i can also filter it by order date now i can see that i do have a filter applied there i'm going to switch that back to all by selecting everything and now i have my totals if i go back down to that values field in the bottom right hand corner and remove this field so that i have a blank pivot table report again if i drag that same field back down to values you'll see it's going to do a sum so by default in excel when you drag a field that contains numeric values so numbers into that values area excel is going to aggregate your numbers by doing a sum if you drag a field that has text in it or dates then it's going to do a count so let's drag this out again to remove it if for example i decided to drag the country field and the country field contains text into this values area take a look at what i get now instead of doing a sum it's doing a count of the countries so i can see how many countries in my source data in the different regions by item type and sales channel so our data may aggregate in one of those two ways depending on the type of field that we've used now i'm going to remove this and i'm just going to put the total profit back into that area now sum and count aren't the only two excel functions essentially that we can summarize our data by we have some others in here as well which you might find useful so what about instead of seeing the sum of the total profit for each of these regions and item types i want to see the average well all i need to do is summarize my data by average and i can do that in a couple of different ways if i'm clicked in my pivot table i can right click my mouse go down to value field settings and then you'll see in this little window that pops up in the summarize values by area i can choose the type of calculation that i want to use to summarize my data so if i want to take a look at the average instead of the sum i can choose that from the list click on ok and my numbers are now showing as averages another way that i can do this is by using that field in the values area you can see now it says average of total profit if i click the drop down arrow in that menu i can also get to value field settings this way and maybe i want to see the maximum value in this range click on ok and my pivot table updates so this is definitely worth jumping into value field settings and taking a look at the different calculations that you can use to summarize your data you can see there is quite a few of them in there we have those main excel functions some count average max and min and then we have some additional functions product count numbers sdt dev var so on and so forth and some of these get a little more specific and i will say that you will mostly use the top five that you see in here some count average max and min so it's definitely worth having a little play around with those so that you're seeing in your pivot table exactly the information that's relevant to your analysis so now that we've seen how we can change how we're aggregating our data let's talk a little bit about combining different methods of aggregation because this is really going to open up your analysis and take it beyond the source data in your workbook now to demonstrate this i am going to make a couple of changes to this pivot table just to make it slightly less complex and easier for you to see so what we're going to do is we're going to remove this filter the order date filter by dragging it outside the pivot table fields area i'm also going to remove the sales channel from columns so i just have the total profit listed by region and item type and i can see that currently i'm just showing the maximum of the total profit so let's change that back to doing a sum remember right click your mouse down to value field settings change it to some and click on ok now what i'm going to do here to make this table a bit easier to read is i'm going to change these heading labels and these are pretty simple to change so for this one here where it says row labels you can see why it says row labels as opposed to region or item type and that's because we have both showing below here we have the region and then we have the item type so excel doesn't really know what one you want to use for the heading so it just says row labels now we can change that i'm going to double click in this cell and i'm going to change this to region and hit enter and i can also change this one just here so where it says sum of total profit by default if i double click it's going to open up that value field settings box again and i can choose a custom name at the top so if this is a bit long maybe i might just want it to say sum of profit and click on ok to change that name now one thing you'll notice is that if you do change the name of this heading if you take a look over the pivot table fields in this values area it's changed down here automatically so it's updated to say sum of profit the same as what it says in the pivot table but the actual field just above is still called total profit so just bear that in mind when you're working in your pivot table and renaming these headings so now i can see the sum of the profit by region and by item type but what if i also want to see the average of the profit we've seen how we can change that by right clicking going to value field settings and changing how we're summarizing our data but if i select average from here it basically replaces the sum of profit column with the average of profit column now that might be what i want to do in some scenarios but sometimes i want to be able to see the sum and the average next to each other so how do we do that how do we combine these two together well let's just take this back to sum because all we need to do here is just add another total profit field to the values area so we basically want to duplicate what we have in column b so i'm going to go over to my pivot table fields i'm going to grab total profit again and i'm going to drag it down to values which is basically going to give me a duplicated column remember this one won't take on the new name we would have to rename this one as well if we wanted it to match but now that i have this in here i can then summarize this specific column using a different calculation so we're going to right click summarize values by and this time we're going to go to average and now i can see both the sum and the average let's double click in the heading and just change that title to average of profit and hit enter what about if now i also wanted to see the maximum profit by region and by item type again i can grab the total profit field drag it down to the values area it gives me another column i can right click summarize values by and i'm gonna say max double click on that heading area and change the title to max of profit and hit enter and i could carry on going what if i wanted to grab something like unit price i can grab that field and drag it down by default it's going to do a sum which is absolutely fine but if i wanted to see the minimum unit price this time i could drag another one of those fields into that values area right click my mouse summarize values by min double click to change the title and i'm just going to call this min of unit price and hit enter so this kind of expands how we're able to use our source data because in my source data i didn't have any columns which worked out the average of anything or the min or the max but we can do that on the fly in our pivot table just by using that summarize by option that's available in the right-click menu now i have a bit too much going on here i'm going to remove the last two that we just added so min of unit price and sum of unit price and leave myself with these three calculations so now it's time for you to give this a go yourself have a little play around see how you feel and then we'll move on to the next lesson time now to move on and start talking about grouping and ungrouping pivot table data and we've already come across grouping a little bit so far in this course although we might not have realized it because excel has automatically applied grouping to this pivot table when we added the item type into rows so if you take a look down in the rows area of the pivot table fields you can see that we have region and item type now notice currently if we look at the pivot table next to each of the regions we have this little minus symbol and that is basically grouping if i was to remove item type from the pivot table fields rows area by dragging and dropping it you can see that we just now have region and those plus and minus symbols have disappeared because excel no longer needs to group our data as soon as we add item type back in because we're now summarizing by two fields excel needs to group that data so we can see all of the item types for asia then australia so on and so forth and excel will do this fairly frequently depending on the field that you've dragged into one of the pivot table field areas so let's drag another one in so you can see something slightly different when it comes to grouping and for this example i'm going to use a date field so if i take a look over at all of my different fields let's see maybe i want to summarize by the order date now what i'm going to do here is i'm going to drag that down into the rows area and i'm going to place it above region take a look at my pivot table excel has automatically grouped my data by the order date and i have those plus buttons next to each of those years take a look at what's going on down in this rows area remember i simply dragged the order date field so why do i have in this rows area years quarters and order date along with my region and item type shouldn't i just really have order date in here where did years and quarters come from well again this is something that is automatic in excel what it does is when you drag something like a date down to an area it looks at your data your source data that is and it says how can i break this down in a way that's going to be most useful so it's recognized that i have different years in my data which i do so it's given me a year's field it's also recognized that each year can be split up into quarters so it's given me a quarters field as well and i can now use these independently of the overall order date field so let's expand 2010 by clicking on the plus and now what i have underneath is quarter one two three and four because every year has four quarters if i expand quarter one then i'm going to see all of my information so now i can see quarter one january for this region and these item types and it's going to go through each one of these for each month of the quarter so my pivot table is going to end up being extremely large if we scroll down we then get to february and we have all of the same details so it can get quite complex and quite detailed so this is grouping in practice now what if i look at this automatic grouping i think to myself you know what this is a little bit of overkill i'm not really interested in seeing these figures broken down by quarter but i am interested in the year and the month well what we can do is go down to our rows area and simply drag that quarters field out and now i'm just going to get that summary by the year and then by each of the months and these months are based on the order date in that sales data and the same will be applied to all of the other years as well we have january if i scroll down we then have february so on and so forth so we can really customize and determine how we want this data summarized now if i click in the pivot table and go up to the pivot table analyze ribbon you'll see that we have a group group on this ribbon and we have three options group selection ungroup and group field now if i click on group field this is going to show you where that grouping comes from where those additional fields come from this is showing me where my data starts at so again it's looking at my source data it's finding the first date in my source data and then it's finding the last date in my source data and then it says okay we've got data in between these two dates i'm going to group them by months quarters and years so this is where it's kind of pulling that level of detail from if you wanted to you could say i never want to see the quarters and deselect quarters so it always summarizes by month and years it's really entirely up to you how you set that up now notice that it's expanded all of my different groups now i want to collapse them all back up a quick way of doing that is to go up to this active field group and you can see here we have a collapse field and an expand field button so i'm going to choose collapse field just to take that back to how it was i might even want to click on the year and collapse that up as well so i can just see those years and then the sum of profit average and max i could go a stage further here and i could go back down to my rows area and drag years out of rows and then it's basically going to break that down by months across all of the different years and remember if you don't want to show these plus and minus buttons if you kind of want to lock that down a little bit and just see these figures for each of the months remember from the pivot table analyze ribbon in that show group you can choose to remove those plus and minus buttons now i'm going to turn mine back on just to bring those back now let's take a look at another example of grouping and this is basically where you can create your own groups as opposed to excel automatically creating them for you so what i'm going to do is in this rows area down here i'm going to remove order date so we get back to how we were at the beginning of this lesson so maybe i look at this data and i say to myself for these item types i actually want to create a group called food and drink and then i want to have another group called other well we can do that by creating our own groups so what i would need to do here if i want to create that first group food and drink i need to select all of the items that i want to include in that group so that's going to include baby food beverages cereal fruits that's food and drink so let's hold down control and select that one as well meat we can select that and also snacks and vegetables so those are all of the items that belong in the food and drink category or group so to create that group let's jump back up to the pivot table analyze ribbon and select group selection notice now i have a new group and currently it's just called group one so what i want to do here is i want to rename this food and drink and the way that you need to do this is to make sure that you're clicked where it says group one and press the f2 key you can then backspace and rename it so let's call it food and drink and hit enter now notice i've renamed this group but if we take a look down in the rows area we just have item type 2 just here and that's exactly what this new field is so if i want to rename this i can click the drop down arrow go to field settings and i can give it a custom name of food and drink just to keep everything consistent notice now that i've changed that also in the active field area the active field is showing as food and drink so now i have that first group i want everything else to go under the category of other so let's select everything else say group selection i have a new group i need to click in it and press f2 and we're going to call this other and hit enter and once again i want to make sure that the field matches what i have in the pivot table and what i can do just so i don't have to go all the way back down to the bottom right hand corner is right click field settings and i can rename it up here as other and click on ok and you'll see that now that field name has changed underneath rows and also in the active field area and notice that it's applied that grouping structure to the rest of my data so it's really quick and easy if i'm just interested on the profit for asia related to food and drink i can basically collapse up everything else and just zone in on these figures just here so that is how you can also create your own custom groups now let's just finish this lesson by doing the reverse and ungrouping our data notice in the group field just above we have an ungroup button so i'm currently clicked on food and drink i'm going to select ungroup and you can just click ungroup wherever you want to ungroup that data so very nice and straightforward so far as we've been working away in this pivot table we haven't really paid too much attention to the number formatting that we're using a number formatting is always going to make your numbers a lot easier to read now when you build a pivot table those numbers are going to be unformatted and you can see here because we haven't applied any number formatting all of the numbers that we can see in our pivot table don't have any formatting applied and you can see they are quite inconsistent when it comes to the number of decimal places being displayed and we also don't have anything like a currency symbol to show what currency we're using and a currency symbol always makes things a lot easier to read now i'm going to assume that if you are an excel user you're probably well aware of how to apply number formatting if not let's just do a quick recap i'm just going to type some numbers into these cells over here outside of the pivot table currently those are plain whole numbers and they just have general formatting applied if i wanted to display these as monetary values i would need to change the formatting that's applied to these cells and from here i could choose either currency or accounting format that's going to give me a consistent amount of decimal places and also the currency symbol and if you don't want to use the dollar symbol you can select the one that is more appropriate for your region from the drop down jump into more accounting formats and then you can choose your currency symbol from this big long list just here so if you are used to doing that you might think to yourself that that is going to be the method that you use when you want to apply the same thing in pivot tables and what i would say to you is that that is definitely not the best method remember pivot tables are not static they're dynamic the fields and the location of those fields is always moving depending on how we choose to pivot our data and when you apply number formatting in the way that i just showed you it applies that number formatting to the cell as opposed to the number so if we were to use that method in this pivot table and maybe we decide to select this group here and apply number formatting from this home ribbon that formatting will apply to the cell if these fields then move around that formatting isn't going to travel with the number and in this case i actually want it to do that wherever i have these number fields i want that formatting to move with the number so in order to make sure that that happens every single time we apply our number formatting in a slightly different way and it's a very simple way and means that we don't also have to go through and select every single number that we want to change the format of so we want to make sure that we're clicked in one of the fields that contains a number that we need to format we can right-click our mouse and you can see we have an option here called number format alternatively you can get to this via value field settings by clicking the number format button at the bottom and it's going to take you into that format cells window so this should look reasonably familiar if you are an excel user you can then go in and apply the number formatting that you want to use so i'm going to use accounting formatting i'm going to keep it on two decimal places and i am going to use the us dollar currency symbol let's click on ok click on ok again and it's going to apply that number formatting to everything in this sum of profit column and i now need to repeat this process for the other two columns so we're going to right click number format accounting and ok i'm going to do exactly the same thing value field settings number format accounting okay and now we have that number formatting applied so don't forget about that in your right-click menu your number format options now of course you can also utilize the custom number formatting that you have at the bottom here if you want to get a little bit more granular a little bit more customized about how these numbers are formatted and custom number formatting is outside of the scope of this beginner's course but i just wanted to highlight that in case you need to do something other than the standard symbol and decimal places formatting for these numbers but in essence it is as simple as that to apply number formatting to the values in your pivot table in this lesson we're going to take a look at how we can handle cells that contain no data so in order to demonstrate this i'm going to need to rearrange my pivot table fields very slightly now the first thing i'm going to do here is i'm going to grab the country field and drag that into filters and what i'm going to do is i'm going to remove the average of profit and the max of profit columns so let's grab both of these and drag them outside of the pivot table fields area so that we're just left with sum of profit so currently what i'm displaying here is the sum of profit for all countries all regions and all item types but what i'm going to do is i'm going to click this country filter and i'm just going to show the data for albania so let's select albania click on ok so my pivot table has updated what i'm going to do now is add another field let's add the sales channel into columns so now i'm summarizing i'm looking at the sum of profit for albania by item type and sales channel so offline or online and what i could do here to make this a bit easier on myself is where it says column labels i'm going to double click and i'm going to change this to say sales channel and hit enter now notice now that i have my data arranged like this i have a couple of empty cells so i can see here that for these particular pieces of criteria so for albania we don't have any details for online sales for household products and the same thing applies to offline sales for snacks we have two empty cells in here now you could keep those blank and then go on and continue analyzing your data what's going to happen is if we would create something like a pivot chart because we don't have anything in here our pivot chart is going to look a little bit strange because if there's no data it's going to be excluded entirely from the pivot chart and sometimes you still want to be able to see that we have this item type there but just that it's generated no sales so a much better way of doing this a much better way of getting your analysis to look correct is to replace all of these blanks with a zero you don't necessarily have to have a zero you could fill it with whatever you want it's going to be the same method that you use but let me show you how we can deal with these blanks what i'm going to do is jump up to the pivot table analyze ribbon and for this we need to go to this first group called pivot table and we're going to jump into our pivot table options now we have lots of different tabs underneath here but the one that we want to be on is this one here layout and format and if you take a look towards the bottom underneath this format group you can see it says for empty cells show and then currently we have nothing in there we have blank which is why we've got blank cells so what i'm going to do here is i'm going to replace the blank with a 0 and click on ok and now you can see that it's added the dollar symbol and a dash and if i look up in the formula bar you can see that the actual value of this cell is zero so effectively there is now a value in both of those cells and any pivot charts or analysis that we do will include this data is just going to show as zero so i would recommend possibly even before you start creating your pivot tables to review what you have in this four empty cells area and if you are going to be dealing with a lot of values and you want to be able to handle those empty cells make sure that you come in here you have a tick in the four empty cells box and add a zero into this field in this section of the course we're going to move on to talking about the different layout options that we have for our pivot table and we're going to start out in this lesson by talking about subtotals and grand totals now something you might notice when you start dragging and dropping fields into these different areas is that excel will automatically display subtotals and grand totals and you can see that we have grand totals on our pivot table right now we've got a column just here showing our grand totals and also one running across the bottom what also isn't as obvious in this current layout that we have is that we do have subtotals as well and the subtotals are listed at the top of each group so here because i'm just showing information for albania and that's within europe i have the subtotals listed at the top just here and this becomes more obvious if we change the layout of our pivot table so what i'm going to do is change this filter to show all countries and now you can see the different subtotals so here we have the subtotals for asia we then have the subtotals just here for australia and oceania subtotals for central america and the caribbean so on and so forth we have our grand totals listed in the column and if i press ctrl down arrow to jump to the bottom of this pivot table i have the grand totals right at the bottom as well control up arrow to jump to the top this is all well and good you might be perfectly happy seeing that information maybe you're interested in the grand totals and also the subtotals for each of the regions but if you're not and you find that these are just getting in the way and making the pivot table more busy than it needs to be then you can choose to remove them or only display them for rows or columns so we're going to find all of these options for controlling our subtotals and grand totals on the design tab and it's in this first group here layout so let's take a look at subtotals first of all now we have a few different options here we can choose not to show subtotals at all we can show them at the bottom of the group or at the top of the group now the default in excel is is to show subtotals at the top of the group which is why we're seeing them up here now i don't know about you but when i think of a subtotal i always think of it as being at the bottom of a group that's just how i like to read my subtotals so what i'm going to do here is click on the drop down and say show all subtotals at the bottom of the group and to me that makes a little bit more sense i'm reading down my different items and then i get my subtotals here in row 18 for asia now of course that is personal preference if you like them at the top you're more than welcome to keep them there if you're not interested in those subtotals at all then you can say do not show subtotals it's going to get rid of them and your pivot table looks a little bit cleaner we can do the same for grand totals so back up to that layout group if we click grand totals we can turn them off for both rows and columns on for rows and columns on for rows only or on for columns only so if i say on for columns only you can see it removes that grand total on the right hand side but if i go all the way to the bottom my pivot table i still have it here because i left it on for columns if i go back to grand totals and say off for rows and columns it's going to get rid of it at the bottom and also on the right hand side and i will say that in general i tend to turn off both grand totals and subtotals if i'm creating a pivot chart on this data because in general you don't want the totals included in your charts so i find it's just easier to toggle those off and then they're not there getting in the way so very straightforward and simple i'm actually going to go back in and i'm going to turn on for rows and columns those grand totals and i'm going to turn my subtitles on and show them at the bottom of the group another thing that we can do with our pivot tables is we can choose what we want our report layout to look like and we have a few different options that we can choose from each one of them are slightly different so again this whole lesson is very much down to personal preference how you like your report to look and how you find it easiest to read now for this we're going to stay working on the design ribbon in the layout group and a little button that we're going to use this time is report layout and you can see that as i hover over it the screen tip says adjust the report layout the compact form optimizes for readability while the tabular and outline forms include field headers so what exactly does that mean well if you remember the last lesson we were taking a look at subtotals and grand totals and i moved my subtotals to the bottom of the group now if i go up to report layout the first option that i have here is to show my layout in compact form now take a look at what happens here if i click it it's basically going to move those subtotals back up to the top and what compact form basically is is the most compact way of viewing the data in your pivot table so it's going to move those subtotals back up to the default which is at the top and it's also going to auto fit all of your column widths so that you're using the space you have within the pivot table most efficiently it doesn't really change anything else but one thing that it does do is that it maintains the grouping structure that you have in your pivot table so with all that said let's go back up to report layout and take a look at what happens if we decide to show in outline form so let's click and what do we get well this isn't quite as compact as the last one what it's actually done is it's moved this item type and put it in its own column so instead of having these item types kind of grouped and listed underneath each region it's given them their own column so if you find your report a little bit easier to read in this way because it is quite easy to see that this is asia and then we have the item types you could choose to use outline form view now remember each of these views are customizable so again if you didn't like the subtotals at the top you could then go in and say show or subtotals at bottom you're still in the outline form view but you just have those subtotals listed underneath now what if i decide that i quite like this layout i like the item type being in its own separate column as opposed to underneath the region in column a but what if i want the word asia repeated on each line of this table well again we can do that from this report layout drop down you can see right at the bottom it says repeat all item labels so what i could do is turn this on and then i get asia asia asia listed out here and the same for the other regions now you might think that this makes it look a little bit too busy and if you do that's absolutely fine but just know that that is an option and there are some instances where you're going to need to do this to create certain types of chart the one off the top of my head that i can think of is when you're creating a map chart and we're going to get more on to that subject a little bit later on in the course if you do have all item labels repeating and you want to reverse that and turn them off you can say do not repeat item labels and it's going to go back to how it was so now let's take a look at the final report layout that we have in here and that is show in tabular form and again not a great deal changes here but what we do have is we now have the grid lines added back into our pivot table i don't know if you notice that if we undo and go back to the previous layout take a look it's just blank just here we don't have the grid line showing in our pivot table but if we change that report layout to tabular form we do get those grid lines so to me this means that the entire report looks less like a pivot table and more just like a regular excel table or something that you've typed into the cells and if you're somebody who likes to have these grid lines here because it enhances your readability then tabular format might be the one that you want to choose but the decision on which one you choose really is up to you just be aware that you have all three of these and they are all very slightly different in this very quick little lesson i just want to show you how you can add to the readability and spacing in your pivot table by adding blank rows and for this we are sticking on the design ribbon in that layout group the final option that we haven't yet looked at in here is blank rows and you can see what the screen tip says it says emphasize groups by adding a blank line between each grouped item so this applies to the grouping that you have within your pivot table so what grouping do i have well currently i am grouping my information by the region that is why i have these little plus and minus symbols next to them so what i could choose to do here is click on the blank rows drop down and i can choose to insert a blank line after each item now when i click this you'll see that i just get a blank row in between the first group and the second group so it kind of separates them out and makes it a little bit easier to read and see and this will apply throughout the entire pivot table so if i scroll down there's going to be a blank line in between each piece of group data if i was to make a change to this pivot table so that i have more than one group for example if i add the sales channel and drag and drop it down into the rows area you can see that blank line is going to continue through and it's also going to add a blank row after each item type so just remember when you're doing this the blank row is going to appear after the grouped items and currently we're looking at our data in that tabular view if i change my report layout and go back to compact view you can see there is the blank row in exactly the same place now i'm going to switch back to tabular view and if you want to remove those blank rows remember you can click that blank rows drop down and remove that blank line after each grouped item now i actually do want one in there so i'm going to put that back but it is as simple as that if you're not a subscriber click down below to subscribe so you get notified about similar videos we upload to get the files the instructor used in this tutorial and follow along click over there and click over there to watch more videos on youtube from simon says it

Channel: Simon Sez IT

Views: 379,965

Keywords: adding pivot fields, cleaning excel data, clear formatting in excel, excel pivot table, excel pivot tables, excel table vs pivot table, excel tips and tricks, excel tutorial, grouping data in excel, how to create pivot tables in excel, how to use pivot tables in excel, pivot chart excel, pivot charts, pivot table, pivot table excel, pivot table in excel, pivot table ribbon, pivot table tutorial, pivot tables, pivoting fields, simon sez, simon sez it

Id: idgT7Gjf0P0

Length: 159min 56sec (9596 seconds)

Published: Wed Nov 03 2021