Phase 3: Editing an Excel chart for use in Illustrator

There are limits to how you can visually manipulate Excel charts. When you want to have a more customized look, you'll want to use Illustrator.

Illustrator allows you to create data-based charts with the chart tool. But that gets awkward with large amounts of data.

The solution is to simply paste an Excel chart into Illustrator and continue to manipulate the formatting there. In this lab, you'll learn how to do that in a way that makes it easiest to work with in Illustrator. The final product of this phase will be an Illustrator file containing your graph which isready for you to add the finishing touches (in phase 4).

Step 1: Testing the waters

In this step you'll paste a chart into Illustrator to see why you'll want to make some modifications within Excel first

a. Open the Excel file from Phase 1 ("EnergyConsumption_unformatted.xslx")

b. Copy the chart (Edit->Copy)

c. Open a new file in Illustrator

d. Paste the chart into Illustrator. You may (or may not) see an error box:

importing error

Just click "OK"

The result will look something like this:

excel chart in Illustrator

Obviously there is a problem with the font.

Less obvious is the fact that there are unnecessary elements. Click elsewhere to de-select the chart, then start trying to select individual elements to see how it's put together. If you select the blue area and move it aside, you may see one of the unnecessary elements underneath (a 'shadow' - you may not see this particular element, it depends upon settings in Excel):

shadow behind blue area

Explore the layers and sublayers to get a better feel for the complexity. Notice all of the Clipping Paths:

Step 2: Simplifying the Excel chart

The next step is to go back to Excel and clean up the chart to make it easier to process in Illustrator

a. Go back to your Excel file.

b. With the entire chart selected, change the font to 'arial' (a font Illustrator will definitely understand).

c. Select the 'Chart Area' and the get rid of the fills and borders (the white box behind the entire chart), then do the same for the 'Plot Area' (the white box behind the area within the axes - the image below shows this selected). When you do this, it will look messy because the data will now show through.

chart area selected

d. If you're using the Mac version of Excel, select each series, and change the fill from 'automatic' to any solid color (because Illustrator doesn't understand the fancy gradient that is used with the automatic setting).

change fill

e. Select the key and delete it (you'll label the lines directly in Illustrator)

key selected

f. Format the vertical axis to have no decimal places and remove the fill

g. Stretch the entire chart horizontally so all of the years appear on the x axis.

The result will look something like this (messy because the data shows through):

chart ready to import to Illustrator

h. Save the file as 'ExcelChart_readyToImport.xlsx'

Step 3: Paste the chart into Illustrator

a. Copy the chart and paste it into a new Illustrator file. It should look something like this:

excel chart pasted into Illustrator

You didn't get that error message (because you had removed the automatic fill) and the text is now readable.

If you open the layers, you'll find there are still a lot of groups with 'clipping path' sublayers. In the next step, you will get rid of some of those.

b. Try selecting the area for one country. You'll find that you can't do that (you'll get a selection box that is the size of the graph).

What's happening is that the chart has combined many elements with 'Compound Paths' and also used 'Clipping Masks'.

c. Watch this video to understand what these are.

d. Do this yourself. Select the chart, then release clipping masks and compound paths with 'Object -> Clipping Mask -> Release' or 'Object -> Compound Path -> Release'

Step 4: Place elements on individual layers

Next you will put each country's data on a separate layer and each axis on a separate layer to keep things tidy as you work.

a. To start, select one of the country areas (if you can't select it individually, release any compound path or clipping masks and try again):

one Series selected

b. Cut it (Edit->Cut)

c. Create a new layer (give it the name of the country)

d. Paste it into exactly the same place on the new layer by using Edit->Paste in Front.

series on its own layer

e. Hide and lock that layer to get it out of the way, then continue with the other countries, the x axis values, the y axis values, the grid lines and the axes themselves. This is what my list of layers looks like when I'm done:

final layers

f. Save the file as ''"



formatting in Excel in preparation for Illustrator

This involves removing elements that are irrelevant in Illustrator (such as the fill behind the chart area) and altering elements that Illustrator won't interpret correctly (like the default font and the automatic fills)

Releasing compound paths and clipping masks