In the beginning there was a table...

It all started with tables. Some of my readers probably still recall all those huge sheets of paper, precisely carved with lines, used to store all operations of whole companies. In Poland, where I come from, we usually called such written records "gallows". Name came for the usual T shaped lines, dividing columns into Credit & Debit sides, with amounts hanging below.
You can still see little faded resemblance of those records in lot of financial reports, produced by modern accounting software. Especially clear when you export such reports to Excel. By the way, that same application has prominent place among most frequently used by financial world. Yet so many of us are not aware of revolution, which lies in very heart of worksheets. Today I would like to catch your attention to very special kind of tables, which can make your life much easier in different ways.
.

Tables inside a table

It may sound stupid, but there are lot of benefits for everyone who'll give structured tables a try. What are those objects? Basically speaking, structured table is a special way, that Excel treats range of cells in a sheet. When you think about table, your mind probably shows you rectangular area, with bunch of lines forming grid. It may have bold captions on top of each column (headers). Eventually may contain kind of summary footer in last row.
-Wait a second - you might say - Isn't that possible to achieve just by drawing frames around any area inside sheet, which has special meaning to you?
Yes, it's true. But after a first moment of joy, road climbs up. Imagine your nicely formatted table few months later. You had created some hell complicated calculations on basis of the sum in one of columns divided by max value from other column minus something something... Of course as it happens, your boss asks you to include his own estimations added to your table & send report back in 5 minutes. Sounds familiar? Your precious work can be broken apart in seconds and, poor you, lot of time spent on correcting formulas ranges in best case, or even worse nice #ADR! errors attacking you from each corner. Not mentioning your beutiful inner borders in flamingo-pinkish with pretty thick avocado-green outer frame. It all gets broken in a strange (at first glance unpredictable) way.
In easy case you might be an only author, but let's assume you sent your file to one of managers in different department, he passed it to hers/his assistant, who is not so well experienced in Excel. In a couple of moments Mr. Disaster sits on a couch eating popcorn and watching. Structured tables can save your romantic cinema evening, planned for tonight! Let's create an easy one.

• Please create an empty Excel file: in Excel window select File > New > Blank workbook
  
• Now create some headers, for example let it be some list of invoices, having headers: Date, Number, Vendor, Net amount, Tax% & Gross amount. Enter such names in cells, starting from cell B2, to form headers row like on screen below:
  
• Being used to older thinking, some of you would like to add some formatting. Please wait a bit with this task. As you see cells, that you've filled up, are forming rectangular area on sheet. Click with a mouse in any of your new headers and press on keyboard CTRL+L.
• There should appear a popup window. Also all your entered headers became surrounded by marching ants.
  
• In dialog window, you need to make sure, that all your table area was properly selected by Excel. If prior pressing CTRL+L, you have been in longer range of filled cells (i.e. cells laid in form of table with headers on top, and data filled below), Excel will try to guess where are borders of your data range. You can correct it, by providing proper area.
• Now you should let Excel know, if your range has headers on top in first row. If it is so - please check checkbox "My table has headers". Do it in our excercise, as we do have headers defined. Please tick checkbox and press OK button.

Now you can feast your eyes with brand new table. Notice, that headers you wrote, were converted automatically into table headers. Excel do not tolerate empty tables. That is why it has created one empty row in table. In case you will need to clear all rows from your table - it will return to the same form, as you see it now: headers & single empty row.

When active cell is in area occupied by table, Excel enables additional tab in ribbon space Table tools > Design. This allows working with table and customizing its look & feel settings. This ribbon shows up also when you will select few cells inside any table area.

All created tables are named automatically using schema "Table1", "Table2", etc. Good habit is to rename them, to reflect their actual content. Advanced designers are used to start all table names with same prefix, i.e. "tbl". It helps to find table later, when you want to reference them in formulas.

• On ribbon Design find field Table Name. Our table will contain information about invoices, so let's call it "tblInvoices".
  

So what is it all about? What's in it for me? Let's check what are benefits of structured tables in practice.

1. Uniform and easy to read formulas

When I discovered structured tables, first thing which has amazed me was clarity of creating formulas, which are referencing table columns. Our table contains net & gross amounts as well as tax %. They are placed in columns E:G of a sheet. Before creation of a table, in order to write formula calculating gross amount, you would need to write in cell G3 for example such formula:

=E3*(1+F3)

There is no big adventure to track where are values coming from, when formula is that short and easy. Usually you are writing little bit longer formulas. If there will happen, that you start reference other sheets in workbook, analysis becomes tedious task and requires lot of single-mindeness. So how it looks, when you refer to table column? Let's create in our example formula which calculates gross amount.

• Go to cell G3 & start writing formula from "=" char
• Click on cell E3 (Net amount) in the same row. What did Excel put in formula text, istead of E3 address? Name of your column from table header in square brackets: [Net amount]
• Continue with formula entry, filling next part "*(1+"
• Click cell F3 (Tax%) & put in closing bracket ")". Accept your formula by ENTER key
• Go back to G3 cell and look, how clearly presents itself formula inside. By pressing F2 key - source columns will be highlighted in color, matching parts of formula.
  
  I bet, that at a first glance you are able to tell, what does this formula calculate. In one of next posts, I will tell you more of how Excel creates column references. You will see how to refer current row or whole column data. But for the moment experience you've gaind should let you construct first easy formulas on table basis.
• So now let's check if it works as intended. Please put some data in remaining columns, i.e. as on screen below:
  

As you see, formula works properly, even though it does not refer cells using direct addresses. But that's not all.

2. Calculated columns

One of the issues, you might have come across before discovering tables, is keeping consistency in formulas, when data range changes dynamically. When users will reach end of prepared area, they start adding new rows below or insert rows. You need then to revise formulas and copy them to new rows. Structured table have the answer!

• Get back to our table and activate cell right below Date column (B4)
• Input in B4 date of next invoice and press ENTER key
  

Observe how table area was automatically resized to new data row. That's not everything. In cell G4 appeared 0. Go to that cell, to see the formula:

Formula entered by us in first row, was transferred in background to next added row. Excel is treating column Gross amount as calculated column, and copies column formula to each added row. If you'll like to change formula to different one - it will be applied in whole column too:

• Click one of cells in Gross amount column
• Enter modified version of fomula:

  =[Net amount]+[Net amount]*[Tax%]

  & accept with ENTER key
• Check what happened to formula in other row

Calculated columns definitely ease work. You no longer need to copy formulas or revise them. When table is resized - formulas are copied to new rows. You no longer need to pay any special attention to it.

So is this all about tables?

No, that's not all. Due to huge scope of table related tips, I've decided to continue this subject more detailed in next post. Your welcome to read and comment. See you soon!