Organise X axis labels by month - regardless of how many transactions took place in that month.

One trick you could try is something like this.

Make sure the date column is not a Header Column and insert a 2D Scatter instead of a 2D Line. Then set the Min: and Max: dates and vary the number of steps until you get the x-Axis labels you want.

The vertical "bars" are achieved by using negative Y-Axis Error bars with percentage set to 100, something as follows:

Note that if you don't really need visual presentation in a chart you can easily Add Category for Date and Numbers will automatically group by month:

SG

You cannot plot all transactions while still maintaining a consistent timescale, if the number of transactions per month varies. This would break all charting practices. What you can do is chart monthly subtotals only.

Take the example below.

There are more transactions in Feb than in Jan, so Feb will occupy a greater horizontal plane in the chart. We must standardize this. I will use a helper column to do so.

A new header column has been added with the formula shown. If it is the last transaction of the month, the value in this column will be the name of the month. If not, the value will be “Hide”. Note that this includes a forward reference, and the formula will break in the last row, as the reference to the row below will produce an error. You may need to play around with it to find a system that works for you - there are dozens of formula options that will essentially do the same thing. I just went with the simplest.

Finally, filter the table to show only rows in which the value is not “hide”. Make sure “show hidden data” is turned off in chart settings. Note that the table, and the chart, now only show the last transaction in each month.

It occurs to me that there is another way, if you are set on plotting all transactions on your chart.

My solution above is to standardize the data, from multiple, and variable, data points per month, to a single point. You can also go the other way with that: standardize the data by adding empty rows to periods in which fewer transactions occurred, until each month occupies an equal number of rows. In this case, that number would be equal to the number of transactions in the busiest month.

Automating such a process is possible. Use COUNTIF to count the number of monthly transactions, then take the MAX of those values. Suppose there were 90 transactions in July, the highest number. That’s an average of 3 per day, or one every 8 hrs. Set up a new table with 3 rows for each day. In the original table, add three helper columns. Set them up as follows.

The first helper column assigns a number to each transaction that resets to 1 each new day. The second helper column creates a reference string for each transaction, and the third just flags any transaction that is numbered higher than three.

Now set up a new table like this:

We use Index-Match to look up the values based on the reference string we just created. Finally, manually add in any flagged transactions where you can by inserting a row where needed and deleting an empty one. Make this your chart data.

Bismarck2387 wrote:

This is true, though ... The scatter charts do the job but they do not really look like actual bar charts. For example, he can not adjust the appearance (colour, etc.) of the bars.

Not entirely true, I think.😀 Probably worth spending more time with 2D Scatter Charts before concluding one cannot adjust their appearance. For example, the three below are all 2D Scatter Charts. There is a seemingly endless variation of appearances once can achieve, all without performing complicated numerical gymnastics on the underlying data.

So, as I originally posted above, a good way to "organize X axis labels by month - regardless of how many transactions took place that months" is simply to make sure the left column is not a Header Column and then insert a 2D Scatter chart and apply error bars, then do the usual adjustments one does in Numbers (many of them not specifically documented) to achieve the desired appearance one wants.

SG

This is true, though it’s an imperfect solution. One advantage to Numbers is its polished presentation of data. The scatter charts do the job but they do not really look like actual bar charts. For example, he can not adjust the appearance (colour, etc.) of the bars. My way is more complicated, thus it too is an imperfect solution, but it will enable him to make an actual bar chart. Up to him to determine which way is best for his purposes.

As demonstrated in my post above you can plot varying numbers of transactions per month while keeping the timescale on the x-axis consistent. The key is to use 2D Scatter rather 2D Column.

SG