Data Disappears in Excel

By Elizabeth Mott

If you see blank cells instead of data when you open an Excel workbook, don't panic.
i Duncan Smith/Photodisc/Getty Images

When you open a Microsoft Excel worksheet to review sales data or other company information, you expect to see an expanse of cell values. Especially if you haven't looked at the document in some time, or you didn't prepare the file yourself, you'll need a moment to digest it. If some of its contents appear to be missing, examine your data closely for signs that it uses advanced Excel analytical features -- or simply got saved with something in the wrong place.

Cursor Location

When you click in a Microsoft Excel worksheet cell to make it active and save and close your file with the cell in that condition, Excel retains that information as part of your document. If the cell in which you clicked -- accidentally or not -- falls many rows or columns away from the worksheet area you're using for data and calculations, you may think you've accidentally deleted your file's contents when you reopen it and see blank rows and columns. Look at the column and row headings for signs that you're viewing the wrong part of your document, and press "Ctrl-Home" to return to cell A1 at the beginning of the file.

Hidden Rows or Columns

If a worksheet requires complex calculations to derive a result that draws on multiple categories of information, the "forest" of data can hide the "trees" of the result. Microsoft Excel enables you to hide entire columns or rows of a spreadsheet, which can focus the viewer on the point you're trying to make. Look for a discontinuity in the letters and numbers that designate worksheet columns and rows, signaling hidden information. To view all the hidden data, switch to the Home tab of the Excel ribbon, locate its Cells group and click on the "Format" item. The Visibility section's Hide & Unhide options include both Unhide Rows and Unhide Columns.

Filtered Data

Excel's filtering capabilities enable you to reveal only those rows or columns that contain values that match criteria you select. For example, in a worksheet that displays information about sales performance, you can filter out all the results for one product or display only the sales for one employee. The Autofilter turns column headings into selectors you can use to choose values from the range of data in each column. Through filtering, you can locate specific text or numeric values, screen for highs and lows, identify blank cells, and find cells that use specific manual or conditional formatting. To reveal all the information that's hidden by the filter, switch to the Data tab of the Excel ribbon, locate the Sort & Filter group, and click on "Clear." You also can clear the filtering criteria from a single column by clicking on the filter button that appears on its heading.

Grouping and Outlining

Summarizing data helps you show the conclusions to be drawn from a set of values. Once you add subtotals that show how your values add up or average out, you can summarize sales performance, production, costs or any other numeric data, producing an outline with up to eight levels. In the process of applying the grouping and outlining features to produce outlined results, Microsoft Excel hides the data that contribute to the results it displays. To turn off these features and show all your cells, switch to the Data tab of the Excel ribbon, locate its Outline group and click on the "Ungroup" item to access its Clear Outline option. If some of your cells remain hidden, switch to the Home tab, locate its Cells group and click on the "Format" item to access visibility options.

Software Versions

Information in this article applies to Microsoft Excel 2010. It may differ slightly or significantly with other versions.