Microsoft Excel Deadly Sins 9.2: Excel Table Formatting

Microsoft Excel Table Formatting Tips

9.2 Not utilizing table object enough

In the previous part we talked about how easy to create table, self-expand mechanism and the smart formatting. In this section, we’re going to explore more benefits about Excel Table object.

Smart Formatting

Table formatting and styles that comes with it doesn’t embed into the cell. If you still do recall I’ve mentioned that, mostly user used to use Format Painter tools a lot, just to standardized the overall look of the whole worksheet. For the newly insert column or row, I would spend hours and hours using format painter to pick up the format from previous column or row and paint it to the newly inserted one. I’m not kidding, hours and hours wasted just to format the worksheets.

Now when I look back, I must be really free back then. Well, it is silly. Because, whenever filtering or sorting take place, the color does not stay as banded as how I formatted them in the first place. Such situation happened (Refer figure below), I mean yeah….. Tell me this is not true!

Let’s take a look at the formatting comes with the table? Can you see the difference?

The banded rows format remains when the filter and sorting take place. Well, it undeniably has make our life so much better.

Finding the table

Have you ever gotten lost in a pool of sheets, navigating around the sheets just to find that specific range? Or when you’re auditing a formula, it drives you crazy while certain cells are linked from other sheets, and mostly linked cells are reference from the sheets with similar sheet name? It happened, it definitely does. But, it is not going to happen when you’re using Table. To look for the table, hit F5. Excel will pop out a Go To dialog box and list all the Table name within this workbook.

Navigating from table to table has never been easier with this. If you’re frequent user of Table, rename the table from the default name such as Table# to something relevant to the table, so you don’t get confuse seeing Table1, Table2….Table # listed in the Go To dialog box.

Yeah, navigating has never been more convenient. You also can reach out to the Name Box, that also allows you to hop to the Table easily.

Renaming Table

Simply remember, while you’re renaming the table, no symbols, reserved words (especially function name/ cell address) and spacing are allowed (Only unique name, it is not that hard to create). The only symbols that are accepted underscore “-“ and backslash “/”.

Selection made easy

I bet you understand the pain of selecting a certain column and row while you’re dealing with normal range. Not with table though. Selection has never been easier if you’re selecting it within the table. Whenever you wanna highlight the column from your current selection, simple Ctrl + Spacebar.

To select the whole row, simply Shift + Spacebar.

To select the table, simply Ctrl + A (make sure your selection/ cursor has already focused within the dataset)

To include the header and table, Ctrl + A + A.

One click aggregation

When you want to have a quick aggregation with the column in table, no selection needed, no highlight needed, no formulas needed, just a click. Yes, you hear me right, just a single click, enabling the Total Row tools. With this, it will quickly add in a row to the end of the table called Total Row. It immediately aggregates the last column of the table using SUM function. The whole row (Total Row) comes with the function SUBTOTAL(). SUBTOTAL() function contains a list of database function (most frequently used) function in within.

This is the argument for SUBTOTAL() function:

If you were to summarize a list/column using SUM, simply set the code of the function_num within the SUBTOTAL(). With that, you can instantly aggregate using SUM function. Not only that, there are a list of function that you can use to aggregate. List is as below: