Let’s face it, if you’re in FP&A or building financial models, Microsoft Excel is probably your home turf. You likely spend hours clicking, typing, and calculating within its familiar grid. You know your SUMs, your AVERAGEs, maybe even a VLOOKUP or two. But are you getting the most out of Excel for your modeling work?
Building robust and flexible financial models is demanding. It requires attention to detail, logical structuring, and the ability to test different possibilities quickly. If you’ve read our previous article on best practices and common pitfalls in building robust financial models, you’re already off to a strong start. But that’s just the beginning — mastering advanced techniques on excel can significantly boost your speed, accuracy, and the overall quality of your models.
This isn’t about theoretical concepts or buzzwords. This article is packed with practical Excel tips that you can start using right away to make your financial modeling life easier and your outputs better.
Ready to sharpen your Excel skills? Let’s jump in.
Before you even think about complex formulas, getting your model’s structure right is fundamental. A poorly organized model is a nightmare to audit, update, or hand over to someone else. It’s the bedrock upon which everything else is built. Here’s how to build a strong foundation:
- a) Tame Your Inputs with Named Ranges
- What it is: Instead of referring to a cell like $C$5 in your formulas, you give it a descriptive name, like Interest_Rate or Tax_Rate.
- Why it’s great for modeling:
- Readability: Formulas like =Revenue * (1 – COGS_Margin) are instantly understandable compared to =B10 * (1 – $C$5). Anyone (including your future self) can grasp the logic faster.
- Easy Updates: If an assumption changes (like the interest rate), you only need to update the value in the single named cell (Interest_Rate). Every formula using that name automatically reflects the change. No more hunting down every instance of $C$5.
- Reduced Errors: It’s much harder to accidentally link to the wrong cell when using clear names. Absolute referencing ($) becomes less critical within the named range context, though still important elsewhere.
- How to do it:
- Select the cell (or range of cells) you want to name.
- Go to the Name Box (the little box usually to the left of the formula bar that shows the cell address).
- Type your desired name (use underscores instead of spaces, e.g., Inflation_Rate) and press Enter.
Pro-Tip: Keep names consistent and intuitive, use meaningful names. You can manage all your named ranges via the Formulas tab -> Name Manager.
- b) Embrace Excel Tables for Dynamic Data
- What it is: Formatting a range of data as an official Excel Table (not just adding borders and colors). Use Insert tab -> Table or the shortcut Ctrl+T.
- Why it’s great for modeling:
- Structured Referencing: Formulas within tables can refer to columns by name (e.g., =[Sales] * [Price]) instead of cell addresses. This is incredibly readable.
- Automatic Expansion: When you add new rows or columns adjacent to the table, the table range automatically expands. Formulas and formatting often extend automatically too. This is amazing for time-series data where you add new months or years.
- Built-in Totals: Easily add a total row with various calculation options (Sum, Average, Count, etc.) via the Table Design tab.
- Improved Sorting/Filtering: Tables have built-in filter dropdowns in the header row.
- How to do it:
- Select the range of data you want to convert.
- Go to the Insert tab -> Table.
- Confirm the range and whether your data has headers. Click OK.
Practical Use: Perfect for historical data inputs, detailed revenue breakdowns, or cost schedules that might grow over time
Are you ready to make informed financial decisions that drive business growth?
- c) Avoid Merging Cells (Use Center Across Selection Instead)
- What it is: Merging cells combines multiple cells into one larger cell. While it might seem like a quick way to center a heading across several columns, it causes numerous problems down the line.
- Why it’s bad for modeling:
- Breaks Sorting/Filtering: You often can’t sort or filter ranges that contain merged cells correctly.
- Complicates Selections: Selecting rows or columns that intersect merged cells behaves unpredictably.
- Formula Issues: Copying formulas or referencing cells within a merged area can lead to errors or unexpected results. It fundamentally breaks the grid logic Excel relies on.
- The Better Alternative: Center Across Selection:
- Type your heading text into the leftmost cell of the range you want it centered over (e.g., type “Q1 Forecast” in cell B2 if you want it centered over B2:D2).
- Select the cell containing the text plus the adjacent blank cells you want to center it across (e.g., select B2:D2).
- Right-click and choose Format Cells (or press Ctrl + 1).
- Go to the Alignment tab.
- In the Horizontal dropdown menu, choose Center Across Selection.
- Click OK.
- Result: Your text looks visually centered across the columns, but each cell (B2, C2, D2) remains individual and selectable. This gives you the visual benefit without any of the functional drawbacks of merging.
- d) Make Navigation Simple
- Large, multi-sheet models can become confusing quickly. Ensuring users (including yourself!) can easily find their way around is crucial for usability and efficiency. According to the ICAEW Financial modeling code, easy navigation isn’t just about aesthetics—it helps users find the right data quickly, saving time and reducing errors.
- Key Practices:
- Table of Contents (TOC) Sheet: Dedicate the very first worksheet as a summary or navigation hub. List the key sections or sheets of your model here (e.g., Inputs, Calculations – P&L, Calculations – BS, Outputs – Summary, Sensitivity Analysis).
- Hyperlinks: Turn the list items on your TOC sheet into clickable hyperlinks that jump directly to the corresponding worksheet or even a specific named range within a sheet. (Right-click cell -> Link -> Place in This Document).
- Clear Worksheet Names: Ditch generic names like “Sheet1,” “Sheet2,” or “DataDump.” Use descriptive, concise names like “Inputs_Assumptions,” “Revenue_Model,” “IS_Output,” “BS_Output,” “CF_Output,” “Scenario_Summary.” Consistency helps.
Navigation Buttons/Links (Optional): For very complex models, consider adding “Back to TOC” or “Go to Summary” hyperlinks directly on individual sheets to make moving around even smoother.
Isn't it time to bring in expert financial guidance to enhance profitability and ensure stability for your business?
2. Formula Powerhouses: Beyond SUM and AVERAGE
Your core calculations live in formulas. Moving beyond the absolute basics unlocks significant flexibility and efficiency. But before diving into specific functions, let’s talk about a core principle: keep your formulas clear and concise.
The “Thumb Rule” and Formula Clarity
There’s a well-regarded best practice in financial modeling, often associated with the FAST Standard (Flexible, Appropriate, Structured, Transparent), sometimes called the “rule of thumb”: No formula should be longer than your thumb. While that’s a bit metaphorical, the point is serious. Overly long, nested, complex formulas are hard to read, hard to debug, and hard to explain.
Think about it: if you can’t explain what a formula does in under, say, 24 seconds (another FAST standard), it’s probably too complicated. Simplicity is key. It’s tempting to build intricate formulas that do many things at once, perhaps to feel like an Excel wizard. However, clear, understandable models are far more valuable in a professional setting. Often, breaking down a complex calculation into several intermediate steps in separate cells or helper columns makes the logic much easier to follow and audit. Remember, clarity trumps perceived cleverness every time.
With that principle in mind, let’s look at some functions that, when used appropriately, add significant capability:
- a) INDEX and MATCH: The Dynamic Duo
- What they do: Used together, INDEX and MATCH are a highly flexible way to look up information. MATCH finds the position of an item in a list, and INDEX returns the value at a specific position in a list or grid.
- Why they beat VLOOKUP/HLOOKUP:
- Lookup Direction: VLOOKUP only looks to the right; HLOOKUP only looks down. INDEX/MATCH can look up a value in any column/row and return a corresponding value from any other column/row, regardless of position (left, right, up, down).
- Less Fragile: VLOOKUP breaks if you insert or delete columns within the lookup table because it relies on a fixed column index number. INDEX/MATCH refers to ranges directly, making it more resilient to structural changes in your sheet.
- Performance (Often): On very large datasets, INDEX/MATCH can sometimes be slightly faster, though this difference is less pronounced in modern Excel.
- How they work together (Conceptual Example):
- =INDEX( [Column you want the result FROM], MATCH( [What you are looking FOR], [Column where you are looking FOR it], 0 ) )
- The 0 in MATCH ensures an exact match, which is what you usually want in financial modeling lookups.
- Practical Example: Find the sales amount for “Product B” in March.
- INDEX(Sales_Data_Column, MATCH(“Product D”, Product_List_Column, 0), MATCH(“March”, Month_Header_Row, 0))
- This example uses two MATCH functions within INDEX to find the row for “Product D” and the column for “March” within a 2D data range. For simpler, single-column lookups, you only need one MATCH.
- b) Aggregate with Conditions: SUMIFS, COUNTIFS, AVERAGEIFS
- What they do: These functions let you sum, count, or average values based on multiple criteria across different ranges. The older SUMIF, COUNTIF, AVERAGEIF only handle a single condition.
- Why they’re essential: Financial analysis constantly requires segmenting data. How much revenue came from the ‘North’ region and was for ‘Product A’? What’s the average deal size for deals closed after Q1 and handled by ‘Sales Rep X’? These functions answer those questions directly.
- How they work (SUMIFS example):
- =SUMIFS( [Range to SUM], [Criteria Range 1], [Criteria 1], [Criteria Range 2], [Criteria 2], … )
- You specify the range containing the numbers you want to add up first, then pairs of criteria ranges and the criteria they must meet.
- Example: Sum sales for the ‘West’ region for ‘Software’ products.
- =SUMIFS( Sales_Amount_Column, Region_Column, “West”, Product_Type_Column, “Software” )
- c) XLOOKUP: The Modern Lookup Solution (with a Caveat)
- What it is: Introduced in newer versions of Excel (Microsoft 365 and recent perpetual licenses), XLOOKUP is designed as a modern, flexible replacement for VLOOKUP, HLOOKUP, and even many common uses of INDEX/MATCH. It allows you to look for an item in one range and return a corresponding item from another range, regardless of their relative positions.
- Key Advantages:
- Simpler Syntax: Generally easier to write and understand than INDEX/MATCH. XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
- Defaults to Exact Match: Unlike VLOOKUP, its default behavior is an exact match, which is usually what’s needed in modeling, reducing potential errors.
- Built-in ‘If Not Found’: Includes an optional argument to specify what to return if the lookup value isn’t found, eliminating the need to wrap it in IFERROR for simple #N/A handling.
- Search Flexibility: Can easily search from bottom-to-top (useful for finding the last occurrence) via the search_mode argument.
- Can Return Arrays: Can return entire rows or columns, not just single values, fitting well with dynamic array capabilities.
- Growing Popularity: Due to its ease of use and flexibility, XLOOKUP is rapidly gaining popularity among Excel users who have access to it. It streamlines many common lookup tasks.
- The Compatibility Warning: Here’s the important catch: XLOOKUP, along with other dynamic array functions (like FILTER, SORT, UNIQUE, etc.), is not supported in older versions of Excel (Excel 2019 and earlier). If you build a model using XLOOKUP and share it with someone using an older Excel version, the formulas will result in #NAME? errors, effectively breaking the model for that user. Therefore, know your audience. If your model needs to be compatible with older Excel versions, you should stick with the tried-and-tested INDEX/MATCH combination for lookups. If everyone using the model is on a current version of Microsoft 365, then XLOOKUP can be a great time-saver.
- d) Graceful Error Handling with IFERROR
- What it does: Checks if a formula results in an error (like #DIV/0!, #N/A, #VALUE!). If it does, IFERROR returns a value you specify; otherwise, it returns the normal result of the formula.
- Why it’s important: Errors can cascade through a model, making results unreliable or sheets look messy. #DIV/0! errors are common when calculating ratios before denominators are populated. #N/A often appears from lookups where the item isn’t found. IFERROR provides a clean way to handle these.
- How it works:
- =IFERROR( [Your original formula], [Value to show if error] )
- Example: Calculating Gross Margin Percentage (= Profit / Revenue). If Revenue is zero or blank, this causes a #DIV/0! error.
- =IFERROR( Profit_Cell / Revenue_Cell, 0 )
- This formula will show 0 instead of the error if Revenue_Cell is zero or blank. You might choose to return “” (blank) or even text like “N/A” depending on context. Be mindful: Don’t use IFERROR to hide genuine problems; use it to manage expected or temporary error conditions gracefully.
Want to optimize your financial models? Contact us for a free consultation.
How can a Fractional CFO help you uncover financial opportunities and manage risks effectively
3. What-If Analysis: Exploring Possibilities
Financial modeling isn’t just about calculating a single outcome; it’s about understanding possibilities and sensitivities. Excel has built-in tools perfect for this.
- a) Goal Seek for Finding Breakeven Points
- What it does: Works backward. You tell Excel what result you want from a specific formula, and which input cell it can change to achieve that result.
- Why it’s useful: Finding breakeven points (e.g., “How many units do I need to sell to achieve zero profit?”). Determining required inputs (e.g., “What interest rate do I need on this loan to keep the monthly payment below $500?”).
- How to access it: Data tab -> What-If Analysis -> Goal Seek.
- Set cell: Select the cell containing the formula whose result you want to define (e.g., the Profit cell).
- To value: Enter the target value you want that formula to reach (e.g., 0 for breakeven).
- By changing cell: Select the single input cell that Excel is allowed to adjust to reach the target value (e.g., the cell containing Units Sold).
- Click OK. Excel will iterate and try to find a solution.
- b) Data Tables for Sensitivity Analysis
- What they do: Show how changes in one or two input variables affect key output formulas across a range of possibilities. Creates a table summarizing all the results.
- Why they’re powerful: Quickly see the impact of varying, say, price and volume on Net Income. Visualize the sensitivity of your model’s outputs to changes in key drivers. Much faster than manually changing inputs and noting results.
- Types:
- One-Variable Data Table: Shows how changing one input cell impacts multiple output formulas.
- Two-Variable Data Table: Shows how changing two input cells simultaneously impacts one output formula.
- How to set up (Two-Variable Example):
- Set up your model so key inputs (e.g., Unit_Price, Units_Sold) drive a key output (e.g., Net_Income).
- In an empty area of your sheet, list possible values for one input down a column (e.g., different Unit_Prices).
- List possible values for the second input across the top row of that same area (e.g., different Units_Sold).
- In the top-left corner cell where the row and column of inputs meet, enter a formula linking directly to your key output cell (e.g., =Net_Income_Cell).
- Select the entire rectangular range, including the input values and the corner formula cell.
- Go to Data tab -> What-If Analysis -> Data Table.
- Row input cell: Select the original input cell in your model that corresponds to the values listed across the top row of your table (e.g., the Units_Sold cell).
- Column input cell: Select the original input cell in your model that corresponds to the values listed down the left column of your table (e.g., the Unit_Price cell).
- Click OK. Excel fills the table with the calculated Net_Income for each combination of price and units.
4. Telling the Story: Visualization and Clarity
A model full of numbers is useless if you can’t quickly understand or present the key takeaways. Good visualization and clear presentation turn data into insights.
- a) Conditional Formatting for Instant Insights
- What it is: Automatically applies formatting (colors, icons, data bars) to cells based on their values or the results of formulas.
- Why it’s great: Draws attention to important numbers instantly. Highlight negative variances, show trends within tables, identify outliers, or visually represent progress towards a goal without needing separate charts.
- Useful Types:
- Color Scales: Apply a gradient of colors based on value (e.g., green for high profit, red for low).
- Data Bars: Put small bar charts directly inside cells, proportional to the cell’s value relative to others selected. Great for comparing numbers in a list.
- Icon Sets: Add traffic lights, arrows, or other indicators based on rules (e.g., green up arrow for positive growth, red down arrow for negative).
- Highlight Cell Rules: Easily format cells that are greater than, less than, equal to a certain value, or contain specific text. Excellent for variance analysis (highlighting unfavorable variances in red).
- How to access it: Home tab -> Conditional Formatting. Explore the different options and apply them to your key output ranges or variance columns.
- b) Sparklines for Inline Trends
- What they are: Tiny charts (line, column, or win/loss) that sit inside a single cell, summarizing a row or column of data.
- Why they’re useful: Provide a quick visual trend summary right next to the data itself, without taking up much space. Perfect for showing monthly sales trends next to the total annual sales figure, or visualizing the pattern of costs over time for different departments listed in rows.
- How to add them:
- Select the cell where you want the sparkline to appear.
- Go to the Insert tab -> Sparklines group (Line, Column, or Win/Loss).
- In the dialog box, select the range of data the sparkline should represent (e.g., the 12 monthly sales figures for that row).
- Click OK. You can then customize the sparkline’s appearance using the Sparkline tab that appears when the cell is selected.
- c) Charting Best Practices
- Keep it Clean: Avoid 3D effects, unnecessary backgrounds, or distracting gridlines. Focus on clear data presentation. Let the data speak for itself.
- Label Everything: Ensure axes are clearly labeled with units. Use informative chart titles that explain what the viewer is seeing. Add data labels where helpful, but don’t clutter the chart.
- Choose the Right Type:
- Line Chart: Best for showing trends over time.
- Column/Bar Chart: Good for comparing discrete categories or values at a single point in time. Use bars (horizontal) if category labels are long.
- Pie Chart: Use sparingly, only for showing parts of a whole when there are very few (less than 5-6) distinct, significant categories. Bar or column charts are often clearer for comparisons.
- Scatter Plot: Shows the relationship or correlation between two numerical variables.
- Waterfall Chart (Built-in in newer Excel): Excellent for visualizing the buildup or breakdown of a value (e.g., starting profit, adding revenues, subtracting various costs to reach ending profit). Ideal for explaining variances.
- Dynamic Charts: Consider setting up charts that reference data ranges within Excel Tables or use Named Ranges (potentially combined with non-volatile functions like INDEX rather than OFFSET where possible) so they automatically update as new data is added to the model.
5. Working Smarter: Efficiency and Auditing
Building great models also means working efficiently and ensuring accuracy. Speed and reliability go hand-in-hand.
- a) Master Keyboard Shortcuts
- Clicking through menus slows you down significantly over time. Learning key shortcuts is one of the fastest ways to improve your modeling speed. Here are a few essentials beyond Ctrl+C/V/X/Z:
- F2: Edit the active cell (places cursor at the end of the formula).
- F4: Cycle through absolute, mixed, and relative (A1) cell references while editing a formula. Indispensable for copying formulas correctly!
- Ctrl + Arrow Keys: Jump to the edge of the current data region (like End + Arrow).
- Ctrl + Shift + Arrow Keys: Select to the edge of the current data region.
- Alt + =: AutoSum the adjacent range (guesses the range, check it!).
- Ctrl + PageUp/PageDown: Switch between worksheets quickly.
- Ctrl + 1: Open the Format Cells dialog box (for alignment, number formatting, etc.).
- Ctrl + T: Create an Excel Table from selected data.
- Alt + E, S, V: Paste Special – Values (Classic shortcut). Learn others like Alt+E,S,F (Formulas), Alt+E,S,T (Formats). While right-click menus offer these, keyboard shortcuts are often faster for repetitive tasks.
- Beyond the common Ctrl combinations, Excel offers another powerful set of keyboard shortcuts initiated by pressing the Alt key. These aren’t single key presses, but rather sequential key combinations that navigate the ribbon menu system without needing your mouse.
- Once you press Alt, you’ll see small letters appear over each tab (like ‘H’ for Home, ‘N’ for Insert, ‘P’ for Page Layout, ‘A’ for Data, etc.). Pressing the corresponding letter selects that tab. From there, further letters appear over the commands within that tab. The beauty of this system, especially in the English version of Excel, is its logical structure. The letters often directly relate to the command names.
- ALT + H + B + S: For instance, to apply an outside border to selected cells, you’d press Alt, then H (for Home), then B (for Borders), and finally S (for Outside Borders).
- ALT + A + T: Similarly, to toggle filters on a data range, you press Alt, then A (for Data), then T (for Filter/Toggle Filter).
- While it might seem like more keystrokes initially, learning these logical sequences for frequently used commands allows you to keep your hands on the keyboard and execute actions much faster than reaching for the mouse and clicking through menus.
- b) Use Formula Auditing Tools
- Mistakes happen, especially in complex models. Finding them quickly is key to maintaining trust in your numbers. Excel’s built-in auditing tools are your allies. Find them on the Formulas tab -> Formula Auditing group.
- Trace Precedents: Shows arrows pointing from all cells that feed into the currently selected cell’s formula. Helps you understand exactly where the inputs are coming from. Click repeatedly to trace further back.
- Trace Dependents: Shows arrows pointing to all cells whose formulas refer back to the currently selected cell. Helps you understand the downstream impact of changing this cell’s value.
- Evaluate Formula: Steps through a complex formula calculation part-by-part, showing the intermediate results generated at each step. Incredibly useful for debugging exactly why a formula isn’t giving the expected output.
- Error Checking: Helps systematically locate cells containing formula errors (#N/A, #DIV/0!, #REF!, etc.).
- c) Organize with Grouping
- Complex models can easily span hundreds or thousands of rows for detailed schedules (like depreciation, debt amortization, or detailed revenue buildup). Scrolling endlessly is inefficient and makes it hard to see the big picture. Use Excel’s grouping feature to collapse and expand sections of rows or columns.
- How to do it:
- Select the rows or columns you want to group together (e.g., the detailed expense lines that roll up into a ‘Total Operating Expenses’ subtotal line). Select the detail rows, not the subtotal row itself.
- Go to the Data tab -> Outline group -> Group.
- Small plus/minus signs will appear in the margin (usually to the left for rows, above for columns), allowing you to collapse or expand that section with a single click. You can create multiple levels of grouping.
- Why it’s helpful: Allows users to switch between a high-level summary view (seeing only totals and key lines) and a detailed drill-down view easily. Great for structuring income statements, balance sheets, cash flow statements, or supporting schedules with sub-totals.
- d) A Word on Macros and VBA
- What they are: Macros are sequences of actions recorded or programmed (using Visual Basic for Applications – VBA) to automate repetitive tasks in Excel.
- The Potential: They can save significant time on complex, multi-step processes that you perform frequently.
- The Caution: While powerful, macros and VBA introduce a layer of complexity and opacity to a model.
- Harder to Audit: The logic is hidden within the code, not visible in worksheet formulas. Someone auditing the model needs VBA skills to understand what’s happening.
- Potential for Errors: Bugs in VBA code can be hard to find and can lead to incorrect results silently.
- Maintainability: If the person who wrote the macro leaves, updating or fixing it can be challenging for others.
- Security: Macros can potentially carry security risks if obtained from untrusted sources.
- Recommendation: Use macros and VBA sparingly in financial models intended for wide use or auditing. If you do use them, prioritize:
- Necessity: Only automate tasks where the time saving is substantial and the process cannot be reasonably achieved with standard Excel features.
- Simplicity: Keep the code as straightforward as possible.
- Documentation: Crucially, document what the macro does, how it works, and any assumptions it makes. Add comments within the VBA code itself and potentially include a description on a dedicated documentation sheet within the model. Think of the next person who has to understand it!
If you’d like to up your game in excel and financial modeling, drop us a message and our experts will take care of you.
Wrapping Up: Practice Makes Perfect
We’ve covered a lot of ground, from structuring your data and worksheets effectively to building clear formulas, running scenarios, visualizing results, and working more efficiently. We also touched upon important principles like keeping formulas simple and making navigation easy.
Don’t feel you need to implement everything overnight. Pick one or two tips that seem most relevant to your current challenges and start practicing them. Maybe begin by religiously using “Center Across Selection” instead of merging cells, or by creating a simple Table of Contents for your next larger model. Perhaps focus on replacing a few key VLOOKUPs with INDEX/MATCH or applying conditional formatting to your variance analysis.
Mastering Excel for financial modeling is an ongoing process of learning and refinement. The key is consistent application of good practices. By incorporating these techniques into your workflow, you’ll find yourself building more reliable, flexible, understandable, and insightful models with greater confidence and speed. Happy modeling!
AG Capital provides fractional CFO services and Financial Planning and Analysis (FP&A) services to small and mid-size companies in the US, UK, EU and globally, including budgeting, profitability analysis, cost analysis, investment projections, and a cash flow planning. The company thrives in offering high-level financial expertise and leadership to businesses on a part-time or project basis.