In-Class Excel - Day 6 - Worksheet

Project 1 Questions 1 On the “Computer Sales” worksheet, change the data validation error message for cells C11:N22 to “ 2 3 To see Project Questions on each sheet, expand rows 1-4 On the “Computer Sales” worksheet, in cells O11:O22, create a conditional formatting rule that uses in Yellow, the midpoint in Light Green, and the maximum in Green. On the “PivotTable & PivotChart” worksheet, in the PivotTable, create a calculated field named “Cha from the December data.

“Please enter a number from 0 to 100,000” a 3-Color Scale format style to display the minimum ange” that displays the January sales data subracted

May June Jul Aug Sept Oct Nov Dec $ 27,543 $ 28,765 $ 15,420 $ 20,989 $ 21,345 $ 17,650 $ 16,875 $ 25,080 $ 10,987 $ 10,766 $ 3,456 $ 5,432 $ 10,987 $ 11,098 $ 9,876 $ 10,988 $ 22,356 $ 25,987 $ 14,356 $ 20,987 $ 22,345 $ 16,789 $ 16,543 $ 22,345 $ 25,768 $ 26,123 $ 14,876 $ 22,987 $ 25,876 $ 16,789 $ 15,674 $ 27,098 $ 12,862 $ 11,567 $ 4,567 $ 6,543 $ 11,753 $ 13,678 $ 10,756 $ 12,631 $ 22,689 $ 24,765 $ 15,832 $ 19,753 $ 21,579 $ 15,982 $ 15,985 $ 26,518 $ 24,173 $ 27,345 $ 13,273 $ 21,345 $ 24,098 $ 18,039 $ 17,987 $ 28,756 $ 22,156 $ 24,311 $ 5,432 $ 7,654 $ 21,008 $ 18,654 $ 15,875 $ 12,543 $ 22,345 $ 21,567 $ 10,876 $ 12,567 $ 14,678 $ 16,784 $ 15,467 $ 21,786 $ 33,456 $ 35,213 $ 23,456 $ 33,456 $ 31,234 $ 23,098 $ 21,345 $ 37,890 $ 20,987 $ 25,778 $ 6,432 $ 4,321 $ 16,789 $ 12,654 $ 10,987 $ 13,876 $ 34,876 $ 37,654 $ 22,345 $ 34,567 $ 31,765 $ 21,345 $ 22,345 $ 33,098

Total $ 254,573 $ 114,933 $ 236,903 $ 267,356 $ 132,225 $ 239,176 $ 212,219 $ 170,851 $ 164,279 $ 350,493 $ 207,195 $ 352,100

Notebook Tablet Grand Total Sum of Jan Sum of Dec

Project 2 Questions 1 On the “2016 Office Masters", use an Excel feature to remove the duplicate records from the data 2 4 6 To see Project Questions on each sheet, expand rows 1-6 On the “2016 Office Masters” worksheet, in cells O13:O15, calculate the number of candidates wit listed in the adjacent cells. On the “Salaries” worksheet, create a Funnel chart that displays the “Salaries” data with the Certifi chart. Change the chart title to “Certification Salaries”. The chart size and position do not matter. On the “2016 Office Masters” worksheet, in cells D13:D32, use a function to calculate the weekda Note: The cell is formatted to display the name of the weekday.

To see Project Questions on each sheet, expand rows 1-6 2016 Microsoft Office Mas Lastname Firstname Access Excel Bellows Samual 3/20/2024 X Cruz Jose 3/13/2024 X Doucette Stephen 3/14/2024 X Freedom Liberty 3/13/2024 X Guerrero Tina 3/6/2024 X Kane Thomas 3/7/2024 X Kramer James 3/6/2024 X Moshirfar Majid 2/28/2024 X X Nygen Sing 2/29/2024 X Moshirfar Majid 2/28/2024 X X Nygen Sing 2/29/2024 X Ormesby Gretchen 2/27/2024 Rajar Abdul 2/20/2024 x River Sharon 2/21/2024 x Schmidt Frank 2/20/2024 Smith Gregory 2/13/2024 x Snow Bella 2/14/2024 x Sweet Rachel 2/14/2024 x Thomas Graham 2/7/2024 x Wells Cindy 2/9/2024 Wells Jonathan 2/8/2024 x x Yu Kim 2/14/2024 x x Date of Certification Day of Certification

ster Candidates Excel-Expert Outlook PowerPoint Word Word-Expert Master X X X X X X X X X X X X X X X X X X X X X X X X X X x X X x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x x

To see Project Questions on each sheet, expand rows 1-6 Average Salary by Microsoft Certification Symbol Name Salary MCA Microsoft Certified Architect $98,349 MCAD Microsoft Certified Application Developer $93,349 MCDBA Microsoft Certified Database Administrator $95,950 MCDST Microsoft Certified Desktop Support Technician $61,750 MCITP Microsoft Certified IT Professional $82,300 MCSA Microsoft Certified Solutions Associate $73,149 MCSA Microsoft Certified Systems Administrator $77,100 MCSD Microsoft Certified Solutions Developer $97,849 MCSE Microsoft Certified Systems Engineer $88,149 MCSE Microsoft Certified Solutions Engineer $94,549 MCT Microsoft Certified Trainer $89,949 MCTS Microsoft Certified Technology Specialist $79,649 MOS Microsoft Office Specialist $62,849 MTA Microsoft Technology Associate $57,599

Project 3 Questions Configure Excel to automatically evaluate formulas only when a workbook is saved, and not each time data On the “Managers Salaries” worksheet, modify the conditional formatting rule applied to cells G10:G16 so th Italic font and no fill color to cells greater than 10,000. On the “Manager Salaries” worksheet, use the Data Subtotal feature to calculate the sum of "Commissions" Salaries" based on the change in "Store Locations". On the “Managers Salaries” worksheet, create a chart that displays the “Fresh Foods” as an Area chart, the “ a line chart, and the "Non-Food" as a line chart. Keep all portions of the chart on the same axis and make su Names are displayed on the bottom axis. The chart size and position do not matter. On the “Manager Salaries” worksheet, modify the chart to use Layout 3 and Style 4. Change the chart color Pallet 8 and the chart title to “Grocery Sales”.

changes. hat it applies Bold " and "Month End “Packaged Foods” as ure the Manager rs to Monochromatic

aries Non-Food Total Sales Commission Bonus Earned Month-End Salary $ 93,110 $ 306,710 $ 12,268 Yes $ 12,268 $ 130,220 $ 337,600 $ 13,504 Yes $ 13,504 $ 110,500 $ 270,000 $ 10,800 Yes $ 10,800 $ 101,910 $ 320,880 $ - Yes $ - $ 110,000 $ 307,970 $ 12,319 Yes $ 12,319 $ 37,880 $ 174,960 $ 6,998 No $ 6,998 $ 55,000 $ 203,300 $ 8,132 Yes $ 8,132

Project 4 Questions 1 Configure Excel to disable all macros in the workboo 2 Require that users enter the password “123” before 3 4 5 To see Project Questions on each sheet, expand rows 1-7 On the “Flavor Summary” worksheet, in cells C11:E1 and profit for each flavor based on the data in the "F On the “Flavor Costs” worksheet, use error-checking with those around it. Correct the error. On the “Analysis” worksheet, create a Pie PivotChar display only data for flavors: "Black Bean Chocolate" chart size and position do not matter.