The Spacebar: Your Excel nemesis

The spacebar can be your enemy in Excel. While pressing the spacebar might seem like a simple method for clearing data from a cell, the results of such an action can be devastating. And spaces take up space, and that can cause other problems with your spreadsheet.

Pressing the spacebar leaves not a blank but a space in a spreadsheet cell, and a space is a character, just like a letter or a number. Here are some sample problems with Excel that can be caused by pressing the spacebar either intentionally or accidentally:

  1. Spaces are characters and spreadsheet size is measured by how much space is utilized and how many characters are entered. If you are concerned about the size of your spreadsheet file, a search for spaces might reveal that unwanted spaces are causing an increase in your file size.
  2. Not only does a single space add a character to a cell, a cell containing a space becomes an active cell for purposes of determining the dimensions of your spreadsheet. For example, a spreadsheet containing data in cells A1, A2, and A3 is a spreadsheet that only occupies three cells. Click over in cell C10 and accidentally press the space bar, and you now have a spreadsheet that covers 30 cells! Spreadsheets are measured rectangularly, not by the specific cells that are occupied, so your spreadsheet now goes from Row 1 to Row 10 and from Column A to Column C – 10 rows by 3 columns equals 30 cells. A 30-cell spreadsheet is going to consume more disk space than a 3-cell spreadsheet. And if that arbitrary space is typed in a cell much further away from your data, the spreadsheet size will grow considerably.
  3. Using a space to clear data in a cell can wreak havoc with certain functions and charts. Because the space is considered an actual character, some functions will include the space in computations, and some chart data will be corrupted when spaces are included with the cells being charted.

There are a couple of methods you can use to determine the existence and location of cells containing spaces, and then you can use an easy technique for removing the unwanted spaces.

  • Find the farthest active cell of your spreadsheet by pressing the End key and then the Home key. Your active cell becomes the cell in the most distant occupied row and column. Use one of the deleting techniques shown below to clear the cell of its space character.

  • Search for cells containing spaces by pressing Ctrl+F (or choosing Edit, Find from the menu), then entering a space in the Find what field. Click the Find Next button to find the cells containing spaces.

    Removing spaces

    Clear your spreadsheet of unwanted spaces using one of these techniques:

    1. If you know a cell contains a space, go to that cell and press the Delete key or the Backspace key to remove the space character.
    2. Use the Find feature with its companion, Replace, to find cells containing spaces and remove the spaces. Either on you entire spreadsheet or with an area of the spreadsheet selected, press Ctrl+F, then enter a space in the Find what field. Click the Replace button to open the Replace with field, but leave that field blank. Now click the Find Next button, then the Replace button for each space found (or click Replace All to replace all of the spaces, but beware – spaces that occur in your headings can also be removed with this command).

    Note: Removing a space from a distant cell does not change the fact that that is the last active cell in your spreadsheet. If you need to reduce the size of your spreadsheet to include only the real active cells, you might want to employ a quick technique of copying the data cells to a new spreadsheet. There are other more complicated methods for resetting the last active cell of your spreadsheet, but the copy and paste method is a nice shortcut.

    Best advice: Don’t use the spacebar to clear the contents of a cell. Rely on the Delete or Backspace key instead and the cell will be empty of all characters.

    You may like these other stories...

    Regulatory compliance, risk management and cost-cutting are the big heartburn issues for finance execs in the C-suite. Yet financial planning and analysis—a key antacid—is insufficient.That's just one of the...
    Continuing its efforts to simplify accounting procedures, the FASB has issued a proposed Accounting Standards Update on customer fees paid in a cloud computing arrangement. The newly-proposed update (Intangibles—...
    How are you planning? What tools do you use (or fail to use) for forecasting? PlanGuru is a business budgeting, forecasting, and performance review software company based in White Plains, N.Y. AccountingWEB recently spoke...

    Already a member? log in here.

    Upcoming CPE Webinars

    Aug 26
    This webcast will include discussions of recently issued, commonly-applicable Accounting Standards Updates for non-public, non-governmental entities.
    Aug 28
    Excel spreadsheets are often akin to the American Wild West, where users can input anything they want into any worksheet cell. Excel's Data Validation feature allows you to restrict user inputs to selected choices, but there are many nuances to the feature that often trip users up.
    Sep 9
    In this session we'll discuss the types of technologies and their uses in a small accounting firm office.
    Sep 11
    This webcast will include discussions of commonly-applicable Clarified Auditing Standards for audits of non-public, non-governmental entities.