📊

Excel Data Analysis Techniques

Sep 11, 2025

Overview

This lecture focuses on Excel data analysis techniques, emphasizing the difference between absolute and relative cell references, basic formatting, and introductory functions like SUM, AutoSum, and SUMPRODUCT.

Data Analysis in Excel

  • Data analysis in Excel involves using various formulas and functions to interpret and manipulate data.
  • Practice files are provided to follow along with the examples demonstrated.

Relative and Absolute Cell References

  • A relative cell reference in Excel adjusts automatically when a formula is copied to another cell.
  • An absolute cell reference ($ sign, e.g., $D$8) does not change when a formula is copied, locking the reference to a specific cell.
  • Pressing F4 while editing a formula toggles between relative and absolute references.

Example: Calculating Total Sales

  • Multiply product units by sales price to find total sales for each product (e.g., =B4*C4).
  • Use the fill handle to copy formulas down a column, employing relative references by default.
  • Format numbers using the comma style for readability and use the Accounting format for currency.

Summing Data

  • Use =SUM(range) to add multiple values together.
  • The AutoSum button on the Home tab quickly inserts a SUM function for selected cells.

Calculating Percentage of Total

  • To find a product's sales as a percentage of total sales: divide product sales by total sales (e.g., =D4/$D$8).
  • Convert decimals to percentages with the percentage style button.
  • Make the total absolute (e.g., $D$8) so it does not shift when copied down.

Using SUMPRODUCT Function

  • The SUMPRODUCT function calculates the sum of products for two arrays (e.g., =SUMPRODUCT(B4:B7, C4:C7)).
  • Useful for weighted average or similar calculations requiring sum of multiple products.

Applying Concepts: Commission Calculation

  • Commission is calculated as total sales multiplied by a fixed commission rate (e.g., =B14*$B$13).
  • Use an absolute reference for the commission rate to apply the formula to all salespersons.

Key Terms & Definitions

  • Relative Cell Reference — A cell address in a formula that adjusts when copied to a new location.
  • Absolute Cell Reference — A cell address with $ signs that remains fixed when copied.
  • Fill Handle — A tool to drag formulas or data across adjacent cells.
  • AutoSum — An Excel feature that quickly sums selected numbers.
  • SUMPRODUCT — A function that returns the sum of the products of corresponding ranges or arrays.
  • Array — A collection (range) of values used in formulas.

Action Items / Next Steps

  • Open the week three practice file and replicate all examples shown.
  • Practice using F4 to switch between absolute and relative references in your own formulas.
  • Experiment with SUMPRODUCT for various data sets.

Full transcript