Skip to main content
W
wuts23143389
Inspiring
August 5, 2017
Answered

Line Graph Help: Connecting Missing Values & More

  • August 5, 2017
  • 2 replies
  • 6827 views

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.

    This topic has been closed for replies.
    Correct answer Monika Gause

    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.

    2 replies

    Bill Silbert
    Community Expert
    Bill SilbertCommunity Expert
    Community Expert
    August 5, 2017

    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.

      W
      wuts23143389Author
      Inspiring
      August 6, 2017

      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
      Community Expert
      Bill SilbertCommunity Expert
      Community Expert
      August 6, 2017

      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.

        Monika Gause
        Community Expert
        Monika GauseCommunity ExpertCorrect answer
        Community Expert
        August 5, 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.

          W
          wuts23143389Author
          Inspiring
          August 6, 2017

          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
          Community Expert
          Monika GauseCommunity Expert
          Community Expert
          August 6, 2017

          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!


          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

            Terms & ConditionsAccessibility statement
            Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices