PChem Teaching Lab

Using Excel 2016 - Add a Trendline to part of a Graph · Add Multiple Trendlines to a Graph

ADD A TRENDLINE TO PART OF A GRAPH
1. Enter the data opposite onto Sheet4 and generate a graph so that your worksheet looks like the one displayed.
To only analyse and add a trendline to the initial rise in profit (i.e. the first 5 data points).

2. Click on the graph, then click the Design tab under Chart Tools, then click on the Select Data icon.

3. Select Series 1 and then click Edit.

4. Click the box under Series X Values and then highlight cells containing the first 5 x-values (in this case A2 to A6).

5. Click the box under Series Y Values and then highlight cells containing the first 5 y-values (in this case B2 to B6).

6. Leave the box next to Series Name blank and click OK.

7. In the Select Data Source window click Add.

8. Select the remaining Series X values and Series Y values (in this case A7-A13 and B7-B13 respectively).
Leave the Series Name blank and click OK (twice).

9. Your graph should now look like the figure opposite.
Note that the two series that have been defined/plotted are shown with different colours.

10. Click on one of the first 5 data points to select the data series and then click on the Design tab under Chart Tools.

11. Click Add Chart Element and select Trendline followed by More Trendline Options from the bottom of the dropdown menu that appears.

12. Select a suitable trendline (with equation) and then click Close.
Your plot should now look like the one shown opposite.

ADD MULTIPLE TRENDLINES
If you also wanted to analyse the decrease in the profit at the end of the day (i.e the last 5 data points) and add a second trendline to the graph, the same process is used:

13. Using the Select Data icon, create a new data set for the last 5 data points, and adjust Series 2 to just include the remaining data points (in this case A7-A8 and B7-B8).
This should give a graph that looks like the figure opposite.

14. Select one of the last 5 data points, and create a trendline for them.

Assuming you want to change the markers on the graph so that the data appears as one, not three, series:

15. Right click on one of the markers (that you wish to change) and select Format Data Series.

16. Then adjust their appearance under Marker Options, Fill and Border, so that the desired appearance is achieved, i.e. so they look the same as the series you want all the data to appear like.
To change colour of marker click on paint can on the right and change colour.

17. Repeat for all the data series until all the markers are changed as desired.
The final plot should then look similar to the one opposite.

Data Analysis

Using Excel 2016 - Contents Page

ADD A TRENDLINE TO PART OF A GRAPH
1.	Enter the data opposite onto Sheet4 and generate a graph so that your worksheet looks like the one displayed. To only analyse and add a trendline to the initial rise in profit (i.e. the first 5 data points).
2.	Click on the graph, then click the Design tab under Chart Tools, then click on the Select Data icon.
3.	Select Series 1 and then click Edit.
4.	Click the box under Series X Values and then highlight cells containing the first 5 x-values (in this case A2 to A6).
5.	Click the box under Series Y Values and then highlight cells containing the first 5 y-values (in this case B2 to B6).
6.	Leave the box next to Series Name blank and click OK.
7.	In the Select Data Source window click Add.
8.	Select the remaining Series X values and Series Y values (in this case A7-A13 and B7-B13 respectively). Leave the Series Name blank and click OK (twice).
9.	Your graph should now look like the figure opposite. Note that the two series that have been defined/plotted are shown with different colours.
10.	Click on one of the first 5 data points to select the data series and then click on the Design tab under Chart Tools.
11.	Click Add Chart Element and select Trendline followed by More Trendline Options from the bottom of the dropdown menu that appears.
12.	Select a suitable trendline (with equation) and then click Close. Your plot should now look like the one shown opposite.

ADD MULTIPLE TRENDLINES If you also wanted to analyse the decrease in the profit at the end of the day (i.e the last 5 data points) and add a second trendline to the graph, the same process is used:
13.	Using the Select Data icon, create a new data set for the last 5 data points, and adjust Series 2 to just include the remaining data points (in this case A7-A8 and B7-B8). This should give a graph that looks like the figure opposite.
14.	Select one of the last 5 data points, and create a trendline for them.

Assuming you want to change the markers on the graph so that the data appears as one, not three, series:
15.	Right click on one of the markers (that you wish to change) and select Format Data Series.
16.	Then adjust their appearance under Marker Options, Fill and Border, so that the desired appearance is achieved, i.e. so they look the same as the series you want all the data to appear like. To change colour of marker click on paint can on the right and change colour.
17.	Repeat for all the data series until all the markers are changed as desired. The final plot should then look similar to the one opposite.