This article no longer applies to those using Outlook ‘2013 since the Excel file format is no longer supported. However, the information related to “deleted” versus “cleared” rows applies to many other scenarios outside of Outlook which may result in people getting unexpected results.
How many times of you seen someone ask (or have even asked yourself) – Why are there so blank rows when I import my Excel file into Outlook (or even a CSV file created via Excel)?
There are many things you don’t have to think about when working strictly within the confines of a one program. However, when the data contained in one program has to be used in another program, the integrity of the data becomes important.
This article focuses on the difference between two simple functions that visually appear to do the same thing but can have radically different results. These functions are the “Clear Contents” and “Delete” options you find when you right click on an Excel row# (right clicking within a cell brings up additional options).
Let’s take the simplest of scenarios – you have an Excel worksheet with 10 rows. You decide that you no longer care about rows 6 through 10 and want to get rid of the information in the worksheet. You have two choices, clear the data or delete the rows. In both cases, your worksheet looks the same – only the first 5 rows are populated. Unfortunately, it’s the “not the same” when external factors come into play.
Clear Contents
This function does exactly what it says, clears the contents of the rows and from a visual perspective it appears that your worksheet only has 5 rows. Reality is that you still have the same 10 rows in your worksheet, only difference now being that the last 5 rows are blank. Why does this matter?
If you save this worksheet as a csv file, you are going to have 5 empty lines in your text file. Empty CSV records are impossible to miss, each line consists of a row of commas, the length of which is determined by the number of columns used. In addition, the program dealing with this CSV file has to handle the issue of empty rows.
Think of it this way, when you remove information from any kind of database, do you “delete” the record or just go through and clear the value of every field (something you can’t do in any kind of intelligently designed database even if you wanted to)?
The same issue applies when it comes to “columns”. If your worksheet has 10 columns (A thru J for sake of argument), and you “clear” columns F thru J, you still have 10 columns in your worksheet. Except now, if you save this worksheet as a CSV file, the column “header” information related to these columns will be empty (assuming that Row#1 contains column header information) meaning that whatever program has to deal with this CSV file also now has to contend with a file that has 10 fields per row but the last 5 columns have no column headings (field names).
Again, to compare this with a database (MS Access, MS SQL, Oracle etc), when you want to remove “fields” in a database (a field = a column in Excel terms), it is not done by blanking out the field name and the contents of the field (even you wanted to do that, you can’t).
Which brings us to “Delete”
This function also does exactly what the name states, it actually deletes the selected rows or columns. So to use the same example above, if you “deleted” the last 5 rows instead of “Clear Contents” and then saved the same worksheet, the CSV file will only end up having 5 lines and not 10. There will not be any trailing lines comprised of all commas. The same applies to columns.
And finally, the impact on “Named Ranges”
If you are very conversant with Excel “Named Ranges”, you will be very familiar with the following. However, for far too many people the only time “Named Ranges” become even remotely important is when they try and import an Excel worksheet into Outlook.
Using the same example, the “Named Range” would be created to cover rows/columns $A$1:$J$10. If you use “Clear Contents” to empty the last 5 rows, the Named Range does not get adjusted. You still have 10 rows within that Named Range. However, if you delete the rows, Excel will automatically adjust the Named Range to $A$1:$J$5 to reflect the change. Why is that important? Outlook will import all information included within the Named Range. Again, the same is equally true of columns.
In summary, if you’re of the mindset that the rest of the world should be able to deal with virtually any kind of garbage presented to it, then none of this is relevant. There seems to be no limit on the ways that can be found to contort data. If, on the other hand, your objective is to accomplish a task with the least amount of potential problems and the priority is to get the job done versus finding out how many ways there are to potentially break another program, taking some very basic and minor steps goes a very long way to achieving that objective. Most problems are not created intentionally but purely caused by a lack of awareness of the consequences resulting from the choices made.