Tutorial 9 • Excel Business Functions

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.

CONCATENATE

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

SORT

The SORT function (Data > Sort) can be used to order spreadsheet entries alphabetically or numerically.

VLOOKUP

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.

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

PivotTables

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

1. Open the file “pivot-table.xlsx.” Note that Sheet 1 contains 214 rows of sales figures for fruits and vegetables by country and date—lots to digest!
2. Create a pivot table that lists the total amount of each fruit exported by all countries.
3. Place the cursor on the first cell of the table.
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.
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.
6. Note that the PivotTable is dynamic—the dropdown arrow in the PivotTable can be used to select the country for the report.

Instructional Video