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. Transpose Data using Excel TRANSPOSE Function.Transpose Data using Paste Special and Find & Replace.

Tools menu in excel 2016 mac