The 8 essential Excel skills to survive every new job in FP&A
A big part of how FP&A adds value is by translating raw data into insights that tell the story of the business. In order to make sense of large amounts of data, we need to be experts in data management.
While visualization software such as Tableau, Microsoft Power BI, Chartio, or Looker become more and more popular, most companies still rely on spreadsheet software like Microsoft Excel and Google Sheets.
Having worked in companies that focus a lot of resources on creating self-serve dashboards using the aforementioned visualization tools, my team still frequently needs to download the raw data that power the dashboards to run in-depth ad-hoc analysis in Excel.
The specific Excel skills you need to master to be successful in FP&A are:
Pivot Tables
Look-up functions
Filtering
Conditional formatting
Consolidation functions
Macros
Text manipulation functions
Quality Control
Let’s take a look at use cases for each skill. I’m mostly referring to how the functions work in MS Excel, however, most can be directly applied to Google Sheets as well.
Do you like this post? Subscribe to my free weekly newsletter "FP&A Tuesday" for insights and actionable advice just like it:
1. Pivot tables
When presented with a large dataset that is organized in labeled columns, the first thing I do is create a pivot table to see what I’m dealing with. Pivot tables are an incredibly fast and easy-to-use way to aggregate vast amounts of information. It allows us to rearrange columns, rows, and values simply by dragging and dropping them in the required layout. This lets us quickly summarize information, for example showing revenue by customer or expenses by month.
Data can be summarized in different ways, be it as a sum, count or average. It’s also possible to create more complex formulas and integrate them in a pivot table analysis.
Additionally, an often-overlooked advantage is that pivot tables can significantly remove the file sizes we are dealing with - this can be critical when large datasets need to be shared that range in the hundreds of megabytes in file size. When a pivot table is created, Excel saves the data in the background in a much more efficient manner than the visible data set. Hence, the tab containing the data can be deleted and the Pivot Table still works, which significantly reduces file size.
2. Look-up functions
Surfacing valuable insights often requires combining multiple data sources. If they originate in different systems, it can be impossible or too time-consuming to combine them in one data set for pivot tables to work. These are situations where a robust knowledge of look-up functions is critical. The two most used formulas are Vlookup and Index/Match.
Most analysts are familiar with Vlookup, while they may have never heard of Index/Match. That can be to their detriment however since Index/Match has a set of advantages that makes it the better tool in most situations.
Some advantages of Index/Match over Vlookup are:
It’s less error-prone due to its dynamic nature
It provides a quick visual of what the formula is displaying
It shows an error message when something is off, while Vlookup displays a wrong data point that can easily be mistakenly taken as a correct value
It does not require manipulation of the source table, which makes future data refreshes more robust
3. Filtering
If the data set is relatively small and it’s necessary to analyze individual data points instead of just looking at aggregated values via pivot tables, knowing how to use Filters is essential. Use cases could be showing all invoices from a given vendor in a certain period or showing all purchase order variances that are greater than $1000, sorted by the amount of the variance.
The built-in function appears to be incredibly easy-to-use and straightforward at first glance, however, I learned the hard way that knowing the shortcomings of Filters is critical. For instance, when the data source is refreshed and additional columns were added, filters do not automatically include those columns.
As a result, when you use the sort function in the filtered table, the data points in the new columns do not change their positions as the filtered columns do, resulting in a complete loss of data integrity.
4. Conditional Formatting
We use conditional formatting to quickly surface data points that meet certain criteria or stand out from the rest. For instance, a scorecard may have dozens of KPIs and it would be impractical to review each one individually during review meetings.
With conditional formatting, we can see at a glance which metrics do not meet expectations and then only discuss those. When used the right way, this functionality can be used to automate processes and build self-service reporting - an important milestone on an FP&A team’s journey to become more effective at their core responsibilities.
5. Consolidation functions
While Pivot tables tend to get us more than halfway there on our quest to surface the insights in the sea of data, using the right consolidation formula is essential. For instance, rather than relying on the built-in average function of a pivot table, we may need to calculate a weighted average.
That’s the most typical use case for the “Sumproduct” formula. It’s a quick and easy way to create weighted averages, for instance when I need to calculate the average gross margin across several products.
Other common consolidation functions you should know are Sum-Ifs to navigate large data sets and Subtotal to sum only visible values in a filter. Additionally, it’s important to understand the difference between Average and Median formulas, to deal with data sets with varying degrees of outliers.
6. Macros
Maintaining Excel scorecards and other reports can at times be tedious and repetitive. You may need to combine data from different sources via numerous copy/paste actions and do some basic data cleaning and calculations on a monthly or even weekly basis. While this is a necessary part of many FP&A roles, there are ways to automate those.
While we could purchase external automation services such as Robotic Process Automation (RPA), we should also consider an alternative that is free and comes already built-in with Microsoft Excel. Macros allow us to record repeating actions such as moving data from one spreadsheet to another or formatting the data a certain way. It’s based on a programming language called VBA (Visual Basic for Applications).
Fortunately, we don’t need to know how to write code in VBA to use Macros. We can simply record the actions we aim to automate as if we are recording our screen in a Zoom call. Excel then automatically turns these actions into code that can be edited afterward. To run the Macro once we are done recording, all we need to do is press a button.
That said, I would encourage everyone working with Macros to spend some time learning how to read the code. That enables us not just to fix issues in case a Macro stops working (typically because the data source changed) it also gives us the ability to quickly tweak and repurpose existing Macro recordings without having to go through the time-consuming process of recording everything again.
7. Text Manipulation (left / right / len / find)
From time to time, valuable insights aren’t clearly presented to the FP&A team in the form of formatted tables with clean columns for each valuable data point. In some cases, we get data that is messy.
For instance, customer information may have address data combining city and ZIP code in one field. If our task is to map our customers according to in which ZIP code they are headquartered, we run into a problem. Of course, we could manually separate data in each field, however that quickly gets impractical as the amount of data increases.
Fortunately, Excel has different formulas that - when combined correctly - allow us to separate text from numbers in seconds. That’s why every FP&A analyst should know “Left”, “Right”, “Len”, and “Find” formulas and how to combine them.
8. Quality control or how to find your own mistakes in Excel
Everyone makes mistakes when working with Excel. That’s true across all degrees of experience. What we have to become great at to succeed in FP&A is not to avoid mistakes in the first place (since that’s impossible), but to catch our mistakes before they cause damage.
Let’s assume you are working on a large report, the first one on the new product launch, the one the execs have been asking about for weeks. Of course, you try not to make any mistakes, however, that may not be enough. Instead, trust that your built-in checks and Excel habits will catch all errors before it’s time to share the final report.
One of those good habits is to build in checksums wherever possible. Adding up individual line items and comparing the sum to another one from the original data source for instance. Or summing up columns in addition to rows and comparing the two results to each other. B
ut what we can do to check our work does not end here. Depending on the kind of report and how long you spend on the details it may also be helpful to put it aside for a day and look at it with fresh eyes, trying to think how the executives would read the report and what questions it may raise.
If you’d like to learn more about FP&A, I offer help in three ways:
1️⃣ Subscribe to my free newsletter “FP&A Tuesday” here.
2️⃣ Listen to 75 bite-sized mini lessons and get FP&A advice on the go.
3️⃣ Join my live online course FP&A Bootcamp to master FP&A in two weeks.
