You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

How do I make a chart from two columns of data?

Hi,


I'm trying to create a daily orders/revenue chart from the following data:



What's the easiest way to make this into a chart with dates along the X axis, and one line on the Y axis showing # of orders/day, and the other showing total $/day? I must be missing something, but every time I click and try to create a chart, it creates an insane block of days going back many years, for some reason.


I'd be grateful for any advice here. Thank you!

MacBook Pro 13", macOS 10.13

Posted on May 16, 2019 5:57 PM

Reply
Question marked as Top-ranking reply

Posted on May 17, 2019 3:23 AM

One way is to set up a separate table containing each date's count and amount. Then base the chart on that.


Starting with something like this:




Collapse Peer Groups:



Select the dates that are in the visible cells, and command-c to copy.


Click in a separate table and Edit > Paste and Match Style




Add a formula for the day's count (number of orders):




Add a formula for the day's amount of total orders :



Fill down the formulas down:



With the cells selected in the Chart Data table insert a 2D Scatter Chart:




Giving you something like this:





Because the counts are so small relative to the amounts, you can change to logarithmic scale on the Y axis to see the relationship more clearly.




And experiment with the Steps in Axis panel and Connection Lines in the Series Pane (after clicking a series in the chart) giving you something with the appearance you want.



SG

2 replies
Question marked as Top-ranking reply

May 17, 2019 3:23 AM in response to robtour

One way is to set up a separate table containing each date's count and amount. Then base the chart on that.


Starting with something like this:




Collapse Peer Groups:



Select the dates that are in the visible cells, and command-c to copy.


Click in a separate table and Edit > Paste and Match Style




Add a formula for the day's count (number of orders):




Add a formula for the day's amount of total orders :



Fill down the formulas down:



With the cells selected in the Chart Data table insert a 2D Scatter Chart:




Giving you something like this:





Because the counts are so small relative to the amounts, you can change to logarithmic scale on the Y axis to see the relationship more clearly.




And experiment with the Steps in Axis panel and Connection Lines in the Series Pane (after clicking a series in the chart) giving you something with the appearance you want.



SG

May 18, 2019 4:34 AM in response to robtour

Hi robtour,


Take a look at Template Chooser > Charting Basics > 2-Axis Graph.

That shows an X axis and a Y1 axis and a Y2 axis


You can change the appearance of a series from Column to Line or Area.

Click on a Series.

Format Panel > Series > Show Series As


'For your data with 2 Y axes, it may look like this, using SG;'s suggestion of a Summary table with COUNTIF and SUMIF as the data for the graph.

Please call back with questions.


Regards,

Ian.





How do I make a chart from two columns of data?

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.