19 Oct SumIf, VLOOKUP and Pivot Tables: 10 Powerful Excel Tools for Business Owners
Excel is one of those business tools that you either absolutely love or you tolerate because it’s useful for making lists or tracking information. Truth be told, most people, from business owners to teenagers, fall into the latter category. Sure, you felt like a programming genius when you figured out the AVERAGE formula but you’re only using a fraction of its true potential because there’s simply too much to learn and who has time for that?
Well, we want to help you take your Excel game to the next level. We’ve compiled 10 of our favorite shortcuts, formulas and tools that every business owner should have in their arsenal.
Alt + =
This keyboard shortcut will quickly calculate and provide the sum for the group of selected cells. Added benefit? It’s smart enough to select the cells above, below or to the side of your destination cell, depending on where the numbers are located. This means if you click on a column to the right of your data, it will automatically select a range of cells to the left that contain numbers.
EXAMPLE: We want to quickly calculate the total sales listed in column G, so we click on cell G45 and press Alt + = which generates the formula =SUM(G2:G44) automatically.
Ctrl + Shift + L
This shortcut toggles the Autofilter so you can sort using the auto-generated criteria. This shortcut also toggles the filter off if you want to remove it for formatting reasons.
EXAMPLE: We want to quickly sort by Order Date, so press Ctrl + Shift + L to toggle the Autofilter feature, which turns the headings at the top of each column into drop-down filter lists.
Ctrl + Shift + ~ / $ / % / etc
This keyboard shortcut applies automatic number formatting (~ for General, $ for currency, % for percentage, ^ for scientific, # for date, @ for time, and ! for number). This is really helpful if you get a spreadsheet someone else created but didn’t format correctly as you’re able to select a range of cells and apply the formatting with a few keystrokes.
EXAMPLE: When we received this spreadsheet, there was no formatting applied to any of the numbers. We want Unit Cost and Total to display dollar amounts, so we select the contents of those columns and press Ctrl + Shift + $ to automatically apply currency formatting.
Want more keyboard shortcuts for Excel? Check out this page from ExcelJet: 222 Excel Keyboard Shortcuts for PC and Mac
This allows you to tell a cell to provide a total, if a specific criteria is met. If you have a spreadsheet with all items sales for the entire year, but you only want to see the total sales for March, SUMIF would allow you to generate that dollar value.
EXAMPLE: On our spreadsheet we want to calculate the total sales for pencils, so we use a SUMIF formula to return the sum of total sales from rows that contain “Pencil”.
For more information on SUMIF, be sure to check out this article from Tech on the Net: MS EXCEL: SUMIF Function
This formula allows you to search a large table of information for a specific piece of data. For example, if you have a large table of test scores on one sheet and student names on another, you could use VLOOKUP to locate the scores for a specific student.
EXAMPLE: We want to only pull all of the sales associated with the sales rep named Jones, so we set up a VLOOKUP on a new sheet which tells the program to look first for “Jones” in column C and if it finds it, to return the value shown in D so we can see what he sold. Then in the next column we use the VLOOKUP function to tell Excel to find “Jones” in Column C and then return the value in Column G, so we can see the total value of that sale.
Want to know more about VLOOKUP? Check out this article from Microsoft: VLOOKUP function
INDEX MATCH is one of those really powerful Excel tools that as a business owner, you really should have in your arsenal. Similar to VLOOKUP, INDEX MATCH allows you to search a large table of information for specific pieces of data. The difference between the two is that INDEX MATCH allows you to specify the columns to search. This is helpful if you have a spreadsheet with a large amount of data as it works as a right to left lookup whereas VLOOKUP only works left to right. The other benefit to INDEX MATCH is that it will return to the same values even if you have to go in and add additional columns to your spreadsheet. Since VLOOKUP is based on an array, adding any additional columns can throw off the results.
EXAMPLE: We want to be able to type in a date and see what item was sold. To do this, we use INDEX MATCH to first search for the date in column A and once it’s found, return the value in column D that’s located in the same row.
For a more in-depth look at how to use INDEX MATCH, check out this article from Random Wok: How to use INDEX MATCH
Tip 7 – Conditional Formatting
This tool allows you to automatically adjust the formatting of a cell based on the contents. For example, if you want all cells with a value of “0” to appear read, you can set up a conditional formatting rule that will turn those cells red. In the same spreadsheet you could also set up a rule that will turn all cells green if the value is greater than 5. This is great for creating visual cues to find specific information.
EXAMPLE: We want each Region to show up as a different color. So we select that column and open the conditional formatting menu. We then tell excel that if the contents of the cell is equal to “East” it should turn the cell green and the text dark green.
For more information on Conditional formatting, check out this Microsoft Knowledgebase article: Highlight data points with conditional formatting
Tip 8 – Pivot Tables
Pivot Tables are often referred to as one of the most powerful Excel (and spreadsheet) tools. Pivot tables allow you to extract useful and detailed information from a large data set. For example, if you have a spreadsheet that tracks common product issues, you could use a pivot table to automatically create a chart that will allow you to see a list of those problems and how often they’ve been reported. You could even expand that criteria and determine how many times each problem was reported during each month for the last year.
EXAMPLE: We want to create a chart that displays sales totals for each representative, broken down by item. To do this, we select the contents of the sheet, go to Insert and then Pivot Table. From there we drag and drop the desired measurement criteria onto the chart and Excel automatically generates the results based on the source worksheet.
For more information on how to create Pivot Tables, check out this article from HubSpot: How to Create a Pivot Table: A Step-by-Step Tutorial
Tip 9 – Goal Seek
Goal Seek is a tool located in the Data tab that allows you to forecast what number you need (in dollar, units, clicks, etc) to get to a specified goal.
EXAMPLE: Company A has a sales goal to reach $75,000 by September, so we can use Goal Seek to see how much we need to do in sales to hit that goal. A few mouse-clicks and keystrokes and we’ve got our September goal set and ready to go. This is a relatively simple scenario and Goal Seek can be used across multiple industries to figure out other more complex calculations.
Want more information about Goal Seek? Check out Tech Republic’s Article: How to use Excel’s Goal Seek Analysis Tool
Tip 10 – Record Macros
Record Macros allows you to automatically ‘record’ macros to automate common, repetitive tasks. A great example is if you receive large file of raw data on a regular basis that needs to be imported, formatted and sorted a specific way. By recording a macro of the import, format and sorting process you’ll turn a task that can take hours into one that will be completed in minutes with just a couple of keystrokes or mouse clicks. Other tasks a macro could help you automate include removing blank rows, splitting names from one column into two and entering repetitive text onto sheets.
For a more indepth view on how to create Macros, check out this article from PC World: 5 Essential Tips for Creating Excel Macros
Needless to say there are dozens of other cool tools, tips and tricks to learn about Excel. Tell us in the comments what other Excel tip you’d share with other business owners.