IC3/Spreadsheets

A spreadsheet is an interactive computer application program for organization and analysis of data in tabular form.

This lesson introduces spreadsheets and helps learners prepare for the IC³ Key Applications certification exam.

Objectives and Skills

Objectives and skills for the spreadsheets portion of IC³ certification include:

Objectives

• Be able to modify worksheet data, structure and formatting
• Be able to sort data, manipulate data using formulas and functions, and create simple charts

Skills

• Layout: Insert/delete, cell sizes, alignment, navigation, merging cells
• Data Management: Filter and sort, formulas and functions, number format, cell format, charts, graphs

Readings

1. Wikipedia: Spreadsheet
2. Wikipedia: Microsoft Excel
3. Wikipedia: Google Docs, Sheets, and Slides
4. Wikipedia: LibreOffice
5. Wikipedia: Chart
6. Wikipedia: Histogram
7. Wikipedia: Microsoft Office XML Formats
8. Wikibooks: The Computer Revolution/Software/Spreadsheet
9. Wikipedia: List of spreadsheet software

Multimedia

1. YouTube: Microsoft Excel Basics
2. YouTube: Microsoft Excel: How to Copy, Move and Delete Cells
3. YouTube: How To Use Microsoft Excel-Help With Formulas Functions
4. YouTube: Tour Of Excel: Formulas, Formatting, Sort, Filter, PivotTables, Charts, Keyboards
5. YouTube: 10 Must Know Microsft Excel Tips and Tricks
6. YouTube: How To Use Shortcuts For Microsoft Excel
7. YouTube: Add,Remove,Find and Edit Comments in Excel
8. YouTube: Excel Beginner Tutorial 1 - Spreadsheet Basics
9. Excel Tutorial for Beginners #1 - Overview

Activities

1. Complete one or more of the following tutorials:
   • Microsoft: Excel video training
   • GCF: Google Spreadsheets Tutorial (Lessons 8<19)
   • GCF: Microsoft Excel 2016
   • GCF: Excel Formulas
   • GCF: Excel Tips
   • LinkedIn learning topic Excel
2. Practice various spreadsheet concepts in Microsoft Excel:
   • http://www.excel-easy.com/
   • http://www.allthetests.com/quiz26/quiz/1233171294/Spreadsheet-Quiz

Lesson Summary

• Spreadsheets are developed as computerized simulations of paper accounting worksheets.
• Spreadsheets are useful for "what-if" analysis since many cases can be rapidly investigated without manual recalculation. Modern spreadsheet software can have multiple interacting sheets, and can display data either as text and numerals, or in graphical form.
• In addition to performing basic arithmetic and mathematical functions, modern spreadsheets provide built-in functions for common financial and statistical operations.
• A spreadsheet consists of a table of cells arranged into rows and columns and referred to by the X and Y locations. X locations, the columns, are normally represented by letters, "A", "B", "C", etc., while rows are normally represented by numbers, 1, 2, 3, etc.
• The key feature of spreadsheets is the ability for a formula to refer to the contents of other cells, which may in turn be the result of a formula. To make such a formula, one simply replaces a number with a cell reference.
• The ability to chain formulas together is what gives a spreadsheet its power. Many problems can be broken down into a series of individual mathematical steps, and these can be assigned to individual formulas in cells. Some of these formulas can apply to ranges as well, like the SUM function that adds up all the numbers within a range.
• Microsoft Excel is a spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications. It has been a very widely applied spreadsheet for these platforms.

Key Terms

algorithm

A set of steps that are followed in order to solve a mathematical problem or to complete a computer process.

bar chart

A chart that presents grouped data with rectangular bars with lengths proportional to the values that they represent.

cell

A unit in a database table or spreadsheet, formed by the intersection of a row and a column.

cell range

A group or block of cells in a worksheet that have been selected or highlighted.

cell reference

A string identifying a particular cell in a spreadsheet, possibly relative to the cell containing the reference.

chart

Visual representations of worksheet data.

circular reference

When the formula in one cell refers—directly, or indirectly through a chain of cell references—to another cell that refers back to the first cell.

column

A vertical series of cells in a chart, table, or spreadsheet.

dynamic data exchange

A method of interprocess communication under Microsoft Windows or OS/2. It allows one program to subscribe to items made available by another program, for example a cell in a Microsoft Excel spreadsheet, and be notified whenever that item changes.

encryption

The process of encoding messages or information in such a way that only authorized parties can read it.

end-user

A person who ultimately uses or is intended to ultimately use a product.

end-user development (EUD)/end-user programming (EUP)

Activities and tools that allow end-users – people who are not professional software developers – to program computers.

expression

A combination of values and symbols used to produce a new value.

Excel

