Creating and Formatting Excel Worksheet with Sales Report

Entering Data

1. Entering Data

a)Start Excel and open a new blank workbook. Save the file as Lastname_Firstname_Excel_Exam.

b)Enter title in cell A1: Summer Sales for Business Unit

c)Enter subtitle in cell A2: Report Date - September 5

d)In cell B4 enter May, then use fill handle to add June, July and August in cells C4 to E4.

e)In cell F4 enter Total, in cell G4 enter Bonus, in cell A11 enter Total.

f)Change the width of columns B to G to 90 pixels.

g)In cells A5:A10 enter the following names: Chang, Davis, Eze, Garcia, Patel, Rodgers.

h)Enter the following sales data in cells B5:E10

May June July August

615 1372 1471 1541

655 1045 1223 1307

498 1305 1415 1480

777 1534 1720 1706

610 1111 1374 1252

880 1275 1450 1395

i)Use AutoSum to enter totals in row 11 and column F.

j)Use the IF function (Formulas, Logical) to place “YES” in cells G5:G10 if the total in the corresponding cell in column F is 5000 or more, “NO” otherwise.

k)In cells A13:A16 enter AVERAGE, MEDIAN, HIGH, LOW.

l)Use the proper statistical function (Formulas, More Functions) to calculate the average value for each month in row 13, the median value for each month in row 14, the highest value for each month in row 15, and the lowest value for each month in row 16.

m)Use the Decrease Decimal button to round the values in cells B13:E16 to the nearest integer.

2. Formatting the Worksheet

a)Use Merge & Center to center the title and subtitle across columns A to G.

b)Change the font size in row 1 to 20 points, in row 2 to 16 points.

c)Apply a Heading 3 cell style to cells B4:G4, then center and italicize.

d)Apply a Total cell style to cells B11:F11.

e)Center and italicize cells A13:A16.

f)Conditional formatting: In column G highlight the cells containing “YES” by making them green.

g)Conditional formatting: Place green gradient data bars in cells F5:F10.

3. Pie Chart

a)Select cells A5:A10 and F5:F10. Insert a 3-D Pie Chart based on the selected data.

b)Move the chart to a new worksheet and change the chart title to Summer Sales.

c)Change the chart title font size to 28 points and hide the chart legend.

d)Use Format Data Labels to show the Category Name and Percentage; center the labels.

e)Make each data label 14 point font size and bold.

f)Use Format Data Series to give the chart a round/circular bevel at the top and bottom, with a shadow below. Set the bevel width and height to 512 points all around.

4. Finishing Touches

a)Change the name of the data worksheet to Bonus Report, and the tab color to gold.

b)Change the name of the chart worksheet to Sales Chart, and the tab color to blue.

c)Move the Bonus Report worksheet before the Sales Chart worksheet.

d)Ensure that the file name appears in the footer in print mode.

e)Save and submit the xlsx file.