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

Excel 2010 Masterclass – Naming Cells and Ranges

Excel 2010 Graphic
How many times, when working with spreadsheets, have you come across formulas such as =B7-B20 or =SUM(B10:B19) and spent ages trying to fathom out what they mean? Wouldn’t it be good if we could give logical and unique names to cells and ranges?

Well we can. By naming cells and ranges, our formulas could now become =Total_Income-Total_Expenditure and =SUM(Expenditure) or you could use some other naming strategy that would mean something to you. This process makes cell referencing and cell selection a lot easier.

Naming Cells

You can download a sample household accounts spreadsheet >>here<<, in Excel 2010 format, to accompany these instructions.

Open the downloaded file ‘haccounts.xlsx’.

Select cell B6.

If you look to the left of the formula bar, near the top of the screen, you will see the names box (shown below). Highlight the B6 name and type in Total_Income (use the underscore because spaces are not allowed). Press Enter to confirm.

Excel 2010 Graphic

You have now renamed cell B6.

Select cell B19 and rename it Total_Expenditure (remember the underscore). Press enter to confirm.

Select cell B21 and rename it Balance. Press enter to confirm.

Click on the down arrow on the right-hand side of the name box and you should see the three names you created appear.

Excel 2010 Graphic

Naming Ranges

Naming ranges is a very similar process to naming cells. This time we highlight the cells we want in the range and then we name them as before.

Select cells B4 and B5. In the names box, highlight the current reference (B4) and change it to Income. Press Enter to confirm.

Select cells B9 and B18. Name the range as Expenditure.

Again, these new names will be added to the drop-down box. Check it out.

Finishing off

Select cell B6 (the one we named Total_Income). Click on the Autosum (Ʃ) function (you will find this on the Home ribbon bar in the Editing section). You have now added the formula =SUM(Income), notice it has used the range name we entered. Once more press Enter to confirm.

Select cell B19 (the one we named Total_Expenditure). Click on the Autosum function again. This time the formula should be =SUM(Expenditure). Press Enter.

Select cell B21. Type in the equals sign to let Excel know we are adding a formula by hand. Select cell B6 to add the Total_Income to the formula. Type in the minus sign. Select cell B19 to add the Total_ Expenditure to the formula. The formula in B21 should now read = Total_Income- Total_ Expenditure. Press Enter.

You have now finished the exercise.

Excel 2010 Graphic

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.

He is also the Group Manager of the East Ayrshire Business Forum on LinkedIn.

Bob Thomson
Owner / Managing Director
Real Options