dataprose.org

I really like a lot of the Custom Hot Rods out there. Among my favorites are the Ford Mercuries that have been chopped with a Photon Laser and lowered. Among the Custom Hot Rod group the preference is for the 1951 Model. But I prefer the 1940 Model shown here with the hood coming to an apex, that awesome grille,split windshield and those enormous swooping fenders. Cars nowadays have by and large lost sight of these awesome design details. I hope to be able to find my own some day that I can restore and customize.

However, today’s post is not about the Ford Mercury or Custom Hot Rods, it’s about Excel Table TableStyles.

I like Excel Tables and Structured References a lot – 2nd only to PivotTables. If I can’t use a PivotTable, I’ll try to use an Excel Table and lastly a conventional cell-based formula. Today, I’ll look at TableStyles and some ideas on how to modify the TableStyles should you desire.

Get Some Data

First, I’ll need some data that I can convert to an Excel Table. To generate test data fast, I use the Random Data Generator Add-in by Dick Kusleika.

Insert Excel Table

Now that I have a Range of data I can insert n Excel Table.

I clicked on some cell in the Range, selected the Insert Menu and clicked on the Table icon in the Tables Group.

The Create Table dialog box pops up dispalying the CurrentRegion based on the cell I selected before I clicked on Insert Table. The check box for Headers defaults to True (checked) the Table includes Headers, so I’ll leave that as is.

When I click on a cell inside the Table the Table Tools Menu lights up.

I clicked on the Design Tab immediately below the Table Tools Menu. In the Table Styles Group, I notice that the Style that was applied to my Excel Table has a feint border around it. I hovered on the thumbnail image of the TableStyle and in the resulting Tool Tip discovered that the name of the TableStyle is Table Style Medium 2.

TableStyles

I clicked on the Table Styles flyout to reveal thumbnail images of all of the different TableStyles. I hovered over each thumbnail with my mouse and the Style was applied to my Table offering a preview of what it would look like if I apply the Style – that’s a nice feature.

I discovered that I do not care for most of the Styles in the Table Style Gallery. Table Style Medium 1-14 are OK – chuck the rest.

TableStyles-Customize

Table Style Medium 1-14 are OK – but maybe I can make them a little better with a little customization. To customize an existing TableStyle, I need to duplicate the Style.

In the resulting Modify Table Style Dialog Box, give the copy of the Style you are duplicating a new name. In the screen shot, I am using tsDataProse3 becuase I already have a couple of Custom Styles in the Workbook.

I now have a Custom Group in the Table Style Gallery in addition to the Groupings for Light, Medium and Dark Table Style Collections.

Now that I have a Custom Style – I can change it however I want. As mentioned I like Table Style Medium 2 – but there are a couple of changes I would like.

• Add thin white borders to interiors left and right for each Field in the Header Row
• Add thin white borders to interiors left and right for each Row that has a Banded Color

I right-clicked on my Custom Style, tsDataProse3, selected Modify from the popup menu and selected Header Row in the Table Element ListBox.

Steps to modify the Header Row:

• Click on the Format Button
• In the Format Cells Dialog Box click on the Border Tab
• Click on the desired Line Style
• On the Border Preview Diagram I clicked on the Vertical Inside Border

Now when I right-click on my Custom Style, tsDataProse3, selected Modify from the popup menu and selected Header Row in the Table Element ListBox – I see a small change in the ElementForatting textual description with the addition of InsideVertical Border.

Here is the Header Row after formatting the Inside Vertical Border.

Next I want to add an Inside Vertical Border to the Banded Rows.

Steps to modify the First Row Strip (Banded Row):

• Right-click on the thumbnail image of your Custom Table Style
• On the pop-up menu, click on modify
• In the Table Element ListBox, click on First Row Stripe
• Click on the Format Button
• On he Format Cells Dialog Box, click on the Border Tab
• Select a color for the border, I chose White, Backgropund 1, Darker 15% (RGB 217,217,217)
• Click on the middle border in the Preview Diagram
• Click “OK” 2x

Now I have nice continuous Inside Vertical Borders for all Cells in the Table.

Next Steps…

In my next post, I’ll look at some VBA that we may implement when working with TableStyles.

Tidy up

The changes I made to the existing TableStyle are very subtle. Make no mistake, this is a conscious choice. I don’t like heavy borders and I largely follow the teachings of Stephen Few when it comes to the display of quantitative data. Check out Steve’s stuff on his blog, Perceptual Edge.

Other Excel Table Articles At dataprose.org

• Sur La [Excel] Table
• Listing Toward ListObjects [Excel Tables]
• Come Together [Excel Workbooks]
• H2H – Excel Range Object Versus ListObject Object

Other Excel Table Articles Around The Horn

• Overview of Excel Tables
• Using structured references with Excel tables
• The VBA Guide To ListObject Excel Tables
• Creating An Excel Table
• Working With Tables In Excel 2013, 2010 And 2007
• ListObject TableStyle Property

Custom, Excel Tables, Hot Rod, Style, TableStyles

My grasp of all things maritime is limited:

1. I love seafood (Scallops with garlic, butter and white wine – I’m looking at you)
2. I can still tie a few knots I learned in Scouts
3. I enjoy The Pirates of the Caribbean Ride at Disney World
4. I can speak like a pirate. (International Speak Like a Pirate Day is Friday, September 19, 2014. So set yer reminder on yer calendar on yer mobile device now matey, arrrrgghhh!)

