Line Graph Help: Connecting Missing Values & More

Contributor ,
Aug 05, 2017 Aug 05, 2017

Copy link to clipboard

Copied

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:

Screen Shot 2017-08-06 at 1.23.56 AM.png

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:

Screen Shot 2017-08-06 at 1.26.30 AM.png

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):

Screen Shot 2017-08-06 at 1.27.56 AM.png

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

Screen Shot 2017-08-06 at 1.23.56 AM.png

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.

Screen Shot 2017-08-06 at 1.32.12 AM.png

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

Screen Shot 2017-08-06 at 1.27.56 AM.png

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

Views

2.9K

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Adobe Community Professional , Aug 05, 2017 Aug 05, 2017

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.

Likes

Translate

Translate
Adobe Community Professional ,
Aug 05, 2017 Aug 05, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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:

Screen Shot 2017-08-06 at 2.43.43 PM.png

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:

Screen Shot 2017-08-06 at 2.49.58 PM.png

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.

Screen Shot 2017-08-06 at 2.51.37 PM.png

Appreciate for any advise!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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.

Screen Shot 2017-08-06 at 8.59.35 PM.png

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?

Screen Shot 2017-08-06 at 8.31.49 PM.png

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.

Screen Shot 2017-08-06 at 8.51.37 PM.png

Thank you so much again!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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.

Screen Shot 2017-08-06 at 10.12.04 PM.png

Screen Shot 2017-08-06 at 10.12.14 PM.png

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 05, 2017 Aug 05, 2017

Copy link to clipboard

Copied

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

GRAPH.png

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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

Screen Shot 2017-08-06 at 10.26.17 AM.png

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Adobe Community Professional ,
Aug 06, 2017 Aug 06, 2017

Copy link to clipboard

Copied

LATEST

Good luck!

Likes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines