Sur La [Excel] Table

FineDiningLG

Generally, I’m an easy-going guy – very content with a good burger and a good brew. Every now and again, however, it’s fun to put on my fines and kick up my heels.

I’ve worked as a controller (spreadsheet jockey) for a few food service companies, so I have been around and pitched in on more than a few catering events. I’m here to tell you, it’s tough work, with excruciatingly long hours on your feet. I’m used to shining a seat, not being on my feet for 10-12 hours at a clip.

So, next time you’re at a great event, make sure you let the folks doing the heavy lifting that you appreciate their hard work so that you can have a good time.

Today’s post, however, is not about extraordinary events and culinary creations. This post is about Excel Tables – an underused feature in Excel. Over on the LinkedIn Excel Groups, Craig Hatmaker is leading the charge to make more folks aware of Excel Tables and their advantages. I’m curious as why more folks do not use Excel Tables? I look forward to any all comments below as to why this may be.

Create An Excel Table

You may Get External Data from Access, SharePoint, etc… and the resulting data in Excel will be presented in an Excel Table automatically. For now, I will simply create an Excel Table from a Range. However, in production these days, I keep all data external from Excel in Access, SharePoint or SQL Database.

I cooked up some data using a Random Data Generator from Dick Kusleika over at Daily Dose of Excel. I added some formatting to make it more palatable on a web browser. Now I want to convert the Range into an Excel Table.

RangeForTable

I clicked on any cell in the Range, clicked on the Insert Tab on the Ribbon and clicked on the Table icon in the Tables Group as indicated by the arrow in the screen shot. If you are a keyboard person, you may press [Ctrl] + [T] to activate the Create Table Dialog.

TableInsertComment

The continuous range is automatically selected as indicated by the “dancing ants”. The Create Table Dialog opens. The Range Selection Tool displays the selected range while also allowing you to use the selector tool to select your own range should you choose to do so. The “My table has headers” checkbox is ticked automatically.

CreateTableDia

Click “Ok” when you are finished and your Range is converted into an Excel Table. The default Table Style is applied to the table

Table3

Ribbon – Design Tab : Table Tools

When you click on an Excel Table, the Design Tab : Table Tools becomes active on the Ribbon. The Design Tab : Table Tools gives you many tools for working with properties styles and options of the Excel Table. The tab disappears when you are not clicked on a cell in the Excel Table.

TableTools1

TableTools2

Excel Table – Name

Please note in the first screen shot of the Ribbon, in the Properties Group, the name of the Excel Table is Table2. You may change this and I recommend you do to something more meaningful. For demonstration purposes, I’ll name this Excel Table -> tblRevenues.

Structured References – Calculated Columns

Let’s take a look at adding a calculation to the Excel Table. Suppose we want to know what happens to Total Revenue if we increase it by 10 %. I move to the next available column, and enter a description in the header row

CalcColumn1

Look at what happens when I confirm the description in the header row by pressing enter. The Table Style formatting is automatically filled down the new column.

CalcColumn2

Now I need to enter a formula to increase the Total Revenue of each row by 10 %. In cell E2, I enter = and click on D2, Excel enters the Column Specifier, “[@TotalRevenue]” for me.

CalcColumn6

I complete the formula by multiplying by 1.1, pressing enter, and then using the fill handle in the lower right corner of the cell to send the formula down.

CalcColumn4

Look Ma’ – No Dynamic Ranges

One of the most frequent errors we read about is data being missed by a formula using by a cell reference formula: =SUM(Sheet1!$A$2:$A$10)

One way around this is to use Dynamic Named Ranges (DNR). Daniel Ferry shows us how to create DNR’s using the INDEX() function in his epic post, The Imposing Index. Definitely check it out.

But with Excel Tables, we do not need to create Dynamic Named Ranges. As we add data to the Excel Table it is added to the table and included in any analyses of the data.

Structured Reference – Analytical Example

Let’s suppose we are interested in a summary total based on a few criteria such as company name, account number and date range. We can use the SUMIFS() function with our Excel Table and Structured references instead of cell references:

=SUMIFS(tblData[Amount],tblData[Company],"="&A2,tblData[Account],"="&B2,tblData[Date],">="&C2,tblData[Date],"<="&D2)

SummaryAmount

What happens if I add 1 more record that meets all of the criteria for $1,000? Wblanke expect the new total to be $10,562.36.

