To unhide the columns, return to the Format drop-down menu and readjust the column width. To unhide columns, highlight the area where the columns are missing, return to the Format drop-down, and readjust the column width. Follow these same steps to hide or show rows by selecting Row Height in the Format drop-down menu.
WindowsOnline
If you have a worksheet with data in columns that you need to rotate to rearrange it in rows, use the Transpose feature. With it, you can quickly switch data from columns to rows, or vice versa.
For example, if your data looks like this, with Sales Regions in the column headings and and Quarters along the left side:
The Transpose feature will rearrange the table such that the Quarters are showing in the column headings and the Sales Regions can be seen on the left, like this:
Note: If your data is in an Excel table, the Transpose feature won’t be available. You can convert the table to a range first, or you can use the TRANSPOSE function to rotate the rows and columns.
Here’s how to do it:
- Select the range of data you want to rearrange, including any row or column labels, and press Ctrl+C.Note: Ensure that you copy the data to do this, since using the Cut command or Ctrl+X won’t work.
- Choose a new location in the worksheet where you want to paste the transposed table, ensuring that there is plenty of room to paste your data. The new table that you paste there will entirely overwrite any data / formatting that’s already there.
Right-click over the top-left cell of where you want to paste the transposed table, then choose Transpose . - After rotating the data successfully, you can delete the original table and the data in the new table will remain intact.
Tips for transposing your data
- If your data includes formulas, Excel automatically updates them to match the new placement. Verify these formulas use absolute references—if they don’t, you can switch between relative, absolute, and mixed references before you rotate the data.
- If you want to rotate your data frequently to view it from different angles, consider creating a PivotTable so that you can quickly pivot your data by dragging fields from the Rows area to the Columns area (or vice versa) in the PivotTable Field List.
You can paste data as transposed data within your workbook. Transpose reorients the content of copied cells when pasting. Data in rows is pasted into columns and vice versa.
Here's how you can transpose cell content:
- Copy the cell range.
- Select the empty cells where you want to paste the transposed data.
- On the Home tab, click the Paste icon, and select Paste Transpose.
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
Use the Rows property or the Columns property to work with entire rows or columns. These properties return a Range object that represents a range of cells. In the following example,
Rows(1)
returns row one on Sheet1. The Bold property of the Font object for the range is then set to True.The following table illustrates some row and column references using the Rows and Columns properties.
Reference | Meaning |
---|---|
Rows(1) | Row one |
Rows | All the rows on the worksheet |
Columns(1) | Column one |
Columns('A') | Column one |
Columns | All the columns on the worksheet |
To work with several rows or columns at the same time, create an object variable and use the Union method, combining multiple calls to the Rows or Columns property. The following example changes the format of rows one, three, and five on worksheet one in the active workbook to bold.
Sample code provided by: Dennis Wallentin, VSTO & .NET & ExcelThis example deletes the empty rows from a selected range.
This example deletes the empty columns from a selected range.
About the Contributor
Dennis Wallentin is the author of VSTO & .NET & Excel, a blog that focuses on .NET Framework solutions for Excel and Excel Services. Dennis has been developing Excel solutions for over 20 years and is also the coauthor of 'Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA and .NET (2nd Edition).'
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.