Instructions:
Download the attached data file TalkWell.xlsx then, save it as TalkWellmobilePhones.
Make sure you know the location where you are placing your saved file before you continue with step 1.
A) Documentation worksheet:
This workbook has two worksheets. As an analogy, a workbook is like a regular paper book and each worksheet are like each of the pages of that book. In this workbook the first worksheet is “Documentation” and it is open by default. Enter your name in cell B3 and the date in cell B4.
Set the Fill Color for all the cells in the worksheet to Blue, Accent 1, Lighter 60% theme color. (Hint: Use the “Select All” button located in the upper left area of the worksheet; intersection of columns and rows headings.)
For the range B3:B5, set the background color to white and add all borders around each cell in the range.
For cell A1, change the font to Cambria, the Headings font of the Office theme, change the font size 28 points, change the font color to white, and then bold the text.
For the range A3:A5, change the font size to 14 points, change the font color to white, and then bold the text.
B) Mobile Phone Sales worksheet:
In the lower left area of the workbook, locate and click the “Mobile Phone Sales” tab to view that worksheet (it is like changing pages), enter formulas with the SUM function to calculate the total sales for each month and region for the three models of mobile phones.
Merge and center the range A1:H1, apply the Title cell style to the merged cell, and then increase the font size to 26 points.
Merge and Center the range A2:H2, apply the Heading 4 cell style to the merged cell, and then increase the font size to 16 points.
Merge and Center the range A3:A16, set the alignment to Middle Align, apply the Accent1 cell style, increase the font size to 16 points, bold the text, and then wrap the text in the cell.
Use the Format Painter to copy the format of merged cell A3 to the ranges A18:A31 and A33:A46.
Center the text in the range B3:H3.
Increase the indent of the text in the range B4:B15 by one character.
Format the range C4:H16 to include thousands separator (,) and no decimal places.
Format the range B3:H16 as a table with the Table Style Medium 2 table style. Display the header row, first column, and last column. (Hint: First create the table, then apply the Table style).
In the range B16:H16, change the fill color to standard yellow.
Use the Format Painter to copy the formats in the range B3:H16 to the range B18:H31 and the range B33:H46 to format the other two tables in the worksheet.
Use conditional formatting to highlight the top 10 items in the non-adjacent ranges C4:G15; C19:G30; C34:G45 with a red border. (hint: Select all the non-adjacent ranges first, then apply the conditional formatting.)
View the “Mobile Phone Sales” worksheet in Page Layout view, then click Normal view.
Click the Page Layout tab and set the margins to Wide and the page orientation to Landscape.
Note: You only must end with 10 highlighted cells in the whole worksheet by selecting all required ranges at the same time, NO range by range! (Hint: press Ctrl key and hold it down to select Non-adjacent ranges, if you are suing a Mac use the Command key).
In case you need help with any step, contact me immediately. Please do not procrastinate because we will need to move on to the next class Unit at the scheduled time.
This assignment is due this Sunday, September 20th at midnight; please remember that 10 points per day will be deducted for late submissions regardless of the reason for that and after three days this assignment will no longer be accepted.
