Excel worksheets are made up of rows and columns organized into a grid. Rows run horizontally across spreadsheets and are identified by numbers. Columns run vertically and are designated by letters. Cells mark the location where a row and column intersect.
A cell address is made up of a column letter and row number. Cells have addresses like B2, C7, and A10. Cell addresses always remind me of playing Battleship when I was younger!
Knowing how to work with rows and columns will help you build spreadsheets more efficiently. The ability to add or remove rows and columns, adjust their dimensions, and show or hide these elements as needed are important skills for any Excel user.
In this article, we’ll cover everything you’ll need to manipulate rows and columns like a pro… and we’ll cover a couple of shortcuts that will make working with spreadsheets a breeze!
Inserting Rows and Columns
There are several ways to insert rows and columns in an Excel worksheet. Whichever technique you use, new rows are always inserted above the active row. New columns are always inserted to the left of the active column. Let’s look at some examples.
If I want to add a new product below Baked Goods, I need to insert a row above Hot Drinks. Here’s the process.
Step #1 – Select cell A3.
Step #2 – Click the down arrow beside the Insert button in the Home > Cells group and select Insert Sheet Rows.
Alternatively, I could have right-clicked the heading for row 3 and selected Insert from the shortcut menu.
If I want to insert two columns to the left of the Q1 figures, I’d need to select two cells from column B and C. Here’s how it’s done!
Step #1 – Drag to select cells B1:C1.
Step #2 – Click the down arrow beside the Insert button in the Home > Cells group and select Insert Sheet Columns.
The Insert Options Button
After inserting the two columns, your screen will look like this. Notice the Insert Options button. This control will appear whenever the width of the column to the left of the selected cells is different than the width of the column to the right.
If you click the Insert Options button, you’re presented with 3 options. You can adjust the formatting (and width) of the new columns using these settings.
Deleting Rows and Columns
Deleting rows and columns is straightforward. Selecting a cell is the first step to deleting a single row or column, selecting two (or more) cells will result in two (or more) rows or columns being deleted.
Let’s delete the row and columns we added in the last section.
Right-click the heading for row 3 and select Delete from the shortcut menu.
Drag to select the headings for columns B and C then right-click anywhere in the selected area and click Delete from the shortcut menu.
Changing Row Height
You can adjust the height of any row in your worksheet. Once again, you would select a single cell or row heading to adjust the height of one row. You would drag to select multiple cells or row headings to adjust the height of multiple rows at the same time.
Let’s make row 1 taller.
Step #1 – Select cell A1.
Step #2 – Click the Format button in the Home > Cells group and select Row Height...
Step #3 – Type 30 in the Row Height dialog box and click OK.
Changing Column Widths
Changing the width of columns is very similar to adjusting the height of a row. Let’s adjust the width of columns B:E using AutoFit. AutoFit adjusts the width of one or more columns automatically.
Step #1 – Drag across the headings for columns B:E.
Step #2 – Position your mouse pointer between two selected column headings and double-click.
Hiding and Unhiding Rows and Columns
You can hide rows and columns in your worksheet. A lot of the time people do this to simplify the spreadsheet or hide data they don’t want anyone to change.
Think about a worksheet companies might use to calculate vehicle expenses. One column could contain the number of kilometers driven, another column could have the reimbursement rate, and a third column could calculate the driving expense.
In this example, let’s hide the column containing the reimbursement rate.
Step #1 – Select cell E4 (or any other cell in column E).
Step #2 – Click the Format button in the Home > Cells group, point to Hide & Unhide, then select Hide Columns from the second panel.
Here is the process to unhide column E.
Step #1 – Drag to select D1:F1.
Step #2 – Click the Format button in the Home > Cells group, point to Hide & Unhide, then select Unhide Columns from the second panel.
Transpose Rows and Columns
If you have a spreadsheet that contains all of the data you want… but you need to flip things around so the column headings are where the row headings are and vice versa, you’ll LOVE the Transpose command.
In our current spreadsheet, each quarter is displayed in a column. Q1 is in column B, Q2 is in column C, etc. The product categories are shown in rows. Baked Goods and the related revenue figures are in row 2. Let’s transpose the rows and columns.
Step #1 – Drag to select cells A1:E6.
Step #2 – Click Copy in the Home > Clipboard group.
Step #3 – Select cell A8, click the down-arrow under the Paste button, and select Paste Special.
Step #4 – In the Paste Special dialog box, select the Transpose check box then click OK.
The data is pasted into the range A8:F12. Notice that the product categories (Baked Goods, Hot Drinks, etc.) are displayed in columns, the fiscal quarters and their associated revenue numbers are displayed in rows.
Freeze Panes
If you’ve worked with large spreadsheets, you may have encountered a situation where you scrolled to view rows or columns of data that weren’t visible on the screen, causing headings and other information to disappear from view.
Freeze Panes lets you specify that part of your Excel worksheet should remain static while the rest of the worksheet scrolls.
Admittedly, the spreadsheet we’ve been using throughout most of this article is pretty small but we can use it to demonstrate Freeze Panes.
If I were to scroll down to work with additional data below what we can see in the screenshot, the viewable area might look like this. The titles for each column have scrolled out of view.
To freeze the first row in the spreadsheet, I would select the Freeze Panes button in the View > Window group and select Freeze Top Row.
This is what the worksheet would look like with row 1 frozen in place. Row 2 has scrolled under the titles in row 1.
The other commands are Freeze First Column, which would cause the category titles in column A to be frozen in place, allowing the user to scroll to the left or right and have the titles remain visible and static.
The first command, Freeze Panes, would result in all of the rows and columns above and to the left of the active cell to be frozen in place.
Once a pane has been frozen, you can unfreeze it and return your worksheet to its former state. To unfreeze the worksheet, select the Freeze Panes button in the View > Window group and click Unfreeze Panes.
