Excel table 2 | Deleting row or column | Total row | Remove duplicates | convert to range

Excel Table

Design Tab

Design Tab various features.

Resize Table

We are going to add the column "Total" to the table. Click inside the table and click Resize tab (top). Resize Table dialogue box opens up. And you can see that the existing table is already selected. Click at the button marked with Red circle.

Resize the table selection including new column.
After resizing

• Press Enter
• Click Ok

New column "Total" is incorporated into the table with changed appearance.

Convert to Range

 A table can again be reverted to a normal data range. Though the appearance will remain as table but after conversion it will loose its functionality as table.

• Click inside the table.
• Under Design Tab click 'Convert to Range' icon.

 

Remove Duplicates

Duplicate values can be removed from table.

After clicking inside the table.

Click Remove Duplicates under Design tab.

Display 1

The dialogue box Remove Duplicates appear. Where we can specify all or individual column/columns from where duplicates are to be removed. Here for the sake of explanation we select only Region column and uncheck other columns. 
And click Ok.

Only 3 region is left over, and all are removed (duplicates are only removed). 

One thing to be noted. Only first 3 region cells are retained and others are deleted.That means, while removing duplicates the function retains first available East, Central, and West cells. Watch carefully the Display 1, and watch the Representative column where 'Jones, Kivell and Sarvino the first available choices of the respective regions.

Table Style Options

 

 

If we uncheck 'Header row'. Filter enabled header row will be gone, rather hidden. You can bring it back by clicking the check box again.

First Column and Last Column checkbox enables and disables special formatting for first  column and last column only. For instance if we click the First Column check box first column's font will get bold.


If we uncheck the Banded Rows and Banded columns check box, the appearance of the table (other than the header row) will be normal. Banded rows are by default checked and hence the appearance of the table is different from normal data range.

Filter Button check box controls the filter on the header row. If we uncheck the Filter Button drop down filter buttons will be gone.

Total Row

After checking the Total Row check box, Excel starts calculating the total values of all the columns in the table. By default it is unchecked.

As we see after checking the Total Row, Amount column is already been summed up. And a text 'Total' is written on the left. Now we can calculate total of all other columns. Just select the desired formula from the drop down list box.
If we click More functions a separate major window of functions pops up. From where we can use our desired formula for the column.

 

Table Styles

  

Table style option helps one to design his/her table. The drop down arrow in the extreme right enables user to choose from different design option. You can choose from the given templates of your choice.

You can create your own design (New Table Style...). Even you can clear the design (Clear).

Shortcuts for selecting table, table row and column 

At the green intersection point below if we hover our mouse cursor our cursor becomes diagonal shaped arrow facing downwards and if we click then entire table will be selected.

To select a Row: Just click on the cell corresponding to the row you want to select and press Shift + Space Bar.

To select a Column: Just click on the cell corresponding to the row you want to select and press Ctrl + Space Bar

These shortcuts are absolutely valid for selecting corresponding row and column in an ordinary data range but the difference is, in an ordinary data range the shortcuts selects entire row and entire column irrespective of data content.
Try it.

To add new row or column within the table. 
Within the table right click the mouse and  navigate your cursor to the 'Insert' shortcut. You can insert an entire new column in reference to the active cell or you can insert entire new row in reference to the active cell. 

 

Deleting row or column inside table

Delete function deletes entire row or column

• Right click on the cell corresponds to the row or column you want to delete inside the table.
• Navigate to 'Delete' shortcut.
• Click Table Columns or Table Rows.