A spreadsheet developed by Microsoft for Windows, macOS, Android and iOS. It features calculation, graphing tools, pivot tables, and a macro programming language called Visual Basic for Applications.

Extensible Markup Language (XML)

Markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable.

formula

Any equation entered into a cell on a Microsoft Excel spreadsheet.

function

A standard routine used to perform common tasks. It represents a complex formula that uses reserved words e.g. VLOOKUP, IF.

histogram

A graphical representation of the distribution of numerical data.

LibreOffice Calc

The spreadsheet component of the open-source LibreOffice software package.

line chart

A type of chart which displays information as a series of data points called 'markers' connected by straight line segments.

logical spreadsheet

A spreadsheet in which formulas take the form of logical constraints rather than function definitions.

macro

A rule or pattern that specifies how a certain input sequence (often a sequence of characters) should be mapped to a replacement output sequence (also often a sequence of characters) according to a defined procedure.

macro recorder

A piece of software that records user actions for playback at a later time.

Microsoft Query

A visual method of creating database queries using examples based on a text string, the name of a document or a list of documents.

object linking and embedding (OLE)

Proprietary technology developed by Microsoft that allows embedding and linking to documents and other objects.

open database connectivity (ODBC)

Standard application programming interface (API) for accessing database management systems (DBMS).

pie chart

A circular statistical graphic, which is divided into slices to illustrate numerical proportion.

pivot table

A data summarization tool found in data visualization programs such as spreadsheets or business intelligence software.

rainbow table

A precomputed table for reversing cryptographic hash functions, usually for cracking password hashes.

reference

A value that enables a program to indirectly access a particular datum, such as a variable or a record, in the computer's memory or in some other storage device. The reference is said to refer to the datum, and accessing the datum is called dereferencing the reference.

row

A series of data banks laid out in a horizontal fashion in a table or spreadsheet.

sheets

In the earliest spreadsheets, cells were a simple two-dimensional grid. Over time, the model has expanded to include a third dimension, and in some cases a series of named grids, called sheets. The most advanced examples allow inversion and rotation operations which can slice and project the data set in various ways.

spreadsheet

An interactive computer application for organization, analysis and storage of data in tabular form.

subroutine

A sequence of program instructions that perform a specific task, packaged as a unit. This unit can then be used in programs wherever that particular task should be performed.

time series

A series of data points indexed (or listed or graphed) in time order.

values

Numbers entered into spreadsheet cells

Visual Basic for Applications (VBA)

An implementation of Microsoft's discontinued event-driven programming language, Visual Basic 6, and its associated integrated development environment (IDE).

workbook

A spreadsheet file that contains one or more worksheets.

worksheet

A single spreadsheet that contains cells organized by rows and columns.

Review Questions

Enable JavaScript to hide answers.

Click on a question to see the answer.

1. Excel macros are written in the programming language ____.

   Excel macros are written in the programming language VBA.
2. To edit a VBA macro, you need to use the Visual Basic ____.

   To edit a VBA macro, you need to use the Visual Basic Editor.
3. To quickly switch to the Visual Basic Editor, press ____ on your keyboard.

   To quickly switch to the Visual Basic Editor, press Alt F11 on your keyboard.
4. The ____ is a Visual Basic Editor window.

   The Properties window is a Visual Basic Editor window.
5. A(n) ____ is a collection of macros, worksheets, data entry forms, and other items that make up a customized application.

   A(n) project is a collection of macros, worksheets, data entry forms, and other items that make up a customized application.
6. Within each project are various items called ____.

   Within each project are various items called objects.
7. A(n) ____ is any element within the Excel working environment, such as a worksheet, macro, or workbook.

   A(n) object is any element within the Excel working environment, such as a worksheet, macro, or workbook.
8. When you enter the name and description of a project, you are actually modifying two of its ____.

   When you enter the name and description of a project, you are actually modifying two of its properties.
9. When you want to view the VBA macro code associated with any item in Project Explorer, you use the ____ window.

   When you want to view the VBA macro code associated with any item in Project Explorer, you use the Code window.
10. Formatting a cell or displaying a chart are examples of ____ procedures in Visual Basic.

    Formatting a cell or displaying a chart are examples of sub procedures in Visual Basic.
11. A ____ procedure returns a value.

    A function procedure returns a value.
12. To create sub procedures without using the macro recorder, you need to know some rules of VBA ____.

    To create sub procedures without using the macro recorder, you need to know some rules of VBA syntax.
13. The ____ command is always the last line in a sub procedure and tells Excel to stop running the macro.

    The End Sub command is always the last line in a sub procedure and tells Excel to stop running the macro.
14. Comments must begin with a(n) ____.

    Comments must begin with a(n) apostrophe.
15. Procedure names cannot contain ____.

    Procedure names cannot contain spaces.
