Excel 2010 Masterclass – Table and Cell Styles

Excel 2010 Graphic
You can enhance your drab, dull and boring Excel spreadsheets very quickly and easily by applying pre-formatted styles to them. This blog will show you how.

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.

Table Styles

Open the downloaded file ‘staffd.xlsx’.

Make sure the Home ribbon bar is selected.

Excel 2010 Graphic

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).

Excel 2010 Graphic

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.

Click OK.

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.

Excel 2010 Graphic

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.

Excel 2010 Graphic

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.

Select Kilmarnock.

Click OK.

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.

Click OK.

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.

Cell Styles

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.

Excel 2010 Graphic

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.

Bob Thomson is an IT professional with over 20 years experience in the industry having worked as a Consultant, Director, Manager and Trainer. He has vast experience using Microsoft software packages as an individual and in businesses situations.

Bob Thomson
Owner / Managing Director
Real Options