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

    K2 Enterprises has announced its 2014 technology awards in 27 categories. The only clear message may have been that there was no clear message in a field marked by many good ideas, but no unanimous winners.The company, which...
    We're all about QuickBooks this morning. First, read this late-breaking news from John Stokdyk, editor of AccountingWEB (U.K.), who is attending the QuickBooks Connect conference in San Jose, California. Then, for more...
    Technology—specifically internet technology—has a record of disrupting tried-and-true methods of operation in ways that we often don't foresee. Look no further than the recent HBO announcement that they *gasp...

    Already a member? log in here.

    Upcoming CPE Webinars

    Oct 23Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.
    Oct 30Many Excel users have a love-hate relationship with workbook links.
    Nov 5Join CPA thought leader and peer reviewer Rob Cameron and learn ways to improve the outcome of your peer reviews while maximizing the value of your engagement workflow.
    Nov 12This webcast presents basic principles of revenue recognition, including new ASU 2014-09 for the contract method. Also, CPAs in industries who want a refresher on revenue accounting standards will benefit.