BUSI 201 Quiz 2 Excel Advanced Tools solutions complete answers

BUSI 201 Quiz 2 Excel Advanced Tools solutions complete answers 

 

Use a custom number filter to show only rows where the quantity ordered (Ordered column) is greater than or equal to 18.

 

Filter the data in place so that only rows where the PO # value is PO1104007 and the Received value is less than 25 are shown. The criteria range has been set up for you in cells A1:G2.

 

Complete the one-variable data table in cells G2:H7 to calculate the total investor payout for varying percentage rates. The formula has been entered for you in cell H2. It references the original investor payout percentage in cell B9. The substitute values have been entered for you in cells G3:G7.

 

Insert a clustered column-line combination chart based on the selected data. The line chart should represent the Total Sales data series. Both data series should be on the primary axis. This is the first chart type in the Combo Chart gallery.

 

Collapse the entire outline to show just the subtotals.

 

Create automatic subtotals to sum the values in the Received column for each change in the Status column. The data have been sorted for you.

 

Modify the chart so the Total data series is plotted along the secondary axis. 

 

Add another data series to the chart to represent the number of items ordered. Use the value of cell B1 as the series name and cells B2:B4 as the series values. Do not rebuild the chart from scratch.

 

Convert the table to a normal range.

 

Insert a recommended PivotTable, using the Sum of Total Spent by State option.

 

Use a custom text filter to show only rows where the item name (Item column) begins with the letter G.

 

In cell A2, use Consolidate to create subtotals using the Sum function for values in the named range ItemsOrdered. Include both the top row and left column labels. The named 

 

range ItemsOrdered has been defined for you. The consolidation should show the subtotals only and not the details.

 

Use a custom date filter to show only rows where the order date (Order Date column) is after 6/30/2017.

 

(the third style from the right in the last row).

 

The August Purchase Orders data point in the Gross Profit data series has been selected. Change the outline width to 6 pt.

 

Insert a PivotChart using the first bar chart type.

 

This worksheet does not include any total rows, and there are blank rows between groups of data. Manually create groups of rows that can be expanded or collapsed as needed. Start with the group of selected cells.

 

The total row for each purchase order uses a SUM function to total the number ordered and the number received. Create an automatic outline from the rows in this data range.

 

Filter the State column so only rows with MD are shown.

 

Add slicers to filter the data in this table by City.

 

Open the Watch Window and create a new watch for the selected cells.

 

Create a new scenario to reflect a change in cell B8 to a value of 0.09 Name the scenario New Bonus.

 

Use a slicer to filter the data in this table to show only rows where the State value is MD.

 

Filter the data to show only cells with the solid green background in the Quantity in Stock column.

 

Sort the data first alphabetically by the values in the Item column and then by the dates in the Date Expected column with the oldest dates first. 

 

Complete the two-variable data table in cells H3:K8. The formula has been entered for you in cell H3. The substitute values in cells I3:K3reference the total expenses value in cell E5, and the substitute values in cells H4:H8 reference the original investor percentage in cell B9.

 

Use Goal Seek to find the value for cell B2 that will result in a value of $200 for cell B6. Accept the solution.

 

Using the Scenario Manager, show the New Bonus scenario. Close the Scenario Manager when you are through.

 

Create a scenario summary report. Accept the recommended results cells.

 

Sort this data by the Visits column so the smallest numbers are listed first.

 

Clear the filter from the State column.

 

Enable filtering.

 

Select the Items Received data series and apply the Intense Effect - Gold, Accent 4 shape style 

The Items Received data series is already selected. Select just the August Purchase Orders data point in the series and change the outline color to Orange, Accent 2 (the fifth color from the right in top row of theme colors).

 

The August Purchase Orders data point has been selected for you. Explode the data point by exactly 45% to move it slightly away from the rest of the pie chart.

 

Install the Solver add-in.

 

Use Solver to find the combination of attendees that will result in the maximum possible profit in cell G1.  Use the range name Attendees as the variable cells. Add these constraints in order: values in the named range Attendees are less than or equal to the values in the named range MaxPerClass; values in the named range Attendees are whole numbers; values in the named range TotalAttendees are less than or equal to the values in the named range MaxAttendees. Run Solver and accept the solution.

 

Create and apply a new conditional formatting rule for the selected cells E2:E26. Apply the default icon set Three traffic lights (unrimmed)icon set, but show only the icon, not the cell value. Change the values so the green circle icon (the first icon) will be applied if the cell value is >= 80 percent and the yellow circle icon (the second icon) will be applied if the cell value is <80 and >=20 percent. Note that the value type is set to percent by default.

 

Add a total row to this table and display the maximum value for the Total Spent column.

Remove duplicate rows where data in all the columns are identical.

 

