Creating a Table

In this example, we will use the data in range A1:C8 on the [Discounts] sheet (Figure 4-2).

To convert this range to a table

1. 1.

   Click a cell within the range.

    
2. 2.

   Click Insert ➤ Table or press Ctrl + T.

   Note You can also click Home ➤ Format as Table and click a style from the list.

    
3. 3.

   In the Create Table window (Figure 4-3), check that the range is correct and that the My table has headers box is checked (there are headers in the first row of the range). Click OK.

    

That’s it! The range is now formatted as a table (Figure 4-4).

The default style is applied. This style keeps any existing formatting that was applied to the range, such as the orange fill color in the header row.

It applies the elements of the default table style to areas where no previous formatting was specified. This includes the blue banded rows, blue border, and white font in the header row.

In the bottom-right corner of the table is a blue resize handle (Figure 4-4). This identifies the last cell of a table and can be used to quickly resize it if necessary.

Changing or Removing Table Styles

You can easily change or remove the table style if you are not contented with the default style applied. You can even create your own styles, but we will not be looking at this in the book.

Personally, I like to remove the table style, so will demonstrate that:

1. 1.

   Click a cell within the table.

    
2. 2.

   Click Table Design and then the More button in the corner of the Styles gallery (Figure 4-5).

    

1. 3.

   The gallery expands. You can choose a different style, create your own, or clear the style. To clear a style, click Clear at the bottom of the gallery, or click the None option (Figure 4-6).

    

In Figure 4-7, the table style has been removed. Although it does not have any new formatting applied, it is still formatted as a table.

You can see the blue resize handle in the bottom-right corner. Also, the Table Design tab appears when the table is active. These are two signs that the range is formatted as a table.

Naming the Table

It is very important to give your table a meaningful name. This will be used to reference it from a formula, so you want it to be distinct to this table and easy for you and others to use.

1. 1.

   Click a cell within the table.

    
2. 2.

   Click Table Design and enter the name you want to use in the Table Name box (Figure 4-8). In this example, it has been named “tblCustomerSales”.

    

Working with Tables

When working with a workbook that contains tables, you may need to find, view, and edit these tables at some point.

The easiest way to view the tables in a workbook is through the Name Manager . There is even a filter in the Name Manager making it easy to only focus on the tables (Figure 4-9).

To open the Name Manager , click Formulas ➤ Name Manager.

From the Name Manager , you can view all tables and see what they refer to.

The quickest way to navigate to a table in a workbook is either using the Name Box to the left of the Formula Bar (Figure 4-10).

Or by pressing F5 or Ctrl + G to open the Go To window (Figure 4-11). Select the table and click OK.

The Magic of @

Let’s begin by using a formula that references a single cell of the table. We will use the [tblCustomerSales] table that was just created.

In cell C2, the following IF function has been used to calculate the discount on the customer totals, but only if they reach the threshold value (Figure 4-12):

=IF([@Total]>=rngThreshold,[@Total]*(1-rngDiscount),[@Total])

When clicking cell B2 while writing the formula, it is referenced as [@Total]. Total is the name of the column, the @ symbol refers to the same row within the table, and it is all enclosed in square brackets.

The names [rngThreshold] and [rngDiscount] refer to cells F2 and F4, respectively.

This is a very meaningful reference. Using the column header [Total] has greater meaning than column B.

And what is extra cool is that every cell in the [Inc Discount] column (column C) contains the same formula. If we used a range, the formulas would all be a little different as they would reference B3, B4, B5, and so on.

Another awesome feature when writing formulas that refer to a single cell on the same row in a table is that they automatically fill down to the last row. No need to click and drag or double-click that fill handle.

Let’s see another example of the magic @ symbol in action. In this example, we have the table shown in Figure 4-13. This table is named [tblProducts].

We will write a formula in the [Percentage] column that calculates the percentage contribution that the sales of each product have made to the grand total.

In cell C2, the following formula has been used (Figure 4-14):

=[@Total]/SUM([Total])

This formula includes a reference to a single product total, shown as [@Total], and a reference to the column, shown as [Total] (without the @ sign).

I think this is a good example of the use of the magic @ sign and how readable these formulas are when referencing table data.

When I read the @ symbol in my head, I like to say the word “this.” So, the formula reads “this total divided by the sum of totals” to me.

The cells where the formula is input are formatted as a percentage. However, Excel appears to change the cell formatting when this formula is entered. So, you may be required to format the [Percentage] column as percentages again.

Referencing Table Elements

A table is made up of a few elements. These include the header row, total row, the columns, the data minus the headers, and everything (data and headers). We will see examples of referencing these different elements as we progress through the book.

You can reference these table elements by selecting or clicking with your mouse or by typing the reference directly into the formula. I am a big fan of typing table references. It is so fast and easy. Let’s look at both methods.

For this example, we have a table on the [Data] sheet of the workbook named [tblSales]. A snapshot of this table is shown in Figure 4-15.

On the [Report] worksheet, we will use the SUMIFS function (covered in Chapter 8) to sum the [Total] column for the sales of a specific product.

The following formula has been entered into cell C3 (Figure 4-16). It sums the [Total] column, only for sales of the product entered in cell B3.

=SUMIFS(tblSales[Total],tblSales[Product],B3)

The name of the table precedes the column name as we are referencing the column from outside of the table.

To reference the columns, you can click the column header. This is great, but you must be careful.

Figure 4-17 shows the [Product] column of the table being clicked. The black arrow indicates that I am in the correct position to select the table’s column.

If you position the cursor a little too high, you may select the sheet column by mistake. Figure 4-18 shows the black arrow positioned on column C of the sheet and not the [Product] column of the table.

The sheet column header changes to a green fill to illustrate this. However, this is easy to miss during the excitement of writing formulas.

A better method could be to type the reference. Excel assists this process by providing a list of all table elements as you type. Figure 4-19 shows this list as I type the reference to the [Product] column in the SUMIFS function.

The list appears when the opening square bracket is typed.

Make Table Column and Cell References Absolute

Single column table references such as [@Product] and [Product] are relative by default, just like references to cells on a worksheet such as B2. Unfortunately, it is a little more awkward to make table references absolute.

Let’s see an example. We have the following SUMIFS function shown in Figure 4-20. It sums the [Total] column from [tblSales] for the product entered in cell E3 and the store in cell F2.

=SUMIFS(tblSales[Total],tblSales[Product],$E$3,tblSales[Store],F2)

This works great!

When the formula is filled to cell G3 to the right by dragging the fill handle, the formula goes horribly wrong (Figure 4-21). The following is the resulting formula:

=SUMIFS(tblSales[Order ID],tblSales[Store],$E$3,tblSales[Units Sold],G2)

All three table column references have changed.

The columns of [tblSales] can be seen in Figure 4-22. The [Product] column has changed to [Store], the [Store] column changed to [Units Sold], and the [Total] column changed to [Order ID]. As it is the last column of the table, the reference shifted back to the first column.

Now, there are a few ways to prevent this behavior.

One method is to copy or fill the formula using an alternative method to the classic drag of the fill handle.

You can click in cell G3 and press Ctrl + R to fill the formula right; the table references do not change. This is a terrific alternative.

Or you can copy the formula from the Formula Bar of cell F3 and paste it in cell G3. The table references do not change using this method either.

Finally, we could make the references absolute. To do this, you change the reference to a range and add an extra set of square brackets.

Figure 4-23 shows the SUMIFS function with absolute table references. When the formula is filled to the right by dragging the fill handle, the references no longer change.

Fixing the table columns in the formula is a more reliable method. It provides protection against other users of the sheet who may copy the formula.

However, the formula is more awkward to read, so it is useful to know the alternative methods such as Ctrl + R. Different situations can warrant a different approach.

Dynamic Lists

In the previous chapter, there was an example of creating a dynamic name using a formula. This name was then used as the source of a Data Validation list.

Tables are dynamic, so offer a wonderful alternative to this approach. Now, unfortunately tables do not work directly with Data Validation. You cannot enter a formula that uses the structured references of a table within a Data Validation rule directly. So, we will create a defined name for the table data and then use the name for the Data Validation list.

Figure 4-24 shows a list of products that has been formatted as a table named [tblProductList].

To name the table data

1. 1.

   Select the data range of the table, A2:A21.

    
2. 2.

   Click Formulas ➤ Define Name.

    
3. 3.

   Type “lstProducts” for the Name (Figure 4-25). Notice the table reference in the Refers to field.

    

We can then use this name for the source of the Data Validation list. Because the name understands the table, it works as a translator between the table and Data Validation features of Excel.

Let’s use the list for cell B3 of the [Report] sheet:

1. 1.

   Click cell B3.

    
2. 2.

   Click Data ➤ Data Validation.

    
3. 3.

   From the Settings tab, click the Allow list and select List.

    
4. 4.

   Type “=lstProducts” in the Source field and click OK (Figure 4-26).

    

The Data Validation list is set up (Figure 4-27).

If more products are added to [tblProductList], they are automatically included in the Data Validation list.

There have been some major improvements made to Data Validation in recent months. So, at the time of writing this book, although we cannot directly use a Data Validation list from table data on another sheet, it may be a functional feature in modern Excel versions soon.

Such advancements will only be available to Excel for Microsoft 365 (Windows and Mac) and Excel for the Web, meaning this technique of using defined names is still very applicable.

Defined names have always had this special power to get two different Excel features working together.

Dynamic Charts

Using tables as the source of charts is a simple way to make your charts automatically update when new data is added to the table.

In Figure 4-28, we have a line chart that is using a table named [tblMonthlySales] as its source.

The table is in range A1:B6, and you can see that range highlighted when the chart is selected, showing that they are connected.

If we look at the chart data though, it does not reference the table that it is connected to.

Select the chart and click Chart Design ➤ Select Data.

In the Chart data range field (Figure 4-29), the chart source is shown as

=Chart!$A$1:$B$6

Although it does not directly mention [tblMonthlySales], it is connected to it. This is a little confusing, but you get used to it.

Because the range that the chart is using for its source is formatted as a table, when new data is added, the chart automatically updates (Figure 4-30).

Tables are an incredible feature of Excel, and many of the formula examples in this book will be using table references.