Create a Pivot Table in Excel

Also, I cannot move the Pivot Table from the new Worksheet it is created in, to my existing Worksheet.

I am running Excel 2011 for Mac.

I have a Worksheet with a small amount of data, and when I click the Pivot Table button it automatically creates the Pivot Table in a new Worksheet. The Pivot Table data is fine, but I want it to display alongside the source data.
I've tried using the Move Pivot Table option and choosing to Create Pivot Table in Existing Worksheet and choosing a source cell alongside my current data, but nothing appears. Also, I checked in Edit menu and found that the Pivot Table appears to have not even been created/moved because there is no option to undo it.

Appreciate any help, let me know if you have any further questions.

Hunter

Hello
Is there a way to create a pivot table template or master copy so whenever I alter the data this template is automatically updated in the format I have set up previously or do I have to buy a special package to do so? It is for MS Excel 2007.
Most of the data comes from the use of drop-down lists.
I've been told just to refresh the table but I've tried this in many ways and for some reason it didn't work.
I have attached a test sheet. The pivot table doesn't look brilliant, I am completely new to this and have to create something in the next day or so.

I hope you can help me.

Monique

Hi - need help with creating a CALCULATED field using a VLOOKUP inside a pivot table - simplified version of what I need to do is below (assume tables start at A1 in the top left corner)

I'm OK with the calculated field (if its possible) but its the VLOOKUP in a Pivot Table I'm struggling with.

PIVOT TABLE Count of 'dog or cat columns' Column Labels Row Labels dog cat AB1 34 54 AB2 55 22 AB3 22 17 AB4 11 34 AB5 76 22 AB6 45 87

From which I want to create the following CALCULATED FIELD:

=VLOOKUP(CONCATENATE($A4,B$3),A2:B13,2,FALSE)

And lookup from these two fields - which I need to place in the pivot table.

LOOKUP FIELDS (need to go into Pivot Table) AB1DOG $5.00 AB2DOG $10.00 AB3DOG $15.00 AB4DOG $20.00 AB5DOG $25.00 AB6DOG $30.00 AB1CAT $2.00 AB2CAT $4.00 AB3CAT $6.00 AB4CAT $8.00 AB5CAT $10.00 AB6CAT $12.00

Thanks in advance for your comment!

Is it possible to create multiple charts from one pivot table - ideally without VBA?

I found a response on another forum saying this was possible and describing the basic steps, but there was no sample data, and I can't get the steps to work with my data.

Attached is a sample data file. My actual data can have about 10,000 to 50,000 rows.

Each record represents an item being sold.

This file has 3 worksheets. One sheet for data, one for the pivot table, and one where I'd like to put an assortment of graphs that get built from the pivot table data.

Specifically, in the Charts worksheet, I'd like one chart that shows Total items sold by month.

I would then like two more charts for Total items, one for the Total sold in the Region = AX2, and another chart for the Total sold in the Region = SCC. This is not all of the Regions, only 2 specific Regions of interest.

Finally, still on this Charts worksheet, I'd also like one chart showing the Profit Score for all the Regions, and then two more charts showing the Profit Score for just my 2 high interest Regions of AX2 and SCC.

Can someone help?

All,

I am creating a Pivot table in Excel using vbscript and I get the below error "Invalid Procedure call" while running the below script:

Set xlptCache = xlBook.PivotCaches
With xlptCache.Add(2)
.Connection = "OLEDB;Provider=MSOLAP.3;Data Source=MT000XSRPD91;Initial Catalog=ICE_CUBE_R21_2011_5_1;Integrated Security=SSPI"
.CommandType = 2
.CommandText = Array("Item")
.MaintainConnection = True
.CreatePivotTable TableDestination=xlBook.Worksheets("AP").Range("$a$1"), TableName="PivotTable3", DefaultVersion=xlPivotTableVersion12
End With

If someone could help me solve the above error that would be great!!!

Thanks in advance
Sathiya

Experts,

I've created a Excel Add-In using Excel 2007 and created a OLEDB connection with this Add-In to create pivot table with that conenction. I've added that OLEDB connection to Add-In (not for activeworkbook) for security resons (If I add the connection to the activeworkbook with help of Add-In my username& password will be displayed in active workbook connection properties).

I'm trying to create pivotCache to create pivot table with Add-In's OLEDB connection, it is raising error(1004: Application-defined or object-defined error).

The following code used to created pivot table cache
ThisWorkbook.Connections.Add "OlapConnection", "", Array("OLEDB;Provider=MSOLAP.3; Persist Security Info=True;Data Source=datasource;Initial Catalog=dbname;roles=Role2005;TimeOut=5000;User ID=UserID;Password=password;"), Array("SampleCube"), 1
Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ThisWorkbook.Connections(OlapConnection), Version:=xlPivotTableVersion12)


Can anyone help me to resolve this issue?

Thanks in advance!
Venkat

