Excel Zoom

We put information from all types of sources into our Excel workbooks, usually just by copying and pasting or by using another program to extract data from a database. In either case, sometimes this produces undesired results. Empty cells could play tricks on certain features in Excel, such as pivot tables, that read an empty cell as 0. If we don’t want these cells to appear in the pivot table, a simple space is all you need to keep it from messing up your data.

To use this macro, select your data, and then run the macro. If your list of data is large, give the macro a couple seconds to run, as it will evaluate each cell in your list. It won’t remove data from cells that contain text, formulas, numbers, etc. It will only add a space to cells that are truly blank. After running this macro successfully, you really shouldn’t notice a visual difference in your spreadsheet. You’ll only notice a change if you know that a particular cell did not have a space in it prior to running the macro and notice that it does after running it.

Copy all of the code below. Paste it into your workbook’s Visual Basic editor, in a Module, or Microsoft Excel Object (This Workbook, Sheet1, Sheet2, etc.).

Need help? Use our nifty guide to help figure out how to install and use your macros.

Sub Add_Spaces()
'MACROS BY EXCELZOOM.COM
Application.ScreenUpdating = False
Dim SelectedCell As Range

For Each SelectedCell In Selection
Selection.Replace What:="", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next
Application.ScreenUpdating = True
End Sub