Now when you return to your spreadsheet, the column headings should be letters (A, B, C, D) instead of numbers (1, 2, 3, 4). Then uncheck the option called ' R1C1 reference style' and click on the OK button. When the Excel Options window appears, click on the Formulas option on the left. To change the column headings to letters, select the File tab in the toolbar at the top of the screen and then click on Options at the bottom of the menu. Step 3: Open the Paste Special dialog, as explained above, and click Paste Link in the lower left-hand corner. Step 2: Select an empty cell in the same or another sheet. Step 1: Copy the rows you want to convert to columns or columns to be changed to rows. When the column headings are numeric values, R1C1 reference style is being displayed in the spreadsheet. Follow these steps to transpose the table and link it with the original data. In the example below, the column headings are numbered 1, 2, 3, 4 instead of the traditional A, B, C, D values that you normally see in Excel. If your spreadsheet shows the columns as numbers, you can change the headings back to letters with a few easy steps. How do I change the column headings back to letters such as A, B, C, D?Īnswer: Traditionally, column headings are represented by letters such as A, B, C, D. =OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))įill this formula across to column F, and then down to row 3.Īdjust the column sizes as necessary.Question: In Microsoft Excel 2016, my Excel spreadsheet has numbers for both rows and columns. In a new worksheet, type the following data:

This is a similar scenario as that which you experience when you open a worksheet or text file that contains data in a mailing label format. The following example converts every four rows of data in a column to four columns of data in a single row (similar to a database field and record layout). When you use the Microsoft Excel products listed at the bottom of this article, you can use a worksheet formula to covert data that spans multiple rows and columns to a database format (columnar).

Change columns to rows in excel