Category: Excel Pivot Table

If you’re creating workbooks with pivot table reports, you might have macros in those files, to make it easier to work with the pivot tables, or to navigate through the workbook sheets.

Instead of creating buttons on the worksheets, to run the macros, you can create a custom tab on the Excel Ribbon, with buttons to run the macros.

For example, this sample file lets users enter parts data in a UserForm, and summarizes the current inventory in a pivot table. The “Db Macros” tab appears when this workbook is active, and disappears when it’s not active.

Getting Started with Custom Ribbon Tabs

In the following video, you can see how to view the code for a custom Ribbon tab, and make a minor change. The written instructions are on my Contextures website, where you can also download the sample file: Excel Ribbon – Getting Started.

Create Your Own Custom Ribbon Tab

In the next video, you’ll see how to start from scratch – add a custom tab, create the code, and modify it. Then, make a slight change to the macros in Excel, and the Ribbon buttons will run your macros. The written instructions are on my Contextures website, where you can also download the sample file: Excel Ribbon – Add a Custom Tab.

_________________

With a GetPivotData formula, you can pull data from a pivot table. For example, how many file folders were sold:

The formula works well with text fields, but you might get errors if you create a formula using dates.

In the example shown below, the formula in cell E4 refers to the date “1/1/13”, but the result is a #REF! error, even though that date is in the pivot table.

=GETPIVOTDATA(“Quantity”,$B$3,”OrderDate”,”1/1/13″)

To get the correct results for dates, you can use one of the following methods:

• Match the pivot table’s date format
• Use the DATEVALUE function
• Use the DATE function
• Refer to a cell with a valid date

For written instructions, please visit the GetPivotData page on my Contextures website.

Watch the Video

To see the steps for using dates in a GetPivotData formula, please watch this short video tutorial.

____________________

There is a PivotPower add-in that’s available on my Contextures website, which helps with your pivot table tasks.

It automates some of the features that aren’t built in to an Excel pivot table, and makes some of the buried Excel pivot table features easier to access. For example, there is a command that changes all the data fields to SUM, which is handy when Excel defaults to COUNT.

Store Favorite Settings

I’ve added new features to the add-in, to make it even more useful. Now you can store your favorite pivot table settings in the add-in, and then apply those settings to any pivot table, and the active sheet and workbook.

There’s a list of the new features below, and one of my favourites is Currency SUM. It changes the selected field to the SUM function, formatted as currency.

Watch the PivotPower Premium Demo Video

To see a few of the new features in PivotPower Premium, you can watch this short video.

What’s New in PivotPower Premium?

The following features have been added in the new version of PivotPower Premium:

User Guide

• Details on installing the add-in, and description of the commands

Formatting

• Column Width Autofit On
• Column Width Autofit Off

Number Formatting

• Currency
• Number – 2 decimals
• Number – 0 decimals
• Currency SUM
• Number – 0 decimals COUNT

Pivot Items

• Hide All (Blank) Items
• Show All (Blank) Items

Pivot Fields

• Clear All Fields
• Field List A-Z
• Field List Source Order

Grand Totals

• Show All Totals and Subtotals
• Hide All Totals and Subtotals

Cache

• Create New Cache

Purchase the PivotPower Premium Add-in

To make your pivot table tasks easier, you can purchase the PivotPower Premium add-in, at my Contextures website.

PivotPower Premium Add-in

_______________

Happy Spreadsheet Day! We celebrate on October 17th, because this is the date that VisiCalc was first released to customers, in 1979. This year’s theme is Spreadsheets for Students, and what better way to help students, than show them a great way to use pivot tables?

Student Budget Workbook

Most students have limited funds, so it’s important that they plan and track their spending. Bob Ryan, from the Simply Learning Excel website, has created a Student Budget spreadsheet, that should help students get their finances organized.

There is a transaction sheet, where you can enter your budget amounts and track your actual spending, including cash and credit cards. At the end of the month, review your cash and credit card spending in pivot tables that shows monthly totals.

In another pivot table, you can keep track of your running balance.

Bob also built a pivot table which compares your budget amounts with the actual amounts.

