by contextures.com
After you sort an Excel pivot table, the wrong item might appear at the top of a column. For example, in the screen shot below, the list of Sales Rep names has Jan at the top. Below that name, all the other names are sorted in the correct alphabetical order.
Continue reading “Wrong Item at Top of Sorted Pivot Table in Excel”
Do you ever make a quick pivot table, then spend the next 30 minutes trying to make it look good? Here are three quick pivot table layout tips, to make your job a bit easier!
Continue reading “Make Your Pivot Table Look Better – 3 Quick Tips”
If you have a report filter at the top of your pivot table, do the items in the drop down list ever appear out of order? That happens in my pivot tables occasionally, and there isn’t a quick and easy way to fix the problem!
In the short video below, I show the report filter problem, where the drop down list is not sorted correctly.
After that, I show a workaround that you can use, to get the sorting problem fixed.
There are written steps, and more pivot table sorting tips, on the Pivot Table Sorting Fixes and Tips page, on my Contextures site.
If you notice that items are out of order in a pivot table report field, you might go to the Data tab on the Excel Ribbon, where the Sort & Filter commands are located.
However, the Sort commands are dimmed out, because you can’t use those in a Report filter field.
Why not? I have no idea!
Fortunately, there’s a workaround that you can use, to sort the report filter items.
It’s not complicated, but it’s a bit annoying that this is the only way to sort things!
To sort the report filter field, follow these steps:
NOTE: If you have several Report Filter fields to sort, use the Report Filter sorting macro, on my Contextures site.
You can get the Excel sample file, and more pivot table sorting tips, on the Pivot Table Sorting Fixes and Tips page, on my Contextures site.
Also, if you have lots of Report Filter fields to sort, you can save time with the Report Filter sorting macro, on my Contextures site.
__________________________
__________________________
After you create an Excel pivot table, the source data usually changes. New records are added, and old records might be changed or deleted.
Later, when you refresh the pivot table, you should see a summary of your updated data, but sometimes there’s a problem – old data sticks in the drop down lists.
To show this pivot table problem, I made short video, which you can see in the next section.
My sample file has data from a fictional sales company, and the source data was changed:
After making those changes, I refreshed the pivot table. As expected, the Central region’s name disappeared from the Region headings.
However, Central still shows up in the Region drop down.
In this short video, I show how you can clear the old Region name from the pivot table drop down list.
Doing this will also prevent old items from appearing in this pivot table, in the future.
In Excel 365, and Excel 2019, it’s even easier to avoid old items in pivot table drop downs.
Instead of changing this setting for every pivot table that you create, you can change it once, in your Excel default settings for Pivot Tables.
In the video below, I show the steps for changing an individual pivot table, like I did in the previous video.
Then, at the 2:57 mark, I show how to change the default setting, in Excel 365. You can skip to that section, if you’d like!
To stop old items from showing in an existing pivot table, follow the steps below.
NOTE: This setting will affect all the pivot tables that use the same pivot cache.
To get the sample file, go to the Clear Old Items page on my Contextures site.
That page also has Excel macros that you can use, to
_______________________
_______________________
It’s easy to get a sum in a pivot table, or a total count. But how can you count unique items in an Excel pivot table?
For example, if you’re analyzing sales data, you might need to show these types of counts:
–How many unique products were sold in each store?
–How many distinct people made sales in each region?
Continue reading “Quick Count Unique (Distinct) Items in Excel Pivot Table”
If you add times to an Excel Pivot Table, and format the time to show tenths of a second or hundredths of a second, zeros might appear after the decimal point.
The decimals for tenths of a second or hundredths of a second are rounded to zero, and changing the pivot table number format does not fix the problem.
In the screen shot below, I’ve highlighted the problem in the pivot table time column.
Instead of rounding the decimal to 2, it rounded to zero.
In the video below, I show how this time rounding problem can happen, and the steps to fix the problem.
There are written steps below the video, and more details on my Contextures site, on the Pivot Table Time Fields page.
Video Timeline
To fix the pivot table times, so they show tenths of seconds and hundredths of seconds, you can use a simple workaround.
To begin, follow these steps to add a column in the source data:
After you add the new column to the source data, follow the steps below, to update the pivot table.
Next, follow the steps below to format the times:
The numbers are formatted correctly in the new field, tenths of a second, and hundredths of a second
To complete the changes, you can remove the original time field, which had the rounded tenths of a second or hundredths of a second, showing a zero instead of the correct numbers.
To get the sample Excel file that I used in the video, go to my Contextures site, on the Pivot Table Time Fields page. The zipped file is in xlsx format, and does not contain any macros.
____________________
____________________
In a perfect world, if you need to make a pivot table, the data is nicely organized in a table, and you can connect to that, quickly and easily.
Unfortunately, as you know, things aren’t always perfect, especially when it comes to data! And sometimes the data is in two or more separate tables, so you need to combine it somehow, before you can build a pivot table..
There are different ways you can combine data from multiple tables in Excel. For example:
In the sections below, there are a couple of short “Combine Data” videos that I’ve made recently.
For all 4 methods, you can find detailed steps, and sample files, on my Contextures site, on the Pivot Table from Multiple Sheets page .
Here’s the VSTACK function video, in which I combine the data from tables on 2 separate worksheets. It only takes one cell with a formula, to return all the data from the two tables.
I included the headings for the first table too, because pivot table data needs headings!
What if you don’t have Power Query, or the Excel VSTACK function. And you don’t want to use Excel macros?
In that case, you can use the old Pivot Table Wizard to do the job. It’s well hidden in newer versions of Excel, but in the video, I’ll show you how to open it, with an Excel keyboard shortcut.
For all 4 methods to combine data, you can find detailed steps, and sample files, on my Contextures site.
Follow this link, to go to the Pivot Table from Multiple Sheets page .
____________________________
____________________________
When you create a pivot table, and select a cell in it, a pivot table field list usually appears, at the right side of the Excel window. See how you can adjust that list’s layout, width, and position. Also, see how we moved pivot fields in the olden days – do you remember the PivotTable Wizard?
Continue reading “Move or Lock Pivot Table Field List in Excel”
Here’s a quick trick to add or move Excel pivot table fields, just by typing.
Instead of dragging a pivot field into the layout, you can type its name over an existing pivot field label.
Here are the steps to add or move pivot table fields on the worksheet:
After you press Enter, the pivot table layout changes.
The first video above shows how to move pivot fields.
You can use a similar trick to move the pivot items in a pivot table.
The short video below shows how to move the Excel pivot items, and you can find written steps on the Move Pivot Table Labels page on my Contextures site.
To try the quick trick to add pivot fields, you can use your own pivot table, or download the Move Pivot Labels sample file from my Contextures website.
The zipped Excel file is in xlsx format, and does not contain any macros.
_________________
____________________
In an Excel pivot table, you can use Excel’s built-in custom calculations, for a different view of the data.
For example, in the video below, I set up a pivot table to show what % of a company’s monthly sales were Binders. Also, what % of Binder sales was for each colour – red, blue, and black.
Note: You can get the sample Excel file that I used for this video on the Pivot Table Show Values As page, on my Contextures site.
In the screen shot below, the pivot table has 4 fields in the worksheet layout:
Following the steps listed in the section below, I used pivot table custom calculations, to show additional sales details:
In the pivot table screen shot below, I’ve added a 2nd copy of the Units field to the values area.
For the 2nd copy of the Units field, I followed the steps below, to change that field’s calculation settings
In the pivot table, the second Units field changed, to show:
For example,
For more Pivot Table Subtotal tips, videos and examples, visit the following links:
Show Percent of Subtotal in Pivot Table
Pivot Table Show Values As % of Parent Total
Show Percent Of Subtotal In Pivot Table
______________________
