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.
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.
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.
To add automatic Formulas – select Total Row.
This will add a SUM Function to the last column.
NOTE: It uses a SUBTOTAL Function.
Click on the Drop Down in F17 to see a list of available Functions.
You can add other Functions to other Columns as well.
Here I have chosen the COUNT Function in E17.
The Function is:
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.
Write a Formula in G8:
The Formula will get copied across the entire Table.
If you want the SUM in another cell, let us say C3, write the Formula:
You can even refer to this Table from other sheets in the same Workbook.
To add a new row click on G16 and press Tab.
Formulas will automatically get copied in the additional rows when you add data.
Tables include Slicers – that allows you to Filter the data.
Let us choose a Slicer for Region.
The Table can be filtered by selecting an item on the Slicer.
Here we are only showing Central Region.
GET SAMPLE FILE AND SOLUTION
Send an email at: