YTread Logo
YTread Logo

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

Jun 04, 2021
Subscribe and click the bell icon to turn on notifications. Hello everyone and welcome to this

course

on

advanced

pivot

table

s. My name is Deborah Ashby and it is absolutely lovely to be with you today. I'm a Microsoft IT trainer and I've been doing this. for about 25 years and I will guide you through this

course

on

advanced

pivot

table

s. As I mentioned, I have been using Microsoft applications, including Excel, for an extremely long time, over 20 years, and if I remember correctly, I was My manager first asked me to put together a pivot table report around the beginning of the 2000s and from then until now, the functionality and things you can do with PivotTables have advanced in leaps and bounds, making PivotTables the most useful tool available in Excel for analyzing data.
advanced pivot tables tutorial in excel   2 5 hour pivot table excel course
This is an advanced course, so we'll focus on some of the more advanced features, but I'll do a summary at the beginning for anyone who maybe hasn't used pivot tables in a while and move on. Using the Office 365 version of Excel, so that's the latest version, but I will say that if you have 2013 or 2016, this course will be fine for you and even if you have Excel 2010, you will pretty much be able to follow most of the things you can find. , there are a couple of features or tools that are not available in your version. Now this course is divided into sections and there are 12 sections that you can work on.
advanced pivot tables tutorial in excel   2 5 hour pivot table excel course

More Interesting Facts About,

advanced pivot tables tutorial in excel 2 5 hour pivot table excel course...

I am going to start. Let's start from the beginning in section 1 by summarizing some of the most basic features of pivot tables. We are going to work from cleaning data, importing data, creating pivot tables and using all the options of the pivot table. to create a really good analysis of our data and we're going to work through it and end up creating an interactive dashboard that will really bring together everything that you've learned throughout this course and really show you what's possible if you want to take your skills with the pivot table to the next level?
advanced pivot tables tutorial in excel   2 5 hour pivot table excel course
Now within each module of this course there is a video demonstration that will last approximately 10 to 16 minutes, give or take a few minutes, and in the video demonstrations I will do that. You will use several different files, if you would like to work with me you will find these files in the course files folder, so download them to your PC and save them somewhere safe. You can see the course files on the right side here. They are divided into sections, so if I click on section 4, you can see that there are all the course files that I use inside section 4 that you can use to work with me.
advanced pivot tables tutorial in excel   2 5 hour pivot table excel course
I will also establish an exercise at the end of each one. section so that you can practice the skills that you have learned and those exercise files are available in the exercise files folder that you can see here on the right side, so again make sure to download them and within each of these folders you will be able to find all the files you need to complete the exercise, so other than those files, all you need to do now is fire up your copy of Excel, have a drink, and then spend the next few

hour

s with me exploring the wonderful world of pivot tables and In the first module, we'll recap some of the most basic pivot table skills, so I'll go there now, so join me when you're ready.
Hello everyone and welcome back to this advanced course. pivot tables my name is deb and we're in section one of module five and in this module we're going to do a quick overview of pivot tables now as you probably know this course is an advanced pivot tables course but I don't really. I want to dive right into those advanced features without first recapping the basics of creating a pivot table from scratch from some data you might have and need to analyze, so that's what we'll cover in this module, so if I haven't I've used pivot tables for a long time, so maybe you used to use them quite often, but it's been a while, so this would be a good summary for you too or if you come to this course and want to learn something. one of the most advanced features in pivot tables, but you're not very familiar with how to create them from scratch, so this will also be useful to you and what it will also do is give us a good jump. out of the point to practice and acquire some of those more advanced skills, so let's take a look at creating a pivot table.
Now, what you can see on the screen here is just basic sales information. Now I have quite a bit of information here. If I press control, the down arrow will take me to the last row of my data so I can see how many rows of data I have. I have 21,576, so I want to analyze a good amount of data. a pivot table control up arrow will take me back to the top of my data now if we just read the columns we have the month we have the store so this is sales data for two different stores, we have the city that the store is located in we have a store code, the country, the manager of that store, the category and then we have the sales amount and what I want to do is analyze this data in different ways, so I might want to see a total of how many sales each of the managers have accumulated or maybe I want to see how many sales in each category or how many sales per store I could even choose to analyze this by date so I could say how many sales between certain dates and all these things or a pivot table is going to be very useful for you now.
I've actually made some progress, so this data that I have in this spreadsheet that I'm going to use is already what we would classify. As I clean now, we're going to get into data cleansing when we get a little further into this course, but suffice it to say that cleaning data just means that you're starting with data that is consistent and doesn't have any anomalies, so we don't have things as blank rows in our data we don't have blank cells we don't have inconsistent cases we don't have duplicates and everything is formatted correctly now the last step that I would take here before putting this data in a pivot table is that I would put this data in a normal table in Excel and that's a pretty simple process.
You can do this in several ways. I could choose to control a to highlight all my data and then control t which will allow To create a table, alternatively what I can do is go to the home ribbon and I can go to the styles group, the format as table button and I can choose any of these different table formats. It will ask me where my data is and because I clicked inside my data, I selected everything around my selection. I'm going to say yes, my table has headers and I'm going to click OK and now my data is essentially in a table.
How I can? you notice the difference between data that is in a table and data that isn't because it doesn't look much different than it did before you put it in the table, well there are a couple of things, the first is if it is If I do click inside your data, if your data is in a table, you will see the table layout contextual ribbon and that is a ribbon in Excel that only appears when it is needed, so as I click on my data I can see that I have a table layout ribbon and I have all my options here to format my table.
If you were to click your mouse outside of that table, you'll see that ribbon disappear and that's why it's called contextual, it's only there when we need to access it. So if you click on your data and don't see the table layout ribbon, then it's more than likely that your data hasn't been placed inside a table. Another thing that will happen when you create a table from data is that you get these handy filter buttons listed at the top of each column, so if you just wanted to filter on a specific city, you could deselect everything and maybe I'm only interested in the data for aberdeen and barnsley, I can select those two, click OK and it's going to filter my data for me and now I only have data for those two particular cities.
Now I'm going to clear that filter to put everything back in, so those are the two ways you're going to know that your data. has been put inside a table now, why is this so important when you are creating pivot tables? Well, what I'm going to do here is from this table layout ribbon. I'm going to name my table and you'll see the first one. group there is called properties it says table name and by default Excel gets my table with the name table one so I'm going to call this data sales and it's really important when you're naming tables or even if you're naming cell ranges.
You can't have spaces between the two words, so you couldn't have sales space data; must be one word or separated with an underscore. Super important point here remember to hit Enter once you've named your table so that sets table names, so now when I create my pivot table everything will be much easier for me to read, it will be much easier to understand and If we now extract something from that table, we can use the table name. as opposed to table cell references, so now that I've done the final step, I'm going to summarize my data or analyze my data using a pivot table again, different ways you can do this, which is probably easier to Right now, depending on the ribbon that I'm currently clicking on, which is the table layout ribbon, I need to use the summarize with pivot tables option right here.
Alternatively, if you jump to the insert ribbon, you have a pivot tables button, it's the first one. one in that group of tables, clicking on any of them will take you to exactly the same dialog box, so now this box appears that says: choose the data you want to analyze and you can see that Excel has intuitively selected the ones you probably wanna. to use the sales data range and you can see those ants marching around the outside which shows me that it is collecting all my data if I had it saved somewhere else so maybe in another

excel

