Pivot Table

Pivot Table

A pivot table is an incredibly powerful tool in Excel that can be used to analyze, explore and summarize your data.

Pivot Tables are in their element with huge amounts of data and allow you to create reports from this data in seconds.

Below is a list of our best tutorials on Pivot Tables.



Leave a Comment

Current day month ye@r *

{ 26 comments… read them below or add one }

Minglei Chen July 9, 2014 at 6:38 pm

Hi Mynda,

I created a pivot table, and inserted few new columns next to pivot and put in some comments on each row next to pivot table.

The root cause of my problem is that those edits on each row outside pivot table is not linked to the row within the pivot table, so when i add some new data and refreshed the pivot, the information move to new rows in pivot table, but the “matching” information outside pivot stayed on the old row, resulting a mis-match.

I would like to have those comment to move together with the pivot, my question is how do i tag the comment on each row outside pivot with each row within pivot? or is there better alternative to achieve what i want to do? Many thanks

Reply

Catalin Bombea July 9, 2014 at 9:32 pm

Hi,
Please upload a sample pivot table with details, i have to see your formulas , it’s hard to give you an advice without seeing the data structure and a desired outcome.
Use our Help Desk to upload a sample file, i’m sure we can find a solution for you..
Catalin

Reply

Charles March 27, 2014 at 10:33 am

This is my question. I use Pivot Tables. I like them. But this is my problem.
I open a file on a server at work. Save it to excel. I then create a query to this file and select the columns that I want in my query. Once I do this I open the table, I insert a column. I type in some data.
I save it.
The Next day I open the file on the server. Save it to the excel file. Close it.
I open the excel file that was created from the query. I hit refresh.
It wipes the data I type in out.

Is there a way to prevent this from happening?

Reply

Mynda Treacy March 27, 2014 at 3:20 pm

Hi Charles,

I can’t see anywhere in your process where you write the new data back to the ‘file on your server’. So, the next day when you open the file on your server you’re looking at the original data, not the new updated data. Refreshing the query brings in the original data from the server again, not the updated data you created in your Excel file.

I think it’s the process of ‘inserting a column and typing in some data’ but not sending that data back to the server that seems to be the problem.

I hope that helps you troubleshoot a solution.

Kind regards,

Mynda.

Reply

Victor Novoa December 2, 2013 at 1:38 pm

Hi Mynda. Going through the Dashboard course slowly. I would like to ask if anybody has a dashboard to dynamically capture and present beginning budget, current usage and amount left of overtime for 8 units (users) with a total of 155 employees. This dashboard has to be updated and presented every two weeks. I already have a tabular excel file with all the units, employees, bi-weekly pay periods and overtime hours worked for the first two periods of this fiscal year. I would like to show usage by unit and employee, regardless of the unit, how much overtime used and how much budgeted overtime remains at the end of each pay period. Furthermore, I would be adding tasks by unit and employees and show which units/employees are the most efficient when performing these tasks with overtime. I would appreciate any ideas.

Reply

Victor Novoa December 2, 2013 at 1:42 pm

Mynda, the 8 units and 155 employees is the population I am working with right now. I can try to adapt any templates or similar examples to my case. I have gone over the examples in the course and I am far away from producing something similar. Thanks.

Reply

Mynda Treacy December 3, 2013 at 12:55 am

Hi Victor,

I don’t know of any templates that are similar to your requirements that I could recommend. Since you haven’t finished the course I recommend you complete it so that you have an understanding of how you can use Excel to create the dashboard that you want.

Once you’ve completed the course start by mocking up the different measures you want to report in your dashboard on a piece of paper. This will give you a layout for your dashboard and you can then go about analysing your data and creating your charts and tables required to complete the dashboard.

If you have any specific questions along the way I’d be happy to help. You can email me via the help desk.

Kind regards,

Mynda.

Reply

Victor Novoa December 3, 2013 at 12:54 pm

Thanks Mynda.

Reply

Philip Treacy December 6, 2013 at 3:55 am

You’re welcome, Victor :-)

Jeremy Creech October 7, 2013 at 1:19 am

Hi Mynda,

I am currently working my way through your excellent Dashboard Course and have come across an issue which I haven’t been able to sort out so would appreciate your assistance.

I have a data table set up with columns containing year, month, date and then columns with the various types of material we mine by each pit (eg Pit 1 Waste, Pit 1 Ore, Pit 2 Waste, etc) with the quantities going down by row against the relevant year, month and date. Is there a way I can set up a slicer in a single pivot chart so that the user can select the desired material mined (ie Pit 1 Waste, etc) against the relevant year and month slicers, which work fine, without me having to create a separate chart for each material type mined.

I have played around with the format of the data table (I have adopted your suggested tabular format) but so far had no success other than getting one or more of the selected material types appearing in the one pivot graph at the same time.

Alternatively, is there another way to achieve the desired result.

cheers Jeremy

Reply

Mynda Treacy October 7, 2013 at 10:39 pm

Hi Jeremy,

Great to hear you’re enjoying my Dashboard course :)

You need one column that contains the ‘type of material’. Not separate columns.

I would also recommend having just one column in your source data that contains the date. When you split the year, month and day into separate columns Excel may give you erroneous results. For example, March 2012 and March 2013 are both ‘March’ because you have the year in a separate column.

I would put your date into one cell and use the Group tool in the PivotTable to group them into months and years.

If you’re still having trouble please send me your workbook via the help desk and I’ll take a look.

Kind regards,

Mynda.

Reply

Eric Stut June 12, 2013 at 5:35 pm

Hi Mynda and collegues,

Your site, blog posts and tutorials are very helpfull. Nice…thank you!
I’m rumbling around to find a way to add columns in a Pivot table. Situation: 40 columns named A1, A2, A3 till A10 en then columns B1, B2, B3 till B10 and columns C1, C2, C3 till C10 and D1, D2, D3 till D10. I’ve to add column A1, B1, C1 and D1. Then A2, B2, C2 and D2 and so on. Is there a pivottable trick??
Thanks in advance for a solution.
Kind regards. Eric

Reply

Carlo Estopia June 12, 2013 at 8:15 pm

Hi Eric,

You just need to add a calculated field.

Here’s what you need to do:
1) Select/Activate your pivot table.
2) By selecting it, it will expose the Options ribbon.
3) Select the Options ribbon.
4) In the Options ribbon, select Formulas-Calculated Field
5) In creating the formula, simply add your columns/fields like this:
=c1+c2+c3+c4

Make sure that there is a unique field/description in your source data
as this will segregate your calculated field results.

For example:

ID	Col1	Col2	Col3	Col4
A	1	2	3	4
B	1	2	3	4
C	1	2	3	4
D	1	2	3	4

Cheers,

CarloE

Reply

said May 27, 2013 at 12:08 am

Its Very Useful Site I Will tell all my friend

Reply

Mynda Treacy May 27, 2013 at 1:03 pm

Thank you, Said :)

Reply

nisha verma April 24, 2013 at 6:27 pm

gud its help me lots

Reply

Funmi April 3, 2013 at 7:51 pm

I find your website quite useful and I have added it to the list of my favorites at work and even on my personal laptop.

Thanks for all the tips.

Reply

Carlo Estopia April 4, 2013 at 11:30 am

Hi Funmi,

On behalf of Mynda,
You’re welcome!

Cheers,

CarloE

Reply

Anita March 22, 2013 at 10:18 am

Hi — I would like to get the median (instead of mean) calculated in my pivot table because I have highly skewed data. Do you know of any effective work around? Thanks.

Reply

Carlo Estopia March 22, 2013 at 11:35 am

Hi Anita,

I have tried to search for answers but the best thing to do is calculate median outside of the Pivot Table.

There may be some VBA workarounds here but it is outside the support we are providing. The good news is that
we are currently building a VBA program for our site.

Cheers.

CarloE

Reply

Nik March 5, 2013 at 9:06 pm

I have many pivot tables and want to assign dynamic print area to all those. The print area should change automatically with the change in data. How to do it. There may be null row values in some case.

Reply

Carlo Estopia March 6, 2013 at 12:22 am

Hi Nik,

Sounds so vague to just say it.
Please send it to Help Desk and let’s see what we can do about it.

Cheers.

CarloE

Reply

Hamilton November 28, 2012 at 11:03 pm

I have two sets of spreadsheets which at the end of the month i have to integrate, the challenge is for me to integrate the two i need a sum if formular which incorporates two variables

Reply

Mynda Treacy November 29, 2012 at 7:31 pm

Hi Hamilton,

You can use either SUMIFS or SUMPRODUCT.

Kind regards,

Mynda.

Reply

Shekher September 20, 2012 at 3:27 am

Please provide me pivot table how to edit and ennter calculation field, and also details of array formula all type, and all details of database function.

I am regular reader of this site.

Thanks
Chandrashekher

Reply

Mynda Treacy September 20, 2012 at 6:03 pm

Hi Shekher,

You can find an index of Excel formulas and other tools here, including arrays, database functions and the like.

I’m not sure you’ll find a tutorial on how to enter a calculated field in a PivotTable though. I am yet to write about that, sorry.

Kind regards,

Mynda.

Reply