by contextures.com
When you are setting up your source data for an Excel pivot table, there are a few data layout guidelines that will help you create the best possible pivot table. Get it right, and you won’t have to fix Pivot Table source data later!
Sometimes you get data that isn’t well suited for creating a pivot table, like the example shown below. It has a column for each month, and the sales amounts are spread over those 12 columns.
Continue reading “Fix Pivot Table Source Data For Better Results”
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?
Have a great weekend, and may all of your pivot table tweets be happy ones!
__________
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.
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.
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.
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.
For details on how the code works, you can read the Select Pivot Table Function From Worksheet Drop Down article on the Contextures blog.
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.
________________
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.
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.
Now, when you double-click a number cell in the pivot table, the new default style is used.
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.
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.
______________
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.
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.
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.
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.
That prefix should make the sheets easier to find and delete, before you close the workbook.
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.
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.
For detailed instructions on adding the drilldown sheet naming and deleting code, visit the Excel Pivot Table Drilldown page on the Contextures website.
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.
________________
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?)
____________
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.
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.
To change the setting, and prevent new items from being included, follow these steps:
____________
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.
___________
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
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.
____________
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”