Overview
This lecture provides a step-by-step guide for creating and formatting scientific charts in Excel, focusing on chemistry lab data, including data selection, axis labeling, error bars, trendlines, significant figures, and troubleshooting.
Data Selection and Chart Insertion
- Select X and Y data using Ctrl for non-adjacent columns before inserting a scatter plot.
- Insert a scatter plot by navigating to Insert > Chart > Scatter.
- Use "Select Data" to correct or adjust the data ranges for the X and Y axes.
Chart Formatting: Titles and Axes
- Add chart and axis titles via the Chart Design tab or by clicking the "+" icon on the chart.
- Chart titles should provide context about the experiment but not simply restate the variable names.
- Use clear, descriptive axis labels with appropriate units and chemical notation.
Gridlines and Chart Scaling
- Add major and minor gridlines to create a millimeter-like grid for better precision reading.
- Adjust the chart's zoom (axis minimum and maximum) to ensure data points fill about 80% of the plot area.
Adding Error Bars
- Add standard error bars for both X and Y axes; use "fixed value" for constant errors or "custom" for variable ones.
- For custom X error bars, select and assign each value individually.
Refining Data Points and Error Bars
- Reduce marker size to make error bars more visible.
- If unable to select elements directly, use the Format panel dropdown to choose specific chart components.
Precision and Significant Figures
- Set the number format for axis labels and equation output to match data precision (decimal places or significant digits).
- The trendline equation and R² should match the required number of significant figures, as per lab protocol.
Adding and Editing Trendlines
- Add a linear trendline and display its equation and R² on the chart.
- Adjust trendline appearance (solid or dashed) according to instructor preference.
- Edit equation and labels for proper significant figures and chemical notation.
Chart Layout and Presentation
- Move the chart to a new sheet for printing or submitting electronically, usually in landscape format.
- Adjust gridline spacing for a squarer, clearer grid if necessary.
- Increase equation font size and ensure equation and points are clearly visible.
Managing Outliers and Multiple Data Series
- To exclude an outlier from the trendline, remove it from the data series but add it as a separate series for visibility.
- Add error bars to the outlier point and change its color for distinction.
- Add a legend when multiple series are present, and name each series appropriately.
Forecasting and Intersection Points
- Extend trendlines using the forecast option to find or visualize intersections between data series.
Troubleshooting Common Issues
- Check for commas vs. periods in decimal notation to avoid Excel misreading numbers.
- Ensure data cells are formatted as numbers, not text.
- If equations or chart updates fail, delete and re-add problematic elements, or restart Excel if necessary.
Key Terms & Definitions
- Scatter Plot — a graph showing points to represent the values of two variables.
- Error Bars — graphical representation of data variability or uncertainty.
- Trendline — a line indicating the general course or tendency of data points.
- Significant Figures — the digits in a value that contribute to its measurement accuracy.
- Legend — a chart element that explains the meaning of symbols or colors.
Action Items / Next Steps
- Practice building a sample scatter plot with error bars and a trendline in Excel using lab data.
- Review your lab's instructions for specific requirements on significant figures and equation reporting.
- Ensure all labels, units, and titles are accurate and meet your instructor’s guidelines.