Rows and Columns in Excel: Complete Guide with Practice Questions

Rows and Columns in Excel: The Foundation of Every Spreadsheet

Open any Excel workbook and the first thing you see is a grid. That grid is made of rows running horizontally and columns running vertically. Rows are numbered 1, 2, 3, all the way down to 1,048,576. Columns use letters, starting at A and running through Z, then AA through AZ, then BA through BZ, finishing at XFD. Multiply those out and a single worksheet gives you over 17 billion cells. You will never fill them all, but knowing the limit helps when you import large data sets.

Every cell sits at the intersection of one row and one column. The cell in row 5, column C, is called C5. This is the cell reference, and it is the basic unit Excel uses for formulas, formatting and navigation. If you can read a cell reference, you can read any formula. The column letter always comes first, the row number second. C5 not 5C. Get this backwards in a formula and Excel returns an error.

Why does this matter for daily work? Because nearly every action in Excel touches a row or a column. You insert rows to add new data. You delete columns to clean up imports. You hide rows to focus on a report. You freeze the top row so headers stay visible when you scroll. You sort by column, filter by column and group by row. Understanding how to control rows and columns turns a beginner into someone who can build proper reports.

Selecting Rows and Columns Quickly

Click the row number on the left to select an entire row. Click the column letter on the top to select an entire column. Hold Shift and click another row or column number to extend the selection. Hold Ctrl and click separate row numbers to select rows that are not next to each other. These are clicks you will repeat thousands of times in your career, so the keyboard shortcuts pay off fast.

Press Shift plus Space to select the entire row that holds the active cell. Press Ctrl plus Space to select the entire column. Press Ctrl plus A once to select the surrounding data block, and again to select the whole worksheet. To move to the last filled row in a column, press Ctrl plus Down Arrow. To move to the last filled column in a row, press Ctrl plus Right Arrow. These shortcuts work in every recent version of Excel including Microsoft 365, Excel 2021 and Excel 2019.

For a quick check of how many rows are selected, look at the bottom status bar. It shows count, sum and average for the current selection. Right-click the status bar to add or remove items such as minimum, maximum and numerical count. This is faster than typing a COUNT formula when you only need the number for a moment.

Inserting Rows and Columns

To insert a single row, right-click the row number where you want the new row to appear, then choose Insert. Excel pushes the existing row down and adds a blank one. To insert several rows at once, first select that many existing rows, right-click and choose Insert. If you select three rows then insert, Excel adds three new blank rows. There is a full walkthrough at our guide on how to insert multiple rows in Excel for repetitive bulk work.

The same logic works for columns. Right-click a column letter, choose Insert, and Excel adds a column to the left of the one you clicked. Need five new columns? Select five existing columns first. The keyboard shortcut for both rows and columns is Ctrl plus Shift plus Plus. After pressing it, Excel asks whether you want to shift cells, entire row or entire column. Pick the right radio button and press Enter.

One trap to avoid: inserting a row in the middle of a formula range. If a formula sums A1:A10 and you insert a row at A5, Excel usually expands the range to A1:A11. Usually. Always check the formula bar after you insert. If the range did not expand, edit it manually or your totals will be wrong. Converting the data to an Excel Table (Ctrl plus T) avoids this problem because Tables expand automatically.

Deleting Rows and Columns the Safe Way

Right-click a row or column header and choose Delete. The shortcut is Ctrl plus Minus. Like the insert shortcut, it asks what you want to shift. Choose entire row or entire column. Pressing the Delete key on its own only clears the contents, it does not remove the row. Many beginners confuse these two actions and end up with empty rows scattered through a sheet, which breaks sorting and filtering later.

Before you delete anything important, save a copy or use Ctrl plus Z to undo. Excel's undo stack is limited, and if you close the file you lose the chance to roll back. If you are removing duplicates, use the Remove Duplicates button on the Data tab rather than deleting rows by hand. It is faster and harder to get wrong.

To delete blank rows in bulk, select the data range, press F5, click Special, choose Blanks and press OK. Now every empty cell is highlighted. Right-click one, choose Delete, then Entire Row. The whole list collapses in seconds. This trick saves hours when you clean exports from older systems that leave gap rows between sections.

Hiding Rows and Columns

Sometimes you do not want to delete data, you just want to put it out of sight. Right-click a row or column header and choose Hide. The row or column collapses to zero height or width but the data stays. The numbers in the row header skip the hidden row, which is a useful clue. To unhide, select the rows on either side of the gap, right-click and choose Unhide.

Hidden rows still appear in formula results, so a SUM that runs across hidden rows still includes them. If you want totals that ignore hidden rows, use SUBTOTAL or AGGREGATE instead of SUM. SUBTOTAL(9, range) sums everything, but SUBTOTAL(109, range) sums only visible cells. The 100-series function numbers are the visible-only versions. This matters for any filtered report sent to a manager.

For temporary work, hiding beats deleting. You can hide row 1 to remove a print header, hide column F to keep a sensitive salary field off-screen during a demo, then unhide when you are done. For permanent privacy, hiding is not security. Anyone who unhides can see the data. Use workbook protection or a separate file if the data is truly confidential.

Resizing Rows and Columns

Drag the border between two row numbers to change row height. Drag the border between two column letters to change column width. Double-click the border to auto-fit the row or column to its largest entry. To resize many at once, select them first, then drag any border. All selected rows or columns change together.

For exact sizes, right-click a row or column header and choose Row Height or Column Width. Excel uses points for row height (one point is one seventy-second of an inch) and character units for column width (the width of one zero in the default font). To set every column to the same width, select all with Ctrl plus A, then set the width once.

Default column width on a fresh workbook is 8.43 characters, which is about 64 pixels at 100 percent zoom. Default row height is 15 points, around 20 pixels. These defaults work for short text and basic numbers, but you will resize constantly for real data. Wrap Text, found on the Home tab, lets long entries flow onto multiple lines so the column stays narrow. For headers with long labels, see our guide on autofitting columns in Excel.

Freezing Rows and Columns So Headers Stay Visible

Reports get long fast. Once you scroll down past row 30, the header in row 1 disappears and you forget which column is what. Freeze Panes solves this. Click cell A2, go to View, Freeze Panes, then Freeze Top Row. Row 1 now stays locked while you scroll. To freeze the first column, choose Freeze First Column instead. For a step-by-step walkthrough see our guide on how to freeze a row in Excel.

To freeze both rows and columns together, click the cell just below and just to the right of what you want to lock. So to freeze rows 1 and 2 and column A, click B3 first, then choose Freeze Panes. To unfreeze, go back to View, Freeze Panes, Unfreeze Panes. Print preview ignores freeze settings, so set print titles separately under Page Layout, Print Titles.

Freezing is a per-worksheet setting, not per-workbook. Each sheet remembers its own freeze. Templates often ship with the header row frozen on every sheet so users do not need to set it. If you build templates, freezing the header row by default is a small kindness that improves daily use.

Grouping, Outlining and Subtotals

Grouping rows and columns lets you collapse and expand sections. Select rows 5 to 10, go to Data, Group, choose Rows. A small bracket appears in the margin with a minus sign. Click it to hide rows 5 to 10 in one click. Click the plus sign to expand them again. Numbers at the top of the margin let you collapse or expand whole levels at once.

This is useful in budget files where each department has a header row and ten detail rows. Group the ten detail rows under each header. Now your manager can see only headers at level 1, or expand any department to level 2 when they want detail. The data is still there, it just collapses out of view.

Subtotal, on the Data tab, combines grouping with automatic SUM rows. Sort by department first, then run Subtotal grouped by department. Excel inserts SUM rows at every break and groups the detail rows under each. Three clicks gives you a collapsible summary that would take a pivot table or several formulas to match.

Common Errors With Rows and Columns

The most common error message you will see is "Cannot shift objects off sheet." It pops up when you try to insert a row near the right or bottom edge and a hidden object would be pushed past column XFD or row 1048576. To fix it, press F5, click Special, choose Objects and delete the orphans. Or set the file option Display options for this workbook, Show objects, to All, then look for the rogue shape.

Another classic: column letters change to numbers and your formulas look like RC[-1]. That is R1C1 reference style. Switch it off under File, Options, Formulas, untick R1C1 reference style. Some macros turn it on without warning. If you inherit a workbook and the columns look strange, check this setting first before trying to debug formulas.

Lastly, watch out for merged cells. Merging across columns makes a sheet look tidy but breaks sorting, filtering and copy-paste. Centre Across Selection, found in the Format Cells dialog under Alignment, gives the same look without the merge. For any sheet you plan to use as a data source, avoid merged cells in the data range.

Another error worth knowing: "The cell or chart you're trying to change is on a protected sheet." This appears when you try to insert or delete on a sheet someone has locked. Unprotect via Review, Unprotect Sheet, then make your change. If the workbook itself is protected, you may not be able to add new sheets either. Passwords stored in older files can sometimes be removed by saving the file as .zip and editing the workbook.xml, but that is rarely needed in day-to-day work.

Split Window and Print Titles

Split, also under the View tab, divides the worksheet into two or four resizable panes. Each pane scrolls independently. Click cell D10, press View, Split, and you get four panes meeting at that cell. Drag the split bars to move the divisions. Compare row 5 with row 5000 side by side without losing your place. To remove the split, click Split again.

Freezing a row only affects what you see on screen. When you print a long report, only page one shows the header. To repeat headers on every printed page, go to Page Layout, Print Titles, and set Rows to repeat at top to your header row, like $1:$1. Set Columns to repeat at left to $A:$A if your row labels live in column A.

If you open a file saved as .xls (the old binary format from Excel 97 through 2003), the worksheet drops to 65,536 rows and 256 columns. Excel calls this Compatibility Mode. Any data beyond row 65,536 or column IV will be lost when you save. Convert the file to .xlsx with Save As to unlock the full modern grid. Web-based Excel and Excel for Mac match the modern Windows limits.

Rows and columns are the structure that every formula, chart and pivot table reads. Learning to insert, delete, hide, resize and freeze them turns a slow spreadsheet user into someone who builds clean reports fast. Most certification exams, including the MOS Excel exam, test these skills directly. Expect questions on inserting multiple rows at once, on the difference between Delete and Clear Contents, and on which SUBTOTAL number ignores hidden rows. The questions below mirror what you will face.

Practice is the fastest route to confidence. Open a blank workbook, drop a few hundred rows of dummy data into it, and run through every action in this guide. Insert ten rows at once. Delete the blank rows with F5 Special. Freeze the header and scroll to row 500. Hide column C, then SUM with both SUM and SUBTOTAL to see the difference. Five minutes of hands-on practice cements what reading alone cannot. After that, run a timed practice test to lock in your speed and you will be ready for any Excel certification or workplace task.