Calculated Fields in Pivot Tables
This is a simple example of how to use a calculated field in a pivot table. I use the Orders table from Northwind.mdb to summarize the orders. I add a calculated field to show me the Extended Price for each product on the order. I’ll be going start to finish assuming you know nothing about pivot tables, so if you like screen shots, you’re in luck. If I gloss over any step, check the other posts in this category.
Select Pivot Table and Pivot Chart Report… from the Data menu. In Step1, select External Data so we can bring in the Orders table.
Select the Get Data button, select Microsoft Access 97 Database (or whatever version you like), navigate to the Northwind.mdb (Mine is in C:\Program Files\Microsoft Office\Office\Samples\). Once there, find the Order Details table (okay, I was less than forthright earlier).
Move all the fields over to the left listbox and click the Next button four times - or whatever it takes to return data to Excel without adding anything more. Excel should now tell you data’s been retrieved.
Click Next, then Finish to get something like this
Drag Order ID to the page field; Product ID to the row field; Unit Price to data items
 |
 |
 |
Now the dragging gets a little tricky. Drag Quantity to Data Items like you did with unit price, but you don’t have those big letters to guide you home anymore.
Hover Quantity over the column and the column is highlighted. Release and you’re all set. Now do the same for discount.
I’m going to use the page field drop down to make the pivot table more manageable. I’m also going to format the last column for readability. Neither of these steps are necessary, however. Select anywhere in the pivot table and from the PivotTable toolbar, select Formulas > Calculated Field.
In the dialog, build the formula you want from the available fields. If you don’t have Field1 and Field2 in your available fields, then you weren’t trying to screw things up like me. Someday I’ll learn how to get rid of those, but for now I’ll just ignore them.
Now you can see the total for each product, by order.
It’s ugly and the totals don’t work, but what do you want? It’s not Publisher. We’ll learn how to make it pretty in another post (and I’m not kidding when I say we).
Just a word of warning that when calculating these fields, Excel does the totalling of each constituent field before calculating this field. In other words, Excel calculates:
[Sum of Quantity] * ([Sum of Unit Price] - [Sum of Dicsount])
rather than:
Sum of [Quantity * (Unit Price - Discount)]
So the only time we get the correct answer is when the pivot table is only showing data in each cell derived from a single record.
In this case (and most cases), it would be better to include the calculation within the SQL query.
You know what is needed, an article on how to retrieve external data. Getting the data is difficult. We are doing more and more of this at work and this process is not easy. I am hoping that Excel12 has easier and better connections with outside sources so that we can do more of this.
speaking of the devil, is there a way to make pivot tables refresh on their own?
Instead of using the blue boxes on the worksheet, I prefer to use the layout option. It’s on the last dialogue box. The spaces aren’t lost for the various drop fields.
Charlie,
I believe it would require several articles to cover the whole field of manage external data.
Recently I reviewed two books that I find to be of interest here:
Conrad Carlberg
Managing Data with Excel
QUE
Bill Jelen
Pivot Table Data Crunching
QUE
I’ve also made an online short review of it at VBAX:
http://vbaexpress.com/forum/showthread.php?t=4179
Carlberg’s book offer more and a wider knowledge in general about manage data while Jelen’s book set explicit focus on the Pivottable-tool.
The combination of these sources and present/future articles here at DDE would propably give You a good platform
BTW, could You be more specific about Your needs so that we can see if it suitable to make any post about them here?
Personally I find the limitations / lack of using ADO / ADO.NET together with some built-in tools to be frustrating and I can only hope it will be a better situation in coming versions.
Kind regards,
Dennis
If you want pivot tables to refresh on their own, you could look into the Application.OnTime method, or Worksheet_Calculate or Worksheet_Change events.
Dennis-
Believe it or not I am walking around with Carl’s book in my brief case. Still I have questions. I will say that instead of working on Carl’s book I have been spending a lot of time working on Windows XP and spyware issues. I am getting to be an expert in this area. Not by choice. I was at your website but I didn’t find a place for comments or notes. Do you have one?
Charles,
In the early days of the IT-evolution there was a vision about the “paperless office”. We ended up in a situation where we produced more paper then ever and still do…
Today the vision is the wireless online 24/7 life but it looks like we will be forced to disconnect us due to the endless issues with all kind of computer-related threats, security and cleaning-up processes…
I’m quite tired of the endless updating-processes, the scanning-processes and the never ending production of new threats. It will propably be
the #1 reason when I decide to unplug myself…
Anyway, I’m glad that You bought the book. I have not had the time to make a translated extraction in English from my Swedish review. It will propably be posted at VBAX.
The general impression of Carlberg’s book is very good as it is well written and gives a clear “picture”. The introduction chapter “Misusing Excel as a Database Management Tool” is the best chapter, at least for me.
I would had welcome a section about the quality of the external data and also a more wider approach when it comes to databases in use and not only set
focus on “Access”-databases. In practice, we all face issues with the data quality, especially from central large databases. These databases are usually likely to be Oracle, DB2, Infomix, MySQL/MaxDB and SQL Server 7.0/2000.
That’s my 2c-opinion in short.
Pls let us know what You think about it
Kind regards,
Dennis
I think there is an error in the formula for the calculated field. Shouldn’t it be this?:
=(UnitPrice*Quantity*(1-Discount)/100)*100
(This is the formula used in the Northwind Database “Customer Orders Subform2″ form)
I should have said that the formula is in the “Order Details Extended” Query. In this Access query the SQL formula works out to be this:
CCur([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100 AS ExtendedPrice
I have no idea, however, why there is that ‘*100′ at the end of the formula. It seems like multiplying by 100 shouldn’t be necessary.
Will: You’re probably correct. I couldn’t tell if the discounts were supposed to be a percentage or pennies. I can’t imagine giving someone a 25% discount, so I went with the pennies option.
As for the 100, I don’t get it either. It seems they shouldn’t divide by 100 in the formula, then they won’t have to multiply by it.
[...] Not the original ref, but a good source nonetheless [...]
To refresh a pivot automatically, go in to Table Options and check “Refresh on Open”.
I just recently began using pivot tables and had a quick question on your example above. I used the calculate field option that you showed and it works great except for one small detail, I want to see the max of the field instead of the sum. The Field settings option for this new calculated field appears to be disabled. Is there any way around that? Also can a pivot table be set to have a different default field settings option?
Thanks
Die_Another_Day
D_A_D -
That “small detail” is really a major shortcoming that prevents me from using calculated fields in most cases. No being able to summarize a field in any way that makes sense in my worksheet is a severe drawback. A related issue is that the field is not calculated on a per-record basis, but on an overall level within the block of the pivot table.
In some cases I’ve resorted to using intermediate pivot tables that in turn serve as data source to the one I wanted to make from the original data.
- Jon
Jon, I’m not sure what your field is but I personally find that Pivot Tables are geared only for financial purposes which make them difficult for me to use in my technical field. I ended up writing a macro to create my pivot tables so I can change the field settings, chart scales, etc… to my liking automatically. It wasn’t the prettiest solution but it worked. I’m hoping the next version of Excel will add more functionality to pivot tables. Another “small detail” that annoys me is that I can’t use a secondary axis with a pivot chart and get it to stay.
Die_Another_Day
D_A_D -
I’ve got a technical background: I trained as a research scientist (doctorate in metallurgy, of all things), then worked for 15 years as a researcher and then a few more as production engineer. Now I’m a full time Excel developer.
The “intermediate” pivot tables I mentioned, and many others, are generated through VBA. I don’t use pivot charts, because they’re even less flexible than calculated fields. I prefer to make regular charts from pivot table data, often making use of VBA to track the changing alignment of the pivot table.
- Jon
Hey thanks for the idea that approach might help me greatly when I need to use my secondary axis. How do you handle the pivot table changing sizes with a regular chart? Do you basically have to rebuild the chart or do you use VBA?
D_A_D
“Do you basically have to rebuild the chart or do you use VBA?”
Yes.
By that I mean the chart is usually hosed. If it’s a well defined pivot table in a workbook that is to be used by a client, I will write code to delete and reconstruct any charts. Otherwise I just do it by hand. I’m pretty good at making charts quickly.
Dear Stephen
is there any other solution for the below problem that you stated
bcos i am facing the same problem my formula something like =if(unit price>50,unit price - discount, unit price)
thanks
utham
Stephen Bullen Says:
August 9th, 2005 at 2:48 am
Just a word of warning that when calculating these fields, Excel does the totalling of each constituent field before calculating this field. In other words, Excel calculates:
[Sum of Quantity] * ([Sum of Unit Price] - [Sum of Dicsount])
rather than:
Sum of [Quantity * (Unit Price - Discount)]
So the only time we get the correct answer is when the pivot table is only showing data in each cell derived from a single record.
In this case (and most cases), it would be better to include the calculation within the SQL query.
Any way to have a Countif from your data calculation in a pivot table? Data above or below the spec limits is what I want. I could make another column in the data table that is summed in the pivot table but that seems rather clunky.
Thanks!!
I have a Pivot Table with zero values, I want to hide these particular rows … how do I do that?
Could I use Calculated Items and/or Fields?
Ta
K
Me too. I want to have the pivot table present only the non-zero exceptions (Used for variance report). Hide the rows that contain only zeros.
Hi, all
I am new to this forum.
I want help on Pivot table.. can i use IF formula in calculated field if i want to calculate something on the basis of year in row field and use some percentage. e.g Row field is having 2004,2005,2006
and i want to calculate =If(”year”=”2004″,”Net”*93%,if(”year”=”2005″,”Net”*95%,”Net”*100)) and so on…
Kindly assist.
I have the same question. I tried but it did not work.
wrt gohar’s post of 9th May, I had some success with Excel 2003.
Excel clearly accepts formulae in pivot table calculations because it normalises recognised formulae to upper case.
My formula was “=IF(’Publication Frequency’=0,NA(),’Review Date’-'Latest Measures Reported’-'Publication Frequency’)”. It evaluates “true” for both missing and zero value ‘Publication Frequency’s, in which case it returns “#N/A”.
I created a calculated percentage field in a pivot table. Is it possible to edit the field, or do I have to delete it and retype the whole thing. I need to get rid of the DIV by 0s. I know how to do this, I’m just curious if I have to start from scratch.
trying to find out if the “IF” function can be used in a calculated field in an excel pivot table