Excel formulas not working: 5 critical examples of checking & fixing workbooks

Wondering why your Excel formula isn't working? Here are 5 ways to check.

When it comes to working with data in Microsoft Excel, especially if it wasn’t your data to begin with, things can get tricky quickly. Between Excel formulas not working, human error, and simply trying to understand someone else’s thought process, there are a lot of ways checking and fixing an Excel workbook can go wrong. That’s where this post comes in. Written by Excel MVP, creator at the YouTube channel Excel on Fire, trainer, LinkedIn instructor and co-host of ExcelTV, Oz du Soleil, this article will dive into five critical examples of checking and fixing an Excel workbook, a situation managers—however reluctantly—might find themselves in. 


This article is just one of the tutorials on our blog. We often ask spreadsheet experts to share their top tips for managers, creating actionable tutorials that professionals can start putting into practice today.

Who’s this article for?

During the very first Excel course I taught, back in 2010, I met TJ. She was a warehouse manager who, though very skilled in many ways, wasn’t savvy with Microsoft Excel or data. For her particular role, she relied on other people’s spreadsheets, with data on scheduling, inventory, performance, products, and more. Since Excel wasn’t her strong suit, you can imagine why this was concerning. And she’s not alone.

More people like TJ have been blindsided by spreadsheets and data as the entire world becomes more data-driven. Nothing is more frustrating than Excel formulas not working. They start at a job—maybe driving a forklift or designing logos—and they move up to leading small teams. Their skills might be more geared towards resolving conflict, motivating teams, sales, or operating heavy machinery. But then there’s the promotion to a role that’s less about people and hands-on work, and more about data. ? 

As I wrote this article I’ve kept several  people in mind:

  • TJ, a manager who relies on and is responsible for complex spreadsheets that she just doesn’t understand. 
  • Nancy, a manager who needs to sign off on someone’s calculations without having the time (or need) to learn that person’s job or re-do said calculations. All she needs to be able to do is look at the calculations to see if there are any spreadsheet “bad habits.”
  • Avi, the CEO of a small company, who likes Excel just fine but has many other more important things to focus on, e.g., looking for clients and growing the business. Still, he needs to know he can trust other peoples’ Excel work.

This article’s promise

This article won’t take you into the guts of Excel—more than a how-to, it’s a what to look for. This article strives to get you thinking about the quality of your data and spreadsheets, as well as the skill of the people whose Excel files you rely on. If there’s an Excel formula not working, one of these tips might lead you in the right direction.

I’m going to share several features of Excel that can help you do quick, high-level investigations of someone else’s work. Can you trust the data? Can you trust the person who made the spreadsheet? Does that person practice good spreadsheet habits?

Those last two points are critical. Over the years as a consultant, I’ve had to break the news to several managers that the person in charge of their spreadsheets might be accurate, but their bad habits are a ticking time bomb. Now it’s time to find out: Is that the case for you, too? And yes, these might very well be tips to improve your own spreadsheets, too.

Let’s dive in!

1. Why Excel formulas are a must: The sin of hard-coded values

The image below shows what these four people need to be paid. There are no formulas. Someone typed these numbers directly into the cells. And there’s a problem in cell E5.

If your Excel formula isn't working, perhaps there isn't any formula at all, but rather a hard-coded value.
This data shows how much four people need to be paid; however, there is an issue in E5.

According to cell E5, we owe $59.25 to H241. After doing the math:

30 hours * $19.75 = $592.50

OUCH! ? Someone used a calculator to do the math, then made a very human mistake of a typographical error and put the decimal in the wrong place. Of course the Excel formula is not working because there was no formula to begin with!

Formulas should be making these calculations, especially when we’re talking about critical data like money. Sure, this is a simple example. But hard-coded values are often applied when calculations get more complex. 

Another problem arises with hard-coded values when the data needs to be updated. Let’s say, H327 actually worked 29 hours and their rate has gone up to $45/hour. Formulas will automatically update the Pay in Column E. By using a formula instead, we don’t have to rely on someone to remember to manually make the update and accurately do so.

2. How to keep your data dynamic and integrated in Excel: Tables

The number one Excel feature that people should use more is Tables. Tables are the glue that holds your data together.  If you receive a workbook that doesn’t have tables in it, be wary. Tables help ensure that data is integrated and that formulas cover complete columns. If your Excel formula doesn’t seem to be working, this might be why.

In the image below, the data in columns C and D are totaled in F3: 185. The total for columns K and L is in H3: 122. They should be the same. What’s going on?

Using tables in Excel is the ideal way to keep data updated and to ensure a formula is working.
Here you can see the potential difference between using a Table and not using one.

In F3 the formula is:

=SUM(Table1[[Store 1]:[Store 2]])

