Nov 16, 2026 | 3124 words | 31 min read
3.1.1. PCMs: Spreadsheet Foundations#
These pre-class materials introduce the foundational Excel skills you will use throughout ENGR 131. These skills allow you to organize data, perform basic engineering calculations, and communicate results clearly using charts.
If you are already comfortable with Excel, you may move quickly through the videos. If not, plan to pause, practice, and ask questions in class.
Getting Started with Excel#
Excel is a spreadsheet tool used to:
Organize structured data
Perform calculations using formulas
Analyze trends
Create charts to communicate results
Introduction to Excel#
In this video, you will review how to:
Open and create a new workbook
Save your workbook appropriately
Navigate worksheets and basic spreadsheet layout.
Additional Resource
If you would like a broader overview of Excel features, review the Microsoft Support article: What is Excel?
Organizing Data for Engineering Work#
In ENGR 131, spreadsheets must:
Use clear, descriptive headers
Include units
Keep one variable per column
Avoid merged cells in data tables
Be readable by another engineer
Cleaning and Formatting Your Data#
Practice
Download and open the following files to follow along with the video:
Writing Formulas in Excel#
In Excel, an equation that performs mathematical calculations on worksheet values is called a formula.
All formulas in Excel begin with an equals sign =. For example, =A2*B2 would
multiply the values in cells A2 and B2.
Operators in Excel#
Excel uses mathematical operators inside formulas.
Operation |
Operator |
Example |
|---|---|---|
Addition |
|
|
Subtraction |
|
|
Multiplication |
|
|
Division |
|
|
Exponent (Power) |
|
|
Order of Operations#
Excel follows standard order of operations:
Parentheses
Exponents
Multiplication and Division (Left to Right)
Addition and Subtraction (Left to Right)
Check for Understanding
Which Excel formula correctly multiplies the values in cells A2 and B2?
A. A2*B2
B. =A2*B2
C. =A2+B2
D. A2=B2
Show Answer
Correct Answer: B
All Excel formulas must begin with an equals sign (=).
Calculations in Excel#
Practice
Download and open the following file to follow along with the video:
Cell References#
When writing formulas, Excel refers to other cells using cell addresses. Examples:
A1, B4, or D12
Relative Reference#
A relative reference changes when you copy a formula to another cell.
Example:
If =A2+B2 is copied down one row, it becomes =A3+B3.
Absolute References#
An absolute reference stays fixed when copied. To create an absolute reference, use
the $ symbol in front of the column letter and row number.
Example:
If =$A$2+B2 is copied down one row, it becomes =$A$2+B3.
Absolute references are used when you need to lock a constant value, such as a tax rate or conversion factor.
Mixed References#
A mixed reference locks either the row or the column of a cell reference, but not
both. Mixed references use the $ symbol in front of either the column letter or the
row number.
Example:
$A1- column A is locked, but the row will change when copiedA$1- row 1 is locked, but the column will change when copied
Mixed references are useful when copying a formula across rows and columns, where one dimension (row or column) must remain constant while the other adjusts automatically.
Shortcut
Press F4 (Windows) or Fn + F4 (Mac) while editing a cell reference to toggle between relative, mixed, and absolute references.
Check for Understanding
A formula contains the reference =$A$2. What happens if the formula is copied to
another cell?
A. The reference changes automatically
B. Only the column changes
C. Only the row changes
D. The reference stays fixed
Show Answer
Correct Answer: D
An absolute reference uses $ to lock both the column and row so the reference does
not change when the formula is copied.
Additional Resource
To review additional documentation on cell references, review Switch between Relative, Absolute, and Mixed References
Built-in Functions in Excel#
A function is a pre-determined formula built into Excel that performs a common calculation.
Functions help you perform calculations efficiently and accurately without rewriting complex formulas.
All functions:
Begin with
=Include parentheses
()Take inputs called arguments
For example, =SUM(A1:A5) calculates the total of the values in cells A1 through A5.
Common Functions to Know#
=SUM(): calculates the total value of a range=COUNT(): returns the number of cells in a range that contain numeric data only=MIN(): finds the smallest value in a range=MAX(): finds the largest value in a range=AVERAGE(): calculates the mean of a range of values=MEDIAN(): finds the middle value in a group of numbers=MODE.SNGL(): returns a single most frequent value=MODE.MULT(): returns multiple values if there is more than one mode
In ENGR 131, you are expected to select appropriate built-in functions rather than manually calculating statistics.
Additional Built-In Functions#
Excel includes many additional built-in functions beyond basic statistics. These allow you to perform more advanced engineering calculations efficiently.
In the accompanying video, you will see examples of:
Unit conversions using
=CONVERT()Trigonometric functions such as
=SIN(),=COS(), and=TAN()Converting between degrees and radians using
=RADIANS()and=DEGREES()
These tools are especially useful when modeling physical systems, working with angles, or converting between measurement units.
Check for Understanding
You want to determine how many measurements were recorded in a dataset, but only count cells that contain numeric values.
Which Excel function should you use?
A. =SUM()
B. =COUNT()
C. =AVERAGE()
D. =MAX()
Show Answer
Correct Answer: B – =COUNT()
=COUNT() returns the number of cells in a range that contain numeric values only.
=SUM() adds numbers together.
=AVERAGE() calculates the mean.
=MAX() returns the largest value in a range.
Additional Resource
To review additional keyboard shortcuts, read Keyboard Shortcuts in Excel
Significant Figures and Rounding in Excel#
When working with measured data, it is important to communicate both the value and the precision of a measurement. The number of significant figures (sig figs) in a value indicates how precise that measurement is.
All measurements contain uncertainty. Significant figures help indicate where that uncertainty occurs in a number. More precise measurements contain more significant digits, but no measurement is ever perfectly exact.
For example:
2.512and2.5have about the same magnitude, but2.512is more precise.2.512implies accuracy to the nearest thousandth.2.5implies accuracy to the nearest tenth.
Identifying Significant Figures#
The significant digits in a number begin with the first non-zero digit and end with the last recorded digit.
Number |
Significant Figures |
|---|---|
1214 |
4 |
1.214 |
4 |
0.00001214 |
4 |
Leading zeros are not significant because they only indicate the position of the decimal.
Significant Figures in Calculations#
When calculations involve measured values, the precision of the result should reflect the precision of the measurements used. Because measurements contain uncertainty, results from calculations cannot be more precise than the least precise measurement. However, spreadsheet tools like Excel do not automatically account for measurement uncertainty. Excel may display many decimal places even when those digits are not meaningful.
Example:
Measurement |
Value |
Significant Figures |
|---|---|---|
Cost ($) |
0.57 |
2 |
Mass (lbs) |
2.8 |
2 |
Cost / Mass ($/lb) |
0.203571429 |
Excel displays many digits |
Although Excel displays nine digits, the result should realistically only be reported with two significant figures, matching the least precise measurement.
Rules for Calculations#
Operation |
Rule |
Example |
|---|---|---|
Addition / Subtraction |
Round the result to the same number of decimal places as the value with the fewest decimal places. |
\(2210.2541 + 21.45 = 2231.70 \) |
Multiplication / Division |
Round the result to the same number of significant figures as the least precise value (fewest significant figures). |
\(12120 * 1.3 = 16000\) |
Statistics |
Round the result to one additional decimal place than your least precise number. |
Mean of 36.3, 24.5, 48.9, 65.2, 21.7 is 39.32 |
These rules help ensure that calculated values do not imply more precision than the measurements support.
Rounding Values in Excel#
Because Excel often displays more digits than are meaningful, engineers must round results appropriately before reporting them.
The ROUND() function can be used to control the number of decimal places displayed.
General structure:
=ROUND(number, num_digits)
-number- the value or formula you want to round
-num_digits- the number of decimal places to keep
Example Formula |
Result |
|---|---|
|
3.14 |
|
3.142 |
|
Rounds a calculation to two decimal places |
Using ROUND() helps ensure that reported results reflect the precision of the
original measurements rather than the default output from Excel.
Statistics Convention for This Course#
When reporting mean or standard deviation, it is acceptable in this course to round to one additional decimal place beyond the least precise measurement. This provides reasonable precision for introductory data analysis.
Check for Understanding
A student measures the mass of a sample as 2.8 lbs and the cost of the sample as
$0.57. They calculate the cost per pound in Excel using the formula =0.57/2.8, and
Excel displays 0.203571429.
a) Based on the measurements, how many significant figures should the final answer contain?
b) What value should be reported after rounding?
c) Write an Excel formula using ROUND() that would report the correct value.
Show Answer
a) 2 significant figures - Both measurements (0.57 and 2.8) contain 2 significant figures, so the result must also contain 2 significant figures.
b) 0.20 - The Excel result 0.203571429 rounded to 2 significant figures
becomes 0.20.
c) =ROUND(0.57/2.8,2) - The ROUND() function ensures the value is reported with
an appropriate number of decimal places instead of Excel displaying many unnecessary
digits.
Data Visualization#
Why Do We Visualize Data?#
Engineers do not just calculate values; they communicate results. Data visualization helps transform raw numbers into information that others can quickly interpret.
We visualize data to:
Identify patterns and trends
Compare groups
Detect outliers or unusual results
Support data-driven decisions
Communicate findings clearly and efficiently
A table of numbers may contain the same information as a graph, but a well-designed visualization allows patterns to be recognized immediately.
When selecting a visualization, the type of data determines the appropriate chart.
Types of Data#
Before choosing a graph, you must correctly identify your data type.
There are two primary categories of data:
Categorical data
Numerical data
Categorical Data#
Categorical data describes labels or groups. It is used to classify information rather than measure it. Categorical data is generally summarized using counts or percentages.
Examples:
Material type
Engineering major
Device model
Categorical data can be further divided into two types: nominal and ordinal.
Type |
Description |
Examples |
|---|---|---|
Nominal |
Categories with no inherent order. There is no ranking or progression |
- Eye color |
Ordinal |
Categories that have a logical order but the spacing between categories is not measurable. |
- First-year > Senior |
Numerical Data#
Numerical data represents measurable quantities. Arithmetic operations such as addition, subtraction, and averaging are meaningful.
Examples:
Time (seconds)
Temperature (\(^\circ\text{C}\))
Distance (meters)
Voltage (V)
Numerical data can be divided into two types: discrete and continuous.
Type |
Description |
Examples |
|---|---|---|
Discrete |
Countable. Usually consists of whole numbers. |
- Number of students |
Continuous |
Measured. Can take on any value within a range. |
- Time |
Check for Understanding
Which of the following is an example of continuous data?
A. Number of students in a class
B. Number of defects in a product
C. Temperature measured during an experiment
D. Number of attempts to complete a task
Show Answer
Correct Answer: C – Temperature
Continuous data is measured and can take on any value within a range.
Be Careful: Numbers Do Not Always Mean Numerical Data#
It is a common misconception that if data contains numbers, it must be numerical. If the number functions as a label or identifier, the data is categorical.
Examples:
Jersey numbers
Zip codes
Room numbers
Check for Understanding
Which dataset is categorical data?
A. Time required to complete a test (seconds)
B. Temperature measured during an experiment (\(^\circ\text{C}\))
C. Engineering major of each student in a class
D. Distance traveled by a vehicle (meters)
Show Answer
Correct Answer: C – Engineering major
Categorical data describes labels or groups rather than measurable quantities.
Time, temperature, and distance are numerical because mathematical operations such as averaging or subtraction are meaningful.
Check for Understanding
A survey asks students to rate their experience with a lab activity using the following scale:
1 = Very Unsatisfied 2 = Unsatisfied 3 = Neutral 4 = Satisfied 5 = Very Satisfied
How should this data be classified?
A. Nominal data
B. Ordinal data
C. Discrete numerical data
D. Continuous numerical data
Show Answer
Correct Answer: B – Ordinal data
The responses have a clear order, but the difference between values (for example between 2 and 3) is not a measurable quantity.
Therefore the data is categorical with an inherent order, which makes it ordinal.
Why Data Type Matters#
Choosing the wrong visualization often begins with misidentifying the data type.
Categorical data is often displayed using bar or column charts.
Numerical data is often displayed using histograms, line graphs, or scatter plots.
Asking the Right Question#
Your Data is Only as Good as the Questions You Ask#
Before creating a visualization, ask: What question am I trying to answer?
A graph does not exist just to “show data.” It exists to answer a specific question. If the question is unclear, the visualization will likely be ineffective or misleading. The type of question you are asking determines the type of chart you should use.
Types of Questions in Data Analysis#
Most engineering data questions fall into one of four categories.
Category |
These Questions Ask… |
Examples |
Recommended Chart Type |
|---|---|---|---|
Comparison |
- How do two or more groups differ? |
- Which material has the longest battery life? |
Bar Chart (for comparing categories) |
Composition |
- What makes up a whole? |
- What percentage of students selected each major? |
Pie Chart (for parts of a whole; limit to 5 to 7 sections) |
Relationship |
- How does one variable change as another changes? |
- How does temperature affect resistance? |
Scatter Plot (markers for measured data; smooth line for modeled data) |
Distribution |
- Which values are most or least common? |
- What score range occurs most often? |
Histogram (bins show frequency across intervals) |
Excel and Histograms
Excel creates a default histogram, but you will often need to:
Modify the number of bins
Estimate or calculate appropriate bin width
Adjust formatting for clarity
The default settings are rarely optimal.
Check for Understanding
You want to determine whether increasing temperature changes electrical resistance.
Which chart type should you use?
A. Scatter plot
B. Pie chart
C. Bar chart
D. Histogram
Show Answer
Correct Answer: A – Scatter plot
Scatter plots are used to examine relationships between two variables.
How to Insert a Chart in Excel#
Select your data
Go to the Insert tab.
Review the chart options available.
You may: a. Select a chart type directly, or b. Use the Recommended Charts option to preview how your data will appear in different formats.
Always verify that the chart matches your question type before finalizing.
Formatting Charts in Excel#
Creating a chart is only the first step. Engineers must also format charts so that results are clear, accurate, and easy to interpret.
Poorly formatted charts can make data difficult to understand or lead to incorrect conclusions. A well-designed chart allows readers to quickly identify trends and compare values.
Basic Chart Formatting Guidelines#
When formatting charts in Excel, follow these best practices:
Include a clear and descriptive chart title
Label both axes so the reader knows what variables are being displayed
Include units of measurement in axis labels when appropriate
Use a legend when multiple datasets appear on the same chart
Choose appropriate axis scales so data patterns are visible
Avoid unnecessary visual effects such as 3D charts
Charts should prioritize clarity and readability over decoration.
Chart Titles#
A chart title should clearly describe what the reader should learn from the chart.
Titles should be descriptive, not generic.
For example:
Poor Title |
Better Title |
|---|---|
Variable X vs Variable Y |
Battery Life of Three Materials at Different Temperatures |
A strong title provides enough context for the reader to understand the purpose of the chart without needing additional explanation.
Axis Labels#
Both axes must be clearly labeled so readers understand what variables are being displayed.
Axis labels should:
Identify the variable being measured
Include units of measurement
Be large enough to read easily
Stand out clearly from the data
Example axis labels:
Temperature (\(^\circ\text{C}\))
Voltage (V)
Time (seconds)
Titles and axis labels can be added in Excel by:
Selecting the chart
Opening the Chart Design tab
Choosing Add Chart Element
Selecting Chart Title or Axis Titles
Scaling the Axis#
Axis scaling affects how easily readers can interpret a chart. Whenever possible, scale axes to meaningful reference values.
For example, when displaying percentages:
If the largest value is 43%, it may still be helpful to scale the axis from 0% to 100%
This provides a clearer frame of reference for interpretation
To modify axis scaling in Excel:
Right-click the axis
Select Format Axis
Adjust the minimum and maximum bounds
Improving readability may also require increasing the font size of titles and labels, since Excel sometimes uses small default text sizes (around 9 pt).
Choosing Chart Orientation
In some cases, category labels may be difficult to read when placed along the horizontal axis.
This can occur when:
Category names are long
Many categories are included
In these situations, a horizontal bar chart may improve readability.
Ordering Data in Bar Charts#
When creating bar charts, categories should usually be arranged from largest to smallest values. This ordering helps readers quickly identify the most significant differences between groups. Unordered bars can make comparisons harder to interpret.
However, if the categories represent a natural sequence, the original order should be preserved.
Examples of sequential categories include:
Days of the week
Months of the year
Time intervals
Ordered rating scales
In these cases, maintaining the natural order helps the reader understand how values change across the sequence.
Modifying Markers and Lines#
When working with line charts or scatter plots, markers and lines can be adjusted to improve readability.
You can modify:
Marker shape
Marker size
Marker color
Line style or thickness
These changes help distinguish multiple datasets within the same chart.
Making Charts Accessible#
Charts should remain clear for readers with different visual abilities and in different viewing conditions.
To improve accessibility:
Use different colors to distinguish datasets
Also use different marker shapes or line styles
Avoid relying on color alone to convey meaning
Use solid fills instead of gradient effects
Charts should remain readable when:
Printed in black and white
Viewed on different screens or displays
Avoiding Chart Junk#
Chart junk refers to visual elements that do not communicate meaningful information.
Examples include:
Decorative backgrounds
Excessive colors
Unnecessary gridlines
3D effects
These elements can distract from the data and make charts harder to interpret. Effective charts prioritize clarity and simplicity.
Consistency Across Charts#
When multiple charts appear in the same report, presentation, or assignment, formatting should remain consistent.
This includes:
Font styles and sizes
Color schemes
Axis formatting
Marker styles
Consistent formatting makes charts easier to compare and creates a more professional presentation.
Check for Understanding
Which formatting choice improves the readability of a bar chart?
A. Using a 3D bar chart
B. Removing axis labels
C. Ordering bars from largest to smallest
D. Adding decorative background colors
Show Answer
Correct Answer: C
Ordering bars from largest to smallest helps readers quickly compare values and identify the largest and smallest categories.