MOS EXCEL - VIP 01

In cell C2 of the “Shirt Orders” worksheet, enter a formula that returns the total cost of all “Blue” shirts even if rows are added or their order is changed.

• COUNT
• COUNTIF
• SUM
• SUMIF

In cell C3 of the “Shirt Orders” worksheet, enter a formula that returns the total quantity of size “Large” shirts sold even if the order of the rows is changed.

• COUNT
• COUNTIF
• SUM
• SUMIF

On the “Shirts orders” worksheet, add a subtotal to the shirt order list that displays the number of each shirt color ordered below the data in the “Shirt Color” column. Insert a page break between shirt colors. A Grand Count should be displayed in cell D201.

• HOME
• INSERT
• FORMULA
• DATA

In the “Shirt Color” column of the “Shirt Orders” worksheet, replace all instances of the color “Amber” with “Gold”.

• INSERT
• FORMULAS
• PAGE LAYOUT
• HOME

Display the “Attendees” worksheet in Page Layout view. Then insert a page break so that attendees with a value of “Y” in the “Confirmed?” column are shown on the first page.

• HOME
• INSERT
• PAGE LAYOUT
• VIEW

On the “Sales by Product” worksheet, modify the “Q1 Sales” chart so that it displays the months on the x-axis and the total sales on the y-axis.

• Chart - Switch Move Chart
• Chart - Change Chart Type
• Chart - Select Data
• Chart - Switch Row/Column

Move the “Total Sales by Month” chart to its own chart sheet named “Total Sales by Month”.

• Chart - Switch Move Chart
• Chart - Change Chart Type
• Chart - Select Data
• Chart - Switch Row/Column

On the "Services" worksheet configure the "Description" column so that entries wider than the column wrap to multiple lines.

• Insert - Wrap Text
• Page Layout - Wrap Text
• Formulas - Wrap Text
• Home - Wrap Text

On the "Quotes" worksheet remove the table functionality from the table Retain the font and cell formatting.

• Design (Table tools) - Remove Duplicates
• Design (Table tools) - Resize Table
• Design (Table tools) - Insert Slicer
• Design (Table tools) - Convert to Range

In cell F3 of the "Quotes" worksheet import the Clientcontacts.txt file as a tab-delimited file that has headers Accept all other default values

• Page Layout - From Text
• Formulas - From Text
• Home - From Text
• Data- From Text