Excel: Pivot Tables and Scatterplots

Summary

In this tutorial, the process of summarizing data in Excel using Pivot Tables is demonstrated, followed by creating scatter plots to visualize relationships between temperature and behavior across juvenile and adult categories. The tutorial covers how to set up and customize Pivot Tables, average behavior data, and separate results by category. It also guides users through graph creation, adding trendlines, and formatting charts for clarity and presentation. Key points include organizing data, graphing multiple behaviors, adjusting axes and labels, and final touches for visual clarity in research papers or presentations.

Takeaways

• 😀 Highlight the data you want to graph before inserting a pivot table in Excel.
• 😀 After creating the pivot table, use the *Rows* section for temperatures and the *Values* section to average behavior counts.
• 😀 Change data aggregation from summation to average by adjusting the pivot table's field settings.
• 😀 Filter the data by categories (e.g., Juveniles and Adults) by dragging the category field into the *Filters* pane.
• 😀 To graph the data, copy the data from the pivot table and paste it into a new location outside the pivot table.
• 😀 When graphing, select the data for one category (e.g., Juvenile) and create a scatter plot using the *Insert Ribbon*.
• 😀 Add additional series (e.g., Behavior 2 or Adult data) to the scatter plot by selecting *Select Data* and choosing the corresponding values.
• 😀 Add trendlines to the scatter plot for each data series to observe temperature-related trends in behaviors.
• 😀 Label the axes of the scatter plot (e.g., Temperature (°C) for the x-axis, Behavior Rate (per 10 minutes) for the y-axis).
• 😀 Format the chart for presentations by adjusting font sizes, adding chart titles, and making the graph readable for an audience.
• 😀 Customize the x-axis scale to focus on the relevant temperature range, avoiding unnecessary data points (e.g., starting at 20°C).

Q & A

What is the first step in creating a pivot table in Excel?

The first step is to highlight the data you want to graph, then go to the Insert ribbon and select Pivot Table. Excel will verify the selected data range and prompt you to confirm where to place the pivot table.

How can you change the aggregation of data in a pivot table from sum to average?

After selecting the data in the pivot table, click the 'i' on the right, which opens the pivot field window. From there, you can select 'Average' instead of 'Sum' to display averages instead of totals.

How do you separate the data for juveniles and adults in the pivot table?

To separate the data, you need to drag the 'category' field (juveniles and adults) into the 'filters' pane of the pivot table. You can then select either 'Adult' or 'Juvenile' from the filter options to view data for each group.

Why can’t you directly insert a scatter plot from data inside a pivot table?

You cannot directly insert a scatter plot from data inside a pivot table because Excel requires data outside the pivot table for graphing. Therefore, you need to copy the data from the pivot table and paste it outside before creating a chart.

What should you do to graph data for juveniles and adults in a single scatter plot?

To graph both juveniles and adults, you first need to create a scatter plot for one group (e.g., juveniles). Then, right-click on the chart, select 'Select Data,' and add the second group (e.g., adult) data to the chart.

How can you add a trend line to the scatter plot in Excel?

To add a trend line, select the chart, then go to the 'Chart Design' ribbon and select 'Add Chart Element.' Choose 'Trendline' and select from various types of trend lines, such as 'Linear.' You can add a trend line to each individual data series.

What should you do if the trend line doesn't appear for a particular data series?

If the trend line doesn’t appear, double-check that the data series is correctly set up. Ensure that the correct data range is selected and that the series is properly added to the chart. If issues persist, you may need to manually troubleshoot the data series.

How do you label the x-axis and y-axis on a scatter plot in Excel?

To label the axes, go to the 'Chart Design' ribbon, select 'Add Chart Element,' then scroll down to 'Axis Titles.' You can label the x-axis (e.g., 'Temperature (°C)') and the y-axis (e.g., 'Behavior Rate (per 10 minutes)').

What is the significance of changing the minimum value for the x-axis in a scatter plot?

Changing the minimum value of the x-axis helps focus on a specific temperature range and makes trends in the data more apparent. For example, setting the minimum value of the x-axis to 20°C can make the data more visually clear if the study is based on temperatures above that value.

What should you do if you want to use a chart in a research paper?

For a research paper, you should remove the chart title and legend, and instead add a caption below the chart to describe the data. This approach is more formal and aligns with academic presentation standards.