Excel 2010 Masterclass – Sorting and Querying Data

blackboardexcel7Okay, we have now created our spreadsheets and got them looking great but now we need to put them to good use.

There are many tips and tricks we can employ and one of the most common and useful techniques to help us on our way is the ability to sort and query our data.

Microsoft Excel has built-in functionality exactly for this purpose so let’s take a look at what is possible.

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

If you have downloaded the file, open it and click on the box to Enable Editing. This will put the instructions in context and make it easier to follow the blog post.

Now that the spreadsheet is open, the first thing we will look at is sorting data.

Sorting Data

excel005_01

We are going to apply a custom sort to our data according to Surname and then First Name.

On the Home ribbon bar click on the Sort & Filter button that you will find in the Editing section (shown right).

Select the Custom Sort option.

The Sort dialogue box appears.

In the Sort by box, click on the down arrow and select Surname from the options on show.

Now, click on the Add Level button on the top-left of the dialogue box.

A new row has been added.

In the new Sort by box, click on the down arrow and select First Name from the options on show. (Shown below).

excel005_02

Click OK.

Examine your data and note that your changes have taken place.

Filtering Data

We are now going to query our data by using a function called Filter.

On the Home ribbon bar click on the Sort & Filter button that you will find in the Editing section (shown right).

Select the Filter option.

Notice that your headings now have a down arrow displaying on their right-hand side. (Shown below).

excel005_03

Now, imagine you have been asked by the local council to supply the number of employees that live in Kilmarnock and earn more than £20,000.

excel005_04

First of all we will filter out the number of employees that live in Kilmarnock.

Click on the down arrow to the right of the Town column heading.

In the dialogue box that appears (shown right), you will see all of the town options that are available. We are only interested in the town of Kilmarnock for this exercise so…

Click on the (Select All) box that you will find above all the town names. This will de-select all the towns.

Now, click in the box beside the Kilmarnock option.

Click OK.

Note that the spreadsheet is now only displaying the employees living in Kilmarnock and that the down arrow to the right of the Town title has changed, and added a filter icon.

Now let’s sort out the salary constraint.

Click on the down arrow to the right of the Salary column heading.

Select the Number Filters option.

In the new dialogue box that appears, select Greater Than.

In the Custom AutoFilter dialogue box, type 20000 in the box to the right of where it says “is greater than”.

Click OK.

How many employees are left that comply with the filters you set?

If the answer is six, you have done very well.

That is the end of our Sorting and Querying Data masterclass.

Please use the comments box below to let us know what you think of our blogs.

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 – Freezing Rows and Columns

Excel 2010 Graphic
Freezing rows and columns is a built-in function that Microsoft Excel provides to help when working with large spreadsheets.

If you have worked with large spreadsheets before, then you will know some of the problems associated with viewing parts of the data area. If you haven’t, then download our accompanying spreadsheet and see what we mean.

You can download a sample staff details spreadsheet >>here<<, in Excel 2010 format.

Problem

If you have downloaded the Staff Details spreadsheet above, you will notice that when you scroll to the right you lose sight of the staff member names.

So, for example, if you want to know the date that Diana Prince started with the company, you find her name in the first column (column A) and scroll to the right to find the date she started (column K). Are you at the right date? It is hard to confirm if you can no longer see the persons name.

Subsequently, we now want to find out the date of birth for Sally Nice. Again, we find her name in the first column and then scroll to the right to the date of birth column. Which one is it? We can no longer see the column headings.

So that’s the problem … let’s sort it.

Solutions

Excel 2010 Graphic

     1. Freezing the top row

Go to the View ribbon bar and click on the Freeze Panes button that you will find in the Window section (shown right).

Select Freeze Top Row.

At first glance it looks as if nothing has happened but if you scroll down you will notice that the titles at the top are now always in view.

     2. Freezing the first column

Go back to the View ribbon bar and click on the Freeze Panes button that you found in the Window section.

Select Freeze First Column.

This unfreezes the top row from before and freezes the first column instead. Try scrolling from left to right and notice that, now, the staff members name is always on show.

     3. Freezing columns and rows together

Go back to the View ribbon bar and click on the Freeze Panes button that you found in the Window section.

Select Unfreeze Panes.

Select cell B2.

Go back to the View ribbon bar and click on the Freeze Panes button once more.