spreadsheet or maybe I wanted to import them. from another system or application, you could also choose to use an external data source.
The second thing I need to tell Excel is where I want this pivot table report to be placed. Do I want it on a new worksheet or an existing worksheet in my workbook? Now a little tip: I would always suggest that you keep your raw data separate from your pivot tables, so in this case I always like to choose a new worksheet and I'll click OK and what you'll see now if you look down on At the bottom of the screen, I have a new sheet called sheet2. I'm going to right click and rename it to pivot table and now what you see is on the left side of the screen I have my empty pivot table reports and on the right side of the screen I have this pane of pivot table fields now for some reason you don't see this panel, you may not have actually clicked on the pivot table report on the left.
On the right side, for example, if I click on another cell, I can see that panel disappears, so if you can't see it, make sure you click on that pivot table report. If you still can't see it, you may have accidentally closed it and all you need to do to get it back is go to the analysis ribbon of your pivot table in the Show group at the bottom, you have a field list button, click that and that little panel will appear again now. What this pivot table panel shows is all the column headers of your raw data so you can see there.
I have month, city, store code, country manager category, and sales, and then underneath, I have these four different areas, filters, columns, rows, and values. Just dragging any of these fields into any of these four areas, that's what will build my pivot table. This is sometimes a bit of trial and error, it really depends on the type of information you are trying to extract, so let's do it. For a very basic example you might want to see all of these sales broken down by manager. What I would do is take my sales field. I'm going to drag it into the values ​​area.
Then I will take my manager field. and I'm going to drag that down into rows and what you'll see in the spreadsheet is that as I drop it now I have all the managers listed in the rows and I can see the total sales for each of those managers and just to show how you can manipulate your table and make it display how you want, if you were to move the admin field up to the columns area you will see that it displays in a slightly different way, so now we have the tour in the columns and we have the sales below, now that's not a particularly nice way to display this data, so I'm going to move the manager back to the rows.
What if you wanted to do a slightly different analysis? Maybe I would like to analyze the sum of all sales by category again, I can get rid of this admin field by simply clicking and dragging it out of the pivot table. I can take the category field, drag it down and drop it into rows and now I have the sum of sales made by category, yesI would like to do something a little different, maybe if I move the category up in the filters you will see that now at the top I get this little drop down menu that shows me all the categories and currently I still only have the sum of sales, so that It's not particularly significant, although you could, if you wanted to see all the sales for the accessories category, click OK, but if you wanted to break it down even further, maybe you could choose to take the city field and drag it down. rows so now I see a list of all the cities with the total sales and I can filter by specific categories so I can say everything to see everything alternatively if I was only interested in maybe games and if I want to select multiple I have a little checkbox in the bottom.
I'm interested in games, laptops and memory cards, just the sales for those three categories, click OK and my data will be updated, so that's what that little filter area is up there. I'm going to go in, say everything, and click OK. I could drag the category field into columns and have my data displayed in a completely different way so now I have my cities listed in the rows my categories in the columns and my sales sum in the middle and that's a pivot table very basic, how you create one and how you can use those fields, move the data, rotate the data and actually get the analysis out of the data that you need.
Now, of course, there's a lot more to this that we'll cover throughout. this course, but I just wanted to get you started with that basic level of understanding if it's been a while since you created a pivot table. that's all for this module see you next time hello everyone and welcome back to this course on advanced pivot tables, we are in section two and in this section we will discuss importing data into Excel to put it in a pivot table and in This first module we are going to talk specifically about importing data from a text file.
Now I have a text file saved on my desktop. You can see it here. It is this sales data.txt if you double click to open this particular file because it is a txt file that will open in the application notepad. Now everything looks a little chaotic and disorganized here, but what you can see is that we have several different columns of data, so I have a country column, product units, a sales price, a manufacturing price, etc., and so on. You can clearly see those headers at the top of this particular text file and then below that we have all the data related to those column headers.
What you'll probably notice here is that it's not particularly inline, it's not in nice neat columns like you would find in a table and that's not a problem when you import this data into Excel. One important thing to keep in mind here if you do. You have a text file that you created, this is how you separate these columns. Some of my columns in this particular file are separated with a tab. Now it could be that the text file that you create, the columns are separated by a comma or maybe a semicolon or even another special character, it doesn't necessarily have to be a touch, it's just important to keep in mind when you import a file like this to Excel.
Now, what we're going to do with this file is we're going to import all this data into Excel, clean it up a bit, and make it look nice and organized in columns before analyzing it with a pivot table. This will also give me a really good opportunity to show where all the import utilities are stored in Excel, so I'm going to close this Notepad file just for a moment and jump into Excel, so I just created a new blank workbook and what I am doing. What I'm going to do is jump right into the data ribbon because this is where you'll find all the tools and utilities available when it comes to importing data into your worksheet and it's this first group here that gets and transforms data now I'm using Office 365.
I have the latest version of Excel. If you are using a slightly older version, this section has gone through quite a few changes in different versions of Excel, so it may not be called. getting exactly transform data depending on which version you're using now in this group we have a ton of different options, this first one here, the get data dropdown, this is where we can choose to import data from a file, so if you have your data stored in another Excel workbook, you can import them from there, we can import them from a text csv file, which is what we will do in a moment from xml and we have other types of files here as well. import from a database, so if you have some data stored in an access database, you can also import it into Excel and we will see that in the next module you can import data from Azure and also from many other different sources.
So for example you can import from a table or a range from the web from a query or you can set up an odbc link to an external database, so if you use some kind of system, maybe an HR system or a financial system, you can create a connection between Excel and that system and import data that way, that is outside the scope of this particular course, we will focus on two of the most common ways to import data and that is from a text file and also from an access database. So apart from that dropdown, we have some other little options.
These are basically repeats of what you see below this data pull, so really the most popular ones are listed here so you can access them easily, so let's import from a text file and I'm going to select from bar csv text, so let's open file explorer. I have the data import dialog right here and it's asking me to find the file I want to import now that I have mine saved. on my desktop so it'll be pretty easy for me to find it and there's the sales data dot txt select it and click import. Now what Excel is doing is establishing a connection and you can see that now I have this get and Open the transform window now, what you can see here is that there is information at the top about the origin of the file, but it also selected the delimiter , so how are my columns separated?
If you remember, I said at the beginning when we were looking at the notebook file that my columns were separated by a tab and that's exactly what Excel has selected. If we click on the drop down menu, you'll see that I have a lot of different options, so if for some reason you select the wrong one, you can just go in here and you can change it very easily, now it says data type detection and it says based on the first 200 rows, so what the get and transform utility is trying to do here is detect what type of data I have in each of these. columns based on the first 200 rows of my data and what I can also see is that they are split very well, because the delimiter is a tab, every time there is a tab, it separates the next item into a different column and if I I scroll and just do a quick visual check to make sure everything looks good.
I can see that it actually looks pretty good. Then I can choose what I want to do so I can choose to load the data which is an option I have at the bottom or we can choose to transform the data and this is where we're moving into more power query options in Excel and again I'm not going to spend too much time on this, but I'll get to it now because it's quite useful if we click on transform data, a power query editor window will open and my data will be imported into this window and now I have the opportunity to do a cleanup of this data again if you remember in one of the previous modules I told you that it is very important to have your data clean and consistent before you start trying to analyze it using a pivot table and there are many ways to do it in Excel, but if you are importing data into this little utility allows you to clean up that data as part of the import process Now I'm not going to go over too many things here, but one thing I will take a look at is that I want to make sure that Excel has determined the format of each column correctly and I can see that in the top of the country column has b c.
If I click on this is the format of the data within that column, it now turns out to be text in this case and I can see that the correct text format has been selected. so I'm going to go through all of my columns just to make sure that they have the correct formatting applied so that the product again is text that looks good, units sold now, when I click on units sold, what I want here is actually I want this to be currency, I'm going to say replace the current one and that will change that to a currency format.
I can see that the next three columns manufacturing price, selling price and gross sales are all correct, they are formatted as currency, now discounts. to leave it as text for now because we don't have anything there. I'm going to change sales to currency, this to currency and also profits to currency. Now we get to the date column, let's click and I go. to change this date format, this is fine since it's text and the year is also fine because it's also text, so I can go through very quickly and make those format changes and there's a lot of things you can do here you can delete any column you don't want to parse, you can do all kinds of things.
Now I'm not going to go too deep into the basic level formatting, but once you're happy with this, you can select the close and load button and what that will do is import all of that data into your Excel spreadsheet and what What it does usefully is that it also automatically puts this data into a table for me and names it sales underscore data. and what I can do now is check and check to make sure everything looks pretty good. I have a column at the end here that I don't really need, so I'm going to right click and I'm going to say delete, but now my data is in a nice format to then enter and summarize using a pivot table, so I hope that shows you what It is simple to take a text file, import it into Excel.
Some basic cleanup using Power Query and import it into your worksheet ready for analysis. That's all for this module. In the next module, I will show you another way to import data and that is from an access database, so join me for that. Hello everyone and welcome back to this course on advanced pivot tables. We are in section 2 where we talked about importing data into Excel to use it to create a pivot table and in the previous module we saw how we can take information that is stored in a text file and we can import it into Excel and then we can go ahead and create a pivot table from that data and this module I just want to expand on that idea and show you one more example of how you can import data from an access database into Excel and then create a pivot table now, if you've never used access before , don't worry too much, all access is basically a database application and can get quite complex, you can have all kinds of different tables. and databases that link together, but fundamentally in this example, all you really need to see is how you can import the data that you have stored in Access into Excel and then create a pivot table from that, so let's get started to so I can see them here on my desktop.
I have the access file that I'm going to use, it's this one here and I'm basically using a file from the Northwind Traders database and Northwind Traders is actually a database provided by Microsoft. It is a free database that you can use to practice. your access skills just give you a good starting point so you don't have to create the data yourself, so I'm going to jump into Excel and again I have a blank workbook open and we're going to jump right into that. tab we were on last time, the data tab and again we want to select one of the options from the get and transform data group.
Now, when it comes to importing data from Access, you will need to click on the Get Data dropdown. and because the access is a database, you will find the option you need under the menu item from database and one of the options we have there is from a Microsoft Access database. Once again, file explorer will open and I need to navigate to the folder where I have the access file I want to use stored. Now I have placed mine on my desktop for easy access, but for you guys, simply navigate to the folder you have it stored in, select the database and click the import button at the bottom now again Excel will set a connection to the access database, it will analyze what it has in that file and then retrieve the results.
Now this seems a bit complicated, I have a lot of things. are listed here and that is because the access database I have selected contains many tablesdifferent, all with different information. While this is not a training course on access, the access database that you have may be much simpler than the ones I have here maybe you just have one table in there that contains some data, some sales data , some financial data and you want to import it now, what I'm going to do is choose one of these tables that I want I put in a pivot table and the one I'm going to choose is this order summary here and you can see on the right side of that screen that just it shows me all the data that I have in that particular table and I can see that this data will be appropriate for a pivot table and if we look at the columns you can see that I have a lot of different information Order ID Employee ID Customer ID Order Date, etc I have some shipping dates, subtotals, rates I have an order total column.
I have who that product will be sent to. I have your address, etc. Now I don't particularly want all of these columns in my pivot table, so this is where the data will be transformed. be particularly useful because I can get rid of those columns that I'm not going to need, so I'll click the transform data button at the bottom, which returns me to my power query editor and, if you remember, from the previous module here It's where we got first and we changed those data types to make sure they were all correct before importing the data into Excel.
Now in this case, I'm going to do something a little different. I'll just go through and delete any columns that are not appropriate or that I don't really need in my pivot table, so I'll select the order id column by simply clicking on the header and then up on the ribbon. I have an option to remove columns. I'll also get rid of the employee and customer id as they don't show me a lot of information that will be useful in my pivot table now to select two columns just hold down the control key and then you can go in and select delete columns.
I will keep the order date and shipping date. I'm going to keep my totals, my shipping fee taxes. I don't think I have anything that isn't so that I will leave it as it is now that I have tied my columns. I have refined the information that will go into the pivot table again. I can click the close and load button, but this time what I'm going to do is load it directly into a pivot table now in the In the previous module, we just did the close and load option, which basically put all that information out of that text file in my Excel spreadsheet into a table and then I was able to create a pivot table from that data.
We will do it in a slightly different way. We're going to create a pivot table right away, so for that I need to close and load 2. What you'll see is this little data import dialog box now appears and asks me what I want to do next. I want to create a pivot table report. I want it in a new worksheet. I'm going to click OK. Now you're creating that connection, you're importing those fields. I'm going to close queries and connections because I don't really need it. but what you will see is that now I have my blank pivot table report on the right side and I have my pivot table fields on the left side ready to create my pivot table and again these pivot table fields are those column headers and they are the headers of my edited columns, so all the columns that I deleted are not showing in this panel and now I can go ahead and start creating a pivot table as usual, so I'm going to take the order total field. to drag that down in values, I'm going to drag the ship name field and I'm going to drag that down in rows and now you can see my pivot table is starting to stack up so I have my ship name so who goes. and I have the total of all your orders.
Then I could choose to add another field and I'll take this date field. I'm going to drag it down into filters so now you can easily filter by a particular date and see those orders, it's very simple to take data that you have stored in an access database, use get and transform to import it into Excel, you can edit it you're looking at, delete columns in the power query editor and then you can build your pivot table normally, so I hope seeing those two examples from this data ribbon gives you an idea of ​​how simple it is to import data from outside of Excel .
In the next module, I give you a very quick exercise to practice the skills you've learned and then we'll move on to how to prepare your data for analysis, so join me for the next section, you'll want to download the course exercise files, do Click the link below in the video. Description to get them, you can also scroll through the details to find timestamps for each section of this course. If you are enjoying this training, please leave us a review. Hello everyone and welcome back to this course on advanced pivot tables that we have completed. Let's move on to our first exercise and what I want you to do in this exercise is basically practice some of the skills that you've learned in this section, so everything about importing data into Excel before putting it into a pivot table is now done. you can do.
You will find the files you will use in this exercise located in the exercise files folder, so make sure you have them downloaded to your PC so you can easily access them and the files we will use in this exercise. The exercise is the files in the Exercise One folder and you can see I have three files in there. Now the first part of this exercise is I want you to practice importing a text file into Excel and just using some of those options within Power Query. To transform your data before importing it, let's take a quick look at the text file we're going to use and it's this one, import text with a dot, txt.
As you can see, this is a pretty simple little file we have our headers at the top, we have the employee names, hire date, salary, department and title, and this information needs to be imported into Excel so we can create a table dynamic. Now, the important things to keep in mind here are what those columns are like. separated, so in this case it's a semicolon and before we do this, there's just a couple of things I want to point out to you when you're transforming this data, so basically I went one step ahead which I imported into the power query editor and a couple of small points to keep in mind if when importing this text file you find that these headers employee name hire date department salary and title are not in the header, so maybe you have them in this first row and you just listed in this top row column one column two columns three and so on, if you find that happens to you when importing this data, a really good little trick is to click on this little Excel spreadsheet icon and select use the first row as headers so that those headers appear in the top row.
Now what I want you to do once you've imported this data into the Power Query editor is to go over and make sure that all of the data types are set up correctly once you've done that, I want you to close them and load them into a table in your worksheet, so I don't want you to create a pivot table at this stage, just load it into the worksheet as table The second part of this exercise is that I want you to practice importing data from a database from Access data to Excel and then creating a pivot table.
For this part of the exercise I would like you to use the datasource.accdb file, which again I will find in the exercise files folder now what I would like you to do is transform the order table within that database and you can see that I have the open order table here. I want you to review it and I would like you to remove the first column called Order ID. I want it to double check that all the data types are correct for each column and then I would like it to close and load this into a pivot table report and what I would then like it to do.
What I do is I simply rearrange the pivot table so that it shows the sum of the total by department and I would like you to add the date as a filter and finally for this exercise and this part is optional if you want a little more practice. You'll find that you have another file in the exercise files folder called base data dot csv, so this is a different type of file and if you want a little more practice, I'll let you figure out how to import. that data and you can also use it to practice creating a pivot table, but again it's completely optional, but try to do those first two exercises to make sure you have a full understanding of importing different types of data into Excel and creating a table dynamics, that's all for this exercise, in the next section we'll talk about how you can prepare your data for analysis, so join me.
Hello everyone and welcome back to this course on advanced pivot tables that we are on now. Jump to section three, where we'll discuss everything related to preparing your data for analysis. I've talked in previous modules about the importance of making sure your data is clean and consistent before now looking at the consequences if not. Not cleaning your data before analysis is that you could end up with inaccurate results if you don't check your data beforehand, so when we say check we mean things like removing blank rows, making sure our columns are formatted correctly, making sure to have consistency with respect to the case and also do things like eliminate duplicate entries and that is what we are going to do in this module.
I'm going to show you a variety of different techniques within Excel that can really help you when it comes to preparing. your raw data, so I'll show you two examples. The first one will be on a fairly small data set and is the one we've used in the last few modules, so we'll open a text file in Excel and then use some techniques in Excel to clean up that data before putting it into a pivot table now in instead of importing this data as we have been doing previously from the data tape. In fact, I'm going to jump directly to the file menu and I'm going to open the file directly and the file I'm going to open is this, only here import text 2 dot txt, now you can see this in my recent files like I.
I have used it recently and this is a text file so you can open other file types besides dot xlsx files in Excel and if you require Excel to convert them it will guide you through that particular process so let's open this text file and let's see. What's going on? I tried to open it and in Excel the text import wizard appeared. Now assistants are always really great as they usually tend to walk you step by step through a process and many of the options that we're going to go through. are very similar to when we used the get data from data tape option now the first thing this wizard asks me to do is select the file type that best describes my data and by default it is selected delimited and what that means is that characters like Since commas or tabs separate each field, I can see a preview of my file in the bottom half of the screen and you can see here that each field is separated by a semicolon, so in this case, delimited would be the option correct to select here.
I'm also going to say that my data has headers and click Next to move on to the next step of the wizard. This is where I can define what delimiter is used in my data. Now, by default, that tab is selected and that one is not. correct, in this case I want to select semicolon and you will be able to see as soon as I do that in the data preview window at the bottom. Excel has split my data wherever it finds that semicolon character, which is exactly what I want if I click Next again. to move on to the final stage of this import wizard, this is where I can go and I can set up some column data formats if I want.
I'm actually going to do this once it's been imported into Excel, so at this stage I'm just going to click the Finish button to load that data into my Excel worksheet and that's it, this is what a once it's been imported, so there's going to be a few things I need to do here to get this. data in a state that is conducive to creating a pivot table, now the most obvious thing here is that my columns are not particularly organized in a very neat way, so I'm going to want to resize these columns in a very simple way, a way quick to do it. this is to mouse over the first column, click and drag to highlight all columns and to resize all columns to properly fit all the data within that column, all you needdo is hover your mouse over one of the column dividers until you get that black double headed arrow, double click and it will resize all the columns at once, so now that makes it a lot easier for me to read the data I have in my worksheet and now what.
What are any other obvious errors or things you might want to fix in this data? Well the one that stands out the most to me is that I have blank rows in all my data. Now this is not a particularly large data set and I imagine that a lot of the data sets you are going to work with will be much larger than this, perhaps hundreds or even thousands of rows, so it would be quite easy for me to manually go through and delete all these blanks. rows if you have a very large data set that will be time consuming and probably not something you want to spend time on so I'm going to show you a much faster way to do it where you can delete all the blank rows in Once, The first thing you want to do is make sure you have all the columns selected, so again I'm going to click and drag to go to the home tab or my home ribbon to this edit group at the end and I'm going to use the find option and select go to special and one of the options that we have in go to special is to select all the blanks and click OK and there we go, now we have all those blank rows selected at once, which means that now I can enter very easily and delete them all and again I will be left on the home ribbon.
I'm going to go to my cell group, click the Delete dropdown button and select delete rows from sheet, so very quickly I managed to delete all those blank rows. I haven't had to go in and do it manually, now the next thing I'm going to do This is where I'm going to go through and make sure that all the elements in my columns have the correct formatting applied, so I'm going to select column A. I'm going to go to the home tab and I will search this number. group and I can see here that we currently have the general formatting applied, so you might want to change it to text.
I'm going to go to column b. Now I can see that in column B we have some dates, so again I'll probably want to change that format to short date. Now column C has salaries, so you could go up to here and this is really a personal preference, whether you want to. choose currency or accounting now I'm going to choose currency in this case and you can see that my currency is set to US dollars and this really depends on what your locale is in Excel of course if you want to change that currency. symbol again from that group of numbers, you can click on the drop down menu right here and you will have some popular currencies that you might want to use, but you can also access more accounting formats and you can go through a whole list of different currency symbols, but for now I'm going to leave mine in US dollars and then finally department and job title, they're both text fields so I'm going to do them in one go, highlighting them both and changing them to text, so now I know to have the correct formatting applied to all my columns now the next thing I want to do is look at this employee name column.
I can see that my employees' names are all in capital letters. Now I really want them to be what we call proper case. where only the first letter of each word is capitalized and I also want to split this employee's name. I want to have a column with the last name and a column with the first name, so let's take a look at how we can deal with all of them. those things so the first thing we're going to tackle is changing the case and to do this I'm going to add or insert what I call a help column so help columns and you I hear a lot of

