|
|
Usually, it's easy to sort an Excel pivot table – just click the drop down arrow, and select one of the sort options. Every now and then, the pivot table doesn't sort the way that you'd expect. In this example, there are sales representative names in column A, and they have been sorted alphabetically – A-Z. However, Jan is at the top of the list, instead of Ann. Why the Sort is Incorrect In this example, Jan is at the top of the list, because Excel assumes Jan means January, and is in one of Excel's built-in custom lists. In addition to the built-in custom lists, you might have created your own custom lists, such as districts or departments. Those custom lists take precedence when you're sorting labels in a pivot table. Fortunately, if things don't sort correctly, you can fix the problem, by changing a
Continue reading Excel Pivot Table Sorting Problems
If your Excel data is in monthly columns, like the worksheet shown below, you'll have trouble setting up a flexible pivot table.
Instead of multiple columns with sales amounts, rearrange your data into a single column of amounts. Adjacent columns will show the product name and month.
Rearrange the Data With a Pivot Trick
To change the data layout, you can create a Multiple Consolidation Ranges pivot table, as shown in the video below. Usually that type of pivot table is used for combining data on different sheets, but it has the side benefit of changing horizontal data into a vertical layout.
After you create the pivot table, double-click on the Grand Total cell, to extract the source data, with amounts in a single column.
Then, build a new pivot table, from the normalized data.
Or watch on YouTube: Normalize Data for Excel Pivot Table
Download the Sample
Continue reading Normalize Data for Excel Pivot Table
Last week, Chandoo interviewed me for his Online Excel VBA School, and we talked about using VBA with Excel Pivot Tables. If you drop a field into the Values area, and that field contains blank cells or text, it's added as "Count of", instead of "Sum of". With a macro, you can quickly change all the fields from Count to Sum, instead of fixing each pivot table summary function manually. Select a Summary Function One way to let users change the function is to create a drop down list of functions on the worksheet. Then, event code runs when the cell changes, and the selected function is shown in the pivot table. The cell with the drop down list is named FuncSel, as you can see in the NameBox in the screen shot above. On another sheet, that could be hidden from the
Continue reading Select Pivot Table Function From Worksheet Drop Down
With the pivot table Show Details feature in Excel, a new sheet is inserted when you double-click on the value cell in a pivot table. It's a great feature for drilling into the details, but you can end up with lots of extra sheets in your workbook. Usually, you don't 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 – XShow_ – when these detail sheets are created. That prefix should make the sheets easier to find and delete. Automatically Delete the Sheets To make the cleanup task even easier, you can use event code to prompt you to delete those sheet when you're closing the workbook. If you click Yes, all the sheets with the XShow_ prefix are
Continue reading Delete Excel Drilldown Sheets Automatically
Recently, we saw how you can use Excel Slicers, to filter fields in one or more pivot tables. In the comments of that article, James asked how to keep those Slicers from overlapping the pivot tables. Does anyone know how to stop slicers moving around when you make selections. This happens if the slicers are viewed on top of the pivot table data. As the pivot output data shrinks or expands the slicers move around and sometimes obscure each other. Any idea how to fix in place? Pivot Table Update Event One way to fix the problem of sliding Slicers is to automatically move the Slicers, any time the pivot table is updated. To do that, you can use the PivotTableUpdate event, and a procedure that moves the Slicer to the right side of the pivot table. Each Slicer has a caption, and you can refer the the Slicer
Continue reading Keep Excel Slicers From Sliding
There is a page on the Contextures website that describes how to clear old items in pivot table drop downs. This week, Laurence emailed me about a problem he was having with those instructions, and you can keep reading, to see how we solved the problem. Old Items in the Pivot Table Those old items can appear if you change the pivot table source data – for example, you might remove a few obsolete products, or change a sales rep's name. When you refresh the pivot table, the new data can appear, but the old names still show up in the drop down lists, that you use for filtering. Manually Clear the Old Items One way to clear the old items is to do the steps manually: Remove pivot field(s) from the pivot table layout Refresh the pivot table Put the pivot field(s) back in the
Continue reading Clear Old Items in Pivot Table Drop Downs
How was your weekend weather? We had a mini-blizzard yesterday, that covered the backyard with snow. But it was a good day to stay indoors, and work on Excel! Pivot Table Subtotals I'm confident that the weather was better last April, when I posted a video about adding multiple subtotals for a pivot table field, and you can see that video at the end of this blog post. This week, Bob emailed a question about creating subtotals for the innermost fields, and showing subtotals for calculated fields. By default, there are no subtotals for the inner field, but you can force them to appear, by following the same steps, shown below, that you use to create multiple subtotals. Add Custom Subtotals for Pivot Fields To show custom subtotals for the inner or outer pivot fields, follow these steps: Right-click on an item in the pivot field that you want
Continue reading Add Pivot Table Subtotals for Inner Fields
Thanks for your formula suggestions on Wednesday's blog post about promotional pricing. Here's another formula example, and I'm sure you'll have alternate methods for this problem too. Driver Limits In some countries, there are limits to the hours that truck drivers can work in a string of consecutive days. In this example, the limit is 60 hours, in any period of 7 consecutive days. The maximum hours is entered in cell C1, and the number of consecutive days is entered in cell F1. If the regulations change, it will be easy to change those settings. Calculate the Remaining Hours To help prevent drivers from going over their limits, we'll set up a table where the daily hours are entered. The date and driver name are entered in each row. In column D, a SUMPRODUCT formula calculates how many hours the driver has remaining, in the current 7 day
Continue reading Excel Function Friday: Track Driver Hours
In Excel 2007, there was a PivotChart Filter Pane, and you had to open that if you wanted to filter the pivot chart. Things have improved in Excel 2010, and the PivotChart Filter Pane is gone. The field names now appear on the pivot chart, which is great news! Each field button has a drop down arrow, and you can use those drop downs to quickly and easily add or clear the filters. You can show or hide the individual field buttons, or use the Hide All command on the Ribbon, to control what appears in the pivot chart. Watch the Pivot Chart Filter Video To see the steps for creating and filtering a pivot chart in Excel 2010, you can watch this short Excel tutorial video. Or watch the video on YouTube: Filter Pivot Chart in Excel 2010 _________________
Continue reading Filter Pivot Charts in Excel 2010
It snowed here all day on Wednesday, and caused traffic problems and backaches. Usually the city clears the streets very efficiently, but things were a mess on Wednesday. Maybe some of the snow removal contracts expired in mid-March, so the full fleet wasn't available. Fortunately, I didn't have any outside meetings scheduled, and spent the day working on client projects. I took a bit of time to download some weather data from the Environment Canada website, and summarized it with an Excel pivot table. How's your snowfall this year? Worse than ours, shown below? Three Years of Data The worst spring snowfall that I remember was in April 1975, so I downloaded the data for that year, plus 2010 and 2011. Unfortunately, the data for March 23rd wasn't online yet, but the Toronto Star said it was 20 cm, so that's the number I used. The data is
Continue reading Excel Snow Days
|
|
Recent Comments