3 Reasons Why Excel Formulas Won’t Calculate + How to Fix - Excel Tutorial
HTML-код
- Опубликовано: 13 окт 2024
- Excel formulas not calculating??? Take a look at 3 things that can cause your Excel formulas to stop working and how you can fix the formulas.
When Excel formulas don't calculate, it's typically due to numbers and / or formulas accidentally formatted as text or a change in the settings of the workbook.
In this Excel tutorial, we'll go over issues with text formatting and with formula and calculation settings that can make your formulas not work. All are surprisingly easy to fix!
Click here if you would like to read our step-by-step article on this: yacostasolutio...
If you liked this, make sure to get your FREE pdf list of top Excel keyboard shortcuts: yacostasolutio...
*FYI:
This tutorial was created using the desktop version of Excel in Microsoft 365. You can upgrade to this version here: yacostasolutio...
The screen recording + editing was done using Techsmith Camtasia. You can check it out here: yacostasolutio...
Thanks for watching this Microsoft Excel tutorial and, don’t forget, if you like this video, please “Like” and “Share” with your friends - it really helps us out 😊
To catch our Excel tutorials as they come out, subscribe to this channel by clicking: yacostasolutio...
Thanks!
*DISCLAIMER: This video description contains affiliate links, which means that if you click on one of the product links, I’ll receive a small commission. As an Amazon Associate I earn from qualifying purchases. This helps support the channel and allows us to continue to make tutorials like this. Thank you for your support!
#YAcostaTutorials
13 Jan 24: Before I found your video, I must've watched 10 other ones to figure out how to fix my number columns that wouldn't sum. When you suggested going to Data and Text to Columns, that was the trick I needed to fix my problem. Thanks for being awesome and helping me overcome massive frustration!
Awesome! I’m so happy this helped you 🙂
YOU ARE AMAZING! SHORT AND TO THE POINT. I SPENT 2 HOURS ON A DATA SHEET AND WAS STUCK ON A PROBLEM. YOU HAVE NO IDEA HOW MUCH YOU'VE HELPED ME
That’s great! I’m so glad this helped you 🙂
Quickly covers problem areas with clear instructions on how to fix your Excel problem. Doesn't draw out explanations or long beginning opening remarks. It was perfect.
Thank you, Rusanne! I really appreciate the compliment 🙂
Thank you mam. I can solve my problem for which i was suffering for last three to four days. Such video is not available on you tube. Really thank you....
Oh wow! I'm so glad this helped you out. Thanks 🙂
Omg! This really helped me. I have to admit I thought it was a waste of time as soon as I began watching and you answered my concern on point one. Ty!
Lol! I'm happy to hear this tutorial didn't waste your time 🙂 Happy it helped you out!
Replacing = with = has saved my life. Thank you.
2.30 in the morning with so much things to update and this has allowed me to sleep instead of working through the night!!!!!!
Been looking for this for so long (always give googling this a go and can never find a solution that isn't click into every formula)
Oh no, James, that sounds dreadful! I'm happy this helped you get a little sleep 😀
Also, feel free to reach out if you need other help
Thank You for posting this Video with the resolution of the formulas not working.
It was the first time faced such query in excel but You tutorial helped me out. Thanks once again👍
Thank you 🙂 I’m so happy this was helpful for you!
One method I found to be extremely useful is by finding and replacing the = signs. I've been struggling with this for a couple of weeks because by default my formulas were set to automatic in the formula tab and I just could not understand why after switching them to manual and having to double click each cell, the correct formula values would then appear. Bear in mind my dataset had about 28 thousand and some odd records and having to double click each cell just wasn't viable because I had to do the same for about four more columns (i.e., 28k * 4 ≈ 112 k) ,so for me the method I'd suggest is by finding and replacing the = signs if you're working with big data. And to avoid excel from not crashing, select about 500 cells and do the find and replace operation...a bit tedious but it works!
This is a great tip, Kgosi. Excel sometimes doesn’t know what to do with certain characters when there are changes. Similar to your issue with the equal signs, I’ve had quotation marks ruin formulas for me also. Excel can be very sensitive if everything is not absolutely perfect. Thanks for sharing. I think I may be due to make a follow-up tutorial on this topic 🙂
Excel is amazingly hard with only basic understanding and this video alone expanded my knowledge by so much! I was spending hours trying to figure out my excel sheet's issues to no end until I decided to google it out and I found you through it all. You're not the hero I asked for but definitely the one I needed!
I'm happy my tutorial helped you 🙂 There is a bit of a learning curve in working with Excel and the learning never ends! Thanks
Thank you very much!!! I am a self-taught spreadsheet user over the age of 60 and your information helped me understand what to look for, and how to fix it. You are amazing!
That’s awesome! I’m so excited for your journey in mastering spreadsheets 👏 I’m happy this was able to help you in that path. Thanks!
Oh God! I've been trying to solve this problem for a long time,Thank U so much
Awesome! Happy to help 🙂
Thumbs up to you thanks a lot I've been working on this unsuccessfully again and again until i found your video.. Again thanks a lot
That’s great! I’m so happy this was helpful for you 🙂
helped me to thanks, I think it because I made my excel sheet on my iPhone it did not work properly but looking at your video and editing the formula on my pc it works again for me. a big thanks from me
That’s great, Robin! I’m so happy this helped you! 🙂
I think the Excel mobil app is great, but still not as user friendly and robust as the pc version. One day.
Thank you! OMG! I have been having this issue for a some time now. With your help, it has been corrected. Greatly appreciated
Me too! Trying to do my PhD and was stuck on this for so long! Thank you Y.Acosta
That’s awesome, Joanna! I’m so glad this helped you out 🙂
Thank you! I’m so glad this helped solve your problem 🙂
Subscribed your channel for the solution i have looked for.. thank you.
Welcome 👍 Happy to have you! Thanks!
the trick of replacing = with = was very very useful, Thanks.
Thanks, Gejo. It is a really neat trick! I'm happy it came in useful for you 🙂
I want to thank you very very much.... It is very helpful and my problem has now been solved!!
That’s great, Ermias! I’m glad this solved your problem. Thanks 🙂
I been searching for ages with no joy someone on internet said its macro settings had do that nothing then your last option show formulas an hey presto i got a working spreadsheet Thank You sooo much. Like most things soo simple when u know how
That's great, Darren! I'm glad this helped you out 🙂
This video is brilliant! Short, to the point, and solved my problems 100%
That’s awesome, Ken! Happy this helped 🙂
OMG this helped sooo much! I have been trying to figure this out for the last 4 hours.
That’s great, Danielle! I’m so happy this helped you out 🙂
I really like it, it saved my life. I was going crazy with this formula.. Great i fixed it. Thanks
Awesome job, Chrysante! 🏆 Happy this helped you out 🙂
Thanks soo much the last tip worked I was going crazy due date for submission
Glad this helped you 🙂 Thanks!
Thank you so much, this was a time saver for me this morning!
That's awesome! I'm happy this was able to help you 🙂
Thank you for sharing. This helped me out 👍🏾
That's great! I'm so glad this helped you 🙂
Thank you so much.. this has greatly helped me.i was really in a fix.
Awesome, Chris! I’m happy this helped you 🙂
Thank you so much ! this saved me a lot of time and effort. Appreciate your efforts in making this !
That’s great! I’m happy this was able to help save you time 🙂
Excellent explanation.
Thank you! Glad you liked it 🙂
You are a STAR....Many thanks
Thanks so much, Harold! I’m happy this helped you 🙂
Very useful trick, thanks
Happy this helped you, Harshad! Thanks 🙂
So helpful thanks..🙂
Glad it was helpful! Thanks so much 🙂
Wel done ,you explain very well, Iwrite a formula in one cell (any one ,Left, Mid , Right) and take result, But when i write a another formula in another cell and refer previous formula (Left,Mid Or Right) in this formula excel does not show result but when i write manually values rather than using formula (Left,Mid Or Right) excel displys values,why? Thanks
Well done. Stay safe.
Thanks so much, Don. I appreciate your feedback 🙂
It's a really helpful tutorial. Thank's
Thanks, Mamunur! I'm happy this helped you 🙂
Thank you so much ... It solved the error
That's great, Abdul! I'm happy this was helpful for you 🙂
Thank a lot.I was struggling with this issue for hours.
Hi, Rakesh Kumar Gautam. That's great! I'm happy this was able to help you with your issue 🏆
Thank you for the info.
Thanks, Andrew! I'm so happy this helped you 🙂
thank you so much my problem decided by your advice
That’s great! I’m happy this helped you 🙂
JUST WHAT I NEEDEEDDDDDDD THANK U SO MUCH
Awesome, Antoine! I’m happy this helped you 🙂
I have Downloaded my trading data from website in excell format. But I am unable to sum the rows. Is there any procedure for it?
Beautiful! just what I needed. I just wish you would have covered the Calculation Option feature first! :) Thank you
Thanks, Rick! I’m happy this helped you. But sorry you had to wait for your solution. How was I to know 🤷♀️
Thanks u soooo much it was soo much useful
You're very welcome! I'm happy this helped you 🙂
Worked.. thanks.. ❤️
Awesome! Happy this helped 🙂
Thanks! Saved 2 hours for me! = with = is genius
That’s awesome, Happiness! I’m happy this helped you 😀
THANK YOU SO MUCH!!! I WAS GOING CRAZY! lol
So happy this helped you 🙂
Thanks a lot! Its so useful 👍
Thank you 🙂 Glad you found this helpful!
thanks so much, the find and replace "=" to "=" solve my problem, thank you so so much. ;)
This is one of my favorite tricks too 🙂 I’m happy this was helpful for you!
I'm getting an "this cell is inconsistent with the column formula" but there is no formula on the column when I click on it. It is a time formated column that I just enter a clock in time. when I click the "restore to calculated formula" option under the yellow exclamation icon thats when I see an "=column J" that otherwise isn't there. It seems that it isn't interfering with any results at least not yet. But I'd love to fix it if I can regardless. Any tips?
I have my formulas in for multiplication and my column is correct, I am trying to get it to multiply over several cells and it is not carrying it over with the calculations. All the cells are highlighted. What am I doing wrong? I'm using 2019 excel. Thank you.
Thank you, you save my time
Thanks, Muhammad! I’m happy this helped you 🙂
best video about it, thanks
Yay! Thanks so so much! I’m happy this helped you 🙂
This info helped… thanks! 👍🏾
Excellent tutorial!
Thanks so much, Kim! I'm happy this helped you 🙂
Thank you for the information. I have an issues with a few formulas from a report but my issues was not review in this video. The formulas in my reports are present but for some reason they are not showing. I checked to see if the cell was format with white but no . I can see the formula in the edit bar but as soon I click enter It looks they the cell does not have a formula.
Hi, solapensado123. This sounds like it might be a formatting issue. Let's start by trying a couple of things.
First, on the cells with the formulas, check what the formatting is. Try clearing the formatting to see if that helps (Home tab, editing section, then use the drop down in the "Clear" option).
The second thing you can try is checking the formatting options for the sheet. Go to your File menu, then Options, and click on the Advanced tab. Then scroll to "Display options for this worksheet:". There should be an option that reads "Show a zero in cells that have a zero value." Make sure this is selected.
Please let me know if you continue to have troubles. Thanks
I try both suggestions but it did not work :(
I'm curious - do the formulas calculate? What I mean is can you see the result, if there is one? Or is it the " 0 " or " - " that would normally display if there's no result?
If you like and are able to, can you send me a sample of this? Doesn't have to be the entire file. You can remove any confidential info. You can email it to yilacosta at outlook . com
TY, finally made it work!!!
Awesome, Brenda 🏆 Happy this helped you!
Thank u so much
Thank you too! So happy you liked this 😊
Got my answer on 3:27 thanks a lot.
I’m so happy this helped you 🙂 Thanks!
This was super helpful, except it didn't solve my problem and it's the 3rd video I've watched. I think I really messed up my simple (adding only) spreadsheet.
Dear ma'am,
Nothing is working in my sheet. I want to convert numbers into numbers format . They are in text format. Can you please let me know why formulas aren't working. Thanks
THANK YOU MAM❤
Thank you! I'm glad this helped you 🙂
tqvm very useful, my problem solved
Awesome, Zaara! Happy this helped you 🙂
Thank you 👍...
Thank you 🙂 Happy you liked this!
Hi, i tried all methods that u told but it didn't work in excel 2007 version what to do ? it is showing 0 as the answer
Thank, it did help
So happy this helped you! Thanks 🙂
Thank you soooOooo much
You’re very welcome, Khosi! I’m so happy this helped you 🙂
Thank you ma'am
Thank you! I'm happy this helped you 🙂
thank you so much
Thank you, Rhasenda! I'm happy this helped you 🙂
Thank you it worked
I’m so glad this helped you! Thanks so much 🙂
wow...thank you .... you have saved me at this time of the day
Thanks, aneela! I’m happy this helped you out 👍
Could you please tell me why im not able to make a multiplication chart in excel using the $ symbol?
Thanks alot
Happy this helped you! Thanks so much 🙂
Very Useful Video
Thank you, Kiran Kumar S G. I'm happy this was useful for you 🙂
your right 😊
Thanks, jhena 🙂
HI, I USE ONLY AUTO SUM IN FOR,ULA
TAB.RECENTLY WHEN I OPEN NEW/OLD FILE THE LOGI
Thanks
Thank you, Colin! I'm happy you liked this tutorial 🙂
Really helpfull
Thanks so much, Thilina. Happy this was helpful for you 🙂
Lifesaver!
Awesome! 🙌 So happy this helped you 🙂
Mam i wanna calculate amt * mins but calculations is not coming exact how to fix it please help
Hi, Minakshi. You can try using the TIME function. So, something like 3*TIME(0,15,0) where 15 is 15 minutes. If the result looks weird, you may just need to select a different type of time format. Here's a playlist of a few time-related tutorials I put together: ruclips.net/p/PLhRdf5TGxBMwCawvLGBmJ8N5ABDbE0YXB
The video on adding and subtracting time has an example of using the TIME function. I use it for addition, but multiplication will work too.
The Format Time video will go over different time formats you can choose from.
Hope this helps and thank you! 🙂
Thank you!! :)
Thanks, Tony! I’m happy you liked this 🙂
Madam i struggling in filter than sum Problem when i use subtotal than sum not workingis is this way working
Hi, Jay. I’m not sure I understand your question? Are you trying to use the subtotal function? Or Groupings? Please let me know. Thanks
@@YAcostaSolutions yes
Is there any way to contact with you except RUclips comments section? Like any website or business mail address? I actually have a ton of suggestions for your excel videos.
Hi, Sanour. If you're on the desktop site, you can go to my channel's About page to find my contact info. Looking forward to your ideas! Thanks
thanks mam i watched inf video of this problem but nothing happned and i watch your video the problem solved (iam from india)
That's great! I'm happy this helped solve your problem 🙂 Thanks
Life saver
That’s great! So happy this helped you 🙂
Good Video for basic errors, but this did not solve my problem. I gave you a thumbs up anyway because your solutions for the issues that you addressed were good & straight to the point. I have an issue where my columns are confirmed formatted as currency. The cell that I have issue with is calculating a value that should be resultant in zero is posting a 0, but then pasting a value greater than zero. My IF Formula is very basic... =IF(B6>0,"Y',"N') It is resulting in N, when it should be Y. The cell it is assessing (B6) has a formula that says =Sum(B4-B5). FYI: B4 = -$60.45 and B5 = -$60.45. However, when I copy and paste the results AS A VALUE to insure that excel is doing what it should, it gives me this... 1.06581410364015E-13 ??? which is why the IF formula is not responding properly. Do you understand what is happening?
I figured it out. Since B4 and B5 were summed values, the true value of B4 was a rounding issue, hence the resultant 1.06581410364015E-13. I used "evaluate formula" to confirm this. Two solutions to my issue in case anyone had my problem as well... Option 1) If you aren't doing a particular work where you need the accuracy of the decimal places in your calculations, you could go to File > More > Options > Advanced > and then under" When calculating this workbook," select the "Set precision as displayed" check box, and then click OK. For some calculations, I need the extra decimal spaces so this doesn't work for me, since it is applied to the whole workbook. Option 2) I simply used SUM and RoundUP functions together. Example =ROUNDUP(SUM(B4-B5),2) Applying this to the appropriate cells gave me a value of zero, rather than what it gave before. I hope this helps someone else.
धन्यवाद महोदया
You're welcome, Arvind! I'm happy this was able to help you 🙂
thanks i subbed
Awesome! Happy to have you join us 🙂 Thanks!
very innovation Lectures*
Thanks so much! I really appreciate your kind feedback 🙂
thanks a lot
Thank you, samoon yaseer arafath! I’m happy this helped you 🙂
All doesn't work on my case, just sharing if anyone have the same problem with mine, the numbers showing a dollar sigh but it was in text form what I did was delete the dollar sign and replace it with space.
Thanks for your feedback. The 3 issues listed in this tutorial are not an exhaustive listing, but common issues. I'm happy you were able to figure out the problem with your sheet.
Tnq
Thank you 🙂
Thanks madam
Thanks, sultan mahmud! Glad you liked this 🙂
@@YAcostaSolutions yes madam till 6 months we cant calculate official chain mail of shift wise production report in excel file but its ok after see ur video. Thanks again.
That's awesome! Thanks for sharing.
thanks use full
Thanks so much! 🙂
Not solve my problem help me
There is a 4th reason then because I checked all those three and still doesnt add.
EXCEL used to be relatively straightforward to use, if not exactly intuitive. This new version is full of problems requiring non-intuitive solutions. Look at the comments. people going crazy, struggling for hours, etc., all over a simple issue that didn't exist in previous versions. Where did the problems come from? Lost competency in program coding? Yet, the solution is not to fix it but to spoon feed complex multilayered solutions to problems MS and its programers created. If you aren't either afraid of what is going on or furious about it, there is something seriously wrong with you, because however old you may be, none of us have that many hours to waste on things like this. Welcome to the Brave New World folks. Good luck to us all, because we'll need it.
My concatenate function not working in excel.. Showing 0 values in cell.. Y so
Hi. What are you trying to concatenate? How are the cells involved formatted? Any other details you can provide would be helpful. Thanks
@@YAcostaSolutions actually I imported the table from access to excel.. Then I tried to concatenate first name n last name but it showing 0 in the cell. Don't know y.. All other functions r giving correct results. But not able to concatenate.. What could be the reason Plse tell me
It should work. Have you double checked that none of the cells involved are formatted as text? Also, try using Find & Replace to find: = and replace with = This can sometimes help reset the formulas.
@@YAcostaSolutions tried.. But still particularly in that table concatenate showing 0 value .but first name n last name r converted in to proper Text function.. Will this be OK? After converting to proper function will concat shows 0 value?
Like this tutorial? Make sure to grab your FREE pdf list of top Excel keyboard shortcuts: yacostasolutions.com/newsletter/
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaamazing!
Thank you so much! 😊 I'm happy you liked this.
❤
Hearts back at you! ❤️❤️❤️ Thanks so much 🙂
LESSSSSGOOOOOOOOOOOOOOOOOOOOOOOOOOOOO
Unfortunally none of these optons worked!
You are like goddess to me
That's very kind of you to mention. I'm so glad this helped you 😊