Unit 7: MS Excel

I. Foundations of MS Excel

Microsoft Excel is a spreadsheet application used to organize, calculate, analyze, and present data in tabular form. It is part of Microsoft Office and Microsoft 365 and stores related worksheets inside files called workbooks.

• Workbook: An Excel file containing one or more worksheets; modern workbooks normally use the .xlsx extension.
• Worksheet: A grid of cells arranged into rows and columns; sheet tabs such as Sheet1 identify the worksheets in a workbook.
• Row: A horizontal group of cells identified by numbers, such as row 1 or row 25.
• Column: A vertical group of cells identified by letters, such as column A, B, or AA.
• Cell: The intersection of a row and column; B4 means column B, row 4.
• Active cell: The currently selected cell, shown with a highlighted border; typed data enters this cell.
• Data types: A cell may contain text, numbers, dates, times, Boolean values such as TRUE, or formulas.
• Formula convention: A calculation begins with an equals sign, for example:
  
  EXCEL

    =A1+B1

• Functions: Predefined formulas such as SUM, AVERAGE, and IF accept values or cell references as arguments.
• Automatic recalculation: When a referenced value changes, Excel normally recalculates dependent formulas automatically.

II. Excel Workspace and Workbook Management

A workbook must be created, opened, and saved through Excel’s workspace and file-management commands before its worksheets can be used effectively.

A. MS Excel environment

The MS Excel environment combines worksheet cells with command areas used to enter, edit, calculate, format, and manage data.

• Title bar: Displays the workbook name and the Excel application name; an unsaved file may appear as Book1.
• Quick Access Toolbar: Provides frequently used commands such as Save, Undo, and Redo.
• File tab: Opens Backstage view, which contains commands including New, Open, Save, Save As, Print, and Options.
• Ribbon: Organizes commands into tabs such as:
  • Home: Clipboard, font, alignment, number formatting, styles, cells, and editing.
  • Insert: Tables, charts, illustrations, links, and other objects.
  • Formulas: Function library, named ranges, and calculation tools.
  • Data: Sorting, filtering, importing, and data-validation tools.
  • Review: Spelling, comments, protection, and accessibility tools.
  • View: Workbook views, zoom, gridlines, and window controls.
• Name Box: Shows the address or name of the active cell; entering D20 and pressing Enter selects that cell.
• Formula Bar: Displays and edits the complete contents of the active cell, including formulas.
• Worksheet area: Contains the grid of cells where data and formulas are entered.
• Sheet tabs: Switch between worksheets and allow sheets to be inserted, renamed, moved, copied, or deleted.
• Status bar: Shows worksheet status, view controls, zoom level, and quick calculations for selected numerical cells.
• Scroll bars: Move horizontally or vertically through large worksheets without changing cell contents.

B. Creating workbooks

Creating a workbook produces a new spreadsheet file containing at least one worksheet for data entry.

• Blank workbook: Select File → New → Blank workbook, or use Ctrl+N to create a new file.
• Template-based workbook: Select a prepared template, such as a budget or calendar, when predefined headings, formulas, and formatting are useful.
• Initial organization: Rename sheets according to their contents, such as Sales, Expenses, or Summary, rather than retaining unclear names.
• Worksheet insertion: Click the New Sheet (+) button to add another worksheet to the same workbook.
• Data entry: Select a cell, type a value, and press Enter; pressing Tab normally moves to the next cell on the right.
• Editing: Double-click a cell, press F2, or edit its contents in the Formula Bar.
• Good design: Place one category in each column and one record in each row; for example, use columns for Date, Product, Quantity, and Price.

C. Opening workbooks

Opening a workbook loads an existing Excel file so that its worksheets can be viewed or edited.

• Open command: Select File → Open → Browse, locate the file, select it, and choose Open.
• Keyboard shortcut: Press Ctrl+O to display the Open window.
• Recent files: Use the Recent list to reopen workbooks that were used recently.
• Direct opening: Double-click a recognized Excel file in File Explorer or another file manager.
• Cloud storage: Files stored in OneDrive or SharePoint may be opened from their synchronized folders or from Excel’s Open page.
• Compatibility: Excel may open older .xls files in Compatibility Mode because some modern features are unavailable in the older format.
• Protected View: A file downloaded from the internet or received by email may open as read-only; enable editing only when the source is trusted.

