On Monday, Excel MVP Kirill Lapin (aka KL) shared his macro to create a standard pivot table from multiple workbooks (as opposed to worksheets in the same workbook). I promised you a second pivot table macro, and here it is -- in today's example, Kirill combines data from a sales list and price list, stored [...]

If you want to create a pivot table from data on different worksheets, you can use a Multiple Consolidation Ranges pivot table. However, that creates a pivot table with limited features and functionality. Last year, Excel MVP Kirill Lapin (aka KL) shared his brilliant code to create a Union query and build a fully functional [...]

Last week, I posted Bob Ryan's Excel macro for formatting a pivot table in Classic style. Bob's macro formats the first pivot table indexed on the active sheet.
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
Ideally, you'd only have one pivot table on a worksheet, to prevent problems [...]

My friend and client, Bob Ryan, from Simply Learning Excel, has just published a hands-on, no fluff, Excel book -- Simply Learning Excel 2007: Learn the Essentials in 8 Hours or Less. To celebrate the book launch, I asked Bob to share one of his favourite Excel tips with you, and you can read [...]

How can you get missing data to show up in your Excel pivot table, showing a count of zero? AlexJ encountered this problem recently, and sent me his solution, to share with you.
One problem with pivot tables is that you can't use them to report on data that is not there. What if you [...]

Remember to enter the Excel 2010 Future of Productivity Giveaway -- it ends at noon (Eastern time) on Tuesday, June 8th. You could win a copy of Microsoft Office 2010 (with fancy new pivot table features), a Flip video camera, and a Seagate 1TB hard drive.
If you're working with an Excel 2007 pivot table, [...]

In the last blog post, you saw how to turn off buttons and drop downs in an Excel 2007 pivot table. In the comments, Jon Peltier suggested a simple Copy and Paste Values instead, but that doesn't paste the pivot table style formatting.
Then, John Walkenbach pointed us to one of his articles, which describes [...]

If you're sharing an Excel pivot table with colleagues who aren't too skilled in Excel, you might want to hide some of the buttons and drop downs in the pivot table before you send it.
For example, in the pivot table shown below, the sales are summarized by city and product, and has filter buttons and [...]

If you have a long list of items, you could use formulas to count how many times each item occurs in the list. It would take a few steps, including pulling a list of unique items from the list, then creating a formula to count each item.
I find it quicker and easier to create a [...]

When you summarize data in a pivot table, it usually shows a sum of the values. (If there are blanks or text values in the field, usually the pivot table shows a count instead.) In this pivot table, you can see the total labour cost for each Service Type.

If there are two or more [...]