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