INFO 165

Leave the existing subtotal values and add a second-level subtotal for area

1. Click data tab 2.Go to outline 3. click subtotal 4. Click the at each change in arrow 5. Click area 6. Click the replace current subtotals checkbox to deselect it 7. Click ok

Load the Solver add-in

1. Click file tab 2. Click options 3. Click add-ins 4. Click Go 4. Click the solver add in check box, click ok

Use the name manager to delete the monthly_payment named range

1. Click on Formulas Tab 2. Click Name Manager Tab 3. Click the monthly_payment 4. Click Delete

In cell F3, insert a 3-D formula to sum the value of cell E3 across Qtr1, Qtr2, Qtr3, and Qtr4 sheets

1. Select cell F3, then type =Sum( 2. Click the first sheet tab, hold shift, and then click the last sheet tab to select qtr1 - qtr4 3. Click cell E3 then press cntrl enter

Insert DSUM function in Cell N2 that sums the gross sales for UT stores for the week ending 10/31/2021. Use the range A2:E52 as the database, gross sales as the field, and the range G2:K3

1. click sell N2, then type =DSUM( A2:E52, "Gross Sales",G2:K3) 2. Press enter

Use the auto outline feature to group the columns

1. go to data 2. select outline 3. go to group and select auto outline 4. hit ok

Use the Subtotal feature to sum the number of wolesales, retail sales, and total book sales by discipline

1. on the data tab in the outline group, click subtotal 2.Click sales:wholesale 3. click sales:retail

With Transaction_ID selected in the transaction table, click the query tab, and click edit in the group. Click the date column, click data type in the transform group, and select date. The currency format will automatically apply to the purchase_price and selling_price columns. Click close & load in the close group to exit power query editor

1.

Create a constraint limiting cell b2 to less than or equal to 300000. Click Add, then create a second constraint to ensure that cell b2 is greater than or equal to 100000. in the add constraint dialog box, click add again

1. Click Add 2. Click cell B2 3. Click in the constraint box and type 300000, then click add

Perform an advance Filter displaying records for UT for the week ending 10/31/2021. Ensure the filtered data output begins in the range G9:K9

1. Click Cell A2 2. Data Tab 3. Click Advanced 4. Click copy to another location 5. Click to criteria range, and then drag the range G2:K3 6. Type G9:K9 in the copy to box , click ok

Replace the cell references in the PMT function located in Cell B12 with the appropriate named ranges

1. Click Cell B12 2. Click Insert function aka (Fx) 3 with the value in the rate box selected, click cell B10 4. Press Tab 5. Click cell B11 6. Type " - ", and click B9-

Insert a hyperlink in cell B2 to the range E2:E11 in the Qtr1 worksheet

1. Click Cell B2, then click insert 2. In the links group, click link 3. Place in this Document 4. In the type the cell reference, Type E2:E11 5. Click QTR1, then click ok

with e09h2Indiana workbook displayed, in cell B3, enter a formula to link to cell F3 in the open 09h2Indianapolis workbook on the yearly Totals worksheet. Change the reference to cell F3 to a relative cell reference

1. Click Cell B3 and type =

Test the data Validation by typing 600000 in Cell D4. Click yes in the potentially Invalid Data message box.

1. Click Cell D4 and then type 600000 2. Click Yes

Use the proper function in Cell E2 to return the proper case data in Cell D2

1. Click Cell E2. Click formula tab 2. Click text, then select proper function 3. Click Cell D2 to enter it in the textbox, click ok

Insert a reference to the original monthly payment in cell E3

1. Click Cell E3 2. Type =b12 3. then press Control + Enter

Beginning in Cell I4 on the home Loan tab, complete the series of substitution values ranging from 4% to 8% in increments of .25% vertically down to column I

1. Click Cell I4 2. Click Fill in the home tab, then click series 3. Select Columns 4. Step Value type .25% 5. stop value put 8%

With cell B5 selected show the book sales values as % of row total

1. Click Field setting in the Analyze Tab 2. Click Show value as 3. Select % of row total

Create a what-if scenario name Best-Case Scenario. The Scenario should change the range B2:B5. Assign 200000 to cell B2, 10000 to cell B3, 0.0225 to cell B4, and 15 to cell B5 Do not close Scenario Manager after completing this step.

1. Click What-if Analysis, then click scenario manager 2. Click Add 3. In Scenario Name click Best-Case Scenario 4. Click Changing cells box, then select the range B2:B5, then click ok 5. Type 200000, press tab 6. type 10000, press tab 7. type 0.0225, press tab 8. Type 15, press ok

Insert a list of range names used in formulas in the workbook starting in cell A2

1. Click cell A2 2. On formulas tab 3. Use In formula 4. Paste Names 5. Paste list

In cell B1 in the books data sheet, enter the GETPIVOTDATA function to cell F14 in the pivottable sheet.

1. Click cell B1 2. Type = 3. Click Cell F14 in PivotTable sheet and hit enter

Sort the PivotTable from largest to smallest total book sales

1. Click cell B5 2. Click Data 3. Click Z-->A

create a calculated field named author royalties that mulitplies the total book sales field by .1

1. Click cell C5 2. Click analyze tab 3. Click calculations 4. Click Calculated Field 5. Type author royalties 6. Select total book sales 7. Then type *.1 in formula

Create a custom number format for cell E3 to display the word Payment

1. Click cell E3 2. Click the number format dialog box launcher 3. In the format cells dialog box, on the number tab, under category, click custom 4. Select general 5. In the type box put "Payment", then click ok

Apply a custom number format to cell I3 to display APR

1. Click cell I3, then click home tab 2. Click The number format dialog box launcher 3. In the format cells dialog box, on the number tab, under category, click custom 4. Select General 5. Type "APR"

Sort the data by discipline and then by area, both in alphabetical order

1. Click the data tab 2. Click sort to open the sort dialog box

Arrange the windows of the active workbook so that they are tiled on the screen

1. Click the view tab 2. In the window group click arrange all 3. With tiled selected click the windows of active workbook check box

Open three new windows within the workbook so that all four windows display the qtr1 worksheet

1. Click the view tab 2. New Window 3. Click the view tab 4. New Window 5. Click the view tab

Use the Data Validation to Circle Invalid Data

1. On the Data tab, in the Data Tools group, click the Data Validation arrow, and then click circle Invalid data 2. Click Circle Invalid Data

Beginning in cell D4 on the Home Loan tab, complete the series of substitution values ranging from 4% to 6% in increments of 0.25% vertically down column D

1. On the Home loan tab click cell D4 2. Click Fill under the autosum tab 3. Click series 4. Click Columns 5. Select step value box and type 0.25% 6. Stop Value 6%

With the Qtr1 worksheet displayed, fill the contents and formatting in cell A1 across all worksheets

1. Select A1. On the home tab, in the editing group, click fill, and then click across all worksheets 2. Then press ok

In the State Managers Worksheet, insert the TextJoin Function in cell D2 to use a space as the delimiter to join the text in the range A2:C2. Set the ignore_empty argument to TRUE

1. Select Cell D2 2. Click the Formulas Tab 3. Click Text, Scroll down and then click TEXTJOIN 4. In the Delimeter box type " " 5. Ignore empty box type true 6. Text1 type A2 7. Text2 type B2 8 Text3 type C2

AutoFit the widths of columns A and B

1. Select Columns A and B 2. Click Home tab 3. Click Format

Locate and repair the circular reference

1. Select E11 and the type D11

Convert the data in the range E2:E11 to columns using a space as the delimiter character. Do not import the blank column during the conversion.

1. Select E2:E11 2. Click the text to columns in the data tools tab 3. Select space, then click next 4. Click do not import column 5. Click Finish

Display all tables within the PivotTable Fields List and select the Total Book Sales from the BOOKS table

1. Select all on the PivotTable Fields 2. Click books and then scroll to Total Book Sales

Fill the heading information in range A2:A10 on the Qtr1 worksheet across all worksheets

1. Select the range A2:A10. On the Home tab, in the editing group, click fill, and then click across worksheets

Copy the data the range A2:E2 and paste the data into cell G2 include column labels for a criteria range. Use the paste option to keep source column widths

1. Select the range A2:E2 2. Click on the clipboard on the hometab 3. Click Cell G2 4. Click paste arrow and click keep source column widths

Create Range names for cells B9, B10, B11, B12, and B13 based on the names located in A9, A10,A11,A12, and A13

1. Select the range A9:B13 2. Click Formulas Tab 3. Click Create from selection 4. Ensure left column is selected

With the worksheets grouped, select the range B3:D10 and unlock the cells

1. Select the range B3:D10, then click the home tab 2. In the cells group, click format, and then click lock cell

Select the range B3:D3 in the Indiana workbook and copy the formulas to the range B4:D11. Use the autofill option to fill without formatting.

1. Select the range B3:D3. Double click the fill handle in cell D3

Group the Qtr1 through Qtr4 worksheets. Use Autosum to simultaneously total the values of the data in the range B3:E11. Simultaneously format the ranges B3:E3 and B11:E11 using the accounting number format.

1. Select the range B3:E11, hold control 2. Then select the range B11:E11

On the Qtr4 worksheet, Create a validation rule for the range B4:D4 to allow decimal values that are less than or equal to $500,00 Do not close the Data Validation dialog box

1. Select the range B4:D4, then click data tab 2. In the Data Tools group, click data validation 3. Click the allow arrow and click decimal 4. Click less than or equal to 5. Maximum type 500000

Convert the data in the range C2:C11 to columns using a comma as the delimiter character. After selecting the Comma delimiter check box, click finish

1. Select the range C2:C11, and click data 2. Click Text to Columns in the Data Tools group 3. Ensure Delimited is selected and click next 4. Deselect the tab check bo, click the comma check box 5. Click Finish

Use the reference and the substitution values created to complete a single-variable data table in the range D3:G12

1. Select the range D3:G12, then click data tab 2. Click What-If Analysis, Then click data table 3. Click in the column input cell box, and then click cell b4, then click ok

Complete the two variable data table using the range I3:L20 and reference cells B2 and B4

1. Select the range I3:L20, then click the data tab 2. Click What-if Analysis, then click data table 3. With the Row input cell box selected, click cell B2 4. Click in the column input cell box and Click cell B4, then click ok

Set solver to solve the objective cell of B12 on the home loan worksheet to the value of 1000. Define the variable cell range as B2:B5. leave the solver dialog box open, as you will define the constraints in the next step.

1. on the data tab, click solver 2. Click cell b12 3. Click value of 4. Type 1000 in the value of box 5. Click in the by changing variable cells and select the range B2:B5

Use Flash Fill to copy the names down Column B

1. on the home tab click fill 2. Click flash fill

In Cell B12, use goal seek to determine how much you can spend on a home (B2) to maintain a 600 dollar monthly payment

1., Click Cell B12 2. Click the data tab then click What-if Analysis 3. Then Click goal Seek 4. To value box type 600 5.By changing cell box type b2, then click ok