How to make graphs in Excel

[dread]

This write up is intended to teach AP users the basics of excel. All of the instructions herein are based on microsoft excel 2003. The menus in later versions of excel are different, but the principles are the same so you may be able to figure out the basics based on this write up by going through the menus. The help feature in excel is very useful so I suggest using it if you get stuck.

Getting data from the AP
The first step to making a graph in excel is getting your data from the AP into excel. First open the AP manager and connect the AP to the computer. Once connected select advanced under the view tab in the AP manager. Now you should see your logs in the order they were collected. Right click the log you want to access and you should see an option to download the file. Save the file to any folder you want.

Opening your data in Excel
Excel may organize the data for you depending on the version you are using. However, because the file from the AP is a csv file instead of xls you may have to go through an additional process to get you data in order. Once excel is running go to the file menu and select open. Then select the datalog you want to work with. If it doesn’t open automatically you will be prompted with a screen called the text import wizard. If this happens make sure you select Delimited under original data type, then click next. On the next screen you will be able to select your delimiters. You need to check the comma box. Once this is done click finished (not next) and your data will be organized by the PID’s that you logged.

The Basics of Excel
There are some simple terms that you should know when using excel. Your data is organized in vertical columns starting with column A. The numerical values are in horizontal rows that are numbered. Each box is referred to as a cell. You can click on the top of a column in the box that has a letter to highlight the entire column. You can also click on the top left corner in the empty box where the columns and rows meet to select your entire data set. This will be useful when organizing your data to make a chart or graph.

Organizing your data
To organize your data I suggest creating an additional sheet where you can copy the data you wish to graph. To do this right click the tab that should be labeled datalog x and select insert. A window will pop up from which you will select worksheet. Now you should have your original sheet and an additional sheet that is labeled sheet 1. You can rename this to keep organized by right clicking the sheet and selecting rename. You can also go back to the original sheet by left clicking on the tab.

Now that you have your second sheet you can select the data you want to graph. Remember more data isn’t always better. You want your graph to be clear, so it is easy to interpret. You should select only the most important data, I recommend a 5-6 PID’s. To do this go to your original sheet, highlight the column, right click anywhere within the highlighting and select copy from the menu. I suggest starting with your RPM column since this will be your x-axis data. Once you have copied the data go to the second sheet you created and paste your RPM data in column A. This can be done by right clicking the first cell in the column and selecting paste. Repeat these steps with the additional data you want to include in your graph. Once all the important data is in the second sheet you should isolate the part of the log you want to graph. In other words you probably want to show a WOT run so you don’t want the data that shows you cruising. The best way to isolate the data you want is to delete the unnecessary data. To do this select the data, right click and delete.

Graphing
Now that your data is organized you can make a graph. First off your x-axis is the horizontal axis and the y-axis is your vertical axis. Go to the sheet that you pasted your data in and highlight all the data including the titles (i.e. A/F ratio, fuel pressure….). Once your data is highlighted click on the icon in the top menu bar that looks like a small graph (you can also go to the insert menu and select chart). The chart wizard should open up and you can select the graph you want to use. I suggest the line graph. Now you will have to select a chart sub-type. The best one to use is in the top left corner. This will give you smooth lines, but you can use whatever you want. Once selected click next and the chart source data page will come up which shows a preview of your graph.

Don’t worry if it looks horrible you can fix that later. You will see two tabs at the top of the page, one that says data range and one that says series. Click on the series tab, which will divide your data by the PID’s or variables you are graphing. A series is the data from only one of your columns. You shouldn’t have to mess with the series unless you want to remove a variable from your chart. However, you do need to add your x-axis at this point, which we will make the RPM column. To do this put your curser in the category x-axis labels box and then highlight the rpm column in your data sheet. You should now see your rpms on the x-axis. Now click next to get to the chart options page. Here you can label your axes and add gridlines, but none of this is necessary if you are lazy. You should at least go to the legend tab and click top under the placement option. This will move your legend to the top of your graph, which will make the graph easier to read. Once you have gotten the graph the way you want it click next to get to the chart location screen. I like to make my graphs a separate sheet, by clicking the circle next to “as new sheet” and label the sheet to your liking. Click finish and you should see your graph.

