Excel PivotTable Calculated Items

hi I'm Mindy Tracy from my online training hub in this video we're going to look at how to insert a pivot table calculated item and a couple of uses for them are calculated items of siblings of calculated fields and I used to have difficulty understanding when to use a calculated field versus a calculated item but I found a way to get my head around them which I'll share with you here if you want to download the workbook used in this video and get step-by-step written instructions click here to go to my blog post okay let's get started okay so here's my data I've got my regions my month the type whether it's renewal or initial and the value so this is just some sales data I'm going to insert a pivot table I'll put it on this worksheet so we can look at it in context of the data so let's take a look by region and then tight and the value and we'll have the months going across the columns so we can see our data stick down by initial and renewal and we've got a grand total here but let's say I want to know what the renewal sales are as of centage of the total I can do that using a calculated item now the first thing I want to do is select a cell in the field where I want my calculated item added it'll just save me a step later on and then on the pivot table tools I'm in 2013 so I'll go to the analyze tab and then fields items and set but if you're in Excel 2010 or 2007 then you'll go pivot table tools options tab and you'll find fields items and sets aren't calculated items so in here I'm going to give my formula and name now the key here is that I can work with any of the items within one field and I can select the different fields but notice how I was already in the type field so when I opened my insert calculated item it defaulted to time so you can always choose a different one if you weren't in the correct CEL so I'm going to call this centers renewal and the formula I'm going to use is an if formula now you can use functions in here but you can't use functions that require references to cells or array formulas so this is an if formula now the first thing I want to do is check whether renewal is equal to zero if it is equal to zero then I'm going to get a div so I'm going to prevent that by using the if formula and if renewal equals zero and then enter a zero otherwise let's calculate what percentage renewal is of the total so in your mister the / / initial + renewal close my brackets on that and then close it on if and then click Add this will add it to my pivot table when I click OK alright so now I've got my calculated item now the way I think about items calculated items versus calculated fields is fields are the column headings that I have in my source data so these are my fields region month type and value now items are the items within each field so in the type field I have initial and renewal items now by inserting a calculated item I'm essentially adding an item to my source data but on the fly only within my pivot table I'm using the data in my source data to add a calculated item as we have here now I have a couple of problems with this first of all I want to format the percentage renewals as sent atures but I don't want the rest of my data formatted in percentages so I can't go in and change the field setting formatting because I'll end up with percentages everywhere as we can see so that's not what I want instead I just want to format centage renewal items so I could go along and select each one and then on the Home tab choose centage but what I'm going to do is just switch the order of these quickly so that all of my percentages are together and then I'm going to format them and then switch them back and the great thing is they all retain their formatting because my pivot table options defaults to preserve self formatting on update now the other thing that we've got a problem with it is if I add up these three values I get 70 which we can see up here but that's essentially double counting because this percentage renewal shouldn't be part of my total so I'm going to turn off my subtotals and I'm going to get rid of my grand total okay so now we have our pivot table with our calculated item for percentage renewal and I can move that around I can change take out month and I can move type up to the columns and my percentages formatting is retained so I'll just turn us around total on there as well as that's no good and lastly let's just move region 10 down to where it should be if my mouse will cooperate there we go all right so they have calculated items let's look at another example here I've got some general ledger and P&L data so this might general ledger transactions or balances by account and here are the equivalent by the P&L so let's say I want to compare whether my P&L balances are the same as my general ledger balances let's insert a pivot table I'll put it on an existing worksheet on that same one and then we'll look at by source in the columns GL account and amount ok so we've got our balances by GL and our balances by P&L let's remove the grand total we want to identify any accounts that have a difference so we can do that with the calculated item again I'm going to select either of these cells in the column labels and then calculated Isum so what I want to know is the difference between the DL and the piano I'll add that and click OK so now I have a pivot table that is automatically reconciling my differences and I can see I have three so there are a couple of examples of using calculated items in pivot tables there are tons of other things you could use them for and the great thing about putting a calculated item in your pivot table as opposed to calculating it outside of a pivot table is that you can now work with this item as though it's part of your source data so now you have pivot table calculated items and a couple of ways to use them if you like this video please take a moment to click the thumbs up and or leave a comment below if you want to download the workbook click here to go to the blog post where you'll find the download links and step-by-step written instructions and be sure to sign up to our weekly Excel newsletter so you can learn more excel tips and tricks to help you stand out from the crowd and make your job easier so you can go home early thanks for watching

Channel: MyOnlineTrainingHub

Views: 124,934

Rating: 4.9837604 out of 5

Keywords: pivottable calculated items, calculated items, excel pivottable calculated items

Id: KllJjHndlqA

Length: 8min 1sec (481 seconds)

Published: Tue May 12 2015