Pivot Table Quick Tip – Using Pivot Table to Separate Data

So the poor little manager looks at his computer and says, "how to heck am I supposed to separate this data down to individual sheets for each department." The manager in question had just been handed over a large file containing financial information for all the departments in his organization and was asked to break it down so that each department's data should end up in a separate sheet. ...

Continue Reading

Pivot Table Quick Tip - Using Pivot Table to Separate Data

Hide Pivot Table Field Header – Pivot Table Quick Tip

Posted on 12 November 2009

Hide Pivot Table Field Header - Pivot Table Quick Tip
When you create a pivot table in Excel 2007, the pivot table field headers get inserted automatically. The headers make the job of slicing the data easier but they also have an unintended side effect - they make the first column to become much wider than the rest. One way out is to adjust the width of the first column to make it match the other columns and then disable ...
Continue Reading

Comments (1)

Pivot Table in Excel 2007 – Comparison with Excel 2003

Posted on 11 November 2009

Pivot Table in Excel 2007 - Comparison with Excel 2003
Pivot Tables underwent a bit of a change in Excel 2007. Our reader Miron commented on the Pivot Table section of my review of Excel 2007 saying, "Excel 2007 ruined a really good tool." Excel 2007 did shake up a lot of things that we were comfortable with - including charts and pivot tables which were probably some of the most frequently used features. But not all have been misses and I think changes have made the pivot tables ...
Continue Reading

Comments (7)

OLAP Cube in Excel and Pivot Table From External Data

Posted on 05 November 2009

OLAP Cube in Excel and Pivot Table From External Data
OLAP Cubes provide a convenient way to crunch data in Excel. Imagine a Pivot Table pulling information from thousands or millions of data points with hundreds of columns and you being able to share this humongous data set with colleagues using an Excel spreadsheet that weights nothing more than a few Kilobytes. Add to this the ability to update the source data while the users get to look at that information real time. That’s what a Pivot Table based on ...
Continue Reading

Comments (8)

Pivot Table Quick Tip – Group By Year, Quarter, Month and Date Field

Posted on 02 November 2009

Pivot Table Quick Tip - Group By Year, Quarter, Month and Date Field
It's monday morning and you've got a pivot table on your hands - its source data consisting of three columns – product, date and sales volume over a preiod of time. A pivot table created using this as underlying data may typically have the date running across rows, the product category as the column or page field and finally the sum of sales appearing in the data field in ...
Continue Reading

Comments (11)

Calculated field and Calculated Items in a Pivot Table

Posted on 11 September 2009

Calculated field and Calculated Items in a Pivot Table
Calculated field and Calculated Items of a pivot table get their values from the result of a formula. Calculated Field A calculated field allows the user to insert a new data field into the pivot table – one which does not exist in the base data but gets its value from a formula. The formula in turn can include existing fields, numbers and other arithmetical operators. Let’s understand this a little better. Whenever you create a pivot table, by default, the list ...
Continue Reading

Comments (9)

Pivot Table in Excel – How to Create and Use Pivot Table

Posted on 15 July 2009

Pivot Table in Excel - How to Create and Use Pivot Table
Pivot Table Basics Learn How to Create Pivot Table in Excel Things that can be done with Pivot Tables in Excel Pivot Table Download What is a Pivot Table? A pivot table is a table that stores the summary of an underlying data set in a condensed form. A pivot table consists of rows, columns, page and data fields each of which can be moved around interactively and help the user isolate, expand, group and sum the data in real ...
Continue Reading

Comments (9)

How To Create Pivot Table Using VBA

Posted on 28 June 2009

How To Create Pivot Table Using VBA
How to create a pivot table using VBA? Assuming that the data for the pivot table is laid out like this, then all you need to create a pivot using vba are there four lines of code: ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:= "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1) ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Name" ActiveSheet.PivotTables("PivotTable1").PivotFields("Name").Orientation = xlDataField The pivot table so created using the VBA code above can be ...
Continue Reading

Comments (11)

GETPIVOTDATA Formula Excel – How to use GETPIVOTDATA Function with Pivot Table

Posted on 10 June 2009

GETPIVOTDATA Formula Excel - How to use GETPIVOTDATA Function with Pivot Table
Syntax of GETPIVOTDATA Formula Example of GETPIVOTDATA Formula Making GETPIVOTDATA Formula Dynamic GETPIVOTDATA formula is used with a pivot table to fetch values from it. GETPIVOTDATA Formula Syntax The GETPIVOTDATA Formula has the following syntax: =GETPIVOTDATA (DataField, PivotTableCell, FieldName 1, Item 1, FieldName 2, Item 2, ... FieldName N, Item N) However before we delve any deeper in the GETPIVOTDATA formula, let's get back to basics for a minute. Basic structure of a pivot table A pivot table is based ...
Continue Reading

Comments (13)

How to synchronize two or more pivot tables using VBA

Posted on 08 June 2009

How to synchronize two or more pivot tables using VBA
One often comes across situations when one wants to synchronize two or more pivot tables. The problem arises when two or more pivot tables have the same underlying data set but show different views to the user. In such cases, combining both of them without losing data separation is not really an option. I wrote this small piece of VBA code a few months back that can help synchronize pivots across ...
Continue Reading

Comments     

How to Format a Pivot Table in Excel

Posted on 19 May 2009

How to Format a Pivot Table in Excel
In this post you will learn about how to format pivot tables in excel. Formatting a pivot table is often the step that people tend to miss out when presenting information using pivot tables. By formatting a pivot table you can make the data stand out, occupy less screen 'real estate' and make the user's life simpler. (You can download a copy of the format a pivot table in excel workbook with an example here.) How to Format a Pivot Table? Let ...
Continue Reading

Comments     

Refresh All Pivots in a Workbook or Worksheet

Posted on 08 March 2009

Refresh All Pivots in a Workbook or Worksheet
The following code will refresh all pivot tables in an excel workbook or a worksheet. There are two ways to achieve this: Refresh all pivots in Workbook - Method 1 Sub try1() For Each pt In ActiveWorkbook.PivotCaches pt.Refresh Next pt End Sub Refresh all pivots in Workbook - Method 1 Sub try2()...
Continue Reading

Comments (1)

Alternative to Pivot Table in Excel

Posted on 22 November 2008

Alternative to Pivot Table in Excel
In one of my previous posts (How to read data from Pivot Tables using VBA), I talked about using the getpivotdata() function in VBA to read values from a Pivot Table. In this article, we are going to look at building an alternative to pivot tables. Let's begin. Pivot tables are an immense help while working with data sets – they sum up the data into a small, neatly organized space. You can pick and choose the various values from ...
Continue Reading

Comments (5)

Making pivot tables move in tandem

Posted on 23 September 2008

Making pivot tables move in tandem
While working with multiple pivot tables, one often encounters a situation where multiple pivot tables are created with the same underlying set of data. In such cases, you may want to have the ability to simply select a value from a single drop-down and have all the pivot tables reflect that change. A small piece of code can be used to achieve this. It uses the PivotTable.PivotFields().PivotItems attribute to check if the value selected ...
Continue Reading

Comments (2)

Excel Dashboard : Data Modelling

Posted on 23 August 2008

Excel Dashboard : Data Modelling
If you have followed this till now (silly me, if you’re reading this, you likely might’ve :-)), you’ve completed the first of the three important phases in the development of your dashboard. You may want to use these formulas to get your data up to scratch for the next important step – Data Modeling and the generation of pivot tables. Generating a Pivot Table Once your data has been properly scrubbed, cleaned for errors and filled up, its time to us ...
Continue Reading

Comments (1)

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES