Create an Excel table from a data area.
How to create an Excel table and sort, format and filter its columns?
Then filter the data? First create the required rows and columns. Fill the table with data and turn it into a table with headers. In the form of a table, it is possible to sort and filter the data easily. An Excel table is a data table, the type of object most commonly used by professionals.
Prepare the data area with the headers
1 – Create a new Excel file, then add the columns. In this example, it is monthly financial data. It could be actuals till the current date (March 2022) mixed with the budget forecasting for the next months.
2 – To generate the rest of the list of months in words, select the two cells January and February. Click on the small cross and hold then drag the mouse to December. The result is one year column and twelve month columns.
Then add the years from 2014 to 2022 in the Year column.
This short tutorial is available in a four minutes video format here:
3 – To duplicate the rows, select the first row from January to the Total column. Then drag down as shown in this screenshot. This area of raw cells, the source of the table, is not filtered or formatted.
Formatting the selected data area as a table
This part shows how to format the Excel table to sort the data.
4 – Select all contiguous cells by clicking on the top left cell. Then use the shortcut CTRL + A to select all the data. This Excel table or area is the table, without formatting.
5 – Under Home, then choose the option Format as a table. Choose the colour and style of the table, here it is a Blue table.
6 – Then, check the option My table has headers, to indicate to take into account the pre-existing headers. This action will not add any row to the table.
Creating an excel table is actually formatting a data area
7 – Add the totals row to obtain the grand total with all the years. Right-click on the Excel table, then choose Table and finally select Totals line. The result is a formatted Excel table. It is possible to sort the cells, for example by year or to select only the year 2021 for example.
8 – Adding the annual total with a formula. To have the Yearly line Total column on the right, use a simple Excel formula.
Use an Excel formula to calculate the sums
The Excel formula in the cell uses the SUM function:
=SUM(Table1[@[January]:[December]])
To facilitate the creation of the formula, write the SUM function in one line and then select the first row of amounts from January to December. Then make the selection slide to the last line of the data. Note: for a more sober display, deactivate the grid on the Excel tab. To do this, in the top menu of Excel, under View, uncheck the Grid option.
Finally, this short article explains how to create an Excel table to sort and filter data. To go further , here is how to manage common errors from Excel formulas like VALUE, NA or DIV0.
