Assigning Names to Data Tables
It’s always a good practice to assign a name to your data table used for creating pivot tables. This is especially true when you are using multiple data tables. It’s easy to pick the wrong data table when you consider the non-descript names that Excel assigns to tables (i.e. Table1, Table2, etc). If you have several tables with these names it will be difficult to remember what data is in which table. To illustrate this point, let’s look at an example:
You need to create a pivot table for XYZ Computer Sales but when you are looking for the data table for it, how are you going to know which of the folders shown below contain XYZ sales? Months may have passed since you created the table for XYZ. Unless you have a great memory, it will be difficult to know based on these Excel-assigned default table names.
It’s always a good practice to assign a name to your data table used for creating pivot tables. This is especially true when you are using multiple data tables. It’s easy to pick the wrong data table when you consider the non-descript names that Excel assigns to tables (i.e. Table1, Table2, etc). If you have several tables with these names it will be difficult to remember what data is in which table. To illustrate this point, let’s look at an example:
You need to create a pivot table for XYZ Computer Sales but when you are looking for the data table for it, how are you going to know which of the folders shown below contain XYZ sales? Months may have passed since you created the table for XYZ. Unless you have a great memory, it will be difficult to know based on these Excel-assigned default table names.
FIGURE 1
FIGURE 2
We see Table1 listed in FIGURE 1. That table is shown in FIGURE 2. We can save Table1 as XYZ COMPUTER SALES shown below in FIGURE 3. Saving the file with a descriptive title will help us locate the correct table later when we need to create a pivot table.
FIGURE 3
Practice Exercise 1: Create a Table and Assign a Name to It
|
Open data file: RANGE TO TABLE TO PIVOT TABLE2
STEP 1: Select the sheet name RANGE. We need to create a table from this range. Select Insert tab from the Ribbon shown in FIGURE 4; and select Table choice. STEP 2: The Create Table dialog box appears (FIGURE 5). The range in the Create Table dialog box should be $A$1:$L$109. If it’s different, correct it to that range by selecting the correct cells with your mouse. Press OK button and table is created.
The range has now been converted to our new table, as shown in FIGURE 6 below: |
 FIGURE 4
 FIGURE 5
|
STEP 3: We now have our table as shown in FIGURE 6.
FIGURE 6
STEP 4:
To customize this table first ensure you click mouse on a data cell of the table, as shown above. In Excel 2013, selecting a table cell automatically produced a Design Tools contextual tab. However, in Excel 2016 this does not occur. We must add a Table Design tab manually.
To customize this table first ensure you click mouse on a data cell of the table, as shown above. In Excel 2013, selecting a table cell automatically produced a Design Tools contextual tab. However, in Excel 2016 this does not occur. We must add a Table Design tab manually.
Select the File tab. In the Backstage view, select Options on the menu, then select Customize Ribbon. In the “Customize the Ribbon” section (shown in FIGURE 7 below) select the choice, “All tabs”. Scroll down and locate the category “Table Tools”, and check the box under it named “Table Design”. Select the OK button in the bottom-right corner. That process is shown below.
FIGURE 7
STEP 5: Click on the newly created tab, “Table Design”. You will see a new toolbar on the Ribbon, as shown in FIGURE 8 below. This entire toolbar is dedicated to customizing a table. Change the name of this table from “Table1” to “XYZ_SALES” (you must insert underscores between the words; no spaces are allowed).
FIGURE 8
|
STEP 6: We can also see that there are several customization features that can change the look of our table, including the various controls in the “Table Style Options” section of the toolbar. For example, If you wish to have a more separative look in the columns, you can check the box, “Banded Columns.” We do not need the filter buttons (downward pointing arrows) so let’s uncheck the box, “Filter Button” as shown in FIGURE 9. As you can see, all of the filter buttons are gone from the table.
|
 FIGURE 9
|
In this practice exercise, we have converted a range to a table. From here, the next step is to create a pivot table from a table such as the one we have created in the above steps. In the next practice exercise we will be creating a pivot table from a table.
This is the end of the section. To continue, go to Module 1 Section 1.3 Creating a Pivot Table; Practice Exercise 2