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

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

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