Create Pivot Tables Like NEVER Seen Before!

wouldn't it be cool if your pivot tables could include pictures something like this to spice up your reports well now you can that feature is coming to excel let me show you okay so over here in Excel I have some data from a fictional company that sells courses with the course image the earnings as well as their customers if you're wondering how I got the images in the cells I made a separate video on this and a link to it in the description of this video just to give you a brief summary in the master data tab I have the course names and I have the course image this was added using a new feature that Excel is currently rolling out called place in cell basically to get these images in the cells you just have to go to insert illustrations pictures place and sell and select your picture either from this device or online okay so once I got these in here I went ahead and used an X lookup function to grab the image now the even cooler part is coming up because I'm going to create a pivot table out of this table so let's go to insert pivot table from table range my table is called course I'm gonna insert the pivot table on a new worksheet click on OK now let's grab the fields that we want why not grab the course image and put it to the rows we have the images automatically inserted let's grab the earnings and add it to the values now I get to see the course image and the earnings together in a pivot table and the cool thing is size of the images depend on the size of your cells so if I just expand the size of the cell I can get bigger images let's quickly go ahead and add a number formatting to these as well now I have my total earnings by course let's go ahead and expand on this why not add customer country to the rows as well and let's bring course image to the columns update the formatting of these so my pictures are all the same size and let's adjust these as well okay so I've noticed that the new data analysts and the team keeps mixing up some of these countries right so sometimes they think Austria is inside Germany or they mix up Liechtenstein with Italy I want to make it clear to them that these are separate countries also when they talk to customers I want to give them some additional information about the country so that they can use in their Chit Chat here's the other cool thing you can do with pivot tables now you can use data types inside pivot tables so let's convert our customer country to a data type I'm going to select the column go to data under data types we have a geography data type select that and if you've spelled your country names correctly you're gonna see this icon pop up on the side meaning that these are converted to a data type and if you click on the icon you get a card pop-up with a lot of information about the country this is some stuff that my analyst could use in their chat maybe not talk about fertility rate and so on but information about the population of the country the largest city in the country and so on now you're probably thinking what about the pivot table we updated this in the table how does the pivot table look like well let's go ahead right Mouse click and refresh it and we get the data types show up here so if I hover over Liechtenstein the card pops up and I can quickly redone all of this extra information about this country but now let's say I wanted to actually add the flag to my pivot table as well you can't click here and add the flag from the pivot table what you can do is add the flag to your original table we can easily do that by just going to one of these cards and if you hover over any of these fields you're going to see that plus pop-up on the side you can extract this area and put it on your grid the same happens if you hover over the flag I get this icon I'm going to select it and because I have an Excel table it's automatically going to add a new column and add the flag for each single row I'll call this flag now let's go back to our pivot table bring up the fields list I'll just go to pivot table analyze activate the field list I don't see yet because I have to refresh my pivot table so let's quickly refresh it it pops up here let's drag it and add it to the rows as well my pivot table currently has the outline form so let's go to design update the layout and make it tabular form this gives me a cleaner look now we don't need these buttons on the side either so let's go to pivot table analyze and toggle off this plus minus buttons so the fact that we can use images and data types in our pivot tables makes it a lot easier to create more visually appealing reports let's quickly go ahead and add a second pivot table I'm going to select the first one go down here press Ctrl V to paste it and just switch out some settings so this time I want to take a look at my top five customers I'm going to kick everything out from the rows instead I'll drag customer name to the rows because I just want to see the top five I'm going to add a value filter to customer name under value filters select top 10 switch up to 10 to a 5 I want to see these by total earnings this is the only value I have in my pivot table anyhow so I'm going to click on ok now I immediately see my top five customers and the courses they've purchased let's sort this from largest to smallest it's easy now to spot the most popular course among them before we wrap up I want to share one last tip with you and that's the fact that you can use custom data types in pivot tables as well so for example here I have a list of our staff but each one has a little card beside it so when I click on this icon I get to see their picture I get to see the name date of birth and some other information like position salary and so on these are all custom made how did I add these well by going to data under data types I have a data type called employee now this is not there by default this was customly created and if you're wondering how it's done I have a separate video on this and I'm going to share the link in the description of this video so now let's say as HR manager you've gone and held performance reviews with the staff some new developments some meet expectations and some exceed expectations you want to create a hierarchy to easily visualize who's who first of all it would be helpful if we add their picture to the table so I'm just gonna go click on a card and extract the image to the grid we could also add other information you can also click on this side to add information to the grid let's say we want to add their salary as well okay so now based on this I'm going to go ahead and add a pivot table go to insert pivot table from table range I want to add it to the existing sheet and I'm going to put it right here we want to group everyone together I'll bring last performance review to the rows and I'm going to bring the staff information to the rows as well now I can easily see who exceeds expectations who meets expectations and who needs development if I want to see their picture or get more information about them I just need to click on the card you can also add their image directly you can then also add their salary information to the pivot table let's just quickly go ahead and change this to tabular form so everything is easier to read and that's custom data types in pivot tables okay so that's a cool Improvement to be able to use pictures or icons inside our pivot tables I hope it gets rolled out soon to everyone because I know you can't wait to have it let me know what you think and I'm going to catch you in the next video [Music] foreign [Music]

Channel: Leila Gharani

Views: 62,175

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, Excel 2016, Excel 2013, Excel 2019, XelPlus, Microsoft 365, Excel 365, pivottables, excel pivot, pivot tables, images in pivot tables

Id: WNrB1Q9Rry0

Length: 8min 34sec (514 seconds)

Published: Thu Jul 06 2023