D. Saving workbooks

Saving records workbook changes in a storage location so that the data can be used later.

1. Save: Updates the current file without changing its name, location, or format.
   • Select File → Save, click the Save icon, or press Ctrl+S.
   • A newly created workbook prompts for a file name and location on its first save.
2. Save As: Creates a separate copy or changes the file name, location, or file type.
   • Select File → Save As or press F12 in many desktop versions.
   • Use a descriptive name such as Monthly_Sales_March.xlsx.

• Standard format: .xlsx preserves formulas, formatting, worksheets, and charts but does not store VBA macros.
• Macro-enabled format: .xlsm is required when the workbook contains VBA macros that must be preserved.
• Other formats: .csv saves plain tabular text and normally preserves only the active worksheet, not formulas, formatting, or multiple sheets.
• AutoSave: In supported Microsoft 365 versions, files stored in OneDrive or SharePoint can be saved continuously when AutoSave is enabled.
• Data protection: Save regularly and maintain backup copies of important workbooks to reduce the effect of accidental deletion or file corruption.

III. Cell Selection and Presentation

Excel processes data through individual cells and cell ranges, while formatting controls how those values are displayed and emphasized.

A. Range of cells

A range is a selected group of cells treated as a unit for calculation, formatting, copying, or other operations.

• Continuous range: A rectangular block is written using the first and last cell references separated by a colon; A1:C4 contains cells from columns A to C and rows 1 to 4.
• Single-column range: B2:B10 identifies cells B2 through B10.
• Single-row range: C5:G5 identifies five cells in row 5.
• Non-adjacent ranges: Hold Ctrl while selecting separate cells or blocks, such as A1:A5 and C1:C5.
• Selecting a range: Drag from the first cell to the last, or click the first cell, hold Shift, and click the last cell.
• Entire row or column: Click a row number or column letter; references such as A:A identify an entire column.
• Relative reference: A1 changes when a formula is copied; copying =A1*2 down one row changes it to =A2*2.
• Absolute reference: $A$1 remains fixed when copied because both the column and row are locked.
• Mixed reference: $A1 fixes only the column, while A$1 fixes only the row.
• Named range: A meaningful name such as TaxRate can represent a cell or range and make formulas easier to understand.

B. Formatting cells

Cell formatting changes the appearance or displayed form of data without normally changing the underlying stored value.

• Font formatting: Change typeface, size, color, bold, italic, or underline to distinguish headings and important values.
• Alignment: Position contents left, center, or right; Wrap Text displays long content on multiple lines within one cell.
• Number formats: Display values as General, Number, Currency, Accounting, Percentage, Fraction, Date, Time, or Scientific notation.
• Decimal control: Increase or decrease displayed decimal places; a stored value of 12.456 may display as 12.46 while retaining its underlying precision.
• Borders and fills: Apply lines and background colors to separate headings, totals, and categories.
• Merge and Center: Combines selected cells and centers one entry, but merged cells can complicate sorting and data manipulation.
• Cell styles: Apply coordinated combinations of font, fill, border, and number formatting.
• Conditional formatting: Applies formatting when a rule is met, such as highlighting values below 50 or displaying data bars.
• Format Cells dialog: Press Ctrl+1 to access number, alignment, font, border, fill, and protection settings.
• Worked example: If A1 contains 0.25, applying Percentage format displays 25%; the stored value remains 0.25.

IV. Formulas, Functions, and Automatic Calculation

Excel functions use values, references, operators, and conditions to perform repeatable calculations and return results.

A. Mathematical functions

Mathematical functions perform arithmetic, aggregation, rounding, and related numerical operations.

• SUM: Adds numbers in a range.
  
  EXCEL

    =SUM(B2:B6)

  
  Here, B2:B6 is the range whose values are added.
