BUSI 201

Select the Items Received data series and apply the Intense Effect - Gold, Accent 4 shape style (the third style from the right in the last row).

Click any data point in the Items Received series to select the series. On the Chart Tools Format tab, Shape Styles group, click the More button to expand the Shape Styles gallery. Select Intense Effect - Gold, Accent 4 shape style (the third style from the right in the last row).

Collapse the entire outline to show just the subtotals.

Click the 2 outline symbol at the left of the column headings.

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

On the Chart Tools Design tab, in the Type group, click the Change Chart Type button. In the Change Chart Type dialog, click the Secondary Axis check box next to the Total series.

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

Type $5,000 Merit and press Enter. Type $ in cell E3 and press Enter to accept Flash Fill¿s suggestion.

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

Click the Insert tab. In the Charts group, click the Insert Pie Chart button. Select the 3-D Pie chart type .

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.

Click the Insert tab. In the Illustrations group, click the Insert a SmartArt Graphic button. Click the Hierarchy category. Click the Organization Chart option and click OK. Click on the word [Text] in the top shape and type Ken Dishner. Click outside the diagram.

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

Click the Table Tools Design tab. In the Tools group, click the Remove Duplicates button. Click OK. Click OK.

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.

i hit save and it counted it as correct

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.

On the Chart Tools Design tab, in the Data group, click the Select Data button. In the Select Data Source dialog, click the Add button. Click cell B1 to enter it in the Series name box. Click in the Series values box and delete the default entry. Click and drag cells B2:B4. Click OK to add the new series. Click OK to accept the change to the chart data source.

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

On the Chart Tools Format tab, Shape Styles group, click the Shape Outline button, point to Weight, and select 6 pt.

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.

On the Chart Tools Format tab, in the Current Selection group, click the Format Selection button to open the Format Data Point task pane. In the Angle of first slice box, type the 130 and press Enter.

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:K3 reference the total expenses value in cell E5, and the substitute values in cells H4:H8 reference the original investor percentage in cell B9.

On the Data tab, in the Forecast group, click the What-If Analysis button, and click Data Table... Enter E5 in the Row input cell box. Enter B9 in the Column input cell box. Click OK.

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

On the Data tab, in the Forecast group, click the What-If Analysis button, and click Goal Seek... Enter 200 in the To value box. Enter B2 in the By changing cell box. Click OK. Click OK.

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

On the Data tab, in the Forecast group, click the What-If Analysis button, and then click Scenario Manager. Click New Bonus in the Scenarios box. Click Show. Click Close.

Clear the filter from the State column.

On the Data tab, in the Sort & Filter group, click the Clear button.

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

On the Formulas tab, in the Formula Auditing group, click the Watch Window button. Click Add Watch. Click Add.

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

On the Home tab, in the Cells group, click the Format button. Click Lock Cell to remove the highlight.

Enable filtering.

On the Home tab, in the Editing group, click the Sort & Filter button and select Filter.

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

On the Home tab, in the Editing group, click the Sort & Filter button and select Filter. Click the arrow at the top of the State column. Click the (Select All) check box to remove all the checkmarks. Click the check box in front of MD. Click OK.

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.

On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule. Expand the Format Style list and select Icon Sets. Click the Show Icon Only check box. Type 80 in the first value box. Type 20 in the second value box. Click OK.

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.

On the Home tab, in the Styles group, click the Conditional Formatting button. Click New Rule. In the Select a Rule Type box, click Format only values that are above or below average. Expand the Format values that are list and select equal or above. Click the Format button. In the Format Cells dialog, on the Font tab, click Italic. Click OK. Click OK.

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.

On the Home tab, in the Styles group, click the Format as Table button. Click Table Style Light 10. It is the third style in the second row under the Light styles. Verify that the My table has headers checkbox is checked. Click OK.

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

On the Insert tab, in the Charts group, click the Recommended Charts button. Click the All Charts tab. Click Templates. Click the POChart template. Click OK.

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.

On the Review tab, in the Changes group, click the Protect Sheet button. Click OK.

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.

i hit save and it counted it as correct

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

Click the Chart Elements button that appears near the upper right corner of the chart. Click the Trendline check box to add a checkmark.

Filter the chart so the Misc data series is hidden.

Click the Chart Filters button. Click the checkbox in front of Misc to remove the check mark. Click Apply.

Move the selected chart to a new chart sheet.

Click the Chart Tools Design tab. In the Location group, click the Move Chart button. Click the New Sheet radio button. Click OK.

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

On the Data tab, in the Sort & Filter group, click the Sort A to Z button.

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

On the Data tab, in the Sort & Filter group, click the Sort A to Z button.

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

Click the Chart Tools Design tab, in the Data group, click the Switch Row/Column button.

