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

    Cybersecurity is no longer the domain of an organization's IT staff. It's moved to the boardroom, and in a big way. Accountants and financial managers may have been thinking it's just the province of the tech...
    You probably don't want to think about how many times you access the File menu in Excel 2010 or 2013. Personally I think Excel 2010 has the best possible File menu arrangement, other than having Print Preview grafted...
    Following other recent high-profile hacking events, investigators discovered yesterday that hackers broke into the draft work paper files of several famous CPA firms. Revealing images of the scantily clad documents have been...

    Already a member? log in here.

    Upcoming CPE Webinars

    Sep 24
    In this jam-packed presentation Excel expert David Ringstrom, CPA will give you a crash-course in creating spreadsheet-based dashboards. A dashboard condenses large amounts of data into a compact space, yet enables the end user to easily drill down into details when warranted.
    Sep 30
    This webcast will include discussions of important issues in SSARS No. 19 and the current status of proposed changes by the Accounting and Review Services Committee in these statements.
    Oct 21
    Kristen Rampe will share how to speak and write more effectively by understanding your own and your audience's communication style.
    Oct 23
    Amber Setter will show the value of leadership assessments as tools for individual and organizational leadership development initiatives.