• PRODUCT: Multiplies all supplied values.
  
  EXCEL

    =PRODUCT(A2,B2)

• ROUND: Rounds a number to a specified number of digits.
  
  EXCEL

    =ROUND(C2,2)

  
  C2 is the number and 2 is the number of decimal places.
• ABS: Returns the non-negative magnitude of a number; =ABS(-15) returns 15.
• SQRT: Returns the positive square root; =SQRT(81) returns 9.
• POWER: Raises a number to an exponent; =POWER(5,2) returns 25.
• Arithmetic operators: Excel uses + for addition, - for subtraction, * for multiplication, / for division, and ^ for powers.
• Order of operations: Parentheses are evaluated first, followed by powers, multiplication or division, and addition or subtraction.
• Worked example: If B2:B4 contains 10, 15, and 20, =SUM(B2:B4) returns 45.

B. Logical functions

Logical functions test conditions and return results according to whether those conditions are true or false.

• Comparison operators: Conditions use =, >, <, >=, <=, and <>, where <> means “not equal to.”
• IF function: Returns one result when a condition is true and another when it is false.
  
  EXCEL

    =IF(B2>=50,"Pass","Fail")

  
  B2>=50 is the logical test, "Pass" is the true result, and "Fail" is the false result.
• AND function: Returns TRUE only when every supplied condition is true.
  
  EXCEL

    =AND(B2>=50,C2>=50)

• OR function: Returns TRUE when at least one supplied condition is true.
• NOT function: Reverses a logical value; =NOT(A1="Paid") returns TRUE when A1 does not contain Paid.
• Nested use: Logical functions can appear inside IF, as in =IF(AND(B2>=50,C2>=50),"Pass","Fail").
• Text constants: Text returned by a formula must normally appear in quotation marks, such as "Eligible".
• Blank versus zero: A blank cell and a cell containing 0 are not always equivalent, so conditions should reflect the intended test.

C. Date and time functions

Date and time functions create, extract, and calculate calendar or clock values stored internally as serial numbers.

• TODAY: Returns the current system date and updates when the workbook recalculates.
  
  EXCEL

    =TODAY()

• NOW: Returns the current system date and time.
• DATE: Constructs a valid date from separate year, month, and day values.
  
  EXCEL

    =DATE(2025,6,15)

• TIME: Constructs a time from hour, minute, and second values; =TIME(14,30,0) represents 2:30 PM.
• Date components: YEAR, MONTH, and DAY extract parts of a date; =YEAR(A2) returns the year stored in A2.
• Time components: HOUR, MINUTE, and SECOND extract parts of a time value.
• Date arithmetic: Subtracting an earlier date from a later date returns the number of elapsed days.
• Display format: One stored date may appear as 15/06/2025, June 15, 2025, or another regional format without changing its value.
• System dependence: TODAY() and NOW() depend on the computer’s clock and recalculate rather than permanently recording the original entry time.

D. AutoSum

AutoSum is a shortcut that detects nearby numerical data and inserts a suitable SUM formula automatically.

• Location: The AutoSum button, marked by the sigma symbol Σ, appears on the Home and Formulas tabs.
• Operation: Select the cell below a column or to the right of a row of numbers, choose AutoSum, verify the suggested range, and press Enter.
• Keyboard shortcut: Alt+= inserts an AutoSum formula in many desktop versions of Excel.
• Generated formula: If values occupy B2:B8 and B9 is selected, AutoSum usually proposes:
  
  EXCEL

    =SUM(B2:B8)

• Range verification: Blank cells, text entries, or nearby totals may cause Excel to suggest an incomplete or incorrect range; the highlighted range should therefore be checked.
• Other functions: The AutoSum menu may also provide Average, Count Numbers, Max, and Min.
• Multiple totals: Selecting several adjacent total cells before using AutoSum can create formulas for multiple rows or columns at once.
• Dynamic result: Because AutoSum inserts a formula rather than a fixed value, the total updates when any referenced number changes.