Excel - Insert Row And Excel Formula Automatically Copies - Episode 2349

[@Tony031570]

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.

[@michaelc8826]

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

[@MatthewBowman]

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.

[@wayneedmondson1065]

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!!

[@GeoWolf789]

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-yo7rv6gn1p]

Thanks a ton sir......

[@mattschoular8844]

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.

[@MrXL]

For me, tables are too buggy. But I know others love them!

[@Pezhan]

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!!!!

[@gekalskip]

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?

[@EXCELFACILERAPIDO]

Congrats!!! very good👏👏👏

[@alchrisu]

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!

[@markjohnston2827]

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).

[@chantelleyurechuk9764]

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, ..

[@darrylmorgan]

Really Great Tips..Thank You Mr Excel :)

[@etainpages5964]

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

[@iamlive8895]

How i auto add formatted table as i designed eith formulas when i add the contents in previous sheet ? Is this posssible ?

[@LexiDraws560]

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

[@MrXL]

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?

[@meridithmatthews]

When I insert a new data or copy paste value the formula is ruined why is that?

[@dkodr]

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.

[@drsteele4749]

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.

[@MrXL]

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.

[@mpo3565]

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,

[@arayahomes4308]

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.Fisherman]

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

[@NicBob89]

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...

[@MrXL]

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.

[@najathemnrojh9783]

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

[@miliswatekana3732]

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.

[@johnborg6005]

Thanks Mr Excel. I enjoyed that :)

[@MrXL]

Thanks for watching John! And thanks for being a subscriber!

[@CarlosArruda77]

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.

[@MrXL]

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?

[@CarlosArruda77]

@@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

[@jayfran1959]

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."

[@MrXL]

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

[@jennaaya4840]

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.

[@MrXL]

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.

[@islamicinstitute2540]

hi i am from bangladesh, please help . i need to add new row without adding previews cell formula in google sheet

[@MrXL]

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.

[@JimFrye1]

Great teacher. In Options my option is check but like Rob, my formulas are not coming down. hmmm

[@MrXL]

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.

[@JimFrye1]

@@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

[@michaeljohnston9713]

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?

[@michaeljohnston9713]

Is there a way I can upload a file?*

[@digitaldebasish1865]

how to make one cell number constant for calculation

[@MrXL]

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.

[@ImKurtzy]

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

[@MrXL]

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.engineermohammedad4269]

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]

