Excel Graphing Tutorial: From Basic to Advanced Techniques

Note: this is a Microsoft Excel 2003 tutorial.  Excel comes ready with the capability to create graphs that are both professional-looking and flexible.  There are a variety of standard and custom-formatted graphs that are possible when using the Chart Wizard, from bar and pie graphs to more advanced graphical displays like bubble and area charts.

Excel Graphing Tutorial Scope

• Pre-graphing considerations and planning activities
• Line (XY Scatter) graphs
• Bar graphs
• Pie charts
• Advanced formatting and styling
• Statistical features

Please download the Excel file by clicking on the icon to the left to follow along with the exercises below using our sample data.

Pre-Graphing Considerations/Activities

Before you begin graphing your data set it is important to understand the basics of what makes a meaningful and visually pleasing graph.  Most importantly, consider the meaning of your data and what story you want to tell with the graph.  I have seen graphs created in an aesthetically pleasing manner but they had no real meaning that was cogent to goal of the exercise and held no insight into the data. These graphs looked good but they were total failures because the true aim of a graph is to visually display a set of data to an audience that is easier to digest than numbers in rows and columns.  It is wise to make a quick hand-drawn version (do not need to plot specific data points) of your graph so you at least have an idea of what it should look like when it’s completed.

After you have clearly defined the objective of the graph it is time to select the appropriate type of graph whether it is a bar, pie, line, or any others.  Certain types of data correspond to a specific type of graph very well, others it is up to the individual to be creative and think outside the box in terms of what will look good and display the data in the desired manner.  Bar graphs work well for comparative data sets, both at a single point in time as well as over a period of time.  Pie charts are typically only used to show relative percentages of a whole; meaning that pie graphs are only impactful if your data set represents pieces of a whole.  Line graphs are best used when plotting data over a period of time.  Bubble charts are meant for comparing three variables as opposed to the others we have discussed, which are two-variable graphs (X-Y axis only).  Bubble charts use the standard X and Y axes with the size of the bubble representing the third variable.

Visual appeal is as important to your graph as the data and story you are trying to tell.  The purpose of a graph is to visually display data in a manner such that it is easy to understand and see relationships.  Selecting colors that do not clash, ensuring labels and titles are in a legible font and large enough to read, and putting on all of the finishing touches are very important to producing an aesthetically pleasing graph. We will also take a look at some advanced formatting techniques such as filling bars or pie slices with a color gradient, texture, or other display tricks.  These details seem insignificant at first but you will find that with a few more moments of careful formatting you can turn the bland bar graph into a stellar visual presentation aid.

Line (X-Y Scatter) Graphs

The first example will be to create a line graph using the sample data in the attached file.  We are going to create a line graph that displays all of the quarterbacks’ passing touchdowns by year from 2002 through 2007.  Go to the sheet labeled Line graph data and select the green highlighted range of data.  The highlighted range should only be the Year (2002, 2003,…) and the number of touchdowns below it – no quarterback names or totals.  Click on the Chart Wizard icon in your toolbar or go to the Insert menu and select Chart.  Choose the XY (Scatter) type and then the subtype in the bottom left hand corner of the five choices (highlighted in black in sample picture).  Why not choose the Line chart type for a line graph?  If you do not intend to have Excel calculate statistical information then the Line type is just fine and will work very similarly to the XY (Scatter) type.  But we are going to insert trendlines and do a few other statistical operations later on so that is why we are using the XY (Scatter) chart type.  Click Next once you have selected the proper chart sub-type.

On the Range tab of the Chart Source Data menu ensure that the “Series in:” parameter is selected “Rows”.  Then click the Series tab and add some meaningful titles to your chart.  You should see a box at the bottom left of the wizard with a listing of series in it labeled “Series 1, Series 2, etc.”  Click on Series 1 to highlight it and then click in the box next to “Name:” to put your cursor in the name field for Series 1.  Then click on the cell in the worksheet with Chad Pennington (A3).  You should see the Name field populate with the sheet name and cell reference and “Chad Pennington” should appear in the Series box where “Series 1″ used to be written.  Continue this process for all ten quarterbacks going right down the list, Chad Pennington is Series 1 and  Tom Brady is Series 10.  Click Next once you have finished assigning the quarterbacks’ names to the proper series.