16. The Excel object you will use to create VBA programs and refer to a range in a worksheet is ____.

    The Excel object you will use to create VBA programs and refer to a range in a worksheet is range.
17. A(n) ____ is a named element in a program that can be used to store and retrieve information.

    A(n) variable is a named element in a program that can be used to store and retrieve information.
18. Every variable is identified by a unique variable ____.

    Every variable is identified by a unique variable name.
19. You can define exactly what type of data can be stored in a variable with the ____ command.

    You can define exactly what type of data can be stored in a variable with the Dim command.
20. The ____ symbol is used to combine two text strings into a single text string.

    The & symbol is used to combine two text strings into a single text string.
21. Most software programs can both save and retrieve data in a(n) ____ file format.

    Most software programs can both save and retrieve data in a(n) text file format.
22. When you use Excel to connect to a text file, the ____ Wizard determines whether the data is in a fixed-width format or a delimited format.

    When you use Excel to connect to a text file, the text import Wizard determines whether the data is in a fixed-width format or a delimited format.
23. The row at which you begin the import process is called the ____ row.

    The row at which you begin the import process is called the starting row.
24. Unless you specify a format, Excel will apply a ____ format to all columns.

    Unless you specify a format, Excel will apply a general format to all columns.
25. In response to a(n) ____, a database displays the records and fields that meet the question's requirements.

    In response to a(n) query, a database displays the records and fields that meet the question's requirements.
26. If you want to extract only specific records when you query a database, your query would contain ____.

    If you want to extract only specific records when you query a database, your query would contain criteria.
27. The ____ Wizard lets you preview the structure of the database and its contents.

    The query Wizard lets you preview the structure of the database and its contents.
28. When you ____ data, you specify which records you want to retrieve using specific criteria.

    When you filter data, you specify which records you want to retrieve using specific criteria.
29. The default folder for queries is the ____ folder on the hard disk

    The default folder for queries is the queries folder on the hard disk
30. Microsoft Query is a(n) ____ that includes several tools that allow you to create even more complex queries.

    Microsoft Query is a(n) Office program that includes several tools that allow you to create even more complex queries.
31. ____ has been widely used in database programs, Web programming, and word processing.

    XML has been widely used in database programs, Web programming, and word processing.
32. Clicking a ____ in a worksheet will activate the computer's Web browser to display the Web page associated with that entry.

    Clicking a hyperlink in a worksheet will activate the computer's Web browser to display the Web page associated with that entry.
33. In XML, the rules about which elements are required and which are not are stored in a document called the ____.

    In XML, the rules about which elements are required and which are not are stored in a document called the schema.
34. Databases such as those created in Access are examples of ____ databases which reduce data redundancy and increase data integrity.

    Databases such as those created in Access are examples of relational databases which reduce data redundancy and increase data integrity.
35. In order to override Excel so that it always enables data sources and refreshes the connection to the source without prompting, you should define the location of the data sources as ____.

    In order to override Excel so that it always enables data sources and refreshes the connection to the source without prompting, you should define the location of the data sources as trusted locations.
36. If you orient a one-variable table so that the input values are in the first row, you would use the ____ input cell option.

    If you orient a one-variable table so that the input values are in the first row, you would use the row input cell option.
37. In two-variable tables, you must always place a reference to the result cell in the ____ corner of the table at the intersection of the row values and the column values.

    In two-variable tables, you must always place a reference to the result cell in the upper-left corner of the table at the intersection of the row values and the column values.
38. ____ are a powerful feature of Excel. If used properly, they help you perform complex calculations within a single formula and extend a single formula over a range of cells.

    Array formulas are a powerful feature of Excel. If used properly, they help you perform complex calculations within a single formula and extend a single formula over a range of cells.
39. A(n) ____ arrow provides a visual clue to the relationship between two cells by pointing from the precedent cell to the dependent cell.

    A(n) tracer arrow provides a visual clue to the relationship between two cells by pointing from the precedent cell to the dependent cell.
40. What operating systems can someone use Excel on?

    Windows, macOS, Android and iOS

Assessments

• Quiz
• Flashcards: Quizlet: Spreadsheets
• Quiz: Quizlet: Spreadsheets
• Quiz: Quizlet: Spreadsheets
• Flashcards: Quizlet: Excel Spreadsheet Vocabulary
• Quiz: Quizlet: Excel Spreadsheet Vocabulary
• Quiz: GCFLearnFree: Excel 2016

See Also

• Spreadsheets
• Apache openoffice.org
• Microsoft Excel Glossary
• Extending the Concept of Spreadsheet by Jocelyn Paine

1. Difference Between Excel Worksheet & Workbook

References

Subject classification: this is an information technology resource.

Type classification: this is a lesson resource.

Completion status: this resource is ~40% complete.

← Word Processing

IC3

Presentation Software ⊟