Making your graph pretty
At this point your graph is probably difficult to read especially if you have graphed PID’s with low values like boost and a/f ratios along with PID’s with high values like fuel pressure. The easiest way to address this problem is to create a second y-axis. This can be done by right clicking the line that represents the data you want on the secondary y-axis. In the menu that pops up, select format data series. Now you should have a window with six tabs. Click the tab that says axis and then click secondary axis. On a side note, you will also notice a tab called patterns. You can change the color or weight of your lines using this tab. You should have a graph with two y-axes, which should be much easier to read. If you have another PID that would work better with the secondary axis you can repeat the procedure to add it to the secondary axis. However, you cannot make more than two y-axes in excel so don’t waste your time trying. You should consider this when choosing the data you are going to graph.

If you are still not happy with the look of your graph you can right click anything and adjust colors, fonts and backgrounds. If select the entire graph by clicking on the border, you can then right click to access chart options or your source data, which will allow you to add gridlines or remove series that you decided that you didn’t need. Make sure you are selecting the entire graph if you do not see these options in the right click menu. Once you get your graph the way you want it you can save the template and you will not have to do all of the formatting the next time you graph your log.

To make a template right click on the border of your chart and select chart type. Again, make sure you are selecting the entire graph if you do not see these options in the right click menu. In the chart type window select the custom type tab at the top. Now in the box that says (select from) you should check the circle next to (user-defined). Now click (add)and name your chart. The next time you make a graph select your template from the custom type tab and all of your formatting will load. Just make sure that you are graphing the same PID's in the same series order you did when you made the template and your graph will work perfectly. However, if you made a graph with two y-axes and you change the order of the PID's your axes may get messed up. You may want to create multiple templates if you are graphing different PID's.

Making your graph a jpeg, to post on the web.
Obviously you want to show off your new graph so you need to make it a jpeg. I use power point for this task, but I am sure there are other ways. To get your graph to power point select the entire graph, go to the edit menu at the top of the screen and copy the graph. Do not just paste the graph in power point, because the formatting will change. Instead go to the edit menu in power point and select paste special. In the window that pops up select (picture) enhanced metafile and select ok. Now you should see your graph. Right click the graph and select save as picture. In the window that pops up you need to change the file type to jpeg by using the pull down menu below your file name. Once you save you can upload your graph to any photo hosting website.

I hope this helps some of you out. Excel is actually very intuitive, so the best thing you can do is play around with it. Now lets see some datalogs.

Here are some of mine

3rd gear notice fuel pressure is on the secondary y axis. Everything else is on the originial y-axis


4th gear run



same 4th gear run but with different PID's. This graph is harder to read because I graphed 8 PID's which is too many in my opinion. You will also notice that the 4 PID's (mph, actual and relative pedal position and wastegate) that are on the right of the legend are on the secondary y-axis. The 4 PID's on the left are on the smaller y-axis.

[FreeFlyFreak]

Thanks for that dread.
I figured some of it out using Help, but now I have a fuller and more detailed picture of what to do.

To do the jpeg.
What I did was, print screen and then paste into MS paint, and save as GIF or JPEG

Steve
FFF

[dread]

updated with pics

[bova]

another example of a pretty graph, haha, although its a little busy

[bioevolve]

I likey! Very Purrrrty Graph!



FYI: you can just highlight the colums you want to chart and go straight to charts, don't have to make another worksheet nor organize, bypass that step.

I did 14 pids on the logging, sampling is 3 per second.

[dread]

You can definately bypass making another worksheet, but I like to do it anyway, so that I can always have my original unmodified database. Your graph looks good, but it is also a good example of what I was saying about graphing too many PID's at once. It is hard to interpret the data. Here is another one I just took that was logged using only 10 PID's The resolution is much better.

[buttons]

Ok , i got my graph ready. Where are you guys uploading the pictures too?

[bova]

i upload mine to my site, haha.

[dread]

I use photobucket.

[fjames]

I broke down and downloaded openoffice on my dialup. Your tutorial pushed me into it, especially the bit about having to export to get an image. Anyhow, made a chart of the only log I have worth graphing. Basically I'm just clicking the shit of things until something happens, but it turned out in a way I can make sense of it.

[FreeFlyFreak]

Originally Posted by fjames I broke down and downloaded openoffice on my dialup. ]

