Solution: Excel drag to “fill” not working - value is copied, formula ignored

Подписаться 2,7 тыс.

Просмотров 189 тыс.

50% 1

A client of mine in the past ran into an issue I hadn’t seen before. When she would click a formula cell and drag down to calculate it across multiple rows, it only copied the value. The formulas were correct, but the value being shown was from the original cell. This video shows the solution. Companion blog post can be found at natechamberlai....
Subscribe to this channel: bit.ly/3fWYhZQ
Donate to help support: bit.ly/3AE3M99
LinkedIn: / nchambe
Website: NateChamberlai...

Опубликовано:

1 окт 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 389

@nataliebaddour3089 3 года назад

You just saved me from a meltdown, much appreciated

@commiebobo 3 года назад

This didn't change anything for me, still getting all the same results. Everything is set as you specified. Any advice?

@deannad.4334 3 года назад

Same thing happened to me and this helped….zoom your sheet in to about 200% then switch to using a mouse and try again.

@simdawgs8185 3 года назад

Hey, so I found for me that in my formula I wanted a particular number to be used repeatedly while others change as they go down the table (essentially locking the cell that is used repeatedly down the collum). You can do this using the '$' sign. If placed before the letter (e.g. $A1) when dragging horizontally it will use the same letter (wont use the next letter and same number). When dragging vertically you place the '$' before the number (e.g. A$1) an it will use the same number (wont use the next number and same letter). FYI tho, if you place '$' infront of both the number and letter ($A$1) it makes it an absolute value which can sometimes be the reason certain formula's/equations wont work. Hope this makes sense and helps you out. I was stuck for hours on this until by trial and error I came across this. :)

@simdawgs8185 3 года назад

@@deannad.4334 Hey, so I found for me that in my formula I wanted a particular number to be used repeatedly while others change as they go down the table (essentially locking the cell that is used repeatedly down the collum). You can do this using the '$' sign. If placed before the letter (e.g. $A1) when dragging horizontally it will use the same letter (wont use the next letter and same number). When dragging vertically you place the '$' before the number (e.g. A$1) an it will use the same number (wont use the next number and same letter). FYI tho, if you place '$' infront of both the number and letter ($A$1) it makes it an absolute value which can sometimes be the reason certain formula's/equations wont work. Hope this makes sense and helps you out. I was stuck for hours on this until by trial and error I came across this. :)

@drAHS85 2 года назад

@@deannad.4334 Thank you Deanna D. This helped me a lot. Tried OP´s solutions but yours was the one that worked.

@stephaniedonnellan 2 года назад

@@simdawgs8185 Ya legend thank you!!!!

@travislvanslooten 2 года назад

PERFECT. Spent about 30 minutes racking my brains over what was going on with my spreadsheet because it wasn't calculating correctly. Your video solved my problem in minutes. THANK YOU!!

@tanxiv Год назад

same here! 😅

@himanshudongre563 8 месяцев назад

Same here... Now worked

@MsYongbang 3 года назад

OH MY GOD! THIS IS EXACTLY THE ANSWER THAT I AM LOOKING FOR RIGHT NOW. YOU SOLVED MY PROBLEM, THANK YOU SO MUCH!

@gordonmolapo6775 2 года назад

Hi, thank you so much for the demonstration. However, the two options do not work and I was hoping there's a third option

@hullo5 2 года назад

Hey Nate, thanks for the video. It's not working for me, but maybe you can help. I just want the 'fill series' to work without me manually selecting it. I want to type the number '1' in a cell, drag the handle down, and have it continue '2, 3, 4' etc. Right now it's just copying '1' a million times. I know that I could change the autofill options, but every time? No thanks.

@LionelJenkins2107 День назад

For everyone who says it does not work after enabling . Simply Keep CTRL key Pressed DOWN while DRAGGING. VOILA !!!

@tehnikbeograd6377 3 года назад

Oh man, you are my hero, i have been busting my ass for 2 days trying to solve the problem. Extremely helpful video!

@jrob3511 2 года назад

FYI if you have a filter set on the column, autofill will only ever copy the cell you're dragging from.

@o0chowmein0o 2 года назад

THIS!!!!

@joedavoc Год назад

My hero.

@SatisfiedOnion Год назад

Very seldom (never?) do I run across a strange issue and actually find the correct solution on the first link I click. Baffled was an understatement. I could not figure out for the life of me what was going on! Thank you for this video.