This will bring you to a menu that has five tabs across the top, left to right: Titles, Axes, Gridlines, Legend, Data Labels.  It is always advisable to give you graph a title that is descriptive yet concise and to label your axes.  To do this select the Titles tab and fill in a title and label the axes.  In the example I have only labeled the Y-axis because I felt it was self explanatory that 2002, 2003,…, 2007 were years, but that may be a bad assumption…  From there I would recommend clicking on the Legend tab and changing the legend placement to the bottom as opposed to the default, which is usually right-aligned.  I find that a right or left-alignment typically cuts down on too much horizontal space when making a line graph and removes the visual spacing impact of the trending between points.

Once you are satisfied with your selections choose Next and then Finish to place the graph as an object in the Line graph data worksheet.  You can then click and hold on the graph to move it, or re-size it by clicking the black boxes on the border of the graph that usually appear at the corners and the middle of the vertical and horizontal edges.  You now have a basic line graph comparing ten different quarterbacks passing touchdowns per season from 2002 through 2007.

Bar (Column) Graph

Bar graphs are a staple of graphing utility that can be used in a vast array of situations.  In this example we are going to graph Tom Brady’s and Peyton Manning’s interceptions by year from 2001 through 2007.  Click on the Bar graph data sheet in the sample file.  Select the range of data that is highlighted green, this time excluding the year column headers and the totals.  Click on the Chart Wizard icon on your toolbar or go to the Insert menu and choose Chart.  This will bring up the Chart Wizard window and you can select the chart type and sub-type you want to use.  Select the Bar chart type and the sub-type that is the leftmost in the middle row (side by side 3D bar graphs).  Click Next.

As with the line graph exercise, ensure that the Series in: Rows parameter is selected on the Range tab of the Chart Source Data menu. Once that is confirmed, click on the Series tab of the Chart Wizard window.  This time the Series are named properly but the X-axis does not have the proper labels.  At the bottom of the Series tab you will see a box that says Category (X) axis labels next to it.  Click inside the box and then use your mouse to highlight the cells with the years in them (B3 to H3).  The years should now be the X axis labels.  Click Next to be taken to the last step of the Chart Wizard.  Assign a proper title to your graph and the axes.  The Legend placement of right-aligned is acceptable depending on dimensions you make the graph once completed.  However, I almost always recommend choosing a bottom placement for the Legend.   Click Finish to create the graph as an object in sheet Bar graph data.

Pie Charts

Pie charts are excellent visual displays that most everyone can immediately wrap their minds around.  Making a pie chart in Excel is very easy provided that your data set lends itself to this type of display.  In this example we are going to take all ten quarterbacks’ interceptions in 2007 and report on the percent of the total interceptions each quarterback represents.  Click on the sheet Pie graph data and select the green highlighted data range.  Click on the Chart Wizard icon or go to the Insert menu and select Chart.  Choose the chart type of Pie and any sub-type that you want to try out (I am choosing Pie with a 3D Visual Effect – love 3D graphs).  Click Next.

For this pie chart the Chart Source Data menu Range tab should have the Series in parameter of Columns selected.  This is different from the two previous exercises in which we selected Rows for the Series in parameter.  The pie chart should be almost completed at this point except for a few extras.  Click Next to be taken to step 3 of the chart wizard.  Put a title on the graph that is descriptive and then click on the Data Labels tab.  For pie charts I prefer to at least show the percentage on the graph itself.  Try selecting a variety of labels or combination of labels (series name and percentage, value, value and percentage, etc.) to see what you like or what is appropriate given the situation.  I am only going to label it with the percentages in this case.  Click Next and then Click Finish to create the pie graph as an object in the Pie graph data sheet.

Formatting Graphs for Presentation Quality

