Articles
Useful Excel Functions for Financial Planning and Analysis
- By AFP Staff
- Published: 10/8/2025
Spreadsheet tools are ubiquitous in financial planning and analysis (FP&A). Their ease of use for performing calculations and consolidating data makes them indispensable. In fact, the 2025 AFP FP&A Benchmarking Survey found that 96% of FP&A professionals use spreadsheets as a planning tool at least weekly, and 93% use them as a reporting tool at least weekly.
The most widely used spreadsheet tool in finance is Microsoft Excel, which is commonly listed as required knowledge on finance job descriptions. For those working in FP&A or looking to work in FP&A, below is a helpful starting point for Excel functions to know.
Quick Navigation
- Financial Functions
- Logical Functions
- Lookup and Reference Functions
- Date and Time Functions
- Math and Statistical Functions
- Text Functions
- Information Functions
AFP Digital Badge: Financial Forecasting in Excel
This badge helps finance professionals transform raw data into strategic insights using advanced Excel tools for dynamic analysis, forecasting and visual reporting.
Financial Functions
Financial functions perform common financial calculations and are often embedded in broader financial models. The functions listed below help FP&A professionals evaluate the time value of money and assess the financial viability of investments and projects. Commonly used in capital budgeting, loan modeling and scenario analysis, they support smarter decisions about where and when to allocate resources.
| Function | Purpose |
|---|---|
| FV, PV | Used to evaluate the value of cash flows over time, such as forecasting the value of an investment or calculating the discounted value of future project returns. Commonly applied in forecasting the value of cash flows, such as savings plans or retirement contributions. |
| NPV, XNPV | Useful when comparing investment opportunities or evaluating a project’s viability. XNPV is especially helpful when cash flows occur on irregular dates. |
| IRR, XIRR | Used to calculate the implied rate of return for capital investments or strategic initiatives. |
| PMT | Used to calculate the payment for a loan based on constant payments and a constant interest rate. |
Logical Functions
Logical functions are used for decision-making and conditional analysis. They help FP&A professionals introduce “if-then” logic into models, allowing calculations and outputs to be determined by specific logic or criteria. This makes models more adaptive, responsive and intelligent, which is particularly useful when building dynamic dashboards, error checks or decision-support tools.
| Function | Purpose |
|---|---|
| IF | Used to return one result if a condition is true and another if it’s false; commonly used to flag variances, apply business rules or trigger alerts in dashboards. For example, in budgeting models, an IF function might adjust expense levels based on revenue performance. IF(A1<0, “Illiquid”,””) says that if cell A1 is less than zero, the formula will display “Illiquid.” If the cell A1 is zero or positive, the formula is display nothing. Users can “nest” multiple IF conditions into a single cell, but this makes it harder to trace the logic. |
| AND | Checks if multiple conditions are all true; often used in combination with IF to create multi-criteria rules (e.g., if revenue > target and costs < budget). For example, in a dashboard, AND conditions can highlight when multiple risk indicators are active. |
| OR | Returns TRUE if at least one condition is met; useful when only one of several inputs has to meet a threshold for a result to be triggered. For example, in a dashboard, OR conditions can highlight when multiple risk indicators are active. |
| NOT | Reverses logic, useful when excluding items from a calculation or flipping a condition (e.g., "only if not approved"). For example, in project planning, NOT may be used to filter out items that don’t meet approval criteria or that fall outside a defined range. |
Lookup and Reference Functions
Lookup and reference functions retrieve data from structured sources like tables, databases and financial models. These functions help FP&A professionals pull the right data into the right place, making models more dynamic, automated and easier to update — especially when dealing with large datasets, cross-tabulations or multi-sheet reports.
| Function | Purpose |
|---|---|
| VLOOKUP, HLOOKUP | Used to search for a value in a column or row of a range, and return a result from another column or row; common in standardized templates and static tables. |
| XLOOKUP | A more flexible replacement for VLOOKUP and HLOOKUP, it can search both vertically and horizontally and return values from the left or right. XLOOKUP can be used to dynamically retrieve cost center data or historical metrics from consolidated datasets. For example, when reconciling departmental budgets with actuals before the quarterly review, XLOOKUP can be used to instantly match cost center codes across two massive spreadsheets, saving hours of manual work. |
| INDEX, MATCH | A powerful combination when used in conjunction with X, V or H LOOKUP that allows for dynamic (variable rather than static) search and retrieval. Ideal for models where lookup columns may shift, or where greater precision is needed in retrieving data from multi-criteria tables. |
| LET | Used to assign names to calculation results or values within a formula, improving readability. Can be helpful in financial models with repeated calculations, as assigning names to calculation results makes the formulas more understandable and maintainable. |
| CHOOSE | Returns a value from a list of options based on a given index number. Ideal for quickly switching between different assumptions, scenarios or outputs without rewriting formulas, making it useful for creating dynamic reports or displaying specific content based on predefined conditions. |
| OFFSET | Used to reference a range that is a specified number of rows or columns from a starting point; often used in conjunction with other functions to build flexible, moving ranges for models or dashboards. Can be used to reference rolling data ranges; for example, pulling the last 12 months of revenue for a trend analysis or creating a dynamic input range for a chart. |
Date and Time Functions
Date and time functions help FP&A professionals control time-sensitive outputs and align calculations with key calendar points. FP&A professionals use them to build accurate timelines, calculate time-based metrics and automate updates that depend on the current date — all of which are critical in forecasting, cash flow modeling and period-based reporting.
| Function | Purpose |
|---|---|
| TODAY, NOW | Automatically returns to the current date or current date and time; often used to create rolling reports or flag overdue items. |
| YEAR, MONTH, DAY | Extract specific components from a date value; useful for categorizing data by fiscal year or month, or grouping transactions for reporting. |
| EDATE | Calculates a date that is a specified number of months before or after a given date; common in financial schedules, such as projecting future payment or contract renewal dates. |
| EOMONTH | Returns to the last day of a month, offset by a given number of months; frequently used to align dates with reporting cycles or month-end close activities. |
Math and Statistical Functions
Math and statistical functions allow FP&A professionals to calculate totals, averages and variances; apply conditional logic to data sets; and conduct more advanced statistical modeling or array-based calculations. These tools power dashboards, forecasts, budget-to-actuals comparisons and error checks.
| Function | Purpose |
|---|---|
| SUM | Adds together values. |
| AVERAGE | Calculates the mean of a range; useful for analyzing trends and comparing performance across periods. |
| MAX | Returns the highest value in a range. |
| MIN | Returns the lowest value in a range. |
| ROUND | Rounds to the nearest value based on standard rounding rules; helpful in formatting results for presentation. |
| ABS | Returns the absolute value of a number, i.e., converts a negative number to its positive equivalent while leaving positive numbers unchanged; useful for variance analysis. |
| COUNT, COUNTIF, COUNTIFS | Used for conditional analysis, such as counting how many projects meet a condition or conditions. Can be used to flag anomalies; for example, using COUNTIF to count how many entries exceed budget thresholds. |
| SUMIF, SUMIFS | Used for summing values tied to specific criteria; helpful for creating dynamic summaries in dashboards and tracking budget categories across multiple dimensions like department and time period. |
| FREQUENCY | Calculates how often values occur within specific ranges; useful for analyzing data distributions. |
| LINEST | Performs linear regression analysis to calculate trendlines and forecast relationships between variables; useful when building trend forecasts, such as projecting sales based on historical data or modeling the impact of volume on variable costs. |
Text Functions
Text functions help FP&A professionals clean, format and parse text-based data, which is particularly important when working with exports from ERP systems, reports or inconsistent inputs. They’re often used in data prep and validation steps before analysis or modeling can begin.
| Function | Purpose |
|---|---|
| TEXT | Converts numeric values to text using specified formatting; can be used to standardize report labels or prepare data for presentation. |
| VALUE | Converts text that appears as a number into a numeric value; useful when importing or cleaning data from external sources. |
| LEN | Measures the number of characters in a cell; useful for validating IDs, codes or entries with character limits. For example, LEN can be used to flag account codes that are too short or descriptions that are missing key info. |
| LEFT, RIGHT, MID | Used to extract specific parts of a text string, such as pulling department codes from a longer string or slicing dates embedded in text. For example, LEFT or MID can be used to extract parts of account numbers, cost centers, or product SKUs to enable filtering or grouping. RIGHT can be helpful when analyzing suffixes or file types in exports, or when consistent ending characters carry meaning. |
| TEXTJOIN | Combines text from multiple cells or ranges into one cell, using a specified delimiter; useful for creating dynamic labels, report headers or account descriptions. |
Information Functions
Information functions help FP&A professionals validate and audit data by identifying the type of content in a cell, such as whether it contains a number, text or is blank, and converting it to the desired type of content for working with. These checks are especially useful when importing large or messy data sets where inconsistencies can impact calculations or results.
| Function | Purpose |
|---|---|
| ISNUMBER | Used to confirm whether a cell contains a numeric value; helpful when checking if imported data is properly recognized for calculations. |
| ISTEXT | Verifies if a cell contains text; useful in validating label fields or ensuring that identifiers aren't mistakenly treated as numbers. |
| ISBLANK | Identifies empty cells, which can flag missing values or gaps in data entry. |
| TYPE | Returns a numeric code that represents the type of data in a cell (e.g., number, text, logical); useful when troubleshooting complex formulas or unexpected behavior in a model. |
Learn More
Bonus Excel tips from Ian Schnoor, Executive Director of the Financial Modeling Institute, originally featured in the AFP FP&A Guide to What Is Financial Analysis:
- Use Excel’s Watch Window feature, located on the Formulas menu (keyboard: Alt+M), toward the right of the menu bar (keyboard: “W”). When the window opens, select “Add Watch” and highlight the numbers you want to observe, from any tab or linked file. Any change from the assumptions tab can be viewed here in real time, risk-free!
- Replace nested IF/THEN instructions with CHOOSE. The syntax is: CHOOSE(Index, Value1, [value2], …) where the index is the number of options and will correspond to the values 1, 2, …). With this, you can build a “switch” to toggle through various scenarios.
Download our financial analysis guide for more ways to improve your financial analysis skills.
Copyright © 2026 Association for Financial Professionals, Inc.
All rights reserved.
AFP FP&A Guide to
Navigating Business Turbulence
Organizations that embed risk‑ready processes react faster, spend less time scrambling for information and protect strategic priorities even under pressure. How do CFOs and finance teams shift from a growth‑focused partner to a protector of financial stability during turbulence? Read the Guide.
AFP Membership
The Hub for Treasury and Finance Professionals
As an AFP member, you'll gain access to exclusive training, certifications, research and networking to advance your career.
AFP Newsletters
Unbiased, practitioner-driven information for treasury and finance professionals
Subscribe to an AFP Newsletter to receive the latest news, events and trends in FP&A, Treasury or career planning.