1. Copying Vertically And Pasting Horizontally In Excel Formula
2. Copying Vertically And Pasting Horizontally In Excel Worksheet

Hello, In worksheet 1 I have information in range B2:AQ2. This info needs to go into another worksheet in a range B953:B994. B953 therefore equals B2 but how can I paste the formula down so the next cell says C2 rather than the default B3? In other words I want to copy relative cell references horizontally rather than vertically.

Re: Help Pls!Simple Copy Paste offset value Horizontally thanks, but value from A1-A6 changes, and i want to records of A1-A6 value, first at H1: H6, when A1-A6 changes new value, will be pasted on I1:I6 etc( keeping old value at H1:H6), up to AAA1:AAA6 or more. Copy vertical data and paste it horizontally in Excel. Excel Details: Step 1. Copy the vertical data. In this case months of the year can be used as a simple example. Just click and drag to select the text, and then Control + C to copy it. How to copy horizontal cells to vertical › Verified 7 days ago. Apr 10, 2012 HI, I want to copy some data from a web page which is in verticle mode but i need to paste it in excel in one row (horizontally). Can i collect all the data like this from a same page in one time. Hello i am newbie to excel and i need some help in this issue i have copied A1 cell from sheet 1 into A1 cell from sheet 2 what i need to do is to drag A1 of sheet 2 horizontally (B1, C1, D1) and get the vertical values (rows) of sheet 1: A2, A3, A4, etc.

Q. I have an amortization schedule arranged vertically in Excel on Sheet 1 that I want to reference horizontally in my income statement on Sheet 2. Is there a way to write and copy/paste a single formula that will accomplish this task?

A. Yes, it is possible to reference vertical data using a single formula that can be copied horizontally. The trick is to use the INDEX and COLUMN functions together, as follows. In the screenshot below, the formula =INDEX($A$10:$F$23,COLUMN(B1),4) has been entered into cell A26 and then copied across to repeat the schedule's vertical column of Interest horizontally on row 26.

Excel's INDEX function allows users to reference values in a range of data (or array of data) by their column and row number position within that range. As a simple example, the formula =INDEX(A1:F10, 4,4) would return the value in the fourth row of the fourth column in that specified data range. Excel's COLUMN function returns the number of the column in a referenced cell. For example, the formula =COLUMN(A1) returns the value 1 because cell A1 is located in the first column. The beauty of this formula is that when copied to the right, the value returned increments by one each time, so that the COLUMN function essentially becomes a counting tool.

Therefore, in our INDEX formula, instead of typing the numeral 2 to reference row 2, we can instead type the COLUMN function =COLUMN(B1), which returns the numeral 2. As this formula is copied to the right, the COLUMN function references cells C1, D1, E1, and so on, which equates to columns 3, 4, 5, and so on, respectively. The result is that each time the formula is copied to the right, the formula references the next row down on the amortization schedule. You can view a video on this technique below and download the Excel worksheet here.

Copying Vertically And Pasting Horizontally In Excel Formula

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

How to copy and paste rows or columns in reverse order in Excel?

Sometimes, you may need to copy and paste a list of column or row in reverse order vertically or horizontally as below screenshot shown. How could you deal with this job quickly and easily in Excel?

Copy and paste a list of column or row in reverse order with formula

Copy and paste a list of column in reverse order vertically

If you want to reverse a list of column cells, you can apply the below formula:

Enter or copy the following formula into a blank cell where you want to reverse the column order:

Note: In the above formula, A1is the first cell in the column, and A15is the last cell in the column.

Then, drag the fill handle down to the cells to extract the cell values in reverse order as below screenshot shown:

Copy and paste a list of row in reverse order horizontally

To copy and paste a list of row in reverse order, please use the following formula:

Enter or copy this formula into a blank cell:

Note: In the above formula, A1 is the first cell in the row, and 1:1 is the row number that your data located. If your data in row 10, you should change it to 10:10.

And then, drag the fill handle right to the cells that you want to apply this formula until all values extracted, and you will get all the values have been reversed horizontally, see screenshot:

Copy and paste a range of columns or rows in reverse order with VBA code

If you need to copy and paste a range of columns or rows in reverse order, here, I will introduce some VBA codes for solving it quickly and easily. Please do with the following steps:

Copy and paste a list of column in reverse order vertically

1. First, you should copy and paste your data into a new place, and then, hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Copy and paste a range of cells in reverse order vertically

3. Then, press F5 key to run this code, and a prompt box is popped out to remind selecting the data range that you want to reverse vertically, see screenshot:

4. And then, click OK button, and the data range has been reversed vertically as below screenshots shown:

Copy and paste a range of cells in reverse order horizontally

To reverse the data range in horizontal order, please apply the below VBA code:

VBA code: Copy and paste a range of cells in reverse order horizontally

After running this code, you will get the following screenshots as you need:

Copy and paste a range of columns or rows in reverse order with an awesome feature

If you are not familiar with the above formulas and VBA codes, here, I will recommend an easy tool-Kutools for Excel, with its Flip Vertical Range and Flip Horizontal Range features, you can reverse the range of cells vertically and horizontally with only one click.

Tips:To apply this Flip Vertical Range and Flip Horizontal Range features, firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

Copy and paste a range of cells in reverse order horizontally

1. Select the range of cells, and then click Kutools > Range > Flip Vertical Range > All (Only flip values), see screenshot:

2. And then, the range of cell values have been reversed vertically at once, see screenshots:

Copy and paste a range of cells in reverse order horizontally

1. Select the range of cells, and then click Kutools > Range > Flip Horizontal Range > All (Only flip values), see screenshot:

2. And then, all cell values in the selection have been reversed horizontally immediately, see screenshots:

More relative copy and paste articles:

• Normally, when you copy the merged cells and paste them into other cells, the merged cells will be pasted directly. But, you want to paste theses merged cells to single cells as following screenshot shown, so that you can deal with the data as you need. This article, I will talk about how to copy and paste merged cells to single cells.

• Supposing, I have a range of data, now, I want to copy the entire rows based on a specific date and then paste them into another sheet. Do you have any good ideas to deal with this job in Excel?

Copying Vertically And Pasting Horizontally In Excel Worksheet

• For a column with lots of duplicates, you may need to get a copy of this list with only the unique values. How can you do to achieve this? In this article, we provide two methods to paste only the unique records from a column list in Excel.

• Supposing, you receive an Excel file from another person which with worksheets are protected by the user, now, you want to copy and paste the raw data to another new workbook. But the data in the protected sheet is not able to be selected and copied because of the user uncheck the Select locked cells and Select unlocked cells options when protecting the worksheet.

• Normally, we can quickly apply the Advanced Filter feature to extract data from the raw data in the same worksheet. But, sometimes, when you try to copy the filtered result to another worksheet, you will get the following warning message. In this case, how could you deal with this task in Excel?

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!