Microsoft Excel is one of the most useful computer programs ever created. Need help with math? Excel has you covered. Mail merge? Excel’s got it. Quickly summarize large data sets? You can do it with Excel. There are so many useful functions in Excel that there are entire books on the topic!
Most business owners don’t have time to sit around and read Excel books. I used Excel roughly eight hours a day, five days a week, for nearly a decade. Here are some of the most useful Excel tricks I learned along the way that you can use to save time working on your business.
Basic math functions
My first experiences using Excel happened in my high school honors science classroom, and our teacher started us out teaching basic math functions in Excel. This is the foundation of all spreadsheet skills, so it is the best place to start, particularly if you are new with Excel or have very little spreadsheet experience.
Most of what you need to know to do math in Excel you learned in pre-algebra. Excel, and other spreadsheet programs, do math with formulas. Let’s say you have three rows of numbers you want to add up. This is one of the easiest functions in Excel:
As you can see in the screenshot above, all you have to do is type the cell location (e.g. B2) or click on the cell, type in a plus sign, click the next cell, and so on. When you’re all done, hit enter and Excel magically calculates the answer.
Pivot tables
If you want to add or summarize any numbers by category, pivot tables are your best option. While they are considered an “advanced” Excel skill, they are actually quite easy to use. Once you give it a try a couple of times, they are the fastest option to summarize any dataset, large or small, into a digestible table that is easy to manipulate.
I added a new column in the spreadsheet to break down our widgets by category: flying or driving. If I want a total of widgets by category, I can add up each cell, but with larger lists that is not easy or practical. That’s where I click to add a pivot table.
Add a pivot table under the “insert” tab. I had to click table and choose Pivot Table, then this screen popped up. I just stuck with the default, which automatically selected my existing data and creates a pivot table in a new tab.
From here, everything is drag and drop. I want a sum of inventory, so I drag that into the Values box in the pivot table builder that popped up. I want the total by category, so I dragged category into the rows box. That’s it, my pivot table was automatically created with that criteria! No additional manipulation needed!
You can drag and drop each column from your original spreadsheet into the rows or columns box in the pivot table builder to add or change the breakdowns or switch from addition to any other math function. But in most cases, I find that all I need to do is add things up.
SUMIF
Pivot tables can accept a huge number of inputs, but sometimes you have a smaller dataset and just want to add up items that meet specific criteria. SUMIF is a great option for that. In fact, we can use SUMIF to do the same thing we just did with the pivot table. It’s just another road to the same destination.
As you can see, the formula here is a little bit more complicated. Lucky for us, Microsoft built in a formula assist feature that tells us what we need to add, comma separated, to get the desired result.
I wanted a total of all inventory with the category flying, so for the first input I highlighted the column that contains the category, after the next comma I chose a cell that included the word flying (it has to match exactly for the formula to work correctly), and in the last argument I told it to add up the inventory. Essentially my formula says: If anything in column C says “Flying”, include the count in column b from that row in the total. I did this twice to get the total for both categories.
As you can see in the screenshot below, the result is nearly identical to the pivot table:
Bonus Tip: Combine and manipulate text
Excel makes it easy to merge, add, subtract, multiply, divide, and summarize numbers. Working with text can be easy too, but you need a few tricks up your sleeve.
Every year I run a speaking event called Ignite FinCon, which takes place at the annual FinCon conference. Anyone who wants to speak submits a topic through a Google Forms form, which I can export to a Google Sheets spreadsheet. I can work with the sheet in my browser (what I normally do) or download it as an Excel file I can open with Excel on my computer. This trick, like those above, works the same way.
I always want to export to another Google Form where participants can vote on topics, so I merge the text cells using a formula. You can combine text using the & character and quotation marks.
Let’s say you want to combine the text in cells B2 and B3. Just say =B2&” “&B3. The result will contain the text with a space in between. I combined name, website name, and topic with a dash in the middle to create a nice, readable entry for the voting form.
Excel is easier than most of us think!
Excel is not just for financial analysts, accountants, scientists, and engineers. Anyone can use Excel to help their business. I used Excel every single day at work for years, and love when I have a chance to use it in my online business.
Before you know it, you’ll be jumping around spreadsheets with keyboard shortcuts and a whole lot more! The more efficient you are with Excel, the sooner you’ll get the data you need and get back to work. Spreadsheets are a great tool, and one no business owner should go without.