For the 1000 scattered rows... are they in a contiguous block of rows, such as row 7 through 1006? Or are they truly scattered, for example rows 7, 9, 12, 15, 19, 20, 24, 26, 27, 28, 30, 33, 34, 36, 37, 38, 42, 46, 48, 52, 55, 59, 63, 64, 65, 67, 70, 73, 77, 81, 82, 85, 89, 93, 95, 98, 101, 102, 106, 108, 110, 111, 113, 117, 118, 121, 125, 129, 131, 135, 139, 141, 142, 144, 145, 148, 151, 155, 157, 160, 162, 166, 169, 173, 177, 179, 180, 181, 182, 184, 186, 190, 194, 197, 200, 203, 204, 206, 207, 209, 211, 212, 215, 216, 218, 219, 220, 221, 224, 227, 230, 234, 238, 242, 246, 248, 249, 253, 256, 259, 262, 265, 268, 272, 275, 277, 280, 282, 283, 284, 285, 289, 291, 293, 296, 300, 304, 306, 309, 312, 315, 316, 320, 324, 326, 330, 332, 336, 338, 340, 342, 346, 348, 350, 354, 357, 358, 359, 361, 365, 367, 370, 372, 376, 378, 380, 381, 384, 385, 386, 389, 392, 396, 398, 402, 403, 405, 408, 411, 412, 416, 417, 421, 425, 429, 433, 436, 438, 440, 444, 448, 450, 451, 454, 455, 458, 459, 460, 464, 466, 469, 473, 476, 477, 481, 482, 485, 487, 489, 493, 495, 497, 501, 505, 508, 509, 513, 514, 517, 518, 521, 525, 529, 532, 534, 538, 541, 545, 547, 548, 552, 554, 557, 558, 560, 561, 565, 566, 570, 573, 575, 576, 579, 582, 585, 586, 590, 591, 593, 594, 597, 601, 605, 606, 608, 611, 614, 615, 618, 620, 621, 622, 624, 628, 629, 630, 633, 634, 638, 639, 640, 641, 643, 646, 648, 651, 654, 656, 659, 663, 667, 668, 671, 675, 676, 680, 681, 685, 688, 692, 694, 697, 701, 702, 705, 707, 709, 711, 712, 713, 717, 719, 722, 726, 730, 732, 733, 736, 739, 741, 742, 743, 744, 747, 750, 754, 756, 758, 759, 761, 763, 767, 771, 773, 777, 779, 780, 781, 784, 785, 786, 789, 791, 793, 794, 796, 800, 804, 807, 809, 810, 812, 814, 816, 820, 821, 823, 827, 828, 829, 830, 832, 835, 837, 841, 844, 847, 849, 853, 855, 858, 862, 866, 869, 872, 876, 879, 881, 882, 883, 885, 889, 893, 896, 900, 902, 906, 907, 908, 910, 912, 913, 915, 918, 920, 922, 923, 924, 928, 930, 931, 934, 938, 941, 944, 945, 947, 951, 954, 955, 957, 960, 961, 963, 967, 971, 975, 976, 977, 981, 985, 988, 990, 992, 996, 997, 999, 1000, 1002, 1005, 1006, 1009, 1013, 1014, 1018, 1020, 1021, 1025, 1026, 1029, 1033, 1034, 1037, 1039, 1043, 1045, 1046, 1050, 1052, 1055, 1059, 1062, 1065, 1067, 1071, 1075, 1079, 1083, 1085, 1088, 1089, 1090, 1093, 1095, 1096, 1100, 1102, 1106, 1107, 1109, 1113, 1116, 1119, 1121, 1122, 1126, 1129, 1131, 1133, 1135, 1138, 1139, 1142, 1143, 1144, 1146, 1150, 1152, 1156, 1158, 1162, 1166, 1168, 1169, 1172, 1174, 1177, 1180, 1184, 1188, 1190, 1194, 1196, 1198, 1202, 1203, 1204, 1206, 1209, 1210, 1214, 1216, 1220, 1221, 1225, 1229, 1232, 1234, 1237, 1241, 1243, 1247, 1251, 1255, 1256, 1260, 1263, 1266, 1267, 1271, 1272, 1273, 1276, 1278, 1281, 1283, 1286, 1288, 1292, 1293, 1295, 1297, 1298, 1300, 1302, 1303, 1304, 1306, 1309, 1311, 1315, 1316, 1318, 1319, 1322, 1324, 1325, 1326, 1327, 1331, 1334, 1335, 1339, 1341, 1342, 1346, 1350, 1353, 1356, 1360, 1363, 1365, 1366, 1368, 1371, 1375, 1377, 1380, 1384, 1387, 1388, 1392, 1394, 1396, 1400, 1403, 1405, 1407, 1410, 1412, 1416, 1418, 1419, 1421, 1422, 1424, 1428, 1431, 1433, 1434, 1438, 1439, 1442, 1446, 1450, 1452, 1454, 1456, 1460, 1464, 1468, 1469, 1473, 1476, 1477, 1479, 1480, 1484, 1485, 1488, 1492, 1493, 1496, 1498, 1502, 1504, 1505, 1509, 1512, 1514, 1518, 1519, 1520, 1521, 1525, 1528, 1529, 1532, 1534, 1538, 1542, 1546, 1547, 1550, 1553, 1555, 1556, 1557, 1559, 1562, 1564, 1568, 1569, 1570, 1572, 1573, 1577, 1579, 1583, 1584, 1586, 1590, 1593, 1594, 1598, 1599, 1602, 1605, 1607, 1609, 1610, 1611, 1612, 1613, 1616, 1617, 1620, 1621, 1622, 1626, 1629, 1630, 1632, 1633, 1636, 1640, 1644, 1646, 1650, 1652, 1655, 1656, 1658, 1661, 1664, 1668, 1670, 1671, 1672, 1673, 1675, 1678, 1679, 1680, 1683, 1685, 1686, 1687, 1690, 1691, 1694, 1696, 1697, 1699, 1700, 1704, 1705, 1709, 1711, 1713, 1716, 1719, 1721, 1724, 1727, 1731, 1735, 1736, 1738, 1741, 1745, 1749, 1752, 1756, 1757, 1758, 1759, 1763, 1764, 1768, 1771, 1775, 1779, 1782, 1784, 1788, 1792, 1793, 1794, 1795, 1799, 1803, 1805, 1808, 1809, 1812, 1814, 1816, 1820, 1821, 1822, 1825, 1829, 1833, 1836, 1840, 1841, 1842, 1843, 1846, 1848, 1851, 1853, 1856, 1858, 1859, 1861, 1863, 1866, 1868, 1870, 1871, 1873, 1876, 1879, 1883, 1887, 1889, 1890, 1893, 1896, 1898, 1900, 1904, 1907, 1908, 1909, 1912, 1916, 1920, 1921, 1922, 1923, 1924, 1927, 1928, 1932, 1934, 1937, 1939, 1942, 1946, 1947, 1950, 1952, 1954, 1956, 1960, 1963, 1967, 1968, 1970, 1971, 1973, 1974, 1975, 1978, 1980, 1981, 1985, 1987, 1990, 1994, 1995, 1999, 2001, 2002, 2003, 2006, 2009, 2011, 2015, 2017, 2020, 2022, 2024, 2026, 2029, 2033, 2036, 2037, 2040, 2042, 2046, 2050, 2052, 2056, 2058, 2060, 2062, 2064, 2067, 2068, 2072, 2075, 2077, 2078, 2079, 2081, 2083, 2084, 2088, 2091, 2094, 2098, 2101, 2103, 2105, 2109, 2111, 2114, 2115, 2119, 2120, 2121, 2122, 2126, 2127, 2130, 2132, 2133, 2137, 2139, 2143, 2144, 2145, 2148, 2151, 2155, 2157, 2159, 2163, 2166, 2168, 2172, 2175, 2178, 2179, 2183, 2184, 2185, 2186, 2189, 2192, 2195, 2199, 2203, 2205, 2209, 2213, 2214, 2216, 2217, 2219, 2220, 2223, 2226, 2227, 2230, 2232, 2233, 2235, 2236, 2237, 2239, 2242, 2246, 2250, 2253, 2257, 2261, 2265, 2269, 2273, 2277, 2281, 2285, 2286, 2290, 2293, 2294, 2295, 2299, 2302, 2305, 2308, 2309, 2312, 2316, 2318, 2319, 2321, 2323, 2327, 2328, 2331, 2334, 2336, 2340, 2344, 2347, 2348, 2350, 2351, 2354, 2355, 2359, 2362, 2365, 2366, 2370, 2374, 2377, 2380, 2384, 2387, 2390, 2392, 2396, 2400, 2402, 2406, 2407, 2411, 2413, 2417, 2421, 2423, 2425, 2428, 2430, 2434, 2438, 2440, 2441, 2443, 2447, 2449, 2451, 2455, 2456, 2460, 2463, 2466, 2470, 2472, 2475, 2478, 2480, 2484, 2487, 2490, 2494, 2495, 2496, 2500, 2504, 2507, 2511, 2514

