EXCEL BATCH HORIZONTAL TO VERTICAL METHOD DON T SAY TRANSPOSE .

The image above is sheet1

The following diagram is sheet2

Work in sheet2.

A2 cell input formula.

offset(sheet1!A$3,row(a3) 3-1,)b2 cell formula.

offset(sheet1!b$2,,mod(row(a3),3))c2 cell, fill to the right.

Then select the second line formula to fill down.

The formula is not explained. If you can use it, you can use it. The test passed.

This children's shoes, the next time you take a screenshot, cut off the row number column clearly, and it is best to make the name of the worksheet clear.

If your distinguishing column is column A, enter the following formula in cell C2 (material A in January) and copy it to other cells, assuming the first cell of your target data is C9, please enter =vlookup($a 9,$a:$ak,mod(row(),3)+2+((column()-3))*3,0).

If your original ** is very long, you can modify the mod(row(),3)+2 in the formula part to make it meet the next three values of 1,2,3 respectively

1. Pivot table, how you want to turn how you want, you can calculate it, it's very convenient! Save time.

2. Don't use vlookup, use index, because you ......Months and months are all three cells, and you can make it into multiples of 3. Maybe you'll use the row() and column() functions in it, and you'll be fumbling around!

Just for your reference.

Excel transverse to vertical or longitudinal to horizontal can be completed by using the Excel column and column transpose transformation function, the specific steps are as follows:

Use this month's sales table to demonstrate the row and column transformation operation

1. It is preferred to copy the content after selecting **.

2. Right-click the mouse button at the position you want to paste, and click Paste Special.

3. Check Transpose in the menu, and then click OK.

4. In this way, the horizontal and vertical conversion can be completed.

What if you need to convert your content from landscape to portrait in the office? I'll teach you a trick next!

01 First of all, let's take the ** shown in the following figure as an example to convert it horizontally to vertically.

02 Select the ** content that needs to be converted first.

03 Then right-click or Ctrl+C to copy.

04 Then click on the blank box and right-click.

05 Find "Paste Special" among the options and click "Paste Content Upside Down" in the list next to it.

06 Click OK to complete the conversion.

1. Box selection**Open Excel and select **.

2. Click CopyClick Copy in the upper-left corner.

3. Select the cellSelect the cells that need to be pasted.

4. Click PasteClick Paste in the upper-left corner.

5. Click Paste SpecialA white box pops up, and click Paste Special.

6. Check TransposeIn the pop-up window, select Transpose.

7. Click OKClick OK in the lower-right corner.

8. The operation is completeTurning excel** landscape into portrait operation is complete.

**How can data in landscape quickly become vertical? Copy-paste will do it, just read it if you don't believe it.

Select the content to be transformed, right-click to copy, and then select Transpose when pasting, to turn the content horizontally or vertically into vertical or horizontal.

1. Select the cells that need to change the horizontal font to vertical font, then right-click and click to format the cells. 2. In the settings page that opens, click on the alignment option. 3. In the alignment setting interface, there is a direction setting bar on the right, just click on the text.

4. According to the above steps, you can set the horizontal and vertical of the text. Note: 1. Click to set the cell format.

2. There is a direction setting bar on the right, click on the text.

How to do the operation of excel batch column transfer pin:

Materials required: Excel.

First, open the Excel** file, select the cells that need to be converted from columns to rows, and press Ctrl+C to copy.

2. Swim and click on the blank cell next to it to select the cell.

3. Click "Paste Options" under "Starvation Start Options" and click "Transpose" in the drop-down menu.

Fourth, this can turn the columns into horizontal rows.

I don't think there's any problem with the time of the entry in excel, and then I think the horizontal and vertical data should be reversed, at this time you need to use the horizontal and vertical conversion function of excel, I shared for you the method of adjusting the horizontal and vertical conversion of excel, I hope it will be useful to you.

How Excel adjusts the method of horizontal and vertical conversionHorizontal and vertical conversion step Mingshenyu 2: Select the range of cells from A1 to I6. Right-click - Copy.

Horizontal and vertical conversion step 3: cursor in cell A7 (or new sheet A1), right-click - paste specially.

Step 4: Select Transpose below.

Step 5: Confirm the effect of horizontal and vertical conversion. You can delete the ** area before the conversion.

Use yellow, white and size to do a transposition, and fill the item number and color in the corresponding position.

A9 =Index($a$3:$b$4,Roundup((row(A1)) 5,0),Column(A2)) is copied to the right to B9

c9 =index($c$2:$g$2,1,if(mod(row(a1),5)=0,5,mod(row(a1),5)))

d9 =index($c$3:$g$4,roundup(row(a1)/5,0),if(mod(row(b1),5)=0,5,mod(row(b1),5)))

Then select a9, b9, c9, d9 together, and then copy down to the ...... you know the error occurred

Your actual application should modify the previous area ......If it's not in the same table, just modify the preceding field of the index function to suit your needs, and you're good to go......

This can be changed to: index(sheet1!$a$3:$b$1000……That's it.

Select the entire area from A2 to H4 -- Right-click "Copy" -Select Cell A8 -- Right-click "Paste Special" and check "Transpose".

C2:H4 selected, copy, paste special-transpose.

In daily work, if you encounter the data in Excel** to "change horizontally and vertically", it is really a waste of time to enter them one by one. Recently, I have seen a lot of friends asking if there is a shortcut key for excel horizontal and vertical conversion, how to operate, and today I will bring you the operation method of excel horizontal and vertical conversion, including the shortcut key operation method, interested friends come and take a look.

WPSOFFICE2019 official free personal edition.

WPSOFFICE is an office software that is fully compatible with Microsoft Office format produced by Kingsoft Co., Ltd. WPSOFFICE2019 personal free is a must-have software for office, the software provides powerful word processing functions, the most commonly used text, table dress, presentation and other functions are available, you can process a variety of documents, the size is very small, **fast, installation is also very fast, users in need are welcome**!

Excel horizontal and vertical conversionMethod 1:1. Select the unit to know the lack of potato gridsSelect the desired cells.

2. Click DirectionsClick Directions above.

3. Press the keyboard shortcutUse the built-in keyboard to press the [V] shortcut.

4. The operation is completeExcel text horizontal to vertical shortcut key operation is completed.

Method 2:1. Box selection**Open Excel and select **.

2. Click CopyClick Copy in the upper-left corner.

3. Select the cellSelect the cells that need to be pasted.

4. Click PasteClick Paste in the upper-left corner.

5. Click Paste SpecialA white box pops up, and the clicker clicks [Paste Special].

6. Check TransposeIn the pop-up window, select Transpose.

7. Click OKClick OK in the lower-right corner.

8. The operation is completeTurning excel** landscape into portrait operation is complete.

Summary:

1. Select the cell.

Select the desired cells.

2. Click Directions

Click Directions above.

3. Press the keyboard shortcut

Use the built-in keyboard to press the [V] shortcut.

4. The operation is complete

Excel text horizontal to vertical shortcut key operation is completed.

All of the above is an introduction to the two methods of Excel horizontal and vertical conversion for everyone, you can choose to use it according to your preferences, I hope it will be helpful to you

Excel horizontal to vertical or vertical to horizontal can be completed by using the Excel column and column transpose transformation function, the specific steps are as follows: auspicious fingers

Use this month's sales table to demonstrate the row and column transformation operation

1. It is preferred to copy the content after selecting **.

2. Click the right chamber infiltration button of the mouse at the position to be pasted, and click Paste Selectively.

3. Check Transpose in the menu, and then click OK.

4. In this way, the ridge can be used to complete the horizontal and longitudinal conversion.