Make your business more efficient with our Excel Tutorial on creating a sales and commission spreadsheet

In this article I am going to show off five top Excel tricks for making your spreadsheets sing and to help edit and refine your sales and commissions spreadsheet.

These tricks make it easier to create spreadsheets that are easy to use and which allow you to get to the heart of the matter immediately. None of them involve any scripting, VBA or macros and once learnt can be applied in any spreadsheet.

The tricks will cover layout and formatting, sorting, highlighting important data and searching for information.

Our working spreadsheet is a very familiar scenario: a list of orders sorted by date with prices and commissions and it looks like the sheet below.

1/ Formatting your spreadsheet

The first thing I want to do is a bit of formatting to make the headers stand out. I get the font, colour and alignment of cell A1 just right.

Everyone knows the little format painter icon on the cut and paste panel on the home tab in the ribbon bar. When you click this it picks up the format of the current cell and allows you to paint another cell or range of cells. It only allows you to do this once, if you have lots of different cells you want to format the same way you need to re-click the format painter. With a big spreadsheet this could be a bit 'peck and hunt'. In modern versions of Excel though you can double click the format painter to lock it on. It changes to and stays that way until you next click it. So I can easily format my page.

2/ Editing headlines to fit the page

OK, so each header looks nice but we have another classic spreadsheet problem: the columns are a bit congested, and if I fix that then the data goes off the page.

I can solve this problem elegantly using the Orientation button on the Alignment panel on the Home Tab:

It's the little angled 'ab' with an arrow. I select all the headings and use this dropdown to select 'angle counterclockwise'. This tips the column headers on their side and I can now shrink the columns to make the page nice and compact whilst keeping the column headers clear:

3/ Conditional formatting to highlight what's important

The next trick is to highlight the most important bits of information for me eg my commission. To do this we are going to use conditional formatting from the Styles panel on the home tab:

The drop down offers a number of options:

• Highlight Cell Rules
• Top/Bottom Rules
• Data Bars
• Colour Scales
• Icon Sets

I am going to choose the icon sets option and then the simple traffic lights option. If I select all the commission payments and apply conditional formatting I get:

Excel has applied the default rules to the traffic lights:

If I go back and select the menu Conditional Formatting -> Icon Sets -> More Rules we get the rules dialog box:

This shows the default rules are green for the top third of values, red for the bottom third and yellow for the middle third.

There is a tricky problem with conditional formatting that trips people up. These conditional rules apply to all the values in the selection at the time the formatting was applied. I need to select all the values and apply the conditional format to them as a group. I want to flag all payments above £40 as green, and all those below £20 as red because they are too small to worry about. The middle set get yellow. We can just select all cells and edit the formula to do that.

4/ Grouping and sorting by a filter

In order to improve my sales performance I need to be able to group all the sales to a single customer account and work out which ones are valuable to me. I am going to do that with the sort and filter function on the sort and filter panel on the data tab.

I select columns A-H in my spreadsheet and use the filter. I can now sort and filter the data by the various columns:

The little drop down buttons on the headers open a filter/sort dialog box when they are clicked on:

This dialog box lets me sort the data by rows, or display only some rows to look at. I can now pick out a particular customers account out of the long list of orders and figure out what they buy and how to sell more to them.

I am going to setup statuses on each order so that I can start using this list to manage my work.

Our possible statuses for each order will be:

• Placed
• Delivered
• Follow up call required
• Completed

This way I can get a new order – chase up at our end to make sure it is delivered, follow up with the customer to see if they are happy and need a new order and finally close off the transaction.

5/ Validating your data

Lastly I want to be able to filter my work list on the work status to make sure I keep everything on the level.

I select the cells in column H and use the Data Validation dropdown in the Data Tools Panel on the Data Tab:

Selecting Data Validation from the menu opens this dialog box:

I can set up the status to come from a list of values – Ordered, Delivered, Followup or Completed. When I click on these cells now they look like this:

I can now only enter one of those four values in the cell.

Ok that's pretty cool, but the data is one line per order – that's not how I work. I sell to accounts, I need to know how valuable each account is. My fifth and final trick will show you how to do this.

Let's sort the data by account number first – then I am going to use the Subtotal command on the outline panel on the Data tab:

When I select columns A-H and then click Subtotal I get the subtotal dialog box:

I am going to change it to sum the commission amount for each account number. When I do that I get:

It has now nicely totalled my commission from each account so I can work out which accounts to focus on.

Gordon Guthrie loves spreadsheets so much he has written his own – Vixo, like spreadsheets but better, which you can explore at Vixo.com - follow him on twitter @gordonguthrie.