Removing Excel Tables

Excel gives you the option to format data in table. In Excel 2016, you can put columns of data on a worksheet in a table by going to Home . . . Format as Table. With a table when you enter a formula in one column in an Excel table, it will automatically be generated in all of the other rows of the table. You can use references to column headings (in brackets) directly in the formula. So in this example we add hits and walks in column T by entering: =SUM([H]+[BB]) in T2, and then it instantly gets copied down for the entire table.

We get a table where by default the row colors alternate, and the headings are set with filters.

On the top of the Design ribbon, a new tab for 'Table Tools' will be added. If you click on it, a set of options for a table will appear. You can add a total row simply by checking off 'Header Row' in the Table Style Options section. A new row will appear at the bottom of the table with drop down lists that let you select a number of options to calculate the data in any one column (Sum, Average, Count, etc.).

While data is formatted in a table, you'll notice that it's not possible to merge any cells. This can prevent basic editing or impair the functions of macros. To remove data from a table (without losing any of the substance) simply go back to the Table Tools tab on the Design ribbon and in the Tools section, select 'Convert to Range'. When prompted to convert the table to a normal range, just click 'Yes'. You'll see that the formulas you added in new columns or in the total row are converted to standard formulas with regular cell references.