MOS19_XL_Expert_PROJ2_Formats Project Description: In this project, you will manage and format data to meet objectives 2.1 through 2.3 of the Excel MOS Expert exam. To complete the project, you will...

1 answer below »


MOS19_XL_Expert_PROJ2_Formats






Project Description:


In this project, you will manage and format data to meet objectives 2.1 through 2.3 of the Excel MOS Expert exam. To complete the project, you will fill cells based on existing data 2.1, format and validate data 2.2, and apply advanced conditional formatting and filtering 2.3. Note: This project was created for PC users since the MOS Exam is completed in the PC user environment.




Steps to Perform:






























































































Step




Instructions




Points Possible



1



Start Excel. Download and open the file named
Student_MOS19_XL_Expert_PROJ2_Formats.xlsx.
Save the file as
Last_First_MOS19_XL_Expert_PROJ2_Formats.



0



2



On the Summary worksheet, create a custom number format in cell B2 which shows the two-digit month, two-digit date, and four-digit year. Include dashes before and after the date and forward slashes after month and date.



6



3



On the Mon worksheet create a validation rule for the Commission data. Input message Title is
Commission, and
Only enter Yes in this column if there is a Commission.
should display as the message.



6



4



On the Mon worksheet apply a new conditional formatting rule that will apply formatting to the lowest 15% bookings. Format the cells with a Red fill and Bold.



5



5



On the Mon worksheet edit the conditional formatting rule for the sales data. Format the values that are above average using Green fill.



5



6



On the Tues worksheet use a formula to apply conditional formatting to cell D5 if the value is greater than or equal to
5000. Apply Light Green fill and Bold formatting to cells that meet this criteria. Use the Format Painter to copy the conditional formatting rule down through D60.



10



7



Apply the Facet workbook theme, Blue Warm colors, and Arial font. Save the theme as
Bookings.



6



8



Create a cell style named
Daily Total. Apply a Thick Outline Black, Text 1 border, Bold and Black, Text 1 font, with a custom Fill color, Red
250, Green
245, and Blue
240. Apply the Daily total cell style to C61 in Mon, Tues, Wed, and Thurs worksheets. Save the file.



8



9



Add the Spanish (Spain) language and ensure it is enabled in the workbook. Close the file as directed and then reopen the file. Apply the English (United Kingdom) Accounting format to the sales data and total in the Wed worksheet.



10



10



Edit the custom theme Bookings. Apply the Arial Black heading font, and name the theme font
Bookings.



5



11



On the Thurs worksheet record a macro named
Conditional
that will apply the same conditional formatting rule from step 4 to the Bookings data and step 5 to the Sales data. View the code in VBE, delete any blank line or lines with (‘). Copy the code and then paste the code in the Macro worksheet in cell B1.



10



12



In cells J2:K3 of the Macro worksheet insert a Button (Form Control) and link the Conditional macro. Rename the button
Conditional.



10



13



On the Macro worksheet, in column A fill the series down to row 29 using step value 1 and stop value 29.



5



14



On the Friday worksheet, convert the table to a range. In C5 type
Tip, and then in C6 type
Tim. Use Flash Fill to fill in the remaining First names to fix the inconsistencies in cell alignment. Delete column B, and then use the Format Painter to copy A4 to B4. Save the file.



5



15



On the Fri worksheet, select A4, remove duplicate records by searching the Last column and selecting the data has headers. Click to remove the records. Sort the records in Ascending order by Location. Group the data rows and then for each Location, show a subtotal for sales.



9



16



Ensure the worksheets are in the following order: Summary, Mon, Tues, Wed, Thurs, Fri, Macro. Save the file as a Macro-free workbook and close
Last_First_MOS19_XL_Expert_PROJ2_Formats.xlsx. Exit Excel. Submit the file as directed.



0












Total Points




100



Answered Same DayJul 30, 2021

Answer To: MOS19_XL_Expert_PROJ2_Formats Project Description: In this project, you will manage and format...

Ali Asgar answered on Jul 31 2021
140 Votes
Gaining Proficiency Instructions
Grader - Instructions    Excel 2019 Project
MOS19_XL_Expert_PROJ2_Formats
Project Descr
iption:
In this project, you will manage and format data to meet objectives 2.1 through 2.3 of the Excel MOS Expert exam. To complete the project, you will fill cells based on existing data 2.1, format and validate data 2.2, and apply advanced conditional formatting and filtering 2.3.
Note: This project was created for PC users since the MOS Exam is completed in the PC user environment.
Steps to Perform:
    Step
    Instructions
    Points Possible
    1
    Start Excel. Download and open the file named Student_MOS19_XL_Expert_PROJ2_Formats.xlsx. Save the file as Last_First_MOS19_XL_Expert_PROJ2_Formats.
    0
    2
    On the Summary worksheet, create a custom number format in cell B2 which shows the two-digit month, two-digit date, and four-digit year. Include dashes before and after the date and forward slashes after month and date.
    6
    3
    On the Mon worksheet create a validation rule for the Commission data. Input message Title is Commission, and Only enter Yes in this column if there is a Commission. should display as the message.
    6
    4
    On the Mon worksheet apply...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here