tutorial

s refer to help columns , they are columns that you add to maybe do a little cleanup or resolve them and usually you delete them after you're done using them, so this is my first help column and what I do.
The first thing I'm going to do in this help column is change the case to what we call appropriate case and you can do this very simply in Excel using one of the text functions now if we jump to the formulas. ribbon in the functions library under the text, you will find all your text functions and the one we are going to use is called right now. If you feel more comfortable using the functions dialog to do them, then of course, you can do that, alternatively you can type the function directly into the cell, so I'll write equals on the right.
I'm going to open up my bracket and you can see that there is only one argument to the supporting function and that is the text. we want to type in correct case and that is the text in cell a2, close the square bracket and press enter and that's it, now we have changed it to proper case and I can fill it in easily just by using the autocomplete handler at the bottom right . In the corner of that little green square, hover over until you get the little black cross, double click and it will copy that formula all the way down, so now that I've done that, I basically have two columns showing the same information , so they might think they can do it. go in and just delete this column, but if you do that you'll get an error because you've essentially deleted the column that that function refers to, so we need to approach this in a slightly different way, what I want to do is highlight column b where I have my clean data.
I'm going to click the Copy button on the home ribbon and what I'm going to do is just paste the values. right at the top now when you click at the bottom of the paste command you have tons of different paste options and you have a bunch of values ​​to paste now when you're pasting values ​​it will essentially discard any of the underlying formulas and literally just paste what you can see in the cell, so if I paste values, press the Escape key. You can see now when I click on these cells, if you look in the formula bar, it just shows me the text in that cell, it doesn't show me the text. which is a proper formula below, so now I can safely delete that first column and everything works fine.
Now I'm going to go ahead and do the exact same process for the department and the job title to make those cases appropriate, so once I've done that I'll come back and join you for the next part, so there we go. Now that I've created the information in columns d and e, those are also in proper case, so things are starting to look good. much better than when we first imported into this particular text file a couple more things I want to do now this employee name like I said I want to split this so the last name is in one column and the first name is in another one like this which again what I'm going to do is insert a column right here and there's a really cool little tool in Excel that will make this super quick and easy to do and now it's called flash fill.
Flash fill was introduced in Excel 2013, so if you have a version of Excel older than that, you will not have access to this utility, but if you have Excel 2013 2016 2019 or Office 365, you will definitely have this button which you will find in the data in the data tools group and it's this little one, right here, flash fill, you can see the keyboard shortcut for that is also control plus e and what flash fill does is it allows you to fill in information. real quick, so I'm going to title this column with the last name and all I have to do is write the first one, so here we go, Juárez.
If I click on that cell, go to my data tools group and click on the flash fill button like Magic, it's going to fill in all of those last names for me and I can do the exact same thing by inserting another column and this will be the first name and all I have to do is type the name and another way I can use the flash fill is not by clicking the flash fill button, but I can click on the cell below and as soon as I start typing the second one, Can you see the ghost fills in the rest of those names for me, so all I have?
What you have to do is press enter to confirm and there we have all those names. Now I can safely delete column a and very quickly I have separated my names into two columns. Now something else that's really important to check is to check that you don't have duplicate entries in your data and again, Excel has a handy utility that's available in the data ribbon, it's in the data tools group and it's this one here , remove duplicates and it says it will remove duplicate rows from a sheet and you can choose which columns should be checked for duplicate information so you can see here because I clicked on my data, I picked up all my data and I have all the columns selected, like this So essentially what Excel is going to do is make sure that every single column is the same before considering it duplicate, so what we mean by that is you can see here that some people have the same jobs, sales executive , sales executive, it doesn't necessarily mean that the entire row is duplicate information, it just means that they have the same job title, so I tell Excel to make sure that every column I've marked here is a duplicate before deleting it, so I'll click OK and you can see here three duplicate values ​​found and removed, so it's a really nice and quick way to remove duplicates from your data.
I might also decide that I want to change some of the text in these columns, for example in the department column you can see that for most of these departments I have complete information. department listed, but for marketing I only have mktg, so I'm going to change that to say marketing, so I highlighted my column. I'm going to go up to the home tape. I'm going to search and select and I'm going to select the replace option control plus h is the keyboard shortcut for that, it's going to say what do you want it to find, so in this case m k tg and I replaced it with marketing and I'll say replace all and I already did a replace and this time we're going to type r and d and I'm going to replace it with research and development and I'll click replace all and that's okay now the last couple of things you might want to do here is I might want to do a general spell check. of my text, so I'm going to jump to the review ribbon and move on to spelling now because a lot of these are names, I'll probably want to ignore most of them until I get to one that I can see is a mistake, so you can see here.
It's supposed to say terry and it's spelled incorrectly so I'm going to choose terry from the suggestions and say change and my spell check is now complete. What I would like to do here before putting this particular set of data into a pivot table is put this into a table, so if you remember from before, if we control a to select all of our data and we control t, it will allow us to quickly place our data in a table, make sure you have my table with headers selected, click OK and voila, the data is in a table.
I have the design tape on my table if I don't particularly like the colors that have been selected. my table I can choose to change them and now we are ready to summarize with a pivot table, so I hope this has introduced you to some techniques that you can use to prepare your data for analysis. We will continue with this topic. We'll use a larger set of data in the next module and move on to talking about tabular data, so join me. Hello everyone and welcome back to this course on advanced pivot tables that we are studying in section two. where we've been discussing how to prepare your data for analysis, how to prepare it for a pivot table and in the previous module we looked at a very basic data set and I showed you some of the skills that you should really know when it comes to cleaning. your data and prepare to place it in a pivot table in this module.
I'll show you a few more techniques to clean your data. We will expand your knowledge on some of those features that are really useful. Then, I'm going to put this data into a table and show you exactly why it's so important to put your data in a table when you're doing things like pivot tables and pivot charts, so let's start by taking a look at the data set we're looking at in the table. screen now, this is a much larger data set than we had in the last module, so if I jump to the end doing the down arrow control, you can see that this has 707 rows, the start control me will take you back to the top.
Now some of these techniques that we're going to use we saw in the previous module and we'll also expand on them, so I'm going to move pretty quickly through the skills that we've already seen and then we'll go into more detail on the ones that are newer, so starting off I can see again here that I have a lot of empty or blank rows in my data, so we're going to go through the same process of highlighting all of our columns, going up to find and select, going to special and selecting blanks. Interestingly, what you will see here has not only selected thoseblank rows but you have also selected some blank cells in my data now I don't actually want to delete them at this stage so I'll just go through them quickly hold down the control key and just click to deselect them now I only have those rows selected . to go delete delete rows from the sheet and those are gone now I just go back to this column column g the discounts column that I can see in this column.
I have several individual cells that are showing blank now. I would say a little advice. When creating pivot tables, it's always good to have at least something in a cell, even if it's just zero. Again, if you have a lot of cells in a particular column or columns, there is a quick way to simply fill in a number or even just a zero in all of those cells at once, so the first thing I'm going to do is highlight this column now. If you have blank cells in other columns, you can highlight all the columns, but I only have them in column g I'm going to go back to find and select go to special and select those blank spaces once again and now, if I want to enter a zero in these cells, all I have to do is press zero control enter and it's going to fill all those blank cells with zero, so again, it's a really good time-saving technique to add to your toolkit.
Now, going back to this first column, looking down here, I have some country names. Now I can see that the United Kingdom is listed as United Kingdom. While the US is listed in its entirety as the United States of America, what I'm going to do here is find and replace the word United Kingdom with United Kingdom, so again we saw this in the previous module. We're going to highlight our column, we're going to go search, select and choose replace control h if you like shortcut keys, what we're going to do is say: search what uk replace with uk replace all I did 140 replacements and closed it .
Now I can see here in column b that we have quite a few different problems here, so the word kensington is capitalized and I actually want everything to be cased again correctly, but I also have a little bit of a problem with some of these individual cells, so where we have Royal Oak you can see that it's imported and it's split into two lines, so I'll have to take care of that as well. Another problem that I can see is that for some of these rows in particular, these rows right here 18 22 we have some bad spaces at the beginning of these words, so I'm going to want to use some text functions to address all of these problems that we have. in column b so I'm going to add in my auxiliary row insert unfortunately what we can do here is combine functions to achieve our result so we know the way to change the text from all uppercase to the appropriate case is by using the function same right now, what I could do here is just select the text and close my parenthesis, but if I copy this it will only fix the capitalization problem, it won't fix any.
Of these other problems, what I'm trying to avoid is adding more help columns so I can deal with each problem individually. Ideally, I want to be able to address everything within this column, so one thing we can do is if we delete where it says b2. I can combine my text functions, so I'll have suitable equals. I'm going to open up my bracket and then add another text function and the first one. I'm going to add the function called clear and you can see there as I type that on the little screen the hint says to remove all non-printable characters from the text, so if you have non-printable characters there or if you have a manual line it will breaks there, which is what we're happening in cell b8, where it splits into two lines, the cleanup function will remove all that kind of stuff now when it comes to bad spacing, so again if we look down at cell b19 , For example.
You can see we have a lot of space before the word Vermont. Now there's another text function that will remove all those bad spaces and they could be leading spaces, training spaces or strange spaces in the middle of your words, so I'm. I'm going to add another function so I'm going to open another square bracket and that function is the trim function and again you can see it says to remove all spaces from a text string except the single spaces between words so I've combined three text functions In a formula, I'm going to open my square bracket and now it asks me for the text.
I'm going to select b2 and remember to close as many brackets as you open, so I'm going to close b2 and close the clipping. and close clean, press enter and now if I copy this by double clicking to auto complete, you can see all those problems have already been fixed, we're going to do our old copy and paste values ​​trick, so I go to select column c , I'm going to click the copy button, I'm going to paste and I'm going to paste those values ​​directly at the top so I can safely delete column b and I'm just going to add that title back to the product, so very quickly we've been able to address a number of different problems, all within one formula.
What I'm going to do is make sure that all of my columns are formatted correctly, so column a' I'm going to change that to text format. Column b will also be text. Units are now being sold. I'm going to put that into a number and you can see here. Now I changed it to number. It actually puts in two decimal places after the number which I don't really want, so from my number group I have my increase and decrease decimal buttons, so I'm going to say decrease those decimals to zero now manufacturing price sales price sales gross discount gears sales and profits, they are all currency fields, so I'm going to select all these columns, I'm going to go up and this time I'm going to apply a count format and, like I said, the currency format I'm using is usd, This date column I have some really weird stuff here and this is pretty common in Excel.
If you ever see a date displayed as a strange number, all you need to remember is that, by default, Excel classifies a date as a number in the background and the number you see is actually the number of days after the date. January 1, 1900. So according to Excel, 1900 is pretty much when the world began and if you see this strange number instead of the day, it usually always means that you just don't have the right number. Number formatting has been applied, so in this case I don't, so I'm going to change it to short date, month name, that's the text column, okay, and yeah, I'm happy to leave that in the text.
I'm going to do a final check. on duplicates, so up to the data in my data tools group, I'm going to click on the remove duplicates button. I'm looking for complete duplicates so I want to have all my columns selected. Click OK, five duplicates were found. to delete and that's it, I'm pretty happy with how my data looks. As we've seen, the last step that I always like to do is put my data into a table Ctrl a to select all and we've seen a There are a couple of different ways you can put your data into a table.
You can press Ctrl. You can also go to the home ribbon and select a style from the format drop-down menu such as table. You can even go to the insert ribbon and simply select the table option. but another option that you have when you highlight all your data, if you look down in the bottom right corner, you can see this little icon appears right there, which is the quick scan tool control and q is the shortcut for keyboard. Now if you click on this, you'll get a bunch of different options. What Excel basically does is analyze the data that you've selected and then suggest some things that you might want to do and that's in every different element of Excel, so it needs to be formatted.
You could put it on a graph. I could add some totals or calculate some totals, but one option I have is also tables, so I'll click on the table option and that will put my data into a table. I don't worry too much about the color scheme we have here, but I'm going to change this to a color that matches the overall theme a little better now again, like I said, once you've put your data into a table, to access to the table layout contextual ribbon and as we've seen before, I always like to name my tables in this first group, so I'm going to give my table a meaningful name, I'm going to call this product underscore data and Press Enter now.
The last thing I want to talk to you about in this module is the importance of putting your data in a table because I think sometimes people get confused at this step and think well, why do I have to put it in a table? Will that be useful to me? Well the way it will be useful is when it comes time to add new data to your data set, most of the time the data doesn't stay static forever if you were to create a pivot table or maybe some kind of chart that use this data, it could be next month I add more data and I want everything to update well and I want the update process to be as efficient as possible.
Now the way to do this is to put your data in a table like we've done here, so I'll quickly show you why this is useful now to demonstrate this. I'm not going to put this in a pivot table, I'm just going to quickly put it in a pivot table. graph, very simply, I'm going to go up to the embed ribbon and I'm going to go to the dynamic graph. I want to use my product data range and I want to put it in a new worksheet and I'm going to Click OK. Now I'm just going to add a couple of fields here, so I'm going to say gross sales in the values ​​and we're going to do it by product in the categories column, so now what I'm going to do.
What I need to do is add some new data and show you how easy it is to update a chart once its data is in a table, so let's go back to our data and I'll make the down arrow control just to Jump to the end of my data set. Now I'm going to cheat a little. What I'm actually going to do is copy some of this control c data and paste it. at the bottom of this data set, so control v now because my data is in a table, it's in tabular format, the table expands to accommodate that new data and because I named my table product data and the chart It is created from product data. range, it means that everything that is included within that range will automatically update on the chart, so let's go back to our chart, click on it and if I want to update this chart to reflect the new data that I just added.
What I need to do is go to the pivot chart analysis ribbon and there's a refresh button right here. I haven't added much so this change may be very small but let's click the refresh button and look at the chart for any slight movement and there we go so we had a very slight movement and the numbers were updated in our PivotTable and PivotChart also updated now if I didn't have my data contained within a table I wouldn't be able to click the refresh button and have everything updated correctly it would have taken me a lot longer to be able to achieve the same thing as well which is one of the reasons why putting your data in a table is super important, it just makes your life so much easier not to mention the fact that you also have these little filter buttons at the top that can really help you organize and sort your data quickly and easily.
That's all in this module. You've already seen a couple more techniques when it comes. In terms of data cleansing, we've talked about the importance of putting your data in a table and you've seen why it's so important when we've done a quick refresh to update everything we have hanging or using this particular set. of data in the next module, we'll do an exercise to practice the skills you've learned in this section and then we'll move on to really dive into creating pivot tables, manipulating them and diving into some of the more advanced features, so join me on the next module for that, for the next section you will want to download the course exercise files, click the link below in the video description to get them.
You can also scroll through the details to find timestamps for each one. section of this course, if you are enjoying this training, please leave us a review. Hello everyone and welcome back to our course on advanced pivot tables. We've narrowed it down to exercise two and in this exercise we're just going to practice some of the skills that we've learned in this section on how to clean and prepare our data for analysis now you'll find the exercise files in the exercise files folder, so again make sure you have them downloaded somewhere and what I want you to do in this.
The exercise is that I want you to open the cleandata.xlsx file and you will find that this file has two worksheets with clean data that are on the screen right now and another worksheet called 2019 data and what I wantWhat you do is use the skills you've learned in this section to clean up this data, put it in a table ready for analysis, so remember a few key points you're looking for here if you want to make sure you're checking your spelling. what you have on the worksheet, you want to make sure everything is consistent, you want to remove any blank rows or cells, you want to check and remove any duplicate entries in your data and you also want to make sure you have each column. formatted with the correct number formatting style now, once you have done all that, I want you to put the data into a table and you can choose any of the methods that I explained to you in the previous modules.
I would like you to name the table and I will leave the name up to you, but remember that if you are using two words to name your table, you will have to do it all in one word or separate it with an underscore now once you name it. you've done. your data in a table. I would like you to put that data into a pivot chart and for that I will show you what my answer looks like. This is what my pivot chart looks like and I want it to try to recreate the Same thing as you can see in my worksheet, what you can see is that I have the departments listed on the x axis.
I have the quarters in the legend area and the sum of the sales is my data, so I want you to recreate this chart. that I have on the screen as best I can and the final part of this exercise is that I want you to add the 2019 data to the original data set so that at the bottom there is a worksheet called 2019 data that has the sales figures for each department. for the year 2019. I want you to take this data and I want you to paste it at the bottom of the table that we have in the cleaning data tab.
Finally, once you've done that, I want you to go back to your dynamic charts. and I want you to make sure to update the chart to reflect the new data that has been added. See how you go with that exercise and see you in the next section. Hello everyone and welcome back to this advanced course. pivot tables now we are in section four where we will talk about creating and manipulating pivot tables. Now, in this first module, I'm going to show you a couple of different methods when it comes to creating a pivot table. I would say that if you take this course on advanced pivot tables, you probably already have a pretty good idea of ​​how to create a pivot table, so my goal here is to really consolidate that knowledge and show you a couple of different techniques that you could use.
I haven't used it before and I also include some additional tips and tricks when it comes to creating a pivot table from scratch so we'll start where we left off and that's basically with our data set clean, all my data is consistent. and I put it in a table and I also named this table sales underline data, so now I'm at the stage where I'm going to create my pivot table. There are numerous different ways that you can do this like As we've seen above, I could, since I'm in the table layout ribbon, click on the summary button with pivot tables.
I could also jump to my Insert tab and in the table group, the first option is the pivot table, now we are. I'll use them in a moment, but the first thing I want to point out to you is this second option. Here we have recommended pivot tables. Now what the recommended pivot tables do is suggest some pivot tables that you might want to use. Based on the data that you have in your spreadsheet, so Excel has essentially analyzed my data and is now suggesting pivot tables that might be useful and you can see that the main one it suggests is the sum of earnings by country and you will usually find that. the most useful one will be closer to the top or the one that best fits your data will be closer to the top of this list, so if you were to select this top pivot table, what you would do is create a pivot table . for me, it will show me all the countries listed in the rows and then the sum total of earnings for those countries, so if that's the type of breakdown I'm looking for, this is a super quick way to create this pivot. table means that all the fields will be in the correct positions for me.
I don't have to do it manually and you can see as we go down, we have other different options here, so I can choose to show this by month and show the earnings sums by month. I can show it by adding the number of units sold and the manufacturing price and all the different types of options that I have here at the moment. I'm going to keep this pretty simple. I'm going to select this top one and click OK and there we go, you can see very quickly with one click. I have managed to create a pivot table that shows the sum of all earnings from each of the countries and the fields in my pivot table have been pre-populated by Excel based on my selection, so now I have my country in rows and my sum of gains in the securities area, of course, I will not stop at this if I wanted to add more. fields in I could definitely do that, so for example I could take the product field and maybe drag it down into columns and now I get a different layout for my pivot table report.
You could remove the country from the rows by simply dragging it and maybe replacing it with product in the rows, but the point here is that the recommended pivot table option is a great option to give you a base to start with, so I'll delete this pivot table sheet and I'll go back to my original data source, so that's your recommended pivot table option, now let's take a look again at how to create a pivot table from scratch. They clicked on my details. Make sure you are not clicked away from your data. I'm going to go up to the insert ribbon and I'm going to select the pivot table option and again we need to choose the data that we want to analyze and if you have given your table a name, you will see the name of your table or range below so you can view sales underline data.
My range again, remember you have other options here. You can choose to use an external data source, so perhaps a table that you have stored in another Excel spreadsheet and there is also a third option to use this workbook data model. We are going now. To get to that at the end of this course for now, I'm going to select my sales data range. I can then choose where I want the pivot table report to be placed, whether it's on a new worksheet, which is always my recommendation. keep your charts and tables separate from your original data source, but if you want, you can choose an existing worksheet and then you can choose any worksheet you have in this workbook, so if I click the up arrow I can go through and if you had a separate worksheet, you could select that one and it would place the pivot table on that particular worksheet.
Now I'm going to say new worksheet and then the final option we have at the bottom is if we want to analyze multiple tables and again this. It's something we'll cover in another module in this section, so for now I'm happy I selected my data source. I said new worksheet. Let's click OK to create our pivot table and that's it. Again we have our blank pivot table report on the left side and we have our pivot table fields on the right side. I have my PivotTable Analysis Contextual ribbon at the top which contains all the utilities I need to analyze my data and I'm going to cover most of these over the rest of this course and I also have an additional PivotTable Design ribbon which will allow me to control the format or the way I look at this pivot table, so I'm going to go add some fields, I'm going to take my gross sales, I'm going to drag them into the values, I'm going to take the country field and put it in rows, and I'm going to take the country field product and I will place it. that in the filters area, now remember that when you're working with these fields you can have more than one field in each area, so what I could do is take the product and drag it into the rows and you can see when I hover over that green Line.
I can place it above or below where it says country. If I leave it below, now you'll see how my pivot table is organized, so it's organized first by country and then by product, first by country and then by product, if I were to move the product above the country. I organize it in a different way, so this time it is organized first by product and then by country. I could go a step further and maybe take the date and drag it into columns and now I see a breakdown of sales between 2018 and 2019. Now you may have noticed that when I drag that date field down into columns, it splits into three separate fields , so now we have quarter and year dates, and this is a useful thing that Excel does for you when you add a date, it will automatically split it into smaller parts, so if I wanted I could sort by quarters and also by years, so if I click on the plus sign next to 2018, you can see here that I have the third quarter and the fourth quarter and if I click on 2019 I have quarter one quarter two quarter three quarter four and I could continue going even further to see the breakdown. per month now if you get to the stage where I've gotten to where you've expanded a lot of different things and you want very quickly, just collapse them all again if you go back to the pivot table, analyze that you have an active field group and this icon here will collapse the fields for you, so now we're going to get into this a little bit more when we talk about grouping, but hopefully that gives you a little idea.
It's also worth noting here that with this auto split you add years, quarters and the date field, if you don't like it, do that if you just want to. have the date and not split it into years, quarters, months, days, then you can disable that option, so for example I have 2018 selected, which is currently grouped and if I go up to the analysis ribbon of the pivot table, I have a group field option and this is where it tells you what will be displayed when you drag that date field, so if I was only interested in years I could uncheck the months and quarters, click OK and now you can see that those other fields They have disappeared from the columns and I simply have date and it only shows me 2018 and 2019.
Now I can no longer drill down into the individual months or quarters. One thing that is also worth noting when it comes to your pivot table is that you can drill down into your data to get more information, for example I currently click on Japan for the Burlington product and I can see the grand total there, but if I would like see what constitutes this grand total, if I double click it will open another worksheet and I can see the details behind that particular grand total, so I can see that the country is Japan, the product is Burlington and I can see the amount of units sold, the manufacturing price, the sales price of each of those sales and all this other information.
Don't forget that double-clicking on any of these elements will allow you to drill down and see the information behind it. The last thing I'm going to briefly mention here is that in this pivot table fields panel, right at the bottom, you can Look, there's an option to postpone updating the layout. Now what that means is that probably what you'll notice is that I'm moving fields, so if I move the country up to filter, the pivot table changes and updates as I move these fields. different parts of the pivot table, that's fine for what I'm doing right now, but if you have a particularly large data set each time you move a field, it may take a while for the pivot table to reorganize. and resolve itself, that's a common problem when you have an extremely large data set, so to make the process more efficient you may want to organize the pivot table fields first and then do an update in one go, and if that's the In this case, you'll want to select the lazy layout refresh button, so if I select this button and then drag the country down to the rows, the fields moved but my pivot table was not updated, it will not be updated until you manually click the Update button. and then any changes you have made will be updated and updated in the pivot table so don't forget about this little lazy layout update if you deal with large data sets so that's all in this module we have seen how we can use Recommended dynamic tables to make the process much easier and give us a good starting point.
We have created a pivot table from scratch from our data set. I've shown you some options when it comes to rotating or moving your fields and we've looked a bit at grouping and ungrouping dates, how you can drill down and see the underlying data, and also how you can defer layout updates. That's all for this module. See you at the next one. Hello everyone and welcome back to this. course on advanced pivot tables this is still deb and we are in section four and in the previous module we casta look at a couple of methods for creating pivot tables from scratch and also using the pivot tables option recommended in this module that I want To do something slightly different, I want to show you how you can combine data from different tables and use them all to create a pivot table , so let's go straight to an example.
I have a worksheet open on the screen called consolidation. .xlsx and what you will notice right away is that I have three separate worksheets contained in this workbook. The first one you can see on the screen is customer information, the second is order information, and the third is payment information. Now three. The worksheets relate to selling some products and what you will notice is that while there is different information on each worksheet, there is also some common information found on all the worksheets, so for example, in this customer information tab i have a column for the order id i have the name of the customer and i have the status that customer is in in the order information worksheet again i have the order id i have the type of transaction, so whether it was online in store or through a third party and I also have the method they use to pay for those products, so whether it's a debit card, credit card, cash check or voucher, and finally , on the final worksheet again, I have the order id, I have the total and then I have the status, so if it has been shipped or if it is still in the processing stage and what I'm going to do while it's here is just do a quick control shift down and I'll add a currency symbol to these totals.
Now, what you'll notice here is that on each of these worksheets is the order ID, so even though the other two columns are different, the order information is the same on each worksheet and this is a really important point when you are To consolidate different worksheets and link them, it is very important that you have a field in each of the worksheets that we would classify as key so that something that links all the data now what we intend to do here is what we are doing. Our goal is to create a pivot table that incorporates all of these column headers as fields in the pivot table and then I can use any of these fields to build my pivot table.
Now another important thing to note here is that with these different sets of data Currently, in the different worksheets, these are independent of each other and that is a very important point to keep in mind when we link addition tables. I just mentioned word tables and again, this is something we've covered in quite a few of the modules before, the first thing we need to do here is put our data into a table and also name those tables, so we've seen how to do this before I click on my first set of data in the customer information. worksheet I'm going to do a quick control and I'm going to do a Ctrl t to quickly create a table.
I'm going to go up to my table design ribbon and in the table name field I'm going to call. this customer highlight information I'm going to go to the next worksheet and I'm going to do exactly the same thing, so this time I'm going to go up to the insert ribbon, I'm going to select the table option, click ok and a again I have my table and this one I'm going to call order underscore information and then finally the payment information tab and I'm going to do this in a different way again. I'm going to use the Home Ribbon I'm going to go to the styles group and I'm going to select the format as table and I'm going to select one of these formats to put that data into a table and this one is going to be called paid underscore. info, I now essentially have my three different tables, now these are not linked to each other at this stage.
Well, the next stage is once you put your data into a table, you've named all of those tables and then you want to create your pivot table. I have to do this in a pretty specific way, so I'm on the customer information tab, I click inside my first table, I'm going to go up to the embed ribbon and I have to use the pivot table option to be able to do this. We can't use recommended pivot tables, we need to select the pivot table, so you should use this little dialog box that says select table or range and we have our range of customer information selected, which is fine at this stage.
I want to put it in a new worksheet, but the important option here is that we want to get to this bottom option where it says choose if you want to To analyze multiple tables I do it because I have a separate table in each worksheet, so I'm going to select add this data to the data model. Now what you'll notice is that I have a new blank worksheet and my pivot table is empty. report now while I'm here I'm going to rename this worksheet, I'm just going to call it pivot table to make it a little more meaningful and I'm just going to drag this worksheet so it comes after the other three original worksheets now what you'll notice here is that we have our blank pivot table report on the left side and then we have our pivot table fields on the right side now this is going to look slightly different you can see here you have customer information what our table is and then we have the three headers or the three fields of the customer information table and this is in a small collapsible and expandable group, so if I have quite a few and I want to just collapse them to make it easier for me to see other things, I can definitely do that now.
The reason we only have the customer information fields here is because that was the table we clicked on when we created the pivot table and what you'll notice is right above it and it shows that. I'm showing the active fields now next to this active area we have an all button, so let's see what happens when we click all. You'll see what Excel has actually done is imported all the tables even though I was right in the customer information table, when I created the pivot table, it recognized the other tables and it imported them for me and now I can expand them and I can see all those fields from each of the tables, so that's cool.
You can probably start to get an idea of ​​how we can use fields from different tables in a pivot table. We can start dragging and dropping into the areas of our pivot table down here. Be careful with that because there is one more stage we need. We have to do before we can start using fields from different tables to create a pivot table and the final step is that we need to create links between these three tables, so currently they are three completely separate tables that are not linked to each other in such a way. effective.
If I were to drag the customer field into the rows and then decide I want to use the total field from the payment information because these tables are not linked in any way, Excel won't know what data it needs to display, so we need to set up a link between all three tables and this goes back to why it's really important that you have a field or a column in your tables that spans all the tables and in this case that field is the order ID. I have the order. id column on each worksheet and if you are used to or have ever used an application like access which is a database application you may be more familiar with the term primary key it is a single field that loops through all our tables and we are going to use that field to link our tables so that we can build our pivot table, so how do we go about linking the order id field in the customer information table to the other tables that we have for this?
Let's go up to the pivot table analysis ribbon and you can see we have a little option here for relationships and our screen tip says create or edit relationships between tables to show related data from different tables in the same report, so let's click. this little button and I see what we have to open our relationship management dialog, so the first thing I'm going to do here is click on the new button to set up a new relationship, so this is where I can start create my links between my tables, so I'll click on the dropdown and you can see it has my three tables listed here, so I'll say payment information and the column I want to link is the order id. column and I'm going to link it to the order id column of the order information table and click OK, so now essentially what I've done is link the order information field in the payment information and in the information Of the order. tables together I still have one more to do, so let's click new and this time I'm going to link the payment information and the order id to the other table that we haven't linked yet, which is the customer information table and the order id and click OK, so now I have essentially linked my tables using that order id field.
If I click close now, what I should be able to do is use any of these fields to build my pivot table, so what I'm going to do is from this customer information table, I'm going to grab the status field and drag it down in rows, so now I have all my states listed in my rows. I'm going down. to the next table and I'm going to take, let's choose the method and I'm going to drag it in columns and then finally in the payment information table, I'm going to take the totals and drag them. down to values ​​now I don't particularly like the layout of that so I could choose to move the method to filters like this and now I can see all the states, I can see the sum of their totals and then I can use my filter to filter by a specific method and I could keep doing it, so again I could take maybe let's take the customer and I'm going to drag and drop it into the rows just above so I can see it now by customer and then by status. drag it below to display it in a different way.
You could drag it into the columns to get a slightly different format. There are a lot of different things I can do here, but the main takeaway is that we've taken information from three unrelated ones. tables, we have created a pivot table and linked them together so that we can use the fields from any of those tables to create a pivot table. Hopefully that makes sense to you, that's all for this module. I'll see you next time hello everyone and welcome back to this course on advanced pivot tables. We're still in section 4 where we'll look at pivot tables in a little more depth and in this module I just want to briefly introduce you to the concept of grouping your data, so let's jump into an example right away.
I'm currently on that sample sales data spreadsheet and you'll find it in the course files folder if you want to follow this called sample sales data grouping grouping. xlsx and what I have done here is I have taken the data that we were using in a previous example, put it in a pivot table and you can see how I have my fields organized so currently I have the country at the top of the columns from my rows I have the year followed by the product and then I have my earnings showing in the values ​​area. Now what grouping can help you do is simply drill down into your data by grouping and organizing it in a way that makes it easy for you to see exactly what you're looking for and it's worth noting that you can group numeric fields, you can group text fields and You can also group date fields, so in this module I'll show you an example of each of them.
It's pretty simple, but if you're not sure how to do it, stay tuned, so let's start by grouping by text fields, so , as you can see from the rows I have the product list here so burlington kensington lux mandarin royal oak and vermont now maybe each of these are part of a different range so maybe burlington, kensington and lux are part of the luxury range mandarin and royal oak be part of the premium range and then vermont be part of the standard range Now I don't have a range field in my original source data, but that doesn't mean I can't group them based on ranges specified.
I just need to do it manually, so all you need to do if you want to group them. Together I'm going to select the fields that I want in the first groups, this first group is going to be called luxury and these three, the top three burlington, kensington and lux, are all part of that luxury range, so I selected them, right click. and you will see that within the context menu when you right click you have a group option and you can see here. Now it says group one. I have Burlington Kensington and Lux ​​below. Now I don't want this to say group one, so I can very easily just click on that cell and rename it.
I'm going to call this group luxury and press Enter now. What you will see is happening in the pivot table fields. If you now look down the row, I have another field that is automatic. was addedthere called product two, so this is essentially the range that I just created, so I'm also going to rename the field from product to range, so if I click on a little drop down arrow and go to the field settings, this is where I can go in and give that field a custom name so it's going to be called range and click OK so now that field is much more meaningful so I've grouped those first three together now what I want to do is I want to have tangerine and royal oak in the premium range, so again I'm going to select those two, right click on the group and I'm going to rename this group to premium and the last one is just Vermont which is part of the standard. range so all I need to do is click here because there is only one and change the name to standard so very quickly I managed to add information which now helps me organize my products by the range they belong to and these are from By Of course, it's foldable and expandable, so if I'm only interested in what's in the premium range, I can just expand it and see my profit figures for those particular items, which is very simple to do and of course, if I would like to delete it because I have this range field now in rows, if I drag it out of the rows all that information will be ungrouped again as it was, but it's also worth noting that even if you delete that field, that field will still remain in the field of your pivot table, so you can reselect it if you want, so now let's take a look at how we can group when dealing with numeric fields, so currently let's take a look at our pivot table.
I have my countries in the columns. I have my sales sum in the values ​​area and I have my years 2018 and 2019 in the rows area. Now I'm going to rearrange this slightly. In fact, I'm going to remove the year from the rows and replace it with the sales price. So what you can now see is the sum of all sales by sales price. Now it might be that I want to group these sales prices together so that it shows a range, so I might be interested in knowing what the sum of the sales or the total sales is. for all of these countries is when the selling price is between zero and fifty dollars, then fifty and one hundred, one hundred and one hundred fifty, etc., and so on, this is where we can incorporate numerical groupings, so all I would do here is again.
I would select one of the items in the sales price column, right click and go down to the group, so now here I can specify where I want to start and where I want to end and how much I want to increase, so I go. To say I want to start with zero dollars, it will end up at 350 because that is the highest amount I have in this data and I want to show a range that increases in increments of 50. Click OK and that's it. Now see that this has been grouped together so I can now see the sum total of sales for all products where the selling price is between 0 and 49, 100 and 149 and then 300 to 350.
Now if you are wondering why there is a big difference. gap, so why don't you go from 150 to the next? It's because if we go back, you can see here in my data that we basically don't have any data for that particular range, so Excel skipped it, so again it's pretty simple. to group or create your own number groups, what you will also notice with this one is that it does not create a field in the fields area of ​​the pivot tables, but if you wanted to delete this grouping, all you would have to do is to jump to the ribbon analysis of the pivot table in the group section, I have an ungroup button and if I click on it, it will ungroup my data and return it to how it was originally in this final example that we will talk about. date field grouping and we touched on this in one of the previous modules, so currently I've reorganized my pivot table again and just have a very basic analysis here.
I have my countries and the sum of sales for those particular countries now. What I'm going to do is remove the country from Rose and replace it with one of my other fields and the field I'm going to use is the date field and I'm going to drag it down in rows, now watch what happens in this area when I drop the date field, you can see that not only do I get my date field in there, Excel has also given me years and quarters, so essentially it's looking at my raw data, so if we cross back in there, this is my column of date and it says, "Okay", I can see that we have months here, we have years and I could also split this into quarters and it automatically gives me that breakdown as separate fields and If you look at the PivotTable report, you can see that we have some areas grouped into 2018 and 2019, obviously those are the years, but if I click on the plus sign next to 2018, it breaks down into Q3 and Q4.
Remember this is based on the dates I have in my raw data, I expanded Q3 and I can see I have data for Q4 September. I have data for the latter part of the year and the same for 2019. I have all my data for the year of 2019. So to me it looks like we started reporting on these sales during the third quarter of 2018 because we didn't have any data before that, so for me it's a good way to see that information. broken down automatically now you might not particularly like this automatic grouping that Excel does and that's absolutely fine, you can delete what you don't want, so if I don't want it This is broken down into quarters, I take my fourth field into the field rows area of ​​my pivot table, I can just drag it out and delete those quarters and I can see it broken down by month.
Alternatively, if I right click and go to the group, you can see in this little grouping area what I've actually selected, so when I drag a date field down it's going to give me months, quarters and years, so again, if I just I am interested in the months, I could deselect these selected months, click OK and now I only get those months. not even broken down into years, if I wanted to add years back, I could again right click go to group and I could also show years, click OK to bring them back and you'll see as I do that it's moving them.
For me, there are fields in the fields area of ​​the pivot table, so I hope that gives you a clearer idea of ​​how you can group numeric and date text fields and also customize the fields, make them meaningful and use them to get a deeper analysis of your data. That's all for this module. See you at the next one. Hello everyone, welcome back to my course on advanced pivot tables. This is still deb and we are still in section four, where we will see how to create, manipulate and organize pivots. tables and so far you have had quite a bit of information regarding how you can organize and design your pivot tables, but there are still a few more options and that is what I want to discuss in this module that we are going to cover. to take a look at things like subtotals and grand totals and the different layout styles you can apply to your data;
However, before we get into that, I just want to address something that you may have been wondering as we went through the last few modules and that is the data that you can see on the screen right here, so the sum of the sales has not been formatted , so there is no currency symbol in this data, so currently these numbers are a bit difficult to read, now there is a bug with the pivot. tables is that when you want to format your numbers or your values, the misconception is that you can just go in, maybe select them all and change the format from the home ribbon like you would with just a normal value in the cell, now you could definitely do So, you could add some currency symbols here, but if you do it that way you may run into problems later.
A better way to do this is to click anywhere in your numeric data, right-click, and move down to the value field. settings and once you are in the value field settings dialog you will see at the bottom that you have a number format option and that will take you to the format cells dialog so this should work for you. reasonably familiar if you have worked with Excel. quite a bit and this is where you want to go in and apply the correct formatting for your values, so in this case I'm just going to say currency and I'm going to reduce the decimals to zero, click OK, click. on ok again and now I've formatted all my values ​​so don't forget about that option hiding in the context menu so now that we've done that let's move on to talking about subtotals and grand totals first if you look at my data you can see I currently have my products and I have them broken down by year so 2018 then my products 2019 and then my products below and what I actually have at the top here are my subtotals for each of the columns .
So in cell B5, here, this is the subtotal of all these products for 2018 for the country of Brazil. Now you may want the subtotals to appear at the top. I'm very used to seeing a subtotal at the bottom, so whenever I have them at the top, it tends to confuse me a little, so if you're similar to me and want to move the way you're viewing or move the location of these subtotals down, you can do it very easily in the layout ribbon of the pivot table and this is the group that we are going to work with in this module, it is the layout group and the first drop-down menu that we have here is the subtotals.
Now I can choose not to show the subtotals at all so if I Select that you can see they all disappear or I can say I want to show them at the bottom of the group or the top of the group so I'm going to select the bottom of the group and to That's much easier for me. read, I can see the 2018 total and it's below all the individual values, but again, that's a very personal preference, we have a similar kind of deal with the grand total, so right on the bottom row we have a grand total for each of our columns and again we have a grand totals dropdown in that layout group, so we can turn it off for both rows and columns, we can turn it on for rows and columns, so not only do I have the grand totals listed in the bottom part, I have them too. listings for rows as well or I can choose to have them on for just rows or just for columns, so I'm actually going to say just for columns and just show that grand total at the bottom now that we're talking.
About totals, another useful little trick that I recommend you activate when you are working in Excel are the status bar options. For example, if you're someone who likes to highlight a set of numbers, let's say these here for the United States. UK, if you want to be able to see the average, minimum, maximum, sum of all the numbers that you have selected in your status bar, then you need to activate those options within the status bar so that you can see that I have this range . of selected cells if you look down on my status bar at the bottom I can see the average of those numbers I can see the count and I can also see the sum now because I have subtotals turned on I can verify that the sum that is shown in the bar status is exactly the same as the subtotal listed in cell e12, so it's very useful to turn on these little metrics when you're working on anything in Excel, not just pivot tables, if you can't see them when you highlight some numbers, if right click on the status bar, it's this little group that's here so you can see I have the average count and some are on, but I can also choose to turn on the minimum and maximum, so now, when I highlight the numbers I can also see the minimum value and the maximum value in that selected range so it's just a little tip I wanted to include because I find it very useful when working in Excel and it's also a good way to check subtotals now moving.
Starting from subtotals and grand totals, the other options we have in this layout group are different types of report layouts, so let's switch to the compact form. Now you can see with the compact form what it does. Again, moving those subtotals to the top and The four compact means is that you can display multiple fields in a single column. Next is the outline form, so let's click on it and see how it's different. Here again we have a slightly different layout. The outline form will display its fields in separate columns. So you can see here, I have a column for the year, a column for the product, then I have all the columns for my country and then the final layout that we have is in table form, so it is similar to the previous one, the outline of the The fields are still in separate columns, but this time there are no nested values, so it's really up to you which report layout you choose which you prefer to work in, but sometimes it depends on what you're doing and what analysis you're attempting. to do, you'll find that maybe one is better suited for what you're trying to accomplish or makes it easier to view your information now.
Acouple more things we have in this report layout dropdown is this one here, repeat all the elements. labels, so what this means is that if you look at what we currently have in the year column, I have 2018 and then I have my products and I have all my sales information, but what you'll see is that I only have the 2018 label in that first row now, if I wanted 2018 to be displayed in each cell next to each of the products, that's where I would use the repeat all item labels button and put the year next to each line item and then of course, the end.
The option we have there is to not repeat the element labels, if you want to remove them you can select that option as well. Now I'm going to change my report layout back to a compact format and you can see here that when I do that in this particular layout, repeating those row labels is not really relevant because of the way my data is laid out, it has a lot more sense if I'm working on something like a tabular form where I have these empty cells here to repeat those rows labels but not so much compactly and then the final option that we have in this layout group is blank rows, so again this is an item of personal preference if you want to organize or layout your data to make it a little easier to read.
I can choose to insert a blank line after each item, so if I select this option, I will place a blank line between my two groups between 2018 and 2019 and I also have a blank line at the end here, so I could decide what I want. putting my subtotals back at the bottom of the group so that's what it would look like if I were creating this report for myself, that's my personal preference. I like to work in a compact way. I like to have my subtotals at the end. and I like to have a blank row separating each section of my data because I find them less chaotic and a little easier to read, so it's definitely worth playing around with these and seeing which one you prefer to work with and just finishing this module, since we're talking about the way things look and the style of our pivot tables, just a couple of little things here, so at the top we have some columns and sales labels that aren't particularly significant, so you may want to rename them now.
I'm going to click on the little drop down arrow just to remember what we have here so I can see that this is a list of all the countries, so what I might want to do is I just rename this to countries in the part. higher to make it a little more meaningful. You may also want to change some of the sales to sales totals, so don't forget you can go in and change any of the labels. titles to make it more meaningful and I can see we have one more here so this is the year so I'm going to change the row labels by just clicking on the year and there we go slowly my pivot table is getting a lot easier to read.
For me and anyone I share this with, that's all for this module, see you in the next one. Hello everyone and welcome back to this course on advanced pivot tables. We have reached the last module of section four. and I really want to finish this section by showing you a couple of other really useful tips and tricks when it comes to managing and organizing your pivot tables now if you look at the example on the screen in front of me and you will see look for this file in the course files folder, is the sample sales data error values ​​spreadsheet and what we are going to do in this module is simply show you how you can format error values ​​and empty cells in your pivot table now.
To demonstrate this I had to add some errors into my source data so let's re-evaluate and take a look at what we are currently seeing in this pivot table which you can now see here when I click on my pivot table the field list is not showing up of my pivot table on the right side, if that happens to you, if you just jump to the pivot table analysis ribbon in the show group at the bottom, you have a field list button so you'll want to turn that back on now. A small change that I made since the last module is that I added another product, so you will see here that for 2018 and 2019 we now have a new product in the list called markhaser and I actually added it to the raw data, so if you take a look here, there's one of them in row 25, we have marchesa and what you'll see in the raw data is that I don't really have any data, but we have an error in this sales column, so when I click on my again pivot table you will see that error has been moved to my pivot table now for 2019.
See we also have Marchesa. I don't have any errors, but I just don't have sales data for the market for 2019. So how can I deal with error values ​​and also items that don't have any data? Fortunately, Excel has a couple of little utilities that have been built into the field settings that will allow you to choose how you want to manage them, so the first thing I'm going to do here is right-click and I'm going to go to my pivot table option, so the first thing I'm dealing with here is that there is now an error value, if you're not someone who likes to make selections from the context menu and you prefer to use the ribbons you also have access. for the pivot table options from the pivot table analysis ribbon in this first group, you can see that you have options and that will take you to exactly the same place.
Now there are many different tabs here and the one you want to be clicked on. Is this the only one here? Layout and formatting you can see in this formatting area. Here the first option is to show error values ​​and we currently have a blank field and we don't have this option turned on, so right now my error values ​​are just shown as n a which is not particularly descriptive or meaningful to anyone. look at this spreadsheet or this pivot table, so what you might want to do is say the error values ​​show the text without data.
I could also choose a numeric value, so if I wanted to I could have zero in there but I'm going to say no.data, I'm going to click OK and now you can see that value n a has been replaced and if I had a lot of errors n a in my table dynamic, they would all be replaced if you did it now, like I said, if we go back to the options, you could choose to use just one number, so click zero on OK and it will give me zero there, so it's completely up to you how you want to display those error values.
Now, another thing you might want to do is I don't want to show any articles that don't have data, so moving into 2019 we have a different situation here with Marchesa. There is simply no data for 2019 and I can confirm that by going back to my original source data, I will use my product. filter to select only marquise and I can see there is my entry that has no data but has an error which is the one we just addressed but for 2019 I don't actually have any data but it still shows the item listed there because it is part of the set of data, now you might be happy with just having a blank row there and again you might want to fill these blank cells with some kind of value, so again you might want it to say zero, so What you could do is go up to the pivot table analysis, go to options and again under layout and formatting where it says for empty cells you could say you want to display that as a zero as well, click OK and it will fill those gaps. cells with zeros now I'm just going to undo that change.
The other thing you could do is choose not to show the line item if it doesn't contain data and for this we go to a slightly different area if I right click on my mouse we want to go to the field settings and I'm immediately going to jump to the group of layout and print and you will see right at the bottom, I have a tick next to it to show items without data, so currently even if there are no values ​​associated with the item, it will still show in the pivot table, just all the cells corresponding will be blank.
If you simply didn't want to show Marchesa when you have no data, you could simply uncheck this box and click OK. and now you'll see that marchesa is gone for 2019 because there's no data, but I still have it listed for 2018 because there is indeed something there and finally, just a quick tip, what I've done now is I've gone into my data without process and I actually got rid of the error and added some data for marquise, so now I just want to very quickly update my pivot table. I can click on my pivot table to go up to the pivot table analysis ribbon. and in the data group I have a refresh button so when I hit refresh just look at what happens with the Marquez app for 2018.
Now you can see I don't have an error anymore, it refreshes with the total for the UK only and the reason. That's done, because we only have the Marchesa item on sale in the UK, it's not on sale in any of these other areas, so again, for these, I might want to make sure I have a blank space filled in there, so I'm going to say zero, okay, and that makes it look a lot better. We don't just have blank cells or error messages floating around in our data, so these are just some quick tips on how to deal with errors in your pivot table. customize them the way you can choose to show or not show items that have no data and just to finish something I haven't done yet, I'm going to name my pivot table so we've seen the concept of naming tables before.
It's the same with pivot tables, if you want to keep everything consistent and easy to read then you'll want to make sure you name your pivot tables, so again on the pivot table analysis ribbon in the first group, I have the very generic name of pivot table one. I'm going to change this to global underscore global sales and press Enter and now I have a pivot table with a very nice name. That's all for this module. See you at the next one. Hello everyone and welcome back. In this advanced pivot tables course, we've narrowed it down to exercise three, where we'll practice some of the skills we've learned in this section.
You will now find this exercise in your exercise folder and workbook. What you need for this particular exercise is called human resources data consolidation.xlsx and in this workbook you will find three worksheets, employee information, department information and job information, and what I would like you to do is create a dynamic table that consolidates the three tables. So, a few things to remember as you work through this exercise. I want you to make sure that you name each of the tables something meaningful and I want to make sure that you create relationships between the primary key field within each table and whether Remember that the primary key field is basically the column of information that links these three tables, so what I'm going to do now is show you my answer, so this is what I want you to end up with now once.
You have now created your pivot table. The last thing I would like you to do is make sure you have your pivot table in tabular format. You may also want to make sure the salary sum column is formatted correctly and that's it. so let's see how that goes and I'll see you in the next section. Hello everyone and welcome back to this course on advanced pivot tables. Now we're in section 5 where we'll look at all the options you have for formatting your pivot table, so when we talk about formatting we're really referring to the appearance of your pivot table and that could extend to things like background shading on your cells to format header rows or columns and apply some type of color scheme for your pivot table, now all the options of your pivot table in terms of style you will find located in the design ribbon of the pivot table and they are really these two groups that we will focus on in this module.
The pivot table styling options and also the pivot table styles are now pretty straightforward, but there are a few things I would like to point out and highlight so that you know exactly what happens when you apply the pivot table styles. I'm currently clicking on my pivot table and I'm going to jump right to this group of pivot table styles and click on the plus button to expand my styles and you'll see that you have a lot of different styles that you can select from that are divided into categories medium light and then we have dark at the bottom now if you want to see what each of these styles will look like, Excel has a live preview so when I hover over any of these styles, you'll be able to get an idea of ​​how will look like if you were to select it and apply it to your pivot table and again, this is a very personal preference, it could be that you have a general color scheme that you like applies to all of your pivot tables or it could be that you are trying to work within your brand colors for your company.
I'll talk a little more about that later in this module, whenLet's talk about creating our own pivot table. styles now, if I scroll down and just show you some of these darker ones as well, so if you prefer a darker color scheme, you might find that some of these fit you a little bit better, but again, like I said, Now there is a very personal preference. What you need to keep in mind is that before selecting any of these styles, if we just take a look at the options that we have in the style options of our pivot table, you can see that we have row headers, column headers, columns with banded and banded rows so again this is style related now I currently have row headers and column headers selected.
If you deselect the row headers, you can see that removes the bold style from where it says 2018 and 2019. If you uncheck the column headers, you can see that. removes it from the sales total for the country years and then all those country headers, now the banded rows and the banded columns. If I turn on banded rows, you can see exactly what it does now depending on which ones I've selected in the pivot table style options. this then updates in the pivot table styles, so now that I have banded rows turned on, if I click on the dropdown you will see that all my pivot table styles now have more of a banded rows format and, If you want, you can find a time where what you want to use is banded columns and again you can see in the pivot table styles that they are now updated based on my selection in the pivot table styles options.
Now I'm going to select banded rows. I'm going to click on this. dropdown and I'm just going to select one of these at the top here, so I'm going to choose this one here, light yellow light pivot style 19. So very quickly I was able to apply consistent formatting and All that means is that if I create another pivot table in this book, I can very easily select the same style and know that they will look exactly the same, which really helps when it comes to consistency between pivot tables. And how they look now, another thing with these pivot table styles.
If you right-click on any of them, you'll see that you have some other options in that context menu so you can choose to apply and clear formatting, apply and keep. format, so it could be if I already have the formatting applied to my pivot table and I just want the color scheme, I can say apply and keep the formatting and that will keep everything the way I wanted. I can choose to duplicate this pivot table style and I can even set it as default so that every time I create a new pivot table it automatically creates it in the style I set as default and finally at the bottom I can choose to add the gallery to the list fast. access the toolbar, so if I were to select that option, you can see it now here in my quick access toolbar.
I have a quick way to access all those different pivot table styles. I mentioned at the beginning of this module that you can create your own. styles Now, why would you want to create your own style when you have so many to choose from from this group of pivot table styles? Well, it might be that you're not particularly impressed with the colors that you have in these preset styles or it's more likely the case that possibly, if you're doing something as part of an organization, it might be that you have your own branding guidelines, so that maybe all the tables you create in Excel have to have a certain color now if you have something like that or if you just want to create and play with your own styles the option you need to select is right at the bottom here, new table style dynamic and this is where you can go in and customize and completely create your own style, so I'll just show you a very basic example because it's not a particularly complex concept to understand, so the first thing I need to do is name my new style, so I'll give it my initials and call it custom style.
Then I will get to choose each element of the pivot table and I can choose which format I want to use, so currently the one above here is the full table, if I click on the format button then I have my font options border options and my fill options, so it could be I want the entire table to be light green. I'm going to click OK. I can choose how I want my report's filter labels to look. The values ​​of my report filter if I want a first column stripe or a second column stripe. first row stripe and I'm actually going to format this one and make it a slightly darker gray color.
I can choose to format my header row so maybe I want the text in my header row to be italic so I can go in my font tab I can select italic and click OK so when you scroll down you can see basically all pivot table elements you can format and style. It could also be if I go back to the whole table and click format. then I want to add a border around the outside of the entire pivot table, so from here I'll select the outline, click OK and there we go, once I'm happy with that I'll click the OK button. and you'll see that that new star hasn't been applied to my pivot table, but what it does is if I click on the dropdown next to the pivot table styles right at the top, I now have a section called custom when I hover over It's my custom style and then when I click on it, it will be applied now.
It might be a bit of trial and error, so looking at this I can see that maybe I want to change the font color in some of these areas. make it stand out a little bit more or maybe I want to change the darkness of these banded rows because they're a little bit dark, which makes it a little bit hard to read, so if I now want to go in and change the element of this again in the styles from my pivot table, I just need to right click on my custom style and select modify to go back to the same screen, so I'm going to go to the first row stripe, I'm going to go to format and I'm actually going to change the color that I selected for that to a much lighter gray color and I'll click OK again and now you can see it's much easier to read.
Another point I want to highlight here is expectations. that you may have when it comes to using any custom styles that you have created in other workbooks, let me show you what I mean, so here I have a different pivot table that is now located in a completely different workbook if I click this pivot table and Go to my layout tab and click on the dropdown menu. You can see that I don't have access to my custom style, so it's an important point to keep in mind if you create your own custom style while having access to it in all worksheets. inside the workbook you created, if you open a new pivot table, you won't see that custom style there, so how can you fix it?
There is a simple little trick that you can do any way you want. Copying a custom pivot table style to another workbook is reasonably simple. Now what I like to do is have both workbooks open. So the one that contains my custom style and also the worksheet that I'm trying to copy it to. I'm going to open my second workbook and all you need to do is essentially drag and drop the worksheet that contains the style. to the new worksheet and you do that by holding down the control key, clicking on the pivot table worksheet and dragging it to the other workbook and what that essentially does is import that style of pivot table into that particular workbook, so now if I want to apply it to my other pivot table, here I can click on the pivot table and there's my custom style.
Then I can safely delete what I copied and I still have that custom style left in this particular workbook, so that's nice. small solution to copy styles to other workbooks. One of the last things I want to show you here is that if you want to delete your custom style, you can right click and you will have a delete option there. I'm going to say it's good and done. to put my style back to what it was before and I'm actually going to remove the banded rows and I'm going to select a different color for this particular pivot table, the yellow pivot style 19.
I hope that gave you an idea of ​​how you can use those really useful built-in pivot table styles, how you can combine them with some of these pivot table style options and also how you can create your own custom styles with the colors that you want to use and also use any custom styles in different workbooks. Hello everyone and welcome back to this course on advanced pivot tables. This is Deb when we're in section five where we'll look at how to format our pivot tables and the previous one. module we focus more on design by applying pivot table styles and customizing styles and in this module we are going to shift our focus to custom number formatting, something that in my experience tends to strike fear into the hearts of most people and I will say that when I was first learning Excel, it was one of those things that I really had a hard time understanding until I found a basic rule that applies when it comes to custom formatting and now I find it much easier to not only remember but also modify so that it fit exactly how I want my numbers in my pivot table or even normal numbers that aren't in a pivot table how I want them to look, so let's start by just reminding ourselves of some basic pivot table number formats, so I'm on the sample sales data custom number format worksheet that you will find in the course file folder if you would like to follow along and currently looking at my values ​​in my worksheet I can see that I simply have a general format applied to these numbers , so there are currently no currency symbols.
I have two decimal places which shows that I also have some negative numbers here. Now it's not exactly how I want it to look, so maybe the person I'm giving this report to wants to see a coin. symbol and they don't want decimals to be displayed, so let's do some basic formatting of our numbers now. If you remember in one of the previous modules I told you that the way not to do this is to go to the home ribbon. and try to start changing it from number format it will only change the current cell you clicked on.
A much better way to change everything is to right click and go down to the value field settings and select the number format from there, so now what? What I can do is go in and I'm going to say accounting format. I'm going to leave my currency symbol as US dollars, but I'm going to reduce these decimals to zero and you can see it in the preview above. exactly what it will look like when you click OK, so let's click OK and OK again and you can see that that formatting has now been applied. I have my currency symbol and I don't have decimals either.
One thing you'll also notice here. is that my negative numbers are shown in parentheses and we're going to cover those in a moment because it may be that for your particular pivot table report you want them to have a minus sign in front of them or maybe you want them. to display in a completely different color so they really stand out in your pivot table and all those things we can do with custom number formatting, so what I'm going to do this time is right click again to go down to field settings of value, click on number format and this time in this category list I'm going to go down to custom and I'm going to start specifying exactly how I want my numbers to look and you'll see immediately here that there are so many different custom formats set up and of course , you can go through and select any of these now, as I said in my experience, this is where people start to get a little confused because they don't really understand what they are.
Looking here we have a whole range of things with hash symbols zeros square brackets square brackets you know what everything means okay the way I like to remember this is just by remembering a very simple rule and let's start with something more basic, I'm going to select this one right here and when I select something I can edit the custom formatting by clicking on this type field. Now the rule you have to remember is that custom formatting is essentially divided into four parts and what separates those four parts is the semicolon, so you can see here, we have this before the semicolon, so this is the first part and this first part is basically what you want your positive numbers to look like.
The second part, right here is what you want your negative numbers to look like. Look now at this particular example that I've selected, it's only showing those two parts, so it's not giving me the third or fourth part, but if I were to scroll down and select another one that's a little bit longer, something like this. This first part is how I want it to be formattedthe positive numbers this is how I want the negative numbers to be formatted the third part here is how I want the zero values ​​to be displayed so if I only have one zero in my pivot table what do I want? so it looks like I can control that through this third part and the final part here is how I want the text to be formatted, so that's all you really need to remember is those four parts separated by semicolons, positive values, zero negatives and text, not now You don't necessarily have to define all four every time you apply a custom format, so as we saw at the top here with this second example of this, if I were to select this, I'm basically just formatting the positive numbers and negative numbers now.
What I'm going to do here is something very simple and is go back to those negative numbers that currently appear in parentheses in my pivot table. Now what I really want to do here is keep my currency symbols and I want to show the negative values ​​in red with a minus, so what you could do is select one of these custom formats and later I'll explain exactly what these pound symbols mean and these zeros. You can see here that basically if I chose this, my positive numbers will be formatted with a thousand separator and my negative values ​​will be displayed in red, so let's select this and see what happens.
I'm going to say okay and okay again, so it's done what I wanted it to do, it definitely made those negative numbers turn red, but what I lost are my currency symbols and I don't have a minus sign in front of my either. negative values, which is how I really wanted this to look, so I may have to make some modifications to this particular custom format, so I'm going to go back to the values ​​settings and the number format again. I'm going to go to custom and it shows me what I currently have. have been applied and I'm going to start making some changes right here, so what I want to do is go to the negative values ​​part and add a currency symbol right before the first hash sign and what I'm also going to do is just before so that we have the red in parentheses, I'm going to add a minus symbol and I'm also going to add another dollar sign before the positive values ​​and you can see just above what that's going to look like. like now, it might be that you want to go a step further and even just remove the brackets from your negative numbers and display them in red, so again I can go in here and modify what it's going to look like if I now click.
Click OK and click OK again, you can see that's exactly what I wanted to achieve. I have my currency symbols back. I have my negative number in red and they also have a minus sign in front of them. What if at some point? stage, I send this to my manager and he says, well, actually we want all of our negative values ​​to appear in blue. Well, you can change the color in which your negative values ​​are displayed very simply again. Let's right click to go down to the value field settings and number. format we're going to go back to custom and all I have to do is change where it says red to a different color, so it could be green or yellow, but in this case I'm going to say blue, click OK, click OK again and very simply changed the color of those negative values.
Now another thing I can see here is that I have some zero values, so for Marchesa we don't have any data for some of the countries, in fact. The UK is the only place where the Marchesa range is sold. We've seen how we can format them through pivot table options, but I just want to show you another way that you can do it using a custom format, so again I'm going to right click down to value the field settings and in the format of number, so this is what we currently have and if you remember I said the first part is for positive numbers, the second part is for negative numbers, so I will add the third part which deals with zero value formatting, so remember to do that , I need to add a semicolon and now I can type the format that I want to use whenever there is a zero value in a cell and for this I'm just going to add some text and the text needs to be enclosed in quotes and I'm going to say there is no data, click OK, click OK again and like magic we now have no data listed in those zero value cells, if you're worried about what this will do. for your calculations, when you start adding text I would say don't worry too much about it, if I click on any of these cells that currently say no data, if you look in the formula bar you will see that the underlying value is It's still there in the background, so according to Excel, that cell essentially still contains zero as a value.
All you're doing when you apply formatting is really kind of masking on top, so what I'm trying to say is essentially it could. I'm still using this cell in a calculation and just to show you that I'm going to do something real quick. I'm going to say the same. I'm going to select this cell that includes this very long formula that we're going to use. I'll talk about it in later modules and all I'm going to say is plus 10, press Enter and the result I get is 10 because this value according to Excel is still zero and therefore my formula still works.
All I've done is reference this cell, which says 0 and added 10. Now let's go back to our custom format once again. It's worth noting that when you create your own custom format it will be saved in the custom format list, so right at the bottom you can see there is the one I just created, it's quite useful if you want to reuse it again in a pivot table different, it just means that you don't have to go in and start recreating your custom format again now when it comes to These symbols that we have particularly, the pound and the zeros, the only thing you have to remember here is that they're really just placeholders for values ​​and the difference between a pound sign or pound sign and a zero is that the pound sign is a variable. placeholder while zero is fixed, so things like zeros are usually used for decimal places, so I just want to illustrate that a little more by showing you an example on a different worksheet, so I'll get out of here and Now I'm going to move on to my parts numbers spreadsheet.
This isn't any kind of pivot table, but it's just a nice quick way to demonstrate how fixed zeros work. So what I have here is some item numbers and then I have some item names and you. I can see these item numbers, we have 1 5 7 10 25 100 and 2 000. Now it could be for consistency reasons, I want all item numbers to be four characters, so one way to do this is that I can use custom formatting, so I'm going to highlight my numbers. I'm going to right click and since we're not in a pivot table, this works slightly different. This time I need to go down to format the cells and it will take me to my number. format, I'm going to go to custom and what I'm going to do is write, I'm just going to do one, two, three, four because remember zeros are fixed, they're not variable and you'll see it in the sample preview. above, when I apply this, the first one will be zero zero zero one, which is exactly what I want because I want all the item numbers to be four characters and click OK and very quickly I was able to apply that. formatting numbers without damaging the underlying data if I had done exactly the same thing, so let's go back to cell formatting and customization, if I had instead made four pound symbols and clicked OK, I won't get the same result because the symbols of pounds are variable, so I hope that shows you a little bit of what's happening there now.
The last thing I want to show you here is how you can use custom formatting to combine text and values ​​in your pivot table, so even though I have my negative numbers displayed in blue with minus symbols, they stand out quite a bit, maybe my manager came to me and he said, well, actually, I want the word loss next to the negative numbers, so one mistake that a lot of people make is that they come here and try to edit this in the formula bar to add the word loss and you'll see. that you can't do that, so again, this is where we would use the custom format, so the part of this custom format that I want to edit is the negative numbers, so that's the second part, so it's this little part that I still want to edit. show up in blue I still want them to have a minus symbol I still want them to have a currency symbol followed by the number but what I mean is that after the number I want the word loss which I'm going to put in quotes and I'm still going to keep my values ​​zero shown since there is no data, click OK, click OK again and you will see that the word loss has now been added to those numbers, but remember again once you click on This cell, if you look in the bar formulas, you haven't added text loss, so I can still perform calculations and this won't alter any of my totals at the bottom; essentially, you are now putting that format on top of the number.
If at some point you're playing with the custom format and it can get quite complex, if you basically want to go back to how it was originally, all you need to do again is go down to the number format and you can just apply the general format. format and that will make everything go back to how it was originally, so here are some tips when it comes to using custom formats in your pivot tables. In the next module we will do an exercise before moving on to section six, so please. join me for that, hello everyone and welcome back to this course on advanced pivot tables.
Now we have exercise four in which we will practice the skills we have learned in the pivot table formatting section of this course. You will find the exercise file in the exercise files folder and the one for this exercise is called hr datastyles.xlsx and you will see that here we have a data table and what I want you to do is take this data table and create a pivot table and I want your pivot table to end up looking like this, so a couple of key things to keep in mind here when you're building this pivot table once you have your fields in the right place, what I want it to do is for sure salaries have the correct format and I also want you to go in and make sure you have an n a or a zero that shows where there are zero values ​​so you can see in my example I've used n a but you're more So, welcome to using xero, once you've done that , I would like you to format your pivot table and what I would like you to do is create your own custom style and apply it to your pivot table, so this is my answer.
This is an example of a table with a custom style I created applied. You definitely don't have to do the exact same custom styling that I've done here, just make sure you change quite a few elements and then apply that custom styling to your pivot table, see how it goes and see you in the next section. If you are not subscribed, click below to subscribe and receive notifications about similar videos we upload to complete the course exercise. files and follow this video, click there and click there to see more videos on YouTube from Simon Says

If you have any copyright issue, please Contact