WHAT!!!

You are on dialup.

You have to be kidding right??!!

[fjames]

Amazing huh? Ask me what I think of the phone companies. I live in the middle of the middle of nowhere so my only option is sat, but that's not much of an option to me. I use a lady friend's laptop a lot and it makes me fucking crazy, and she's only got a lousy 3Mb. And she only lives 5 miles away. I've got all these homicidal thoughts running through my head now, so I better stop.

[itzl0l]

Yay! check out my kick ass fuel pressure.....thx mazda! Oh yea....v101 "stock" map right now. 3rd gear. (on this graph fuel pressure uses the same scale as rpm....it drops below 1000psi).

[SharkDiver]

Hey all,Im using open office and I cant figure out how to get rid of the #s on the left side of the sheet.Those #s keep showing up on the bottom of my graph instead of rpm.Im sure its something simple that Im doing wrong but I cant figure it out.

Thanks

[fjames]

Originally Posted by SharkDiver Hey all,Im using open office and I cant figure out how to get rid of the #s on the left side of the sheet.Those #s keep showing up on the bottom of my graph instead of rpm.Im sure its something simple that Im doing wrong but I cant figure it out. Thanks

Not sure what you mean, but in default settings, left is your primary Y axis and right is your secondary Y axis - you get that right? Have you found editing mode? Right click an open space on the chart - it gives you an edit option, take it. If it doesn't, you're already in edit mode, so hover over a line and choose "object properties." Not sure if any of this helps ...

[94jedi]

this should be a sticky. Dread is the effing man. I didn't need the tutorial but the masses of gnubs surely do. lol.

[dread]

thanks man

[dread]

I updated the first post with a section on how to make a template of your graph so you do not need to reformat everytime you graph. I copied it to this post as well.

To make a template right click on the border of your chart and select chart type. Again, make sure you are selecting the entire graph if you do not see these options in the right click menu. In the chart type window select the custom type tab at the top. Now in the box that says (select from) you should check the circle next to (user-defined). Now click (add)and name your chart. The next time you make a graph select your template from the custom type tab and all of your formatting will load. Just make sure that you are graphing the same PID's in the same series order you did when you made the template and your graph will work perfectly. However, if you made a graph with two y-axes and you change the order of the PID's your axes may get messed up. You may want to create multiple templates if you are graphing different PID's.

[Lama]

I don't know what I'm doing wrong but I'm unable to see the other line except RPM. They are all on the legend but not in my graph.

If I select all the spec and click graph tool they are not separate in diffenrent PID ??? What I'm doing wrong?

[dread]

can you state your question more clearly. I am not sure what you are asking. RPM should be on the x-axis. select the series tab in your graph remove rpm if it is on the y-axis and add the data to the x-axis box.

[fjames]

I've never been able to directly effect the x-axis in openoffice. The only way I can get RPM to the x-axis is that the first column always goes there, so I edit the sheet accordingly. When I go to edit the x-axis and get the same dialog box as the y-axis for scale and such, it's always grayed out. I'm probably missing something, and it's workable the way it is, but I wish I could fool with the x-axis like I can the y-axis.

The question above might be referring to what i see in openoffice, which is if you just default your way through to a line graph, everything but one is on the y-axis, and piled on top of each other so it looks like a line in the graph. The first step for me is to right click and get them out of there and onto the secondary y-axis. Then of course you get to get confused by the two y scales and which is where cause all of a sudden you're only showing 10lbs boost because you've got stuff on the wrong axis or whatever, but it's fun figuring it out.

[dread]

Excel is different than open office. You need to select the data seperately for the y axis.

[mdd5148]

logged my first run with the accessPort today... pretty cool. Here are the results:

I am running the Stage 2+MSCAI 91 v101e. This was a 3rd gear pull.

[dread]

looks good. I am assuming you meant 103e. Did you use the writeup to make the graph.

[mdd5148]

I have a speed6, I think this is why I am using a v101 map. I bet someone more knowledgeable than me could speak to this.

I did use your write up to make the graphs. It was very helpful and gave me a format to "standardize" the chart to.

[dread]

oh I keep forgetting people have the ms6. Looks good, but there is still room for improvement. Hopefully Christian can bump the boost up and lean out some more for you.