{"id":336,"date":"2020-04-18T20:06:45","date_gmt":"2020-04-18T20:06:45","guid":{"rendered":"https:\/\/pressbooks.library.ryerson.ca\/ebooks\/chapter\/tutorial-2-%e2%80%a2-excel-business-functions\/"},"modified":"2021-07-20T13:22:02","modified_gmt":"2021-07-20T13:22:02","slug":"tutorial-9-excel-business-functions","status":"publish","type":"chapter","link":"https:\/\/pressbooks.library.torontomu.ca\/ebooks\/chapter\/tutorial-9-excel-business-functions\/","title":{"raw":"Tutorial 9 \u2022 Excel Business Functions","rendered":"Tutorial 9 \u2022 Excel Business Functions"},"content":{"raw":"This tutorial explains some of the Excel functions identified by Ben Dugas, product manager at eBook publisher Rakuten Kobo, during a guest lecture he presented in the 2018 summer eBooks class.\r\n

CONCATENATE<\/h2>\r\nThe CONCATENATE function combines two or more columns into one. Text (including spaces) goes in quotes and is separated from variables by \"&,\" e.g., \"=CONCATENATE(B3&\" \"&A3\").\r\n

\"Screen<\/p>\r\n\r\n

SORT<\/h2>\r\nThe SORT function (Data > Sort) can be used to order spreadsheet entries alphabetically or numerically.\r\n

\"Screen<\/p>\r\n\r\n

VLOOKUP<\/h2>\r\nThe VLOOKUP function provides a way to look up data in an Excel table. VLOOKUP can be combined with ActiveX interactive objects to create dynamic lookups.\r\n
    \r\n \t
  1. Open the file, \u201cvlookup.xlsx.\u201d Note that the file contains a list of sales representatives and their annual sales.<\/li>\r\n \t
  2. To the right of the last column, in cell G2, create a field called \u201cID\u201d and to the right of that, in cell H2, set the first value to \u201c1.\u201d<\/li>\r\n \t
  3. Below the \u201cID\u201d and \u201c1\u201d cells, in cell G3, create another field, \u201cSales,\u201d and to the right of that, in cell H3, insert an \u201c=VLOOKUP\u201d function, which has 4 arguments:\r\n
      \r\n \t
    • cell containing the desired value (H2)<\/li>\r\n \t
    • cells containing the data range (B3:E9)<\/li>\r\n \t
    • column in the range with the desired data to report (4)<\/li>\r\n \t
    • whether an approximate match is acceptable to the desired value (FALSE)<\/li>\r\n<\/ul>\r\n<\/li>\r\n \t
    • The function should read =VLOOKUP(H2,B3:E9,4,FALSE)<\/li>\r\n \t
    • Note that Excel reports the sales volume for Sales Representative #1.<\/li>\r\n \t
    • To make an interactive VLOOKUP, turn on the Developer Tab in Excel (Preferences > View > Developer Tab).<\/li>\r\n \t
    • Insert a \u201cSpinner\u201d (up\/down arrow) to the right of the VLOOKUP fields.<\/li>\r\n \t
    • Right-click the Spinner and set Format Control to Cell Link H2. Also set the appropriate Minimum and Maximum values.<\/li>\r\n \t
    • Click on another cell. Note that the Spinner will increase or decrease the lookup value in Cell H2.<\/li>\r\n<\/ol>\r\n\"Screen\r\n

      PivotTables<\/h2>\r\nPivotTables (Insert > PivotTable) are a powerful tool for creating summaries from large amounts of data that would otherwise be too vast to interpret.\r\n
        \r\n \t
      1. Open the file \u201cpivot-table.xlsx.\u201d Note that Sheet 1 contains 214 rows of sales figures for fruits and vegetables by country and date\u2014lots to digest!<\/li>\r\n \t
      2. Create a pivot table that lists the total amount of each fruit exported by all countries.<\/li>\r\n \t
      3. Place the cursor on the first cell of the table.<\/li>\r\n \t
      4. Select Insert > PivotTable, click button for Table or Range. When you put the cursor in the field Table\/Range, Excel selects the entire table for the input and asks you where to put the table. Select a cell to the right of the table. Note that a PivotTable object appears, along with a sidebar of PivotTable Fields.<\/li>\r\n \t
      5. In the sidebar, rearrange the Field Names as shown in the screen capture below, i.e., Filters: Country; Rows: Product; Values: Sum of Amount.<\/li>\r\n \t
      6. Note that the PivotTable is dynamic\u2014the dropdown arrow in the PivotTable can be used to select the country for the report.<\/li>\r\n<\/ol>\r\n

        \"Screen<\/p>\r\n\r\n

        Instructional Video<\/h2>\r\n[embed]https:\/\/youtu.be\/SiX8KBArHoA[\/embed]","rendered":"

        This tutorial explains some of the Excel functions identified by Ben Dugas, product manager at eBook publisher Rakuten Kobo, during a guest lecture he presented in the 2018 summer eBooks class.<\/p>\n

        CONCATENATE<\/h2>\n

        The CONCATENATE function combines two or more columns into one. Text (including spaces) goes in quotes and is separated from variables by “&,” e.g., “=CONCATENATE(B3&” “&A3”).<\/p>\n

        \"Screen<\/p>\n

        SORT<\/h2>\n

        The SORT function (Data > Sort) can be used to order spreadsheet entries alphabetically or numerically.<\/p>\n

        \"Screen<\/p>\n

        VLOOKUP<\/h2>\n

        The VLOOKUP function provides a way to look up data in an Excel table. VLOOKUP can be combined with ActiveX interactive objects to create dynamic lookups.<\/p>\n

          \n
        1. Open the file, \u201cvlookup.xlsx.\u201d Note that the file contains a list of sales representatives and their annual sales.<\/li>\n
        2. To the right of the last column, in cell G2, create a field called \u201cID\u201d and to the right of that, in cell H2, set the first value to \u201c1.\u201d<\/li>\n
        3. Below the \u201cID\u201d and \u201c1\u201d cells, in cell G3, create another field, \u201cSales,\u201d and to the right of that, in cell H3, insert an \u201c=VLOOKUP\u201d function, which has 4 arguments:\n
            \n
          • cell containing the desired value (H2)<\/li>\n
          • cells containing the data range (B3:E9)<\/li>\n
          • column in the range with the desired data to report (4)<\/li>\n
          • whether an approximate match is acceptable to the desired value (FALSE)<\/li>\n<\/ul>\n<\/li>\n
          • The function should read =VLOOKUP(H2,B3:E9,4,FALSE)<\/li>\n
          • Note that Excel reports the sales volume for Sales Representative #1.<\/li>\n
          • To make an interactive VLOOKUP, turn on the Developer Tab in Excel (Preferences > View > Developer Tab).<\/li>\n
          • Insert a \u201cSpinner\u201d (up\/down arrow) to the right of the VLOOKUP fields.<\/li>\n
          • Right-click the Spinner and set Format Control to Cell Link H2. Also set the appropriate Minimum and Maximum values.<\/li>\n
          • Click on another cell. Note that the Spinner will increase or decrease the lookup value in Cell H2.<\/li>\n<\/ol>\n

            \"Screen<\/p>\n

            PivotTables<\/h2>\n

            PivotTables (Insert > PivotTable) are a powerful tool for creating summaries from large amounts of data that would otherwise be too vast to interpret.<\/p>\n

              \n
            1. Open the file \u201cpivot-table.xlsx.\u201d Note that Sheet 1 contains 214 rows of sales figures for fruits and vegetables by country and date\u2014lots to digest!<\/li>\n
            2. Create a pivot table that lists the total amount of each fruit exported by all countries.<\/li>\n
            3. Place the cursor on the first cell of the table.<\/li>\n
            4. Select Insert > PivotTable, click button for Table or Range. When you put the cursor in the field Table\/Range, Excel selects the entire table for the input and asks you where to put the table. Select a cell to the right of the table. Note that a PivotTable object appears, along with a sidebar of PivotTable Fields.<\/li>\n
            5. In the sidebar, rearrange the Field Names as shown in the screen capture below, i.e., Filters: Country; Rows: Product; Values: Sum of Amount.<\/li>\n
            6. Note that the PivotTable is dynamic\u2014the dropdown arrow in the PivotTable can be used to select the country for the report.<\/li>\n<\/ol>\n

              \"Screen<\/p>\n

              Instructional Video<\/h2>\n