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