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.
Article continues belowI 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.