First we will examine Table Styles and, whilst there, explore a very useful feature called Filters and then we’ll look at Cell Styles.
So let’s get our brains in gear and get going.
You can download a sample staff details spreadsheet >>here<<, in Excel 2010 format, to accompany these instructions.
Open the downloaded file ‘staffd.xlsx’.
Make sure the Home ribbon bar is selected.
Highlight all the text in the table (cells from A5:G33) and click on the Format as Table button that you will find in the Styles section (shown right).
This brings up a box showing you lots of pre-formatted styles in a variety of colours.
Click on the style of your choice.
The Format as Table dialogue box will appear (as left). Double check the data range and ensure the My table has headers checkbox is ticked.
Your newly formatted table will appear.
The table I formatted can be viewed below. Take a special note of the downward pointing arrows that have appeared next to each of the column headers. These are called Filters.
Filters allow you to view selected records that meets the criteria you choose by temporarily hiding the data you don’t want to see.
You can use one or more filters at a time.
For example, I want to find out how many of my employees that live in Kilmarnock earn £20,000 or over. Filters make this easy to find out.
Click on the down-arrow to the right of the Town header.
The dialogue box (left) will show up.
Click on the Select All option to de-select the town options.
Notice that this has filtered out all other towns from the table.
We are now going to repeat this process for the Salary header.
NOTE: We could spend some time deleting all the salary options less than £20,000 but, as usual, there is a quicker way.
Click on the down-arrow to the right of the Salary header.
Again, a similar dialogue box appears as before.
This time, select the Number Filters option and the Greater Than Or Equal To… option.
The Custom AutoFilter box appears.
Type 20000 in the box provided.
This should now display the records we wanted.
NOTE: If you correctly completed the exercise, how many employees met the criteria?
The correct answer is 7.
Sometimes we don’t want to let Excel 2010 format the whole table for us, we want to have more control.
To start with, either undo the table style you set previously or open up a fresh copy of the Staff Details spreadsheet.
Highlight the column headings (cells from A5:G5).
Go to the Home ribbon bar and in the Styles section, click on the Cell Styles button.
This brings up the Cell Styles dialogue box (shown left).
In the Titles and Headings section, select Heading 2.
Select Cell F34 and type in ‘Total’. Ensure the cell is still highlighted.
Click on the Cell Styles button again.
In the Themed Cell Styles section, select Accent 1.
Select Cell G34 and type in the formula ‘=SUM(G6:G33)’. Ensure the cell is still highlighted.
Click on the Cell Styles button once more.
In the Titles and Headings section, select Total.
Review your work. Looks good … doesn’t it.