For many in Financial Planning & Analysis (FP&A), Excel is like a second language. It’s familiar, dependable, and installed on virtually every computer in the corporate world. But here’s a thought: what if the way you’ve been using Excel is only scratching the surface of what it can do? What if there are tools built right into the program that could automate your most tedious tasks, reduce manual errors, and give you a much clearer picture of your company’s financial health?
This isn’t about fancy, expensive software or learning to code, or even vibe-code overnight. This is about using the tool you already have, just more effectively. We’re going to walk through some of Excel’s most effective data analysis features, moving from foundational best practices to advanced analytical techniques. The goal is to give you practical steps you can take today to make your work faster, your analysis deeper, and your life a little bit easier.
Before we get into any complex analysis, we need to address a fundamental habit. Many people work with data in a simple range of cells. This works, but it’s inefficient and prone to error. The single best thing you can do to improve your data workflow is to format your data as an Excel Table. It’s a simple action with significant benefits.
To turn a range of data into a Table, just click anywhere inside your data set and press Ctrl+T. A small dialog box will appear to confirm your data range and ask if your table has headers. Check the box, click OK, and you’re done.
So, what did that simple keystroke just do for you?
- Structured Referencing: Forget manually typing and selecting =SUM(C2:C5000). With a Table, your formulas become readable, like =SUM(Sales[Amount]). If you add new rows to your sales data, the formula automatically includes them. No more manually adjusting formula ranges every month and worrying you missed a row.
- Automatic Formatting: New rows and columns automatically adopt the table’s formatting. This keeps your reports looking clean and professional without any extra work.
- Built-in Filtering and Sorting: Headers in a Table automatically get filter dropdowns, making it simple to sort your data or view specific segments without needing to apply the filter function manually.
- Easy Totals: Go to the “Table Design” tab that appears when you click on your table, and check the “Total Row” box. A new row appears at the bottom. You can then choose to show a SUM, AVERAGE, COUNT, or other calculation for each column, right from a drop-down menu.
Starting with a proper Table is the bedrock of good analysis in Excel. It organizes your data, makes your formulas more resilient, and sets the stage for the more advanced tools we’re about to explore.
Power Query (Get & Transform Data)
Here is where we take a big step forward. One of the most time-consuming parts of any financial analysis is data preparation. You get data from the ERP, the CRM, an accounting system, maybe even a few stray CSV files. The formats are inconsistent, there are extra columns, blank rows, and dates are stored as text. You might spend hours cleaning and combining this data before you can even begin your analysis.
Power Query is designed to eliminate that manual work. It’s an ETL (Extract, Transform, Load) engine built directly into Excel. Let’s break down what that means in plain English:
- Extract: Connect to and pull data from a huge variety of sources—a file on your computer, a folder full of files, a SQL database, a website, and more.
- Transform: This is the magic. You can clean and reshape your data using a simple, user-friendly interface. You can remove columns, filter rows, split columns, change data types, and even unpivot data—a common task for FP&A professionals dealing with reports where months are listed as column headers.
- Load: Once your data is clean, you can load it into an Excel Table or directly into the Data Model (more on that later).
The most valuable part of Power Query is that it records every transformation step you make. When you get a new data file next month, you don’t repeat the cleaning process. You just point Power Query to the new file and click “Refresh.” All your cleaning and transformation steps are automatically reapplied in seconds.
A Practical Example: Consolidating Monthly Sales Files
Imagine you get a separate Excel file of sales data for each month. At the end of the quarter, you need to combine them into one master file for analysis. Here’s how you’d do it with Power Query instead of mind-numbing copy-pasting.
- Place all your monthly sales files into a single folder.
- In Excel, go to the Data tab.
Click Get Data > From File > From Folder.
- Browse to and select the folder containing your files, then click OK.
A preview window will appear showing the files in that folder. Click the “Combine & Transform Data” button at the bottom.
Excel will ask you to select a sample file to determine the structure. Choose one of your sales files and click OK.
The Power Query Editor window opens. Here, you’ll see all your data combined into one table. You can now perform any necessary cleaning. For example:
- Right-click a column header and select “Remove” to get rid of unnecessary columns.
- Click the icon in the column header (e.g., “ABC” or “123”) to change the data type to a proper Date, Currency, or Whole Number.
Once you’re happy with the data, click “Close & Load” in the top-left corner.
Your consolidated, clean data is now loaded into a new sheet in an Excel Table, ready for analysis. Next month, when you add the new sales file to the folder, you just right-click your table and hit “Refresh.” The new data appears instantly. This single workflow can save hours of tedious, error-prone work each month.
Mastering Pivot Tables
Once your data is clean and structured in a Table, PivotTables are the fastest way to summarize it and find meaningful patterns. Most finance professionals have used them, but many don’t venture beyond dragging fields into the Rows, Columns, and Values boxes. Let’s look at a few features that provide deeper financial insight.
1. Calculated Fields
Instead of adding new columns to your source data, you can create calculations directly within the PivotTable. Let’s say your data has Sales and COGS (Cost of Goods Sold), but not Gross Margin.
- Click anywhere inside your PivotTable.
- Go to the “PivotTable Analyze” tab.
- Click Fields, Items, & Sets > Calculated Field.
- Give it a name, like “Gross Margin.”
- In the formula box, type = Sales – COGS.
Click OK.
A new “Gross Margin” field now appears in your PivotTable, and you can analyze it just like any other data point. You could do the same to calculate Gross Margin Percentage with the formula = (Sales – COGS) / Sales.
Not familiar with these terms? No worries! Check out our article about demistifying financial jargons and terms.
2. Show Values As
This is an incredibly useful feature for variance and performance analysis. By default, the Values area shows a Sum or Count. But you can change this to show contextual calculations.
- Right-click a value in your PivotTable.
- Go to Show Values As.
- You’ll see a list of options. Here are a few valuable ones for FP&A:
- % of Grand Total: Shows what percentage each line item contributes to the overall total. Perfect for seeing which product line makes up the bulk of your revenue.
- % of Parent Row Total: Excellent for drilling down. You could see what percentage each individual product contributes to its product category, not the grand total.
- Difference From: This is your variance analysis tool. If you have months in your columns, you can show the dollar variance from the previous month. Just set the “Base field” to your date field and the “Base item” to “(previous)”.
- Running Total In: Instantly create a cumulative total for a field, which is great for tracking progress toward a quarterly or annual target.
3. Slicers and Timelines
Static reports are good, but interactive dashboards are better, especially when presenting to leadership. Slicers and Timelines are visual filters that make your PivotTables interactive.
- Click on your PivotTable.
- On the “PivotTable Analyze” tab, click “Insert Slicer”.
- Check the boxes for the fields you want to filter by, like Region, Product Category, or Sales Rep.
If you have a date field, click “Insert Timeline”. This creates a special, time-based filter that lets you easily select years, quarters, months, or even specific days.
You can now click on these Slicers and Timelines to filter your PivotTable instantly. This allows you and your audience to explore the data dynamically during a meeting, answering questions on the fly without having to build a new report for every scenario. If you’d like to know more about pivot tables, check out our previous article about it.
Planning for the Future: What-If Analysis
So far, we’ve focused on analyzing historical data. But a huge part of FP&A is looking forward—budgeting, forecasting, and modeling different business outcomes. Excel’s What-If Analysis tools are built for exactly this purpose. You can find them on the Data tab under the “What-If Analysis” dropdown.
1. Goal Seek
Goal Seek is a simple but effective tool that works backward. You know the result you want, and you need to figure out the single input required to get there.
The Scenario: You operate under a straightforward profit model. Initially, you sell 10 units at a fixed price of $50 each, with your cost of goods sold (COGS) accounting for 30% of sales. Unit sales increase by 5% each month. Currently, your net profit stands at $3,342. You want to know how many units must be sold to reach a net profit of $5,000.
Note that in this model, the input Initial Quantity Sold on D4 is referenced in D13, and current net profit in D7 is simply the sum of the Net Profit on row 16. These references are very important points for the next steps to work properly.
- How to Use It:
- Click What-If Analysis > Goal Seek.
- Set cell: Select the cell that contains “Current Net Profit”.
- To value: Type 5000.
- By changing cell: Select the cell that contains “Initial Quantity Sold” input.
- Click OK.
Excel will run through the possibilities and find the exact number of units you need to sell to hit your profit target. It’s perfect for quick, single-variable questions.
If you would like to build more advanced financial models, give us a quick call and our world class experts will train you.
2. Data Tables
Data Tables are ideal when you want to see how changes in one or two variables affect a key outcome across a whole range of possibilities.
- One-Variable Data Table: You want to see how different interest rates affect your company’s Net Income.
- Set up a small table. In one column, list a range of possible interest rates (e.g., 3.0%, 3.5%, 4.0%).
- In the cell one row above and one column to the right of your list, link to your final Net Income formula (e.g., =B20).
- Select the entire table (your list of interest rates and the linked formula cell).
- Go to What-If Analysis > Data Table.
- In the “Column input cell” box, select the original interest rate cell from your financial model.
- Click OK. Excel will fill the table, showing you the Net Income for each interest rate in your list.
- Two-Variable Data Table: You want to see how Net Income changes based on both Unit Price and Units Sold.
- Set this up similarly, but list a range of Unit Sold down a column and a range of Units Price across a row.
- In the corner cell where the row and column meet, link to your Net Income formula.
- Select the entire table.
- Go to What-If Analysis > Data Table.
- For “Row input cell,” select the input Units Price cell in your model.
- For “Column input cell,” select the original Unit Sold cell in your model.
- Click OK. Excel will populate a grid showing every possible combination of price and volume, giving you a powerful sensitivity matrix.
The Next Level: The Data Model and Power Pivot
For years, when you had to combine data from different tables (like sales data and customer data), the go-to solution was a VLOOKUP or INDEX/MATCH formula. These work, but they can make your files slow, large, and fragile. If you need to analyze multiple large datasets together, there is a much better way: Excel’s Data Model.
Think of the Data Model as a small database living inside your Excel file. Instead of mashing all your data into one giant, flat table with VLOOKUPs, you can load separate tables into the model and then define relationships between them.
Power Pivot is the interface you use to manage the Data Model and create advanced calculations. It introduces a formula language called DAX (Data Analysis Expressions). DAX looks a bit like regular Excel formulas but is far more capable, especially for creating calculations that respond to the context of a PivotTable.
Why is this a better approach?
- Performance: Files are smaller and faster. The Data Model uses a modern engine that can handle millions of rows of data far more efficiently than a standard worksheet.
- Simplicity: No more complex lookup formulas. You create a relationship between two tables once (e.g., connect the ProductID in your Sales table to the ProductID in your Products table), and it just works.
- Deeper Analysis: DAX allows for calculations that are difficult or impossible with regular formulas. Time intelligence functions, for example, make it easy to calculate Year-over-Year Growth, Year-to-Date totals, or Moving Averages.
A Simple Use Case: Analyzing Sales with a Calendar Table
- Use Power Query to load your Sales data and a separate Calendar table (a simple table with every date and columns for Year, Quarter, Month, etc.).
- When loading, choose “Add this data to the Data Model”.
- Go to the Power Pivot tab and click “Manage”. This opens the Power Pivot window.
- Go to the “Diagram View.” You’ll see your two tables. Drag the Date field from your Sales table to the Date field in your Calendar table to create a relationship.
- Close the Power Pivot window.
- Insert a new PivotTable, but this time, in the dialog box, select “Use this workbook’s Data Model”.
- Now, in your PivotTable Fields list, you’ll see both tables. You can drag Year from the Calendar table to Rows and Sales Amount from the Sales table to Values. The PivotTable will correctly summarize sales by year, even though the data comes from two separate tables.
This is just the beginning. With DAX, you could write a measure like Sales YTD := TOTALYTD(SUM(Sales[Sales Amount]), ‘Calendar'[Date]) to get a year-to-date calculation that automatically resets every year.
From Data Entry to Data Storytelling
Excel is a powerful environment for turning raw data into a clear financial story. By moving beyond basic formulas and embracing the tools built into the program, you can fundamentally change your workflow.
Start small. This week, try converting one of your regular data ranges into an Excel Table. Next, take a repetitive data cleaning task and see if you can automate it with Power Query. Experiment with the “Show Values As” feature in a PivotTable to create a quick variance analysis.
Each of these steps will save you time, reduce the risk of manual error, and free you up to focus on what really matters: interpreting the numbers, understanding the trends, and providing the insights that guide better business decisions. The tools are already on your computer, waiting to be used.
AG Capital CFO Services 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.