Browse by Tags

Slicer or Data Validation list

This note documents a somewhat creative – and I suspect an unintended – way of using a slicer. A slicer is a control element introduced with Excel 2010. It is a large easy-to-use control to filter the results of one PivotTable or even multiple PivotTables. I like the UI look-and-feel of a [...] Read More...

Published Sat, Oct 10 2011 5:51 PM by Daily Dose of Excel

Filed under: Data Validation, User Interface, Pivot Tables, Filtering, Excel 14, Controls, Data Visualization

Animal Songs

J-Walk compiled a list of songs with animals in the title. That data screams for analysis, doesn’t it? The vertebrates (phylum Chordata) kicked ass and mammals (class Mammalia) dominated. But Birds (class Aves) were not slouch either, although many of those songs only references “bird”. The most popular species is dog (Canis lupus). Oddly [...] Read More...

Published Mon, Feb 02 2011 6:30 AM by Daily Dose of Excel

Filed under: Pivot Tables

TM PivotTable Dashboard

A client wanted to visualize a table of data with a dashboard where each chart represented specific information in the table. I had two choices: 1) Develop a client-specific solution and charge the client the entire development cost, or 2) develop a more general solution, market it to a broader customer base and charge the client [...] Read More...

Published Fri, Jan 01 2011 7:00 AM by Daily Dose of Excel

Filed under: Add-ins, Charting, Pivot Tables, Data Visualization

Slice and Dice!

Hi all, I had a bit of time to spare the other day (got snowed in during travel by train) and wrote a couple of pages about a new Excel 2010 feature: Pivot table slicers I show how to customise them and also included some VBA examples on how to work with them. Enjoy the read! Regards, Jan Karel Pieterse www.jkp-ads.com Read More...

Published Wed, Jan 01 2011 9:54 AM by Daily Dose of Excel

Filed under: General, VBA, MVP, Pivot Tables, Excel 14

My Last Post on Filtering Pivottable Source Data

Based on the comments from these three posts, there appears to be exactly zero people interested in this topic, so I’ll make it my last post on the matter. In short, I wrote some code that subverts the default action of double clicking on a Pivot Cell (creating a new sheet with the underlying data) and [...] Read More...

Published Tue, Nov 11 2010 2:31 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Filter Pivot Table Source Data Calling Procedures

Filter Pivot Table Source Data Filter Pivot Table Source Data II I call my filtering code from two separate places. First, if a cell in a pivot table is double clicked, the code is called. My code is in an add-in, not in the workbook that contains the pivot table, so I have to set [...] Read More...

Published Wed, Nov 11 2010 12:02 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Filter Pivot Table Source Data II

In this post I show the main procedure that filters a pivot table’s source range. In this post, I show how to determine which filters are applied to a particular cell in a pivot table. First, I count the number of filters. Public Function FilterCount(Target As Range) As Long Dim [...] Read More...

Published Tue, Nov 11 2010 2:47 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Filter Pivot Table Source Data

I started by showing you my awesome code to parse the SourceData of a pivot table. Code that has been rendered obsolete by John’s comment. I was going to continue with the supporting utilities used by the main procedure, but it may be hard to put things into context, so I’ll show the [...] Read More...

Published Mon, Nov 11 2010 1:44 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Getting the Source Range of a Pivot Table with VBA

I was pouring through Héctor Miguel Orozco Díaz’s filtering code recently and decided to rewrite it. I was hoping to make it shorter, but I couldn’t. I did move some code outside the main procedure, though, and I’ll be posting that code over the next few days. In order to filter the source range [...] Read More...

Published Thu, Nov 11 2010 2:47 PM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Toggle PivotFields from Count to Sum

Carrie laments: Is there a way to change the default in a pivot table so sums the values when created rather than count? It is very time consuming if you have several data points. I hear ya, sister. Excel does a pretty good job guessing, but sometimes it gets stuck on Count like that’s all [...] Read More...

Published Tue, Aug 08 2010 7:30 AM by Daily Dose of Excel

Filed under: VBA, Pivot Tables

Most Frequent Occurence

Tammy has this data: and she wants to know who used the vehicle the most by month. I couldn’t think of an easy way to do this with worksheet functions, so I resorted to a pivot table. With Date on the left, Driver across the top, and Count of Driver in the data area, I grouped [...] Read More...

Published Fri, Jun 06 2010 10:12 AM by Daily Dose of Excel

Filed under: Pivot Tables

OLAP Catastrophic Failure

I’ve never needed to use an OLAP cube before recently. I’ll admit that I didn’t see what all the fuss was about. But I came upon a problem for which an OLAP worked perfectly. Theoretically. In practice (Excel 2003), I experienced error after error. I rebuilt the cube from scratch several times. [...] Read More...

Published Tue, Feb 02 2010 9:51 PM by Daily Dose of Excel

Filed under: Pivot Tables

Multi-Column PivotTables

J-Walk posted a link to the Top 101 Cities with the Largest Temperature etc…. The title is too long, so you’ll have to follow the link if you want the details. I wanted to see how this broke down by state. Here’s what I did. I brought the data into Excel using a [...] Read More...

Published Tue, Sep 09 2009 6:20 PM by Daily Dose of Excel

Filed under: Pivot Tables

Quick PivotTables

Sometime in the mid-1990s, a man named Lyle Lanley walked into Microsoft with an idea. Probably the greatest.... Oh, it's more of an Apple idea. "Just tell us the idea and we'll implement it", said Bill Gates. I'm on a quest to rid my life of wizards. The wizards that Microsoft seems [...] Read More...

Published Mon, Jun 06 2009 11:41 AM by Daily Dose of Excel

Filed under: General, VBA, Pivot Tables

Syndication

Recent Posts

Tags

News

Archives

Slicer or Data Validation list

Animal Songs

TM PivotTable Dashboard

Slice and Dice!

My Last Post on Filtering Pivottable Source Data

Filter Pivot Table Source Data Calling Procedures

Filter Pivot Table Source Data II

Filter Pivot Table Source Data

Getting the Source Range of a Pivot Table with VBA

Toggle PivotFields from Count to Sum

Most Frequent Occurence

OLAP Catastrophic Failure

Multi-Column PivotTables

Quick PivotTables