Google Sheets: Fill out dates and replace blank cells with zeroes

Question

I'm trying to make a timeline graph of seed sales, and right now it's almost completely unuseable:

I think there are two reasons for this:

1. Not all dates have values associated with them, and therefore are not represented on the graph
2. Zeroes are represented as blanks, and therefore are not represented on the graph.

To illustrate this, here is what my current data table looks like to build the above graph:

Sales Data ($): Dates | Arugula | Bean | Beet | Broccoli | ... 10/21/2019 | 4 | | | | ... 10/19/2019 | | 20 | 5 | | ... 10/15/2019 | | | | 2 | ... ...

And here is what I think it should look like:

Sales Data ($): Dates | Arugula | Bean | Beet | Broccoli | ... 10/21/2019 | 4 | 0 | 0 | 0 | ... 10/20/2019 | 0 | 0 | 0 | 0 | ... 10/19/2019 | 0 | 20 | 5 | 0 | ... 10/18/2019 | 0 | 0 | 0 | 0 | ... 10/17/2019 | 0 | 0 | 0 | 0 | ... 10/16/2019 | 0 | 0 | 0 | 0 | ... 10/15/2019 | 0 | 0 | 0 | 2 | ... ...

Given that I have about ~100 columns for each seed type, and about 500 dates currently, what is the best way to make my data table look more like the second example to make a better timeline chart?

Answer 1

Create a clone table but in the cells that would import the numbers use =0+reference_cell.

Example: let's assume that Dates is recorded in A1, 10/21/2019 is recorded in A2 and so on, therefore Arugula is recorded in B1, 4 is recorded in B2 et cetera. In such case, if e.g. AA1 is blank you may select it and type =A1, then drag-and-drop AA1 to the right and below in order to repeat the "cloning" (referencing) process thus causing AA2 to store =A2, AB1 to store =B1, AB2 to store =B2 and so on.

Once you do this, go back to AB2 and replace =B2 by =0+B2 and then drag-and-drop AB2 to the right and down until you "clone" / expand this referencing formula to the entire range of cells that are storing numbers. This will cause the formulas to add zero to empty cells and add zeros to numbers (4, 20 et cetera) which will make no difference to such numbers.

The attached image illustrates my example.

You may also prefer to create a new sheet and then add this "clone" table in such new sheet.