Computer Application Lecture 1: Microsoft Excel

Introduction to Microsoft Excel

• Microsoft Excel is a spreadsheet application developed by Microsoft. It is designed to perform a variety of tasks including:

  • Calculations.

  • Statistical analysis (e.g., finding sums, means, and standard deviations).

  • Graphing tools for data sets.

• Data files created with Excel are referred to as workbooks, similar to how Microsoft Word files are called documents.

• Data processed in Excel can be:

  • Numerical: Consisting of numbers.

  • Alphanumeric: Involving letters or combinations of letters and numbers.

Primary Purposes of Microsoft Excel

• Store and organize data: Keeping information in a structured format.

• Analyze data: Using formulas and functions to interpret information.

• Represent data graphically: Visualizing datasets through various charts, including:

  • Bar graphs.

  • Histograms.

  • Scatter plots.

Components of the Excel Window Interface

• Office Button: A button that provides access to global workbook-level features and program settings.

• Quick Access Toolbar: A collection of buttons for one-click access to common commands like Save, Undo, and Repeat.

• Title Bar: Displays the name of the active workbook and the software name (Microsoft Excel).

• Ribbon: The main set of commands organized by task into various tabs (Home, Insert, Page Layout, Formulas, Data, Review, View) and functional groups.

• Column Headings: Letters appearing at the top of the worksheet window used to identify different columns.

• Row Headings: Numbers appearing along the left side of the worksheet window used to identify individual rows.

• Workbook Window: The specific window that displays the content of the Excel workbook.

• Vertical Scroll Bar: Used to navigate vertically through the workbook window.

• Horizontal Scroll Bar: Used to navigate horizontally through the workbook window.

• Zoom Controls: Tools for magnifying or shrinking the visual scale of the active workbook window, typically showing a percentage such as $100\%$.

• View Shortcuts: Buttons to change the display mode of the worksheet content, including Normal, Page Layout, and Page Break Preview.

• Sheet Tabs: Tabs located at the bottom that display the names of individual worksheets within the workbook (e.g., Sheet1, Sheet2, Sheet3).

• Sheet Tab Scrolling Buttons: Buttons used to scroll through the list of sheet tabs in the workbook.

• Select All Button: A button positioned at the intersection of column and row headings used to select all cells in the active worksheet.

• Active Cell: The specific cell currently selected in the active worksheet.

• Name Box: A box that displays the cell reference (e.g., A1) of the currently active cell.

• Formula Bar: A dedicated bar that displays the value or the formula entered into the active cell.

Worksheet Structure: Columns, Rows, and Cells

• Rows: Horizontal divisions represented by numbers along the side of the sheet.

• Columns: Vertical divisions represented by letters across the top of the sheet.

• Cells: A cell is the intersection point between a column and a row.

  • Each cell is named according to its coordinate: the column letter followed by the row number (e.g., cell B12 is the intersection of column B and row 12).

Data Entry Methods

There are two primary ways to enter information into a cell:

1. Direct Entry: Click on a cell, type the data (text or numbers) directly, and press Enter.

2. Formula Bar Entry: Click on a cell, then click inside the Formula Bar (next to the $f_x$ icon), type the data, and press Enter.

Formatting Cells and Tables

• Cell Border Formatting:

  • Click the down arrow next to the Border icon on the Ribbon.

  • Select the required border style from the drop-down menu.

• Formatting as Table: Provides predefined styles and structural organization for a selected range of cells.

Calculation Concepts: Formulas and Functions

• Formulas: Equations that perform calculations. They always begin with an equals sign ($=$). This symbol signals to Excel to perform a calculation.

• Functions: Pre-defined formulas already built into Excel. They take selected data (inputs), process it, and return a value.

Formatting Rules for Functions

• Every function follows a standard format: $=\text{FUNCTIONNAME}(\text{input})$.

• The input (arguments) can be:

  • A specific set of numbers: e.g., $=\text{AVERAGE}(2, 3, 4, 5)$ calculates the mean of those four specific digits.

  • A reference to cells: e.g., $=\text{AVERAGE}(B1:B18)$ calculates the average for all cells from B1 through B18.

  • A list of individual cell references: e.g., $=\text{AVERAGE}(B1, B2, B3)$.

• Cell references can be entered manually or by clicking and dragging the mouse over a range.

Functions for Descriptive Statistics

Users can enter these functions manually in the formula bar or use the Function Wizard ($f_x$) in the toolbar.

• Mean: $=\text{AVERAGE}(\text{first cell}:\text{last cell})$

• Median: $=\text{MEDIAN}(\text{first cell}:\text{last cell})$

• Mode: $=\text{MODE}(\text{first cell}:\text{last cell})$

• Variance: $=\text{VARP}(\text{first cell}:\text{last cell})$

• Standard Deviation: $=\text{STDEVP}(\text{first cell}:\text{last cell})$

Example Data Set Analysis

Based on a comparison of two instructors, the following data was entered and processed:

• Instructor 1 Data: $3.1, 2.7, 3.2, 2.9, 2.8, 3.0, 3.3, 2.8, 3.0, 3.2$

  • Mean: $3.0$

  • Median: $3.0$

  • Mode: $3.2$

  • Variance: $0.04$

  • Standard Deviation: $0.19$

• Instructor 2 Data: $2.5, 3.9, 4.0, 2.4, 2.7, 2.8, 4.0, 2.6, 3.1, 3.0$

  • Mean: $3.1$

  • Median: $2.9$

  • Mode: $4.0$

  • Variance: $0.36$

  • Standard Deviation: $0.60$

Modifying Columns and Rows

Resizing Columns and Rows

• Manual Dragging:

  • Drag the right border of a column heading left or right to change width.

  • Drag the bottom border of a row heading up or down to change height.

• AutoFit: Double-click the right border of a column heading or the bottom border of a row heading to automatically adjust size to accommodate the longest cell entry.

• Ribbon Method:

  • Select columns/rows.

  • Go to Home tab -> Cells group -> Format button.

  • Choose Column Width, Row Height, or the AutoFit options.

Inserting Columns and Rows

• Procedure:

  • Select the column(s) or row(s) at the position where new ones are needed.

  • Excel inserts the same number of units as are currently selected.

  • Method A: Home tab -> Cells group -> Click the Insert button.

  • Method B: Right-click a column or row heading and select Insert from the shortcut menu.

• Visual Shift:

  • When a new column is inserted, existing columns shift to the right.

  • When a new row is inserted, existing rows shift down.