How to save massive time creating formulas in data tables

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.

1. Create your data table
2. Create your formula columns
3. Hit ENTER
4. 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:

1. Sales
   1. Closed deals analysis
   2. Sales segmentation analysis
2. Marketing
   1. Customer lifetime value analysis
   2. Cost of Customer Acquisition Analysis
   3. Marketing spend analysis
3. Corporate Finance / Business Unit Finance
   1. Quarterly forecasting process
   2. Budget Planning
   3. Attrition analysis
4. Operations Finance
   1. Activity-based costing
   2. Cost to Serve
   3. 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

PS: This post is 100% human-made