Tasks: In the Sales-Data worksheet: 1. Break the data in the Salesperson column into two columns: First Name and Lost Name. (Use the Text to Columns feature in the Data tab). Make sure that you also type the column labels First Name and Last Name. 1. Add a validation to the country column so the only valid entries include England, France or Germany. The following criteria should be considered while applying the validation rule: a) Apply the validation rule to the entire column (original values in the column should be preserved). b) Add a user-friendly Error Alert (notifying users in case they try to enter an invalid value for country) c) Add an input message to inform users which countries are valid. 3. Format the worksheet to make it aesthetically pleasing and include measurement units (eg. $) 4. Sort the data by country and by last name of salesperson within each country. 5. Create subtotals by Country showing: a) Totals for Salary and Sales b) Averages for Salary and Sales Hints: You should try to solve the problems without these hints, but it needed, click this box and change its background color to reveal some hints To split the salesperson column, explore & use the "Text-to-Columns" feature under Data tab => Data Tools. Use the "Delimited" option (with a space as the delimiter). Before creating the validation rule, copy the original values from the country column into a temporary column (anywhere in the worksheet). After the validation rule has been applied to the country column, you can copy the data back into it. The Error Alert and Input Messages can be specified in the Data Validation dialog box while specifying the source of the list of valid values. SSSR 1 Annual Sales - 2012 Country Salary Sales England #### ##### Englad England ## # # England England England ##### England ### France France France France France France France # # # # # # 3 Salesperson 4 Alan Shearer 5 Bobby Charlton 6 David Beckham 7 Gary Lineker 8 Gordon Banks 9 Kevin Keegan 10 Michael Owen 11 David Trezeguet 12 Didier Deschamps 13 Eric Cantona 14 Jean-Pierre Papin | 15 Just Fontaine | 16 Lilian Thuram 17 Marcel Desailly 18. Marius Tresor 19 Michel Platini 20 Patrick Vieira 21 Raymond Kopa 22 Robert Pires 23 Thierry Henry = 24 Zinedine Zidane 25 Franz Beckenbauer 26 Gerd Mueller 27 Jurgen Klinsmann 28 Karl-Heinz Rummenigge 29 Lothar Matthaeus 30 Michael Ballack 31 Oliver Kahn 32 Paul Breitner 33 Sepp Maier 34 Uwe Seeler # # # # # # # # # # # # # # # # France PP ##### # ###### #### # # # # ##### ### # France France France : France France France Germany Germany Germany Germany Germany Germany Germany Germany Germany Germany E_ _ # ## # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #### # # ###### ###### 35 Tasks: In the Sales-Data worksheet: 1. Break the data in the Salesperson column into two columns: First Name and Lost Name. (Use the Text to Columns feature in the Data tab). Make sure that you also type the column labels First Name and Last Name. 1. Add a validation to the country column so the only valid entries include England, France or Germany. The following criteria should be considered while applying the validation rule: a) Apply the validation rule to the entire column (original values in the column should be preserved). b) Add a user-friendly Error Alert (notifying users in case they try to enter an invalid value for country) c) Add an input message to inform users which countries are valid. 3. Format the worksheet to make it aesthetically pleasing and include measurement units (eg. $) 4. Sort the data by country and by last name of salesperson within each country. 5. Create subtotals by Country showing: a) Totals for Salary and Sales b) Averages for Salary and Sales Hints: You should try to solve the problems without these hints, but it needed, click this box and change its background color to reveal some hints To split the salesperson column, explore & use the "Text-to-Columns" feature under Data tab => Data Tools. Use the "Delimited" option (with a space as the delimiter). Before creating the validation rule, copy the original values from the country column into a temporary column (anywhere in the worksheet). After the validation rule has been applied to the country column, you can copy the data back into it. The Error Alert and Input Messages can be specified in the Data Validation dialog box while specifying the source of the list of valid values. SSSR 1 Annual Sales - 2012 Country Salary Sales England #### ##### Englad England ## # # England England England ##### England ### France France France France France France France # # # # # # 3 Salesperson 4 Alan Shearer 5 Bobby Charlton 6 David Beckham 7 Gary Lineker 8 Gordon Banks 9 Kevin Keegan 10 Michael Owen 11 David Trezeguet 12 Didier Deschamps 13 Eric Cantona 14 Jean-Pierre Papin | 15 Just Fontaine | 16 Lilian Thuram 17 Marcel Desailly 18. Marius Tresor 19 Michel Platini 20 Patrick Vieira 21 Raymond Kopa 22 Robert Pires 23 Thierry Henry = 24 Zinedine Zidane 25 Franz Beckenbauer 26 Gerd Mueller 27 Jurgen Klinsmann 28 Karl-Heinz Rummenigge 29 Lothar Matthaeus 30 Michael Ballack 31 Oliver Kahn 32 Paul Breitner 33 Sepp Maier 34 Uwe Seeler # # # # # # # # # # # # # # # # France PP ##### # ###### #### # # # # ##### ### # France France France : France France France Germany Germany Germany Germany Germany Germany Germany Germany Germany Germany E_ _ # ## # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #### # # ###### ###### 35