Electricity graph Excel to Illustrator

In this lab, you'll go from a set of data in Excel to a graph in Illustrator, to an interactive graph in Flash. Excel's formatting capabilities are limited (though advanced Excel users can achieve impressive results), but it's a good tool for creating an initial graph which can then be modified in Illustrator. The interaction you'll add in Flash is a nice skill to have as well.

a. Download this data file. It consists of a column of dates and a column of the peak electricity consumption for that day in the community this data represents.

I suspect that some of you have little or no experience with creating charts in Excel while a handful have extensive experience and most fall somewhere in between. For those who have little experience, or would like a refresher, I would suggest that you watch the following three videos:

If you understand the following concepts, it will make it easier to figure out how to select the data you want for a given graph:

chart anatomy/vocabulary

x axis: the horizontal axis

y axis: the vertical axis

category axis: the axis for the categories the data falls into (typically this is the 'x' axis, but not always)

category axis labels: the names of the categories which appear along the axis.

series: a group of data values for a category

b. Select all of the data by clicking on the first cell (the one with the heading 'Peak for Day'), holding down the shift key and cicking on the last cell (the bottom cell of the second column).

c. Create a chart of this data by going to Insert ->Chart, then choosing the line graph option. You'll probably see something totally unsatisfying like this:

What's happening is that Excel is seeing two rows of number and trying to chart them as two series. You need to tell it that only one (the electricity numbers) is a series and the other (the dates) is the numbers to put along the x axis. Here's how:

Click on 'Date' and copy the reference that appears in the 'Y values' box. Then click the 'Remove' button to remove this series, and paste the reference into the 'Category (X) axis labels' box for the 'PeakForDay' series. You should now see a graph that looks much more like what you were expecting:

e. We want the final graph to be a line graph, but in the conversion to Illustrator, a line graph will be represented by a series of very short lines. To avoid that, turn the line graph into an area graph for now. Also stretch it out a bit by grabbing one side of the chart and pulling:

In the next step, you'll create another graph, then you'll be pasting both graphs into Ilustrator.

a. Download this data file. It contains a number of data points including the high and low temperatures for each day from June 1, 2008 to May 31, 2009.

The goal is to create a display that effectively answers the question "how warm and cold did it get on a given day and how did that change over time?" I'll walk you through the process I went through deciding this.

b. Select the data from the first 3 columns (click on the first cell, hold the shift key and click on the last cell) then choose Insert -> Chart and pick the Column Chart option.

c. Copy the chart, then click on another place in the Excel sheet and paste the copy. Then change the copy to a Line Chart.

Your results will look something like this (I've used the Mac version of Excel - yours may be somewhat different):

Now the task is to decide if either display is effective and/or how to change either to be more effective.

A bar (column) chart doesn't seem quite right because each bar feels like a stack of something and it doesn't make sense to have a stack of temperature or degrees. The line graphs seem more appropriate in this respect. But the two lines feel too disconnected from each other. What I really want to represent is the range of temperatures which were experienced, and that range is the space between the two lines. That gives me the idea of trying to color the area between the lines. Here's a strategy for that:

d. Turn the line graph into an area graph. Depending upon which series is in front (the low or the high temperatures), you may end up with the 'high temperature' series hiding the 'low temperature' series:

In the Mac version, right-click on the red area and select "format data series". You'll get this dialog box:

Choose 'Order' from the list on the left and you'll be allowed to change the order of the series.

In the Mac version, right click and choose 'format data series', then choose 'fill' and select white.

This feels better- now I can see the range of temperatures and how they fluctuate. I don't quite like the fact that the line moves in a continuous path from day to day. It's makes it look as if the high and low points always occured exactly at noon, then smoothly change to the high/low points for the next day. To address this, I'll go back to a column chart, but still try to get the effect of a shaded range.

g. Go to the bar (column) graph and change the 'low temperature' series to white just as you did for the area graph (I found I had to change the order of the series to be able to choose the 'low temperature' rather than the 'high temperature' series):

This isn't right yet because the white bars are next to the red bars. What I want to do is to make the white bars overlap the base of the red bars.

h. If you're using a Mac, right click, choose "Format Data Series" and then select 'Options' from the menu on the left in the dialog box:

a. To see what will happen if you do no preparation, copy the chart and paste it into Illustrator.

Depending up the version of Excel you're using (and perhaps the version of Illustrator), you may get an error message about the shading (which you can ignore), and you may end up with symbols rather than text:

b. Delete this graph (unless it looks just fine in which case you can ignore this step and the next), then go back to Excel and, with the chart selected, change the font to something Illustrator will definately understand ('Arial' is a safe bet).

d. Do the same with your electricity peak line graph. The result will look something like this (I stretched out my electricity graph much more than my weather graph - yours might not look that way):

Electricity Graph (Excel to Illustrator)