Select Freeze Panes.

This has frozen the top row and the first column at the same time.

Problem solved, methinks.

That is the end of our Freezing Rows and Columns masterclass.

Please use the comments box below to let us know what you think of our blogs.

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 – Conditional Formatting

Blackboard Excel Graphic
Conditional formatting is used when you want to apply a certain style to cells where particular criteria are met.

There are many excellent, pre-determined ways that Excel 2010 provides in order to format cells quickly, some of which are self-explanatory. This blog will concentrate on some of the less well-known techniques that still provide impressive results.

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

If you are ready to continue, let’s get started.

For all the upcoming exercises in this blog we will be using the Conditional Formatting icon that you will find in the Styles section of the Home ribbon bar.

Familiarise yourself with it now. Click on the small arrow at the bottom right of icon and see what options are available. (The icon options are shown below). We will deal with them in the order they appear.

Excel 2010 Graphic

Highlight Cells Rules

Let’s say we want to use conditional formatting to identify the salary bands of our company employees based on the amounts they earn.

The following steps will achieve this.

Highlight the cells containing the salaries (cells G6:G33).

Click on the small arrow at the bottom right of the Conditional Formatting icon. Click on the Highlight Cells Rules option and select Greater Than.

In the dialogue box that appears, type 30000 in the first box and select ‘Green Fill with Dark Green Text’ in the other. (Shown below).

Excel 2010 Graphic

Click OK.

Make sure the salary cells are still highlighted.

Click on the small arrow at the bottom right of the Conditional Formatting icon again. Click on the Highlight Cells Rules option and select Less Than.

In the dialogue box that appears, this time, type 20000 in the first box and select ‘Light Red Fill with Dark Red Text’ in the other.

Click OK.

Make sure the salary cells are still highlighted.

Click on the small arrow at the bottom right of the Conditional Formatting icon again. Click on the Highlight Cells Rules option and select Between.

In the dialogue box that appears there are now three input boxes. Type 20000 in the first box, 30000 in the second box and select ‘Yellow Fill with Dark Yellow Text’ in the third.

Click OK.

Click anywhere in the spreadsheet to view your handywork.

Top/Bottom Rules

Either undo the rules from the exercise above or download a fresh version of the spreadsheet. You are now ready to continue.

You can use these rules to determine the Top 10 items, the bottom 10 items, the top items as a percentage and the bottom items as a percentage etc.

In this example, we will use it to determine the staff members that are paid a higher wage than the mathematical average of the total wage bill.

Highlight the cells containing the salaries (cells G6:G33).

Click on the small arrow at the bottom right of the Conditional Formatting icon. Click on the Top/Bottom Rules option and select Above Average.

Excel 2010 Graphic

The dialogue box (shown left) appears.

This time I want to make it a bit more interesting so, select the ‘Custom Format …’ option for the selected range.

This will open up the Format Cells dialogue box.

Select a font colour, border colour and fill colour of your choice and click OK.

Click OK again.

Click anywhere in the spreadsheet to view your handywork.

Data Bars, Color Scales and Icon Sets

Once again, undo the rules from the exercise above or download a fresh version of the spreadsheet. You are now ready to continue with the final exercise.

This next set of rules are very similar so we will examine one technique in greater detail and leave you to play about with the rest to see what you can come up with.

In this example, we will revert back to the salary scales but with a further complicaton of hiding the exact salaries from unauthorised eyes.

Excel 2010 Graphic

Highlight the cells containing the salaries (cells G6:G33).

Click on the small arrow at the bottom right of the Conditional Formatting icon. Click on the New Rule … option.

The ‘New Formatting Rule’ dialogue box appears (shown right).

In the Format Style option box, click on the down arrow and select ‘Icon Sets’.

In the Icon Style option box, click on the down arrow and select ’3 Traffic Lights (Rimmed)’.

Tick the box that says ‘Show Icon Only’.

Change both the Type boxes to read ‘Number’.

Change the Value options to 30000 in the top box and 20000 in the bottom box (shown below).

Excel 2010 Graphic

Click OK.

Your completed spreadsheet should look like the one below.

Excel 2010 Graphic

That is the end of our Conditional Formatting masterclass.

Please use the comments box below to let us know what you think of our blogs.

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 – 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