How to Plot Pivot Charts in Microsoft Excel

so in this tutorial we are going to look at pivot charts so pivot charts are very very important when it comes to analyzing our data so what differentiate pivot chart with the normal chart is that pivot charts are dynamic in nature in that they vary with time they vary according to the kind of data or kind of criteria you put on it the chart can change and the data can change accordingly so for example let us say we have this data pivot chart okay let's say we have list of products that we have sold including the quantity and the cells that we've made over time so we want to plot this product according to quantity and sales and in using pivot charts and try to vary some of the parameters and see how the table is going to behave in a dynamic way so to insert a pivot chart the first thing you need to do is to click out and come to the home tab over here you just come to insert and you come straight all the way to pivot charts so once you click on this small drop down you can see there are two types of pivot charts there is pivot charts the one that is directly pivot chart and the pivot chart and pivot tables okay but because we have covered pivot tables in the previous class we will just concentrate on pivot charts so we just click on pivot charts to insert our pivot charts now once we click on this the first thing you need to pay attention is you have to choose the data that you want to analyze so you have to keep just select this to take the range of data that you want to analyze or you can use an external data source if you have it somewhere saved within your computer you can just select on this and choose a connection or to connect with the data that you've saved somewhere within your your drive or within your computer but in this case we want to select a table within this computer within this worksheet so we just click and we can come over here click on this and we select this entire table as our table and once you click on ok or enter or this small arrow it returns back into the selection or the creating pivot table dialog so next is we have to choose where you want the pivot table the pivot charts to be placed now we have the option to put it on a new sheet a new worksheet or an existing worksheet with this default location so the default location is this location and you can change it if you want to new sheet or you may want to select a particular location you may want to target once you select everything over here you can just click on ok and it's going to bring in this to build a private chat choose fields from the pivot fields list okay so this is the pivot feed pivot table fields where you have all the parameters highlighted within the the pivot chart okay or within the table you can see the parameters we have products we have quantity and we have sales so whatever you select is going to come over to this uh product pivot chart field list okay the word chart fields okay so you can bring it it's going to come into the builder so for example we may want to plot in product as soon as we select product you can see it has brought it in into the axis and it brought it in as a as a parameter in the platform so all you need to do is select another parameter and once we select for example quantity we want to plot indirectly what this means is we are plotting product against quantity and you can see very easily it has just shown us we have plotted product against quantity and you can see where it sends some of the quantity directly over here okay and if we select cells also it's going to bring it into this same value step and give us the sum of cells okay so you can see over here so what makes this table actually quite dynamic is the fact that you can come to the product over here and you have the option to sort the product according to maybe you can sort from a to z or from z to a or you have more sorting options once you click on this you'll be able to have the option to manually choose or drag and drop items and rearrange them within the chart or you can sort accordingly in ascending order based on product or based on quantity sum of quantity or sum of cells so for example we may save some a product and we want to go with ascending order once you click on ok it's going to be this is in ascending order but then we can put in other features as well and come back to more sorting options we can say maybe descending order and we want it to be on sum of quantity and once you hit on ok you can see the chart has rearranged to that particular feature or to fit in our value you can do quite a lot you can sort from a to z or z z to a you can just rearrange it and it's going to go back to it's going to sort accordingly and you can do all sort of things you can do you can decide to show certain products from here for example right now everything is selected you may want to unselect all and just show some selected ones maybe you want to show shoes mouse cups and maybe cables and once you hit on ok it's going to arrange and show only those values okay so you can see it's from shoes mouse cuffs and cables so this is what actually makes the table dynamic it's very very dynamic you can change everything and you see the value instantly you can see the sorting you come over here you can clear the filter from here directly and it will go back to normal settings as uh that it was before the filter so you can click you can do level filters you can do equal to does not equal to or begins with for example we can say we want to see only values that begins with the letter c or they are about and then once we click on ok it's going to rearrange and bring us the only the bars that are only the products that begin with the letter c that's cables cops and car and cars okay so you can see the arrangement very easily so this is how fantastic this table is and you can easily give you anything that you want you can do level filters okay does not contain all contains or does not ends with or does not begin to it or less than or equal to or less than so you can say less than a given value and you can just go ahead and just specify and then let's say we select level filters and we can come all the way to the equal to or we can say not between greater than if so you can see the arrangement you can come to some of our you can come to product and you can see value filters okay you can do equal to or greater than something or less than something or between this value or top 10 or the about so all these are covered in the filter options that we've treated recently so this is how to bring in or deal with the pivot charts and play around with the dynamic nature of it so one thing you should pay attention to when you are dealing with private chat is once you enable the pivot chart you will see at the top over here you have the pivot chart analyzed that just simply comes up you have the chart name over here and you have a range of options that you can play around with over here you can rearrange the data you can insert slicers if you want you can insert timeline you can refresh in cases when you add a given data or you want to add a data or you have made some changes you want to refresh and get it back on track you can change the data source in cases where you don't want to you don't want all this data you can change the data source and the reflection is going to be on the graph easily and you can clear everything you can clear all you can move the chart to from one sheet to another or from one level box to another okay you can transfer the chart to anywhere and then you can check out field items and sets over here you can do all that and you can play around with the field list and fill buttons you can enable or disable them accordingly very easily from here okay so this is it another thing you should pay attention to is you can see the design tab over here because we are dealing with a chart so we have the design tab so you can do all this to play around with the chart elements you have access access title chart title data levels and data tables error bars and all that you can enable or disable accordingly you have the quick layout you can play around and show whatever you want to show in terms of legends title brass and whatever you want to show you can play around with the color palette as well so the charts can you can play around with the chart with the color palette as it is changing accordingly because these are not in ordinary colors they are color palettes okay so you can change them accordingly and you can you may decide to play around with the chart styles you can change due to anything of your choice as you navigate over you can see as you hover over it you can see the samples of the charts showing up you can switch rows and columns you can select different data you can change the chart type to something else and you can move the chart also to somewhere lastly you can have the format format tab over here where you can deal with the text that is present in the chat okay you can have multiple of things that you can deal deal with within the chat you have the format selection you have to you can insert different kind of shapes and even text boxes over here you can insert different kind of shape styles over here you can see multiple of them you can start shape fill you can insert enable shape outline and shape effects over here you can do all that from here what add styles you can have multiple of them you can play around with all of them over here and enable within your text in the in the chat and you have the option to play around with the selection pane and aligning you can do away with the sizing from here you can enable or disable or increase or decrease the sizing accordingly so there is quite a lot that you can do and additionally when you double click on the table you on the chart when you double click on the chart you can see the chart options over here you can see the field options you have the border options you can all play around with all these change to the transparency the width the compounds type the dash type and the cap type you can do all that you can play around with the shadow effect the glow and soft edges complete around with the axis properties like the sizing and other properties as well you can find you can check and uncheck all these you have the text options also you can play around with the text options you can increase or decrease the transparency text outline you can enable or disable you have the option to play around with the shadow effect as well as the text boxes properties you can see them over here so this is about a pivot chart they are quite dynamic in nature you can play around with them accordingly as you so wish and you can export anything that you are plotted in form of pivot charts into microsoft word or into your own what document that you are working on very easily you can connect them and as you edit your data from here it's going to take over and and also update itself on the other axis or on the other charts or on the other document that you're working on so this is about pivot charts i hope you enjoyed this post thanks for watching and i'll see you in the next post