Prologue
For the purpose of this blog post we will call files with a .csv extension CSV files and files with and .xlsx extension Excel files.
The big confusion
Have you ever had someone send you a file that had a CSV extension, that is, the filename ended with .csv?
When you tried to open it, you probably used the built-in editor on Mac called Numbers and on Windows, a basic file editor without any formatting.
So what do you make of this?
You may have been expecting an Excel file and you got a CSV file. You have been had! Maybe it was a prank. Or was it?
Well, it's complicated.
CSV files and Excel files are very confusing.
They are not the same but they can look the same.
Let's take a look at two screenshots of the same spreadsheets and try to guess which one is an Excel file and which one is a CSV file?
If you picked the screenshot A then you were wrong, sorry.
But don't feel too bad, It wasn't easy. Have no fear though, there is one thing that Excel files can do and CSV files can't.
It's formulas!
The power that comes from knowing Excel is that you can manipulate the data easily by writing simple or complex formulas.
So following this logic if the file your prankster friend sent you contains formulas, then it's an Excel file right?
It turns out that it may not be. It is actually possible to embed formulas into a CSV file. So this trick won't work. If you want to read more about this monstrosity, feel free to follow this link.
According to the link above, you can include formulas in the CSV file which makes it behave like an Excel file.
You can also save a CSV file as an Excel file which means that a CSV can be the basis for an Excel file and an Excel file can be the basis of a CSV file.
If you are feeling lost now, then don't worry. It's normal.
Choosing the right format
Choosing the right format depends on what kind of software you'll be using to edit the file and also which operating system you use.
Most basic text editors can open a CSV file because a CSV file is just lines of text whose values are separated by commas or semicolons. Each line in the file represents a row.
On Windows, depending on certain settings, your operating system may expect CSV files to have the values contained in the file separated by commas or semicolons.
You can find the expected List separator as it is called in the Regional and Language Options. This is the character Windows applications expect to be the CSV separator. Of course this only has effect in Windows applications, for example Excel will not automatically split data into columns if the file is not using the above mentioned separator.
All applications that use Windows regional settings will have this behavior.
A good editor will know that you want to display those values as a table and each value will be displayed in a cell.
So in terms of compatibility, if you are not sure that the person to whom you are sending the file has the Microsoft Office suite or a software capable of opening and editing an Excel file, then sending a file as a CSV file will ensure that the file can be opened by the recipient.
Taking chances
Out of the 8 Billion people currently living on this planet, 4 Billion have access to the internet and 1.2 Billion (almost 33%) have access to a software that will open an Excel file via the Microsoft Office suite.
If you are sending a file to the only unlucky person of your entourage who does not have access to Microsoft Office, the built-in editors included in the various operating systems will still allow this person to open the file.
On Mac, the default program to open a CSV or Excel (.xlsx) file is called Numbers. It is available for free on each and every Mac out there.
If the person you are sending the file to has a Mac then the format does not usually matter. Numbers will open both Excel and CSV files regardless of the format and the end user(you or your colleague will be none the wiser).
If you don't want to think too much about these kinds of issues, you are not alone. In this case, and especially if you expect the recipient to add formulas to the file, then sending it as an Excel file is a no-brainer. Otherwise, send it as CSV.
Conclusion
Choosing which format to use can seem like a thankless task and mostly pointless but it is much more than that. When you are the only one working on a file, then the format is irrelevant and you should use whatever you feel most comfortable with.
However as soon as someone else is involved in the process then using the most accepted file format will ensure that the recipient can open, edit and manipulate the data contained in the file without any issues.
As the file creator it is your job to make sure that your file is readable.
There is nothing more infuriating than spending hours working on a file, creating slick formulas and then getting an email from the person whom you sent the file to, saying that their program refused to open the file or worse, opened it but all the data was mangled and basically unusable.
The CSV and Excel (.xlsx) formats have both long histories going back decades. There is a lot of legacy embedded in the programs capable of reading and editing those files.
99% of the time, you will have no issues, but every once in a while, when you need a way to diagnose a problem with a file you sent, remember that the format can be the issue and either switching to a CSV or Excel (.xlsx) format could fix the issue you are having.