MEM Excel Workshop

1. MEM Excel Workshop Advanced SortingPivot Table Introduction 4.5.2012

2. http://faculty.fuqua.duke.edu/~pecklund/MEM2012

4. Our workshop topics Advanced sorting techniques in Excel. An introduction to Pivot Table techniques. Material from INFORMATION MANAGEMENT, DEC483 A Duke MBA elective course. Excel 2010 running in a Windows environment.

5. C O N T E X T

6. What is structured data?

7. Human-friendlyformat. Excel-unfriendlyformat. Unstructured data

8. Human-unfriendly format. Excel-friendly format. Structured data The same data as on the previous slide.

9. A closer look Nothing is assumed!

10. 4 characteristics of Excel structured data • The data is in a contiguous range; there are no entirely blank rows or columns. • First row contains column headers. • Each row is a record describing one item or transaction. (All cells in the row hold info about the same thing.) • Each column holds the same kind of data.

11. Can you recognize Excel structured data? Five questions 

12. 1/5- Structured data?

13. 2/5 - Structured data?

14. 3/5 - Structured data?

15. 4/5 - Structured data?

16. 5/5 - Structured data?

17. Why structure is important Many Excel data management techniques require/look for structured data. Sort Subtotal Autofilter Pivot Table Advanced Filter Database Functions Group

18. Advanced Sorting Techniques in Excel 2010 Workshop topic 1 of 2

19. Excel has two sorting tools

20. There are lots of “sorting facts” Data can be sorted by row as well as by column. Custom sorts can be specified. Excel default sort order is: - smallest to largest numbers - special characters then alpha characters A-Z - FALSE before TRUE - Error values (all are equal) - Blanks With a descending sort, reverse all the above except blanks remain last.

22. Our sample data to sort • About this data: • - 967 records. • All from Arizona. • All fields but “Office Code” are straightforward. Worksheet: Sort Data

23. Region: Sort ascending. Store Status: Sort in the order NEWVEN, then ESTAB, and then OLDESTAB. Day: Sort by cell color (first purple, then blue, and then green). Quantity: Sort by icon set (first yellow, then green, and then red). Task 1: Apply 4 sorts to the data Sort the data using the Sort Dialog in this order: Most important Least important

24. Special: The Day & Quantity columns These two columns are conditionally formatted* to help the user distinguish the cell contents visually. Here: By cell colorand by icon. * Home tab, Styles group, “Conditional Formatting” button

25. For this demo we use Excel’s Sort Dialog When using the Sort dialog, the firstsortdescribed is the dominant sort. As additional sorts are defined in the dialog, their location indicates their importance in the sort hierarchy. The most important sort is defined first and the least important sort is defined last.

26. The first sort: Region We immediatelyencounter a problem. Region (and Store Status) are “hidden” inside theOffice Code field. The 3 elements of data in this column must be separated out before we can sort by region.

27. Data granulation Does each column hold data that is brokendown into its smallest useful component? no The Office Code field holds THREE components.

28. The Office Code field and its 3 elements

29. Parse the REGION variables Use a combination of TEXT category functions from Excel’s function library? Sometimes the only solution. But not this time.

30. We can use an easier method because …the data to be parsed is all in a regular pattern. The regular pattern: There are 3 elements in each cell. Each element is separated from the next by a hyphen.

31. An easy way to parse the Office Code • Insert 2 empty columns to the right of Office Code. • Select all Office Code data values (not the header). • On the Data tab click the Text to Columnsbutton.

32. Text to Columns Wizard

33. Set a hyphen as the delimiter

34. Population Store status Add Population and Store Status headers. Text-to-Columns result Rename Office Code to Region.

35. The Region sort specification Region is the first sort to specify in the “Sort” dialog. Specify it first: Excel will sort it last, after the other three sorts. Region

36. Store status The 2nd sort: Store Status Sort using this sort order: NEWVEN ESTAB OLDESTAB How can a sort in this orderbe accomplished? The order we want is NOT A-Z or Z-A.

37. Store status The 2nd sort (Store Status) Solution Establish a custom sort order: NEWVEN ESTAB OLDESTAB Define an Excel custom list and then specify a sort by this custom list. Click the File tab andopen the Excel options.

38. Choose to “Edit Custom Lists…” General Advanced Edit Custom Lists

39. Enter the new list items

40. Add the 2nd sort to the Sort dialog Select the customlist for this sort.

41. Conditional formatting is setin the “Styles” group on the HOME tab. The third sort: By Day The Day column has conditional formatting applied.

42. Day’s conditional formatting This is rule-based conditional formatting.Use the “Edit Rule…” button to make changes. Use “New Rule…” to add and “Delete Rule…” to delete.

43. Condit. format color assignments The Day falls in the: Last third of the month Middle third First third Use this color sort order: First color - Purple Second color - Blue Third color - Green

44. Add the 3rd sort to the dialog It takes two levels to specify the third sort. BLUE is by default betweenOn Top and On Bottom. Sort in the color order First: Purple Second: Blue Third: Green

45. Add the 4th and final sort: Quantity Quantity is conditionally formatted with an icon set.

47. 2 levels are required for Quantity Icon set sort order First: Yellow Second: Green Third: Red

48. Finally: Execute the sort

49. Sort result A partial view of the sorted data.

50. Advanced sort take aways Make data fields as granular as you need them to be. (TEXT TO COLUMNS) Create your own custom sort orders. Sort by Values, Cell Color, Font Color, or Icon.