Working with Tables

Introduction:

Week 4 provides an exploration of working with relative, absolute, and 3-D cell references in the formation of functions and formulas. The creation of new worksheets and the cutting and pasting of data from one worksheet to another will also be reviewed. Lastly, you will learn how to format a large data set into a table, filter and sort that data, and determine the best possible choice for use in functions and formulas.

You have been using relative cell references every time you utilized the Auto Fill feature to copy functions and formulas in the first three weeks. Now you will be introduced to absolute references, which keep the cell reference linked to a single cell and its data when you copy and paste functions and formulas. You will also learn how to use cell references between different worksheets. This allows for the creation of summary worksheets that incorporate data from other worksheets in the calculation of data on the summary worksheet.

To this point you have been performing your editing and formatting on individual worksheets. However, Excel provides an amazing option to format and edit multiple worksheets at once when they are grouped. This week you will also learn how to create new worksheets and cut and paste data into them.

Sorting and filtering large data sets in Excel is made easier using tables. Data that are formatted into tables have built-in sorting and filtering options that make finding the data you are looking for easier. Likewise, there is also an option to freeze a pane which allows row(s) and column(s) placement to remain in place while scrolling top to bottom or left to right.

Course Competencies:

1. Understand and describe what Excel is and how it is used to work with quantitative data
2. Design, construct, format, and edit workbooks and worksheets for professional use
3. Create and apply formulas and functions to calculate data using appropriate mathematical principles
4. Construct and design charts and tables to effectively visualize and manipulate data
5. Identify and assess the suitable printing and presentation of workbooks and worksheets
6. Utilize data analysis in determining the best possible outcomes of business-related decisions

Learning Objectives:

Each Outcome shows in parenthesis which Course Competency it aligns with.

1. Reorganize data sets utilizing cut and paste in the creation of new worksheets containing the cut data (2)
2. Group worksheets and administer formatting, editing, and data manipulation to multiple worksheets all at once (2)
3. Apply relative, absolute, and 3-D cell references in the construction of formulas and functions to calculate data (3)
4. Utilize the COUNT function to determine the number of items using numbered data (3)
5. Construct a table from data in a worksheet (4)
6. Demonstrate the use of the filter and sort functions of a table to identify the best choice from the results (4)
7. Improve the presentation of grouped worksheets by determining how to apply headers and footers (5)
8. Review the print previews of grouped worksheets and examine them for inconsistencies and devise how to modify them to print correctly (5)

To-Do List:

1. Week 4 KnowledgePath
2. Week 4 Learning Materials
3. Week 4 Discussion
4. Course Lab Project – Exercise 4

Sources:

Image attribution: Adapted from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.