Sort this data by the Date of Last Visit column so the oldest dates are listed first.

 

Changes have been made to the underlying data for this PivotTable. Refresh the PivotTable data.

Change the data source for this PivotTable to use the table named CustomersTable.

 

Add a calculated field to this PivotTable to calculate the average sales per visit by state (=Sales/Visits). Name the new field Average.

 

Sort this data alphabetically from A to Z by values in the Last column.

 

Filter the chart so the Misc data series is hidden.

Add line Sparklines to cells E3:E12 to represent the values in B3:D12.

 

Convert the cell range to a table using table style Table Style Light 10 (the third style in the second row under the Light styles). The table should include headers.

 

Record a new macro with the name ConvertTable. Do not assign a shortcut key or description. 

 

The macro should convert the table to a range using the command on the Table Tools Design tab, Tools group.

 

The August Purchase Orders data point has been selected for you. Rotate the pie chart exactly 130° so this data point appears at the right side of the chart near the legend.

Change the gridlines to use the Long Dash dash style.

 

Move the selected chart to a new chart sheet.

 

Add a report filter for the State field and use it to filter the PivotTable to show only data where the value of the State field is MD.

 

Add a slicer to the PivotTable for the State field and use the slicer to show only data where the value is VA.

 

Apply the Style 5 Quick Style to the chart.

 

Apply the Cartoon SmartArt style to the diagram. It is the third option in the 3-D section of the gallery.

 

Create and apply a new conditional formatting rule for the selected cells D2:D26 to apply italic font formatting to only cells that are equal to or above the average for the selected range.

 

Apply the Colorful Palette 4 color scheme (the fourth option in the Colorful section) to the chart.

Add a timeline to the PivotTable to filter the data by values in the Date of Last Visit field. Use the timeline to filter the PivotTable to show only dates in September.

 

Apply the Style 3 Quick Style to the PivotChart.

 

Change the chart type to the first 100 % stacked column option (the third option along the top of the right pane).

 

The chart area is selected. Apply the Linear Up gradient fill (the second gradient in the last row of the Light Variations section of the gallery).(the second style from the left in the second row of the Light Variations)

 

Create a chart from the selected cells using the POChart chart template.

 

Import data from the Employees table in the Staff database into a new worksheet.

 

Import data from the text file WageIncreases. Allow Excel to import the data into a table in a new worksheet.

 

Switch the rows and columns in the chart, so the data points are grouped into data series by PO Month.

Add the Developer tab to the Ribbon.

 

Begin recording a new macro.

 

Insert a 3-D pie chart based on the selected data.

 

Insert a Line with Markers chart based on the selected cells.

 

Use Flash Fill to autofill the cells in this column. Start by entering the pattern $5,000 Merit in the cell E2.

 

Split the selected text into columns using the comma character as the delimiter.

 

Apply data validation rules to the selected cells to allow only decimal numbers less than 5.00. 

 

Extend the trendline to forecast forward two periods.

 

Add a Picture Organization Chart SmartArt diagram to the worksheet. It is the second option in the first row of the Hierarchy category.

 

Allow blank cells.

 

In cells D2:D13, use data validation to display an in-cell drop-down list of values from the MedCodes named range. Allow blanks.

 

Add this comment to cell B4: Why did advertising increase?

 

Insert a Organization Chart SmartArt diagram. It is the first option in the first row of the Hierarchy category. Enter Ken Dishner as the text for the top shape. When you are finished, Click outside the diagram.

 

Add a linear trendline to this chart. Use the default trendline formatting.

 

Show markers for just the highest values in this Sparkline group.

 

Add a hyperlink from cell C11 in the Summary worksheet to link to cell A7 in the Downtown worksheet. Don't forget to use A7 as the cell reference.

 

Enable sharing so more than one person at a time can work on this workbook.

 

Protect this worksheet so users can select but not edit locked cells. Users should be able to select and edit any cells that are unlocked.

 

Click any of the data markers to select the entire Items Ordered data series.

 

Insert a Clustered Column chart based on the first recommended chart type.

 

Insert a 3-D Clustered Bar chart (the first chart type in the 3-D Bar section of the Insert Column or Bar Chart menu).

 

Unlock the cells B3:D8 so the user can edit the cells when the worksheet is protected.

 

Open the Compatibility Checker to check if this workbook contains elements that are not compatible with earlier versions of Excel. Close the Compatibility Checker without making any changes.

 

Change the Sparklines from columns to lines.

 

Change the high point marker color for the selected Sparkline to Light Green (the fifth color from the left in the row of standard colors). The Sparkline has already been ungrouped.

 

Save this workbook as a template. 

 

Mark the workbook as final so it opens as read-only to discourage editing.

 

Ungroup the Sparkline in cell B8 so you can apply formatting to just that Sparkline.