DAY 8 EXCEL CHAPTER 5 RAHUL KAVI Rahul

DAY 8: EXCEL CHAPTER 5 RAHUL KAVI Rahul. Kavi@mail. wvu. edu September 12, 2013 1

LAST CLASS • • Charts Printing Charts Sparklines Trendlines 2

WORKING WITH LARGE DATASETS • • • Freezing Rows and Columns Printing Tables Sorting Filters Conditional Formatting 3

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 4

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 5

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 6

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. 7

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

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 Table Row Below • To delete a record, right click a cell in the record – Delete->Table Rows – Or select the cell, Home->Delete Table Rows 9

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 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 Table Columns 10

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 11

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 – Table. Name[@Field Name] 12

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 13

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 14

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 15

MANAGING CONDITIONAL FORMATTING RULES • Home->Conditional Formatting->Manage Rules • Can edit, delete, or create new rules 16

NEXT CLASS • • Outlines Grouping Pivot. Tables Pivot. Charts 17