Excel Tip of the Week #79 - Revisiting Tables

Hello all welcome back to the Excel Tip of the Week!  This week, we have a Creator Revisited topic.   We're going back to a topic first covered in TOTW #6 and #13, which is the awesome Excel Tables feature.  This is a bigger topic than we usually do but we wanted to get everything about this great feature in one place.

What is an Excel Table?  How is it different from just laying out data and using filters?

An Excel Table is a special object that can be added to a worksheet.  Tables have a variety of cosmetic and practical advantages that makes blocks of data easier to work with, such as clearer formatting, automatic updating for new data, and easy formula writing.  Whilst the Table format by default adds filters to data, it goes way beyond what’s possible with filters alone.

How do I turn a block of data into a Table?

Select a cell within the block and navigate to Home => Format as Table, then select one of the preset styles.  Alternatively press Ctrl + T.



The dialogue box then asks you to confirm the range of data to be used and whether a header row currently exists or one needs to be inserted.



You can change the style later so don’t worry too much about which you start with.

Cosmetic improvements

If you select a style with striped rows, it will be easier to read across a single line.  Furthermore, the striped format will be reapplied if the data is sorted or filtered, such that adjacent rows are always different colours (unlike if you striped the rows manually).

If you scroll part of a Table off the screen, the header row will be shown in place of the column identifier row as long as the selected cell is within the Table.



You can easily rearrange the columns of the Table by dragging the column header to a different position – the entire column will be moved with it.

Finally, from a special Design menu (see Structured Formulas, below), you can add a Total row to a Table.



Click into any cell in the Total row to access a dropdown menu of common summary formulas which Excel will write automatically for you.



Auto-updating

If data is entered underneath or alongside a Table, Excel will automatically expand the Table to include that data.  Any columns with formulas will have those formulas continued into the new row.



The appropriate formatting will be copied into the new cells – e.g. the highlighted cell in the second shot here will be formatted with a dollar sign and 2 decimal places.

Formulas are also autocompleted into columns – so if you enter a formula into a newly made column, the entire column will automatically be filled with that formula.

Structured formulas

You can name a Table by clicking on it, then selecting the special Design menu that appears.  The box is at the far left and you can just write in a new table name and press Enter.



When writing formulas, you can now use the name you chose to represent the area with the data from the Table.  So if you’re writing a formula that looks at the entirety of the data (e.g. a VLOOKUP), you can just write Fruit instead of writing e.g. B2:G9.

You can also use [] to write special references to all or part of the Table:
   - [COLUMNNAME] means all data in the named column
     o You need to use e.g. Fruit[COLUMNNAME] if the formula is outside of the Table itself (this is true for all the below examples as well).
   - [@] means the entire of the row of the Table on the same row as the formula
   - [@COLUMNNAME] means the entry from the named column and the same row as the formula
   - [[COLUMNA]:[COLUMNB]] means all data between the named columns A and B
   - [#All] means the entire Table, including the header row and total row
   - [#Headers] and [#Totals] indicate the header row and the total row, respectively
     o You could also write e.g. [[#Totals],[COLUMNNAME]] to mean "The totals line of the COLUMNNAME column"

Tables are a powerful tool for organising, manipulating, and working with data.  They add easy-to-use features and powerful formula functionality.  They also allow e.g. multiple filtered Tables to sit within a single worksheet.  They’re one of the best features of Excel and I encourage you all to try them out.

Previous post 
TOTW index
Next post

This blog is brought to you by the Excel Community where you can find additional blogs, extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business. There is also an online forum where you can ask questions and share ideas with other community members.

Anonymous
  • Hi Paul - I can help with a few of those.


    You can copy a reference as absolute by using Control+r to fill right - select the cell to be copied and the cells to the right to which it is to be copied to, and use Control+r.


    Alternatively you can do it by creating the reference as a range and copying as normal. For example for a column in a Table called Table1 and a column headed Total:


    =Table1[@[Total]:[Total]]


    If you are an Excel community member we have covered the fill-right method a couple of times. Here's the most recent: Copying">www.ion.icaew.com/.../Copying-Table-structured-references-as-absolute">Copying Table structured references as absolute


    Also, I've just tried copying a total formula to other columns using the fill handle and it seemed to copy OK.


    You can add rows to a table with a total row by using the Tab key in the last cell above the total row.

  • I love using Tables, however I have come across some limitations (in Excel 2010):

         

         

      Excel doesn't allow you to extract sheets with Tables in them in some circumstances;

         

         

         

      When adding a Total row it only defaults to one column being summed and you cannot copy this across so it requires manually choosing for each column;

         

         

         

      After adding a Total row it is more difficult to add data at the bottom and sometimes the formatting of rows added to the bottom will be different to the rest of the Table (in the row where the Total was);

         

         

         

      If copying a formula that refers to fields in the Table it will treat it like an absolute cell reference and I am not aware that it is possible to change this to a relative cell reference (i.e. referring to adjacent rows/columns in the Table);

         

         

         

      When I have put a Table into a template used by our document management software, the Table is lost when creating documents with it.