I have two sheets and in Sheet 2 Column A I want to duplicate all of the values from Sheet 1 column A so that when I add a new value to Sheet1 Column A it displays in sheet 2. There are roughly 30000 rows so I don't want to have to drag the fill handle each time.

I thought I could do this by defining a name giving it a Range of Sheet1!A:A but I can't figure out how to get this to fill the entire column in Sheet2.

Hope this makes sense.


If you make Sheet1!A a table you can always simply create a pivot table for Sheet2 and you're done! The only caveat is that the data in Sheet2 will have to be in column B

Here's a quick snapshot of what I did to get this to work:


  1. Column1(A) is the data
  2. Column2(B) is a formula (=ROW([@Column1])-1); that keeps the current order in tact.



  1. Insert pivot table and add table both columns from Table1 in Sheet1
  2. Order the Row Labels like so: Column2, Column1 Row Labels
  3. Change Field Settings in Row Labels > Column2 to 'Show item labels in tabular form' Field Settings
  4. Hide column A in Sheet2

Lastly, refreshing the data. You can refresh the data by using the shortcut key Alt+F5 or you can set it to automatically refresh when opening the file: PivotTable Tools > Options > Options > Data > Refresh data when opening the file

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.