Spreadsheets
Learning Objectives
- Demonstrate an understanding of the purposes of spreadsheets and the essentials of mastering the use of spreadsheet applications.
- Identify the major components of electronic spreadsheets and how they work, including navigation of program menus and ribbon, and screen manipulation.
- Create, design, and edit spreadsheets and workbooks using formatting techniques.
- Understand the syntax and apply electronic spreadsheet formulas and functions: including simple calculations; analyze and chart data; and summarize data with pivot tables.
- Understand the basics of using spreadsheets’ statistical functions including average and standard deviation.
- Apply data analysis techniques like charts and graphs to different data structures including lists and tables.
- Create basic plots including histograms and dependent vs. independent variables.
LEARN IT
A spreadsheet is a file with cells in rows and columns. A spreadsheet helps arrange, calculate, and sort data. Data in a spreadsheet can be numeric values, text, formulas, references, and functions. We will even learn how to embed charts and graphs into a spreadsheet. Spreadsheets are used to visualize data in a meaningful way that can be used to make complex decisions. Spreadsheets are the most basic tool in data science. Spreadsheets can take raw data, and tell a story with it.
Common Spreadsheet Software:
|
Software Name |
Type |
Key Features |
|
Microsoft Excel |
Commercial |
Runs on Windows and MacPart of Office 365. Recent features include robust formulas and functions, charts, graphs, and sparklines. Arguably the most popular spreadsheet software. |
|
Google Sheets |
Online—Part of the free, web-based Google Docs Editors suite |
Allows users to create, view, and edit spreadsheets online while collaborating with other users in real-time. Available as a web application supported on most web browsers. Compatible with Google Drive. |
|
LibreOffice Calc |
Free and open-source office productivity software suite |
Uses the OpenDocument standard, but supports formats of most other major office suites, including Microsoft Office. Official support for Microsoft Windows, macOS, and Linux. It has several unique features, including a system that automatically defines a series of graphs, based on information available to the user. |
|
Apple Numbers |
Online—Part of the iWork productivity suite |
Runs on the MacOS, iPadOS, and iOS operating systems.
|
Going forward, we will focus on Microsoft Excel and LibreOffice Calc.
SPREADSHEET PRACTICE 1
There are many similarities across spreadsheet software, so the skills we are learning can be translated to any other software and apps. The following assignments are designed to be completed using either Microsoft Excel in Office 365 or LibreOffice Calc on a PC with Windows 10 or higher.
We will use spreadsheet software to perform Data Analysis including complex calculations, analyze data so that we can make intelligent decisions, and create