Skip to main content

Introducing How to Add Custom Table Styles in Excel

· 3 min read
hoh
hoh

Once you discover Excel's ListObject (table styles), it's so convenient you'll want to use it for even small tables.
However, one downside is that the default table styles (appearance) are not very appealing.

img

From left to right:

  1. None: No style applied
  2. Standard: Default table style
  3. TableStyleLightBlue: Custom table style #1
  4. TableStyleLightGreen: Custom table style #2

You can create your own custom table styles like the ones above, but the issue is that they are saved on a per-workbook basis.
While you can copy them to other workbooks, you may end up transferring unwanted data, which can be risky.

That’s where a script that lets you add table styles anytime comes in handy.
If you have such a script, you can apply your preferred table style to any workbook whenever you like—so let me show you how.

Info

If you’d prefer to skip the explanation of ListObjects and just see the Python code, go here:

Adding Custom Table Styles to Excel with Python

Benefits of Using ListObjects (Table Styles) Again​

  1. Cells can be referenced not only by absolute coordinates but also by names — e.g., cell E3
  2. You can detect inconsistencies in formulas across rows — e.g., cell E5
  3. It’s easy to toggle the visibility of total rows

In short, it lets you treat data like a relational database table.
You can more easily spot inconsistencies or missed updates, and formulas become easier to read when referencing columns by name instead of using something like =C3&": "&D3.

If you use absolute references like "C3" in VBA, you'll need to update your code every time the table's position changes or columns are added, which makes maintenance harder.

I'll save the VBA discussion for another time,
but the key point is: "If your data can be grouped as a table, use a ListObject."

Where to Set the ListObject (Table Style)​

img

  1. Select the table you want to style
  2. Go to Ribbon > Home > Format as Table

The Drawbacks of ListObjects​

ListObjects include a default design (appearance), but unfortunately, most of the built-in styles aren't great
(Of course, this is subjective.)

You can freely customize the design, meaning you can always apply your favorite table style...
Or so you'd think, but these custom designs can only be saved within a single workbook—that's the issue.

Why Use Python​

The main ways to apply a table style to a new workbook are:

  1. Copy the style from a workbook where it’s already saved
  2. Add the table style using VBA

While VBA can automate the process, it still has to be saved within a workbook, so you must open the workbook to run it.

That’s where Python comes in.

Advantages of managing table styles with Python:

  1. You can separate implementation from design
  2. It’s plain text, so it can be version-controlled with Git
  3. Once the environment is set up, applying styles is quick and easy

That’s it.
Thanks for reading this far.

You can find the method to add table styles using Python here:

Adding Custom Table Styles to Excel with Python