Lesson 5- Excel 2019

In the table on the current worksheet, filter the records to display records with an item type of Machine.

Click the Filterdrop-down arrow in cellB2 Clear the Select Allcheck box Select the Machinecheck box Click the OKbutton

In the Combined Salesworksheet, calculate the sales percentage for each representative in the % Total column and change the cell formatting to Percentage.

If the Combined Sales worksheet is not open, click on the Combined Sales tab.2. Click on cell F3and type the formula =E3/$E$8.3. Hit the ENTERkey.4. Click on cell F3again.5. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.6. Click and drag down to cell F7.7. When you release the mouse button, the cell range should now display sales percentage for the other representatives.8. Select the cells F3:F8.9. On the Hometab, Numbergroup click %symbol.

In the Profit - Loss Summaryworksheet, apply a cell reference in cell D5 to the Income worksheet cell H31.

In the Profit - Loss Summaryworksheet, select cellD5, and begin a cell reference by typing=.2. Click on theIncome worksheet, and click cell H31, and hit Enter.

In the Totalsworksheet in column B, enter a cell reference to the matching Totalsvalues from the Juneworksheet.

In the Totals worksheet, select cell B2 and type = to begin a formula.2. In the June worksheet, select cell B34, and hit enter. The contents of Cell B2 in the Totals worksheet should read, =June!B34.3. In the Totals worksheet, select cell B3 and type = to begin a formula.4. In the June worksheet, select cell C34, and hit enter. The contents of Cell B3 in the Totals worksheet should read, =June!C34.5. In the Totals worksheet, select cell B4 and type = to begin a formula.6. In the June worksheet, select cell D34, and hit enter. The contents of Cell B4 in the Totals worksheet should read, =June!D34.

In the Marketing Budget Plan worksheet, sort the data in the cell range A78:D85 by Column B, Largest to Smallest.

In theMarketing Budget Planworksheet, select the cell rangeA78:D85.2. In the Data tab, locate the Sort & Filter group, and click Sort.3. In the Sortdialog box, Sort By Column B,Sort On Values, and Order fromLargest to Smallest. Click Ok.

In the Expenses worksheet, sort the data in cell B40:D45by the Actual column, Largest to Smallest

. In the Expensesworksheet, select cell range B40:D45, in the Data tab, go to the Sort & Filterand click Sort.2. In the Sort dialog box, Sort by Actualand Order byLargest to Smallestand click Ok.

Calculate the total for cells B4, B5, and B6 in cell B8. AutoFill that total through cell M8. Then, in cell B9, calculate the sales tax through the rate in cell Q2. AutoFill those values through to cell M9.

Click in cell B8 Type: =B4+B5+B6 Click the fill handle in cell B8and drag it through cell M8 Click in cell B9 Type: =B8*$Q$2 Click the fill handle in cell B9and drag it through cell M9

In cell B9, multiply the contents of cell B8 and the named range, TaxRate. Then, AutoFill the formula to cell M9.

Click in cell B9 Type: =B8 * TaxRate Click the fill handle in cellB9 and drag it through cellM9

Below each of the headings for Sum, Average, Min, andMax, add functions to get the sum, average, maximum, and minimum values for cells B6 through M6. Then, AutoFill the results down through all of the morning classes.

Click in cell O6 Type: =SUM(B6:M6) Click in cell P6 Type: =AVERAGE(B6:M6) Click in cell Q6 Type: =MIN(B6:M6) Click in cell R6 Type: =MAX(B6:M6) Select cells O6 throughR6 Click the fill handleand drag down through row 20

In cell Q2, insert a function that displays a YES if the total number of morning classes for January is at least 175 and a NO if not.

Click in cell Q2 Click the Formulas tab In theFunction Library group, click theLogical drop-down arrow Click IF In the Logical_testfield type: SUM(S6:S20)>=175 Click in the Value_if_true field Type: YES Click in the Value_if_falsefield Type: NO Click the OKbutton

In cell S6, use a function to display a count of the cells from B6through M6 that contain anumber. In cell T6, use a function to display a count of the cells fromB6 through M6 that contain nothing. AutoFill both cells downthrough all of the morning classes

Click in cell S6 Type: =COUNT(B6:M6) Click in cell T6 Type: =COUNTBLANK(B6:M6) Select cells S6and T6 Click the fill handle and drag down through row 20

In the Source Data worksheet Sort the Data alphabetically by CustomerID and then by Product.

Click on the Source Data datasheet. Selectcell A1, click the Datatab, locate the Sort & Filter group, and clickSort.2. In the Sort By box, select CustomerID, verify that sort on: is Values, and that the Order dropdown isA to Z.3. Click the Add a Level button. In this new level, selectProduct in the Sort Bybox, and verify that the other 2 values areValues and A to Z. Click OK.

In the Summary worksheet, determine the total units sold by flavor for each quarter based on the values in the 2009 Sales and 2010 Sales worksheets.

If the Summaryworksheet is not open, click on the Summarytab.2. Select cell B3.3. In the Formulas tab,Function Librarygroup, click theAutoSum button.4. Ensure that your cursor is blinking between the parentheses in the text=SUM().5. Open the 2009 Sales worksheet. Click once on cell B3.6. Hold down theSHIFT key and then open the 2010 Salesworksheet. This will create a 3-D reference across both worksheets.7. Hit the ENTER key and then click once on cell B3.8. Move your cursor over the AutoFillhandle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.9. Click and drag down to cell B25.10. When you release the mouse button, the cell range should now display the units sold in the first quarter of 2009 and 2010 for each flavor.11. Move your cursor over the AutoFill handle at the lower-right corner of cellB25. The pointer should change from a thick white cross to a thin black cross.12. Click and drag down to cell E25.13. When you release the mouse button, the cell range should now display the units sold in the remaining quarters of 2009 and 2010 for each flavor.

In the Top Performers worksheet, determine the highest sales number for 2011 in cell B3. Help

If the Top Performersworksheet is not open, click on the Top Performers tab.2. Select cell B3.3. In the Formulas tab,Function Library group, click the drop down forAutoSum. Select Max.4. Ensure that your cursor is blinking between the parentheses in the text=MAX(). 5. Open the Sales 2011worksheet. Select cellsD3:D7.6. Hit the ENTER key.

In cell B18, enter a cell reference to cell A7.

In cell B18, begin a function by typing =. 2. Click in cell A7, and hit Enter.

Without using the averagefunction, create a formula in the cell H4 that gets the SUM of the values in the cell E4 through G4 and divides the sum by three.

In cell H4 type:=SUM(E4:G4)/3

In the Annual Sales worksheet,Sort the data in the cell range A5:F15 by the Total column and order the values by largest to smallest.

In the Annual Sales worksheet, select the cell rangeA5:F15.2. in the Home tab, locate the Editinggroup, click Sort & Filter and in the drop down menu click Custom Sort.3. In the Sort byrow, select the Totalcolumn, sort onValues, orderLargest to Smallest, and click Ok.

In the Budget Plan Chartworksheet, create a reference in cell E42 to EventTotal, cell F42 to ProTotal and G42 to AdTotal

In the Budget Plan Chart worksheet, select cell E42.2. Begin a cell reference by typing=, and then enter the predifined named rangeEventTotal.3. Follow steps 1 and 2 for cell F42 with named rangeProTotal, and cellG42 with named range AdTotal. Press Enter.

Filter the table to display data from only the Oncology and Gastrointestinalcategories and those pharmaceuticals that end with the suffix exor lux

In the Data tab, Sort and Filter group, select Filter.2. Click the drop-down arrow in cell A2.3. Click to clear the (Select All) check box, and then click to select the Gastrointestinaland Oncology check boxes.4. Click OK.5. Click the drop-down arrow in cell B2.6. Click the Text Filters > Ends With... option.7. In the Custom AutoFilterdialog box, type ex in the first box. Click Or. Choose ends with in the next drop-down and type lux in the last box.8. Click OK.

In the Expenses worksheet, in cell F54, create a formula that gets the AVERAGE of each Event item cost using the data in the Actual column heading in column D. Exclude the total rows

In the Expensesworksheet, select cellF54, in the Formulas tab, go to the Function Library and click Insert Function2. In the Search field enter Average, click Go. Under the Select a function field, select AVERAGE and click OK.3. Starting with theNumber1 field select or enter D6:D9, in theNumber2 field D13:D17, in the Number3 fieldD21:D23, in theNumber4 field D27:D30, in the Number5 fieldD34:D37, in theNumber6 field D41:D45, in the Number7 fieldD49:D50.4. Click OK.

In the June worksheet, in cells E4:E33use the IF formula to display a 1 if the values of the cells D4:D33 are greater than 2500, and a 0 if it is less than or equal to 2500.

In the June worksheet, highlight the cell E4.2. In the Formulas tab, go to the Function Libraryand click Insert Function.3. In the dialog box select the IF function and click Ok.4. In the new window for: Logical_test enter: D4>2500 and for Value_if_true: 1, and Value_if_false: 0 and click Ok.5. Highlight the cells E4: E33 and in the Home tab, go to the Editing group and click Fill and select Down.

In the Marketing Budget Planworksheet, in cells D10, D18 and D25create a formula that gathers the SUMof the data above in each of the cells.

In the Marketing Budget Plan worksheet, select cell D10. In the Formulas tab, locate the Function Library group, click the AutoSum dropdown, and select Sum.2. Enter cell range D5:D9as the range for the formula, and hit Enter.3. Repeat steps 1 to 2 for cell D18 using cells D13:D174. Repeat steps 1 to 2 for cell D25 using cells D21:D24

In the Marketing Budget Planworksheet, cell E51, insert a COUNTIFformula to count the data in cell range D43:D51 that is greater than 0

In the Marketing Budget Plan worksheet, select cell E51.2. In the Formula tab, locate the Function Library group, and clickInsert Function.3. In the dialog box, search for the COUNTIFfunction. Select it from the list, and click OK.4. In the Function Arguments dialog box, enter D43:D51 as the range, and ">0" as the Criteria, and click OK.

In the Profit - Loss Summary worksheet, name cell C5 EstIncomeand C6 EstExpenses. In cell C9 use a formula that subtracts EstExpenses from EstIncome.

In the Profit - Loss Summaryworksheet, select cell C5. Right click the cell, and select Define Name.2. In the dialog box, enter EstIncome. Click Ok.3. Repeat step 1 and 2 for cell C6and name EstExpensesbefore continuing to step 4.4. In cell C9 enter the formula =EstIncome-EstExpenses to subtract the values of the 2 named ranges. Press Enter.

In the Quarterly Sales worksheet, cell B9, enter a formula that consists of a cell reference to the Annual Salesworksheet, cell F16, multiplied by 1plus the growth value in the Quarterly Sales worksheet, cell B3.

In the Quarterly Salesworksheet, cell B9, begin a formula by entering the= character.2. Switch to the Annual Sales worksheet, and click on cell F16.3. Type *(1+ and then click on the Quarterly Sales worksheet, cell B3. Finally, close the formula by typing ). Press Enter

In the Top Performersworksheet, determine the highest sales number for 2010 in cell B2.

Open the Top Performersworksheet.2. Select cell B2.3. In the Formulastab, Function Library group, click the drop down forAutoSum. SelectMax.4. Ensure that your cursor is blinking between the parentheses in the text =MAX().5. Open the Sales 2010. Select cellsD3:D7 6. Hit the ENTERkey.

In the Combined Salesworksheet, correct the formula in the cell range D3:D7 so that it calculates the percentage of change from Sales 2010 to Sales 2011.

Open theCombined Salesworksheet.2. Select cell D3.3. In the Formula Bar, add an opening parenthesis after the text = and a closing parenthesis before the text /.4. Change'Combined Sales'!E3 to 'Sales 2010'!D35. Hit the ENTERkey.6. Click on cell D3again.7. Move your cursor over theAutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross.8. Click and drag down to cell D7.9. When you release the mouse button, the cell range should now display change percentage for the other representatives.

Sort the table on the current worksheet by Item Type and then by Item. Use ascending order for both sorts.

Right-click anywhere within the table Point to Sort Click Custom Sort Click the Sort by drop-down arrow Click Item Type Click the Add Levelbutton Click the Then bydrop-down arrow Click Item Click the OK button

In the June worksheet, enter a formula in cell B34that will get the SUM of the cell range B4:B33. Copy the formula to columns C and D.

Select cell B34. In the Formulastab, locate the Function Librarygroup, and click Insert Function.2. In the dialog box click SUM and click Ok.3. Enter B4:B33 in the Number1 area and click Ok.4. Select cells B34:D34. In the Home tab, locate the Editing group, select Fill, and click Right.

In cell D3 create a formula that gets the SUM of cells I6:I33.

Select cell D3, enter the formula,=SUM(I6:I33). Press Enter.

Correct the function in the Overall column. The values Profitableor Marginal should display, depending on whether the quarterly sales exceed research costs

Select cell K3.2. In the Formula Bar, replace the comma after SUM(D3,F3,H3,J3) with a greater than sign >. Add a quote " after the text Profitable and before the text Marginal.3. Hit the ENTER key.

In the Mileage Logworksheet, define the name for cell range I6:I33 as Miles. In cell D3, insert a SUMformula to show the sum of the Milesrange.

Select cell range I6:I33, in the Formulas tab, go to theDefined Names group and click the Define Name.2. In the New Name dialog box enter the name Milesand click Ok.3. In cell D3, enter the formula, =SUM(Miles). Press Enter.

Sort the table data by the Variantcolumn and then by the Yearcolumn in ascending order.

Select cells A5:D106.2. In the Data tab, Sort & Filter group, clickSort.3. In the Sort dialog box, from the Sort by drop down, chooseVariant. Click Add Level. From the Thenby drop down, chooseYear.4. Click Ok.

Remove all duplicates from the table based on the values in the Year and Value columns.

Select cellsA5:D106.2. In the Data tab,Data Tools group, click Remove Duplicates.3. In the Columnslist, clear theVariable andVariant check boxes.4. Click OK twice.

Filter the data so that only the people with a position of Developerare displayed.

Select the cell range A3:G33.2. in the Data tab, locate the Sort & Filter group and click Filter3. In C3 click the drop down next to Position. In the drop down, unselect all the check boxes except Developer, and click Ok.

In the June worksheet, in Cell E34, enter a formula that returns the AVERAGE value of all the numbers in the range B4:D33

Select the cellE34. Click theFormulas tab, locate theFunction Librarygroup, click theAuto Sumdropdown, and cick Average.2. In the formula, type B4:D33. Press Enter.