Highlight Subtotals for Easy Reading

Wed, 16 May 2012 21:48:00 -0700

One of the things that always struck me as odd about using subtotals is that only the words in the subtotals turn bold, and not the actual subtotals themselves. With a long list of data this can make it hard to see which numbers are the subtotals amongst the data. Fortunately this is very easy to fix using conditional formatting.

Assume that you have a list of data with Date, Vendor and Amount columns. After going to Data --> Subtotals and selecting to Sum the Amount for every change in Date, you�d end up with a subtotaled list like this:

Retrieving Selections From A PivotTable Slicer

Tue, 01 May 2012 23:45:00 -0700

So you've built a really cool PivotTable, and you hooked up a slicer to allow exploration of the data. And now you want to do something really cool, but you need to make your formula react to the slicer value. Can you do it? Of course you can, but how?

This article will focus on the technique to do exactly that: return the value of a slicer to a formula. Note that, in order to follow along you will need Excel 2010 or higher, as Slicers didn't exist prior to this version.

Background

Let's assume that you have a set of data that looks like this:

Sales Affiliate Program

Wed, 25 Apr 2012 22:00:00 -0700

If you have a website or blog, or distribute newsletters by email, you can earn a 30% commission by selling Excelguru products!

All commissions are tracked by e-Junkie, the same vendor I use to sell all my products, and commissions are paid out on the 15th of the month following sale via PayPal. So what are you wating for? Join the Affiliate Program now!

This page contains images and the html code you can use to place ads on your web pages. Replace AFFILIATE in the code below with your e-Junkie Affiliate

The Magic Of PivotTables (2010) - Video Course

Tue, 24 Apr 2012 00:10:00 -0700

Excel PivotTables: It�s a polarizing term. People who use PivotTables absolutely love them. For those who don�t, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

Why You Should Take This Course:

If you�ve never created, or don�t regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

Excel PivotTables are an

The Magic Of PivotTables (2007) - Video Course

Tue, 24 Apr 2012 00:07:00 -0700

Why You Should Take This Course:

The Magic Of PivotTables (2003) - Video Course

Tue, 24 Apr 2012 00:03:00 -0700

Why You Should Take This Course:

XLG File Tools Add-in

Tue, 03 Apr 2012 21:36:00 -0700

XLG File Tools is a FREE add-in which was created to increase the functionality of Excel and address the plethora of extra clicks that were introduced begining in Excel 2007. It's current feature set is designed to make the job of opening existing files and creating new files more efficient.

XLG File Tools is easy to use, and is guaranteed to make you more efficient. In addition, it's a snap to install and requires no administrative priviledges to do so.

Beta Alert!
Please note that, while fairly stable, this addin IS currently considered in Beta

Sorting A Column Of PowerPivot Data By Another Column

Tue, 27 Mar 2012 22:16:00 -0700

One of the things that used to drive me crazy about working with PivotTables in PowerPivot�s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.

In short, I was never happy with my PivotTables when they ended up looking like this:

Displaying �Last Updated� Date And Time In PowerPivot

Tue, 27 Mar 2012 21:49:00 -0700

We use PowerPivot to display key information in dashboards, some of which can be refreshed right up to the current second. Naturally, one of the first questions asked when looking at the reports (particularly if they get printed) is �When was the data last updated?�

Personally, I struggled with this for a while. I could tell when the most current record in the database file was, but that�s sometimes not enough. As we run a golf course, we have days with no revenue due to snow or heavy rain. And in that case, we actually want to know that no revenue was present, so picking up the last posted transaction just doesn�t cut it.

The solution to this is actually really easy though. It consists of three steps:

Approximate Matches With VLOOKUP

Wed, 21 Mar 2012 22:00:00 -0700
The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for an item given the ID of the product.

But VLOOKUP is more than just that; it is the gateway to real Excel knowledge. The VLOOKUP function contains everything that a function can throw at you: multiple required parameters, optional parameters with defaults, and needs both ranges and numeric data in its input strings. If you can master this function, you can master ANY other function in Excel.

The VLOOKUP function syntax is as follows:

Excelguru Forums

Highlight Subtotals for Easy Reading

Retrieving Selections From A PivotTable Slicer

Background

Sales Affiliate Program

The Magic Of PivotTables (2010) - Video Course

Why You Should Take This Course:

The Magic Of PivotTables (2007) - Video Course

Why You Should Take This Course:

The Magic Of PivotTables (2003) - Video Course

Why You Should Take This Course:

XLG File Tools Add-in

Sorting A Column Of PowerPivot Data By Another Column

Displaying �Last Updated� Date And Time In PowerPivot

Approximate Matches With VLOOKUP