[@msc.str.engineermohammedad4269]

@@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_tv3409]

Thanks, Mr Excel. But could you please instruct me how to make this function in Google sheet?

[@MrXL]

Google Sheet? No idea. Only Excel used here.

[@mordechaischon7839]

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?

[@MrXL]

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.

[@hughlenzer8879]

Great videos - I have Office 365. I have Extend Data Range and Formulas checked, but it does not copy formulas. Any thoughts?

[@mikeolgren7359]

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.

[@susannem6302]

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)

[@MrXL]

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)

[@susannem6302]

@@MrXL Super, thanks, that worked perfectly

[@jeromebosch5819]

@@MrXL Thank you so much, this was exactly what I needed as well!!

[@levigilcona4694]

Not working on mine. Office 365 2021

[@MrXL]

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.

[@omprakashthakur4515]

How to install row sort data after ends Frist Name

[@MrXL]

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

[@Mrdongare]

Yours VIDEOS are too good but Why SAMPLE FILE ARE NOT PROVIDED WITH YOURS VIDEOS

[@MrXL]

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.

[@dbburton6432]

now how to do this in google sheets

[@orchidcolonel2602]

Google sheets? Given that they offer about 20% of the features of Excel, I would not be hopeful that they support this.

[@ura9390]

this is useless i dont want to stick something in the middle, of course we want to keep adding rows BELOW