SIMnet- Practice Test
Use AutoSum to enter a SUM function in the selected cell. Font Size
On the Home tab, in the Editing group, click the AutoSum button. Press Enter.
Display the details for just the order closed group. Font Size
Click the + symbol next to row 9.
Enter a formula in cell C1 to calculate the mean absolute deviation of the values in cells E4:E12. Font Size
In cell C1, enter this formula: =AVERAGE(E4:E12)
apply the colorful pallet 4 color scheme to the chart
On 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.
Move the selected chart to a new chart sheet
On the chart tools design tab, in the location group, click the Move Chart button click the New Sheet radio button then click ok
Change the color of the sheet tab for the November worksheet to Purple (the last option at the right in the row of standard colors). Font Size
Right-click the worksheet tab and point to Tab Color. Click Purple (the last option at the right in the row of standard colors).
Enter a formula in the selected cell using the SUM function to calculate the total of cells B4 through D4. Font Size
Type the formula =SUM(B4:D4) in the cell or the formula bar and press Enter.
Sort this data by the Date of Last Visit column so the oldest dates are listed first. Font Size
Click the Data tab. In the Sort & Filter group, click the Sort A to Z button.
Switch to Page Break Preview view. Font Size
Click the Page Break Preview button at the lower right corner of the status bar, to the left of the zoom slider.
Spell check the Cash Flow worksheet and correct the misspelling of the word Accounting. Close the checker when the check is complete. Font Size
Click the Review tab and click the Spelling button. Click the Change button. Click OK.
Add slicers to filter the data in this table by City. Font Size
Click the Table Tools Design tab. In the Tools group, click the Insert Slicer button. Click the City check box. Click OK.
Remove duplicate rows where data in all the columns are identical. Font Size
Click the Table Tools Design tab. In the Tools group, click the Remove Duplicates button. Click OK. Click OK.
Set the print area
Click the back button. On the Page Layout tab, in the page Setup group, click the Print Area button. Click Set Print Area
Hide column E
Click the column selector for Column E. On the Home tab, in the Cells group, click the Format button. Point to Hide & Unhide, and click Hide Columns.
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.
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. Font Size
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.
Run the FormatTable macro. Font Size
On the Developer tab, in the Code group, click the Macros button. In the Macro dialog, FormatTable is selected. Click the Run button.
Edit the code for the FormatTable macro in the Visual Basic Editor. Font Size
On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button.
Edit the code for the FormatTable macro to change the chart style from TableStyleLight11 to TableStyleMedium4. Save your changes. Font Size
On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button. In the line of code Change ActiveSheet.ListObjects("Table2").TableStyle = "TableStyleLight11"change TableStyleLight11 to TableStyleMedium4. On the Visual Basic Editor toolbar, click the Save button.
Edit the code for the FormatTable macro to add a new comment in the blank line above the chart style code that reads Change table style Press Enter when you are done. Font Size
On the Developer tab, in the Code group, click the Macros button. Select FormatTable and click the Edit button. Type 'Change table style and press Enter.
Begin recording a new macro. Font Size
On the Developer tab, in the Code group, click the Record Macro button.
Use the Create from Selection command to create named ranges for the data table B8:E11 using the labels in row 7 as the basis for the names. Font Size
On the Formulas tab, in the Defined Names group, click the Create from Selection button. The Top row check box is checked by default. Click OK.
Hide all of the dependency tracer arrows at once. Font Size
On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.
Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. Use a negative value for the Pv argument. Font Size
On the Formulas tab, in the Function Library group, click the Financial button, and click PMT. Enter B5/12 in the Rate argument box. Enter B6 in the Nper argument box. Enter -B4 in the Pv argument box. Click OK.
In cell B14, enter a formula using NPV to calculate the value today (the present value) of the tuition payment option 3. Use cell B7 as the Rate argument and the cell range B10:B13 as the Value1 argument. Use cell references for all values. Font Size
On the Formulas tab, in the Function Library group, click the Financial button. Click NPV. Enter B7 in the Rate box. Enter B10:B13 in the Value1 box. Click OK.
In cell B10, enter a formula using PV to calculate the value today (the present value) of the four-year tuition plan. Use cell references wherever possible. The annual interest rate for your investment account is stored in cell B8, the number of monthly payments in cell B7, and the monthly payment amount in cell B6. Payments will be made at the beginning of every period. Pay attention to the time periods for the interest rate and payment schedule. Remember to express the Pmt argument as a negative. Font Size
On the Formulas tab, in the Function Library group, click the Financial button. Click PV. Enter B8/12 in the Rate box. Enter B7 in the Nper box. Enter -B6 in the Pmt box. Type 1 in the Type box. Click OK.
Enter a formula in cell C3 using the SUMPRODUCT function to calculate the total value of the current leases by multiplying the current monthly rents by the remaining months on each lease. Use the range names SpecialPrice and MembershipsSold. Font Size
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMPRODUCT. In the Array1 argument box, type SpecialPrice. In the Array2 argument box, type MembershipsSold. Click OK.
Enter a formula in cell C1 to calculate the standard deviation of the values in cells C5:C13. Assume this array is a sample of a larger set of values. Font Size
On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select STDEV.S. In the Number1 argument box, enter C5:C13. Click OK.
In cell D2, enter a formula using TEXTJOIN to combine the text from cells A2:C2 to display the customer name, city, and state. Use a space as the delimiter. Ignore blank cells. Use a cell range as the Text1 argument. Font Size
On the Formulas tab, in the Function Library group, click the Text button. Select TEXTJOIN from the list. Type " " in the Delimiter argument box. Type TRUE in the Ignore_empty argument box. Type A2:C2 in the Text 1 box. Click OK.
Use a single command to clear everything (content and formatting) from the selected cell. Font Size
On the Home tab, in the Editing group, click the Clear button. Select Clear All.
Apply the Accounting Number Format to the selected cells. Font Size
On the Home tab, in the Number group, click the Accounting Number Format button.
Modify the number format so no decimal places are visible after the decimal point. Font Size
On the Home tab, in the Number group, click the Decrease Decimal button twice.
Apply the Title cell style to the selected cell. Font Size
On the Home tab, in the Styles group, click the Cell Styles button. Click the Title style.
Insert a PivotChart using the first bar chart type. Font Size
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.
Apply the Dark Blue, Pivot Style Dark 2 Quick Style (the second option in the first row under the Dark styles) to the PivotTable. Font Size
On the PivotTable Tools Design tab, in the PivotTable Styles group, click the More button to expand the Quick Styles gallery, and select Dark Blue, Pivot Style Dark 2. It is the second option in the first row under the Dark styles.
Clear the selected Sparklines from the worksheet. Font Size
On the Sparkline Tools Design tab, in the Group group, click the Clear button.
Change the table design so the Total row is showing. Font Size
On the Table Tools Design tab, in the Table Style Options group, click the Total Row check box. OK
Add a total row to this table and display the maximum value for the Total Spent column. Font Size
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.
Use a slicer to filter the data in this table to show only rows where the State value is MD. Font Size
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.
Insert a Left Arrow block arrow shape to the right of the Jan column in the chart
On the insert tab, click the illustrations button to open the Illustrations group click the shapes button and select left arrow shape from the Blocks arrow section. the cursor changes to a cross hair. click on the chart to the right of the Jan column
Save this file as a macro-enabled workbook. Font Size
Click the File tab. Click Save As. On the Save As page, expand the list of file types and select Excel Macro-Enabled Workbook. Click Save.
Apply the Layout 1 Quick Layout to the chart. Font Size
On the Chart Tools Design tab, in the Chart Layouts group, click the Quick Layout button. Click the Layout 1 option.
Use Flash Fill to autofill the cells in this column. Start by entering the pattern $5,000 Merit in the cell E2. Font Size
Type $5,000 Merit and press Enter. Type $ in cell E3 and press Enter to accept Flash Fill¿s suggestion.
=E3Enter a formula in the selected cell to display the value of cell E3. Font Size
Type =E3. Press Enter.
Collapse the entire outline to show just the subtotals. Font Size
Click the 2 outline symbol at the left of the column headings.
Hide the chart title.
Click the Chart Elements button and click the Chart Title check box.
Display the data labels above the data markers on the chart. Font Size
Click the Chart Elements button. Click the Data Labels arrow and select Above.
Filter the chart so the Misc data series is hidden. Font Size
Click the Chart Filters button. Click the checkbox in front of Misc to remove the check mark. Click Apply.
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. Font Size
On the Data tab, in the Analyze 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.
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. Font Size
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.
Use Goal Seek to find the value for cell B2 that will result in a value of $200 for cell B6. Accept the solution. Font Size
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.
Import data from the text file WageIncreases. Allow Excel to import the data into a table in a new worksheet. Font Size
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. Font Size
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.
Use a custom number filter to show only rows where the quantity ordered (Ordered column) is greater than or equal to 18. Font Size
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.
Sort this data alphabetically from A to Z by values in the Last column. Font Size
On the Data tab, in the Sort & Filter group, click the Sort A to Z button.
Enter a formula in cell B2 using the VLOOKUP function to find the total sales for the date in cell B1. Use the name DailySales for the lookup table. The total sales are located in column 5 of the lookup table. Be sure to require an exact match. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select VLOOKUP. Type B1 in the Lookup_value argument box. Type DailySales in the Table_array argument box. Type 5 in the Col_num argument box. Type False in the Range_lookup box. Click OK.
In cell B3, enter a formula using INDEX to look up the expected delivery date for the purchase order at the row position in cell B2. Use the named range JunePOs as the Array argument. The price is found in the fifth column of the array. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter JunePOs. In the Row_num box, enter B2. In the Column_num box, enter 5. Click OK.
Enter a formula in cell E2 using SUMIFS to calculate the total price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to "Salon Supplies". Font Size
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIFS. In the Sum_range argument box, type JunePrices. In the Criteria_range1 argument box, type JunePOs. In the Criteria1 argument box, type D1. In the Criteria_range2 argument box, type JuneCompanies. In the Criteria2 argument box, type "Salon Supplies". Click OK.
Add the DEVELOPER tab to the ribbon
Click 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 OKk
Resize the selected chart so it is approximately 12 rows tall. Font Size
Click and drag a resize handle up or down toward the center of the chart to make it smaller.
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).
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). Font Size
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. Font Size
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.
Move the Car Loan worksheet so it is positioned before the Personal Loan worksheet. Font Size
Click the Car Loan worksheet tab and hold down the left mouse button. Drag the mouse pointer to a point just in front of the Personal Loan worksheet tab, and release the mouse button.
Display the data table, including the legend keys. Font Size
Click the Chart Elements button and click the Data Table check box.
Add a linear trendline to this chart. Use the default trendline formatting. Font Size
Click the Chart Elements button that appears near the upper right corner of the chart. Click the Trendline check box to add a checkmark.
Change the print option to print the entire workbook, and then print the workbook. Font Size
Click the File tab to open Backstage. Click Print. Click the Print Active Sheets button. Click Print Entire Workbook. Click the Print button.
Save this worksheet as a tab-delimited text file. Font Size
Click the File tab. Click Save As. On the Save As page, click the Save as type arrow and select Text (Tab delimited) (*.txt). Click Save. Click OK.
Use AutoFill to copy the formula and formatting in cell B9 to cells C9:D9. Font Size
Click the Fill Handle tool in the selected cell and drag across to cell D9. Release the mouse button.
Cell F6 has been copied. Paste just the formula without the cell formatting into the selected cell (cell F7). Font Size
Click the Paste button arrow, and then click the Formulas option.
Add line Sparklines to cells E3:E12 to represent the values in B3:D12. Font Size
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
Change the zoom level for the worksheet to be 110%. Font Size
Click the Zoom In button on the zoom slider.
Apply the Data bars conditional formatting option to the selected cells from the Quick Analysis tool
Click the quick analysis tool button at the lower-right corner of the selection. On the formatting tab, click the Data Bars button
Set the last data point (March Net Income) as a total. Font Size
Double-click the March Net Income data point to open the Format Data Point task pane. In the task pane, click the Set as total check box
Autofit column F to best fit the data. Font Size
Double-click the right column boundary for column F.
Enter a formula in cell E4 to calculate the absolute value of C4-D4. Font Size
In cell E4, enter this formula: =ABS(C4-D4)
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.
Change the chart type to the first 100 % stacked column option (the third option along the top of the right pane). Font Size
On 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.
Modify the chart so the Total data series is plotted along the secondary axis. Font Size
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.
The chart title is selected. Apply the Gradient Fill, Gray WordArt quick style (the first style in the second row of the WordArt Quick Styles gallery). Font Size
On the Chart Tools Format tab, in WordArt Styles group, click the WordArt Quick Styles button to expand the gallery. Click the first style in the second row (Gradient Fill, Gray).
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. Font Size
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.
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, clcik the What if- Analysis button, and then clcik 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
Using the Scenario Manager, show the New Bonus scenario. Close the Scenario Manager when you are through. Font Size
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. OK
This worksheet does not include any total rows, and there are blank rows between groups if data. Manually create groups of rows that can be expanded or collapsed as needed. Starter with the group of selected cells
On the Data tab, in the Outline group, click the Group button. In the Group dialog, Rows Is selected by default. Click OK
Use a custom text filter to show only rows where the item name (Item column) begins with the letter G. Font Size
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/2020. Font Size
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/2020 in the box next to is after. Click OK.
Sort the data so cells with 100% solid fill circle icon in the quantity in stock column appear on top
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 column. Point to Sort by Color and click the solid fill circle icon
Sort this data by the Visits column so the smallest numbers are listed first. Font Size
On the Data tab, in the Sort & Filter group, click the Sort A to Z button.
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. Font Size
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.
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. Font Size
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.
Add a form control button to run the FormatTable macro. The button should be placed at approximately cells G1:I3. Font Size
On the Developer tab, in the Controls group, click the Insert Controls button. Click the Button (Form Control) button. Click and drag over across cells G1:I3 to draw the button. Click FormatTable. Click OK.
Show the tracer arrows from the precedent cells to cell B5. Font Size
On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.
Enter a formula using DSUM to calculate the total value in the Total Spent column for rows that meet the criteria in the crteria range A1:62. The database is defined by the named range CustomersDB.
On the Formulas tab, in the Function Library group, click the Insert Function button to open the Insert Function dialog. Expand the Or select a category list and select Database. Select DSUM, and click OK. Enter CustomersDB in the Database arqument box. In the Field argument box, enter "Total Spent". In the Criteria argument box, enter A1:G2. Click OK
In cell H2, enter a formula using COUNTIFS to count the number of rows where values in the range named DeliveryTime have a value greater than 14 and cells in the range named ReorderStatus display "no". Font Size
On the Formulas tab, in the Function Library group, click More Functions. Point to Statistical, and select COUNTIFS. In the Criteria_Range1 argument box, type DeliveryTime. In the Criteria1 argument box, type ">14". In the Criteria_Range2 argument box, type ReorderStatus. In the Criteria2 argument box, type "no". Click OK.
In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11). Font Size
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Max. Press Enter.
In cell H12 enter a formula to find the lowest percentage of items received in the order (cells H2:H11)
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Min. Press Enter.
In cell B6, enter a formula to calculate the future value of this savings strategy. Use cell references wherever possible. The annual interest rate is stored in cell B5, the number of payments in cell B4, and the monthly payment amount in cell B3. Remember to divide the annual interest rate by 12 and use a negative value for the Pmt argument. Font Size
On the Formulas tab, in the Function Library group, click the Financial button. Click FV. Enter B5/12 in the Rate argument box. Enter B4 in the Nper argument box. Enter -B3 in the Pmt argument box. Click OK.
In cell D6, enter a formula using AND to display TRUE if the daily sales (cell C6) is greater than the overall average (cell C3) and the daily sales (cell C6) is greater than the employee`s average (cell C4). Use cell references and enter the arguments exactly as described in this question. Font Size
On the Formulas tab, in the Function Library group, click the Logical button. Click AND. In the Logical1 box, enter C6>C3. In the Logical2 box, enter C6>C4. Click OK.
Enter formula in cell B8 using the IFS function to calculate a bonus. If the value in cell B6 is greater than or equal to 20000, the bonus is 1000. If the value in cell B6 is greater than or equal to 10000, the bonus is 250. If the value in cell B6 is less than 10000, the bonus is 0. Font Size
On the Formulas tab, in the Function Library group, click the Logical button. Select IFS. In the Logical_test1 argument box, enter B6>=20000. In the Value_if_true1 argument box, enter 1000. In the Logical_test2 argument box, enter B6>=10000. In the Value_if_true2 argument box, enter 250. In the Logical_test3 argument box, enter B6<10000. In the Value_if_true3 argument box, enter 0. Click OK.
Enter a nested function in cell B2 using INDEX and MATCH to find the expected delivery date for the item listed in cell B1. Use the named range JunePOs to reference the cell range INDEX Array argument. The expected due date is in column 5. In the INDEX Row_num function argument, use MATCH to look up the row number for the item listed in B1. Use the named range POitems as the MATCH Lookup_array argument. Require an exact match. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select INDEX. Select the array,row_num,column_num option. Click OK. In the Function Arguments dialog, in the Array box enter JunePOs. In the Column_num box, enter 5. Click in the Row_num box, and then click the arrow in the Name box to the left of the formula bar and select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter B1. In the Lookup_array box, enter POitems. In the Match_Type argument box, enter 0. Click OK.
In cell B2, enter a formula using MATCH to look up the row position of the item listed in cell B1 in the array named POitems. Require an exact match. Font Size
On the Formulas tab, in the Function Library group, click the Lookup & Reference button. Select MATCH. In the Function Arguments dialog, in the Lookup_value box, enter B1. In the Lookup_array box, enter POitems. In the Match_Type argument box, enter 0. Click OK.
Enter a formula in cell E1 using SUMIF to calculate the total quantity in stock for items from the company "ColorFab". Use the range name Company for the Range argument, the text string "ColorFab" for the Criteria argument, and InStock for the Sum_range argument. Font Size
On the Formulas tab, in the Function Library group, click the Math & Trig button. Click SUMIF. In the Range argument box, type Company. In the Criteria box, type "ColorFab". In the Sum_range argument box, type InStock. Click OK.
In cell F12, enter a formula using a counting function to count numbers in the Ordered column (cells F2:F11). Font Size
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNT from the list. Click and drag to select cells F2:F11 in the Value1 box. Click OK.
In cell G12, enter a formula using a counting function to count the number of blank cells in the Received column (cells G2:G11). Font Size
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTBLANK from the list. Click and drag to select cells G2:G11. Click OK.
Enter a formula in cell E2 using AVERAGEIFS to calculate the average price (use the named range JunePrices) where the value in the JunePOs named range is equal to the value in cell D1 and the value in the JuneCompanies named range is equal to the text string "DermoMagic". Font Size
On the Formulas tab, in the Function Library group, click the More Functions button. Under Statistical, click AVERAGEIFS. In the Average_range argument box, type JunePrices. In the Criteria_range1 argument box, type JunePOs. In the Criteria1 argument box, type D1. In the Criteria_range2 argument box, type JuneCompanies. In the Criteria2 argument box, type "DermoMagic". Click OK.
In cell D2, use the CONCAT function to combine the values of cells B2 and C2 with a space in between. The result of the formula should look like this: Davidsonville MD Font Size
On the Formulas tab, in the Function Library group, click the Text button. Select CONCAT from the list. Type B2 in the Text1 box. Type " " in the Text2 box. Type C2 in the Text3 box. Click OK.
Add a footer that displays the page number in the center section. Font Size
On the Header & Footer Tools Design tab, in the Header & Footer Elements group, click the Page Number button. OK
Apply bold and italic formatting to the selected cell. Font Size
On the Home tab in the Font group, click the Bold button and the Italic button.
Center the content in the selected cells horizontally. Font Size
On the Home tab, in the Alignment group, click the Center button.
Merge the cells so the text appears centered across the merged cells. Font Size
On the Home tab, in the Alignment group, click the Merge & Center button.
Wrap the text in the selected cell. Font Size
On the Home tab, in the Alignment group, click the Wrap Text button.
Insert a column to the left of column F. Font Size
On the Home tab, in the Cells group, click the Insert button arrow and select Insert Sheet Columns.
Add a new worksheet to the left of the November sheet Font Size
On the Home tab, in the Cells group, click the Insert button arrow, and select Insert Sheet.
Use Format Painter to copy the formatting from cell E1 and apply it to cell F1. Font Size
On the Home tab, in the Clipboard group, click the Format Painter button. Click cell F1.
Enter a formula in cell E4 to calculate the average value of cells B4:D4. Font Size
On the Home tab, in the Editing group, click the AutoSum button arrow and select Average. Press Enter.
Add a Blue-Gray, Text 2 (in the top row of theme colors, the fourth color from the left) top border to the selected cells. Use the thickest single line style available. Font Size
On the Home tab, in the Font group, click the Borders button arrow, and select More Borders... In the Format Cells dialog, Border tab, click the thickest line available in the Style section. Expand the Color palette and select Blue-Gray, Text 2. In the Border section, click the button representing a top border.
Apply the Top and Double Bottom Border to the selected cells with a single command. Font Size
On the Home tab, in the Font group, click the Borders button arrow, and select Top and Double Bottom Border.
Apply conditional formatting so cells with a value greater than the average are formatted using a yellow fill with dark yellow text. Font Size
On the Home tab, in the Styles group, click the Conditional Formatting button. Point to Top/Bottom Rules, and click Above Average. Click the drop-down arrow and select Yellow Fill with Dark Yellow Text. Click OK.
Filter the state column so only rows with MD are shown
On the Home tab, in the editing group, clcik the sort and filter button and select filtter click the arrows at the top of State column; click the (select all) check box to remove all checkmarks. Click the check box in front of MD then click OK
Convert the cell range to a table using table style Table Style Light 10 The table should include headers.
On the Home tab, in the styles group, click the Format as table button click Table Style Light 10 (Third style in second row under light styles) Verify My table has headers checkbox is checked click ok
Apply the conditioning formatting to the selected cells so cells with a value greater than 10 are formatting using a yellow fill with dark yellow text
On the Home tab, in the styles group, click the conditional formatting button. Point to Highlight Cells Rules, and click Greater Than.. type 10 in the format cells that are greater than box. Expand the format selector box and select yellow fill with dark yellow text. Click ok
Insert a Waterfall chart based on cells A1:B10. Font Size
On the Insert tab, in the Charts group, click the Insert Waterfall or Stock Chart button. Select Waterfall.
Insert a recommended PivotTable, using the Sum of Total Spent by State option. Font Size
On the Insert tab, in the Tables group, click the Recommended PivotTables button. Click the Sum of Total Spent by State option. Click OK.
From Page Break Preview view, insert a page break between rows 4 and 5. Font Size
On the Page Layout tab, in the Page Setup group, click the Breaks button. Click Insert Page Break.
Change the scaling option so all columns will print on one page. Font Size
On the Page Layout tab, in the Scale to Fit group, click the Width arrow. Click 1 page.
Modify this worksheet so gridlines will print. Font Size
On the Page Layout tab, in the Sheet Options group, click the Print check box under Gridlines.
Apply the Gallery theme to the workbook. Font Size
On the Page Layout tab, in the Themes group, click the Themes button. Click the Gallery theme.
Modify the visits field to use the average function instead of Sum function
On the PivotTable Tools Analyze tab, in the Active Field group, click the Field Settings button. Select Average . Click OK.
Show markers for just the highest values in this Sparkline group. Font Size
On the Sparkline Tools Design tab, in the Show group, click the High Point check box.
Apply the Sparkline Style Colorful #2 Quick Style to the selected Sparkline group. It is the second option in the last row of the gallery. Font Sizeuick Style to the selected Spark
On the Sparkline Tools Design tab, in the Style group, click the More button. Click the second option in the last row of the gallery.
Convert the table to a normal range. Font Size
On the Table Tools Design tab, in the Tools group, click the Convert to Range button. Click Yes. OK
Use a slicer to filter the data in this table to show only rows where the State value is MD or DE. Font Size
On the Table Tools Design tab, in the Tools group, click the Insert Slicer button. Click the State check box. Click OK. In the State slicer, click the Multi-Select button. Click the VA button to turn it off.
Create a scenario summary report. Accept the recommended results cells
On the data tab, in the forecast group, click the What-if-analysis button, and click scenario manager. Click the summary button. Click ok
Insert the current date in the selected cell. Do not include the current time
On the formulas tab, in the Function Library group, click the Date and Time button. Click TODAY. Click ok
Apply the Orange, Table Style Medium 3 Quick Style (third style in first row under medium) to this table
On the table design tab, in the table styles group, clcik the quick styles button or clcik the more button to expand the Table Styles gallery. Click Orange, Table Style Medium 3. It's the third style in the first row under Medium styles
On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet. Font Size
Type = and then click the ByMonth sheet tab. Click cell B3. Press Enter.
Clear the print area. Font Size
On the Page Layout tab, in the Page Setup group, click the Print Area button. Click Clear Print Area.
Freeze the top row of the worksheet. Font Size
On the View tab, in the Window group, click the Freeze Panes button. Click Freeze Top Row.
Add the Visits field to the PivotTable. Font Size
In the PivotTable Fields pane, click the Visits check box to add the field to the PivotTable.
Enable filtering
on the home tab, in the editing group, click the sort and filter button and select filter
