Debra – Page 27 – Excel Pivot Tables

Excel Pivot Table Tweets

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

__________

Quickly Change Pivot Table Data Functions

It’s easy to change the summary function for one data field in a pivot table. For example, if a field shows the Sum of Units, you can right-click on that field, and change it to Count of Units.

pivottablesummaryfunctions02

However, if you have several data fields in a pivot table, it can take quite a bit of time to change each pivot table summary function manually.

Create a Worksheet List of Functions

To make it easy for yourself, or other people who use the pivot table, to change the functions, you can add a drop down list of functions in a worksheet cell.

PivotFunctionChange02

The list is created with Excel data validation, and Excel VBA code runs when a different function is selected in that cell. The code changes the summary function for all the data fields in the pivot table.

Read the Details

For details on how the code works, you can read the Select Pivot Table Function From Worksheet Drop Down article on the Contextures blog.

Download the Sample File

To test the Summary Function code, you can download the sample file from the Contextures blog link above. The file is in xlsm format, and zipped. Enable macros when you open the file.

________________

Pivot Table Drilldown Formatting

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.

showdetails12

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

showdetails13

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

showdetails07

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

showdetails08

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.

showdetails10

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

showdetails09

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.

showdetails11

______________

Clean Up Pivot Table Show Details Sheets

When you double-click on the value cell in a pivot table, a new sheet is automatically inserted in the workbook. The new sheet contains a table, listing all the source data records that are summarized in the number that you double-clicked.

showdetails03

Double-clicking the cell is a shortcut to the pivot table Show Details command. You can also right-click on a value cell, and click Show Details.

showdetails02

Show Details, also called DrillDown, is a great feature for digging into the details, but you can end up with lots of extra sheets in your workbook.

However, you don’t usually want to save the sheets, so you manually delete them before you close the file.

Automatically Name the Sheets

With event code on the pivot table’s worksheet, and in the workbook module, you can add a prefix, such as “XShow”, when the Show Detail sheets are created.

showdetails06

That prefix should make the sheets easier to find and delete, before you close the workbook.

Automatically Delete the Sheets

To make the cleanup task even more efficient, you can use programming to show a message when you’re closing the workbook, if there are Show Details sheets in the file.

showdetails04

If you click Yes, all the sheets with the “XShow_” prefix are deleted, and the workbook stays organized. Then, save the tidied up version of the workbook, when prompted.

See the Drilldown Sheet Code

For detailed instructions on adding the drilldown sheet naming and deleting code, visit the Excel Pivot Table Drilldown page on the Contextures website.

Download the Sample Drilldown File

To see how the event code names the sheets, and deletes them when closing, you can download the sample file from my Contextures website, at the link shown above.

________________

The Pivot Table Song

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

____________

Include New items in Pivot Table Filter

In a pivot table, you can apply a manual filter to a pivot field, by using the check boxes in the field’s drop down list.

In this example, there is a date field in the Row Labels area and a few dates have been selected in the manual filter.

Updating the Pivot Table Data

If you add new records in the pivot table’s source data, new dates might be added. When you update the pivot table, the new dates might appear, even if you hadn’t selected those dates in the manual filter.

Seeing the new dates could be helpful, if you want to make sure that you notice new records when they’re added. You can manually deselect the new items after they appear.

However, if you want to prevent the new dates from automatically appearing, you can change a setting in the pivot field, to specify if new items are included or not, when the field is manually filtered.

Change the Pivot Field Setting

To change the setting, and prevent new items from being included, follow these steps:

In the pivot table, right-click a cell in the date field, and click Field Settings.
On the Subtotals & Filters tab, in the Filter section, remove the check mark from Include New Items In Manual Filter
Click OK.

____________

Excel PivotPower Add-in Update

The Excel PivotPower add-in on the Contextures website has been updated.

The PivotPower add-in makes it easier to change the summary functions in a pivot table, or add protection, and has several other helpful commands.

After you install the add-in, it shows up as a drop down list on the Excel Ribbon’s Add-Ins tab. In older versions of Excel, the add-in creates a PIVOT menu on the Excel menu bar.

You can select a cell in a pivot table, and run a command, so the changes only affect the selected pivot table.

Or, select a cell that isn’t in a pivot table, and the command will change all the pivot tables on the active sheet.

To download the file, you can go to the PivotPower Add-In page on the Contextures website. You’ll find installation instructions and details on the PivotPower commands.

___________

Make Pivot Table Macros Run Faster

Pivot table macros can run painfully slowly, even if you have turned off screen updating and disabled events.

To make the code run faster, you can try adding a line that turns off automatic updating in the pivot table. For example:

     Set pt = Worksheets("PivotSales").PivotTables(1)

pt.ManualUpdate = True

At the end of the code, you can reverse the setting, to turn automatic updating on:

     pt.ManualUpdate = False

Watch the Pivot Table Video Tutorial

In this short video, you can see that the pivot table macro runs much faster when the ManualUpdate setting is changed.

Note: In Excel 2007, the macro won’t run if the Defer Layout Update setting is checked. This problem is fixed in Excel 2010.

____________

Pivot Table Calculated Field: Tasks Per Hour

Instead of creating formulas in the source data for your pivot tables, you can create formulas in the pivot table, by using calculated fields and calculated items.

In this example, we’ll create a calculated field, to show the number of tasks per hour that are completed by each worker.

Continue reading “Pivot Table Calculated Field: Tasks Per Hour”

Quickly Add Fields in Excel 2003 Pivot Table

When you have a long list of fields in an Excel 2003 pivot table, it can take a long time to drag them into the pivot table layout, using the PivotTable Wizard.

pivotfieldadd00

For a quicker way to add the fields, don’t go into the Layout screen in Step 3 — just click the Finish button.

pivotfieldadd01

Add Fields from the PivotTable Field List

When the PivotTable Wizard closes, you’ll see a blank pivot table on the worksheet.

Note: If the PivotTable Field List isn’t visible, click the Show Field List button on the PivotTable Toolbar.

pivottoolbarshowfieldlist

To add the pivot fields:

Then, in the Field List, select one of the layout areas from the drop down list.
In the Field List, double-click on each field that you want to add to the selected layout area.

Repeat Steps 1 and 2, for all the layout areas, leaving the Data Area to fill in last.

____________