We, data junkies, love pivot tables. We think pivot tables are solution for everything (except for may be global warming and that broken espresso machine down stairs).
Today, we are going to learn 5 awesome pivot table tricks that will make you a star.
Click on these links to jump to tips.
Drill down pivot tables | Change Summary from Total | Slice & Dice Pivots | Difference from last month | Calculated Fields in Pivots
(If you are not familiar with basic pivot tables, you should check out this excellent pivot tables tutorial)
1. Drill down on your Pivots with Double click
This is by far the simplest and most powerful pivot table trick I have learned. Whenever you want to see the values behind a pivot field just double click on it.
Lets say, the sales of Lawrence in Middle region is $5,908 and you want to know which items contribute for this total, when you double click on the number $5,908 excel will show a list of all the records that add up to this number, neatly arranged in a new worksheet. Instant drill down.
See this magical trick in action.
2. Summarize Pivot Data by “Average” or some other formula
By default excel summarizes pivot data by “sum” or “count” depending on data type. But often you may want to change this to say “average”, to answer questions like “what is the average sales per product”. To do this, just right click on pivot table values (not on row or column headings) and select “summarize data by” and select “Average” option.
(In excel 2003, you have to do this from “field settings” menu option)
3. Slice & Dice your Pivot Tables with Grace
Re-arranging pivot table layouts is as easy as shuffling a pack of cards. Just drag and drop the fields from row areas to column areas (vice-a-versa) and you have the pivot table rearranged.
Here is a simple screencast explaining the secret
4. Show difference from last month (or year) without bending backwards
We all know that you can show monthly summaries using Pivots. But what if your boss wants you to also include “difference from previous month” as well? Now, dont rush back to source data and add new columns. Here is the right trick to make you a star.
- Just use field settings to tell excel how you want the data to be summarized.
- Right click on any pivot table value, select “value field settings”
- Now go to “Show value as tab” and Change “Normal” to “Difference from”
- Select “Previous” from Base-item area. Leave Base field as-is.
Now, your pivot is updated to show difference from previous column.
Bonus: There are quite a few value field settings you can mess with. Go play and discover something fun. 🙂
5. Add new dimensions to your Pivot Reports with Calculated Fields
Let us say you have both “sale” and “profit” values in your source data. Now, your boss wants to know “profit %” in the pivot report (defined as Profit/Sales). You need not add any extra columns in your source data, instead you can define custom calculated fields with ease and use them in pivot reports.
- To do this, Go to pivot table options ribbon, select “formulas” > “calculated field”
- Now define a new calculated field by giving it a name and some meaningful formula.
- Make sure you adjust the cell formatting so that output of calculation can be displayed (for eg. change number to % format)
(In excel 2003, the formula option is available from Pivot menu in toolbar)
See this tip in action:
What is your favorite pivot table trick?
Do you like pivot tables? What are your favorite tricks? What areas do you face difficulties? Tell me using comments.
Learn More about Pivot Tables:
- Excel Pivot Table – Tutorial (there is a video too)
- Authentic Resource on Pivot Tables from Debra
- Grouping Data in Pivot Tables
Now if you excuse me, I will go check that espresso machine and see if the beans need a refresh.
142 Responses
I like to go to the last blank column in my data and then put in a few extra dummy headers to include in my tables. If you need to make up a column of something calculated from the rest of the data, you don’t need to recreate the whole table. Just rename the column header and refresh the table.
I love Pivots.
One thing few people don’t like is the subtotals, and hiding them is not easy in 2003.
The only option I found was in PivotTable Toolbar.
One option I like is that the row fields or column fields can be dragged and dropped either on another side or can re rearranged too using dragging.
I recently figured the importance of “Show Value As” where I wanted to show the row totals as % of Grand Total of all the rows.
Pivots is a bless
Hi Nimsh,
Put the cursor in the subtotal cell>Rightclick>Hide.
Thanks
or even simpler answer, right click on the subtotaled item and un-check the subtotal option…
good one, I knew all except 4th which is really helpful, thanks chandoo!
I was confused by your #1 example because the animation (screencast) did not match what you wrote:
“….the sales of James in North is $23,012…when you double click on the number $23,012 excel will show a list of all the records that add up to this number, neatly arranged in a new worksheet.”
Instead, I believe it should read, “The sales of Lawrence in the Middle Region is $5908, and when you double-click on the number $5908, Excel will show a list of all of the records that add up to this number, neatly arranged in a row (i.e. $2460 + $1896 + $1552 = $5908).”
@Steve M: Good tip. I do that often to save some trouble during report generation.
@Nimesh… Good tips about hiding sub-totals. Areas like these were made readily available (and accessible in 2007)
@Jas: You are welcome Jas…
@Lee: I corrected the sentence now. I didnt mean the screencast to convey the same. I wrote a simpler example, but showed a more complicated case in the animation. Thanks for pointing it out.
Hey Chandoo I didn’t get option of Calculated fields in Formulae option MS-Excell 2010
Cheers, didn’t know the first one, will come in very handy
Yes, that’s one thing I like about 2007 🙂
And I would like to add one more benefit of using Pivot – Creating chart from Pivot is just a one click job.
I like all the tips , but what I am sad about excel 2007 is we cannot put the same field in value area if it is already there in either row or column area. For example: I have name in row and marks in columns. Now if i want the count of all marks I cannot drop the marks filed in value as it is already there in column. Whereas, in Excel 2003 we can do it.
Can you pl. find some check on this and publish the solution for this.
thnks
Go to “PivotTable Option”
Go to “Display” tab
Check the “Classic PivotTable Layout”
I still use the classic version because of your problem because I need to get the “count” and the “sum” of the same field.
Hope this helps.
Can you not include the same marks field again with setting as Count.
@priyanka: I don’t know about 2007, but it’s present in 2010.
This might be one of those features where MS became mad and removed them from 2007, and then added back in 2010, just like the file menu 😀
Group
PivotTables will group numerical fields (e.g. Age into age bands) and date/time fields (into months etc), although it won’t like it if there is a blank record in the field.
I’m looking to create a Pivot Table that is based off of individual days but I need it to group into weeks. Can you explain how I would do this?
create a column with a function called weeknum with the date as a parameter. (you can define the starting day of the week also, by adding the second parameter viz saturday starting day of week = 16).
Then use the weeknumber column as a part of the pivot table to group.
I have a spreadsheet that looks up data in another spreadsheet using a vlookup formula. I want to use a pivot table to summarize the data on the first spreadsheet including the data pulled in using the vlookup formula it doesn’t seem to be working correctly when I try to sum the values from the lookup fields. Any ideas?
@Shel: Does the vlookup formula return any blanks or errors? Also, are the source values numbers or text looking like numbers?
Found a solution, used a function (ISNA) to fix the lookup formula so that when there wasn’t a match to the lookup formula it would produce a blank cell rather than an error. This allowed the pivot table to calculate the sub and grand totals. Thanks for getting back to me. Shel
I have found that the “ISERROR” funtion is really useful in almost all potential error situations. You can use it rather than the ISNA. The format it simple. You type “=iserror(” whatever formula, vlookup for example, that you would normally use that causes the error, put in a comma and then the result you want if an error happens, such as a zero. The formula will then execute your original formula normally unless an error is found in which case it moves to your default. Super useful and nice to only remember one formula!
I am trying to “Summarize Data By” then “Averages” in a pivot table however I keep getting the same error message that “Averages, standard deviations and and variances are not supported when pivot table report has calculated items”.
How can I fix this. I am fairly new and am tried to remove the calculated fields. The original table I exported from Sharepoint.
I have a hard time keeping my comments to a row field in a Pivot Table. If I re-select the roll/columm, my comment still stay on that spot eventhought my subject data is change. Whatever ideas you all can help to get this remedied is greatly appreciated.
I would like to filter my pivot table based on the values inside. Is there any way to do that on the second or third row label indentation?
really love the 1st one…didnt know before!!
I always wanted to calculate margins within a pivot table, thought there must be a way to do it but did not know till I found your tutorial on the web – great job, thanks
Hi Chandoo,
I have a peculiar problem. I want to show some data points which are for the previous months (not a part of the Current month’s Survey file) along with the current month Pivot table. I need to have Previous months data against respective Parameters in order to show the last month’s performance.
Is there any way to define some stangnent data points in a Pivot Table, which will not change? I am describing this as problem because, not all the Parameters will have values all the months. So, if some of them are missing, the total table will be collapsed as it has some external data which Excel cannot understand and arrange accordingly.
Regards,
Ramnath
Hi,
Is their any way I can filter calculated filed, for example if i calculate a percentage, I just want to display percentage greater that 70%
is this possible ?
Thank You,
Kittu
Hello…
I have the same question as Kittu – filtering a pivot table display based on the value of a calculated field (using Excel 2003).
Thank you,
Shelley
Use Value Filters and select the Calculated field in the filtered by drop down
Hello,
I renamed an existing column header in my worksheet, and then created a new Pivot table. However, the new pivot is still displaying the old column header name and won’t pick up the additional rows (values I added to this renamed column. Why not? How do I fix this? Thanks
@Choy
In the pivot table re-select the data source, not just refresh.
Thanks lot for “Calculated Fields” trick…that was amazing..
In 2010, how can I highlight the row totals? It was easy in previous versions
I have not been able to get my pivot table to display date formats. I have selected the date format within the value field settings. However, only the numerical value will show –even though the date shows up if I go into edit mode. Can you tell me what is causing this and how I can get the date format to show on the pivot table?
Check that your dates are numbers and not text
Select a dare at the source and click on the , button
It should display a number between 30-40,000
If not your dates are text
Hello_
Despite your good explanations I am still confused with calculated fields functions: Can I consider a Calculated field in a PV Table : a field containing data validation or just any cell formula outside the PV Table that uses the PV Table’s results?
Thank you
Joseph
the pivot table drill through usually displays the underlying data in the worst possible format (2003) any ideas on how to have custom formats on the drill though sheet?
We have a pivot table that collects information like “man days” a job will take.
After it gets the “man day” total we want to have the info from the pivot table
go into a cell on another worksheet. However the pivot table number of columns
can change. How do I get the cell from the other worksheet to follow the cell
from the pivot table that moves?
Kathy, if the column moves but the header is always the same, I usually use match function instead of fixed column number. so the formula looks like this:
=VLOOKUP(“reference”;”table”;MATCH(“column header”; “Header row”;0);0)
This nicely takes care of your problem if the header row is always the same. However, I am not sure about the case when you add filters above the core table and your header row moves down. I am sure it is possible and I personally would like to see and answer to that one…
For calculated columns in pivot tables, does anyone know of a way to create a calculated column that references an exeternal cell outside of the pivot table? (i.e. I want to take Sales * A5, where A5 is an external cell) Thanks in advance for the help!
I am having the same problem as Stephanie.. I have a hard time keeping my comments to a row field in a Pivot Table. If I re-select the row/columm, my comment still stay on that spot eventhought my subject data is change. I need the comments to move with the data
Hi All,
I have a lot of usage of Pivot tables in my work…I have a big table,which we refresh everyday and it shows the sales Details.It gets updated everyday cos it fetches data from our ERP.
When I take the pivot of this table,I find 5 more fields listed in”Choose Fields to add to Report” box of the Pivot table Screen.
I did some trials but am not really able to judge how those fields were created extra..!!
Any suggestions,so that I can trace back as to how we can create Extra Fields in the Pivot,that are not in the Table.?(I guess they did some manipulation using Calculated Fields option.I am not sure who did it or when..,)
I lose my customized formatting on pivot table charts (e.g. data series colours on a bar graph) when refreshing data or filtering. Can anyone tell me how to prevent this from happening in Excel 2003?
Paul Coleman
Is it possible to add a calculated field that uses a pivot table row total as part of the formula? I don’t see a way to insert it as a field. I would like to use a calculated field with an IF statement. If the row count total is greater than a certain value display one text string, if not display another. Thanks in advance for any guidance!
Just click the column next to the pivot table results, type “=If(” then point to the calculated field. It is a really long title that starts with “GetPivotData(…
How to do first trick using VBA? Thanks!
@Chandra
Something like Range(“B6”).ShowDetail = True
I tried this but in VBA getting particular cell in this case B6 is difficult and I need this data in the same sheet not in a new sheet.
Thanks,
Chandra Shekar B
Hi Chandoo, Hui.. I have a in the Trick 4 – Difference from last month (or year) .. Tried this and i am getting an #N/A error in all cells. Any way of correcting this?
Hi,
Is there a way to show data filed instead of sum, average etc. I have a sheet wherein I have already calculated the monthly percentage based on certain formulas. But when I am creating pivot table it is either sum/count or averaging the data.
Thankd
Rajesh
Hi Chandoo
I have a problem in the Trick 4 – Difference from last month (or year) .. Tried this and i am getting an #N/A error in all cells. Any way of correcting this? / Any hints on why this issue could be arising..?
How do I get Average total On Raw Grand Totals
YTD-Employee Count Summary -2010
MONTH Data
Jan-10 Feb-10 Mar-10 Total *EMP_COUNT Total *GSALARY-PAY
Designation *EMP_COUNT *GSALARY-PAY *EMP_COUNT *GSALARY-PAY *EMP_COUNT *GSALARY-PAY
Analytical Development – Officer 1 289 1 325 2 614
Assistant Manager 9 4,251 11 4,795 10 5,374 30 14,420
Assistant Operator 1 218 1 260 1 280 3 758
Asst 2 450 2 417 1 175 5 1,042
How do you delete calculated fields from the field list?
It is awesome and so helpful to the beginners.
It sounds simple but I have not been able to do it, ir maybe not possible. I have a column of Org code and org name in my master sheet. I create pivot of finances by org code, but also want org name in the adjacent column for others to review. I normally have to cut paste after pivot is done as I can never them the two columsn in pivot side by side.
It it possible at all?
Suma, you should have no problem with this since, as I understand this, it is one org code per one org name. I have a similar situation where I usually only use customer number in my reports, but some people require a customer name also. All you need to do is to pull both your org code and org name into row headers (in older version pull the fields all the way to the left) and remove automatic subtotals on the first column so the data is not messy..
I have had a similar issue with customer name and customer code and my solution was to create a helper control in the original data that is a formula: =<name>&” – “&<code>”. Then I would use this column as the basis for the pivot table rows
I have added a percentage column in my Pivot table. but do not know how to get the cumalative percentage within the pivot Table. (Excel 2007)
Can you help?
Just trying to explain myself in more details. I need to put in a pivot table the cumulative percentage of an analysis of data. But pivot table doesn’t cumulate. I need help with that. I’m trying to make something like the histogram function of excel but in a pivot table that can change the data through filters and the pivot table calculate the cumulative percentages automatic depends on the information. Currently, I’m calculating it outside of the pivot table, but everytime I refresh, I have to redo it. I am using Excel 2007
Thanks for these tricks.
I read the comments and thought it might be helpful to contribute.
When creating your pivot table use a named range. This is especially helpful if frequent data refreshing is occurring with differing number of records.
If you have contiguous data in a single region take it up a level, go to Formulas.Defined names.Name manager and create a dynamic named range using the OFFSET function.
=OFFSET(ws_data!$A$1,0,0,COUNTA(ws_data!$A:$A),COUNTA(ws_data!$1:$1))
For an explanation of the OFFSET function see,
http://office.microsoft.com/client/helppreview.aspx?AssetId=HP103427399990&lcid=3081&NS=EXCEL&Version=12&respos=0&CTT=1&queryid=ee6cf526%2D29ca%2D43b8%2Dab34%2Dbea22642fa6c
Suma – September 23, 2011
You could create a new column in your master data which is a concatenation of the ORG code and the ORG name eg: =CONCATENATE(org_code,” “,org_name)
If you are using the dynamic named range above you will only have to refresh the pivot table and choose the new field from the field list for the Row Labels.
I like using the Pivot tool. Your examples were good. Though one problem I have is subtotals adding instead of Averaging. Let’s say I’m comparing prices at my store to competitor store. I have the 2 prices and I create a metic of my price less comp price. I then indicate I want that fact to be averaged (not summed). I have my products in groups. At the group level, it is the sum of the details as opposed to average. I looked at Solve Order but it came up blank. Any thoughts?
Is there is any shortcut key to drill down insted of double click…?
@Venkatesh
You could record a macro to do that and assign it to a key stroke combination
@Hui
Thanks, Shortcut is “Properties key + e”
I am trying to refresh a grouped item in the pivot table after deleting some data from the source of the pivot but cannot refresh the grouped pivot field/row or column.
I keep getting an error message that says “This command requires at least two rows of data.” even after I select the whoel data range it will not refresh. Even after I sleect 2 rows, it will not refrsh and posps up the same error message.
Hi,
As you mentioned before, “We all know that you can show monthly summaries using Pivots. But what if your boss wants you to also include “difference from previous month” as well? Now, dont rush back to source data and add new columns. Here is the right trick to make you a star.” but in this way we can have the difference between the columns or the value of the columns but not both, You think it is feasible to have the value of the columns and their difference in the next column on the right as it is considered as a grand total.
thank you in advane.
hi there
I created a calculated field, but it’s always displayed as ‘sum’ even if I define it to be displayed as ‘average’ ‘count’ etc
any clues? … if anyonw could send me the answer to my email I would greatly appreciate it
cheers
Good morning,
I have been creating pivot tables for years, extracting data from an external database. I have now moved to Excel 2010 and am having to tweek some of my older tables. One question I have is how do you add a blank column in a pivot table. I tried inserting a ‘0’ field in my SQL statement but it messed up the pivot table.
Any suggestions?
hi Chandu,
could you please provide if there is any link about formatting pivot table(Making
in-appropriate pivot in an appropriate form).
this blog is very useful thank you very much.
Praveen
I love the pivot table drill-down option in Excel. Ever since switching to 2007, I haven’t been able to use the double click drill-down option on spreadsheets that I’ve saved and reopened. It only seems to work while I’m in a new, active spreadsheet.
Todd
I am trying to create a pivot table which enables me to manipulate data quickly and efficiently but falling on my face here. Here’s my problem:
I create site reports. I carry out safet based inspections and look at a number of areas eg traffic movement, welfare provision, electrical inspections etc. I then categorise my findings by importance, with S being “satisfactory”, C being low risk, B being mid risk, A being high risk. When I produce a pivot table, I can arrange the table so the UK region is a filter and the row lable is the site name, and column labels are the areas I look at (traffic movement etc). The data goes into the data area. No problem so far. However, the data presents me with a table of the cumulative count of my scoring (so 2 S and 2 C ratings would show as “4” in the pivot table. What I want, is to be able to show the data in the pivot table as follows: row as site name, columns as area (traffic management0 then a breakdown under this showing number of S, C, B and A ratings for each area. for the life of me, I cant find a way to do it! I have also tried Microsoft Access, to similar results. HELP!
@Alan
You can have multiple levels of Row/Column
Just drag another field into the Row/Column area and excel will subdivide the Rows/Columns by the new Field
@Alan,
I think it is where you are placing your “risk” field in the pivot table.
From the description above all the fields are categorical. ie not measures.
Hui is correct. You will, also need to drag the “risk” category to the Rows.
For a measure, put a field, say the report’s index number on the Values area, and if it does not do it intuitively, change “summarize value field by” to count with no decimal places.
I need to freeze the first column and the first 6 rows in a pivot table. If I freeze the first column, then I can freeze only row 1. If I freeze the first six rows, then I cannot freeze the first column. Can you help me. Thank you.
@ Mike Fox – Assuming you have a rows of data on rows 1-6 and data on column 1, all you need to do is go to cell B7 then click on View, then Freeze Panes. It will freeze Column1 and rows 1 through 6. Hope this helps.
(MS EXCEL 2010 Version is what im using)
Is there a way to present pivot table data as a percentage of subtotal ?
@ Chamara Lakmal
For just this purpose Microsoft has been good enough to include in Excel 2010, additional choices in the Value Field Settings | Show Values As,
% of Parent [Row, Column, Total]
Good one thanks for the update on Pivot table.
But when i say days it will not take the start date of the month rather it will take the first day to seven days at the end the days per week will be a mistake. Can u please help on this.
Hi Chandoo:
is it possible to use the REPT function in a pivot table inserted calculated field?
Example: =REPT(“|”, A1)
Thank you –
Charlie Epes
Buffalo, NY
Dear Pal
Thanks for sharing the knowledge nice & useful site.
I’m working on large amount of data, and I need to show the values in “Average” & “Median”
As you shown in your Second Example “Summarize Pivot Data by “Average” or some other formula” I got the values in average is there is any other way to get the Median instead of Average
-Kalmeenow
I have 5 years of Data in Excel – Day wise.I have re arranged the data in Pivot Table and in the report filter i have made the date filed in to years.so that data can be looked into year wise.
But I want those yearly data into columns.I need the data in the following manner.
product year 2007 2008 2009 2010 2011.under years columns i need the qty sold in that particular year corresponding to the item.How to get this?Pls help me ASAP
Add the Date field as a Row Field
Then Group by Year
Then Drag the date field to the Columns
If you drag it first Excel may complain that you have too many records
THANK YOU SO MUCH
Hi:
Once I add a calc field, how do I edit the formula?
Thanks-
Charlie
I like the calculated field option but I need to do something a bit more complex. I want to calculate the average differently on the subtotals than the rows, is this possible? I want to average the rows normally, which is easy to achieve with a calc field, but I want to AVERAGEIF(range,”>0″) on the subtotals.
Many thanks in advance for your help.
Hi Chadu,
In excel 2003, i have a pivot table with data for eg. day bucketing :>2 days, 2-4 days etc & count below this heading.
If i double click on any of the value in particular bucket, it opens ALL the colum data on which again i need to filter.
Is there any way of opening the data only for the particular col/value?
hi this is a nice post Is there a way to display the filter criteria in a pivot table for the purpose of showing it on a pivot chart. 5 tricks are very Helpful.
I like using the Pivot tool. Your examples were good. Though one problem I have is subtotals adding instead of Averaging.“Summarize Pivot Data by “Average” or some other formula” I got the values in average is there is any other way to get the Median instead of Average
I still have not understood trick 4 (Show difference from last month). Is it possible to understand it in an easy manner?
Instead of showing the sum or avg, can I just show the value of the field itself? For eg I have the details about the schools, its ranks, city, state, private/public/Charted, score etc in the xls. Can I have row as city, column as whether its private or public and show the rank as the value? I dont want to sum the rank or get the avg of the rank, it doesnt make sense. All I need is to understand quickly a city and its rank along with the school is a public, private or charted one etc
Any help will be greatly appreciated…
Hi,
I am facing the problem while using calculated field in pivot table..
hi
If I am getting the same information month after month and I also want to do monthly comparisons, would it make sense to put all the data into one big list and then do the pivot table on that? Or is there a better way to do it?
@Ann Walsh,
Pivot tables can use multiple ranges, but not knowing how many there is I would be inclined to use one big list as you have said.
Reading between the lines this is a continuing process. I think, to make it easy to update, I would put the monthly worksheets in a single workbook.
Ensure each ROW or COLUMN has a value to identify the Year, Month, etc
Use Data | Data Tools | Consolidate to combine multiple ranges into one
Create a dynamic range (search OFFSET on this page) or use Home | Styles | Format as Table
Create your PivotTable | Table/Range:[Name from previous step]
4th one is different. its very helpful
Hi,
5 tricks are very Helpful. & i need simple help How to merge the two pivot table values. kindly reply the solution for this.
Thanks
Love #4, I didn’t know that. Thanks!
My Question is this ?
In your Explanation about data export from pivat table.
i have to click on each total again and again.
http://www.ehow.com/how_5973777_export-pivot-table-data-excel.html
Boss I have 1 file with 1000+ vender Details.
How will i do click on alls.
Can u plz. help me to do the click work automatically by excel.
and i will save each file with different vender name.
Plz. Help.
Waiting for your reply.
@Birpal
I would look at a different method of retrieving what your after than clicking on pivot tables
Can you post a sample file, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
with details of what fields you need to retrieve and in what format
Thanks for your quick response.
Here is the link of my Sample File
http://www.2shared.com/file/kja6fv6q/Venders_Data.html
There is 3 sheets in the file
1st is full data sheet.
2nd is pivot table.
3rd is Format (in which format i want to export from pivot)
My mean to say that i want export one by one customer with headers in individual files.
as like i have 10 venders master data in 1 sheet.
i have to make 10 different excel file with full details in same format.
kindly help and solve this big problem.
and make it eary or automatically for me.
Waiting for your reply.
Thanking You,
Regards:
Birpal Singh
Kindly Revert ???
I m waiting..
Regards:
Birpal Singh
@Chandoo, great work…
See my post, on Nov 21.
Is there a way to display the filter criteria (value filter) in a pivot table for the purpose of showing it on a pivot chart.
So that people looking at the chart know how its been filtered.
Dears,
For kind support, How to arrange the pivot table for row by row if it’s large file.
Your reply will be highly grateful.
I’m a big fan of pivot table. But there is something that I couldn’t figure it out…
Does anyone know if you can select a group of fields in the Pivottable Field List and drag it into the values area? The only way that I know is that you have to select one by one and drag it into the area that you want.
Thanks, Caraujo
Caraujo, I am not aware of a way to select multiple items at one time (as in CTRL or Shift as with normal cell seclections). In the Pivot Table Field List you can check the fields fairly easily, which may be faster than dragging to the Value fields. Sorry, perhaps Chandoo has an answer for you (that’s why we come to this site, to learn).
# 3 Above – Is it possible to use both Row Labels & Column Labels to arrange data? Poorly worded question, I wish I could share an image of what I have in my Pivot Table, and what I want it to look like (regular cells with various formulas). Not sure how this will appear on screen
A B C D
Product C Product D
East Sales 10 5
Forecast 12 8
West Sales 15 6
Forecast 14 9
It seems like this should be easy and possible, but for some reason I can’t figure it out today. Posted here because this site shows users many concepts and adding this to #3 above would likely help many.
Sorry that looked awful. The difficulty is that all of the data comes from a single row. The row has columns for East Product C Sales, East Product C Forecast, East Product D Sales, East Product D Forecast. Then another row will have all those same columns but for a different region (i.e. West). This may be a matter of how my data is arranged, I can get there if I break the data up with a different row for East Product C and East Product D in another row. It seems like this should be possible within a Pivot Table (without re-arranging data). Food for thought for a future posting on Chandoo, which is a super helpful site (I can’t tell you how much better my Dashboard is because of this site).
Hii Chandoo,
Thanks for post.
The difference from last month works really well..very easy for analysis at just one go without going back to source data and adding columns & formulas.
using excel 2010
need to add date wise (product count ) to values..its for 90 to 120 days ..
so every day i wants to do it manualy by selecting each day from filed.. is there any short cut to move all days to valuse in a single click or any other idea to select all days to values…
Nice tips!
I have a question,
i have too many values that i need to add all of them to the pivot table, and it’s just very much time consuming to drag one by one to the values section. is there a way to select all and add them to the values section?
Sir,
While adding field names to the values quadrant of pivot table, the default value setting is Count. How to change the default to Sum. Otherwise, I have to change the value field setting to SUM individually.
Hi
Really need help in getting the Median values for a categroy in the pivot table that is created using the new worksheet option.
this means i have the base file (data source) in one sheet of the workbook and the Pivot table in the Other Sheet of the same workbook
i have different categories and efforst against them. and need the median of these efforts in the pivot table refer to table below for more clarity
can someone please help on this urgently
Eg:
I need the Area wise Median of efforts(hrs) in the Pivot table. I cant have the Pivot created in the same workseet as the data is huge, hence will have to create it in New Worksheet.
I have listed down the data to be used for the pivot to understand how we can go ahead for the median as the atual data is huge and cant be listed here.
Data source:
Area Efforts (hrs) Role id Type
Mumbai 12 123 Full time
Pune 20 124 Parttime
Delhi 22 125 Parttime
Mumbai 22 126 Parttime
Mumbai 21 127 Parttime
Mumbai 23 128 Parttime
Pune 24 129 Parttime
Mumbai 13 130 Full time
Delhi 12 111 Full time
Delhi 1 112 Full time
Delhi 1 113 Full time
Delhi 23 121 Full time
Hi,
Can someone help me to reproduce data from pivot .
Pivot summarizes data. I want it to reproduce the data in a new worksheet when I double click on any value.
Suppose I have “2” in the values column for a category. When I click the value 2, I want excel to create a new spreadsheet and reproduce the data of the rows from which this value has been derived.
I know this is possible but not able to figure out how.
Any help or clue will be appreciated.
I pull a lot of data for analysis, and I try to keep the file size small by eliminating data fileds that can be calculated in the pivot table. For example, I pull dollars and units and create average price, instead of pulling average price in my base data.
However, my calculated fields sometimes come back blank when I update my base data. I am using an offset formula to ensure all data in rows and columns are captured for the pivot table data. I have read that sometimes when additional columns are inserted in the base data it causes this to occur, but in the most recent update, I added no additional columns. I’d love to know if anyone has found a way to protect calculations from disappearing on updates–whether adding columns or not.
Thanks, Steve
hi,
I am creating a Pivot table based on a data like this
Category of issue and Issue no raised , if issueno is not raised it will be blank My pivot table should have the List of category , No of issues arised and No of issue nos
Category Issue No
——————————–
Application 404
Application
Databases 405
Network
Network 324
Network
Services 122
Services
In the Pivot Table , I placed the Category in the row label , this automatically groups the elements . under the Values , i place again the Category and the Issue No . For The Summarize Value field by i used the Count function
So my Resultant Pivot Table was like this
ROWLABELS Count of Category Count of issueno
——————————————————————–
Application 30 13
Databases 15 6
Network 10 3
Services 33 3
I have to get a 3rd Value column which is ( Count of category) – (count of Issueno)
I tried a lot of things but couldnt get this
can you guide me in this
I love the pivot table a lot but I don’t know how to align my data like your from the example you gave and I am not able to create region and salesmen to look like yours is very sad to me because I manipulate data of such and how to insert formula to me pivot data I use Excel 2007 what will I do.
I need to pull data from a pivot table, not the column or row headings and was trying to select and copy the data.
Are there specific keyboard shortcuts for pivot tables.
I was trying Shift + End and Shift + Right arrow and it is not working.
Thanks in advance for any tips.
I have common cities in column A, total is coming 100 but actual cities are 50. So how can I pivot table for this and show actual count of cities, i e. 50 ?
I don’t want to put formula here only through pivot table.
Regards,
nasa mulla
Hi. I’m trying to learn pivot tables, and I’m running the examples you’ve given above on the sample data you’ve given in the links, as a test. I had trouble reproducing the percentage profit in #5. I typed in the same formula you have above, but received this error message:
“An item name cannot be found. Check that you’ve typed the name correctly and the item is present in the PivotTable report.”
and then immediately after,
“The formula you typed contains an error”.
Can someone please help? Thanks 🙂
Hi
Thanks for the tip about drilling-down tables! I needed that! 🙂 You are absolutely fantastic! It used to take me at least a week to compile my sales data into a report summarising each outlet’s quantity and sales. Now it takes me all of 5 minutes. THANK YOU!!!
I have a small question. My boss has a set format for the tables (red for decrease, green for increase). The drill-down tables come out in blue. Is there a way to change the default colour settings, so I can get it in black and white? Then it won’t disrupt the format specified by my boss.
Thank you! 🙂 🙂
Hi,
Never mind, I found it 🙂
If anyone reading this wants to know as well, I went to Table Tools>Design>Table Styles. I picked a black and white one, right clicked on it, and selected “set as default”.
Cheers 🙂
please have issue with referencing data from pivot report. the error meassge is always #VALUE!
Can anyone help me out?
Thanksssssss
I am relatively new to pivot tables and have even bought a book, and reviewed many tutorials but still cannot seem to get the end result I am looking for.
I have multiple sets of data in the pivot, and the % by column or percentage by row does not work in this scenario. I am looking for percentage by overall total.
Am I missing something? Is there something I can do to get the correct percentages?
I have even tried writing my own formula outside of the table, but then cannot link it to adjust for additional data or link the the chart.
Any and all help would be accepted.
Thanks
Dear sir,
Is it possible to group values(numbers) given in rows as our own desired range..
Since, i got range as given by fixed step. But my requirement is slightly different.
For ex:
In pivoted table:
Age count
45 3
87 2
34 1
56 2
For above data i can easily get group in following range
20 to 30
30 to 40
40 to 50…
..
80 to 90
90 to 100 (for constant steps)
But my requirement is range of type:
20 to 30
30 to 59
59 to 75
75 to 85
85 above..(where steps not same…!!!!)
Dear sir,
Is it possible to group values(numbers) given in rows as our own desired range..
Since, i got range as given by fixed step. But my requirement is slightly different.
For ex:
In pivoted table:
Age count
45 3
87 2
34 1
56 2
For above data i can easily get group in following range
20 to 30
30 to 40
40 to 50…
..
80 to 90
90 to 100 (for constant steps)
But my requirement is range of type:
20 to 30
30 to 59
59 to 75
75 to 85
85 above..(where steps not same…!!!!)
Dear sir,
Is it possible to group values(numbers) given in rows as our own desired range.since, i got range as given by fixed step. But my requirement is slightly different.
For ex:
In pivoted table:
Age count
45 3
87 2
34 1
56 2
For above data i can easily get group in following range
20 to 30
30 to 40
40 to 50…
..
80 to 90
90 to 100 (for constant steps)
But my requirement is range of type:
20 to 30
30 to 59
59 to 75
75 to 85
85 above..(where steps not same…!!!!)
@Prakash
Can you please ask the question in the Chandoo.org Forums
http://forum.chandoo.org/
Please supply a sample file to make the solution more relevant
SUPER! Tons of thanks for this knowledge sharing!
I was tortured by the data analysis in the past few months but now I’m going to torture the data with the tips —- until they are confessed!
Hi ,
Superb Tutorials.
However, I am not getting the 4th point. So, do you have any video to explain the same. It would be very helpful.
Thanks,
Silika
Hi,
So much keen attention that’s great to see how much you learn each and every minute.
Great, keep it up.
Thanks & Regards,
Amitsingh Rajput
+1 647 629 2908
I also find the drill-down by double-clicking very helpful. However I’ve come across a problem. If a calculated field is involved, the drill down results exclude that data and only shows the data related to non-calculated fields. For instance, my pivot table has 4 columns of sums, and a grand total column. One of the columns is a calculated field. When I double-click on a sum in the grand total column, it gives me the data for only the non-calculated field columns and leaves out the calculated field data. (And double-clicking on a sum in the calculated field column only opens up the underlying formula so I can edit it. It doesn’t show me the drill down.) Do you know of any way around this?
My brother suggested I might like this web site. He was entirely
right. This post trul made my day. You can not imagine just how much time I had
spenmt for this information! Thanks!
Hi
I have created a report using pivot table. In this I need to add a column containing some values manual. That values are constant and no changes will occur.
Example :
Budget hours: 2000 hrs for a proj.
I have created pivot table containing hours used month wise eg.1500 hrs.
I need to refresh every month to have % of consumption/Budget + Balance hours in separate columns from Budget hours.
Please give me a suitable solution.