How can i fill a certain number of rows in excel?
- Excel Autofill Formula
- How Do I Auto Populate Data In Excel Based On Another Cell?
- Some Data Will Auto Populate In Excel 2016
Lets say 5 and it fills 5 rows with numbers, like
1 2 3 4 5
(downwards 1 number each cell). If i change it to 12 it should result in 1 2 3 4 5 6 7 8 9 10 11 12
.I could do it by dragging the formula
=A1+1
down, but then i have to adjust the last number manually (it will vary from ~50 to ~900)StefanStefan
3 Answers
If the need for automation is not so great then you can use built-in Excel functionality as outlined in my answer. If you need a more automated solution then you will have to record a Macro to accomplish it.
Using the
Fill
option from the ribbon/toolbar, select Series
and then adjust your parameters accordingly, filling in a Column from 1 to n. Screeshots below:This brings up a dialog box which you complete as per your requirements.
Excel Autofill Formula
Matt WellerMatt Weller1,60822 gold badges1616 silver badges2727 bronze badges
Oblivion let me do that one again. Ok. This solution will require you to paste a formula down your entire possible range (ex. in your case to row 900). Assuming:- Your first value is in A1- Your step size is 1- The number of rows you want to change is in D1,
You should be able to copy the following formula into A2 and then drag it through your desired range:
=IF(ROW()<=$D$1,A1+1,')
The only caveats are: - That you can't enter values manually into Column A in that entire range- There are 900 formulas hanging out and recalculating on your sheet
M.BanerjeeM.Banerjee
Click in any cell (let's assume cell
BaCaRoZzoC5
). Type =ROW()-ROW(C$5)+1
and press enter then drag down the arrow at the bottom right corner to create numbering as far as you want. One nice advantage is that it will not break if you add rows before or within, or delete rows before or within. At any time you can go to the last cell and drag more to create more numbers and it will pick up right from the last number.6,19266 gold badges3838 silver badges6565 bronze badges
BillBill
Not the answer you're looking for? Browse other questions tagged excelrowsfill or ask your own question.
Given: Data is entered in columns A to H. Formulas are entered in columns I to S.
The process: If data is entered in columns A to H, the formulas some how are automatically copied to cells I thru S when the data is entered correctly in the preceding cells A to H.
What causes the cells to automatically populate the destinations cells (I thru S)?
More specifically, There are 2 formula cells that refuse to populate. Level 2 support said, the Workbook must be corrupted (as told to me by a level 1 support person who does not speak American English. Needless I am confused.
The next attempt to correct this issue was to create a new workbook and only enter the headers and 1 row of data. The data and formulas look OK. However, when I attempted to enter data into the next row, none of the cell containing formulas populated after data was entered.
The recommendation is to do something which when told to me I could not understand. I currently have close to 500 rows of data in the workbook, I don't want to reenter the data.
How do I correct this new workbook to auto-populate cells.
A world of ice and fire warband guide. ***Post moved by moderator to the appropriate category***
I've created a table with multiple columns in Excel, and I'm trying to let it autocomplete some columns based on another one: There's an 'Item name' column and a 'Item type' one, and I want to make excel autofill the Item Type field on every row based on the entry in the Item name field on the same row, is there a way to make Excel handle this by itself based on previous entries?
EDIT: Example for clarity:
If I insert 'Pizza' in Item Name and 'Food' in Item type, when I enter 'Pizza' again in another row, Excel auto-fills the same row 'Item type' field with 'Food'
If I insert 'Pizza' in Item Name and 'Food' in Item type, when I enter 'Pizza' again in another row, Excel auto-fills the same row 'Item type' field with 'Food'
Brian Tompsett - 汤莱恩
4,3821414 gold badges4040 silver badges107107 bronze badges
xKylesxxKylesx
1 Answer
Based on your edit you want the vlookup function.
VLOOKUP( value, table, index_number, [not_exact_match] )
If index_number is less than 1, the VLOOKUP function will return
for the not_exact_match parameter and no exact match is found, then the VLOOKUP function will return #N/A.
But first you'll need to make sure that these associations are already present. You need a table like this. Assume that 'object' is a1 and 'type' is b1:
Now for your table that you're entering the values, assume that 'name' is d1 and 'type' is e1:
The '$' will prevent the relevant cells from changing if you use autofill to complete the column. Ntfs reader for mac.
If this is a set list that is relatively short you can manually enter it into the autfill options.
Microsoft gives you all the various ways to do this. https://support.office.microsoft.com/en-us/article/Fill-data-automatically-in-worksheet-cells-74e31bdd-d993-45da-aa82-35a236c5b5db?CorrelationId=99c7dec0-4c5c-4988-b148-6d3b7c4dd2e5&ui=en-US&rs=en-US&ad=US
Also, creating a pivot table may be what you want to do. Simply highlight your main table and go to the insert table and click pivot table. Move the headers into the appropriate box.
The last option may be your best bet if it involces a ton of values. lookup/match functions will give you a more robust formula that is more versatile.
If you can give a sample set of data and what you want it to look like I can give you the formula.
Oedhel SetrenOedhel Setren
How Do I Auto Populate Data In Excel Based On Another Cell?
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.