Because the dataset is in a formal Table, the SUM formula includes the entire columns Store 1 and Store 2 in the table named Table1.

In H3 the formula is: =SUM(K3:L10)

By selecting H3 and placing the cursor in the formula bar, we’re able to see what area is covered by the formula. It stops counting at Electric Grape.

If your Excel formula is not working, try putting the cursor in the formula bar of Excel to see that without a Table, data has been excluded
Place the cursor in the formula bar to see which area is covered by the formula.

How does this happen, you might wonder?

Well, sometimes a person adds data slowly, whenever it happens to come in. Done this way, it’s easy to forget to modify the formula. However, when adding new data to a table, the table absorbs the new data, applies existing formulas and updates calculations … automatically. Insist that tables be used by the people who are sending you Excel files. And when in doubt about an Excel formula not working, check and see if a table is being used or not.

A few examples of how to identify Tables in Microsoft Excel to keep in mind if an Excel formula isn't working
Here are three ways to identify a Table in Microsoft Excel.

3. A great way to double check your data in Excel: Crossfooting

It just arrived! The Excel workbook with the summary that we’ve been waiting for. It includes the breakdown of data by month, and by product line.

A summary page on Excel can be helpful, but only if the source data is correct.
A summary tab in Excel can be helpful, but how do you know you can trust the numbers you’re seeing?

But the thing with summaries like this is that they can raise several questions. Can we trust these numbers? How can we be sure the correct Excel formula is working?

Let’s say that in this scenario, we know that even on bad months that figure is at least $24,000. September is low, and $18,033.74 in October suggests either dreadful sales, a big client dropped us, or the calculations aren’t accurate.

This is definitely something that you’d want to check before meeting with the executives, right? Below is the source data that’s also in the workbook.

Source data that is used for the summary page in an Excel report. This is important to check if an Excel formula isn't working
The source data used in the summary tab.

In situations like this, where we have the source data and a summary, good spreadsheet development would include some kind of an alert; i.e., crossfooting, to indicate that the data is sound.

While crossfooting is technically a formal term in accounting, I’m borrowing it to describe calculating the data in at least two ways that should come out equal.

With this data, you can see the crossfooting section within the dashed line. 

One quick, easy way to double check that the data adds up and that Excel formulas are working is by crossfooting
An example of using a crossfooting method to double check data.

There are several components in this fairly sophisticated alert (Again, our objective isn’t to get into the how-to. The main point is to show why it’s important to have these features in good, reliable Excel files.):

  1. Cell J12 is a total of the Amounts in column E.
  2. J14:J16 totals each product line separately and has an overall sum.
  3. J20:J21 uses the data in column F to get monthly totals.
  4. Cell J11 uses an IF statement to check if those 3 totals are equal. If the totals don’t match, the IF statement shows “BEWARE!”
  5. Conditional Formatting is used to show bold text with a red fill if the numbers don’t match.
This alert is set up to have a quick glance at whether an Excel formula is working or not, and if you can trust the data
The BEWARE! alert in red lets us know that we can’t trust the data in the summary.

This alert is a quick-glance method of knowing whether or not you can trust the data in the summary. 

Question: What can we tell from this specific example? 

Answer: we CANNOT trust the summary in I3:L6

Send this data back to whomever compiled it, and have them troubleshoot and repair it until the alert looks like this:

An example of when an Excel formula is working, the data does add up in Excel, and we are able to trust the data in the summary tab of the workbook
When you see the OK alert at the top, you know you can trust the data.

All three values are equal, and the Check value says OK.

4. Right-click: Is there anything hiding? ?

Here’s a workbook that includes membership data. Members are in three groups on different sheets: Orange, Crimson, and Purple. We’ve got a nice summary on the Overview sheet.

This overview sheet in Excel has incorrect data, so how can we make sure the Excel formula is working?
An overview tab that shows membership data.

However, there’s a problem. In this scenario, we know that Dakota is the Chairperson of the Crimson group, not Ariana, as shown in cell D6.

However, in D6 there’s a complex formula that’s retrieving Ariana’s name, as noted below.

This complex formula in D6 lets us know that there is a hidden sheet named Details in this Excel workbook
This complex formula is used in D6.

Knowing exactly what this formula is doing isn’t so important. But at a higher level, notice this: Details!. The exclamation point is Excel’s way of telling us that there is a sheet named Details somewhere in this workbook. But, according to our view in the screenshot above, we only see sheets named: Overview, Crimson, Orange, and Purple.

The Details sheet must be hidden. ?

Here’s how to find it:

  1. Hover over any of the tabs
  2. Right-click
  3. Select Unhide
To find hidden sheets in Excel workbooks, right click over any of the tabs, and select unhide.
Right click while hovering over any of the tabs, and select Unhide.

