Class Session: Wednesday Oct 17, 2018

Spreadsheet Introduction


  1. About.com for some basic terminology you will be using
  2. Excel 2013 Courses and see if any are ones you need to view. You might want to bookmark all or parts of all of them.
  3. Part I: Worksheet Basics and Part II: Formulas and Functions in Excel 2013: The Missing Manual

These from About.com can also be useful

As can these from Microsoft

top/reload prep panel

History of Spreadsheets

Accountants have been using paper spreadsheets for hundreds of years.

Here is an image of a French ledger book from the late 19th century.

Source: Jaysonhome.com

There are columns of descriptive text and numbers. The totals are tabulated horizontally and vertically. The "set up" for this ledger was printed in advance. The accountant would then have a standard format for data entry. You can enlarge the image to see it in greater detail.

Spreadsheet applications were created in the 1970s and were clunky and difficult to use.

In the mid 80s the newly released graphical user interfaces on Macintosh and Windows operating systems made working with spreadsheets much easier. For more information on the history of spreadsheets, if interested, visit DSS resources.com.

Spreadsheets today

Spreadsheets are applications that display rows and columns of data. The entered data can be calculated, sorted and transformed. Charts and graphics can be easily produced from the data calculations.

Excel spreadsheet program is one of the most used spreadsheet applications. Excel's parts and its Worksheet are similar to Word and other Office applications. Many of the shortcuts in Word will work in Excel.

  • CTRL+S to save
  • CTRL+N for a new document
  • CTRL+C to copy
  • CTRL+X to cut
  • CTRL+V to paste, etc.

OpenOffice Calc or LibreOffice Calc are both reasonable alternatives, but Excel is still the most used and has some advantages.

Lesson Plan:

Lesson-04-01: Saving and Entering Data, Editing Data, Deleting Data, Autofill

Download lesson-04-01.xlsx and follow along as I demo the worksheets.

Lesson-04-02: Worksheets, Ribbons,

Download lesson-04-02.xlsx and follow along as I demo the worksheets.

This workbook (the entire file) contains the following worksheets:

1a.Worksheets

Objective: learn how work with worksheets within a workbook

Demos: New worksheet, copy worksheet, move worksheet to another workbook (Another open Excel file) or to a new workbook, select multiple worksheets, rename a worksheet, change worksheet tab color, turn gridlines on and off.

1b. Overview

Objective: learn the global basics. Focused on working with the top three bars, the worksheet area, and the bottom worksheet bar and status bar below the worksheet bar.

Top three bars: Quick access bar, ribbon bar (note the expansion buttons on each tab in the ribbon), formula bar with name box and insert function

Worksheet Area: Top left corner box selects all; selecting a row or column, selecting multiple rows or columns, scroll bars; inserting one columns or row; inserting multiple columns or rows, moving columns or rows, shift dragging to move.

2. Mac Ribbons

This worksheet shows at-a-glance chart of Macintosh ribbons and the operations that are available under each ribbon. If it is highlighted in yellow, you will need it for your 04-task assignment. Orange shows that the option is available one more than one ribbon.

3a. Home Ribbon

This home ribbon worksheet showcases the commands in the areas of the clipboard, font, and alignment areas. Examples of all of these options are shown in the worksheet. As you click on the samples, you can see the buttons in the ribbon change to match the example. There are also examples of wrapped text, indented text, amd merged and centered text.

3b. Home Ribbon

This worksheet shows examples of all of the options in the drop-down box to the right of the Wrap Text button: general, number, currency, accounting, short date, long date, time, percent fraction, scientific, text.

There are also examples of conditional formatting and format as Table button.

A demonstration of the insert, delete, and format button was shown. Row height and column width were also demonstrated. Important, because Task-04 requires you to make a column fit and to adjust a column height.

Formatting Practice

This worksheet demonstrates how to clean up the header cells (consolidate multiple cells into one cell), format rows with different colors (sorting was needed to do this efficiently).

Also covered: freeze panes, hide columns and rows, validate data

4. Formulas, filters

This worksheet demonstrates the sum and average formula. The filter command and a multi-filter command was also demonstrated.

5. Relative vs. locked formula

6. Flash fill

This is not available Mac Excel Version

7. Examples

Reference for Angled text

See Spreadsheet Task Page for practice files and help links.

Quick links

opal login: ssh ONYEN@opal.ils.unc.edu
opal password: ONYEN password

Instructor

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.

Office hours by appointment.