Job descriptions for financial planning and analysis (FP&A) positions tend to list advanced Excel skills among the requirements. But what are the key skills required? Let’s look at three broad areas where FP&A might interact with Excel.

1. Data and data analysis

There is a running joke in BI communities that the most used feature in any business intelligence solution is the ‘Export to Excel’ button.” That is because Excel is flexible, and allows you to immediately review your data and test it by building models off the extract.

FP&A must be fluent in looking at data via pivot tables and data tables, and then sorting and sifting through data using filtering functions. A higher level is to add the Power Pivot add-in to your base installation of Excel so that you can, in the word of Microsoft, “mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.”

Microsoft has worked hard recently to expand Excel’s ability to obtain and manage data. It has always been easy to create or import a flat text file, such as a CSV or text delimited file, where rows of data are put into a single row separated by a comma, paragraph mark, semicolon or other notation. Excel has expanded its “Get External Data” to “Get & Transform” functions in the 2016 version, calling its steps as follows: Connect to a data source, starting with a new query to pull data from a file, database, online or cloud source; Transform, or simple editing functions you can perform on your data; Combine, or create tables from a single or multiple sources; and Share, in which you save or send, presumably using the MS Office tools. This is designed to imitate a data vendor’s ETL functionality: extract, transform and load.

Extra credit for building regressions to help determine covariance and key drivers.

2. Building models

The process of building models may start with the very data set just imported. There are sets of functions that FP&A should know to manage the data, whether it is for analysis or application into a model. Some include picking out data points, including HLOOKUP, VLOOKUP, OFFSET, CHOOSE and INDEX, MATCH, COLUMN, ROW. Prepare text and date data using CONCAT and logical (IF, AND, OR, LEFT, MID, RIGHT) formulas.

FP&A must be fluent in best practices of building models, including: separating inputs, calculations and outputs; never hard-coding a formula; and color-coding your variables and parameters to rapidly identify them. Then, we should add error-proof checks into the model, and use the formula auditing tools to navigate through it. To enhance collaboration, and since we often share models around the enterprise, we need to add security features to lock sheets or cells, and establish drop-down menus for users.

Advanced modeling also includes using GOAL SEEK from the ribbon bar’s What-If Analysis; extra credit for getting familiar with the ribbon bar’s Forecast Sheet to automatically make projections based on historical data.

3. Reporting

In many ways, reporting is equal parts art and science, but advanced skills here include the ability to build dashboards, knowing which graph tells the story best, and presenting your information in the clearest way possible—clear message, uncluttered graphs, consistent formatting of font style and size, decimals, alignment, etc. The reports and exports should be able to tell a story if they were read by your boss without you there to explain the page.

With 750 million installations of Excel worldwide, it is clear that we will be using Excel alongside other specialized tools for years to come; it is our responsibility to use it well.

Connect with Bryan Lapidus, FP&A, via email, LinkedIn or Twitter. For additional insights on FP&A, subscribe to the AFP monthly newsletter, FP&A in Focus.