04. Introduction to Excel(1)

Introduction to Excel

  • Week 4 TEC 101

References

  • Textbook Reference: Duffy, J. & Cram, C. (2024). The Illustrated Collection Microsoft 365 & Office, First Edition. Cengage.

  • Graphics/Charts: Used from the above references; all other images comply with Fair Use policies.

  • Copyright Inquiries: Contact Pierce Mortuary Colleges, Inc. for removal of protected materials if Fair Use is disputed.

Microsoft Excel Overview

What is Excel?

  • An electronic spreadsheet program for calculations, data analysis, and presentation of numeric data.

  • A workbook is a collection of related worksheets, saved as an xlsx file.

  • A worksheet features a grid of columns and rows for data entry and manipulation.

Excel User Interface

Key Elements

  • File Name: Displayed at the top; allows for easy identification.

  • Search Box: Quick access feature (shortcut key Alt+Q).

  • Ribbon: The main tool panel containing tabs (Home, Insert, etc.).

  • AutoSave: Can be toggled on or off.

  • Formula Bar: Used to enter or edit data within active cells.

Data Entry

How to Enter Data

  • Directly into the cell or through the formula bar.

  • Labels: Descriptive text used for identification (not involved in calculations).

  • Values: Numeric entries used in calculations.

Casket Sales Example (Data Entry)

  • Categories and data for various types of caskets across periods (e.g., 18ga steel, 20ga steel).

Editing Data

Editing Instructions

  • To edit, double-click the active cell or start typing.

  • Excel switches to Edit mode during cell entries.

Common Excel Pointers

  • Cell Pointer: Indicates the active cell.

  • Fill Handle (+): Used to copy cell content.

  • Edit Mode Pointer: I-beam cursor when hovering over the formula bar.

Copying and Moving Cell Data

  • Options include using buttons (Cut, Copy, Paste), the fill handle, or drag-and-drop.

  • Clipboard: Temporarily stores copied or cut data.

Using Formulas

Formula Basics

  • A formula starts with an equal sign (=) and can include arithmetic operations.

  • Examples of arithmetic operators: +, -, *, /, ^.

Excel Arithmetic Operators Table

  • Operators:

    • +: Addition (e.g. =A5+A7)

    • -: Subtraction (e.g. =A5-10)

    • *: Multiplication (e.g. =A5*A7)

    • /: Division (e.g. =A5/A7)

    • ^: Exponential (e.g. =6^2)

Entering Formulas

  • Formulas are essential for calculation and begin with =. Use proper syntax for arithmetic calculations with respect to operator precedence.

Order of Operations

  1. Parentheses (): Calculated first.

  2. Exponents ^: Second.

  3. Multiplication/Division */: From left to right.

  4. Addition/Subtraction +-: From left to right.

Functions

What is a Function?

  • A predefined procedure returning a value based on given arguments.

  • AutoSum: Automatically calculates the sum of selected cells.

Function Examples

  • Syntax for SUM: =SUM(range) to sum a range of cells.

Modifying Worksheet Views

View Options

  • Change view using the ribbon (View tab) or status bar buttons.

  • Normal View: Standard view.

  • Page Layout View: For printing preview.

  • Page Break Preview: Shows page breaks in a layout format.

Printing Options

Page Layout Adjustments

  • Adjust orientation (Portrait or Landscape), paper size, and margins.

Preview and Printing

  • Use Backstage view to manage print options, including printing sheets and selecting printers.

Cell Formatting

Formatting Values

  • Formatting alters the appearance (e.g., bold, monetary symbols) but does not change data.

  • Access formatting options through the Ribbon or contextual menus.

Font Attributes

  • Default font in Excel: 11-point Calibri.

Centering and Merging Text

  • Merge and Center helps align text across multiple cells.

Resizing and Adjusting Cells

AutoFit Features

  • Columns/rows can be adjusted using the ribbon, right-click shortcut, or mouse dragging.

  • AutoFit: Adjusts based on content size.

Inserting and Deleting Rows/Columns

  • New rows shift existing data down; new columns shift data to the right.

  • Delete any selected rows/columns, and data will shift accordingly.

Conditional Formatting

Overview

  • Automatically formats cells based on specific criteria (e.g., highlight values above threshold).

Functions for Logical Operations

IF Function

  • To build a logical test with conditions, use syntax: =IF(condition, true_action, false_action).

AND & OR Functions

  • AND returns true if all conditions are met; OR returns true if any condition is met.

Using the ROUND Function

  • Round values using syntax: =ROUND(value, num_digits) to control decimal display.

Creating and Formatting Charts

Chart Basics

  • Key components include axes, legends, plot area, and data markers.

  • Utilize the Insert tab to create and modify charts.

Common Chart Types

  • Column, Pie, Bar, Line - each serves specific data representation needs.

Chart Modifications

  • Use the Chart Design tab to change styles, data ranges, and element arrangements.

Conclusion

  • Excel serves as a powerful tool for data analysis, formatting, and presentation through various features and functionalities.