Why use Excel Pivot Tables? If you would like to urge insights from your data or create reports really fast, you are going to wish Pivot Tables. for instance you receive this data set, you would like to work outthe total sales by product and obtain them so as so you'll see which products generate the foremost sales. you furthermore may want to work out which customer accounts for the very best percentage of total sales. we will get this done faster than it takes to form a cup of coffee.
Excel pivot tables tutorial
Let's start . (upbeat music) during this example, we've sales and quantity data by product, customer and company. Now, the primary thing you would like to try to to before you insert a Pivot Table is to form sure that your data is organized during a proper format. this suggests it should bein a tabular format such as you see here. Each column features a header. you should not have empty columns within the middle and you furthermore may shouldn't have empty rows within the middle of your data set. and that i do think you should not have all total values within the middle of this data set.
Once you'll check of these boxes, you're able to insert your Pivot Table. to try to to that, you'll attend Insert, and choose PivotTable from here. But check this out for the Range is tough coding it to SAS3 SKS108. this is often until where I even have data. If I add more lines to the present , they're going to not be considered in my end report, i will be able to need to update my range and I'd rather avoid this step. So what i want to try to to before I insert a Pivot Table is to show my data set intoan official Excel table. This has the added advantage that whenever i buy new data added, my Pivot Tables willautomatically reflect that the instant I press Refresh,. and every one I even have to try to to is click on this button here that's called Table or use the shortcut key Ctrl+T.
How to Compare Two Excel Sheets (and find the differences)
Excel figures out thatthis table has headers, you'll accompany OK. And if there's automatic table layer that Excel gives you bothersyou love it bothers me, you'll go and take away it, just select None. But what I also like better to do is to offer my table a reputation in order that I can recognize it afterward . you'll roll in the hay from thisTable Name box here. Now that we've a table, we will automaticallysummarize with a Pivot Table, or return to Insertand click on PivotTable. Check what happens now, we get the name of our table. As our table expands with new data they're going to automatically beincluded in our Pivot Table.
But before I do that , i would like to point out you even a neater way of inserting a Pivot Table, and that is to click onRecommended PivotTables. These are literally quite smart, they could just give youexactly what you are looking for, or they could assist you get a faster start. So for instance , here Ihave the sum of sales USD by customer name, one among the reports i would like tocreate has sales USD in it. So I'm getting to accompany that, just select it and click on on OK, you get your Pivot Tableinserted on a replacement sheet. this is often the results of my table, it does all the aggregations on behalf of me , I did not have to write down asingle formula to try to to this. Now on the side here weget the Pivot Table fields. But you'll see I even have them organized wherethe fields are on one side, and therefore the boxes that definethe layout of my Pivot Table on the opposite side.
That's an option you'll select here. The default is that the first one, Fields Section that Stacked. Now, if you would like to possess morespace for your fields here, you'll go and choose FieldsSection and Area Section, side by side. Now, what you see here are allthe headers of your dataset. That's why each columnneeds to possess a header. What Excel went ahead and did, once you inserted your Pivot Table, is that it put the salesUSD within the value section, and customer name within the row section. Now, if for a few reason, you do not want to seethe sum of sales USD, but instead you'd rathersee something else, for instance , the typical , you'll click on this sink , attend Value Field Settings and alter your selection from here. So i'm going with Average and OK. Now, you'll also change that from here, right click is yourbest friend during a Pivot Table. If I right click , I also can select ValueField Settings from here, I'm getting to put this to Sum and accompany OK.
Now, another thing you'll getto with a right click is that the Number Format. So for instance i would like to feature 1000 separator to my numbers here, I can select Number Format. So don't select Format Cells because that just formatsthe underlying cell, Number Formats stays with yourPivot Table because it expands. So I'm getting to accompany Number, use 1000 Separator andzero decimal places. Now, you'll adjust andupdate this Pivot Table as you see fit. So for instance , if I wanted to seem atthe different regions, I could plan to addthe region within the filter. then once I click on this sink , I can select a selected region. Or I can activate multi select, which during this casedoesn't really add up , because I just have two items, and you'll also leave it on All. Now, if you opt that youdon't want to possess Region within the filter, you'll bring it to the column section. And now we've customername by region, here. We also automatically get thegrand totals on this side. Now, if you've got another level, so for instance we're going tobring product description to the rows here, we also get subtotals.
Now, here's where you would possibly want to update the planning of your Pivot Table. So for instance , under Grand Totals, youmight plan to deactivate it for either to rows or the columns or for everything if youdon't want to ascertain it in the least . So if i'm going with Off, it completely removes my grand totals. If I just activate it for Columns Only, i buy the grand total on rock bottom here. you would possibly also want to dothe same thing for subtotals. If i choose don't Show Subtotals, it's getting to remove them from here. Now, as for the layout of your report, the layout that I personally prefer is to point out during a tabular form. this provides me column headersfor each of the fields here. Now, another thing you would possibly want to try to to is to fill within the gaps here. Under report layout, you'll select Repeat All Item Labels, which repeats everythingand fills within the gaps.
Now I'm just getting to remove that. And now let's take alook at the button here. So you've got the power to collapse fields or expand fields. If you would like to require away this ability, you'll attend PivotTable Analyze, and under Show click on this toggle here, and it takes away the Buttons, if you click again, it brings them back. Now, the sector list, this one here, if for a few reason you shut that, and you would like to urge it back, you'll do this by going back to point out and selecting the fields list from here. Now, I'm just getting to puteverything back to the way I had it. So i do not want region. Whenever you do not want something, you'll just kick it out, just drag and drop it heresomewhere within the fields list. I also don't need the merchandise description. So I just want customer name by sales USD. One other thing you would possibly want to try to to is to update the header here.
So i do not want anyoneto see sum of sales USD, for instance I just wantthem to ascertain sales USD. once I press Enter Excel doesn't love it if a header here is identicalto a field name here. So if you would like it to be identical, what you would like to try to to is eitheradd an area after the name or before the name, otherwise you can completely change the name. So we could just call this Sales rather than Sales USD. Also for Grand Total, I can just call this Total. Okay, so let's also updatethe design of this Pivot Table by selecting a special style. So you've got tons of options here and you even have the power to make your own Pivot Table style, if you want . Now, one thing I'd wish to do here is to sort this, so good click ,Sort, Largest to Smallest. Now, notice that the PivotTable columns collapse, there's an option for this, you'll actually remove Autofit if you do not want this to happen.
So right click , attend Pivot Table options, on rock bottom here you haveAutofit column with an update, let's deduct that checkmark and accompany OK. Now, one thing I originally wanted was to urge the salespercentage by customer as compared with the entire . And for instance I also want to ascertain the entire sales values here. Well, what I can do is to usher in the Sales USD a second time into my Values field. But now check this out, I'm getting to right click , Show Values As and choose you look after Grand Total. But inspect all the otheroptions that you simply have.
If you get an opportunity , plow ahead and check out these because they will are available handywhen you're creating reports. Now, I'm getting to call this advertisements Okay, so our first report is completed . Before we test whetherthis updates properly if we add new data to our table, let's plow ahead and createour second Pivot Table. The way I personally like better to do this is to repeat an existing Pivot Table, attend the side and paste it in then adjust what i want . this provides me a start because the Pivot Tableformatting comes with. So during this second report, i do not need the sales percentage, what i would like is that the product by sales and that i want it sorted. So let's right click , Sort, Largest to Smallest. Okay, so my Pivot Tablesare actually done. But I just want to point out youone thing before we conclude .
And that's how easy itis to feature Slicer buttons to your Pivot Table. So for instance , for instance for region, rather than having it within the filter here and selecting from sink , what we could do is add it as a slicer. So while it's within the field list, you'll right mouseclick and Add a Slicer. And it adds these buttons here that you simply can organize anywhere you would like . And once you click thereon , it filters your report back to show the values in your slicer. Now, for your slicer you furthermore may get slicer options here so you'll update the planning for this, you'll also update how you would like it shown by adjusting the amount of columns, the peak and therefore the width of your slicer.
Topic Covered
excel pivot tables
excel pivot tables tutorial
excel pivot table refresh
excel pivot tables 2016
excel pivot tables training
excel pivot tables calculated fields
why use excel pivot tables
what is excel pivot table used for
microsoft excel pivot tables
excel pivot tables youtube
excel pivot table tutorial pdf
excel pivot tables 2010
excel pivot tables examples
excel pivot table multiple sheets
excel pivot table sort by value
excel pivot table 2013
excel pivot table median
grouping in excel pivot table
excel pivot tables formulas
excel pivot table date grouping
excel pivot tables advanced
what is the purpose of pivot tables in excel
excel pivot table weighted average
excel pivot tables charts
excel pivot table values as text
excel pivot tables for dummies
excel pivot tables formatting
excel pivot tables practice
excel for dummies pivot tables
excel pivot table group by week
Post a Comment