Apply the Style 5 Quick Style to the chart.

Click the Chart Tools Design tab. In the Chart Styles group, click Style 5. It is the fifth option in the Quick Styles gallery.

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

Click the Chart Tools Design tab. In the Chart Styles group, click the Change Colors button. Click Colorful Palette 4, it is the fourth option in the Colorful section.

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).

Click the August Purchase Orders column in the Items Received data series once to select it. On the Chart Tools Format tab, Shape Styles group, click the Shape Outline button, and click Orange, Accent 2 (the fifth color from the right in top row of theme colors).

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

Click the AutoFilter arrow at the top of the column. Point to Filter by Color and click the green color under Filter by Cell Color.

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

Click the Chart Tools Design tab. In the Type group, click the Change Chart Type button. Click 100% Stacked Column (the third option along the top of the right pane). Click OK.

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

Click the Data tab. In the Sort & Filter group, click the Sort A to Z button.

Add the Developer tab to the Ribbon.

Click the File tab. Click Options to open the Excel Options dialog. Click Customize Ribbon. At the right side of the Customize the Ribbon window, the Customize the Ribbon list shows all the tab and groups available. Click the Developer check box to add the Developer tab to the Ribbon. Click OK.

Install the Solver add-in.

Click the File tab. Click Options. Click Add-Ins. Click Go¿ Click the Solver Add-in check box. Click OK.

Save this workbook as a template.

Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Excel Template (*.xltx). Click Save.

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.

Click the File tab. Click the Check for Issues button, and then click Check Compatibility. Click OK.

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

Click the File tab. Click the Protect Workbook button, and select Mark as Final. Click OK. Click OK.

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

Click the Green column

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

Click the Insert tab. Click the Insert a SmartArt Graphic button. In the Choose a SmartArt Graphic dialog box, click the Hierarchy category. Click the second option in the first row and click OK.

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).

Click the Insert tab. In the Charts group, click the Insert Column or Bar Chart button. Click the 3-D Clustered Bar chart type.

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

Click the Insert tab. In the Charts group, click the Insert Line Chart button. Select the Line with Markers chart type.

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

Click the Quick Analysis Tool button and click the Sparklines tab. Click Line.

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

Click the Quick Analysis Tool button, and then click the Charts tab. Click Clustered Column.

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

Click the SmartArt Tools Design tab. Click the More button to display the gallery. Select the third option in the 3-D section of the gallery.

Change the Sparklines from columns to lines.

Click the Sparkline Tools Design tab. In the Type group, click the Line button.

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.

Click the State field in the PivotTable Fields pane and drag it to the Filters box in the bottom part of the pane. Click the filter arrow in cell B1. Click MD. Click OK.

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

Click the Table Tools Design tab. In the Tools group, click the Insert Slicer button. Click the City check box. Click OK.

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.

On the Chart Tools Format tab, in the Current Selection group, click the Format Selection button to open the Format Data Point task pane. In the Point Explosion box, type 45 and press Enter.

Extend the trendline to forecast forward two periods.

On the Chart Tools Format tab, in the Current Selection group, click the Format Selection button. Type 2 in the Forward box. Press Enter.

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.

On the Data tab, in the Sort & Filter group, click the Sort button. Expand the Sort by list and select Item. Click the Add Level button. Expand the Then by list and select Date Expected. Click OK.

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)

On the Chart Tools Format tab, in the Shape Styles group, click the Shape Fill button. Point to Gradient to expand the gallery and click the second gradient in the last row in the Variations section (Linear Up).

Change the gridlines to use the Long Dash dash style.

On the Chart Tools Format tab, in the Shape Styles group, click the Shape Outline button. Point to Dashes, and select the sixth dash style in the gallery (Long Dash).

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.

On the Data tab, in the Analysis group, click the Solver button. Type G1 in the Set Objective box. (The Max radio button is selected by default.) Type Attendees in the By Changing Variable Cells box, and then click the Add button. Type Attendees in the Cell Reference box. Type MaxPerClass in the Constraint box. Click Add. Type Attendees in the Cell Reference box. Expand the drop-down list in the middle box and select Int. Click Add. Type TotalAttendees in the Cell Reference box. Type MaxAttendees in the Constraint box. Click OK. Click Solve. Click OK.

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.

On the Data tab, in the Data Tools group, click the Consolidate button. In the Consolidate dialog, the Sum function is already selected. Type ItemsOrdered in the Reference box. Click the Top row check box. Click the Left column check box. Click OK.

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

On the Data tab, in the Data Tools group, click the Data Validation button. Expand the Allow list and select Decimal. Expand the Data list and select less than. Type 5.00 in the Maximum box. Click OK.

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

On the Data tab, in the Data Tools group, click the Data Validation button. In the Data Validation dialog, expand the Allow list and select List. In the Source box, type: =MedCodes Click OK.

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

