At Capsule, we’re rather partial to a good Excel formula. If you need to track and analyze data, the humble spreadsheet can be extremely useful.
For example, imagine you’re running a summer lemonade stand empire. Yes, an empire. You’ve got multiple stands across town, each with its team. Nothing is stopping you from tracking everything, from lemons bought to cups sold, all in a spreadsheet. With a few keystrokes, you can magically add up total sales, calculate profits, and even forecast how many lemons you’ll need next week.
Everyone knows that Excel formulas (or formulae, strictly speaking) are undeniably cool – whether you’re managing a lemonade empire, a sales team, or your personal budget. They’re quick, powerful, and get the job done.
Of course, some people will tell you there’s an easier way to juggle all this data. They’ll say that a CRM like Capsule does all the hard work for you. But for the time being, let’s celebrate nine of the best Excel formulas every business owner and sales manager should know.
1. SUM: The classic go-to
There’s a reason SUM is one of the first formulas anyone learns in Excel. Whether you’re tallying up sales, tracking expenses, or just trying to figure out how many donuts are left in the office break room, SUM does it all. You highlight a range of cells, hit enter, and boom, your total is there.
Let’s say you’re running that lemonade empire and need to figure out how much money you brought in last week across all your stands. With SUM, you can add up the sales from each location in seconds, without having to recount all your receipts or scroll endlessly through rows of data.
“For example, =SUM(B2:B10) will add up all your values in cells B2 through to B10.”
While SUM works great for small calculations, imagine not having to do any of that manually. With Capsule, your sales data updates automatically, and it’s all in one place – no need to pull numbers from multiple tabs or type formulas. You get real-time insights at a glance, saving you even more time and letting you focus on making those business decisions instead of crunching numbers.
2. VLOOKUP: Finding the needle in a haystack
We’ve all been there: you’re staring at a sea of data, looking for that essential piece of information you need. VLOOKUP is your bloodhound, searching through rows and columns to bring back exactly what you’re looking for.
Let’s say you’ve got a long list of lemonade stand locations and their corresponding managers. You need to find out who’s running the downtown stand. Instead of scrolling through rows and rows of data, a quick VLOOKUP instantly pulls the manager’s name. Case solved!
XLOOKUP, (VLOOKUP’s newer, cooler sibling) takes it up a notch by being even more flexible. For example, VLOOKUP can only take data from the right side of the search column.
On the other hand, XLOOKUP has the superpower of being able to look for values to the left and right.
“For example, =XLOOKUP(A2, D2:D10, E2:E10) searches for A2 in column D and returns the corresponding value from column E.”
As amazing as VLOOKUP and XLOOKUP are, they can get tricky with larger, messier datasets. Capsule does the heavy lifting for you. Need to find a client’s contact info or check on a deal’s status? Capsule stores everything in a neat, searchable system, with no VLOOKUP error messages to worry about.
3. IF statements: Decision-making in a cell
If there’s a formula that can make you feel like a savvy computer programmer, it’s the IF statement. This nifty little formula helps Excel make decisions for you. Basically, you’re telling Excel “If X happens, then do Y, otherwise do Z.” It’s great for creating automatic actions based on specific conditions, like sorting leads into categories or flagging overdue tasks.
Back at our lemonade stand, let’s suppose you want to see whether your stands hit their weekly sales targets. With the following IF statement, Excel checks to see if the sales in B2 are over 500. If so, it’ll tell you the stand exceeded its target. Otherwise, it’ll flag it as needing improvement.
“=IF(B2>500, "Exceeded Target", "Needs Improvement")”
IF lets you build simple logic into your spreadsheet, but once your business grows, setting up conditions for every little detail becomes a time sink. Capsule automatically categorizes and segments your leads, customers, and deals based on real-time data. With dynamic saved lists, you can see an up-to-date list of every lead that’s ready for a follow-up. Capsule has it covered – no formulas, no fuss.
4. CONCAT: Bringing text together
Like a cool glass of lemonade and a hot summer’s day, some things just go well together. CONCAT lets you combine text from different cells into one, allowing you to unite first and last names or combine product codes and descriptions in perfect harmony.
So, if you have a list of your lemonade stand employees, with their first and last names in separate columns, CONCAT can merge those cells into a single “Full Name” column.
“=CONCAT(A2, " ", B2) combines the values in cells A2 (first name) and B2 (last name), with a space in between to create a full name.”
While CONCAT works wonders for merging names or data points, Capsule does this work behind the scenes for you. All your customer info – names, contact details, notes – is automatically connected and organized, so you never have to worry about piecing it together manually. Everything’s already in its place, ready when you need it.
5. COUNTIF: Counting what matters
Wonder how many times something shows up in your data? COUNTIF counts the number of cells that meet a certain condition, whether that’s tracking how many reps hit their sales target, how many leads are in a certain status, or how many times your favorite client’s name pops up.
If you want to know how many of your lemonade stands surpassed $500 in sales last month, a quick COUNTIF can check the sales column and tell you exactly how many stands hit that milestone.
“Just tap in =COUNTIF(B2:B10, ">500") for instant enlightenment.”
There’s also a plural version, COUNTIFS, where you can count the number of cells that meet multiple criteria.
COUNTIF is a fantastic tool, but Capsule gives you real-time reports and dashboards that show key metrics like sales performance and lead statuses automatically. No formulas are required, and you can easily drill down into the details for additional insights.
6. TEXT: Formatting with ease
Sometimes, the numbers in your spreadsheet are technically correct but somehow still don’t look quite right. The TEXT formula lets you format numbers such as dates, times and currency in a way that makes them easier to read and work with.
“For example, you can use =TEXT(A2, "MMMM D, YYYY") to convert your date in the A2 cell from a long, clunky format (like “2024-10-01”) into a cleaner, more readable one (“October 1, 2024”).”
TEXT gives you more control over how you present your data, ensuring everything looks sharp before you share it with the team (or investors).
While formatting in Excel is handy, Capsule automatically presents your data in a clean, organized layout. Everything’s readable and professional, so you can focus on the information behind the data.
7. AVERAGE: Finding the middle ground
When you need to understand the overall trends behind your data, AVERAGE can be used to get a quick snapshot of your typical sales, deal sizes, customer satisfaction ratings and more.
So, you’ve got sales data for each lemonade stand, and you want to know how your empire is performing overall. Using something like =AVERAGE(B2:B10) for all your sales figures shows you whether your stands are making bank or if it’s time to step up your lemonade game.
Instead of getting lost in individual numbers, AVERAGE helps you see the bigger picture, spot trends and make more informed business decisions.
While AVERAGE is handy for quick calculations, Capsule gives you a more detailed look at your business’s performance. The built-in reporting tools offer insights like average deal size or average time to close a sale at a glance. It’s like having your data analyst on standby, ready to provide answers in real-time.
8. INDEX and MATCH: The power couple
Batman and Robin. Bert and Ernie. Deadpool and Wolverine. We all love a good partnership, and INDEX and MATCH are the dynamic duo of Excel. This two-part formula is like VLOOKUP’s more advanced, flexible cousin, allowing you to look up values from any direction and across multiple ranges.
Let’s say you’ve got your lemonade sales data in one sheet and your stand locations in another. With INDEX and MATCH, you can pull the correct location for each sale without having to rearrange your data.
“For example, =INDEX(D2:D10, MATCH(A2, E2:E10)) will search for the value in A2 within the range E2:E10, then return the corresponding result from D2:D10.”
INDEX and MATCH are lifesavers for Excel users, but Capsule automatically connects all the dots between your customer data, deals, and interactions for you. Need to find a client’s contact info or see all their associated deals? Capsule’s got it all laid out without the need for complex formulas.
9. ROUND: Keep it clean
Precision is great, but too many decimal places can make your data look messy. The ROUND formula tidies up your sales figures, percentages, or financial data so it’s clear and concise.
So, you want to improve the efficiency of your lemonade stands and you’re tracking the average time it takes each of your stands to serve a customer. The result is 3.6789 minutes. A quick ROUND formula like =ROUND(B2, 2) rounds the value in B2 to two decimal places, giving you a more digestible 3.68 minutes.
A neat trick is using negative numbers in the formula. For example, you could round a number to -3 decimal places, which actually rounds it to the nearest thousand. This comes in handy if you’re reporting financial details, which is often in thousands or millions.
ROUND helps your reports look professional without drowning your audience in unnecessary details. However, with a CRM like Capsule, your data is already properly formatted. Whether you’re looking at deal values, customer info, or sales metrics, Capsule presents everything in a clean, professional layout.
Excel formulas are great, but Capsule CRM is better
All joking aside, Excel is an incredible tool. Whether you're running a lemonade empire or managing sales for a small business, Excel gives you flexibility and control over your data.
However, as your business grows, so does the complexity of your data. Manually managing spreadsheets, updating formulas, and piecing together information from multiple sheets can become a time-consuming puzzle.
This is where Capsule CRM shines. While an experienced data scientist can use Excel to analyze your information in all kinds of fantastic ways, Capsule keeps things simple. Capsule automates much of the data management process, keeping everything connected, organized, and up to date without the need for endless formulas.
In short, Excel is awesome – but when it comes to running a business, there’s a better way. Capsule CRM takes the heavy lifting out of data management, allowing you to spend less time on spreadsheets and more time building relationships with customers and closing deals.
Try any Capsule plan free for 14 days or sign up to our free plan to get started with a simple yet powerful CRM.
