Skip to main content

Excel Chapter 4

Chapter Demonstrable Skills

After completing the Excel Chapter 4 portion of the Excel & Data Analysis unit, students will be able to:

Demonstrable Skill Learning Activities
& Informal Assessments
Formal Assessments
(excluding Exams)
Freeze panes, rows, and columns on a worksheet.
  • MyLab Lesson A
  • MyLab Lesson A
Format print-outs by specifying print areas, headers, and footers.
  • MyLab Lesson A
  • MyLab Lesson A
Create Excel tables to organize data.
  • Excel: Tables Participation Project
  • MyLab Lesson A
  • Homework #1
  • Homework #2
  • Homework #3
  • MyLab Lesson A
  • MyLab Bonus Project #1
Sort and filter table data.
  • Excel: Tables Participation Project
  • MyLab Lesson A
  • MyLab Lesson A
Remove duplicate table rows.
  • MyLab Lesson B
  • MyLab Lesson B
Apply styles and formatting options to tables.
  • Excel: Tables Participation Project
  • MyLab Lesson A
  • Homework #3
  • MyLab Lesson A
  • MyLab Bonus Project #1
Insert and use a total row to display statistics.
  • Excel: Tables Participation Project
  • MyLab Lesson A
  • Homework #1
  • MyLab Lesson A
  • MyLab Bonus Project #1
Apply conditional formatting using highlight cell rules.
  • Excel: Formatting Participation Project
  • MyLab Lesson A
  • Homework #1
  • MyLab Lesson A
Use color scales and data bars to illustrate data.
  • Excel: Formatting Participation Project
  • MyLab Lesson A
  • Homework #2
  • MyLab Lesson A
Use structured cell references.
  • Excel: Formulas & Functions I Participation Project
  • MyLab Lesson A
  • Homework #1
  • Homework #2
  • MyLab Lesson A

Chapter Notes

PivotTables

In this chapter, we'll look at PivotTables. These are helpful for making sense of large datasets [1].

Freeze Panes

To freeze one or more rows and columns, use the Freeze Panes option.  Before selecting the option, make the active cell one row below and one column to the right of the rows and columns you wish to freeze.To unlock the frozen rows and columns, click Freeze Panes in the Window group of the View tab and select Unfreeze Panes [1].

Tables

A table is an area in the worksheet that contains rows and columns of related data formatted to enable data management and analysis [1]. Tables offer many features not available to simple data ranges.

Column headings remain onscreen without need for freezing. Filter drop-down lists permit the table to be filtered to show only a subset of that data satisfying certain conditions.

Tables will be the first place we come across the idea of fields and records. Fields are individual bits of information (one cell) and records are several fields that are related (a row).

Conditional formatting

Another way of working with large datasets and making sense of them is to use conditional formatting.

For example, you might want to isolate all of the students in a 200 seat section that have less than a 70% average. Conditional formatting is a feature of Excel which allows you to apply a format to a cell or a range of cells based on certain criteria. You can find the option in Conditional Formatting in the Styles group on the Home tab [1].

Tips & Tricks

Table formatting

In the homework, you're often asked to format a table [2].  Make sure you don't confuse formatting a table and formatting a cell.  To format as a table, select the range you want to format as a table (highlight it) and then from the home tab in the ribbon, chose format as a table.  There are many different options - if you're asked to use a specific one in the homework, be sure to chose the correct one.

Filtering

Filtering is very handy when working with a table; to filter records by a particular field, click the column’s filter arrow and select the values of interest. Records that match the values in the filter display, and other records are hidden from view [1].

Conditional formatting

Be sure to look through all the different ways of applying conditional formatting from the ribbon [2]. Sometimes the easiest thing to do if you're asked to apply conditional formatting using a formula is to use a wizard (choose highlight cell rules, great than), and then edit the rule under the Conditional Formatting icon in the ribbon.  It's often faster than writing your own formula.

References

  1. A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 1. Upper Saddle River, NJ: Pearson Education, 2013.
  2. R. G. Phipps and B. M. Powell, “Excel Chapter 4,” Computer Science 101, May 18, 2016. Available: http://cs101.wvu.edu/materials/units/excel-data-analysis/excel-chapter-4/.