Add a Counter Field to Excel Table

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.

counterfield03

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.

calculatedfieldcount01

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.

calculatedfieldcount04

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.

Or watch on YouTube: Create Calculated Field With a Count

_________________

You may also like...

5 Responses

  1. Thanks for giving me the useful information. I think I need it. Thank you

  2. Alexj says:

    “Most of the named Excel tables that I create are also used as the source data for pivot tables…”

    Me too. So what I do is add a pivotcache refresh routine which is triggered from either a change in the table data, or when I activate the sheet that holds the pivot.

  3. Roger Govier says:

    Excellent tip Deb, as always.
    The only thing I might do in addition, would be to Hide the Orders column.
    That way the user cannot overtype the formula, and there is one less field to tab through on each row.

  4. PATRICK says:

    Bonjour,

    superbe truc sur les tableaux et champs calculés

    Merci

Leave a Reply

Your email address will not be published. Required fields are marked *