Download the Student Budget Workbook

You can download the Student Budget Spreadsheet file, and use it to track your own finances. To get the file, go to the Excel Sample Files page on my Contextures website, and in the Pivot Tables section, look for PT0023A – Student Budget Workbook. The file is in xlsx format, and it is zipped. There are no macros in the file.

Contribute to Spreadsheet Day 2011

If you’d like to join the Spreadsheet Day celebrations, please post your own free and useful spreadsheet template or add-in, that will help students get organized. Or, if you prefer, post a tip or link in Twitter, with the hashtag #spreadsheetday

Thanks for joining the Spreadsheet Day celebrations, and for sharing your knowledge with students. I’m sure they’ll appreciate it!

___________

Last week, you saw a technique for changing monthly data into a better layout, when creating an Excel pivot table.

We used a multiple consolidation ranges pivot table to change this 13-column table into a 3-column data source.

Today we’ll look at a different approach to using multi-column data in a pivot table.

Bi-Weekly Data

Thanks to Jim LaBarr, who sent me a sample Excel file, with his solution to creating reports from multi-column financial data.

In his file, Jim has dates in the heading row – not just the month names. Every column has a heading, and there are no blank rows or columns within the data.

NOTE: Usually, Jim would create an Excel Table from this data, but that changes the headings to text. Don’t create an Excel Table if you want to use the technique that Jim is sharing today.

Pivot Table from Bi-Weekly Data

After setting up his bi-weekly financial data, Jim creates a pivot table, using multiple consolidation ranges. This creates a pivot table with 3 fields – Row, Column and Value.

The dates are the Column items, and the descriptions are the Row items.

This multiple consolidation ranges pivot table shows a grand total for the columns, because all the dates are in the Column field.

NOTE: If we had created a normal pivot table from the data, each date would be a separate field, and there would be no grand total.

Filter the Pivot Table

After creating the pivot table, Jim filters the Row and Column fields, to focus on specific data. For example, you could filter the Rows to show only the Revenue items (but not the Revenue Total). Then, filter the Columns, to show the December 2010 dates.

The Row and Column grand totals automatically update, to show the totals for the filtered pivot table.

Change the Pivot Table Values

When you create a pivot table, the values are summarized by Sum or Count, and shown as simple totals. For a different view of the data, Jim changes the way the Values are shown. In the pivot table shown below, the Values are shown as % of Column.

This is a quick way to see the revenue breakdown, for the two revenue streams. Revenue A produces 60% of the revenue, and 40% is from Revenue B.

Grouping the Dates

Because the multiple consolidation ranges pivot table puts the Dates in a single field, Jim is able to group those dates. This gives him even more flexibility in his pivot table reports.

Here, the dates are grouped by year and month, and again, the grand totals are automatically calculated.

Or, the Column field can be moved to the Row area, to show the dates at the left, in a vertical report.

Additional Summary Functions

In another version of his pivot table report, Jim puts 3 copies of the Value field in the Values area.

• Sum
• Change
• % Change

To show the Change, Jim changes the custom calculation to Difference From. For % Change, Jim uses the % Difference From custom calculation.

Create a Pivot Chart

To give a visual overview of the data, Jim create a pivot chart from the multiple consolidation range pivot table. Here, the Years and Months are in the Row area, and the Descriptions are in the Column area, filtered for Total Cost and Total Revenue.

Jim’s Tips for Financial Data Pivot Tables

If your financial data is in many columns, like Jim’s example, here are Jim’s tips for when to use this multiple consolidation ranges techniques, and when to use an Excel Table instead.

• Use this method to keep multiple columns, with pivot table benefits, like date grouping
• Use Tables if all you need is Filtering on Rows
• Use Tables if you need Chart of multiple Rows with no need for time period Grouping
• For source data with multiple description columns, use the normalization workaround

Download the Sample Financial Data Pivot Table File

To see Jim LaBarr’s sample data, and the pivot tables, you can download his Financial Data Pivot Table sample file. The file is in Excel 2003 format, and zipped.

______________

