Introduction to Spreadsheets

  • This lesson covers basic spreadsheet concepts and introduces common screen elements found in spreadsheet applications, particularly Microsoft Excel in versions 2007 to 2011 (Windows OS).
  • Differences in screens and menus may exist in other versions or different spreadsheet applications but concepts remain similar.

Workbook Structure in Excel

  • An Excel workbook can consist of multiple sheets; the default is three sheets per workbook.
  • A spreadsheet is defined as a table of values arranged in rows and columns.
  • Excel has unique elements for navigation, formatting, and editing worksheets, which will be discussed alongside commonly used toolbars.

Uses of Microsoft Excel

  • General Uses:

    • Financial tool to perform calculations automatically.
    • Creation of contact lists, budgets, and data tracking/analysis for business and personal use.
    • Excel streamlines tasks compared to manual calculations.

  • Educational Uses:

    • Teachers can manage student records such as names, marks, ranks, and other details.
    • Excel allows for the production of student reports.
    • It can generate graphical representations of data (e.g., pie charts, bar charts) for easy understanding of students' statistics.

Screen Elements in Excel 2007

  • Key components of the Excel 2007 window:
    1. Office Button: Located in the upper left corner, providing access to file commands like New, Open, Save, Save As, and Print.
    2. Quick Access Toolbar: Customizable toolbar featuring frequent commands (e.g., Save, Undo).
    3. Tabs: Provide options to access different tools and commands.
    4. Ribbon: Displays commands grouped by category across different tabs.
    5. Dialog Box Launcher: Opens dialog boxes or task panes for modifying settings.
    6. Lists and Galleries: Dropdown arrows allow access to various lists and galleries.
    7. Status Bar: Shows information about the active workbook.
    8. Workbook Window: Area to enter data and labels.
    9. View Buttons: For switching between different views.
    10. Zoom Controls: To zoom in or out.

Office Button in Detail

  • The Office Button serves similar functions as the traditional File Menu in earlier versions, allowing users to manage file operations.

Quick Access Toolbar

  • Located at the top of the Excel window next to the Office Button.
  • Includes commands such as Save and Undo and allows for user customization.
  • To customize, click the list arrow at the right end of the toolbar to display the menu and select the desired commands for addition or removal.

The Ribbon

  • The Ribbon in Excel replaces previous menus and task panes, organized into tabs by task.
  • Each tab contains groups for subtasks; for example, groups for Font or Alignment on the Home tab.

Tabs on the Ribbon

  • Types of Tabs:
    • Standard Tabs: Default tabs at startup include Home, Insert, Page Layout, Formulas, Data, Review, View, and Add-ins.
    • Contextual Tabs: Tabs that appear only when performing certain tasks (e.g., Picture Tools, Drawing).
    • Program Tabs: Shown when switching to view modes such as Print Preview.

Dialog Box Launcher

  • Found in the lower right-hand corner of some groups, this allows access to additional settings through dialog boxes.

Displaying and Hiding the Ribbon

  • To minimize the Ribbon, double-click the Home tab. Clicking on a tab shows the Ribbon temporarily; click again to minimize.

Formula Bar

  • The Formula Bar, located above the workbook window, allows for value or formula entry/editing. It displays contents of the active cell.

Screen Elements in Excel 2010

  • Similar to Excel 2007 but replaces the Office Button with the File tab.
  • Main components: Quick Access Toolbar, Ribbon Tabs, Ribbon Groups, Zoom Controls, View Buttons, Status Bar.

Opening a New Spreadsheet

  • Upon launching Excel, a new, untitled workbook labeled Book1 is provided.
  • To open a new workbook:
    • Keyboard shortcut: Press Ctrl + N.
    • In Excel 2010: Use the File tab, select "New".
    • In Excel 2007: Click the Office Button, select "New", and then create a blank workbook.

Opening an Existing Workbook

  • In Excel 2010, for recently used workbooks, navigate through the File Menu to "Recent" to select.
  • If not recent, click "Open" to navigate the Open dialog and select from saved locations.

Moving Around Within a Workbook

  • Movement options include:
    • Down One Row: Press Enter key.
    • Right One Column: Press Tab key.
    • Scroll Several Rows Up/Down: Use vertical scroll bar.
    • Scroll Several Columns Left/Right: Use horizontal scroll bar.
    • Return to Upper Left Corner: Press Ctrl + Home.

Sheet Tabs

  • Each worksheet has a Sheet tab, named Sheet1, Sheet2, etc. Excel permits renaming tabs as necessary.

Moving Sheets Around

  • To change the order of sheets, click and hold the mouse on the sheet tab, moving it into a new position indicated by a black triangle during the drag.

Saving a New Workbook

  • The Save As window opens on the first save attempt irrespective of method (Save or Save As).
  • Newer Excel files (.xlsx) may not be compatible with older versions (.xls). It's advisable to save as Excel 97-2003 format for compatibility.
  • In the Save As dialog box, use the Format box to select the desired file format.

