Histogram for scattered dates (some days have no row, they should still show up, with height zero)

Question

I have this data (as you can see I did zero pushups on the 21st/23rd/24th):

Date       Pushups
9/20/2020  150
9/22/2020  100
9/25/2020  200

What is the easiest way to make a histogram with this data?
It should show empty days too, and have histogram bars, not points floating in the air. It should look like this:

^
|         █
| █       █                             👍
| █  █    █
| █  █    █
+----------->

In particular, it should not look like this:

^
|     █
| █   █                                 👎
| █ █ █
| █ █ █
+-------->

I have thousands of rows, so inserting empty rows is not a good solution (unless there is a quick way to do it in a annex sheet).

A scatter chart is not a good option either, unless anyone knows a trick to make it show bars instead of points in the air:

👎

Answer 1

Chart only uses data you input, it won't guess and fill the blanks.

Thus as a solution recommend using this supporting formula and build chart based on its results:

={SEQUENCE(MAX(A:A)-MIN(A:A) + 1,1,MIN(A:A),1),
  ARRAYFORMULA(IFERROR(VLOOKUP(SEQUENCE(MAX(A:A)-MIN(A:A) + 1,1,MIN(A:A),1),$A:$B,2,0),0))}

It creates sequence of dates starting from the oldest to the last one in your original data and then VLOOKUP values. If the date is missing in your dataset then it puts 0.

Range A:B is your original data range.

Next step: Format the left column as date, and select the generated table create the histogram as seen below.