I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or withdrawls have been made.
Static Copy:
https://docs.google.com/spreadsheets/d/1TJMjlMRIXGiEA6v_ZgMLORu3MFZEQhks7lx9xFDohL8/edit
Columns A & B were added to show the time intervals between records and are not part of the chart.
Details:
The production copy of the sheet is part of a much larger workbook of financial information. Normally, the data in this particular sheet comes from an import from another sheet, with the following syntax:
=IFERROR(
SORT(
FILTER(
{BILL_PAY_LEDGER!E3:E, BILL_PAY_LEDGER!F3:F, BILL_PAY_LEDGER!G3:G},
BILL_PAY_LEDGER!F3:F=$C$1
), 1, TRUE
), ""
)
...so the number of rows and the total date range is not known until the user selects a Bucket to view. The data range for the chart is set to C2:C1000,E2:E1000,F2:F1000 in the production copy of the sheet, to account for Buckets with a much longer history of transactions.
I would like the X axis to be formatted with a consistent interval from the first date to the last. I want the graph to show deposits/withdrawls on the dates they happened, but I want the balance to show a proper representation of the available balance over time.
Example:
The best way I can explain it is to provide a screenshot comparison of how Google Sheets spaces out the dates vs. Microsoft Excel. The most obvious difference is between 11/9/2020 and 12/9/2020. You can see that Excel properly condenses that period and Google Sheets seems to treat each data point as the same horizontal distance as the previous/next one.
Microsoft Excel has the following options when formatting the axis:
How can I tell Google Sheets to do the same thing?
