To convert a regular Data Set into a Table – Click on Insert > Table.

 

Shortcuts are Ctrl + T or Ctrl + L.

 

 

You can select any cell inside the range before creating a Table, even though I would advise you to select the entire range before creating a Table.

001.png

If your Table has headers – like the example does – then Tick the Option My Table has headers.

If your Table does not have headers – then do not Tick the Option.
Excel will create Table Headers automatically.

002.png

A new Tab called Table Design will appear.
You can Change the Table Name, Create a Pivot Table, Change Options and Styles and so on.

Change the Table Name to My_Table.

003.png

To add automatic Formulas – select Total Row.

004.png

This will add a SUM Function to the last column.
NOTE: It uses a SUBTOTAL Function.

=SUBTOTAL(109,[Sales])

005.png

Click on the Drop Down in F17 to see a list of available Functions.

006.png

You can add other Functions to other Columns as well.

007.png

Here I have chosen the COUNT Function in E17.
The Function is:

=SUBTOTAL(103,[Region])

008.png

Let us assume you want to add a column with a Target at a 10% YoY growth.

When you write Target in G7 the column is added to the Table automatically.

009.png

Write a Formula in G8:

=[@Sales]*1.1

010.png

The Formula will get copied across the entire Table.

011.png

If you want the SUM in another cell, let us say C3, write the Formula:

=SUM(My_Table[Sales])

You can even refer to this Table from other sheets in the same Workbook.

012.png
013.png
014.png

To add a new row click on G16 and press Tab.

015.png
016.png

Formulas will automatically get copied in the additional rows when you add data.

017.png

Tables include Slicers – that allows you to Filter the data.

018.png

Let us choose a Slicer for Region.

019.png

The Table can be filtered by selecting an item on the Slicer.

Here we are only showing Central Region.

020.png

GET SAMPLE FILE AND SOLUTION

Send an email at:

havish@havishmconsulting.com 

  • LinkedIn
  • YouTube
  • Instagram

©2020 by HAVISH M. CONSULTING. Proudly created with Wix.com