Pivot Table Question

[Roar]

Weekly, I run a report showing dollars brought in by each employee and put it into a pivot table. The information comes from a CRM database

Each time I run it, the employee list may change because one week one employee may not bring in any money.

So, to do a week by week analysis, the sheet gets screwy because the names are missing.

Is my only way to keep the names the same each week to manually enter the missing names with a 0 value so they will still show up on a pivot?

The pivot ends up looking like this... so how I can get Fiona to show in order, but with zero? The data does not go into the pivot because for last week, there was no Fiona.

Current week	Sum of Sales		Last Week	Sum of Sales
Andy	$576,000		Andy	$432,000
Fiona	$55,092		Ridge	$275,459
Ridge	32,459

<tbody>
</tbody>

I hope this makes sense, I feel rather stupid.

 

[James006]

Hi,

You can easily show items with no data ...

See Debra's excellent explanation : http://www.contextures.com/xlPivot05.html#NoData

HTH

 

[Roar]

Thanks, I learned a lot from that but it did not work. Only because the source code only pulls people who had a sales, so if someone did not have a sale, they are not on there and no way to pull a blank.

I think the only answer will be to insert a row of each person with zero to get it to do it right.

Thanks,

 

[James006]

Hi,

Indeed, you are right ... the pivot table cannot "guess" the names of the missing sales persons ...

Updating your database with a zero amount will certainly solve your problem ...