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. |
|
|
| Format print-outs by specifying print areas, headers, and footers. |
|
|
| Create Excel tables to organize data. |
|
|
| Sort and filter table data. |
|
|
| Remove duplicate table rows. |
|
|
| Apply styles and formatting options to tables. |
|
|
| Insert and use a total row to display statistics. |
|
|
| Apply conditional formatting using highlight cell rules. |
|
|
| Use color scales and data bars to illustrate data. |
|
|
| Use structured cell references. |
|
|
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
- 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.
- 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/.