\[ \begin{align}\begin{aligned}\newcommand\blank{~\underline{\hspace{1.2cm}}~}\\% Bold symbols (vectors) \newcommand\bs[1]{\mathbf{#1}}\\% Differential \newcommand\dd[2][]{\mathrm{d}^{#1}{#2}} % use as \dd, \dd{x}, or \dd[2]{x}\\% Poor man's siunitx \newcommand\unit[1]{\mathrm{#1}} \newcommand\num[1]{#1} \newcommand\qty[2]{#1~\unit{#2}}\\\newcommand\per{/} \newcommand\squared{{}^2} \newcommand\cubed{{}^3} % % Scale \newcommand\milli{\unit{m}} \newcommand\centi{\unit{c}} \newcommand\kilo{\unit{k}} \newcommand\mega{\unit{M}} % % Percent \newcommand\percent{\unit{{\kern-4mu}\%}} % % Angle \newcommand\radian{\unit{rad}} \newcommand\degree{\unit{{\kern-4mu}^\circ}} % % Time \newcommand\second{\unit{s}} \newcommand\s{\second} \newcommand\minute{\unit{min}} \newcommand\hour{\unit{h}} % % Distance \newcommand\meter{\unit{m}} \newcommand\m{\meter} \newcommand\inch{\unit{in}} \newcommand\foot{\unit{ft}} % % Force \newcommand\newton{\unit{N}} \newcommand\kip{\unit{kip}} % kilopound in "freedom" units - edit made by Sri % % Mass \newcommand\gram{\unit{g}} \newcommand\g{\gram} \newcommand\kilogram{\unit{kg}} \newcommand\kg{\kilogram} \newcommand\grain{\unit{grain}} \newcommand\ounce{\unit{oz}} % % Temperature \newcommand\kelvin{\unit{K}} \newcommand\K{\kelvin} \newcommand\celsius{\unit{{}^\circ C}} \newcommand\C{\celsius} \newcommand\fahrenheit{\unit{{}^\circ F}} \newcommand\F{\fahrenheit} % % Area \newcommand\sqft{\unit{sq\,\foot}} % square foot % % Volume \newcommand\liter{\unit{L}} \newcommand\gallon{\unit{gal}} % % Frequency \newcommand\hertz{\unit{Hz}} \newcommand\rpm{\unit{rpm}} % % Voltage \newcommand\volt{\unit{V}} \newcommand\V{\volt} \newcommand\millivolt{\milli\volt} \newcommand\mV{\milli\volt} \newcommand\kilovolt{\kilo\volt} \newcommand\kV{\kilo\volt} % % Current \newcommand\ampere{\unit{A}} \newcommand\A{\ampere} \newcommand\milliampereA{\milli\ampere} \newcommand\mA{\milli\ampere} \newcommand\kiloampereA{\kilo\ampere} \newcommand\kA{\kilo\ampere} % % Resistance \newcommand\ohm{\Omega} \newcommand\milliohm{\milli\ohm} \newcommand\kiloohm{\kilo\ohm} % correct SI spelling \newcommand\kilohm{\kilo\ohm} % "American" spelling used in siunitx \newcommand\megaohm{\mega\ohm} % correct SI spelling \newcommand\megohm{\mega\ohm} % "American" spelling used in siunitx % % Capacitance \newcommand\farad{\unit{F}} \newcommand\F{\farad} \newcommand\microfarad{\micro\farad} \newcommand\muF{\micro\farad} % % Inductance \newcommand\henry{\unit{H}} \newcommand\H{\henry} \newcommand\millihenry{\milli\henry} \newcommand\mH{\milli\henry} % % Power \newcommand\watt{\unit{W}} \newcommand\W{\watt} \newcommand\milliwatt{\milli\watt} \newcommand\mW{\milli\watt} \newcommand\kilowatt{\kilo\watt} \newcommand\kW{\kilo\watt} % % Energy \newcommand\joule{\unit{J}} \newcommand\J{\joule} % % Composite units % % Torque \newcommand\ozin{\unit{\ounce}\,\unit{in}} \newcommand\newtonmeter{\unit{\newton\,\meter}} % % Pressure \newcommand\psf{\unit{psf}} % pounds per square foot \newcommand\pcf{\unit{pcf}} % pounds per cubic foot \newcommand\pascal{\unit{Pa}} \newcommand\Pa{\pascal} \newcommand\ksi{\unit{ksi}} % kilopound per square inch % NOTE: do NOT define \bar -- it is the built-in LaTeX accent used by \bar{x}, % \bar{y} (sample means). Redefining it made those render as the word "bar". \newcommand\barunit{\unit{bar}} \end{aligned}\end{align} \]

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

Excel Workspace Shortcuts

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

+

=A1+B1

Subtraction

-

=A1-B1

Multiplication

*

=A1*B1

Division

/

=A1/B1

Exponent (Power)

^

=A1^B1

Order of Operations#

Excel follows standard order of operations:

  1. Parentheses

  2. Exponents

  3. Multiplication and Division (Left to Right)

  4. 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 copied

  • A$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.

Practice

Download and open the following file to follow along with the video:

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.512 and 2.5 have about the same magnitude, but 2.512 is more precise.

  • 2.512 implies accuracy to the nearest thousandth.

  • 2.5 implies 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 \)
2 decimal places

Multiplication / Division

Round the result to the same number of significant figures as the least precise value (fewest significant figures).

\(12120 * 1.3 = 16000\)
2 significant figures

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

=ROUND(3.14159,2)

3.14

=ROUND(3.14159,3)

3.142

=ROUND(A2/B2,2)

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
- Brand name
- Fuel type

Ordinal

Categories that have a logical order but the spacing between categories is not measurable.

- First-year > Senior
- Poor > Excellent
- Small > Medium > Large

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
- Number of defects
- Number of points in a game

Continuous

Measured. Can take on any value within a range.

- Time
- Temperature
- Height

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 condition performs better?
- How does a value change over time?

- Which material has the longest battery life?
- How does efficiency differ between two designs?
- How does performance change over time?

Bar Chart (for comparing categories)
Line Chart / Time Series Plot (for change over time)

Composition

- What makes up a whole?
- What percentage does each category represent?
- How is a total divided among groups?

- What percentage of students selected each major?
- How is the budget allocated across categories?

Pie Chart (for parts of a whole; limit to 5 to 7 sections)

Relationship

- How does one variable change as another changes?
- Is there a trend between two measured quantities?

- How does temperature affect resistance?
- How does time affect voltage?

Scatter Plot (markers for measured data; smooth line for modeled data)

Distribution

- Which values are most or least common?
- How are values spread across a range?
- Where are data points concentrated?

- What score range occurs most often?
- How are voltage readings distributed?

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#

  1. Select your data

  2. Go to the Insert tab.

  3. Review the chart options available.

  4. 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:

  1. Selecting the chart

  2. Opening the Chart Design tab

  3. Choosing Add Chart Element

  4. 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:

  1. Right-click the axis

  2. Select Format Axis

  3. 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.