MS Excel Associate – MO 200 Part 3

In the Annual Sales worksheet, insert a 2-D Clustered Column chart based on the data in cell range A5:E15. Include the column headings and position the chart below the table.

Change the Chart title to Annual Sales.

In the June worksheet, define the name Fiction12 for the cell range B4:B33.

In the Totals worksheet enter a formula in cell B2 that displays the SUM of the Fiction12 named range.

In the Source Data worksheet Sortthe Data alphabetically by CustomerID and then by Product.

In the Source Data worksheet, set the print settings to repeat row 1 at the top.

In the June worksheet, in Cell E34, enter a formula that returns the AVERAGE value of all the numbers in the range B4:D33.

Filter the data so that only rows with a value that is Above Average are displayed in the Fiction column.

Create a hyperlink in the cell B12 that links to the file EXCEL07.xlsx.in the GMetrixTemplates folder.

Change the view to Page Layout.

Change the view to Page Layout.

Change the margins to the Narrow setting.

In the Annual Sales worksheet, copy cells A7:F7 and paste it into cells A19:F19.

In the Quarterly Sales worksheet, cell B9, enter a formula that consists of a cell reference to the Annual Sales worksheet, cell F16, multiplied by 1plus the growth value in the Quarterly Sales worksheet, cell B3.

Inspect the current workbook for hidden properties such as Author. Remove any of these properties that are found and return to the document.

Change the options on this worksheet so that Gridlines are visible.

Move the entire Loan Amortization Schedule worksheet in the EXCEL07.xlsx workbook located in the GMetrixTemplates folder and insert it after the Time Card worksheet in the EXCEL06.xlsx workbook.

In the Time Card worksheet, apply a red solid fill data bar conditional formatting to the cells D21:H27.

In the Time Card worksheet, add a header that uses the Current Date format in the left header field and Page Number in the right header field. Add the Sheet Name to the left footer field.

In the Time Card sheet, Cell D30, change the formula to get the SUM of cells D21:D27 and multiply the result by cell D29.

In the Excel Options change the Formulas to not Enable background error checking.

Set the Page margins to the following specifications: Top & Bottom: 2“(5.08 cm) and Left & Right: 1” (2.54cm).

In Excel Options, change the Save AutoRecover information to every 5 minutes.

Freeze the Top Row of the Loan Amortization Schedule worksheet.

Set a Print Area for cells A1:J13.

Modify the Title style to use 20 pts.

Color each of the worksheet tabs: Expenses, Income and Profit – Loss Summary to have a different color.

Create a PDF of the workbook called FusionTomo.pdf, and save it in your GMetrixTemplates folder. Do not open the file after publishing.

In the Marketing Budget Plan worksheet, edit the cell range reference Research; have it only refer to the Research types under the heading.

In the Budget Plan Chartworksheet, create a reference in cell E42 to EventTotal, cell F42 to ProTotal and G42 to AdTotal.

In the Budget Plan Chart worksheet, Change the Workbook View to Page Break Preview.

In the Budget Plan Chart worksheet, apply a Round Bevel (or Circle) to the illustration.

In the Budget Plan Chart worksheet insert the values from the chart into the Budget row of the table below.

In the Marketing Budget Plan worksheet, remove the hyperlink attached to the title Fusion Tomo Marketing Budget Plan.

In the Marketing Budget Plan worksheet, unmerge cells A97:C97.