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
|