Note: Since our original data has A1 as empty, you would need to manually delete the 0 in G1. If you make any changes in the original data set, the transposed data would automatically update. This will give you a linked data that has been transposed.
Now open the Find and Replace dialog box again and replace =.Copy paste this newly created transposed data to the one from which it is created.In the Paste Special dialog box, select Transpose and click OK.In this example, I am pasting it in cell G7. Select a new location, right-click and select paste special.Right-click and copy the data set (or use Control + C).This will replace the equal to from the formula and you will have followed by the cell reference in each cell. You can use any such unique set of characters). In Replace (note that I am using as it’s a unique combination of characters that is unlikely to be a part of your data.In the Find & Replace dialog box, use the following:.This will open the Find & Replace dialog box.
With this new copied data selected, Press Control + H (or go to Home –> Editing –> Find & Select –> Replace).This will give you the same data set, but here the cells are linked to the original data set (for example G1 is linked to A1, and G2 is linked to A2, and so on). In the Paste Special dialog box, click on the Paste Link button.Copy it (Control + C, or right-click and select copy).Here are the steps to transpose the data but keep the links intact: Suppose you have a dataset as shown below: Here is a cool trick you can use to transpose the data and still have it linked with the original cells. This means that if your original data changes and you want the transposed data to be updated as well, then you need to use Paste Special again to transpose it. Using Paste Special alone gives you static data. Transpose Data using Paste Special and Find & Replace If you want these transposed cells to be linked to the original cells, you can combine the power of Find & Replace with Paste Special. Note that the copied data is static, and if you make any changes in the original data set, those changes would not be reflected in the transposed data. If you only want to copy the value, select ‘value’ in the paste special dialog box. The steps shown above copies the value, the formula (if any), as well as the format. Below is a demo showing the entire process. This would instantly copy and paste the data but in such a way that it has been transposed. In the paste special dialogue box, check the transpose option in the bottom right.In this example, I want to copy in G1:K5, so right-click on cell G1 and select paste special. Now you can paste the transposed data in a new location.Copy the dataset (Control + C) or right-click and select copy.Select the data set (in this case A1:E5).Now for some reason, if you need to transpose this data, here is how you can do this using paste special: This data has the regions in a column and quarters in a row. Suppose you have a dataset as shown below:
Paste Special can do a lot of amazing things, and one such thing is to transpose data in Excel.
0 kommentar(er)