@viki19910 2 года назад

thankss!!!! I have changed to manual previously now i remember..

@bryanlopez4974 3 года назад

Wow, I wish I came to youtube first. It would've saved me a 5 hour headache. Thanks Nate!

@davidormsby9439 2 месяца назад

Neither of those solutions worked for me. What's happening is that if I take cell D10 and multiply it by C11 it gives the answer. But if I drag down it now takes d11*C12 instead of D10*C12

@wallypelaez 2 года назад

Hello Nate, I’m having the same problem, I already tried the two options you share in the video but still couldn’t fix the issue. Please let me know what else I can do. Thank you!

@ShellsFavoriteThings 2 года назад

Same issue for me. Both the fixes shown in the video were already that way on my end, but the formula will still not carry down for the series. It only copies the same formula for the initial cell into all the other cells.

@Gurmukhi_Class 2 года назад

I ll fix it, Go to Formulas and then click calculation options and enable Automatic mode👍

@LionelJenkins2107 День назад

For everyone who says it does not work after enabling . Simply Keep CTRL key Pressed DOWN while DRAGGING. VOILA !!!

@mariaod 3 года назад

Thank you! Thank you! You rock! I upgraded to Excel version to 2016 and thought it was that! I spent a better part of 2 hours reformatting my data, cleaned, trimmed, etc., trying to figure this out! OMG!!!!

@yinkaonireti9774 Месяц назад

Looks trivial, but spent over 1hour trying to figure out where the problem is until I watched your video.....thanks

@TicklePickleLover Год назад

i spent 3 days reading blogs and watching how to videos, yours is the only one that mentioned the calculator function. Thank you!@!!!!!

@heartzxo Год назад

😊😊😊

@AtulSharma-vv5fw 2 года назад

Perfect, I am putting my head to solve this small problem but could not do, Thanks for sharing trick to solve my problem.

@carterloesch2216 2 года назад

So this issue is happening to me but I have the edits changed- I made those fixes and it still is dragging down the same numbers- why would this be happening?

@andytownman1916 Год назад

Can't like it twice (why not RU-vid?) so commenting instead. Great video!

@yaboy2288 4 месяца назад

Video was to the point and it worked successfully for me. Thanks very much.

@fantasytalker 3 месяца назад

great video! so helpful to fix a templete I used from work. thank you!

@thissmiss6563 4 месяца назад

My boyfriend is having the same issue but none of these fixes work, all the correct settings as described in this video were already set correctly. He has work for school due and has to do every one by hand -_-

@LionelJenkins2107 День назад

For everyone who says it does not work after enabling . Simply Keep CTRL key Pressed DOWN while DRAGGING. VOILA !!!

@carlyporter363 2 года назад

THANK YOU! I was ready to jump out the window.

@laurencoopermarketeer 2 года назад

Thanks a million for this tip - i was nearly going nuts trying to figure out why the formulas weren't' calculating properly.

@addinew 10 месяцев назад

THANK YOU KING IVE BEEN IN MY FINANCE CLASS FEELING SO STUPID!!! sometimes when I enter a number in a cell like 1, it will automatically change the format to 0.01? There is no number/currency/percentage formula implemented! Anyone know?

@NateChamberlain 10 месяцев назад

Sounds like the data type of the column may be percentage. This makes it so you don't need a formula to show numbers correctly. 1 would translate to 0.01. Select the column, then use your top ribbon menu to change the data type from percentage to whole number or decimal number.

@addinew 10 месяцев назад

@@NateChamberlain It was preferences, data, then uncheck the decimal place holder mine was set to two!

@NateChamberlain 10 месяцев назад

Glad you got it! 🙌

@angellegna9662 Год назад

Thank you so much for information.. it's very useful to me..

@katendefrancis6349 4 месяца назад

30 minutes issue on my side got solved in two seconds! Thanks

@naderibrahim6714 3 года назад

My laptop was about to learn how to fly....Thank you so much

@summer284 Год назад

Thank you thank you! I was wondering what I was doing wrong :)

@isaaccorreia906 2 года назад

Good Video very happy men thanks bro u saved my work

@devendrajain7099 4 месяца назад

GOOD JOB BRO IT WAS BOTHERING ME FOR LAST 5 DAYS 🤗🤗🤗

@LionelJenkins2107 День назад

