You are here:

Excel/Blanks inserted by


QUESTION: I have set up a workbook to compile four worksheets into one. The trouble is that the people who provide the original data have slight amendments in each of their submissions. I used "=IF(EAC!A11="","",IF(EAC!$D11="","",EAC!A11))" to tell the sheet only to pick out those items where a specific cell in each row contained a value. My view was, if the original cell was blank, I wanted a blank, if the original cell was not blank but the specific column cell was blank, I wanted a blank. If they both contained values, I wanted the value of the original cell.

I then copied my table, just values, and sorted it on the column which identified whether I was interested in the row, to put all the blanks at the bottom, so that I could concentrate only on the active data.

What I wanted to do next was to use end an down to establish the address of the lowest active entry, but the cursor moved to the bottom of the maximum number of inputs; as I was starting with four 200 row tables, this was 800 lines down.

How do I make the IF function leave the cell empty, so that end down only goes to the bottom of the data?


but the cell isn't empty - it has a formula in it and there is nothing that will make excel think it is empty.

Why not apply a filter to the data and only show rows that have a value.

Then you wouldn't even need to sort it - but that would work with it sorted as well.

when you did your end down arrow it would stop on the last filled row (in either case) because it would not consider the hidden rows.

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: The table containing the formulae has a formula in every cell of the data range but, when I copy it to another sheet using Paste Special, Values, why does the "" not just become a blank cell?

I have checked that you solution works, so many thanks for that, but I am intrigued as to why "" is not a blank, when copied as a value.


Because it is a null string and not empty.

I can't tell you much beyond that.  I have looked at how information is stored in the old file format and there are "types" of records that store information about cells.  I suspect the record for the cell that determines whether it is empty says that it is not empty.  It is just a decision that was made by the designers/programmers of the excel application.  There may be a legitimate reason it is that way - it may have to do with the definition of the used range - I don't really know - I just know it is that way.  

if you click in your data and do Ctrl+Shift+8  those null string cells will be selected.  If you select the null string cells and clear them, then do the same (click in the data and do Ctrl+Shift+8), they won't be included in the selection.   that isn't showing you anything new, but it does emphasize that the cells are still considered not to be empty.

Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 All rights reserved.