Excel Basics #20: Data Analysis Pivot Tables PivotTables

Hello, (xl2010)

I spend a lot of time adjusting data formats in tables and pivottables.
I need only very few formats, mainly those: "# ###", "##,##", "%" .
My data are generally retrieved from sqlserver views and moved to excel tables.
From those tables, I build a lot of pivottables.
When creating new pivottable (or new tables), I need to adjust the format of the displayed data, even though the formats I need are most often unique for each field.

Could you give me some trick or some advice about how to avoid wasting too much time to care for formats? Are there some built-in ways to propagate formats automatically?

Thanks, and have a nice weekend,

Michel

Hi All,

I have created a Pivot table as below:

Milk 1 Pint Whole

JAN FEB MARCH APRIL etc
Shop 1 10 12 14 20
Shop 2 8 7 6 2
Shop 3 10 10 10 10
Shop 4 20 30 40 50

Although i can manually look at the sales figures and work out if they are buying less or more products i would like a a more automatic way of finding out if customers sales are going down on the up or staying constant. Does anymore have any ideas on how best to acheive this.

Many Thanks

Mike

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.

I am trying to find out how to make a three dimensional sensitivity analysis using data tables.

Example: 1,2,3 would be on right hand side as columns. 2,3,4 would be on the left hand side as columns, row would XYZ variables --output would be in the middle.

Any help or any link I could go to. Thanks much

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

Hi All,

Is there any way in a pivot table to repeat data down for the fields?

State Town Population
NSW Sydney 50
Parramatta 5

to:

State Town Population
NSW Sydney 50
NSW Parramatta 5


I have looked through the settings and can't seem to find anything.

Does anyone know of anything?

vaneagle

Hi,

Has any body so far retrieved Excel pivot data in to ADO.net?

If so please let me know your approach. For some reason i get more
exceptions to my pivottable/cache objects than values.

I am using the below namespaces. Pl. let me kowif i missing any?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Office = Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data.Common;


And here is all the code: i have a ui from which i get the location of the
excel spreadsheet.

namespace _
{
public partial class Form1 : Form
{
public Excel.Application xlApp;
public Excel._Workbook xlWkBk;
public Excel.Workbooks xlBooks;
public Excel.Sheets xlSheets;
public Excel.Worksheet xlWkSheet;
public Excel.PivotTable xlPvt;
public Excel.PivotCache xlCche;
public Excel.PivotFields xlFlds;
public Excel.PivotItems xlPvtItms;
//public OleDbConnection cnExcel;
//public OleDbDataAdapter daExcel;
public DataTable dt;
public DataAdapter da;
public DataSet ds;
int i,j;

public Form1()
{
InitializeComponent();
xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!");
System.Windows.Forms.Application.Exit();
}
xlApp.Visible = false;

}

private void button1_Click(object sender, EventArgs e)
{
//xlBooks = new Excel.Workbooks();
xlWkBk = xlApp.Workbooks.Open(textBox1.Text, 0, true, 5,
System.Reflection.Missing.Value, System.Reflection.Missing.Value, true,
Excel.XlPlatform.xlWindows, System.Reflection.Missing.Value, false, false,
System.Reflection.Missing.Value, false, true,
Excel.XlCorruptLoad.xlNormalLoad);
if (xlWkBk == null)
{
MessageBox.Show("ERROR: EXCEL file couldn't be started!");
System.Windows.Forms.Application.Exit();
}
xlWkSheet = (Excel.Worksheet) xlWkBk.ActiveSheet;
xlPvt = (Excel.PivotTable)xlWkSheet.PivotTables(1);
xlCche = (Excel.PivotCache)xlPvt.PivotCache();
MessageBox.Show(xlCche.BackgroundQuery.ToString());
label1.Text = xlCche.Connection.ToString();
textBox2.Text = xlPvt.Name.ToString();
}

private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
xlWkBk.Close(false, System.Reflection.Missing.Value, false);
xlApp.Workbooks.Close();
xlApp.Quit();
}
}
}

Thank you.

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