step 3-step 11
Excel | Appendix A Working with Text Functions and Creating Custom Formats Case Problem 2 Data File needed for this Case Problem: House.xIsx Tidy House Every two weeks, Tierra Waggoner collects payroll information for the employees who work at her housekeeping business in Terre Haute, Indiana. The worksheet with the information is sent to a payroll service that generates the paychecks. Tierra has started to collect the information, but she needs you to clean up the data before she sends it to the payroll service. Complete the following: 1. Open the House workbook located in the ExcelA > Case2 folder included with your Data Files, and then save the workbook in the Excel Workbook format as Housekeeping in the location specified by your instructor. 2. In the Documentation worksheet, enter your name and the date. 3. In the Employee Hours worksheet, create an Excel table named Employee Tbl with a blue table style. 4. Split the data in the Name column into two columns. Store the first name data in column A and the last name data in column B. Change the column headers to First Name and Last Name, respectively. 5. Split the data in the City, State Zip column into three columns. Store the city data in column D, the state data in column E, and the zip codes in column F. (Hint: First split the data into four columns: City, comma, State, Zip. Then, delete the column with the comma.) Change the column headers to City, State, and Zip, respectively. 6. Use functions as needed to change the data in the City and State columns to use standard capitalization. (Hint: Remember to copy the data as values and remove any unnecessary columns. 7. Apply the Social Security Number format to the data in the SS Number column. 8. Use a function to change the data in the Type of Work column so that it is all lowercase. (Hint: Remember to copy the data as values and remove any unnecessary columns.) 9. Apply a custom format to the data in the Hourly Rate column that shows two decimal places. 10. Apply a custom format to the data in the Overtime Hours column so that full hours display only significant digits and partial hours display two digits whether significant or insignificant. (Hint: Refer to Figure A-11 to see a description of the digit placeholders used in custom formats for values.) 11. Save the workbook, and then close it.Third Street Bird Watchers Author Date Created Purpose Analyzing backyard bird counts in Pueblo, Colorado Data Definition Table Field Description Data Type Notes Watcher First name of watcher Text Brenda, Daniel, Julie, Michael, Nick or Rene Date Date the bird was observedDate Bird Name Common name of bird Text Count Number counted Number Date viewed in MM/DD/YYYY format Number of birds viewed Watcher Date Watcher Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Brenda Daniel Daniel Daniel Daniel Daniel Daniel Daniel Daniel Daniel Daniel Daniel Daniel Daniel Julie Julie Julie Julie Julie Julie Julie Julie Julie Julie Julie Julie Julie Julie Date Bird Name Count Bird Name Count 11/1/2017 Blue Jay 11/1/2017 Canada Geese 11/1/2017 Evening Grosbeak 11/2/2017 Blue Jay 11/2/2017 Bullock's Oriole 11/2/2017 Canada Geese 11/2/2017 Cedar Waxwings 11/2/2017 European Starling 11/2/2017 Evening Grosbeak 11/2/2017 Yellow Warbler 11/3/2017 Blue Jay 11/3/2017 Bullock's Oriole 11/3/2017 European Starling 11/3/2017 Evening Grosbeak 11/3/2017 Yellow Warbler 11/1/2017 Bullock's Oriole 11/1/2017 Canada Geese 11/2/2017 Bullock's Oriole 11/2/2017 Canada Geese 11/2/2017 Cedar Waxwings 11/2/2017 Yellow Warbler 11/3/2017 Blue Jay 11/3/2017 Bullock's Oriole 11/3/2017 Canyon Towhee 11/3/2017 European Starling 11/3/2017 Evening Grosbeak 11/3/2017 Great Blue Heron 11/3/2017 Yellow Warbler 11/1/2017 Field Sparrow 11/1/2017 Northern Shrike 11/1/2017 Red-tailed Hawk 11/1/2017 Ruby-throated Hummi 11/1/2017 White-breasted Nutha 11/1/2017 Wood Duck 11/2/2017 Northern Shrike 11/2/2017 Red-tailed Hawk 11/2/2017 White-breasted Nutha 11/2/2017 Wood Duck 11/3/2017 Field Sparrow 11/3/2017 Great-horned Owl 11/3/2017 Northern Shrike 11/3/2017 Red-tailed Hawk 2 3 1 1 2 2 2 3 1 2 3 1 2 2 1 1 3 2 2 1 1 2 3 2 2 1 1 2 1 1 1 1 1 1 1 1 1 1 1 1 1 1 Julie Julie Julie Julie Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Michael Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Nick Rene Rene Rene 11/3/2017 Ruby-throated Hummi 11/3/2017 White-breasted Nutha 11/3/2017 Wood Duck 11/3/2017 Yellow Warbler 11/1/2017 Canada Geese 11/1/2017 Canyon Towhee 11/1/2017 European Starling 11/1/2017 Great Blue Heron 11/1/2017 Yellow Warbler 11/2/2017 Blue Jay 11/2/2017 Bullock's Oriole 11/2/2017 Canada Geese 11/2/2017 Canyon Towhee 11/2/2017 European Starling 11/2/2017 Great Blue Heron 11/3/2017 Blue Jay 11/3/2017 Bullock's Oriole 11/3/2017 Canada Geese 11/3/2017 Canyon Towhee 11/3/2017 Cedar Waxwings 11/3/2017 European Starling 11/3/2017 Evening Grosbeak 11/3/2017 Yellow Warbler 11/1/2017 Blue Jay 11/1/2017 Bullock's Oriole 11/1/2017 Canada Geese 11/1/2017 Cedar Waxwings 11/1/2017 European Starling 11/1/2017 Great Blue Heron 11/1/2017 Yellow Warbler 11/2/2017 Blue Jay 11/2/2017 Bullock's Oriole 11/2/2017 Canada Geese 11/2/2017 Cedar Waxwings 11/2/2017 Evening Grosbeak 11/2/2017 Great Blue Heron 11/2/2017 Yellow Warbler 11/3/2017 Blue Jay 11/3/2017 Bullock's Oriole 11/3/2017 Canada Geese 11/3/2017 Canyon Towhee 11/3/2017 Cedar Waxwings 11/3/2017 European Starling 11/3/2017 Evening Grosbeak 11/3/2017 Yellow Warbler 11/1/2017 Bullock's Oriole 11/1/2017 European Starling 11/1/2017 Great Blue Heron 1 1 1 1 2 1 5 2 1 4 1 2 1 2 1 1 2 5 1 2 1 2 2 3 1 3 2 3 2 3 2 1 3 1 4 1 2 2 2 1 2 1 3 1 4 1 2 1 Rene Rene Rene Rene Rene Rene Rene Rene Rene Rene 11/2/2017 Blue Jay 11/2/2017 Bullock's Oriole 11/2/2017 Canada Geese 11/2/2017 Canyon Towhee 11/2/2017 Evening Grosbeak 11/2/2017 Great Blue Heron 11/2/2017 Yellow Warbler 11/3/2017 Blue Jay 11/3/2017 European Starling 11/3/2017 Yellow Warbler 2 1 2 1 1 1 4 1 3 1 Date Birds Identified 11/1/2017 11/2/2017 11/3/2017 Birds Identified Brenda Daniel Julie Michael Nick Rene Date 11/1/2017 Total Birds Counted Date 11/2/2017 Date 11/3/2017
