Excel Basics

Excel Terminology

Basic Components

  • Workbook: An Excel file that contains one or more worksheets.
  • Worksheet: A single spreadsheet page within a workbook.
  • Cell: An individual box in a worksheet used for entering data.
  • Cell Reference: A way to identify a cell using its column letter and row number (e.g., A1).

Formulas and Functions

  • Formula: An equation used to perform calculations in Excel (e.g., =A1+B1=A1+B1).
  • Function: A predefined formula that simplifies common calculations (e.g., =SUM(A1:A5)=SUM(A1:A5)).
  • Range: A selected group of cells (e.g., A1:A10).
  • Absolute Reference: A fixed reference to a specific cell that does not change when the formula is copied. Indicated by dollar signs (e.g., $A$1).
  • Relative Reference: A cell reference that changes automatically when the formula is copied to another cell (e.g., A1).

Features and Tools

  • AutoFill: A feature that automatically fills cells with a series of data or formulas based on a pattern.
  • Chart: A visual representation of data, such as pie charts, bar charts, or line charts.
  • Filter: A tool that allows you to display only the data that meets specific criteria.
  • Sort: Arranging data in a specific order, either ascending or descending.
  • Conditional Formatting: Formatting cells based on specified rules. For example, highlighting all values over 100.
  • Data Validation: A feature that restricts the type of data or values that users can enter into a cell.
  • Freeze Panes: A feature that keeps specific rows or columns visible while scrolling through the worksheet.
  • Wrap Text: A formatting option that makes text appear on multiple lines within a cell.
  • Merge & Center: Combining multiple cells into one and centering the content within the merged cell.

Functions

  • PMT Function: Calculates the payment amount for a loan. The syntax is =PMT(rate,nper,pv)=PMT(rate, nper, pv), where:
    • rate = interest rate per period,
    • nper = total number of periods,
    • pv = present value (loan amount).
  • VLOOKUP Function: Searches for a value in the first column of a range and returns a related value from another column in the same row.
  • IF Function: Returns one value if a specified condition is true and another value if the condition is false.

Pivot Table

  • Pivot Table: A tool that summarizes large amounts of data in an interactive way, allowing for easy analysis and reporting.