thanks for the tip Bill! I removed the table then re-inserted a table and the formula now populates automatically. I also dragged the formula to the last remaining rows since some were not populated with a formula so that could have also been part of the issue.
@michaelc88263 жыл бұрын
3:22 This helped me figure out why an old formula was being populated in a new row after I had changed all the cells to a different formula. Thanks
@MatthewBowman7 ай бұрын
I found this because I had the same question. My table has data in multiple categorized columns which are tallied in a bottom row. Right-clicking to insert a row did not allow that to carry over at the bottom (right before the tally row). After watching this, I instead inserted a new row at the second-to-last spot above the tally row, (so if I had 9 rows of data tallied on Row 10, I inserted it at position 9, so the Tally Row became position 11), copied the data from the last entry into the blank row, deleted the data from the last row, renamed that row to show it was just junk with no data, and everything is now fine. I can insert new rows into the "middle" of the table, but not the end of it. I'm sure there must be a more elegant way to do it, but it's working now.
@wayneedmondson10654 жыл бұрын
Hi Mr. Excel.. good tips! This has always worked for me.. as I use the default settings. Nice to know about the circumstances when it might break. Thanks for the insights. Thumbs up!!
@GeoWolf7893 жыл бұрын
Mate! best content i have seen in a long time. short sharp perfect, I love how you read out your key strokes. very very helpfull
@user-yo7rv6gn1p18 күн бұрын
Thanks a ton sir......
@mattschoular88444 жыл бұрын
Thanks Mr.Excel. Excel tables are super handy but in cases like this, the auto calculate feature can be dangerous if you're not paying attention. It's good to know how it breaks and also how to disable auto calculate.
@MrXL4 жыл бұрын
For me, tables are too buggy. But I know others love them!
@Pezhan4 ай бұрын
I am ejoying the videos, and you seem like the man to ask. I am having some trouble creating a macro. I need to seperate the last cell by the number of words contained within it, copy all 5 cells to which includes the last cell, paste the copied cells below the original 5 cells , add a letter (A,B,C, or D) to the end of the text of the first cell. Is this to complicated for excel, or is this something you may know how to do?
@AusFastLife Жыл бұрын
I see what you have have shown working for you. When I type up what you are showing in a new spread sheet, the new spread sheet works exactly as you have shown it to work in this video, so thank you for that. In my actual spread sheet though I have an excel table. In that table I have a formula that carries out an Index Match on a table on another page and enters the matched value in the cell. My problem is that this is not copying down in the same way that the simple formulas you are showing do. The formula I am using is: =IFERROR(T(INDEX(Provider_List9[Type of Provider],MATCH([@[Provider Name]],Provider_List9[Provider Code],0))),"") The IFERROR formula is just to get the result to be blank rather than 0 when the cell being referenced is empty. I can manually copy the formula down and it works perfectly, but when I add new rows the formula does not automatically copy down. I am just adding this here to note that there are use cases in which formula do not automatically propogate, in addition to simpler use cases when they do. I don't know why.
@PatHernandez000 Жыл бұрын
Same issue here, did you found out a solution? thks!!
@HandFoodler Жыл бұрын
Hello! Thanks for the video! I'm still having a problem, and I've ensured that my "Extend data range...." is selected. So when you say, " you have to have three identical formulas in three consecutive cells", do you mean three consecutive cells in the same column or row, or does the direction not matter? When I add a new row, some of the formulas automatically copy over. Other cells will copy a formula that isn't in the cells above or below. So right now, I have to go in and change about 70 different formulas on each new row I add. This is because we are counting deliveries for each month, then those are broken down into age groups in each household (row), and then all summed into quarterly columns, and then a yearly column as well. None of us up here are Excel experts, so I'm sure there are probably easier ways, but this is the established tracking system, so here I am. :) This spreadsheet has gone through about 4 other's hands before it got to me. Heeeeeeeellllllpppppppp!!!!
@gekalskip8 ай бұрын
I would like to insert a new row at the TOP of my table every time i go and enter a new line of data, so that i dont have to scroll down each time. Therefore the automatic application of formulas doesn't work to this new row (because its ABOVE the rest of the rows, not beneath 4 rows as is required). Is there any solution to this?
@EXCELFACILERAPIDO4 жыл бұрын
Congrats!!! very good👏👏👏
@alchrisu3 жыл бұрын
Do we know how to automatically add another row in another sheet, a sheet that copies what we put in the first sheet? Along with the formula, of course. Thanks for the video!
@markjohnston28272 жыл бұрын
Thanks! I had a case where all the formulas looked the same but had been added at different times. I copied the formula text as text, using an apostrophe, to a blank cell, deleted all the formulas in the column where the formula wasn't propagating correctly, closed the spreadsheet, reopened the spreadsheet and copied the formula without the apostrophe into the top cell and then it worked (I then deleted the text cell contents).
@chantelleyurechuk97642 жыл бұрын
Just to let you know, I am using latest Excel , my options to extend data is selected, but when I insert rows, where I have extended a formula in the column, it DOES not copy! If you have any other answers, ..
@darrylmorgan4 жыл бұрын
Really Great Tips..Thank You Mr Excel :)
@etainpages596427 күн бұрын
I am having an issue with this because it works well UNTIL I protect the sheet. I only want to protect the column with the formula, but when I "allow add row" while its protected, the new rows don't carry down the locked formula cells
@iamlive88955 ай бұрын
How i auto add formatted table as i designed eith formulas when i add the contents in previous sheet ? Is this posssible ?
@LexiDraws5602 жыл бұрын
Hi Mr. Excel, Enjoyed the way you presented this, no waffle just to the point, however, I have a problem with it! On the first method, the "extend data range formats and formulas" box is ticked but does not copy the formula down when adding a new line, and with the second method, "format as table" this disables the "merge cell" function which is something i need to retain. Any suggestions? Regards Gavin
@MrXL2 жыл бұрын
Check out the ideas here: kzbin.info/www/bejne/hJLCdImdms5pbrc
@danawoods1386 Жыл бұрын
I'm trying to find out if there is an analogous setting in Google Sheets.
@itrstt66 Жыл бұрын
How cna i use alphabetical orders with blank cells, and putting them (the blank cells) in the bottom of the ordering?
@sameetraj8661 Жыл бұрын
what if the sheet is protected with some cells locked from editing, but when adding a row the formulas are needed to be extended. How do you do it without unprotecting it?
@meridithmatthews8 ай бұрын
When I insert a new data or copy paste value the formula is ruined why is that?
@dkodr4 жыл бұрын
I noticed that Excel *sometimes* struggles to fill the formula when you have a query loaded from Power Query to a sheet *and* a column with a formula added to that loaded table. Refreshing the query and loading new data sometimes causes the autofill to break. And I couldn't find any pattern in this behaviour, because at times it was working just fine. I know it's better to do those additional calculations in Power Query instead of the sheet, but this was a very specific case and it was better for the end user to see the actual calculation happen in formulas.
@drsteele47494 жыл бұрын
I frequently put formulas in columns adjacent to the loaded query Table. It fouls up when the query adds or removes columns. When it does there seems no alternative but to rebuild the whole worksheet.
@MrXL4 жыл бұрын
In my casual use of Power Query, I am always pleasantly surprised when the formulas don't get wiped out! But if I came to rely on it, I can see how that would be super-annoying.
@mpo35652 жыл бұрын
Hi, How do I fix if it has broken? My excel is in a table format, with an ordinary sum for columns L:W, however when I insert a new row it automatically calculates the formula as L:V thus excluding W. How do I resolve this !? Many Thanks,
@arayahomes43082 жыл бұрын
Hahaha, mr excel. You're so good, you're outro was soooo cringy it was good. REally though hahahaha.
@freemanstandsbyhumanity Жыл бұрын
Same way can we insert row with data (Name, region, commission) instead of blank row?
@paulblais9210 Жыл бұрын
Hi Mr. Excel now can you insert or delete rows in a protected sheet containing columns with formula and keeping the formula intact?
@MrXL Жыл бұрын
Sorry Paul - I have very little experience trying anything with protected sheets. Not sure what would happen.
@Mr.Fisherman2 жыл бұрын
Hi sir.. can u help with a formula. I have data in the first few columns . Then at the end i have a percentage of a single cell divided by the column total. I keep adding a new column everday before the last column . How do i keep the percentage formula continued
@GokulaKrishnan-xb3yx Жыл бұрын
i did the enable also but not working
@NicBob894 жыл бұрын
Alt I + R... hmmm I always use shift + space then ctrl + shift + space. When it does extra things with the table you can ctrl + Z normally and it will just move back one step...
@MrXL4 жыл бұрын
Thanks for the Ctrl Z tip for tables. I’ve seen the shift + space technique. But the old old Alt I R is ingrained for me.
@christiant9535 Жыл бұрын
Hey Mr. Excel, nice video! I have a protected sheet where users can add new rows and only edit certain columns (protecting the original budget and formula columns). When a new row is created, the standard formulas are not copied into the new row - this only happens when the sheet is protected. Any ideas on cause and potential workarounds?
@MrXL Жыл бұрын
If you are in Windows or Mac (and not Excel Online or Mobile), it would be tempting to give your people a button for inserting rows. The VBA would unprotect the sheet, insert the row(s), copy the formulas, re-protect the sheet, all in less than a second.
@najathemnrojh97832 жыл бұрын
hi .. thx u for your nice video but its not working i mean not going to expanding table if i protect table with pass word i dont know why ... hope you can help me
@miliswatekana37323 жыл бұрын
I have followed the above instructions, 1.worked well at first, now does copy formula when I add the row but required me to double click on the cell to update, how do I correct this? 2. Once this was done I protected the sheet, now the auto copy is gone. Pls. Help
@TheLevantos-go5mo Жыл бұрын
How to I get my VLookup formula to copy down without changing the array in the formula. Thanks
@MrXL Жыл бұрын
Change =VLOOKUP(A2,X10:Z99,3,False) To =VLOOKUP(A2,$X$10:$Z$99,3,False) The dollar sign locks the next part of the reference. Another option: select the lookup table. Click in the Name Box (to left of Formula Bar). Type a one-word name and press Enter. If you named X10:Z99 as Reps then use =VLOOKUP(A2,Reps,3,0) The names range of Reps will not move as you copy down. Also…zero is a shorter way of writing False.
@johnborg60054 жыл бұрын
Thanks Mr Excel. I enjoyed that :)
@MrXL4 жыл бұрын
Thanks for watching John! And thanks for being a subscriber!
@CarlosArruda7710 ай бұрын
Hmm, on my table all of the sudden excel is now changing a simple formula from cell 1 - cell 2 to cell 1 + cell 2! It's frustrating to say the least!!!! Can't seem to find a fix.
@MrXL10 ай бұрын
Very strange. Is it a Ctrl+T table or a regular range? How many rows of cell1 - cell2 are in the column before it switches to cell1 + cell2?
@CarlosArruda7710 ай бұрын
@@MrXL it is a ctrl+T table and when i tab at the end collumn it adds a nother line with the previous cell formula but it changes it from =IF(C28=0,"-",B28+C28) to =IF(C28=0,"-",B28-C28), being that this formula is now on row 29.
@anosromanee-conti7960 Жыл бұрын
how do i insert a row in the middle of a table that has formula applied? i cant just left click the row and click insert cause
@MrXL Жыл бұрын
I just did a quick test, and it works for me, but only after I type in the non-formula cells. Take a look at my test here: kzbin.info/www/bejne/foTQZ3eLnpdqrq8
@jayfran19592 жыл бұрын
I cannot add a row, I am not allowed. it says "Microsoft excel can't insert new cells because it would push non-empty cells off the end of the worksheet. these cells might appear empty but have blank values, some formatting, or a formula. Delete enough rows or columns to make room for what you want to insert and try again."
@MrXL2 жыл бұрын
Go to the end of your data. Delete 100 blank rows. Now you can insert 100 new rows. The problem is that one cell in row 1048576 has a space or has been formatted. It is hard to find which cell. This happened accidentally. Here is a video that explains more: kzbin.info/www/bejne/pWq3Yn2JjJufa8k
@jennaaya48402 жыл бұрын
What if you have data linked between two grouped sheets? I want to insert a row in the first sheet. Because they are grouped, a row is automatically inserted into the second sheet, but the links are not automatically added in. It's also an existing formatted and sorted excel, so I would prefer not to use a table if possible.
@MrXL2 жыл бұрын
My only thought would be to use VBA to insert a row in both sheets and set up the links. Are they perfectly in sync? If you insert row 100 on sheet1 will it always be row 100 on Sheet2? I could see doing a video for this, but you would have to know where to insert the row on the second sheet.
@kimmacpherson8952 Жыл бұрын
Hi Mr Excel, I am trying to cost out recipes and every time I update my pricelist on another worksheet by adding a new row to add a new ingredients it is messing up all of the links to my recipe worksheet. How do you add rows and get the linked formula to move with the row when it moves down instead of staying on the new row?
@MrXL Жыл бұрын
Are they two different Excel files? Or two worksheets in the same workbook? For two different files, try opening both workbooks before adding rows. If that is not practical, then using VLOOKUP formulas to get the files from the closed workbook might be better. Tell me a little more and I can make a video about this.
@princesathyaseelan.g1871 Жыл бұрын
hi mr.excell..... how can i apply a formula for entire column . i want the value enter in new row then the value want to change automatically in gsheet . please clarify my doubts .thank you
@MrXL Жыл бұрын
Easy in Excel. I know nothing about G Sheets. There must be channels who create videos about other products. Ask them.
@kelvincarrion8098 Жыл бұрын
I try to do this, but don't know why it won't work. I add a row and the formula isn't copied.
@MrXL Жыл бұрын
First: you have to have three identical formulas in three consecutive cells. If the column contains inconsistent formulas (such as a few cells with a +50 added to the end of the formula, it won’t work. Second: it is possible to turn the feature off in Excel options (but I am not sure where right now). Third: it is also possible that if you used undo to prevent the formula copy in the past that Excel toggled it off.
@bethbyers1119 Жыл бұрын
how do you fix it if it is broken and will not automatically add the formula when adding a new row in a formatted table?
@MrXL Жыл бұрын
Is it broken in all worksheets on the computer or just one particular spreadsheet? If it is all worksheets then it is turned off in File, Options. But if it is turned off in one worksheet (due to an untimely Ctrl+Z)…I will have to research that tomorrow morning.
@MrXL Жыл бұрын
Go to File, Options. Along the left, choose Advanced. The 6th checkbox should be "Extend Data Range Formats and Formulas". Make sure this is selected.
@islamicinstitute25402 жыл бұрын
hi i am from bangladesh, please help . i need to add new row without adding previews cell formula in google sheet
@MrXL2 жыл бұрын
I am so sorry to hear that you are using Google Sheets. I checked to see if they have the same setting as Excel. But they do not.
@JimFrye14 жыл бұрын
Great teacher. In Options my option is check but like Rob, my formulas are not coming down. hmmm
@MrXL4 жыл бұрын
Jim Frye it has to be three rows or more. All formulas have to be the same. I am trying to think of what else it might be.
@JimFrye14 жыл бұрын
@@MrXL Thanks. I figured it out. I needed to highlight the full table then Insert>Table. For those with the same issue instructions can be found at www.extendoffice.com/documents/excel/3855-excel-insert-row-copy-formula.html
@michaeljohnston97132 жыл бұрын
Is there a file I can upload to show that my table doesn't automatically carry the same formula down to a new row when entered?
@michaeljohnston97132 жыл бұрын
Is there a way I can upload a file?*
@digitaldebasish18652 жыл бұрын
how to make one cell number constant for calculation
@MrXL2 жыл бұрын
Here is an example. You have a 1000 rows of invoice amounts in G3:G1002. You need a formula that multiplies all of those by a constant commission rate that can change each month. Enter the current commission rate in J1. The formula in H3 is =G3*$J$1 When you select H3 and double-click the fill handle, Excel will copy the formula down to J1002. The first five formulas will be: =G3*$J$1 =G4*$J$1 =G5*$J$1 =G6*$J$1 =G7*$J$1 In this example, the reference to G3 is called a relative reference and it will change from G3 to G4 to G5 and so on as it is copied down. But the reference to $J$1 is constant. You can copy this formula down, right, left, up and it will always point to J1. The $ before the J locks the J portion. The $ before the 1 locks the 1 portion.
@ImKurtzy3 жыл бұрын
hey what if I want the same formula but slightly different for each row how would i do that? basically i have a webfetch for a crypto coin but i dont want to copy the url every time i want it to copy all the URL but change the name in the specific location in the url depending on which coin i chose... if you need more info please let me know
@MrXL3 жыл бұрын
Here is an example: Let's say you choose a coin and place the answer in cell B4. You might use a formula such as ="www.MyCoinSite.com?C="&B4&"?Q=History?D=30" Rather than putting the entire URL in the formula, I usually put the part of the URL that goes before the coin in Z1, the part that goes after the coin in Z2. Then the formula is =Z1&B4&Z2
@msc.str.engineermohammedad42692 жыл бұрын
Hi sir, How can I copy only the formulas in the cells of a row to specific scattered rows, such like 1000 rows by a professional method? thanks alot
@@MrXL they are starting from row 4 and repeated each 5 rows, so their numbers are row 4, 9, 14, 19, ... till row 3000 for example
@phu_tv34093 жыл бұрын
Thanks, Mr Excel. But could you please instruct me how to make this function in Google sheet?
@MrXL3 жыл бұрын
Google Sheet? No idea. Only Excel used here.
@mordechaischon78394 жыл бұрын
I’m trying to have a formula on spilled data automatically fill as the spilled data increases... I can’t use a table like you show here because the first column is spilled data... any ideas?
@MrXL4 жыл бұрын
Yes. Let's say that you have a formula in A2 that spills. Perhaps =SORT(RANDARRAY(10,1)). Then you have a formula in B2 that is =A2^2+5. Change the formula in B2 to point to A2#. The Hashtag is a new operator that means "include the entire array found in A2". Your new formula would be =A2#^2+5. Later, if you change the formula in A2 to use RANDARRAY(50,1), the formula in B2 will automatically grow.
@hughlenzer88792 жыл бұрын
Great videos - I have Office 365. I have Extend Data Range and Formulas checked, but it does not copy formulas. Any thoughts?
@mikeolgren73595 ай бұрын
You may have sorted this out by now... It is very easy to "break" the auto-copy formulas functionality in a table or set of data. I broke mine just by creating a huge (50x50) table with formulas for many of the columns, created during different iterations. To fix, I wrote down the formula for the top row, deleted that column below the header, re-entered the formula for the top row (not counting the header), and it auto-populated. I expect my xls will eventually have 2000 rows, so this was 1) great to catch (learn) early, and 2) a huge time-saver.
@abdulmusawer6263 Жыл бұрын
How i freez one row in mid like after 10 row 11 row i dont know how to freez 11 row in excell...please help..me
@MrXL Жыл бұрын
The only way to freeze row 11 is this: Carefully use the vertical scroll bar so that rows 1 to 10 are scrolled off the top of the screen. Row 11 will be the first row visible. Select cell A12. View, Freeze Panes, Freeze Panes This will keep row 11 at the top and allow you to scroll other rows below. You will never see rows 1-10 again, although you can use F5 key to Go To A1 and use the arrow keys while looking in the formula bar. If your goal is to keep row 11 in the middle while other rows appear above and below… then No, Excel does not do that.
@susannem63023 жыл бұрын
Hi Mr Excel, I just found your channel and think it is great. I currently have a problem with the function which does excatly what you show but the formula is only correct, if I manually adjust the formula. It does not matter whether I use a difficult or easy formula. Do you have any idea how I could correct that? For example: 1 2 -> Formula: A1+1 3 -> Formula: A2+1 4 -> Formula: A3+1 4 -> Formula: A3+1 (this will be corrected to 5, as soon as I manually correct the formula with the Excel function) 5 -> Formula: A4+1 (this will be corrected to 6, as soon as I manually correct the formula with the Excel function)
@MrXL3 жыл бұрын
If you want a formula to *always* point to the cell above, enter 1 in A1. Use this formula in A2 and copy down: =OFFSET(A2,-1,0)+1 As you insert new rows, the formula will always point to the cell above. (Note that OFFSET is volatile and will slow down your workbook if you have many thousands of formula)
@susannem63023 жыл бұрын
@@MrXL Super, thanks, that worked perfectly
@jeromebosch58192 жыл бұрын
@@MrXL Thank you so much, this was exactly what I needed as well!!
@levigilcona46949 ай бұрын
Not working on mine. Office 365 2021
@MrXL9 ай бұрын
You have to have three or more cells with the same formula. Let’s say that you had 10 rows with the same formula but you edited the 8th formula to handle something special that month (a change to the bonus plan due to flooding). That single formula anomaly will prevent the formula from extending.
@GokulaKrishnan-xb3yx Жыл бұрын
Man not working for please help me anybody knows
@MrXL Жыл бұрын
Just below the video, there is a link for About. On that section, you will find my e-mail address. Send me a copy of the workbook with the confidential information removed and I can help.
@mosharrofhossain-nx6nw Жыл бұрын
I want this in Google sheets, is it possible?
@MrXL Жыл бұрын
I have no experience with Sheets. I know David Benaim covers both Excel and Sheets on his channel: m.youtube.com/@learnspreadsheets
@mosharrofhossain-nx6nw Жыл бұрын
@@MrXL Thank u for immediate reply.
@omprakashthakur45152 жыл бұрын
How to install row sort data after ends Frist Name
@MrXL2 жыл бұрын
Microsoft has provided a way to automatically sort editing data. But this is a recent improvement. You might have to upgrade to Office 2021 or to Microsoft 365 in order to use the new SORT function. Here is the video: kzbin.info/www/bejne/d4WtmHqNo7hqgck
@Mrdongare4 жыл бұрын
Yours VIDEOS are too good but Why SAMPLE FILE ARE NOT PROVIDED WITH YOURS VIDEOS
@MrXL4 жыл бұрын
Here is why... I create every sample file in less than five minutes. There is nothing special about the sample data. Open Excel to a blank workbook and throw some data in there. It is good practice.
@dbburton64322 жыл бұрын
now how to do this in google sheets
@orchidcolonel26022 жыл бұрын
Google sheets? Given that they offer about 20% of the features of Excel, I would not be hopeful that they support this.
@ura93903 жыл бұрын
this is useless i dont want to stick something in the middle, of course we want to keep adding rows BELOW