Day 8: Excel Chapter 5

1. Day 8:Excel Chapter 5 RAHUL KAVIRahul.Kavi@mail.wvu.edu September 12, 2013

2. Last class • Charts • Printing Charts • Sparklines • Trendlines

3. Working with large Datasets • Freezing Rows and Columns • Printing • Tables • Sorting • Filters • Conditional Formatting

4. Freezing rows and columns • View->Freeze Panes • Freeze Top Row • Freeze First Column • Freeze Panes • Freezes all rows and columns above and to the left of the selected cell

5. Printing • Page Breaks • Page Layout->Breaks • Print Area • Page Layout->Print Area • Print Titles • Select rows and columns to print on every page. • Usually this is used for data labels • Page Order • Down, then over • Over, then down

6. tables • Tables extend the capabilities of a range of data • Column headings stay onscreen without needing “Freeze Panes” • Sorting/Filtering of data • Table Styles • Total Row • Structured References

7. Some terminology • Record • A record is a collection of data about one entity. Each row in a table is one record. • Field • A field is an individual piece of data. Each column specifies a field.

8. Creating tables • Click a cell within the existing range of data, then Insert->Table • Verify the range, click Ok • Name Table • Choose Style

9. Working with Records • Records are rows • To add a record right click a cell in the row below where you want your new record • Insert->Tables Rows Above • To add a record to the end of the table, click a cell in the last row. • Home->Insert->Insert Table Row Below • To delete a record, right click a cell in the record • Delete->Table Rows • Or select the cell, Home->Delete->Delete Table Rows

10. Working with Fields • Fields are columns • To add a field right click on a cell in the column to the right of where you want your new record • Insert->Table Columns to the Left • To add a field to the right of the table, select a cell in the last column • Home->Insert->Insert Table Column to the Right • To delete a field, right click a cell in the column • Delete->Table Columns • Or select the cell, Home->Delete->Delete Table Columns

11. Sorting and filtering • Each field name has a sorting and filtering dropdown • The Sort Dialog Box can be used to sort by multiple fields • Data->Sort • Filtering can be turned on and off • Data->Filter

12. Structured references • Within a table, you may use the field names as references • Names go in brackets • @ indicates the current record • The table name is optional within the table, but required outside the table • TableName[@Field Name]

13. Total Row • Table Tools->Design->Total Row • The function used to calculate the total can be chosen via the dropdown • Additional totals can be added for other columns

14. Conditional formatting • Like the IF function • If the condition is true, the formatting is applied • If the condition is false, the formatting is not applied • Rules • Highlight Cells • Top/Bottom • Data Bars • Color Scales • Icon Sets

15. Applying conditional formatting • Home->Conditional Formatting • Highlight Cells applies text and fill colors based on condition • Top/Bottom applies text and fill colors based on top/bottom percentage or top/bottom number of items • Data Bars, Color Scales, and Icon Sets • Helps visualize differences between data

16. Managing Conditional Formatting rules • Home->Conditional Formatting->Manage Rules • Can edit, delete, or create new rules

17. Next Class • Outlines • Grouping • PivotTables • PivotCharts