Spreadsheet Intro

Spreadsheet Overview

We will glance over the Spring 2021 lecture notes.

Just discovered this: the University of Michigan Library has a very nice Excel Overview

Data Module Task 04 Overview

You are to download (see below) and modify several existing worksheets to make them a more useful product. To be specific, these worksheets are collected from a variety of publicly available data provided by the US government in spreadsheet form.

The 04-task will require you show capability with a number of data management skills

• create new worksheets
• cell formatting
• cell and worksheet formatting
• validate data
• freeze panes
• hide and un-hide data
• print rows with column headers each page (only on the format worksheet, not all the other worksheets.)
• building formulas and using functions that will include some of the following: max | min | average | sum | count | countblank | countif
• Conditional Formatting
• converting raw data into charts/data displays
• chart annotating
• sorting - simple and multivalue (warning: if you are not working in a table or filter view, you must include all the data in all the columns to keep your data from getting mixed up.)
• filtering - simple and multivalue (filters will ensure that all your data will stay in the same row.)
• VLookup
• pivot tables
• We will not use manually entered headers and footers in this task. You will use Page Layout. Also, do not change the view of your worksheets to print view. Just enter the settings in the Page Layout dialog boxes. I will be able to find them and grade them. Submitting your project in print view will break all of the frozen panes and complicate my grading workflow. All worksheets in your workbook should be in grid view only.

Additionally, we will introduce basic database concepts

• we will gain a familiarity with Entity Relationship Model concepts and terminology
• we will gain a familiarity with Structured Query Language (SQL) queries

Today's Session

For this session you will need to download the following:

• the grade sheet: task04.gradesheet-2021-ss1.xlsx The grade sheet may be enough direction for students that have substantial skills, but those students that do not attend class or watch the videos do not usually get all the tasks done correctly.
• the more-detailed PowerPoint instructions for today: formatting-functions-formulas-2021-ss1.pptx
• the workbook with the worksheets that you will edit and submit for final grading: task04-worksheet-2021-SS1.xlsx

We will use the Powerpoint for our guide to complete the following:

1. Formatting a Worksheet
2. Setting up A Workbook
3. Setting up Conditional Formatting
4. Freezing Panes
5. Hiding Data: Columns
6. Hiding Data: Rows
7. Understand and Use Functions
8. Understand and Use Formulas