On the Data tab, in the Data Tools group, click the Text to Columns button. Click Next. Click the Comma check box. Click Next. Click Finish.

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

On the Data tab, in the Data Tools group, click the What-If Analysis button, and click Scenario Manager. Click the Summary button. Click OK.

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.

On the Data tab, in the Forecast group, click the What-If Analysis button, and click Data Table... Enter B9 in the Column input cell box. Click OK.

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

On the Data tab, in the Forecast group, click the What-If Analysis button, and then click Scenario Manager. Click the Add button. Type New Bonus in the Scenario name box. Click OK. Type 0.09 in the text box. Click OK.

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

On the Data tab, in the Get & Transform Data group, click the From Text/CSV button. In the Import Data dialog, click WageIncreases, and click the Import button. Click the Load button.

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

On the Data tab, in the Get & Transform group, click the Get Data button. Point to From Database, and click From Microsoft Access Database. Click the Staff database file, and then click the Import button. Click Employees, and then click the Load button.

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.

On the Data tab, in the Outline group, click the Subtotal button. Expand the At each change in list and select Status. Click OK.

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.

On the Data tab, in the Sort & Filter group, click the Advanced button. Enter A1:G2 in the Criteria range box. Click OK.

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

On the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. Click the AutoFilter arrow at the top of the Item column, and point to Text Filters. Click Begins With... Type G in the box next to begins with. Click OK.

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

On the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. Click the AutoFilter arrow at the top of the Order Date column, and point to Date Filters. Click After... Type 6/30/2017 in the box next to is after. Click OK.

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

On the Data tab, in the Sort & Filter group, click the Filter button to display the AutoFilter arrows at the top of each column. Click the AutoFilter arrow at the top of the Ordered column, and point to Number Filters. Click Greater Than Or Equal To... Type 18 in the box next to is greater than or equal to. Click OK.

Begin recording a new macro.

On the Developer tab, in the Code group, click the Record Macro button.

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.

On the Developer tab, in the Code group, click the Record Macro button. Type ConvertTablein the Macro name box. Click OK. On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes. On the Developer tab, in the Code group, click the Stop Recording button.

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.

On the Insert tab, in the Charts group, click the Combo Chart button. Click the first chart type in the gallery.

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.

On the Insert tab, in the Links group, click the Link button. Under Link to: click Place in this document. In the Type the cell reference box, type: A7 In the Or select a place in this document box, click Downtown. Click OK.

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

On the Insert tab, in the Tables group, click the Recommended PivotTables button. Click the Sum of Total Spent by State option. Click OK.

Apply the Style 3 Quick Style to the PivotChart.

On the PivotChart Tools Design tab, in the Chart Styles group, click Style 3. It is the second option in the Quick Styles gallery.

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

On the PivotTable Tools Analyze tab, click the Calculations button, and then click Fields, Items, & Sets. Select Calculated Field... In the Insert Calculated Field dialog, type Average in the Name box. In the Formula box, delete the 0 and type Sales/Visits. Do not delete the equals sign. Click OK.

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

On the PivotTable Tools Analyze tab, in the Data group, click the Change Data Source button. Type CustomersTable in the Table/Range box. Click OK.

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

On the PivotTable Tools Analyze tab, in the Data group, click the Refresh button.

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

On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Slicer button. In the Insert Slicers dialog, click the check box for State. Click OK. In the slicer, click VA.

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.

On the PivotTable Tools Analyze tab, in the Filter group, click the Insert Timeline button. In the Insert Timeline dialog, click the check box for the Date of Last Visit field. Click OK. Click the September segment on the timeline.

Insert a PivotChart using the first bar chart type.

On the PivotTable Tools Analyze tab, in the Tools group, click the PivotChart button. Click Bar in the list of chart types at the left side of the Insert Chart dialog. Click OK.

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

On the Review tab, in the Changes group, click the Share Workbook button. In the Share Workbook dialog, click the Allow changes by more than one user at a time check box. Click OK. Click OK to save the workbook and add sharing.

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

On the Review tab, in the Comments group, click the New Comment button. Type: Why did advertising increase? Click outside the balloon.

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

On the Sparkline Tools Design tab, in the Group group, click the Ungroup button.

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

On the Sparkline Tools Design tab, in the Show group, click the High Point check box.

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.

On the Sparkline Tools Design tab, in the Style group, click the Marker Color button, point to High Point, and click Light Green (the fifth color from the left in the row of standard colors).

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

On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box. In the Total row, click in the Total Spent column. Click the arrow and select Max.

Convert the table to a normal range.

On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes.

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

On the Table Tools Design tab, in the Tools group, click the Insert Slicer button. Click the State check box. Click OK. In the Category slicer, click the MD button.