Today’s post, however, is not about my favorite ale or the Jolly Roger. It is about the ListObjects Object and / or ListObject Object of the Excel Object Model. There is no typo there – though admittedly it does look and sound a little odd. Links to the Object Models are at the bottom of this post.

Convert a ListObject To A Range

I’ll begin by converting an Excel Table (ListObject) that I created in my last post back to a Range. Warning: Converting an Excel Table to a Range will wreak havoc on any Structured Reference formulas that are using the Excel Table Do not try this on any production models, use test workbooks only until you have a full understanding on the outcomes.

Unlist The Excel Table

Option Explicit

Sub ConvertListObjectToRange_1()
    'Purpose:   Unlist an Excel Table to convert it to a range

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim lo As ListObject
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    
    For Each lo In ws.ListObjects
        lo.Unlist
    Next lo
    
    Set ws = Nothing
    Set wb = Nothing

End Sub

1. The filter arrows are gone
2. When I click on a cell within the Range, the Table Tools Tab no longer activates

The Excel Table has been converted to a Range. But I would also like to remove all formatting that was introduced by the Excel Table.

Remove Formatting Left By Excel Table

I would like to remove the these formats:

1. Remove all color
2. Remove all borders
3. Make sure all font colors are black
4. Make sure all fonts are normal (not bold)

Sub RemoveFormatting()
    'Purpose:   Remove formatting left from Excel Table

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set rng = ws.Range("A1").CurrentRegion
    
    With rng
        .Interior.ColorIndex = xlColorIndexNone
        .Borders.LineStyle = xlLineStyleNone
    End With
    
    With ws
        .Range("A1").EntireRow.Font.Color = vbBlack
        .Range("A1").EntireRow.Font.Bold = False
    End With
    
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Looks good – all cleaned up! Next, I’ll look at adding a ListObject (Excel Table) using VBA

A Quick Segue – Table Styles

In my code for adding the Excel Table, I will want to add a Table Style so let’s look at some code to list available styles:

Sub ListTableStyles()
    
    'Purpose:   List all Table Styles in the workbook
    'Comment:   [Ctrl] + [G] to activate the immediate window to view the output

    Dim wb As Workbook
    Dim ts As TableStyle
    
    Set wb = ActiveWorkbook
    
    With wb
        For Each ts In .TableStyles
            Debug.Print ts.Name
        Next ts
    End With
    
    Set wb = Nothing
    
End Sub

Now I have a list of all available styles in the workbook as well as the correct naming convention to be used.

Convert A Range To A ListObject (Excel Table)

Now I am ready to convert the Range back to a ListObject (Excel Table)

Sub ConvertRangeToTable()
    
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim lo As ListObject
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set rng = ws.Range("A1").CurrentRegion

    Set lo = ws.ListObjects.Add(SourceType:=xlSrcRange, _
                                Source:=rng, _
                                TableStyleName:="TableStyleMedium4", _
                                Destination:=Range("A1"))
    lo.Name = "tblData"
                                
    Set lo = Nothing
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
                                       
End Sub

Looks great! I now have an Excel Table named, “tblData”. There are additional Source Types that may be used as a source for the Excel Table: Source Type Enumeration. I will look at these in future posts.

ListObject Business Case

A fairly common request over on the LinkedIn Excel Groups is how to copy filtered data without the header row to another Worksheet or Range. This can be accomplished with a Range Object and a few manipulations to shape the data. But it is much easier with ListObjects.
The ListObject offers three distinct Ranges that may be exploited in VBA:

The HeaderRowRange

The DataBodyRange

The TotalsRowRange

For today, I will focus on the DataBodyRange. I want to filter the data and copy the Rows that remain except for the HeaderRowRange.

Sub FilterTable()
    'Purpose:   Filter Excel Table, copy visibile range

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim lo As ListObject
    Const strCriteria As String = "Aetna"
    
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Data")
    Set rngDestination = ws.Range("G3")
    
    With ws
        For Each lo In .ListObjects
            'DataBodyRange to Range
                Set rng = lo.DataBodyRange
            
            'Filter the Range
                rng.AutoFilter _
                    Field:=1, _
                    Criteria1:=strCriteria
                    
            'Copy the visible range
                rng.SpecialCells(xlCellTypeVisible).Copy
                rngDestination.PasteSpecial xlPasteValuesAndNumberFormats
        Next lo
    End With

    Set rngDestination = Nothing
    Set rng = Nothing
    Set ws = Nothing
    Set wb = Nothing
End Sub

Looks great! Quite a bit easier than jumping through some gyrations to reshape the data to remove the header row from the visible range.

I use the SpecialCells Method of the Range Object here to copy just the visible range. I covered this previously in ,this post

Tidy Up

Final Thoughts

That’s it for today. I hope you find this post helpful and are able to go through fewer gyrations in the future to shape your data. Now, where’d I put my parrot and wooden leg?

Downloads

Download the file from Skydrive

Additional Recources

ListObject Object Model
ListObjects Object Model
Working with Tables in Excel 2013, 2010 and 2007 (VBA)
Working with Microsoft Excel tables in VBA

Excel, Excel Tables, ListObjects, Lists, VBA

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.

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.

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.

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

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.

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

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.

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.

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.

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)

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.

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?

Downloads

,Download the file from Skydrive

Additional Recources

Absolute Formula References In Structured Tables
Overview Of Excel Tables
Using Structured References With Excel Tables
Working With Tables In Excel 2013, 2010 And 2007

Excel, Excel Tables, ListObjects, Lists