Tracking Sales Performance Using Excel Functions

Every day, I receive comments and questions and many of them seem to concern one common problem. Companies or managers that have a sales force must track how their sales staff is doing. How much are they selling, how much commission are they making, what are they selling, etc. I’ve been answering several of those but given the still very strong demand, I thought I would start building a more complex spreadsheet that will help any of you that are managing such spreadsheets.

Of course, there are as many different types of spreadsheets as there are companies and very few would be able to simply take this spreadsheet and “run with it”. That is not the goal either. Rather, I hope to provide good examples of a structure or functions that can be used to easily manage a sales team. Keep in mind that depending on the complexity of your data and of the requests, the size of the actual database and many other factors, the required spreadsheet could end up looking vastly different. In fact, it could be using simple excel functions, macros or pivot tables.

I will start off with a rather simple spreadsheet, one where we have one tab that includes all of the sales data and one where we can get different types of data. I will be adding 1-2 functions every time, please be sure to let me know the types of things you’d like to see. For now, here is the data that I will be using:

The first query is quite simply to get the sum of sales and commissions for both sales guys. I will do so using the sumif function:

=SUMIF(data!A:A,info!C4,data!D:D)

The result is:

You can download the spreadsheet here!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss

***************************************************