Oh! There’s a Details sheet and an Indigo sheet, both hidden.

In fact, there were two hidden sheets in this Excel workbook: Indigo and Details.
A window pops up to show two hidden sheets.

Select Details and OK.

A hidden page in this Excel workbook has incorrect data, which is why the Excel formula is not working properly
After clicking OK, the hidden sheet Details shows up.

WOW! There’s a lot on this Details sheet, including a separate dues structure for people in the Orange group. And cell G3 is where Dakota’s name should replace Ariana.

You can use hidden sheets for many purposes, including:

  • protecting complex formulas 
  • sheets with notes or instructions
  • background data that needs to be accessible but not always visible

If you’re dealing with a workbook that you didn’t create, it’s a good habit to check for any hidden sheets. They may not always be relevant, but it’s a best practice to find out if any exist.

5. How do you show formulas in Excel? The accent trick

One simple but valuable feature in Excel allows you to see all of the formulas on a sheet, which is a handy trick if you’re troubleshooting why an Excel formula isn’t working. It’s a shortcut to show all formulas in Excel. This helps with troubleshooting when you know that something is wrong or you simply want to review all of the formulas (or check if the person who created the file uses good habits.)

Below, we received the summary of contractors that need to be paid.

To double check formulas not working and math in Microsoft Excel, simply press CTRL+' to reveal all formulas.
A summary of contractors that need to be paid.

Rather than do the math in our heads to determine if these numbers are accurate, let’s take a quick, high-level peek at the formulas.

Press CTRL+`

After pressing CTRL+' to reveal all formulas, here's an example of the formulas you'd see and be able to make sure they're functioning properly.
By pressing CTRL+`, you can reveal all of the formulas being used.

And just like that, all of the formulas are revealed. The Hours and Expenses calculations look okay, but the Pay column is strange.

A close-up look at the formulas once they've been revealed by pressing CTRL+' shows there is some unaccounted for amounts being added and subtracted.
Once the formulas have been revealed, you might have further questions.

Subtract €100 from Chana, and add €75 to Humberto? 

While there might be legitimate reasons for this, the point is that we don’t know what they are. This can cause confusion when it comes to understanding why Excel formulas may not be working correctly. We aren’t sure if these are corrections from previous payments or can be explained some other way. The bottom line, however, is that we need to press CTRL+` to return to the normal view, then contact the person who created the summary and:

  1. Find out what these adjustments are.
  2. If these are legitimate corrections, look for an area in the workbook to place such corrections, and apply them to the Pay column by using formulas.
  3. Train that person away from the sin of hard-coded values in a spreadsheet.

And if the adjustments aren’t valid, we’ve got a different problem entirely that likely needs to involve Human Resources.

The art of understanding why Excel formulas aren’t working

When it comes to understanding an Excel workbook and diving into data that someone else has analyzed and summarized, there are many tools and techniques to ensure that formulas are working. The ones I’ve highlighted here are quick ways to troubleshoot Excel data, ensure your reporting is as accurate as possible, and pinpoint some of the most common mistakes you (or your team) might be making. 

Tell me in the comments: What’s been your biggest data snafu in Excel? How’d you fix it?

The experts who have written or contributed to this article are independent from Beebole, and their contribution doesn't serve as endorsement for our company/tool or their past/present organizations, employers, or associates.
Oz du Soleil is an Excel MVP, creator at the YouTube channel Excel on Fire, trainer, LinkedIn instructor and co-host of ExcelTV.

Comments

Related posts

Project cost management: Adjustable forecasting and the Excel OFFSET function

Published: 2023/9/5 | Andrew Childress

Today we’re talking project cost management with adjustable forecasting and the Excel OFFSET function. Follow along below, or watch the tutorial on YouTube by clicking here. The first time you heard about a forecast, it probably had nothing to do with project cost management. The team of weather experts behind that forecast used a range […]

Read more

Automating emails from Excel: The easiest way for HR to send employee bonus emails

Published: 2021/4/20 | Szilvia Juhasz

“Annual compensation and bonus planning is easy, ” said no human resource professional, ever. That’s precisely why we’re going to have a look at automating emails from Excel. Say farewell to tedious manual sending and embrace this time-saving solution to effortlessly communicate bonus details to your employees. Indeed, compensation and bonus planning can be hard. […]

Read more

How to calculate project profitability using time tracking data: Everything you need to know [Excel tutorial]

Published: 2022/7/19 | Andrew Childress

If you bill clients based on the time you spend, here’s a pop quiz: which projects make the most money? Or even more importantly: which projects aren’t profitable? If you don’t know the answer to these questions, it’s time to start thinking about project profitability, a measurement of revenue billed versus time and cost expended. […]

Read more