How to Make a Trendline Graph in

Techniques in Excel 2008 for Mac Users

Basic Functions
XY Scatter Graphs/Trendlines
Forecasting with Trendlines
Localized Trendlines
Bar Charts
Pie Graphs

Basic Functions in Excel 2008

1. Main Toolbars: There are three main toolbars we will be using for LSP 120. The first is bar at the top of the screen that has drop down menus.

The second is the Gallery, which you will use to create graphs. You can open the Gallery toolbar on your screen by clicking the button at the top. The last toolbar is the Toolbox. You will use the Toolbox to format graphs and to find functions like PMT and MAX. You can open the Toolbox by clicking the button at the top.

2. Summation and Sorting: These buttons are at the top of the screen and work the same as in Excel 2007/2003.

You can also sort in this way: First select and highlight your data. Then go to the the Data menu at the top of the screen and select Sort from the drop down menu.

3. Paste Special: Since Macs cannot "right click," you can access Paste Special by going to the Edit menu at the top of the screen and selecting Paste Special from the drop down menu. A special note when Transposing: There are sometimes problems with this if you have a graph or other images in your worksheet. If you have a graph in your worksheet, first copy the data to a new worksheet, then transpose.

4. PMT Function: You can access the PMT function by using the Toolbox. Select the Format Builder section of the Toolbox. Either look up PMT or find it on the list under Financial. Double click on PMT. You will enter your information into the Format Builder. (This is also where you can find the MAX function.)

5. Other Special Notes on Excel 2008:

Freezing Cells: Macs do not have an F4 key, so to freeze manually you need to put a dollar sign ($) before the letter and number of the cell you are freezing. For example, if you wanted to freeze cell B23, you would type $B$23 into your equation.
Highlighting Non-Adjacent Cells: On a PC you will use the Control key to highlight non-adjacent cells, however it is not the same for a Mac. On a Mac you should instead hold the Apple key when you wish to highlight non-adjacent cells.

How to Make a Trendline Graph in Excel 2008

1. Select the data. To select the data for your graph click on cell A9, and then drag your cursor (it should be a thick cross) to cell B29.

2. Choose a chart type: In the Elements Gallery, click the Charts tab. (If you do not see the Elements Gallery, click on the Gallery button from the top menu.)

3. Select the group XY (Scatter). Within that group, choose Marked Scatter (the first option). A simple graph is created.

4. To add title and axis labels: First click on the chart to activate it. (Then click on the Toolbox button from the top menu if the Toolbox is not already open.) Select the Formatting Palette from the Toolbox. On the Formatting Palette, expand the Chart Options section. Under Titles, use the drop-down menu to select Chart Title or an axis. Below the drop-down menu, click Click here to add title, and then type a title. You can also remove the legend by looking under Other Options and using the drop-down menu to select None for Legend.

5. To add a trendline (including the equation and r-squared value), first click on the datapoints to activate them. Make sure you have activated all of them, not just one. From the drop-down box under the Chart menu at the top of the screen, select Add Trendline. A new dialogue box will appear. From the menu on the left side, select Options. Check the boxes for "Display equation on chart" and "Display R-squared value on chart."

Your graph should look like this:

6. Formatting: For this graph, it is suggested that you format the numbers on the y-axis to 0 decimal places. Double click on the y-axis (vertical axis). Select Number to change the decimal places, and select Scale to change the scale (if necessary).

To format the Trendline Label, first click on the trendline to activate it. From the drop-down box under the Format menu at the top of the screen, select Trendline Label. Then choose Number and change the number of decimal places to at least 3.

Forecasting with Trendlines in Excel 2008

1. Once you have created your XY scatter graph and added a trendline, you can forecast with the trendline to make predictions. First click on the trendline in the graph to activate it. From the drop down box under the Format menu at the top of the screen, select Trendline. A new dialogue box will appear. From the menu on the left side, select Options. Under Forecast, you can select how many years you would like the trendline to go foward:

Your graph with the extended trendline should look like this:

2. It is important to note that while you can forecast as far forward into the future as you want, Excel 2008 only lets you forecast 5 years backward. To forecast more than 5 years into the past, use the equation method. If you attempt to forecast more than 5 years backward, you will get an error message:

Localized Trendline in Excel 2008

1. Add data you want to use for the localized trendline: Click on your graph to activate it. From the drop-down box under the Chart menu at the top of the screen, select Source Data. Under Series select the Add button. Click in the X values box, then use your cursor to select the appropriate x-values for your localized trendline. Repeat with the Y values box. When you are finished, there will be a new series of data overlayed on the end of the existing data series.

2. To add a localized trendline: Click on the new points to activate them, then from the drop-down box under the Chart menu at the top of the screen, select Add Trendline. (Don't forget to add the equation and R-squared value.) Your finished graph should look like this:

How to Make a Bar Chart in Excel 2008

1. Select the data for your graph by clicking the first cell of data and then dragging over all the cells. Select the category labels and series labels as well.

2. Choose a chart type: In the Elements Gallery, click the Charts tab. (If you do not see the Elements Gallery, click on the Gallery button from the top menu.) Select the group Column. Within that group, choose Clustered Column (the first option). A simple graph is created. Use the Formatting Palette in the Toolbar to add a title and axis labels. Your graph should look like this:

How to make a Pie Chart in Excel 2008

1. Select the data for your graph by clicking the first cell of data and then dragging over all the cells. Select the category labels as well. In the Elements Gallery, click the Charts tab. Select the group Pie. Within that group, choose Pie (the first option).

2. To format: Use the Formatting Palette in the Toolbar to add a title. Under Other Options you can add Labels to your graph with either the percent, category name, or both. For this example, add Percent. To change the number of decimal places, click on the labels to activate them. Under the Format menu at the top of the screen, select Data Labels from the drop down box.

3. To change the number of decimal places, click on the labels to activate them. Under the Format menu at the top of the screen, select Data Labels from the drop down box. Choose Number on the left side. Uncheck the box marked "Linked to Source." The Category should be "Percentage" and you should change the decimal to at least 2 places.

Your graph should look like this: