Add entire calender year to pivotTable in Excel from sample with only few dated entries

Question

Hopefully somebody can help me figure out a better way to create what I need in a pivotTable. I have a table structured as follows:

Date      |    ID#     |    Revenue
---------------------------------------
1/1/14    |  123       |  $200.00
1/1/14    |  234       |  $99.00
1/5/14    |  455       |  $100.00
1/31/14   |  5666      |  $50.00
2/4/14    |   2454     |  $500.00
...
...
...
12/4/14   |  88484     |  $3000.00

Unfortunately, when I create a pivotTable off of this information, and then try to process the data all the way into chart data, it gives me only dated entries when there is an actual entry.

What I need is to not only create a table that shows dates that had $0.00 revenue, but also a chart that displays all 365 days, without manually going back day by day (as there are 1000+ rows) to enter a day that has a $0.00 revenue.

Any ideas?

There's no "clean" way doing this in Excel. You can create theses rows at the top of your data source — Seb
– Seb, Commented Oct 24, 2014 at 20:18
@Seb Just because there is not necessarily a 'clean solution' to do something does not mean there is an efficient way to do something. I can think of the farthest, most inefficient way would be running a macro to check date one by one and create a new 'blank' row with that date entry, but that, again, is the last possible solution. — hawkhorrow
– hawkhorrow, Commented Oct 24, 2014 at 20:37

Tom Sharpe · Accepted Answer · 2014-10-25 14:35:04Z

2

One approach would be to put the days of the year into a spare column, say D, by starting with 1/1/14 in D2, pulling down with the right mouse button and choosing 'Fill Days'.

Then put a formula into column E starting in E2 like

=SUMIF(A:A,D2,C:C)

to put in the total revenue for each day in column C based on all matching days in column A.

enter image description here

that's assuming that what you want is the total revenue for each day of the year whether or not it's zero.

answered Oct 25, 2014 at 14:35

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

hawkhorrow Over a year ago

Beautiful! Thanks so much. That will work perfectly!

Collectives™ on Stack Overflow

Add entire calender year to pivotTable in Excel from sample with only few dated entries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related