Excel thumbnails for table styles
show a header
row and four rows, which is just enough to show any alternating colors. You can also see whether borders are showing.
Once you have defined a table, the Table Tools: Design tab lets you choose several other features for your table style to manage. You can mix and match these options and the style thumbnails will automatically update. Some table styles do not include formatting for all of the options.
You can rearrange your table by sorting on any column. Sorting multiple times in a sequence (carefully!) can work well or it might give very unhappy results, depending on your choices!
Applying a filter will hide rows that do not match your criteria. For example, in a table of customer orders, you could filter to see orders from a particular person or company. Or perhaps you are looking for a particular order that was for $152.95 but you cannot remember who placed the order. A filter on the order's total would hide all orders for other amounts. The orders are not removed from the spreadsheet. They are just hidden while the filter is in place.
The danger to filtering is, of course, forgetting that what you see is filtered!
![]() |
Step-by-Step: Modify Table |
![]() |
What you will learn: | to change a table's size to change table style options to sort and filter with table columns to filter on existing values to filter with a logical test to print a selection of a sheet to convert a table back to a range to clear all formatting |
Start with: trips11-Lastname-Firstname.xlsx (saved in previous lesson)
You can modify several features of your table, like the size and whether or not there are special rows or columns.
The bottom
right cell of a table has a symbol in its bottom right corner.
When you hover over this corner symbol the mouse pointer changes to the
diagonal resize shape .
You can drag to increase or reduce the cells that are included in the table. Table
style formatting will change as you drag the table larger or
smaller.
If you get a warning about a circular reference, use Undo to return your table to its original state.
Click in the table somewhere.
The tab Table Tools: Design appears
again.
The tab group Table Style Options has check boxes for
special types of rows and columns. A table style might not include special formatting for some of these special table parts.
The default table styles show a Header Row and Banded Rows, so those boxes are already checked.
The thumbnail cannot show that a column will
be Bold or what font will be used.
On
the tab Table Tools: Design in the Table Style Options tab group, click the box beside First Column.
What
changed?
The first column of the table became bold.
The thumbnail you saw
did not show this.
Click on the box by Total Row.
This is not a change in formatting! A new row appears at the
bottom of your table, just outside the table border.
This works well ONLY when you did not already have a totals row!! Look at the value in row 26. It is double the lovely sum you already had in row 25. Clearly the total in the new row is adding up everything in the last column, including the total that was already there. Bad idea for this table!!
This new total row creates only a total for the last column. If you want other totals, you can create them on this row.
Use
the Total Row option when you do not already have a totals row for
the table. This is especially useful when you want to look at how
some totals change as you filter the table temporarily.
Experiment: Special Rows and Columns
Try out several combinations of the choices in Table
Style Options and various table styles.
Which do you think would work well with this
particular table?
When you are ready to continue...
Uncheck the boxes for Total Row, First Column, Last Column, and Banded Columns.
You should be back to
the formatting you started with.
It is not necessary
to save since no changes were kept.
A 'table' in Excel can be sorted using a menu from the arrow in the column label. You hid those arrows in an earlier lesson.
The header row (row 4) gained a drop list for each column. Each arrow opens a menu of choices for sorting and filtering the whole table based on what's in that column. Sorting rearranges the rows. Filtering hides the rows that do not match the criteria that you pick.
Click outside the table to deselect the
table.
The column arrows did not disappear! This is one of the benefits of setting up cells as a table. You do not have to select the table again to use the commands on the arrow menu.
What changed?
Original Totals row: Sorted with other rows. The calculated values changed! This could be a problem!
There is a green triangle in the corner that show that Excel thinks there IS a problem.
Click in the cell with with green triangle and then click on the
error icon to see the message. "Inconsistent Formula". Look at the Formula bar. It shows =SUM(E2:E21)
but the last
customer row is row 24. Moving the formula is making a mess!
Total rows don't belong inside a table!
Leave your own totals
row out of the 'table'. Then it won't be sorted!
What changed?
Undo twice!
Your table should be back the
way it was before sorting, with the custom sort by trip with
Tahiti first.
Sorting formula cells: Do not
include a cell with a formula when sorting unless ALL relative cell references
in the formula are to cells in the same row.
Click on Sort by Color.
You will see choices here only when you have applied colored fill in cells with Cell Styles (which we will look at in a later lesson). The background fills from the table's style do not count. The Sort by Color choice also includes a link to the Sort dialog where you can sort on multiple columns, as you did in an earlier lesson.
When you are ready to continue...
The choices in the Filter section of the menu depend on whether the data in the column is text or numbers. Once of the most common filters looks for one or more values that are known to be in the column. Those values show in the menu from the column's arrow.
What changed?
Sometime you want to see the rows where the values are less than a particular number, or greater than a certain value. Or you might want to search for values that start with a particular text.
Click the arrow on the column Total sale.
The Sort
and Filter menu appears.
Click on OK to close the dialog and apply your filter.
Only 5 rows of
data and the Totals row still show. They are the only ones with a
Total sale value of 15,000 or greater.
The value in row 25 for Totals is not changed. It is still the total of all values, including the hidden ones.
Experiment: Custom Filter
Try out other choices for a custom filter.
What
happens when you combine filters with AND selected in the dialog?
What happens when you combine filters with OR selected?
When you are ready to continue...
You can print just part of a spreadsheet by setting the Print Area, but that is permanent until you change the print area. For a temporary situation, it's better to select what you want to print and use the Print choices to print only that selection.
Excel 2010, 2013, 2016: Open Print Preview. Change the settings, if necessary, to Print
Selection.
Are you annoyed by those arrow buttons on each column? Once you get your table sorted, you can return the cells to an ordinary range, but the formatting and the sort order stay in place. Filters will be cleared automatically when you convert the table back to a range.
This is a fast way to remove all the cell formats and get back to basics - Normal font, no borders, no fills, no merged cells. To return to a previous stage in your formatting, you can use Undo. But Undo only remembers 100 actions. That sounds like a lot, but you can easily exceed that number in a single editing session!
Click on Table1.
Your list will show a
different number if you created a table more than once during your
work.
The whole table
appears to be selected, but the header
row is outside the border. It is hard to see highlighting when the table color is similar to the highlight color!
One of the common uses for tables is to apply a table style for formatting. But suppose you need to get rid of that formatting later? You can clear the formatting in several ways.
Open Print Preview.
Problem:
Preview does not show all of the data
What happened?? Your
print settings are still set to Print Selection.
Solution: Change the setting for the printer to Print Active Sheets.