Line Graph Help: Connecting Missing Values & More

[wuts23143389]

Hi all, I am struggling with Illustrator's Graph Tool, hope you could help me with the questions below. Appreciate very much in advance.

I have excel data that needs to be converted into graphs via Illustrator before placing inside an InDesign document. My questions are:

1) How do I set up the graph's category value in excel sheet to appear correctly in Illustrator when copy/paste the date over? Here's what I have on Excel at the moment:

I however did not copy the first column as Illustrator doesn't understand that I want the first column to appear as category value along the x-axis. So I just copy over everything except first column like this:

In summary, my question is how do I rearrange the information on Excel sheet so when I paste data over the years would appear along the x-axis like this (below is generated within Excel):

2) How to make the lines connect automatically when there are missing values? As you can see, the original data has some missing values:

When data is placed in Illustrator, the graph looks choppy like below. You can see that the light green and black lines do not connect because of the missing values.

How do I generate lines that smoothen out the missing values like Excel could?

Pardon me if the questions are confusing. English is not my native language.

[Monika Gause · Accepted solution]

To get your years into the axes, you need to put them in quotes: "1990", etc.

Illustrator can't connect your gaps automatically. You would need to do that manually.

In general it might be easier to create the graph in Excel, save as a PDF and open that in Illustrator to further edit it. The values won't be editable then of course.

[wuts23143389]

Thank you very Monika, that was most helpful. I have one more question regarding graph, hope that is okay:

1) Is there a way to do I tell Illustrator to automatically rotate the category values when generating the graph? From inputting data, this is what the current graph looks like:

As you can see, there are too many categories and it is impossible to read, so I want to rotate them "each" 90 degree so it look something like:

However the Transform each does not work for me when using group selection tool for graph. When I click Transform each, no dialog box pops up.

Appreciate for any advise!

[Monika Gause]

You could select all the texts with the direct selection tool and then apply Effect > Distort and Transform > Transform. Rotate by 90°

Alignment of text will then get complicated though, because right or left-align won't work as expected. You could either shift baseline or use the transform effect for that as well. Which will of course be most effective when all the text are of the same length. If you need to align them differently then you will need to select them one by one.

[wuts23143389]

Thank you for the tip Monika, the Effect transform does get part of the job done. Do you have a tip on how to align them perfectly once applied the transform rotate effect? I created a makeshift guide then eyeballed the rest, but I would prefer to have them align perfectly (they are rotated 45%). You can see below I just used yellow makeshift guide then hand dragged each text – not optimal at all I'm afraid.

By the way I do have more questions if you and Bill do not mind. Is it possible for Illustrator to do these types of the following graphs?

1) Stacked bar graph with two sides, mirroring. Do I just create these by have two graphs, one align left, and the other align right, then move them together?

2) Column graph with floating marker representing values from another column of data. For the floating marker look, do I just create a graph "slide" design where the bar is invisible, with the marker positioned on top? Hmm I might just have answered my own question.

Thank you so much again!

[Monika Gause]

As for the alignment:

I'm not quite sure if Asian options have something that might get this job done, but it would be another experimental use of these options. Nevertheless eyeballing is not that bad, since some letters would need different treatment, e.g. the letter "d". If aligned automatically it would surely look oddly.

1: I would do it as you described. You culd mirror them, but you can also input negative values. Combining positive and negative values might get difficult with the stacking of the bars. But there's another difficulty in this: Illustrator adds the values in stacked charts.

2: you can combine different chart types in one diagram. It might get very tricky to align them like that.

How to create graphs in Illustrator

[wuts23143389]

Thank you very much Monika. I got the first graph to work fine but still haven't done the second one. For the first one, I didn't input negative value. I just create two graphs, one with value axis data (reflect this one, but I had to change one of the stacked value to full 100% in order to reflect properly), one without, then just tweaking around.

It works for now, thank you very much for your help.

[Bill Silbert]

Hopefully, the screen shot and explanation below will answer your questions:

In order for the years to appear across the category axis they must have quotation marks around them as shown in Illustrator’s graph data spreadsheet in the top picture. To smooth the lines “Mark Data Points” (highlighted in red in the bottom left picture) must be unchecked (it is checked by default). The Graph Type dialogs that I have shown here can be found by double-clicking on the graph tool in the tool bar while the graph is selected. I have shown here the three areas the dialog set up offers set up to meet your needs. Note that the Value Axis section shows that Tick Marks are full width to match the Excel style you showed in your last screenshot. If you need to reaccess the Graph Data at any time Go under the Object Menu to Graph while the graph is selected.

One last thing to keep in mind is that stylizing and/or reproportioning graphs manually after their creation will remove them from further control by the Graph Data spreadsheet. It seems that you can usually get away with some minimal scaling done by applying the scale tool using mathematical increment but even that seems to have its limits.

[wuts23143389]

Thank you very much Bill, that was most helpful! I have asked Monika another question, could you please also look into it? Basically I want to know whether it's possible to have the category value automatically generated on 90 degrees, and distributed evenly along the x-axis.

Also, I have been experienced a lot of crash while working with graphs. Is there a common problem that I should know in order to avoid crashing? (it's always when I try to edit the graph type)

And also is it possible to use Align tool for graph objects?

[Bill Silbert]

The only way to rotate the categories is to select each one with the direct selection tool and double-click the rotate tool in the tool panel and put in the value of 90°. The Align tool and most other functions that have been added to Illustrator since graphs were introduced in Illustrator will not work. For some reason the graph tools have not been updated over the years to take advantage of Illustrator's newer features.

[wuts23143389]

Thank you for your reply Bill, much appreciated. It's unfortunately the graph feature has not been updated indeed!

[Bill Silbert]

Yes. I did not realize that the Transform effect that Monika mentioned in her new reply would work with this old feature when so many other later features won't. I am constantly amazed at how instructive these forums can be for us all!

[wuts23143389]

I am that you are able to learn something new while teaching someone new like me! Appreciate for all of your help so far Bill!

[Bill Silbert]

You can create a stacked bar graph that will set up as you've shown but only to a point. See screen shot:

Using your earlier spreadsheet figures I switched the axis with the icon highlighted in red and adjusted the minimum and maximums through the Value section of the Graph Options. However, Illustrator will only allow either all positive values or all negative values for any given category. As you can see I gave negative values to the East Asia category which works fine but when I tried to make some of those values positive it would not let me. So doing two charts and "marrying them together might indeed be the best solution. As for your second question your own solution seems like it will work well.

[wuts23143389]

Thank you very much Bill! I was working and did not see your reply until I have actually finished the graph. I still have a few more graphs like these, will use the techniques you provide – that seems like it could be more efficient than what I did. I can do one positive, and the other one I convert all values to negative then superimpose on each other.

[Bill Silbert]

I just wanted to add one other suggestion. As I alluded to in one of my posts above Illustrator does not tolerate style changes for a graph while it is controlled by the graph data spreadsheet. For example if you change the dates from horizontal to vertical either through the rotate tool or the Transform effect that change will be reverted to its original state if you make even a single data point change in the graph data. I would like to suggest a way to make changes to your graph stylistically and still be able to make use of the graph data spreadsheet for input changes. Option/Alt drag a copy of the graph to sit below the original and while it is selected ungroup it. This will disassociate the copy graph from the graph data. Then select the original and make the entire thing have no fill and no stroke so that it will be effectively invisible in the Preview mode but completely visible in the Artwork mode. Then you can make any stylistic changes you want to the copy visible in Preview and any data changes  can still be made to the original in Artwork mode and then used as a guide and/or template to manually apply them to the stylized copy graph.

[wuts23143389]

Thank you for your suggestion Bill, I will take them to heart. Yes I have experienced times and times what you described. The most I did was to prepare object and paragraph styles accordingly, but your option seems much more efficient if the data needed to be changed several time.

[Bill Silbert]

Good luck!