Comparing Monthly Data Points Across Three Years in a Bar Chart on Numbers

Question

Level 1

4 points

Comparing Monthly Data Points Across Three Years in a Bar Chart on Numbers

I have three columns of data for 3 years worth of data

Year
Month
Data point.

I want to create a bar chart that has each of the months for the three years beside each other in order to compare the monthly data points for all years. ie the first set of bars would be all of the January data points, one bar for each year then the second set of bars would be the three years of Feb data etc.

If I select the three columns and do a bar chart it simply creates a bar for each month year by year and runs them out chronologically from beginning of the first month, first year to the last month, last year so I dont get the "overlay" to compare year to year.

Any ideas? Apple support could not help.

[Re-Titled by Moderator]

iMac 27″, macOS 15.0

Posted on Nov 14, 2024 11:00 AM

Reply

Answer 1

Camelot

Level 9

55,305 points

Nov 14, 2024 12:30 PM in response to grandiat

It's doable, but your data format (one long table) is complicating the matter for you.

Numbers will automatically create your table if it were in the format of months in the rows, then one column for each year, like:

Then it's literally a one-click to create your chart since the data is in a format that Numbers expects.

The three options I suggest to you are:

1) Reformat your table into the above format

2) Create a new (dummy) table that looks like the above, but pulls the data from your one-long-list so you can continue to work the way you're familiar, but under the hood, Numbers is reformatting into a more logical (for it) format

3) Use a Pivot Table to automatically categorize your data - this is kind of like an auto-option 2

If you want to create a pivot table, just select the main table and tap the Pivot Table button in the toolbar. This will create a new sheet for your data.

You'll need to give it hints as to how to categorize the data, here's how I set it:

in this case, the 'Fields' section shows the column headers from my source table (yours may be different, and you may have others).

The important thing is to tell Numbers how to categorize the data. I did this by putting the Year into Columns, and the Months into Rows, and the Data Points into Values.

Now you should have the data in the format that Numbers can build a chart from. One click should get you your chart, which you can tweak to display how you like:

Reply