{"id":7605,"date":"2021-03-16T14:00:00","date_gmt":"2021-03-16T13:00:00","guid":{"rendered":"https:\/\/beebole.com\/blog\/?p=7605"},"modified":"2025-01-22T12:06:56","modified_gmt":"2025-01-22T11:06:56","slug":"excel-formulas-not-working-fixing-workbooks","status":"publish","type":"post","link":"https:\/\/beebole.com\/blog\/excel-formulas-not-working-fixing-workbooks","title":{"rendered":"Excel formulas not working: 5 critical examples of checking &#038; fixing workbooks"},"content":{"rendered":"\n<p>When it comes to <a href=\"https:\/\/beebole.com\/blog\/excel-power-query-for-business-intelligence\/\">working with data in Microsoft Excel<\/a>, especially if it wasn\u2019t your data to begin with, things can get tricky quickly. Between <strong>Excel formulas not working<\/strong>, human error, and simply trying to understand someone else\u2019s thought process, there are a lot of ways checking and fixing an Excel workbook can go wrong. That\u2019s 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, <a href=\"https:\/\/ozdusoleil.com\/#about-me\" target=\"_blank\" rel=\"noopener\">Oz du Soleil<\/a>, <strong>this article will dive into five critical examples of checking and fixing an Excel workbook<\/strong>, a situation managers\u2014however reluctantly\u2014might find themselves in.&nbsp;<\/p>\n\n\n\n<p class=\"has-text-align-center\"><em>&#8211;<br>This article is just one of the <a href=\"https:\/\/beebole.com\/blog\/category\/learn-tutorials-howtos\/\">tutorials<\/a> 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.<br>&#8211;<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-who-s-this-article-for\">Who\u2019s this article for?<\/h2>\n\n\n\n<p>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\u2019t savvy with Microsoft Excel or data. For her particular role, she relied on other people\u2019s spreadsheets, with data on scheduling, inventory, performance, products, and more. Since Excel wasn\u2019t her strong suit, you can imagine why this was concerning. And she\u2019s not alone. <\/p>\n\n\n\n<p>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\u2014maybe driving a forklift or designing logos\u2014and 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\u2019s the promotion to a role that\u2019s less about people and hands-on work, and more about data. ?&nbsp;<\/p>\n\n\n\n<p>As I wrote this article I\u2019ve kept several&nbsp; people in mind:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>TJ, a manager who relies on and is responsible for complex spreadsheets that she just doesn\u2019t understand.&nbsp;<\/li>\n\n\n\n<li>Nancy, a manager who needs to sign off on someone\u2019s calculations without having the time (or need) to learn that person\u2019s 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 \u201cbad habits.\u201d<\/li>\n\n\n\n<li>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\u2019 Excel work.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-this-article-s-promise\">This article\u2019s promise<\/h2>\n\n\n\n<p>This article won\u2019t take you into the guts of Excel\u2014more than a <em>how-to<\/em>, it\u2019s a <em>what to look for<\/em>. This article strives to get you thinking about <strong>the quality of your data and spreadsheets<\/strong>, as well as the skill of the people whose Excel files you rely on. If there\u2019s an Excel formula not working, one of these tips might lead you in the right direction.<\/p>\n\n\n\n<p>I\u2019m going to share several features of Excel that can help you do quick, high-level investigations of someone else\u2019s work.<em> Can you trust the data? Can you trust the person who made the spreadsheet? Does that person practice good spreadsheet habits?<\/em><\/p>\n\n\n\n<p>Those last two points are critical. Over the years as a consultant, I\u2019ve 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\u2019s 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.<\/p>\n\n\n\n<p>Let\u2019s dive in!<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"h-1-why-excel-formulas-are-a-must-the-sin-of-hard-coded-values\">1. Why Excel formulas are a must: The sin of hard-coded values<\/h3>\n\n\n\n<p>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\u2019s a problem in cell E5.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"475\" height=\"230\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/Excel-formulas-not-working.png\" alt=\"If your Excel formula isn&#039;t working, perhaps there isn&#039;t any formula at all, but rather a hard-coded value.\" class=\"wp-image-7621\" title=\"\"><figcaption class=\"wp-element-caption\">This data shows how much four people need to be paid; however, there is an issue in E5.<\/figcaption><\/figure>\n\n\n\n<p>According to cell E5, we owe $59.25 to H241. After doing the math:<\/p>\n\n\n\n<p>30 hours * $19.75 = $592.50<\/p>\n\n\n\n<p>OUCH! ? Someone used a calculator to do the math, then made a very human mistake of a typographical error <strong>and put the decimal in the wrong place<\/strong>. Of course the Excel formula is not working because there was no formula to begin with!<\/p>\n\n\n\n<p>Formulas should be making these calculations, especially when we\u2019re talking about critical data like money. Sure, this is a simple example. But hard-coded values are often applied when calculations get more complex.&nbsp;<\/p>\n\n\n\n<p>Another problem arises with hard-coded values when the data needs to be updated. Let\u2019s 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\u2019t have to rely on someone to remember to manually make the update and accurately do so.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">2. How to keep your data dynamic and integrated in Excel: Tables<\/h3>\n\n\n\n<p><strong>The number one Excel feature that people should use more is Tables<\/strong>. Tables are the glue that holds your data together.&nbsp; If you receive a workbook that doesn\u2019t have tables in it, be wary. Tables help ensure that data is integrated and that formulas cover complete columns. If your Excel formula doesn&#8217;t seem to be working, this might be why.<\/p>\n\n\n\n<p>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\u2019s going on?<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"278\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/troubleshooting-formulas-excel-700x278.jpg\" alt=\"Using tables in Excel is the ideal way to keep data updated and to ensure a formula is working.\" class=\"wp-image-7629\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/troubleshooting-formulas-excel-700x278.jpg 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/troubleshooting-formulas-excel-768x305.jpg 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/troubleshooting-formulas-excel-1536x609.jpg 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/troubleshooting-formulas-excel.jpg 1780w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Here you can see the potential difference between using a Table and not using one.<\/figcaption><\/figure>\n\n\n\n<p>In F3 the formula is:<\/p>\n\n\n\n<p>=SUM(Table1[[Store 1]:[Store 2]])<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>In H3 the formula is: =SUM(K3:L10)<\/p>\n\n\n\n<p>By selecting H3 and placing the cursor in the formula bar, we\u2019re able to see what area is covered by the formula. It stops counting at Electric Grape.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"483\" height=\"413\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/why-are-excel-formulas-not-updating.png\" alt=\"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\" class=\"wp-image-7630\" title=\"\"><figcaption class=\"wp-element-caption\">Place the cursor in the formula bar to see which area is covered by the formula.<\/figcaption><\/figure>\n\n\n\n<p>How does this happen, you might wonder?<\/p>\n\n\n\n<p>Well, sometimes a person adds data slowly, whenever it happens to come in. Done this way, it\u2019s 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 \u2026 <em>automatically<\/em>. <strong>Insist that tables be used by the people who are sending you Excel files. <\/strong>And when in doubt about an Excel formula not working, check and see if a table is being used or not.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"422\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-excel-workbook-700x422.png\" alt=\"A few examples of how to identify Tables in Microsoft Excel to keep in mind if an Excel formula isn&#039;t working\" class=\"wp-image-7625\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-excel-workbook-700x422.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-excel-workbook-768x463.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-excel-workbook-1536x927.png 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-excel-workbook.png 1724w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">Here are three ways to identify a Table in Microsoft Excel.<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">3. A great way to double check your data in Excel: Crossfooting<\/h3>\n\n\n\n<p>It just arrived! The Excel workbook with the summary that we\u2019ve been waiting for. It includes the breakdown of data by month, and by product line.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"455\" height=\"189\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/excel-formula-troubleshooting.png\" alt=\"A summary page on Excel can be helpful, but only if the source data is correct. \" class=\"wp-image-7620\" title=\"\"><figcaption class=\"wp-element-caption\">A summary tab in Excel can be helpful, but how do you know you can trust the numbers you&#8217;re seeing?<\/figcaption><\/figure>\n\n\n\n<p>But the thing with summaries like this is that they can raise several questions. <em>Can we trust these numbers?<\/em> <em>How can we be sure the correct Excel formula is working?<\/em><\/p>\n\n\n\n<p>Let\u2019s 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\u2019t accurate.<\/p>\n\n\n\n<p>This is definitely something that you\u2019d want to check before meeting with the executives, right? Below is the source data that\u2019s also in the workbook.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"534\" height=\"510\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/fixing-spreadsheet-workbook-in-excel.png\" alt=\"Source data that is used for the summary page in an Excel report. This is important to check if an Excel formula isn&#039;t working\" class=\"wp-image-7623\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/fixing-spreadsheet-workbook-in-excel.png 534w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/fixing-spreadsheet-workbook-in-excel-28x28.png 28w\" sizes=\"auto, (max-width: 534px) 100vw, 534px\" \/><figcaption class=\"wp-element-caption\">The source data used in the summary tab.<\/figcaption><\/figure>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>While <strong>crossfooting <\/strong>is technically a formal term in accounting, I\u2019m borrowing it to describe <strong>calculating the data in at least two ways that should come out equal<\/strong>.<\/p>\n\n\n\n<p>With this data, you can see the crossfooting section within the dashed line.&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"363\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-troubleshoot-excel-not-working-700x363.png\" alt=\"One quick, easy way to double check that the data adds up and that Excel formulas are working is by crossfooting\" class=\"wp-image-7627\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-troubleshoot-excel-not-working-700x363.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-troubleshoot-excel-not-working-768x399.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-troubleshoot-excel-not-working.png 1158w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">An example of using a crossfooting method to double check data.<\/figcaption><\/figure>\n\n\n\n<p>There are several components in this fairly sophisticated alert (Again, our objective isn\u2019t to get into the how-to. The main point is to show why it\u2019s important to have these features in good, reliable Excel files.):<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Cell J12 is a total of the Amounts in column E.<\/li>\n\n\n\n<li>J14:J16 totals each product line separately and has an overall sum.<\/li>\n\n\n\n<li>J20:J21 uses the data in column F to get monthly totals.<\/li>\n\n\n\n<li>Cell J11 uses an IF statement to check if those 3 totals are equal. If the totals don\u2019t match, the IF statement shows \u201cBEWARE!\u201d<\/li>\n\n\n\n<li>Conditional Formatting is used to show bold text with a red fill if the numbers don\u2019t match.<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"361\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-mistakes-excel-troubleshooting-700x361.png\" alt=\"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\" class=\"wp-image-7617\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-mistakes-excel-troubleshooting-700x361.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-mistakes-excel-troubleshooting-768x396.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-mistakes-excel-troubleshooting-1536x792.png 1536w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-mistakes-excel-troubleshooting.png 1830w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">The BEWARE! alert in red lets us know that we can&#8217;t trust the data in the summary.<\/figcaption><\/figure>\n\n\n\n<p>This alert is a quick-glance method of knowing whether or not you can trust the data in the summary.&nbsp;<\/p>\n\n\n\n<p>Question: What can we tell from this specific example?&nbsp;<\/p>\n\n\n\n<p>Answer: we CANNOT trust the summary in I3:L6<\/p>\n\n\n\n<p>Send this data back to whomever compiled it, and have them troubleshoot and repair it until the alert looks like this:<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"356\" height=\"383\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/why-excel-formula-isnt-working.png\" alt=\"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\" class=\"wp-image-7631\" title=\"\"><figcaption class=\"wp-element-caption\">When you see the OK alert at the top, you know you can trust the data.<\/figcaption><\/figure>\n\n\n\n<p>All three values are equal, and the Check value says OK.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">4. Right-click: Is there anything hiding? ?<\/h3>\n\n\n\n<p>Here\u2019s a workbook that includes membership data. Members are in three groups on different sheets: Orange, Crimson, and Purple. We\u2019ve got a nice summary on the <strong>Overview sheet<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"527\" height=\"350\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/examples-of-excel-formulas-not-working.png\" alt=\"This overview sheet in Excel has incorrect data, so how can we make sure the Excel formula is working?\" class=\"wp-image-7618\" title=\"\"><figcaption class=\"wp-element-caption\">An overview tab that shows membership data.<\/figcaption><\/figure>\n\n\n\n<p>However, there\u2019s a problem. In this scenario, we know that Dakota is the Chairperson of the Crimson group, not Ariana, as shown in cell D6.<\/p>\n\n\n\n<p>However, in D6 there\u2019s a complex formula that\u2019s retrieving Ariana\u2019s name, as noted below.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"624\" height=\"146\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-an-excel-formula.png\" alt=\"This complex formula in D6 lets us know that there is a hidden sheet named Details in this Excel workbook\" class=\"wp-image-7624\" title=\"\"><figcaption class=\"wp-element-caption\">This complex formula is used in D6.<\/figcaption><\/figure>\n\n\n\n<p>Knowing exactly what this formula is doing isn\u2019t so important. But at a higher level, notice this: Details!. The exclamation point is Excel\u2019s way of telling us that there is a sheet named <em>Details<\/em> somewhere in this workbook. But, according to our view in the screenshot above, we only see sheets named: Overview, Crimson, Orange, and Purple.<\/p>\n\n\n\n<p>The Details sheet must be hidden. ?<\/p>\n\n\n\n<p>Here\u2019s how to find it:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Hover over any of the tabs<\/li>\n\n\n\n<li>Right-click<\/li>\n\n\n\n<li>Select Unhide<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"317\" height=\"367\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/checking-fixing-excel-workbooks.png\" alt=\"To find hidden sheets in Excel workbooks, right click over any of the tabs, and select unhide.\" class=\"wp-image-7615\" title=\"\"><figcaption class=\"wp-element-caption\">Right click while hovering over any of the tabs, and select Unhide.<\/figcaption><\/figure>\n\n\n\n<p>Oh! There\u2019s a Details sheet and an Indigo sheet, both hidden.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"426\" height=\"293\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/excel-workbook-troubleshooting.png\" alt=\"In fact, there were two hidden sheets in this Excel workbook: Indigo and Details.\" class=\"wp-image-7622\" title=\"\"><figcaption class=\"wp-element-caption\">A window pops up to show two hidden sheets.<\/figcaption><\/figure>\n\n\n\n<p>Select Details and OK.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"380\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-someone-elses-excel-workbook-700x380.png\" alt=\"A hidden page in this Excel workbook has incorrect data, which is why the Excel formula is not working properly\" class=\"wp-image-7626\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-someone-elses-excel-workbook-700x380.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-someone-elses-excel-workbook-768x417.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-check-someone-elses-excel-workbook.png 804w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">After clicking OK, the hidden sheet Details shows up.<\/figcaption><\/figure>\n\n\n\n<p>WOW! There\u2019s a lot on this Details sheet, including a separate dues structure for people in the Orange group. And cell G3 is where Dakota\u2019s name should replace Ariana.<\/p>\n\n\n\n<p>You can use hidden sheets for many purposes, including:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>protecting complex formulas&nbsp;<\/li>\n\n\n\n<li>sheets with notes or instructions<\/li>\n\n\n\n<li>background data that needs to be accessible but not always visible<\/li>\n<\/ul>\n\n\n\n<p>If you\u2019re dealing with a workbook that you didn\u2019t create, <strong>it\u2019s a good habit to check for any hidden sheets<\/strong>. They may not always be relevant, but it\u2019s a best practice to find out if any exist.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">5. How do you show formulas in Excel? The accent trick<\/h3>\n\n\n\n<p>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&#8217;re troubleshooting why an Excel formula isn&#8217;t working. It\u2019s 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.)<\/p>\n\n\n\n<p>Below, we received the summary of contractors that need to be paid.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"472\" height=\"229\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/excel-formula-common-errors.png\" alt=\"To double check formulas not working and math in Microsoft Excel, simply press CTRL+&#039; to reveal all formulas.\" class=\"wp-image-7619\" title=\"\"><figcaption class=\"wp-element-caption\">A summary of contractors that need to be paid.<\/figcaption><\/figure>\n\n\n\n<p>Rather than do the math in our heads to determine if these numbers are accurate, let\u2019s take a quick, high-level peek at the formulas.<\/p>\n\n\n\n<p>Press CTRL+`<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"700\" height=\"145\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-errors-in-excel-workbooks-700x145.png\" alt=\"After pressing CTRL+&#039; to reveal all formulas, here&#039;s an example of the formulas you&#039;d see and be able to make sure they&#039;re functioning properly.\" class=\"wp-image-7616\" title=\"\" srcset=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-errors-in-excel-workbooks-700x145.png 700w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-errors-in-excel-workbooks-768x159.png 768w, https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/common-errors-in-excel-workbooks.png 1140w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><figcaption class=\"wp-element-caption\">By pressing CTRL+`, you can reveal all of the formulas being used.<\/figcaption><\/figure>\n\n\n\n<p>And just like that, all of the formulas are revealed. The Hours and Expenses calculations look okay, but the Pay column is strange.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"253\" height=\"185\" src=\"https:\/\/beebole.com\/blog\/wp-content\/uploads\/2020\/11\/how-to-troubleshoot-microsoft-excel-workbook.jpg\" alt=\"A close-up look at the formulas once they&#039;ve been revealed by pressing CTRL+&#039; shows there is some unaccounted for amounts being added and subtracted.\" class=\"wp-image-7628\" title=\"\"><figcaption class=\"wp-element-caption\">Once the formulas have been revealed, you might have further questions.<\/figcaption><\/figure>\n\n\n\n<p>Subtract \u20ac100 from Chana, and add \u20ac75 to Humberto?&nbsp;<\/p>\n\n\n\n<p>While there might be legitimate reasons for this, the point is that we don\u2019t know what they are. This can cause confusion when it comes to understanding why Excel formulas may not be working correctly. We aren\u2019t 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:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Find out what these adjustments are.<\/li>\n\n\n\n<li>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.<\/li>\n\n\n\n<li>Train that person away from the sin of hard-coded values in a spreadsheet.<\/li>\n<\/ol>\n\n\n\n<p>And if the adjustments aren\u2019t valid, we\u2019ve got a different problem entirely that likely needs to involve Human Resources.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The art of understanding why Excel formulas aren&#8217;t working<\/h2>\n\n\n\n<p>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\u2019ve highlighted here are <strong>quick ways to troubleshoot Excel data<\/strong>, ensure your reporting is as accurate as possible, and pinpoint some of the most common mistakes you (or your team) might be making.&nbsp;<\/p>\n\n\n\n<p>Tell me in the comments: What\u2019s been your biggest data snafu in Excel? How\u2019d you fix it?<\/p>\n\n\n\n<div  class=\"mx-auto bbl_cta_block bk-dark\">\n\t<a class=\"bbl_cta_block-blockcontent bbl_cta_block-link d-block overflow-hidden position-relative rounded-4 text-decoration-none\" href=\"https:\/\/beebole.com\/blog\/excel-power-query-for-business-intelligence\" title=\"Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal\">\n\t\t\t\t\t<div class=\"object-fit-cover position-absolute w-100 h-100 top-0 start-0\" style=\"background-image: url(https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/cta_background.483613.svg)\"><\/div>\n\t\t\t\t<div class=\"bottom-0 end-0 object-fit-cover position-absolute bbl-orange-dot\" style=\"background-image: url(https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/orange-dot.47ecad.svg)\"><\/div>\n\n\t\t<div class=\"bbl_cta_block-row align-items-center d-flex flex-md-row justify-content-center mx-0 no-gutters position-relative row\">\n\t\t\t<div class=\"bbl_cta_block-img-col col d-flex justify-content-start pe-md-2 pe-lg-4 px-0\">\n\t\t\t\t<img\n\t\t\t\t\talt=\"Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal\"\n\t\t\t\t\tclass=\"d-block h-auto mw-lg-100\"\n\t\t\t\t\tloading=\"lazy\"\n\t\t\t\t\theight=\"240\"\n\t\t\t\t\tsrc=\"https:\/\/beebole.com\/blog\/wp-content\/themes\/sage\/public\/images\/promotion-post.9422b6.png\"\n\t\t\t\t\twidth=\"360\"\n\t\t\t\t\/>\n\t\t\t<\/div>\n\t\t\t<div class=\"bbl_cta_block-text-col col mt-md-0 ps-0\">\n\t\t\t\t\t\t\t\t\t<div class=\"mb-1\"><div class=\"bbl_cta_block-label lh-base mb-2 mb-md-4\">RELATED POST<\/div><\/div>\n\t\t\t\t\t\t\t\t<div class=\"bbl_cta_block-title lh-base\">Excel Power Query: The 5 hacks for business intelligence you should have in your arsenal<\/div>\n\t\t\t\t\t\t\t\t\t<div>\n\t\t\t\t\t\t<div class=\"bbl_cta_block-button h6 lh-1 mb-0 mt-3\">\n\t\t\t\t\t\t\tRead more\t\t\t\t\t\t\t<svg class=\"ms-2\" width=\"15\" height=\"14\" viewBox=\"0 0 15 14\" fill=\"none\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\">\n\t\t\t\t\t\t\t\t<path d=\"M5.9375 1.09375L6.625 0.40625C6.9375 0.125 7.40625 0.125 7.6875 0.40625L13.7812 6.46875C14.0625 6.78125 14.0625 7.25 13.7812 7.53125L7.6875 13.625C7.40625 13.9062 6.9375 13.9062 6.625 13.625L5.9375 12.9375C5.65625 12.625 5.65625 12.1562 5.9375 11.8438L9.71875 8.25H0.75C0.3125 8.25 0 7.9375 0 7.5V6.5C0 6.09375 0.3125 5.75 0.75 5.75H9.71875L5.9375 2.1875C5.65625 1.875 5.625 1.40625 5.9375 1.09375Z\" fill=\"#fff\" \/>\n\t\t\t\t\t\t\t<\/svg>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t<\/a>\n<\/div><div class=\"bbl-post-disclaimer\">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.<\/div>","protected":false},"excerpt":{"rendered":"<p>When it comes to working with data in Microsoft Excel, especially if it wasn\u2019t your data to begin with, things can get tricky quickly. Between Excel formulas not working, human error, and simply trying to understand someone else\u2019s thought process, there are a lot of ways checking and fixing an Excel workbook can go wrong. [&hellip;]<\/p>\n","protected":false},"author":26,"featured_media":7775,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[123],"tags":[3989,4012],"class_list":["post-7605","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-productivity-time-management","tag-excel","tag-tutorials"],"acf":[],"_links":{"self":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/7605","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/users\/26"}],"replies":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/comments?post=7605"}],"version-history":[{"count":12,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/7605\/revisions"}],"predecessor-version":[{"id":12599,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/posts\/7605\/revisions\/12599"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media\/7775"}],"wp:attachment":[{"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/media?parent=7605"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/categories?post=7605"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/beebole.com\/blog\/wp-json\/wp\/v2\/tags?post=7605"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}