On my pivot table blog yesterday, I wrote an article about using counts in a calculated field. A helpful tip is buried in the 8-minute video that shows how to create the calculation, so I’m posting the tip separately here.
This tip can help prevent data entry problems, if you’ve set up a named Excel table for a client or co-workers to use, or even if you’re the only one using the table. Excel will automatically enter the data for you!
Pivot Tables from Excel Tables
Most of the named Excel tables that I create are also used as the source data for pivot tables. For example, a pivot table from an orders table can show a summary of sales per month or by product.
Sometimes I need a count in the pivot table too, and in the old days I added a field, such as Date or Invoice ID, that I knew would have an entry in every row of the table. The field was set to show a Count, instead of a Sum.
Add a Counter Field
Using a Date or Invoice ID field is okay for showing a count, but those counts can’t be used in a Calculated Field – Excel will always use the SUM of a field. So, if you’ll need to use a count of the records, it’s best to add another field, specifically for counting.
In this Orders table, I added a column named Orders, and it will have a 1 in each row. Then, those 1s can be summed in the pivot table, or used in a Calculated Field.
But, instead of typing a 1 in each row (which I might forget to do), I use a simple formula: =1
Then, because the list is a named Excel Table, the formula fills down all the rows, and is automatically entered in new rows. You’ll never have to remember to enter that key piece of data, and the counts will always be accurate. That saves time, and prevents errors from missing data.
Do you use counter fields in your source data? If so, do you type the values, or use a formula, to take advantage of the Excel Table features?
Video: Create Calculated Field With a Count
If you have 8 minutes to spare, and the attention span for a long video, you can watch this, to see how to create a pivot table, add a new counter field to the source data, and create a calculated field using the counter field.
It also shows the problems with using a Date field, displayed as a COUNT, and trying to include that in a Calculated Field.
Video: Create an Excel Named Table
When you work with lists in Excel, use the built-in Table feature, to make it easy to sort and filter your data. This short video shows the simple steps
_________________
Hi Deborah,
I would like to add a column expressing the nth order, the ordinal number of orders to date. How do I do that?
Many thanks,