On my trainings I usually show the “smart table” as a bonus topic, the students are amazed by the options of this tool and collectively agree that they do not want to work with another table anymore. Indeed it makes easier both formatting and filtering, subtotals, and more. Let’s see at least some of the tweaks, others I will be happy to demonstrate directly on the trainings. If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.
When creating tables, users spend a lot of time by changing its graphic design and preparation for calculation formulas, filters or setting freezing panes with headlines. This tool, which can be unofficially called “smart table”, will save a lot of time.
The tool is activated by placing the mouse anywhere in the prepared table with data, since Excel 2007/2010 we can find it on the Insert tab > Table. This command is apparently somewhat perplexing for Excel because it is itself only a one large table, what sense does it make to insert a new table into another table? The name is misleading, but rather should therefore be called “smart table” or according to the official terminology “table of the list form” After confirming the dialog with range location, it is ready, as shown below.
When selecting any cell inside the table now, it activates a new tab at the end of the Ribbon called Design. It contains tools for facilitating effective work with the smart table. We can describe some of them:
- table styles – it allows you to quickly change the graphic style (format) across the table at once, plus nicely alternating colors in odd and even lines, which can be changed by selecting Banded rows resp. Banded columns.
- Total row – that activates at the end of a table row named Total. In each cell of this line, users can select the type of summary, which will be calculated from that column values. This summary is calculated only from the filtered items that you can easily choose also by activated automatic filter.
- Remove duplicates – removes duplicate (repeated) rows in the table, let’s end with long manual removal!
Furthermore, it can automatically freeze heading row, copies the formula to the whole column without having to drag and a few more goodies. See them on the training!