Description
1. Use a PivotTable with Count and the “Show values as % of row [or column] total” option to create a column chart to compare the % of men / women within each of the 5 job levels. Format the chart appropriately so that it could be dropped directly into a report. 2. Use a PivotTable with Average and grouped Row Labels to create a line chart showing the average # of previous employers at time of hire for current employees who were hired over the past 10 years of hire dates (2010 to 2019, by year). Add data labels and format. 3. Use a PivotTable with Average to create a table showing the average monthly income for men vs. women within each of the titled job positions. Sort this table by the overall average for each position, and create a horizontal bar chart. Add data labels and format. (You might want to try copy-pasting this PivotTable, then using the “Show values as % Difference from…” option with “Gender” as the “Base Field” and “Male” as the “Base Item” to show the pay differentials as a % difference across gender categories.) 4. Use a PivotTable with Count and a hierarchical / nested Row Label structure + the “Show values as % of Parent Row” option to create the data for two pie charts comparing the distribution of work-life satisfaction responses across employees with and without required overtime. Create the two pie charts based on the response percentages for these two employee groups. Make sure to arrange the 4 different response options in the correct order, and make sure to copy-paste or use a cell reference “pointer” formula to get the data out of the PivotTable first. 5. Create a scatterplot showing the relationship between Years In Position (X axis) and Last Performance Rating (Y axis) and a second scatterplot showing the relationship between Last Performance Rating (X axis) and Last Raise Amount (Y rating). Add axis titles and format.