Intro
We have some big data sets to summarise using pivots. And Excel page-filter drop-downs are not as helpful as our users would like. I want to see if I can give them what they want in VBA without insisting on 2010 Slicer functionality.

Our problem
There is a definite "hierarchy" of dependent filters for our data - is best outlined in terms of what the analysts at Amazon must have: eg:
Page Filter 1: Dept (Department)
Page Filter 2: Item

And you might have Geography for a row field, Months for Column and Sum of sales as data

The problem is that if you filter on a particular dept the dropdown filter for Item still offers every item from every department. What we want is to just offer the items from that department (ie possible matches given the dept filter)
I need to pick up filter change events and then make some filter values invisible.

Where I am so far:
I can pick up a change in the pivot using Worksheet_PivotTableUpdate
I can tell which filter (if any) changed
I can define a hierarchy of filters (hard-coded)
But how can I tell which items to display in the Page Field (for Item) if I change the Dept Page field?

Thoughts so far: Either:
Duplicate the pivot table. Remove the Row fields and set Item as a Row field. I should then have possible values of Item. Capture them and use to set visible values in the original pivot
OR
Use the property DataBodyRange. Find the column (Dept) and filter on the selected value. Capture the possible values for item (capturing unique)
OR
Is there a way of querying the internal data of a pivot to get what I want?
OR
Has anyone managed to do this already? Am on 2003 but could maybe get upgrade to 2007 for users.

I have seen this question posted many times and not seen an answer - but some indication that 2010 Slicers are the answer. Would be interested in confirmation.


Mike

I created a union query in Access to join two tables (Projections and Actual Sales). The query produces the results I want. I need to create a pivot table in Excel using the union query as the source. When I pull up the data import function in Excel, the union query does not appear. Do I need to do something else? I have tried to create a select query where I select all from the union query and I can find that fine.

When I use this query to create the pivot table the results end up all zeros when I try to sum the values. It creates some crazy results when I show it as count of also.

I can provide anyone with the data in either the Access database or Excel spreadsheet

Hi All,

I am attempting to use a macro to create a pivot table in excel. However, the source data will contain a different amount of rows each time I run the macro. By simply recording the macro I get this code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R5249C95").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

I can see that the problem is that the code builds the pivot table from source data rows 1 through 5249. Does anyone know how to get the macro to set the source data to however many rows I have at the time I run it?

Thanks in advance!

I'd like to generate a summary from columns of data. Normally I'd use a pivot table but I need to automate this using VBA. Basically there is a new entry for every time someone uses a tool and how long the tool is used for. What I need to do is summarize the total time that each person spent with each type of tool.


My data table is laid out as such:

Column A - Names (that can be repeated)
Column B - Tool (can be repeated)
Column C - Time

What I would like to see is VBA creating a new "summary" table using data from the input table similar to the functionality of a pivot table. I've found some code that will allow me to put a list of unique names from column A in a list so I need to expand that to include the tools that were used. After I generate that list, I can use an array formula (unless there's a better suggestion) to calculate the time used with each tool.

Here's the code I have so far. Any additional help would be greatly appreciated.

Sub UniqueList()
Dim rListPaste As Range

On Error Resume Next

Set rListPaste = Range("H1")

Range("A1", Range("A65536").End(xlUp)).AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=rListPaste.Cells(1, 1), Unique:=True

End Sub

Thanks!

Hi All,

I want to make my pivot table uneditable (read-only)! I have to post this sheet on internet and users will download/read it directly from website. I want that those users should not be able to edit any of the contents of the pivot table in other words they should have read only access! Can you please help me?

Thanks in advance!!!

I have a pivot table and I HATE pivot table graphs. I have trouble re-sizing them and they look horrible. I'm in Excel 2003, maybe they are better in 07.

I want to create a regular chart off the pivot table, just as I would if it wasn't a pivot table. Is there any way to do this?

Dear Experts,

With a data i have made a pivot table and the pivot is copied and paste special and values. It is in a MIS format. If i want to have the original format ie. from which i have made this pivot table how can we do this.

ie. Reverse pivot table.

I have opened an email id and a test file is uploaded in the email itself

www.rediffmail.com

user id - exceltestfile
password - testfile

Can anybody help me on this.

I dont know how to copy a file in this forum so opened an external email id.

i want exactly the same of the original data from which the pivot table is made. No consolidation of rows .

Thanks in advance.

Is it possible to create a pivot table directly from a csv file that is longer than Excel's row limit without Access. My csv contains almost 200,000 records so importing over several worksheets would defeat the purpose of avoiding Access. The pivot table feature obviously has the ability to handle records that exceeds Excel's ability to import thanks to MS Query. One would think that querying a csv file would be a piece of cake.

I can go to the pivot table wizard and choose external data and click Get Data. Why not have the ability to pull in a csv there along with dbf, Access and the rest? It has the option of "New Data Source" but I'm at a loss on how to proceed from there.

Any ideas?

Thanks!