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

    Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...
    No field likes its buzzwords more than technology, and one of today's leading terms is "the cloud." But it's not just a matter of knowing what's fashionable. Accounting professionals who know how to use...
    There is a growing trend of accountants moving away from traditional compliance work to more advisory work. Client demand is there, but it is up to the accountants to capitalize on that. What should accountants' roles be...

    Upcoming CPE Webinars

    Apr 22
    Is everyone at your organization meeting your client service expectations? Let client service expert, Kristen Rampe, CPA help you establish a reputation of top-tier service in every facet of your firm during this one hour webinar.
    Apr 24
    In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
    Apr 25
    This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
    Apr 30
    During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.