To determine the number of member and nonmember registrants for the VCU School of Business Emerging Technology Center’s quarterly Business Intelligence Symposium, we’ll create an Excel workbook with three tabs as instructed: “Notes,” “Prospect List (data),” and “Registrants Pivot Table.” The goal is to showcase the total corporate member and total corporate nonmember counts on the Prospect list, along with a breakdown of the total members by company. We will also highlight the total number of nonmembers and members in the pivot table.

Here’s a step-by-step guide on how to accomplish this:

Step 1: Create an Excel Workbook

Open Microsoft Excel and create a new workbook.

Rename the first sheet as “Notes.”

Import the data from the Prospect List by clicking on “Data” and then “Get Data” or by copying and pasting the data from the provided Google Sheets link into a new sheet named “Prospect List (data).”

Step 2: Create the Pivot Table

Click on the “Prospect List (data)” sheet.

Select any cell within the data range.

Go to the “Insert” tab and click on “PivotTable.”

In the “Create PivotTable” dialog box, ensure that the data range is correctly selected and choose to place the PivotTable in a new worksheet.

Click “OK” to create a new sheet with the PivotTable.

Step 3: Configure the Pivot Table

In the PivotTable Field List, drag and drop the following fields:

Drag “Registration Type” to the “Rows” area.

Drag “Company Name” to the “Rows” area below “Registration Type.”

Drag “Name” to the “Values” area (this will count the number of registrants).

Ensure that “Name” is set to count (by default, it will count the number of non-blank entries).

Drag “Registration Type” again to the “Values” area, under “Name.”

Change the value calculation for “Registration Type” to “Count” (this will count the number of members and nonmembers).

Step 4: Highlight Member and Nonmember Counts

In the PivotTable, you will see a breakdown of the total number of members by company under “Registration Type = Corporate Member.”

To highlight the total number of nonmembers and members:

Click on the cell where “Registration Type = Corporate Non-Member” is displayed.

Apply cell formatting, such as a different background color or font color, to visually distinguish it.

Do the same for the cell where “Registration Type = Corporate Member” is displayed.

Step 5: Finalize and Save

Review the PivotTable to ensure that it accurately displays the total corporate member and nonmember counts, as well as the member counts by company.

Save your Excel workbook with an appropriate filename.

In conclusion, we have successfully created an Excel workbook with a PivotTable that displays the total corporate member and nonmember counts on the Prospect list for the VCU School of Business Emerging Technology Center’s Business Intelligence Symposium. We have also highlighted the relevant cells to make the information easily accessible. This report provides a clear overview of registration statistics for the event, which can be valuable for planning and analysis.