It’s Friday, and the weather has been unbearably hot all week. On top of that, your boss and/or Excel are making you crazy.

If you’re on your last nerve, you can vent your rage in a comic strip, by using Rage Builder. I made this pivot table themed comic, and I’m sure you can do better!

I didn’t upload my comic to the website – I used Snagit to take a screen shot, and save that image file.

________

It’s interesting to see what people are saying about Excel in Twitter, and I post a few favourite Excel tweets, from my daily reading.

Of course, many of those tweets are about pivot tables, so here, for your Friday entertainment, are some highlights from the past.

Do any of these pivot table tweets sound like you could have posted them?

• Pivot tables are my favourite part of excel. I’m actually sad enough to have a favourite part.
• Just discovered the "Show Pages" function in Excel Pivot tables… my world may never be the same again. #dork
• Pivot tables were originally implemented by Lotus Improv (which was all it did). Yours, the spreadsheet bore 🙂
• If there was an Olympics for Excel Pivot tables, I would have just done the equivalent of the Double McTwist.
• You are not the boss of me, Pivot Table. Sorry, I mean Mister Pivot Table.
• A Pivot Table cannot overlap another Pivot Table – why is this my problem Microsoft?
• Careful lying about Excel. I got caught in that once. They gave me a test. Stupid pivot tables.
• Is it me or does excel rarely assume the correct function when i drop data into a pivot table?
• that’s right ladies, i may not be able to build you a spice rack, but i can sure as hell make you a pivot table. that’s hot, right?
• Also, if you don’t know how to use pivot tables, you don’t *really* know how to use Excel. There, I said it.
• OH:"I’m going to write a book about Excel pivot tables. It’s going to be a romance novel."
• I refuse to help anyone with any system or data unless they know what a excel pivot table is
• I work with technology every day. Excel pivot tables still baffle me.

Have a great weekend, and may all of your pivot table tweets be happy ones!

__________

Double-clicking on a number cell in a pivot table creates a new sheet in the workbook, showing the records included in that number. Sometimes the list doesn’t look the way you’d like it to, and the numbers aren’t formatted the way they are in the source data.

For example, the source data might have sales amounts formatted as currency, or dates in a long format.

Unfortunately, those formats aren’t used in the new sheet.

You don’t have much control over the default formatting in the drilldown details list, but here are a couple of things you can do.

Change the Default Table Style

The drilldown details list is formatted as a named Excel Table, in the default style selected in the workbook. If you change the default Table Style, new lists will use the new default style.

To change the default Table Style:

• Select a cell in any Excel Table in the workbook. If there aren’t any tables, double-click a pivot table data cell, to create one.
• In the Ribbon’s Design tab, click the More drop down arrow for Table Styles
• Right-click on the style that you want as the default Table Style
• Click Set As Default

Now, when you double-click a number cell in the pivot table, the new default style is used.

 

Change the Normal Style

If you’re seeing strange number formatting in the drilldown details, such as dates in Currency format, the Normal Style can be adjusted.

When you select a Theme in an Excel workbook, or use the default Theme, it uses specific fonts, colours, and other format settings.

The Normal style is affected by the selected Theme, and you can make further adjustments to the Normal style. The drilldown details list will use the font and number formatting from the Normal style.

To change the Normal style:

• On the Ribbon’s Home tab, click Cell Styles
• Right-click on Normal, and click Modify

• Click the Format button, and change any of the Cell formatting options.
• Click OK, twice, to close the dialog boxes.

TIP: For number formatting, select General, so dates and numbers will look best. If you select Currency, or another specialized format, all the numbers, including dates, will get that format.

______________

You probably sing your own songs about the joy of pivot tables, while you work in Excel every day. If you want to add to your pivot table song repertoire, here is a new one for you.

The students in this video sing their original composition, Pivot Tables Make Everything Just Right. It’s got a catchy tune, and you can read the lyrics in the video description on the YouTube page.

Great work guys! My only suggestion is to add more screenshots of pivot tables, to convince viewers that they’re really easy to use. Maybe they’ll have more pivot table shots in the sequel. (Or should I spell that – SQL?)

____________