3.3 Structure data within Excel tables - Video Tutorials & Practice Problems

<v Instructor>Most Excel models utilize tabular data,</v> which is typically structured using built-in Excel tables. This is probably one of the most powerful Excel capabilities for data analysis. Organizing our data within an Excel table provides several inherent capabilities. We will review some of them during this sublesson. To start with, let's create an Excel table using this data set. To do that, we select the data range and then select one of the Format as Table options under the Home tab of the ribbon bar. Alternatively, we can use the Control + T keyboard shortcut. And then confirm if our table range has headers, and then click OK. Once created, the Excel table allows for more efficient formatting, data entry, and updates. Several quick formatting options are available under the Table Design tab in the ribbon bar. Here, we can customize the look and feel for the entire table or for specific rows and columns within the table. To add data to this Excel table, we can simply type in the new values in the first row after the table, or we can add the new value in the last cell and then press the Tab key. The last cell is typically the lower right cell of the table. For inserting new rows between existing rows, we can right-click anywhere in the table and use one of the options under the Insert menu. Similarly, we can add new columns for our tables as well. If we want to remove data from our table, we can either select the row and delete it from the worksheet, or we can right-click in a row within the table and select an option from the Delete context menu. However, in some cases, other adjoining data on the worksheet might prevent us from using one of the above methods to add or delete data from our Excel table. It is thus important that we plan around such constraints when designing our data structures and model layout. As an example, since we have other data within this row, we will not be able to delete the entire row from the worksheet. Similarly, since there is another table with more columns below our data table here, Excel will not allow us to delete a row from our data table using the context menu option. (computer chimes) In these cases, we may be able to move some of our data within the table to overwrite the rows that we want to delete and then use the sizing handle for our table to resize the table layout. We can also temporarily move our data table to another location which does not have any adjoining data, make the required changes, and then move it back to the original location. However, it is usually best to plan for such constraints when designing our table layouts and other data structures within our models. We can use a similar process for deleting columns from our data tables as well. Once we have structured our Excel table and have captured the required data within it, we can leverage some of its other capabilities that are extremely useful within a data analysis context. Apart from the quick style and formatting options that we highlighted earlier, when working with larger data sets, an important and often ignored benefit of Excel tables is the support for various task and navigation shortcuts. These shortcuts immensely improve our productivity when entering, navigating, or viewing data within our tables. Now let's review some of these options. Upon selecting any cell within the table, we can use the key combination Control + A to select all data within the table. We can press Control + A a second time to select the headers and total rows in addition to the data rows. We can also use the Control + arrow keys to navigate to the start or end of a continuous data segment within our table. And pressing Shift with this key combination allows us to select the same data as we navigate through it. In some cases, we might want to ignore the gaps within our data and select the entire row or column. As you can see in column one, by using the Control, Shift, and down arrow key, it only selects the first two rows in the column. If we wanted to select the entire column, we can use the Control + space keyboard shortcut, which ignores any gaps within the data set. We can use a similar shortcut with Shift + space bar, which selects the entire row, ignoring any gaps within the data. These shortcuts will also work if we had multiple rows or columns selected. So in this case, if we select the first two rows in the table and use the shortcut Shift + space, it will span the selection across both those rows. As we enter data within a table, we can also use the Alt + down arrow key to display other text values entered within the current column. This feature can assist with easy and consistent data entry. Please note that this shortcut ignores any numeric values within the current column, including dates and Booleans. Using the key combination Control, Shift, and plus sign, we can also add a row or column next to our current cell. If we select two cells in the same row and use the key combination Control, Shift, and plus, it will add another row above the currently selected cells. Similarly, if we select two cells within the same column and use the same key combination, Control, Shift, and plus, it will enter a new column to the left of the currently selected cells. We can delete rows or columns using a similar key combination, Control and minus sign. All Excel tables by default are created with a header row. All column headers in this row are pre-configured for sort and filter capabilities. As we scroll through larger data sets or other content on the worksheet, the headers for our data table are visible at the top of each column, if the currently selected cell is within the Excel table. If we select a cell outside the data table, the Excel column headers revert back to the regular column references. We can optionally add a total row at the bottom of our table by selecting the cell immediately below the column we want to aggregate and then selecting the AutoSum option from the Editing group on the Home menu tab. This row can also be added or removed from the Table Styles option under the Table Design menu tab. Once added, we can change the aggregation function using the drop-down list for any cell within the total row. New aggregations can also be added using the same drop-down list. Quite often, we will also need to create calculated columns within our tables. These are created by entering your formula within any cell of the table and then pressing Enter. If there is no other data within the column or if it is already a calculated column, the newly entered formula is auto-filled for all cells within the edited column, without the need to manually copy or fill it across other cells. We can use Undo immediately after such auto-fill for scenarios where this behavior might not be desired. When writing formulas within or outside our Excel tables, we can easily reference data within the tables by using dynamic cell ranges that are automatically created and named for each table and its columns. These cell ranges referring to different parts of an Excel table are referred to as structured references and expand automatically when new data is added to the table. Structured references allow formulas to be read, understood, and maintained much more efficiently than explicit cell references and, in some cases, even define names. In Microsoft 365 Excel, these references exhibit spill behavior and can be used with the implicit intersection operator. We will discuss these concepts in more detail later in this lesson. For now, let's look at some of the structured references provided with Excel tables. Structured references for Excel tables have a consistent syntax. The table name is followed by either a column specifier to reference a specific column, an item specifier to reference a specific portion of the table, or a table specifier to reference a specific part of an item specifier. Furthermore, all these specifiers need to be enclosed within square brackets. Now let's look at some of these in action. If we wanted to reference the entire table, including the column headers, data, and totals, we will use the All item specifier for the table. As we enter the formula, you can see the table data spill into the columns and rows, starting from the formula cell. This is made possible by the spill behavior within Excel 365. We will discuss this in further detail later on in this lesson. Similarly, if we want to reference only the headers for this table, we will use the Headers item specifier and the Totals item specifier for the total row. For referencing the data portion of this table, we could either use the Data item specifier or simply type in the name of the table and press Enter. For referencing the data values within a specific column, we will use the corresponding column specifier. If we wanted to get all values in column two, including the column headers, data, and totals, we will create a table specifier with the All item specifier and a Column2 of reference. As mentioned earlier, structured references can also be combined with the implicit intersection operator. Although this topic will be discussed in further detail later in this course, it basically retrieves the values from the reference data in the same row as the formula cell. So in this specific case, it's retrieving the values from the ET table Table1 from Column2 in the same row as this formula cell. This can be validated. Once we compare the retrieved value with all values in column two for the previous structured reference reviewed. The implicit intersection operator can also be used to retrieve all values in the table within this row of the Excel worksheet. Similarly, we can use the This Row item specifier to retrieve all values in the Excel table on this specific worksheet row. So as you can see, structured references provide a variety of ways to dynamically access data within Excel tables. This helps immensely when developing complex Excel models using tabular data sets. Another benefit of Excel tables is the ability to easily summarize, filter, or display your data using PivotTables, dynamic charts, Slicers, Timelines, and more. Some of these capabilities will be discussed during later lessons in this course. With Excel tables, we can also leverage other advanced capabilities within Excel, like PowerQuery, Relationships, and Data Model. However, these capabilities are not within the scope of this course and may be covered within other intermediate or advanced Excel courses.