The default color schemes Excel assigns to your graphs may not always be the most ideal.  Visual presentation is sometimes as important as the content itself, so it is a good idea to pretty-up that newly made graph.

Formatting Axes and the Legend

Many times Excel will default the font size of the axes and series to a size that is unnecessarily large, thereby reducing the amount of space allocated the graph.  You can change the font size, style and other aspects by right clicking on your target (Legend, X axis labels, Y axis labels) and choosing Format…  This will bring up a formatting window, typically with multiple tabs, where you can format the font style and sizing, the alignment (vertical, horizontal, any desired angle) of the text.  If you are choosing to format an axis then you will have the option to choose the number formatting and scale of the axis.

Formatting the Plot Area and Other Fillable Objects

The graph background is called the plot area and can be made far more presentable by right-clicking anywhere in the shaded area created by the x and y axes and selecting Format Plot Area.  This will bring up a window with color selections, line and border selections, and a few others.  The easiest way to make the plot area look better is to simply choose a solid color that will not obscure the plotted points, lines or bars in any way, an enhancing effect would be even better.  To instantly make this solid color choice look extra-fancy is to click on the button underneath the color palette called Fill Effects.  For the line graph example in this tutorial I am going to stay with the default 25% gray plot area color but I am going to utilize a Gradient fill effect with one color and shift the dark/light contrast bar about 75% toward the light, and a horizontal shading style with darker gray at the top fading to lightest at the bottom.  Play around with this functionality a lot because you can be very creative yet very disastrous.

The fill effects technique is applicable to filling any 3D graph (bar, cone, pyramid, line, etc.) and all pie chart slices.  The trick to performing it properly on a series is to left click once on an individual line, bar or any similar graph object.  Pie charts are a little different so we will address pie charts in a minute.  After left clicking ONCE on the object, right click on it to bring up a menu and select Format Data Series.  This is where you can get creative coloring your graphs and charts.  Try to select colors that stand out against the plot area you choose, for instance do not use a yellow object on a light or white plot area.

When dealing with pie charts left-clicking once selects the entire pie, not just one slice.  If you want to select one slice, left click once anywhere on the pie and then left click for a second time on the individual pie slice that you want to format.  Then right click on the selected slice and choose Format Data Series from the menu that appears.  Pie charts create a unique visual challenge in that you must utilize colors that are distinct from each other enough to discern easily at a distance but they must also compliment one another.  Be sure to use shading styles that compliment each other when using color gradients because you do not want to use a light to dark middle stripe on one pie slice and a from top left two color gradient for another slice.

Formatting Lines

Believe it or not, formatting lines does indeed warrant a full subsection.  There are seven key formatting elements to a line in an Excel graph: line style, line color, line weight, marker style, marker foreground color, marker background color, and marker size.  The line style drop down allows you to choose a variety of dotted, dashed and solid line styles.  Line weight is important depending on the number of lines on your graph and the expected audience distance.  You can also select a color but there is more to consider with that option.  Most of the lines have some sort of marker, a shape that is placed at the plotted points that the lines are merely connecting.  These shapes are also colored and are defaulted to be the same color as the line.  If you choose a new color for the line you will have to decide whether you want to keep the marker foreground and background the same color as the line or if you want to play around with any combination of different colors for the line and the marker foreground and background.

Statistical Functionality

We can use the line graph that was created earlier to show off Excel’s impressive statistical prowess.  Simply right clicking on a data series (line) and selecting Add Trendline you can draw a regression line based on that series.  There are six different types of trendlines you can insert including linear, polynomial, moving average, logarithmic, exponential, and Power.  The polynomial trendline can be customized to accommodate higher-exponent functions and moving averages can be customized for the period.  This is all done on the Types tab of the Add Trendline window.  Clicking on the Options tab of the Add Trendline window will bring up more details relating to the type of trendline you chose.  This tab also has check boxes if you want to set the Y intercept and also to toggle display of the trendline equation and R-squared value on the graph.

Interested in learning more? …check out the tutorials page for more Microsoft Excel techniques.