Categories
Excel

Working with Rows and Columns in Excel

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.

Excel spreadsheet showing sales by category for food items over four fiscal quarters

Step #2 – Click the down arrow beside the Insert button in the Home > Cells group and select Insert Sheet Rows.

Insert sheet rows command highlighted in shortcut menu
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.

Excel worksheet with cells B1:C1 highlighted

Step #2 – Click the down arrow beside the Insert button in the Home > Cells group and select Insert Sheet Columns.

Insert Sheet Columns command highlighted in shortcut menu
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.

Insert Options button
Insert Options Button

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.

Available choices when you click the Insert Options button

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.

The Delete command highlighted in a 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.

The Delete command highlighted in a 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.

Excel spreadsheet showing sales by category for food items over four fiscal quarters

Step #2 – Click the Format button in the Home > Cells group and select Row Height...

The Row Height command highlighted in a shortcut menu

Step #3 – Type 30 in the Row Height dialog box and click OK.

Row Height dialog box
Row Height Dialog Box

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.

Excel worksheet with Columns B:E selected

Step #2 – Position your mouse pointer between two selected column headings and double-click.

Mouse pointer shaped like a double-headed arrow on the border the column headings for D:E

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).

Excel worksheet for vehicle expenses

Step #2 – Click the Format button in the Home > Cells group, point to Hide & Unhide, then select Hide Columns from the second panel.

The Hide Columns command

Here is the process to unhide column E.

Step #1 – Drag to select D1:F1.

Cells D1:F1 selected

Step #2 – Click the Format button in the Home > Cells group, point to Hide & Unhide, then select Unhide Columns from the second panel.

The Unhide Columns command

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.

Spreadsheet with cells A1:E6 selected

Step #2 – Click Copy in the Home > Clipboard group.

The Copy button

Step #3 – Select cell A8, click the down-arrow under the Paste button, and select Paste Special.

The Paste Special command

Step #4 – In the Paste Special dialog box, select the Transpose check box then click OK.

The Transpose checkbox selected in the Paste Special dialog box
Transpose Rows and Columns

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.

Rows and Columns Transposed
Rows and Columns Transposed

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.

Excel spreadsheet showing sales by category for food items over four fiscal quarters

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.

Spreadsheet with rows 3 - 7 visible

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.

Freeze Top Row command selected
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.

Row 1 frozen

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.

Unfreeze Panes command highlighted
Unfreeze Panes

By Michael Belfry

Working as a full-time training consultant, Michael provides Microsoft Office courses to government and private sector clients across Canada.