For everyone who says it does not work after enabling . Simply Keep CTRL key Pressed DOWN while DRAGGING. VOILA !!!

@sophiemarengere5718 2 года назад

THANK YOUUUUU. I was so frustrated lol

@Jason-fp2qm 4 месяца назад

Omg thank you

@divesh613 2 года назад

thanks bro..saved my precious time

@debaleenadutta2265 11 месяцев назад

This post saved me!! Thank you so much :)

@ankitamalakar6137 2 года назад

Thank you so much 😭 I have tried every possible method to solve this problem since hours. you just saved me. Thanks a tonnn 😭

@aravindbalakrishnan4254 Год назад

Thank you sir for the solution to this problem.

@navyasinchana7281 Год назад

Superrrr!!!! thanks... worked like a champ!!!!

@mm.f262 3 года назад

God bless your heart! I was now about to catch an uber to the golden gate bridge!

@NateChamberlain 2 года назад

Glad it helped 😄

@ineszimmer835 2 месяца назад

THANK YOU SOOOO Much for this!!!! 😊

@liginmathew1874 11 месяцев назад

Perfect solution for my issue with out any lagging of time. Spent lot of time to crack the issue. Thanks a lot for your video

@sarahwalsh3714 2 года назад

This is all useful, but still not fixed my problem. Can you suggest a solution for if when copying the cell formula its resulting in a zero value in adjacent cells. I have tried both options in the video, but still doesn't work Instead the result cell formula has changed slightly from the original cell and instead +1 column plus formula....for example original cell details d4*xx/12 copied cells then generate e4*xx/12, f4*xx/12, g4*xx/12 and so on ......all with a zero value. Any help greatly appreciated....

@MariusB86 3 года назад

THANK YOU SIR! - I had filtered the table and that's why it didn't fill the series for me.

@blancitakiwanga374 Год назад

You saved my life today!! Been trying for hours without a success. Until I found your video. Thank you so much❤

@GauravRodhia-t6u 3 месяца назад

THANK YOU

@JenniferMeyer84 2 года назад

You're a lifesaver, weirdest error I'd ever seen and COULD NOT figure out how to fix it! :)

@jackmurrayahern3837 Год назад

Omg thank you, this was doing my head in

@angelabannister455 2 года назад

Baaaaaaybeeeee this simple thing was a LIFESAVER!!! Thank you!!!!!

@ATS2190 2 года назад

hahaha I hate that I was mentally screaming "ok enough talk, just show me the solution" on a 1:51sec video lol, work will do that to you sometimes.....thanks for the help Nate!

@NateChamberlain 2 года назад

Ha, totally understandable. Glad it helped!

@vinayvishwanathan613 2 года назад

Thank you so much, I had to deal with a huge data and dragging was not working. I was so frustrated and this solved the problem

@grrrzie 11 месяцев назад

I need help for something similar...I need formula to drag down, but I'm using figures that are part or a pivot table, with the results outside of the pivot it's self... so I want to know the different in balance A (inside of pivot) compared to balance B, outside of pivot..any ideas? It works one by one, but obviously that isn't an option..many thanks

@ethanyong3589 3 года назад

found your article and it helped me out. decided to come here to give you a LIKE! thank you!

@iokona 8 дней назад

I followed everything as you said and it’s still copying over the original number.. it’s driving me crazy.. paper due tomorrow

@skilo81 9 месяцев назад

How can I add the number in one cell to a list of numbers in a column. The formula keeps picking up the blank cells underneath the cell I want. Instead of using B3 it will use B4, B5, B6 and so on.

@bobbydean5943 27 дней назад

@nate Both Enable fill handle and Automatic are enabled/selected, but the drag and drop is still not updating the formula in the next cells. Also the entire worksheet is set to "General", but still not working.

@ajinkyababar6072 2 года назад

You saved me ! Boss had just asked for an urgent excel and i had got stuck

@deepanshukhurana5901 3 года назад

Thanks a lot , wasted 3 hours in getting this thing , really much appreciate 👍

@chestbuster1987 11 месяцев назад

Awesome! Resolved my issue immediately!

@megwoodland6005 2 года назад

Hey thanks for the explanation.

@chiranjeevik1674 2 года назад

Thanks a ton.. its a life saving hack..

@toystoreandmore1135 2 года назад

Just came across your video and it solved my problem straight away! Thank You :)

@andytownman1916 2 года назад

