Most financial leaders are spreadsheet junkies. We pore over mountains of data, seeking the latest trends and predicting how customers will behave in the future.
That’s why we live and breathe Microsoft Excel. Sure, it’s a common business tool that has been around for over 25 years, but it packs a lot of powerful functionality. Many of us at Hardesty have built our careers crunching numbers in Excel, so we’ve come up with our four favorite Excel functions and ways they can be helpful to today’s business executives.
The CHOOSE function
The CHOOSE function is handy when you need to return the values from an existing list depending on specific input. When building models, it can adjust formulas for a scenario selected from a drop down menu. Let’s say you were trying to work out total revenue generated by multiplying the units sold by the selling price. There are 3 possible scenarios for both units sold and selling price, and the CHOOSE function can be set up to automatically calculate the total revenue. When you are dealing with a huge spreadsheet, it helps users find out specific keywords from the datasheet rather than searching for it manually. Learn more about how to use CHOOSE through this tutorial.
The SUMIF function
The SUMIF function adds all numbers in a range of cells, based on a given criteria. If you’re looking for a total of a particular entry in a column or an array, SUMIF is the easiest way to get that total. For instance, if you have a spreadsheet of bikes sold and wanted to know the total, it can look for a bike and add the sale amount from another column to get the total for all bikes. We’ve also used the SUMIF function to find the total annual sales for sales representatives. View a simple example of how to use SUMIF here.
VLOOKUP
VLOOKUP is one of Excel’s most useful functions – it can find the value of anything in a database. This function, which stands for “vertical” lookup, can help you find specific information in large spreadsheets such as an inventory list of parts or a large membership contact list. For example, if you have a subset of employee information in a database and you want to reference their pay from another database, you can match their employee number or name and it will search the other database and pull out whatever info you need. Step-by-step instructions on VLOOKUP can be found here.
Pivot Tables
Suppose you’ve compiled a large list of data that is updated frequently, such as sales figures for all your business products. You’re ready to extract some meaningful figures, like total sales, bestselling products and highest-performing salesperson. A pivot table can summarize an array in any conceivable manner you like and then compile all the detail and summary levels in a view that’s easy to digest. You can use pivot tables to analyze the data, make comparisons, identify patterns and discover trends. Pivot tables can be created in as little as five minutes.
And speaking of pivot tables, have you tried PowerPivot? It’s a free add-in to Excel, but you need to download it separately from Microsoft. Bill Jelen (aka Mr. Excel) calls it “the best new feature to hit Excel in 20 years” and we agree! Look for more information on PowerPivot in the Hardesty blog later this month.
What are your favorite Excel functions?
If you need some sophisticated data analysis, Hardesty can help. Visit hardestyllc.com to learn more.