Closing the Workbook

  • In Excel 2007, use the Office Button followed by "Close" to close a file without exiting Excel.
  • In Excel 2010, open the File tab and select "Close".
  • Another closing method is using the close box on the workbook window.

Entering Data

  • Data entering procedures are similar across Excel 2007 and 2010.
  • Cells: Store data types such as values, text labels, or formulas.
  • Activating a cell requires clicking it, which darkens its border. Data can be entered directly or via the Formula Bar, finishing with Return or the Green Check Mark in the Formula Bar to accept changes.

Filling Data

  • AutoFill: Automatically copies a cell's content to adjacent cells.
  • To perform AutoFill:
    • Move the pointer to the cell's bottom right corner until it turns into a black cross (fill handle) and drag to fill adjacent cells.

Inserting Rows and Columns

  • Inserting a Row: Select the desired row by clicking its heading, then choose "Insert" under the Home tab. A new row will be added above, shifting existing rows down.
  • Inserting a Column: Click on the desired column heading, then use "Insert" under the Home tab. A new column will appear to the left of the selected column, shifting other columns right.

Deleting Rows and Columns

  • Select the row or column for deletion. Then in the Home tab choose "Delete" in the Cells group and select "Delete Sheet Rows" or "Columns".
  • Alternatively, right-click the row/column header to access deletion from the contextual menu.

Cut, Copy, and Paste

  • Similar to other applications, these commands work for individual or ranges of cells.
  • Cut: Select cells, and in the Home tab click the Cut icon or use Ctrl + X.
  • Copy: Select cells, and in the Home tab click the Copy icon or use Ctrl + C.
  • Paste: Select the destination cell and click the Paste icon or use Ctrl + V.

Formulas

  • Formulas are sequences of values, cell references, and operators used to produce a new value by performing calculations.
  • Components of a Formula: Formulas must begin with an equal sign '='. For example, =A1+A2 sums values from Cell A1 and A2.

Entering a Formula

  • Activate the cell where the formula will be placed and type '=' followed by the formula expression. A corresponding value appears in the cell after pressing Return.
    • You can also click on cells to enter references directly in the formula.

Editing Formulas

  • Double-click a cell to edit its formula directly, or hold Ctrl and press the Grave accent mark ( ` ) to view formulas in the cells instead of their results.

Relative Addressing

  • By default, Excel uses relative addressing, which adjusts cell references when a formula is copied to a new location. For example, a formula summing a range in Cell B5 will refer to Column C when copied to C5.

Absolute Addressing

  • Absolute cell references remain fixed regardless of the formula's new location. These are denoted with a $ symbol (e.g., $A$1). Pressing F4 toggles the state of a reference (Relative, Absolute, Mixed).

Functions

  • Functions simplify formula creation and are accessible via the Insert Function button.
  • Example of a function: using =SUM(A1:A10) for summation instead of adding individual cells.

Creating Functions

  • Activate the desired cell, click on Insert Function, and select a function from the dialog that appears.

AutoSum

  • Use the AutoSum button to automatically generate a formula summing a range. This can be done by selecting the range first or selecting an empty cell next to the range.

Working with Cells and Ranges

  • A range reference indicates size and position of a cell range. Ranges can be adjacent (e.g., A1:G5) or non-adjacent (e.g., A1:A5; F1:G5).
  • Selecting a range enables collective operations like moving and copying.

Working with Formulas

  • Order of Precedence: Determines the sequence of operations in calculations.
  • Viewing: Select a cell to review the formula in the formula bar; each reference is color-coded.
  • Copying formulas adjusts references according to the new position.

Formatting Text and Numbers

  • Formatting Text: Change font type, size, and style through the Home tab. Adjust attributes like wrapping and merging text in cells.
  • Formatting Numbers: Access formatting options under the Home tab or through the dialog box launcher for additional number formatting options.

Changing Column/Row Width

  • To adjust widths, hover the pointer on the line between columns/rows until it shows a double-headed arrow. Drag to resize.

Borders and Format Painter

  • Borders: Add lines around cells for visual separation. Defined through the Home tab’s border button.
  • Format Painter: Copy formatting from one cell to another, deploying multiple attributes at once. Click the Format Painter icon and then select target cells.

Creating Charts

  • Learn to create charts representing data graphically. Charts enhance visual communication of spreadsheet data.

Types of Charts

  • Column Chart: Displays data changes over time or compares items.
  • Bar Chart: Shows comparisons among items with vertical categories.
  • Line Chart: Illustrates trends over intervals.
  • Pie Chart: Portrays proportions of a whole with a single data series.

Creating a Chart in Excel 2007/2010

  • Select the data range for the chart type via the Insert tab on the Ribbon, then choose the desired chart.

Modifying Chart Type

  • To select a different chart type, click the chart, access the Design tab under Chart Tools, and change the chart type.

Exercise

  • Create a student record system with five subjects and around twenty students, showing their total, average, and grades.
  • Create a chart to visualize student names and average marks using two columns in the graph.