Do you cringe when you hear the word “spreadsheet” or dread using Microsoft Excel or Google Sheets to perform a task? Well fear not, we are here to turn you into the Superman of spreadsheets!
The IF function
The IF statement is a powerful function that allows you to analyze a column of data and determine if the data in the cell meets as a criteria or not. Here’s the function overview:
IF(criteria, value if yes, value if no)
The criteria performs the logical check, and if that check executes to true you can set the function to return any value that you define. It could be “Yes” or “True” or “Awesome! :) :) :)” and same with the value if no - its totally up to you.
Let’s see it in action: If I have a spreadsheet that has 2 columns, Account Name and State (2 letter abbreviation). From this spreadsheet my sales manager has asked me to compile a list of accounts that are from the Massachusetts (MA) area. What a perfect use case for and IF statement.
In order to determine which accounts are from MA, here is the IF statement:
IF (B3 = “MA”, “Yes”, “No”)
The IF statement ran on the State column to check for MA results in the following:
The COUNTIF / SUMIF functions
We just learned about IF statements, and there’s two other functions that use the same principles to achieve two goals - conditional count and sum.
Here’s the formula for COUNTIF: COUNTIF(range, criterion)
Give our table from the first example, the State column is filled with a few Accounts in MA. I can use COUNTIF to get a single number for the number of Accounts in MA.
COUNTIF(F3:F7, "MA")
The result is “3” using COUNTIF:
SUMIF operates under the same philosophy but has 2 ranges, the check range and the sum range.
SUMIF (range, criterion, sum range)
I’ve slightly modified the example column by adding sum data (Amounts of dollars won).
Now we’re ready to SUM all the amounts where the State is MA. Here’s what the function looks like:
SUMIF(B3:B7, "MA", C3:C7)
And now, the big reveal, the answer is $578. Our function only summed the rows where the State is MA, brilliant. Use this next time you are doing territory reassignments.
The SPLIT function
The next time you need to do an analysis using data out of your CRM and it ends up being a jumbled mess of colons, commas or parentheses - don’t sweat it. SPLIT is going to be your best friend and it is truly awesome. SPLIT allows you to take a cell and remove text based on a marker (also known as a delimiter). Here’s an example.
Say you export data out of your CRM related to the number of leads by 'Lead Source' that you have received for the last week and you want to make a stacked bar graph in your spreadsheet app.
Now unfortunately your spreadsheet app probably will not be able to plot this because the number is contained in the same cell. So naturally you can just type in the numbers 10, 5, 14 and 12 because this is a small example, but if this was hundreds of lead sources, you’d probably not sign up for that task. So instead, save yourself future grief and use SPLIT.
Here’s how I would SPLIT these so that we have two columns of data with lead source and number. If you look, each cell has a colon, in the same spot. SPLIT loves repetition like this, so if we create the function and apply it to all the cells:
SPLIT(A1, “:”)
We get beautiful, wonderfully split cells:
Resist the temptation to perform this manually and just use SPLIT already :)
VLOOKUP formula
I have saved the best for last. You’ve definitely learned about super powers but you aren’t quite ready to leap tall buildings in a single bound… until you harness the power of vertical lookup, also known as VLOOKUP, the most awesome function there is.
Let’s go through a hypothetical example. Say your Controller comes to you says “Can you look through all these transactions that have occurred and cross reference them against the data in our CRM and tell me which are yours?” Now the good thing is that your Controller has provided the transaction data with IDs that share a common key with the Account data in your CRM, so you can technically do the analysis… but there’s hundreds of transactions and by now you know from my SPLIT example, I hate doing things manually when there’s a spreadsheet function to use!
Let’s get back to it, on one hand you have the Transactions by ID, it’s a stream of IDs - with a few duplicates.
Now, your Controller has asked you to determine which accounts you own out of this list. But the reality is, you definitely don’t know your Accounts by the number ID. Good thing your CRM has a report that your Sales Ops can provide that links ID to Account Name.
And now, you are ready to take the first step to a dedicated comic book and a ride at Universal Studios. The magical VLOOKUP. Here’s how it’s set up:
VLOOKUP(search_key, range, index, [is_sorted])
The “search_key” is the cell that you are trying to match on. So that would be the Transaction by ID. The “range” is the table that it is being joined with, in this case it would be the ID and Account Name, all the values starting at 1 and ending with MI6. The “index” relates to the column that you want to return from the table defined in the range, in this case, it would be 2. Lastly, the “is_sorted” is a bit confusing, so I always set this to FALSE for 99% of all cases, you should do the same.
Here’s the function all wired up:
VLOOKUP(V4,$S$3:$T$6,2,FALSE)
One note, on the $ in the range… that means that the spreadsheet app won’t continue to slide the range downwards when you click drag it. It will always keep the same range. Its called an absolute reference.
And now we have the answer about what Accounts relate to the Transaction IDs that were provided.
In conclusion, spreadsheets are commonly found in sales teams. Being a rep that knows your way around a function or two can help you out in the long run. Now if you are more of a visual learner and like to tinker, We’ve created a spreadsheet with all the examples that you can get access to here.
One more thing while we're on the topic of Microsoft Office products. If you currently using Microsoft Word to send and negotiate sales documents, check out LinkSquares, a single platform to create, send, sign, and track your sales documents.
LinkSquares integrates with Salesforce, so both legal and sales can work in the systems they love. With the LinkSquares Finalize App in Salesforce, you can get agreements drafted and out the door faster with centralized communication and real-time updates. Request and draft contracts, right in Salesforce. Just fill in a few fields and the agreement will automatically flow into LinkSquares for legal to review. Boom, no more disruptions.
Want to learn more? Request a demo today.