Heart Meals Cafe a famous restaurant chain has cafes in Geor

Heart Meals Cafe, a famous restaurant chain, has cafes in Georgia, North Carolina, and South Carolina. As an employee in the home office, you are asked to create some reports about the employees. You will need the worksheet Q1-Employees for this set of questions. This worksheet contains the list of all the employees in the company. You will create three reports. Place each report in a separate worksheet, and name the worksheet accordingly (e.g., Q1a, Q1b, and Q1c). Using Subtotals, create a report showing the total salary for each department in each city. Using Advanced-Filter, create a report showing female employees hired in the 1990s, as well as all employees earning more than $30,000 but less than $45,000. Sort the final list by Gender (with female listed first), then by Date of Hire (with the first hired listed first), then by salary (from lowest to highest). Using PivotTable, create a report showing the number of employees hired in each year (row) for each state (column). Show the numbers as percentage of the total number of employees in each state

Solution

a) In Q1a -

1) Select all data.

2) Click on custom Sort

3) sort by Department -> and then by city by adding another level of sort in custom sort from + icon.

4) Seelct all data -> Click subtotal in data tab -> select at each change in city.

b) in Q1b -

1) Select all the data.

2) click on filter.

3) click on date of hire column -> select comparisons as Equals - > year as 1990.

4) Click on salary -> Select comarison as Between -> specify range.

5) Click on sort -> add levels for gender, date of hire and then salary.

c) in Q1c -

1) Click on Insert - > Pivot table

2) Select fields -> Select date of hire and State.

3) Select Values as Count of Employees.