Slicer VBA Code – Create, Change or Modify a Pivot Table Slicer using VBA

Posted on 23 November 2009

Here is some VBA code that can help you automate working with a slicer in Excel. Slicers, as you would know, are the latest introduction to Excel. As I mentioned in my previous post, they can also be programattically controlled using simple VBA code. Let's see how. Slicer Creation using VBA - Basic Steps Let's look at the slicer hierarchy - a workbook has slicer caches (accessed the SlicerCaches object), each slicer cache in turn having multiple slicers (one for each ... Continue Reading

Comments (7)

Slicer in Excel – The Pivot Table Remote Control

Posted on 20 November 2009

Slicer What is a Slicer? One can think of a slicer as an extension of a pivot table which makes the job of filtering the pivot table data easier. One shortcoming that pivot tables earlier had was that you could not control them remotely. Short of VBA, you could do things with pivot tables only when you had one in front of you. A slicer on the other hand acts as a 'remote control' for the pivot table. You can ... Continue Reading

Comments (7)

Pivot Table Quick Tip – Using Pivot Table to Separate Data

Posted on 16 November 2009

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. The manager ... Continue Reading

Comments (4)

Hide Pivot Table Field Header – Pivot Table Quick Tip

Posted on 12 November 2009

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 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 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

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)

Pivot Table VBA: VBA to Read & Modify a Pivot Table in Excel

Posted on 11 September 2009

Here is a collection of VBA codes to work with Pivot Tables: 1. Create a pivot table using a VBA Macro 2. Delete all Pivot Tables Using a VBA Macro 3. Refer to a Pivot Table directly within a VBA Macro 4. Refer to a Pivot Table Field directly within a VBA Macro 5. Refer to a Pivot Item of a Pivot Field present in a Pivot Table directly using a VBA Macro 6. Remove old (ghost) Items from ... Continue Reading

Comments (1)

Calculated field and Calculated Items in a Pivot Table

Posted on 11 September 2009

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 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 (8)

How To Create Pivot Table Using VBA

Posted on 28 June 2009

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)

Alternative to Pivot Table in Excel

Posted on 22 November 2008

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)