Excel Tips - Introduction to Pivot tables

Greetings All:

Any suggestions / links where I could find some basic to maybe intermediate tutorials for Pivot tables? It is a weakness in my Excel repertoire I would like to improve.

THANK YOU.

Hi Everyone,

Do you guys have a site similar to the site the word folks have below for
creating and manipulating pivot tables? We would like to learn how to
create pivot tables in excel 2000, we will soon be getting Office 2003 but
will be keeping 2000 also. We also have some money in the budget for some
books. Do you have any recomendations? We work in the medical field so are
not computer geeks yet so we need books that are geared towards non
programmers. Our excel use is mainly for tracking outcomes and not a lot of
heavy math. I have a couple of database books that were recommended for
using Access from the Access group. One is Access 2000 from QUE and
Database design for mere mortals was another and both have helped out alot.
We would like books similar but for Excel.

http://www.word.mvps.org/FAQs/Format...ingColumns.htm

Thanks,
Linda

I'm no expert but I'm a huge fan of pivots and thought I'd share a couple of things I use to help format them and get around certain shortcomings.

Splitting up values and supressing totals on certain columns.

My report required grouping of columns and then a gap between those columns and for certain columns of values to not be summed. My solution below.


By toe

Another thing I found out is the ability to mix tabbed fields with compact fields. That allows you to create the effect I have above with the 'Investments' and 'Equities' headers as compact and the further detailed fields as tabbed.

I also used a macro to hide the pivot headers.

My goal was to create a dynamic report but make it not look like a pivot at first glance

Any tips you guys would like to share?

Hi,

I've made an Introduction to SQL tutorial aimed at Excel users.

I would love to hear feedback on how to improve the tutorial, especially
if you are an Excel user without much existing SQL knowledge.

http://www.querycell.com/SQLIntro.html

Cheers
Sam H

Hi friends!,

I have a data file, and another file with some pivot tables on different worksheets, each reading from the data file.

I am trying to write some code in the data file that tells the relevant pivot table to refresh anytime data changes in the source table. What I wrote doesn't work. I have pasted a sample of what i came up with, below.



Private Sub Worksheet_Change(ByVal Target As Range)
[file_name.xlsm] worksheet_name!.PivotTables("PivotTable1").RefreshTable
End Sub



Could anyone help me correct this code, or give me a better way to do this?

As always, I am very grateful for any ideas and suggestions.

How does the term 'pivot' apply to Excel's Pivot tables and Pivot Charts

Here I want to know whether i can get the Distinct or Unique Count of the different User ids
within the same Journal Entry Number column.



Example The pivot table has MTX-10001158 Journal Entry Number has

USER95
USER94
USER95
USER94

in the line and grlines level.


so i want count=2 for User id USER94
and count = 2 for User id USER95 within the same Journal Entry using Pivot table and so on for all the Journal Entry Numbers.


That is Distinct count of different user ids within the same Journal Entry Number using pivot table and
if possible the sum of count of user ids distict count.(example count =2 for user94 and count for user95 is 2 so sum is 2+2=4)

Please help me with this query.

Thank you.

Hi

Within my macro is some code which sorts 4 pivot tables on a worksheet. Elsewhere in the macro one can select the filters required on the pivot table which refreshes the pivots. However if a filter is selected that contains no data the Macro falls over on whichever pivot does not contain the data.

I guess I need an IF statement around the autosort lines of code supplied below which will move on to the next line if there is no data in the pivot.


ActiveSheet.PivotTables("PivotTable4").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_AREA", ActiveSheet.PivotTables("PivotTable4"). _
PivotColumnAxis.PivotLines(1), 1

ActiveSheet.PivotTables("PivotTable5").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_SECTOR", ActiveSheet.PivotTables("PivotTable5"). _
PivotColumnAxis.PivotLines(1), 1

ActiveSheet.PivotTables("PivotTable8").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_LBC", ActiveSheet.PivotTables("PivotTable8"). _
PivotColumnAxis.PivotLines(1), 1

ActiveSheet.PivotTables("PivotTable7").PivotFields("CLIENT_NAME").AutoSort _
xlAscending, "Sum of RANK_SECTOR_LBC", ActiveSheet.PivotTables("PivotTable7"). _
PivotColumnAxis.PivotLines(1), 1


Please help
Thanks
Charlie

I am trying to write a macro that changes the source data for multiple pivot tables on multiple sheets. here is what I have so far. However it seems to only update 1 Pivot table per worksheet and crashes once it hits a sheet without any pivot table. Any help is appreciated.


Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
ws.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"Data_063011"
Next pt
Next ws
End Sub

I recently constructed top A Top 10 Pivot Table for each Quarter in 2008, so one for Q1, one for Q2, one for Q3, and one for Q4.

The list shows our top 10 customers and the total amount they purchased for that Quarter.

I am trying to find a way to show if the customers repeat in different quarters.