by contextures.com
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
______________________
On Saturday, it’s the 38th anniversary of Microsoft Excel, which was released on September 30th, 1985. Eight years later, sometime in 1993, Pivot Tables were added, in Excel 5.0! Happy 30th Anniversary to Excel Pivot Tables.
Continue reading “30 Years of Excel Pivot Tables-Looking Back”
By default, the Pivot Table shows only the items for which there is data. In the example shown in this video, not all colours were sold to each customer. You may wish to see all the items for each customer, even those items with no data.
Continue reading “Show All Pivot Table Items To Compare Sales Easily-No Data”
If you build an Excel pivot table, and no data is entered for some items, there will be blank cells in the pivot table values area. See how to change those blanks to zero, or a text string, such as “N/A”
Continue reading “Show Zero in Empty Pivot Table Value Cells”
When you create a new pivot table, and add multiple fields to the row or column area, subtotals are automatically created. For Row fields the automatic subtotals usually appear at the top, and you can move them to the bottom, if that’s your preference. There are a few limitations though, which you can see in the video and the written notes below.
Continue reading “Show Excel Pivot Table Subtotals Top or Bottom”
