1

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:

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.

Comparison of Google Sheets to Microsoft Excel

Microsoft Excel has the following options when formatting the axis: enter image description here

How can I tell Google Sheets to do the same thing?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.