1

Recently both Numbers and Excel failed me in my charting needs. I got the job done in Numbers, but only with brute force by creating 28 charts in what should have been two.

Back in the 90s, when I was using Aldus Persuasion for my presentation needs, I remember there being a dedicated Mac OS charting program called something like Omni Chart (not related, to my knowledge, to The Omni Group) that offered more advanced charts than were at that time unavailable in Excel. Since it looks like I'll be doing more charting in the near future, I'm looking for something similar for OS X.

A major test of any recommended software would be its ability to easily create a chart like the one below, which shows comparison data for two groups who answered seven questions in two ways each (in this case, how important the question was and how well performance rated). The chart was created in Numbers and is actually fourteen charts, one for each column.

Comparison Column Chart with Stacks

Some points to mention:

  • The above chart is going to be created for dozens of projects in the future, so software price isn't too big an issue. I'm willing to spend money to save time.
  • I have a programming background. I'm open to something like the one of the Python charting libraries. I'm not restricted to Python, only OS X (self-imposed).
  • How about jQuery? ds3 might be good. – bjbk Aug 10 '15 at 0:48
  • 1
    Are you able to post the data? And the chart types that you tried in Excel and how you were able to create a single graph column in Numbers. It is possible that you use the wrong type of chart and there is actually a way to accomplish in those packages. – AMR Aug 10 '15 at 1:01
  • @bjbk These type of questions are perfectly off-topic on Ask Different. It's asking for software recommendations and that attracts opinion-based answers which aren't the type of answers for Ask Different. I'm downvoting because this is off-topic and is basically "I can't use Numbers and Excel, I need to do ..., please find software". Perhaps Software Recommendations will fare this question better but not here – Downgoat Aug 10 '15 at 6:31
  • Well, I thought that might be the case regarding the down vote, but when Googling this very topic I found someone else here had asked for graphing software and found OmniGraffle as a result. I assumed that perhaps the restriction on opinions was less strict here than on Stack Overflow. Regardless, I'm willing to pay a little reputation loss if it means I'll find worthy answers. – Chuck Aug 10 '15 at 6:44
  • 1
    @Chuck this is on topic here as software recommendations are. However you do need to describe what you need in more detail – user151019 Aug 10 '15 at 10:20
3

You were not able to get Excel to do this for you?

enter image description here

The only thing I was not able to do was to get the Horizontal labels centered underneath the columns, but you could likely do it by hand if you hid the column labels then manually typed them into the axis label instead.

This is how I got to the picture above.

Highlight A1 to N3 and then select Stacked Columns.

Next you will want to edit the Data Series to this

enter image description here

This shows Series 1 in the Y Values... Just select Series 2 and change it to $A$3..$N$3

You will have two data series and it will initially be stacked, so you will end up with a y-axis of 16 or so. Leave that for now as it is easier to do all of the formatting before you make the final changes to get it to look like this.

Go through and change your color scheme for all of series 1 to Green. You will then have to select the even numbered columns and change their fill to the lighter color individually.

Also on the version of Excel I am using there is a Gap Width slider under the formatting for Series 1 and 2 (Though I only had to set it once. I am not 100% sure if it is the same on other versions, but I set it to 25% and that gave me the spacing that you see between the columns.

Next select no fill for series 2. Then change the label color to a darker color so that you can see it. You will also need to change the position to Inside End so that they move to the top of the column Next change the Border to Solid Line and you can choose the dashed line option. I chose the fill as blue as that is what you had.

Now what you will do is select series two and you will choose Plot Series on Second Axis under Series 2 Format Series. This will drop then down so that instead of starting where Series 1 column ends, the zero-points are made the same for series 1 and series 2. When I did it the scaling was a bit off. So select Vertical (Value) Axis and make the Minimum Bounds to 0.0 and your Maximum Bounds to 10.0 (At least for this data set) adjust accordingly for different data sets under that Formatting option.

Next select the Secondary Vertical (Value) Axis and make sure that you set all of the parameters to the same settings as you did for Vertical (Value) Axis formatting. This will bring the scaling of the two series in line. You then select None for label position so that The Secondary Axis is no longer displayed on the graph.

At this point you are basically done. You can do minor things like deleting the Legend that shows series 1 and series 2 (or you can name them accordingly) and adding gridlines. You could manually go in a move the 7.6 in Question # 1 column 2, so that it is outside of the fill. etc. But you can basically get almost the entire way to where you want to be inside of Excel.

If I left anything out just ask it the comments and I'll respond.

UPDATE

As I am a bit of a perfectionist and was really annoyed by the fact you could see the dashed line around the entire border, I worked out the how to cover that up.

You would think that the data mapped to the Primary Axis would be in front... NOPE! That would make too much sense to a Microsoft developer... Anyway here is what you do.

Select Series2 and Plot Series on Primary Axis. Then Select Series1 and Plot Series on Secondary Axis. Adjust the scaling (AGAIN), and add a solid border of the same color to the fill of Series1 (sorry, but you have to do it for all of the even colored ones individually), and set the size to be the same point size as your dashed border for Series2, and Series1 now covers Series2, like this:

enter image description here

UPDATE 2: Bonus Material

So if you need to expand this for more question, insert columns in between the data. The graph remembers everything except the even numbered column color scheme, so you then only need to go in and change the fill and border color to the lighter green and you have added to the graph... Oh and of course you have to fiddle with your solution on the centering of the Question numbers, but probably not as bad as having to do everything manually in OmniGraffle.

  • @Chuck. I didn't go into it in my answer, but as you have a programming background, record a macro when you are doing the formatting of the columns and then look at the VBA code that is generated. It will likely give you a clue as to how to write a VBA script to automate the formatting of the chart for any amount of data without having to manually click the even columns for Series1 and change their Fill and Line Fill manually. – AMR Aug 17 '15 at 5:16
  • I just used your instructions to create the chart for the second project I'm working on like this, and it worked perfectly. Really, can't thank you enough for this. Your answer went above and beyond the call of duty. If I'm ever in New York, I owe you a six pack. :) – Chuck Sep 17 '15 at 2:31
0

I know this is a bit obvious but you could try Google Sheets.

It's scriptable in server-side JavaScript so if it doesn't do charts like that (and I don't think it does) then you might be able to hack in a solution.

  • 1
    Do you have an example of code that you have written, or seen written that does actually modify the basic charting functionality? It would help to determine if this is a possible solution. – AMR Aug 10 '15 at 0:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .