Hide and delete rows and columns in Excel: tidy a list before you share it

An expense export rarely arrives ready to use. It has internal notes, voided lines, blank rows where two pastes met, a second header row in the middle, and amounts in three different formats. Before you sort it, total it or send it to an auditor, you tidy it. This page explains hiding versus deleting, why blank rows and repeated headers cause trouble, and how to give a column one consistent format.

Hide or delete: two very different actions

Hiding and deleting both make something disappear from view, so they are easy to confuse. They do opposite things to the file.

• Hiding keeps the row or column in the workbook. Its values are still there, formulas still use them, and anyone can unhide it. Only the display changes: the row numbers or column letters skip over it.
• Deleting removes the row or column completely. The cells below a deleted row move up to close the gap, and the columns to the right of a deleted column move left. The data is gone from the file unless you undo.

So ask: does this data need to stay in the file? If yes, but out of sight, hide it. If no, because it is a blank row, a duplicate header or a column of unused codes, delete it.

Hidden data still counts

A hidden row is not excluded from your calculations. If C5:C16 holds twelve amounts and you hide two of them, =SUM(C5:C16) still adds all twelve. The total on screen then looks larger than the visible lines would suggest, which can confuse a reader who adds up what they can see.

That is often what you want: a list for an auditor might hide voided lines for readability while the file keeps them for your records. But if you need a total of only the visible lines, a plain SUM is the wrong tool; functions such as SUBTOTAL can skip hidden rows.

Hiding is not security

Hidden rows and columns are one click away from anyone who opens the file: they can select the columns either side and choose Unhide, or notice that the column letters jump from C to E. So don't rely on hiding for anything confidential, such as salaries or bank details. Remove it from the copy you share instead. Hiding is for tidiness, not for keeping secrets.

Clear contents is not the same as delete

Pressing the Delete key on a selected row clears its contents. The cells become empty, but the row itself stays where it was, so the list now has a blank row in it. Deleting the row, using the Delete rows command, removes the row and pulls everything below it up by one.

The same applies to columns. Clear an unused column and an empty column remains; delete it and the columns to its right shift left to fill the space.

Why blank rows and repeated headers break a list

Most list tools treat a block of filled cells with a header on top as one table. A blank row ends that block. A header row repeated in the middle looks like data. Either one quietly breaks the tools you will use next:

• Sorting. When you sort from a cell in the list, the spreadsheet usually picks up only the block around that cell, stopping at the first blank row. Part of the list is sorted and the rest is not. A repeated header that is inside the block gets sorted in among the payees, as if Date were a transaction. See sorting a general ledger in Excel for more.
• Filtering. A filter applied to a list with gaps may cover only the rows above the first blank, so lines further down never appear in the filter results.
• Totals. A quick total placed under the list may stop at the blank row and add only the last block.
• PivotTables. A PivotTable built from the block above a gap misses the rest of the data, and text in a number column can make the field count instead of sum.

The fix is one solid block: one header row at the top and every data line directly below it. The same cleanup comes up when you import a bank statement CSV into Excel and before you look for duplicate transactions.

How to hide, unhide and delete

The steps are the same in our practice grid and in the desktop spreadsheet. You do not need to select the whole row or column; any cell in it is enough.

1. Hide a column: select a cell in the column, then choose Hide columns.
2. Hide rows: select a cell in each row you want hidden, then choose Hide rows. You can select several rows at once.
3. Unhide: select the rows or columns on both sides of the hidden ones, then choose Unhide.
4. Delete rows: select a cell in each row to remove, then choose Delete rows. Deleting them all in one step avoids a common slip: after each single deletion the rows below move up, so the row numbers you noted point at different lines.
5. Delete a column: select a cell in the column, then choose Delete columns. The columns to its right move one place left.

Worked example: one list pasted in twice

Northside Print Shop exported its expense list for February, but the export was pasted into the sheet twice, with gaps. The header is in row 4. Here is the messy version (the lines after row 17 continue in the same way):

Rows 7, 11 and 15 are blank and row 12 repeats the header. Sorting from row 5 would move only rows 5 and 6, because blank row 7 ends the block. All four rows have to go.

Select a cell in each of those four rows and choose Delete rows once. Everything below moves up, and the list becomes a single block in rows 4 to 16:

The twelve expense lines are in their original order, directly below one header. A total of C5:C16 now covers every line: 14,742.53. Sorting, filtering and a PivotTable will all see the whole list.

Give each column one format

A column of amounts should look the same on every line. Pasted data often arrives in a mix: one line shows $1,611 with no cents, the next 2301.68 in General, another 1295.7 with one decimal. The values may be correct, but the column looks careless, and too few decimals hide the cents a reviewer needs.

The fix is to select the whole range of amounts, for example C5:C16, and choose one format for all of it. For a list of money, Accounting with two decimals is a good default: choose Accounting from the number format list, and every amount shows a dollar sign at the left edge of the cell and two decimals, such as $ 1,610.94. A format only changes how the value looks, never the value itself. The page on the Accounting number format in Excel explains the formats in detail.

Common mistakes and how to fix them

These are the mistakes our exercises check for.

Deleting what should be hidden

The internal notes had to stay in the file, just out of sight. Undo and use Hide columns instead.

Hiding the column but not the rows

When a task asks you to hide a column and some lines, such as the ones marked VOID, check both. It is easy to hide the notes and forget the void rows still show.

Keeping the repeated header

Deleting the blank rows but leaving the second Date / Payee / Amount row puts a header in the middle of the data. It is not an expense line; delete it too.

Keeping blank rows

Deleting the repeated header but leaving the gaps still splits the list into blocks. Delete the blank rows so the expenses form one block.

Clearing a column instead of deleting it

Selecting the old codes and pressing the Delete key empties the column but leaves it in place, so the amounts stay in column D. Use Delete columns so the amounts move into column C.

Currency instead of Accounting

Currency also shows a dollar sign and two decimals, so the column looks consistent, but Accounting lines the dollar signs up at the edge of the column. When the task asks for Accounting, choose it from the number format list.

Where this fits in MO-220 practice

Hiding, deleting and formatting are part of preparing data for analysis. Our MO-220 practice exercises describe the result you need and check the finished sheet by its values, hidden rows and columns, and formats. Accounting Exercises is not affiliated with Microsoft or Certiport.