Mastering Excel: Creating and Managing Tables and PivotTables

1. Day 9:Excel Chapter 5 Tazin AfrinTazin.Afrin@mail.wvu.edu February 06, 2014

2. Objectives • Freeze rows and columns • Print large datasets • Understand table design • Create a table • Apply a table style • Sort data

3. Freezing rows and columns • Freezing keeps rows and columns visible during scrolling • View->Freeze Panes

4. Tables • A table is a structured range of related data formatted to enable data management and analysis • Excel tables offer many features not available to regular ranges

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 • Automatic Autofill • Total Row • Structured References

6. Table Design • 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 and fields • Insert rows/columns • Delete rows/columns • Remove duplicate rows • Design -> Remove Duplicate Rows

9. Table Style Options • Design –> table style option group

10. Sorting Data • Sorting arranges records in a table • Sort on one column • Sort on multiple columns • Records can be sorted in ascending or descending order • The Sort Dialog Box can be used to sort by multiple fields • Data->Sort

11. Filtering Data • Filtering is the process of displaying only records that meet specific conditions. • 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

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. Day 9:Excel Chapter 6 Tazin AfrinTazin.Afrin@mail.wvu.edu February 06, 2014

18. Objectives • Group and ungroup data • Subtotal data • Create a PivotTable • Change the values field • Modify a PivotTable • Sort, filter, and slice a PivotTable • Create a calculated field • Format a PivotTable • Create a PivotChart

19. Outlines/GRoups • An outline is a hierarchical structure of data. • Excel will not create an outline or group data if the dataset does not contain a formula or an aggregate function such as SUM or SUBTOTAL.

20. Outlines/GRoups • Grouping is a process of joining related rows or columns of related data. • Groups allow you to hide raw data while you work with the calculations derived from that data • You can group rows or columns • Easily hide and unhide large chunks of data • Data->Group

21. Subtotal Data • Use Subtotal command to insert subtotal rows at each designated field change in a sorted range of data • Allows you to aggregate groups of data using one of several functions • Sum • Average • Variance • Standard Deviation • Min/Max • Count

22. Using group subtotals • Sort the data by the group identifier • Does not work on tables • Data->Subtotal • Select the sorted column for “At each change in:” • Select your aggregation function • Select which columns to subtotal

23. Pivot table • A PivotTable is an organized structure that summarizes large amounts of data. • Insert->PivotTable • Ensure the range is correct • Select target location (new worksheet or existing worksheet)

24. Areas of pivot table

25. Adding rows • Drag field to Row Labels • Or, right click on field name -> Add to … • Check box to add to Values

26. Adding Columns • Drag field to Column Labels • Or, right click on field name -> Add to … • Automatically aggregates with SUM(), but you can choose a different function

27. Modify pivot table • Add and remove fields • Rearrange fields • Refresh

28. Updating Pivot Tables • PivotTables do not automatically update if you change the source data • You can force a refresh via PivotTable Tools->Options->Refresh

29. Pivot table options • Sorting • Filtering • Slicing

30. Pivot table options • Sorting • Click a cell in the column you would like to sort by, then PivotTable Tools->Options->Sort • Filters • Report Filters: drag the field to the “Report Filter” area, select desired values in B1 • Group Filters: Select dropdown arrow on row or column labels, select desired values

31. Pivot table options • Slicers • Slicers provide quick buttons to filter the PivotTable • PivotTable Tools->Options->Insert Slicer

32. Calculated field • A calculated field is a user-defined field that performs a calculation based on other fields in a PivotTable. • It is not found in the original data set • PivotTable Tools->Options->Fields, Items, & Sets->Calculated Field

33. Format a PivotTable • PivotTable Tools->Design • Layouts • Subtotals • Grand Totals • Compact/Outline/Tabular • Styles • Row header • Column header • Banded row/column • Table styles

34. Pivot charts • A PivotChart is a graphical representation of data in a PivotTable. • PivotTable Tools->Options->PivotChart • Chart adjusts to match the PivotTable • Edit from tools • Design , layout, format, analyze

35. Thank You Log Off