Main Content
Finance professionals live and die by Microsoft Excel. Proficiency in Excel is crucial to your success in finance, as it's still the most widely used software for spreadsheets. And yet, most of us only use a fraction of Excel's available functions. It’s like using only a hammer and screwdriver while having an entire tool chest at your disposal.
Take full advantage of Excel’s benefits by diving deeper into the numerous functions, tricks and shortcuts that can make finance professionals and FP&A teams even more productive. Whether you’re a seasoned vet and know Excel like the back of your hand, or a junior financial analyst looking for ways to improve your spreadsheet prowess, the tips we cover in this blog can make your job easier.
Excel is to finance what the crane is to construction. You need Excel skills to succeed in FP&A. It’s as simple as that.
The average knowledge worker spends more than 20 hours per month working on spreadsheets, and 85% of those workers use Excel.
As stand-alone software, Excel's ability to process mathematical equations and manage and store data is second to none. Calculations that would normally take hours to complete can be presented in spreadsheets in seconds.
Any number of what-if scenarios can be shown with a few assumption updates. What's more, there are enterprise-class solutions that complement and leverage the power of Excel. With all that in mind, we’ll outline some tips, tricks and shortcuts to help you and your finance team unlock Excel's power for FP&A and beyond.
Microsoft MVP Liam Bastick has previously discussed some of the following functions as being great to learn when working with data regularly. But there are more you can add to your Excel skill set Here is a list of 13 Excel functions every finance professional should know.
The VLOOKUP function allows users to search for specific data or values in a table by range or row, the "V" actually stands for "Vertical." The function can be used to retrieve or combine data, generate reports or even look up pricing, phone numbers or others. Here’s how to do it:
Open the Excel file that contains the data you want to use for your VLOOKUP.
Select the cell in your worksheet where you would like your VLOOKUP to appear.
Type in the LOOKUP function, starting with an equal sign followed by "VLOOKUP" and then an open parenthesis. The formula should look like this:
=VLOOKUP()
Enter the lookup value or the value you wish to search through to find a result, within quotes inside parentheses, followed by a comma.
=VLOOKUP("value",)
Choose the table array you wish to return data from and enter it directly into your formula, followed by a comma.
=VLOOKUP("value",A1:B10,)
Pick which column number you would like the VLOOKUP to return information from and enter it into your formula using a comma.
=VLOOKUP("value",A1:B10,4,)
Finally, close off your formula with either TRUE or FALSE specifying whether or not you are looking for an exact match of your lookup value within your table array (use FALSE if looking for an exact match) and then press Enter.
=VLOOKUP("value",A1:B10,4,FALSE)
The HLOOKUP function is very similar to VLOOKUP, in that it can help users find data in a table. The "H" in this case stands for "Horizontal." Here’s how you use it:
Open the Excel file that contains the data you want to use for your HLOOKUP.
Select the cell in your worksheet where you would like your HLOOKUP to appear.
Type in the LOOKUP function, starting with an equal sign followed by "HLOOKUP" and then an open parenthesis.
=HLOOKUP()
Enter the lookup value or the value you wish to search through to find a result, within quotes inside parentheses, followed by a comma.
=HLOOKUP("value",)
Choose the table array from which you wish to return data from and enter it directly into your formula, followed by a comma.
=HLOOKUP("value",A1:B10,)
Pick which row number you would like the Hlookup to return information from and enter it into your formula using a comma.
=HLOOKUP("value",A1:B10,4,)
Finally, close off your formula with either TRUE or FALSE specifying whether or not you are looking for an exact match of your lookup value within your table array, and then press Enter
=HLOOKUP("value",A1:B10,4,FALSE)
Similar to VLOOKUP and HLOOKUP, Excel users can also wield the mightier XLOOKUP function, which was actually designed as a replacement for those other two functions (note that it may not work in older versions of Excel). The main difference is that while VLOOKUP looks to return values in a column to the right, XLOOKUP can do so in any direction in the spreadsheet, returning values in both ranges and arrays.
Open the Excel file that contains the data you want to use for your XLOOKUP.
Select the cell in your worksheet where you would like your XLOOKUP to appear.
Type in the LOOKUP function, starting with an equal sign followed by "XLOOKUP" and then open parentheses.
=XLOOKUP()
Enter the lookup value or the value you wish to search through to find a result, within quotes inside parentheses, followed by a comma.
=XLOOKUP("value",)
Choose the table array you wish to return data from and enter it directly into your formula, followed by a comma.
=XLOOKUP("value",A1:B10,)
You can also add [if_not_found] to return a specific value if no match is found.
You can also use [match_mode] or [search_mode] to specify specific types of matches or search orders.
Hit Enter, and the function should retrieve the data.
Index and Match can be used to look up values in a dataset, similar to the VLOOKUP function. As such, it may be useful when trying to perform lookups in any column or retrieve data from any part of a table, as it can perform both horizontal and vertical lookups. It may also be helpful when looking for exact or approximate value matches.
Open the Excel file that contains the data you want to use for your Index/Match lookup.
Select the cell in your worksheet where you'd like your Index/Match lookup to appear and type in an equal sign followed by "INDEX" and then open parentheses.
=INDEX().
Enter the range of cells from which you wish to extract a value within quotations, followed by a comma.
(=INDEX(A1:A10,) )
Type in the row number you’re trying to retrieve a value from within parentheses.
=INDEX(A1:A10, 4))
After another comma, enter in MATCH followed by another open parenthesis and enter the value you are looking for, surrounded by quotation marks.
=MATCH("value",)
Finally, after another comma, type in 0 (exact match, will return #N/A if one isn’t found), 1 (exact match, will return the next larger item if a match isn’t found) or -1 (exact match, will return next smaller item if one isn’t found) specifying whether or not you are looking for an exact match of your lookup value within your table array and then press Enter.
=MATCH("value", A1:B10,0)).
VBA stands for Visual Basic for Applications, the programming language used in Excel and all Office programs. FP&A professionals use VBA to create macros that execute computer instructions and automatically run routine tasks. If you've worked with a button created in Excel that allows you to click and execute some actions, that's an example of a VBA macro.
For the lay Excel finance user who doesn't know how to program in VBA, you can still record a macro by going to View>Macro>Record Macro. At the next screen, click OK and then perform the actions you want the macro to do, which could include a range of tasks such as formatting, data analysis, importing or exporting data and interacting with other applications.
When finished, go to View>Macro>Stop Recording.
To run the macro, go to View>Macros>View Macros.
Then select the macro you've created and click Run.
Once the macro is recorded, go to the Visual Basic Editor (VBE) if you need to edit it. The quickest way is the shortcut ALT+F11 (for PC users).
In the VBE, to edit the macro you just created, click Tools>Macros. Then select your macro from the list and click Edit. The example shown is a simple copy-and-paste command, but the macros can be used for more complex tasks.
In Excel, PivotTables are interactive tools that can be used for a variety of purposes, including summarizing or analyzing data sets and revealing patterns or trends. They’re a popular and powerful tool.
For FP&A professionals working with large amounts of data, they can be one of the most used functions in your tool kit. They can help summarize or analyze data, group or filter information, allow users to drill-down to pull up detailed records or even create charts or graphs. The term “Pivot” in PivotTables refers to the ability to rearrange the fields in your spreadsheet to access different views or dimensions of the data itself. The main idea is to be able to “pivot” around and unveil trends or insights that may have otherwise remained unseen.
To create a PivotTable, select any cell or range in your dataset.
Then, use the Insert>PivotTable. Then choose where you want to place it, either in your existing sheet or a new one.
Drag and drop fields into specific areas of the PivotTable Field List to build the PivotTable, including the Rows, Columns, Values and Filters.
As you may have guessed, the SUMIF function can be used to add up the values within a data range that fit a specific single condition. It can be useful for summarizing data, particularly if you’re trying to do so based on a given criteria, such as numbers of a certain size, names that start with a certain letter, etc. It may be useful if you’re trying to add up sales revenue for a specific product in a certain region or track spending within a certain category.
You can think of the function as "sum, if" as it may be helpful to think of yourself asking Excel to add things up "if" they meet a condition.
Open the Excel file that contains the data you want to use.
Type in the SUMIF function, starting with an equal sign followed by "SUMIF" and then an open parentheses
=SUMIF().
Enter the range, criteria and if need be, the actual cells you want to add up
=SUMIF(range, criteria, [sum_range])
Hit Enter.
Similar to SUMIF, the COUNTIF function can be used to count the number of cells in a spreadsheet or data set that meet a certain condition. An example might be that you want to count the number of times a transaction of a specific amount—say, $100— appears in a list. The function itself can be pretty simple to use.
Open the Excel file that contains the data you want to use.
Type in the COUNTIF function, starting with an equal sign followed by "COUNTIF" and then an open parenthesis.
=COUNTIF()
Enter the range, and criteria—which may be text, a number for a specific threshold or “value” for an exact match.
=COUNTIF(range, criteria)
Hit Enter.
Another fairly simple but useful function, “LET” allows users to assign names to calculation results. That can help streamline your workflow and make formulas easier to read, especially as they get more complicated. In terms of use cases, the LET function can break down complicated calculations into simplified, readable steps or reuse intermediate calculations within the same formula.
Open the Excel file that contains the data you want to use.
Type in the LET function, starting with an equal sign followed by "LET" and then an open parenthesis.
=LET()
Enter the name of the variable, value of the variable and the calculation.
=LET(name, name_value, calculation)
Hit Enter.
If you want to increase the complexity a notch, you can use the LAMBDA function. It allows you to create custom and reusable functions and give them a name. LAMBDA can help you create functions for your functions. That means you’re not copying and pasting formulas, helping to avoid errors and adding your own new formulas to your library of functions.
An example of a LAMBDA could be a function that calculates the area of a rectangle, which would calculate length and width, and a user could name the function AreaOfRectangle, or something similar. Then, the user could use AreaOfRectangle like other Excel functions.
Open the Excel file that contains the data you want to use.
Type in the LAMBDA function, starting with an equal sign followed by "LAMBDA" and then an open parenthesis.
=LAMBDA(length, width, length * width)
Enter the parameters and calculation.
=LAMBDA(AreaOfRectangle)
Hit Enter.
Dynamic Arrays allow formulas or functions to return multiple values, and those values can then populate in neighboring cells, or "spill" into them. That spilling feature is one of the key features of dynamic arrays. In effect, this is a simplification of an otherwise complex data manipulation effort and can utilize the "spilling" feature in Excel to add more horsepower to certain functions.
There are certain functions within Excel that are designed specifically to take advantage of the dynamic array functionality, and those include FILTER, SORT, SORTBY, UNIQUE, SEQUENCE and RANDARRAY.
The basic advantages of utilizing Dynamic Arrays are that they can simplify your formulas and ideally, improve your analyses. Spilled ranges will also automatically update in response to any data changes.
Dynamic Arrays can be useful in several scenarios (you can learn more in our Vena Academy course), such as filtering, sorting, combining or validating data, or within a specific type of report such as a break-even analysis, in which they can help aggregate, summarize and validate values.
Power Query is an Excel functionality that lets you import data into the program and shape or transform it as you see fit. For instance, Power Query allows a user to import data from a web page or text file and to then filter or alter it. Users can also load the data into Excel and create charts or reports from it.
The main functionality is to import, transform, automate, prepare and then integrate your data from other sources, including other Microsoft programs. Using a Power Query is also fairly simple:
In Excel, navigate to Data>Get & Transform Data>Queries & Connections.
Select your data source and import it.
Use the Power Query Editor to remove or split columns, merge queries or change data types, as needed.
Choose Close & Load to lob the data back into Excel, into an existing worksheet or a new one.
We already covered PivotTables, but Power Pivot kicks things up a notch. The Power Pivot functionality in Excel can be used to create advanced data models and calculations, along with analysis. If you’re dealing with a lot of data from different sources, Power Pivot can help finance professionals create complex reports, data models and more.
Select File>Options>Add-Ins.
Choose COM Add-Ins from the prompt.
Select Microsoft Office Power Pivot, then select OK.
From there, go to Power Pivot>Manage, then Get External Data to import data from your chosen source.
Once the data finishes importing, click Diagram View to manipulate tables using drag-and-drop fields.
Use the Calculation Area within the Power Pivot to write formulas and more.
Once you’re back in Excel’s main field, select Insert>PivotTable or PivotChart, and select the Power Pivot data model as your source.
It’s intricate and may take some practice, but Power Pivot can be another powerful addition to your Excel skill set.
If you’re an FP&A professional or a part of a finance team, solid Excel skills for finance are a must have. That includes the ability to conduct financial analyses, modeling and reporting, along with finding ways to calculate, format and use tables and data visualization tools to tell a story around the data in a way that stakeholders can easily understand.
Since most finance teams are already well versed in using Excel, augmenting it with Vena, FP&A software that natively integrates with Excel, gives your team a leg up. Many organizations have tried to move away from Excel, only to find themselves coming back to it. Why force the finance folks to learn new applications when you can complement Excel with an integrated solution?
The result is more efficient teams and more fulfilled finance professionals. And when you use Vena, you overcome Excel's limitations by adding data integrity, up-to-the-minute updates and version controls. Excel is here to stay.
Polish your Excel skills for finance by earning your Excel Certification in Vena Academy.
Explore our library of free financial Excel templates for operational, strategic and financial planning!
Learn MoreEvan Webster is an experienced sales professional and storyteller with a passion for innovative technology. He currently serves as a Senior Area Sales Manager at Vena and previously worked as a Content Specialist. He continually strives to inspire finance professionals to become strategic business partners and is dedicated to helping them automate and streamline their planning processes so they can make better decisions with reliable, data-driven insights—enabling meaningful growth for organizations across the globe.
