Excel Proficiency Assignment
Use the instructions below to complete the assignment.
Use of Excel for Organizing
Change the name of the “Dataset” worksheet to “Excel Proficiency Exam”
Insert a new column between Column A and Column B
Split the single column A data into two separate columns
Add a new heading name to these columns, “Last Name” and “First Name”
Format Column C, D, E, and G for currency
Format Column F for percentages
Use of Formulas in Excel
Create a formula in Column E to Add Columns C and D
Label Column G “Taxes Paid”
Create a formula in Column G to Multiply Column E and F
Label Column H “Net Pay”
Create a formula in column H to subtract G from E
Insert a new column between Column E and Column F
Label the new column F “Average Daily Wage Rate”
Create a formula in the new column F to divide “Total Weekly Wages” by 7
Merge cells K1 and L1 and name the cell “Employee Averages”
In column K, under the heading, copy the heading titles from C1:I1 to K2:K8. Use a formula to link the cells.
Create a formula in column L that calculates the averages for each matching category in K2:K8
Use of Complex Formulas in Excel
In cell K25, create a heading titled, “Thunder Occurrences”
In cell K26, create a formula that counts the number times “Thunder” occurs in the columns containing “Names”
Use of Excel to Create Graphs
Using data in the average table, create a pie chart that compares the amount of average weekday wages to the average weekend wages
Change the chart title to, “Weekday & Weekend Wage Comparison”
Position the chart so that it is placed between K10-L23
Use of Excel for Aesthetics
Change the font for your entire workbook to 12pt Arial
Widen or shrink all columns to the exact size of the words in the heading
Wrap text in headings with three or more words so that are no more than 3 words per line
Center the entire data set
Sort the entire data set according to last name
Add all borders to the entire data set along with the Employee Averages and Thunder Occurrences data set
Add a thick outside border to the first row of headings including the Employee Averages and Thunder Occurrences headings
Italicize and bold the text in the headings along with the Employee Averages and Thunder Occurrences headings
Add a Light Gray Background to all headings including Employee Averages and Thunder Occurrences
Insert an image of any GCU logo under the number of “Thunder Occurrences”
——-
Assignment on Excel Proficiency
To complete the assignment, follow the steps outlined below.
Excel is used to organize data.
Replace “Dataset” with “Excel Proficiency Exam” on the “Dataset” worksheet.
Between Column A and Column B, add a new column.
Split the single column into two. Data is divided into two columns.
“Last Name” and “First Name” are new heading names for these columns.
For currencies, use Columns C, D, E, and G.
Column F should be formatted for percentages.
In Excel, you may use formulas.
In Column E, provide a formula to add Columns C and D.
Label Column G “Taxes Paid”
Create a formula in Column G to Multiply Column E and F
Label Column H “Net Pay”
Create a formula in column H to subtract G from E
Insert