Thank you so much. I had changed this setting to improve Excel performance (a tip from another site) but didn't realize it would have this unintended effect.

@johniedove5967 2 года назад

Thanks for restoring my sanity!

@Reva_Prime Год назад

Thanks and love from India 😊

@Xocyndy21oX 2 месяца назад

Thank you! You're the best!

@abeselomtadesse8237 2 года назад

Thanks bro.. I was searching hard for a solution & u saved me.

@SossityCCorbyMCC Год назад

Tried all of these solutions, nothing worked, the amount of time I spent trying to fix it, I ended up having to calculate each cell by hand.

@fatihgul77 8 месяцев назад

Oh man, you saved my life 😃 Thanks another for your sharing. I was searching this solution for a wile🎉

@martinbell7781 2 года назад

I have an issue where i want it to go 1,2,3,4 and it always goes 1,1,1 i click two boxes 1,2 to establish the pattern and it still goes 1,1,1,1. Very strange

@divyanshsingh6368 2 месяца назад

Thanks the last option worked

@bjac1682 3 года назад

Thank you! In my case, a fill handle does appear, but the options are not what I expect (copy, fIll series, etc); instead, it has options for formatting, charts, totals, etc. The fill handle icon also looks different. Instead of the one shown on your video, mine shows a gray button with a yellow lightbolt. Any suggestion for reverting to the "normal" fill handle button? Thanks!

@jonathanvalencia5260 Год назад

How did you fix this?

@audreyafe-tuufuli2015 9 месяцев назад

Hey Nate, I am trying to copy data from one workbook column to another workbook column but the formula remains the same. What is wrong

@damianpellerano6795 Год назад

Life Saver - worked very well.

@CollegeYari 11 месяцев назад

Thanks buddy. Helped❤

@odnalor182 10 месяцев назад

All of a sudden, the fill drag-down feature stopped working for me when referencing a different sheet. If I use the reference within the same sheet, the fill drag-down works fine.

@NJards-zt4fp Год назад

Thank you! I would never have found the cause if not for your video.

@avantj 5 месяцев назад

Bill gates company has disabled it deliberately this feature even on other apps if used in any other operating system other than his own. Use it in old version of windows and it will work

@Rashmismgi 4 месяца назад

both did not help fix the issue

@yitao5685 2 года назад

Thank you so much! this helps me a lot!!!! save me out of the depression!

@pranavthakkar9714 2 года назад

ultimate help for me i serch for this from 5 hours now get the solution thank you dear sir love your point to point not every where.

@NateChamberlain 2 года назад

Happy to help

@kevinhanks5613 3 года назад

Thanks so much. Not sure how that happened, but it worked.

@abdelrahmanmedhat2161 2 года назад

Thanks a lot, it was driving me crazy

@bobmoore1319 2 года назад

Thank you, thank you, thank you. That was driving me nuts

@NikhilHarshay 11 месяцев назад

thanks sir was working with offset function and not able to understand what happening you might just saved my job thanks

@rossdownie1502 2 года назад

Thank you so much mate cheers

@yoxi401 2 года назад

i've already checked and tried these two solutions but it still didn't change anything for me :( i'm gonna lose my mind

@ngourab98 3 года назад

No amount of "THANKS" is enough for this. Subscribing to your channel.

@anthonyramirez7663 Год назад

I'm having the issue we're my formula is on but the color won't show it stays white and I already selected all the auto fill options

@IshaBhutani-z6z Год назад

Thanks so much for this video. I wasted 1 hour rto figure out what was going on with my spreadsheet:)

@frankiepaul77 2 года назад

Lifesaver bro. Fixed a report I had to give my boss by the end of the day.

@dyyy270 2 года назад

God you’re a life saver I was about to break my monitors

@Vagabondion 3 года назад

saved my life ! i was about to break my laptop from anger !)

@leahbaiden6377 2 года назад

OMG!! Life saver for real! Thank you a million times.

@LouisCharlesish 2 года назад

Im having a similar issue on Mac, both automatic calculation and drag to fill option/preferences are set correctly. For example if I copy one cell A1 and paste to B1 this brings the formula or Function and data across. If I drag A1 to fill B1, B1 = some other random cell in my sheet and removes my function/formula. Ie A1= CORREL(A2:A150,B2:B150) when dragged to B2, B2 = (K16-K17)/K17. HELP