How to save massive time creating formulas in data tables
Welcome to the 125 people who recently joined us!
Today’s post is a continuation of our excel data tables series.
Here’s what you’ll learn in this post:
Reminder on how to create data tables quickly
How to set up formulas automatically
A real example walkthrough with steps
Use cases to speed up your value-added analysis
In our combined 40 years of FP&A experience, we hated dragging formulas. It was a pain and a constant source of worry.
”Does the data tie…? Why doesn’t the data tie…? Why aren’t the formulas dragged to the last set of rows…?
No worries! Data tables with formulas are the answer.
The best part is they’re super easy to create and takes no effort at all.
Let’s dive right in!
🤔Why It Matters
4 words — save time, consistency, trust!
Not only will you save massive amounts of time setting up your analysis, but you’ll also create a consistent framework for them along with the trust that the data is right.
Read here to learn more about how to create data tables in excel and its context.
⚙The Process
Just like you would create formulas in normal spreadsheets, data tables are exactly the same.
Create your data table
Create your formula columns
Hit ENTER
Done
It’s that easy. Seriously.
Here is a real example in the next section.
💻Example
Let’s try some formulas out using excel data tables.
We’ll create a Sales Actuals vs. Budget example to test our newfound skills.
Step 1: Create a data table
Let’s insert a data table.
The shortcut for this = CTRL + T
Step 2: Create a new total column
Now let’s create a new total column to generate our sales totals.
Step 3: Add budget data
Now let’s apply some sample budget data.
In this scenario, I applied 75% of sales.
Feel free to add real data to make it more realistic.
I also included one cell that’s below Actuals. You’ll see why in the next section.
Step 4: Make it visual — apply conditional formats
Time to get fancy.
Let’s set up some conditional formatting for our newly created data set. For more info on how to achieve this, read here.
In the example below, we applied a RED format for any variances that are below $0.
Visually, this helps us immediately see those items that missed our budget targets.
Helpful, right? 🙂
Step 5: Create pivot tables and charts to make it analytical
Now comes the fun part we were all waiting for…the Analytics!
Now that you’ve got your data tables set up, formulas automatically dragging down to the last set of rows, you can trust you’ve got your data structured in the right place.
Go ahead and apply pivot tables.
Play with the data, and see what you can gather.
Add charts to make them more impactful and easier to digest by your business partners.
In the example below, we created a simple bar chart to illustrate 2021 sales vs. 2022 sales. You could also splice the data by sales rep, items, and region.
💼Use Cases
Now that we’ve got data tables and formulas in our toolkit, you can apply them to other use cases.
Here are some ideas below:
Sales
Closed deals analysis
Sales segmentation analysis
Marketing
Customer lifetime value analysis (refer here)
Cost of Customer Acquisition Analysis
Marketing spend analysis (refer here)
Corporate Finance / Business Unit Finance
Quarterly forecasting process
Budget Planning
Attrition analysis (refer here)
Operations Finance
Activity-based costing
Cost to Serve (refer here)
Cost benefits analysis
Data tables and the help of formulas will make you that much more valuable to your organization and managers.
Trust us, you’ll be saving tons of time going forward.
Conclusion
Start implementing formulas into your data tables today.
Empower yourself and your teams to be more agile, more flexible, and, above all, more value-added to your companies.
After all, that’s the strength and mission of FP&A. 💪
How do you feel now? Excited to implement data table formulas into your workflow?
Let us know by replying here or emailing us. If there are specific topics you’d like us to cover as well, let us know!
Now go have fun making an impact on your business and your career!
Cheers,
Drew & Yarty
🗃 Want the free excel file?
You can download the free excel file below:
If you have any issues or questions, reach out to us. We’re happy to help.