CalcColumn7

SummaryAmount2

Perfect! $10,562.36 as expected.

Does The Formula Make Your Eyeballs Spin?

Let’s take another look.

=SUMIFS(tblData[Amount],tblData[Company],"="&A2,tblData[Account],"="&B2,tblData[Date],">="&C2,tblData[Date],"<="&D2)

Kris Szabo once commented on one of the LinkedIn Groups that the formula made her eyeballs spin. Fair enough – Let’s try wrapping the formula to see if that makes it easier to read.

  • Click on the cell with the formula so that the formula is visible in the formula bar
  • Move the cursor to the beginning of each occurrence of the table name, “tblData”
  • Press [Alt] + [Enter] on the keyboard to force all subsequent text to the next line
  • Press [Spacebar] to move the text until it is in the correct column

When complete, this is what the formula should look like in the formula bar

=SUMIFS(
        tblData[Amount],
        tblData[Company],"="&A2,
        tblData[Account],"="&B2,
        tblData[Date],">="&C2,
        tblData[Date],"<="&D2
       )

Much easier to read – what do you think?

The Peter Principle – Update Sept. 6, 2014

Over on the ExcelHero Group on LinkedIn, Peter Bartholomew correctly pointed out that we may use a 2nd Excel Table and then refer to the variable components through the use of the Table Object Nomenclature instead of using the cell references as I did above.

The revised formula using the Table Object Nomenclature:

= SUMIFS( tblData[Amount], 
          tblData[Company], "=" & [@Company], 
          tblData[Account], "=" & [@Account], 
          tblData[Date], ">=" & [@StartDate], 
          tblData[Date], "<=" & [@EndDate] 
        ) 

My take:

I like the 2 Table approach. However, the horizontal (ColumnWise) layout does not feel natural. Many, perhaps most, user-input forms are designed with a vertical (RowWise) layout.

Additionally, in some reporting situations, we may want to refer to the same criteria without repeating the criteria (Some folks find the repetition “ugly” or at least “irritating”). For example we may want to hold constant “Company” and “Account” through the use of Absolute References while varying the Start and End dates to examine account balances through the periods of the fiscal year or some span of time.

Is This A Segue To DAX?

DAX is Dynamic Analysis eXpressions. It is the formula language behind Data Models with Power Pivot as well as a query language for working with Tabular Databases. Here is a very simple DAX Formula that I borrowed from Chandoo’s site: SUM(sales[sale amount]). Looks a lot like the syntax for Structured References – doesn’t it? I’m not saying that if you know Excel Tables w/ Structured References that you will know everything there is to know about DAX. I’m simply saying that if you can work with Excel Tables w/ Structured References, it will help you understand DAX a bit more.

Next Up – VBA for Excel Tables (ListObjects)

I covered Excel Tables here as a precursor to looking into the ListObject Object Model in VBA. The ListObject is parlance in VBA for Excel Tables – I suspect a holdover from Lists the original name for Excel Tables when they were first introduced in Excel 2003. Have a look at the Members (Properties and Methods) of the ListObject Object Model and stay tuned for my next post on ListObjects.

Tidy Up

    Final Thoughts

    That’s it for now. I really like Excel Tables and I think you will like them even more once I show some VBA code for ListObjects. Remember, Excel Tables and ListObjects are the same thing.

    There are several nice features about Excel Tables, but for me the best part is automatically adding data to the table so that all data is included in any analyses. What do you like most?

, , ,
Trackback

4 comments untill now

  1. Hi Winston,

    Great article! That’s a nice way to wrap formulas to make them easier to read. Another option is to hit the Function button to the left of the formula bar to open the Function Arguments window. There you can see all the arguments listed vertically in text boxes. I tend to forget about this feature, but it does come in handy when your formulas get really long.

    I’m looking forward to the next post on VBA with tables. One example that might be helpful to users is a technique for looping through each row in a column of a table and getting the value of another column in the same row of the table. That’s a procedure that I use often when developing applications that reference tables. I would be interested to know the most efficient way to go about it, as I’m not sure my method is the best.

    Thanks for the link!

    Jon

  2. Hi Jon,

    Thanks for your kind comments. I try to stay away from looping through cells in a range whenever possible, opting instead for AutoFilter or AdvancedFilter.

  3. […] Sur La [Excel] Table […]

  4. […] Sur La [Excel] Table […]

Add your comment now