Excel easy project
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.
Documentation
TalkWell Mobile Phones
Author:
Date:
Purpose: To report the annual sales of TalkWell mobile phones
Mobile Phone Sales
TalkWell Mobile Phones
2019 Sales Report
Saveur phone Month Region 1 Region 2 Region 3 Region 4 Region 5 Total
Jan 1996 2877 1599 5017 2075
Feb 1888 3769 1207 4033 1919
Mar 1813 2187 1680 4805 2065
Apr 1303 2602 1263 3846 2093
May 1876 2436 1137 3437 2115
Jun 1225 2187 1326 5993 2002
Jul 2492 2773 2138 3825 2586
Aug 2245 3010 1144 4438 1650
Sep 1947 3204 658 5386 1886
Oct 2318 3442 1317 4277 2100
Nov 1135 2905 1435 5629 2224
Dec 1872 2082 1109 3354 2240
Total
Elan smartphone Month Region 1 Region 2 Region 3 Region 4 Region 5 Total
Jan 1358 1974 1162 4604 1316
Feb 1767 2017 1141 3416 1744
Mar 1240 2276 1093 3500 1547
Apr 1541 2061 1286 3585 1648
May 1681 2360 1169 4751 1774
Jun 1785 2290 1116 4147 1410
Jul 1401 2070 1207 3986 1666
Aug 1352 2208 920 4284 1491
Sep 1389 2026 1197 4369 1716
Oct 1408 1897 1144 4163 1874
Nov 1298 2063 1015 4387 1884
Dec 1508 2415 1009 4420 1935
Total
Elan (no camera) Month Region 1 Region 2 Region 3 Region 4 Region 5 Total
Jan 514 1312 507 2903 1060
Feb 798 1494 427 3099 754
Mar 757 1492 535 2633 885
Apr 850 1618 500 2807 1221
May 855 1667 579 2786 976
Jun 763 1352 561 3176 1144
Jul 796 1274 561 2707 1135
Aug 819 1153 565 2859 1128
Sep 703 1400 593 2838 1240
Oct 824 1387 533 3102 1151
Nov 973 1519 750 2231 1067
Dec 635 1417 378 2817 1029
Total