CSV?   XLS?  Saving your document in Excel is like getting to the bottom of a mystery, but without very many clues.  Never fear, I’ve got a handy table AND some tried and true tips to share about the different types of spreadsheet files.

 

depositphotos_13951197-stock-illustration-cartoon-computer-frustration
Frustrated by file extensions?  You’re not alone!
Type Description
.xls Legacy Excel worksheet, often known as “Microsoft Excel 97-2003.”  Functionally works pretty similarly to .xlsx files for most users.
.xlsx Standard Excel workbook.
.csv CSV (stands for Comma Separated Value) is a simple, standard file format that computers use to store “tabular” data (columns and rows!).  Excel reads and displays CSVs but you can open them without Excel.  Your computer would likely open a text editor and show you your data, separated by commas.  The CSV format is the main way that data gets imported and exported from programs or databases.  Think of a CSV like ONE TAB of an excel workbook.
.xlsm Excel macro-enabled workbook (same as above, with potential for macros or scripts – but don’t worry about those!)
.xltx Excel template
.xltm Excel macro-enabled template (same as above but with potential for macros and scripts)

Why I like .xlsx format:

  • In a Xls workbook, the row limit is 65,536 (216)and 256 columns (28) which corresponds to the column IV.  Now with xlsx workbooks (and xlsm), limits are 1,048,576 rows (220) and 16,384 columns (214) or the column XFD.
  • .XLSX is compressed, so it takes up less room on your computer or in your Cloud storage drive
  • You can tell by looking at the file extension if the file has Macros or not. This is a good security feature!  A macro is a program written in Excel that kicks off a series of automatic processes.  This is great if you need to do the same functions in Excel over and over again, but it’s not so great if it causes you problems.  It’s nice to know ahead of time if your doc has macros or not!
  • You can have many tabs!!

Why I like .csv format:

  • CSVs are straight up data, no frills. No colors, formatting, tables, etc.
  • CSVs are one tab at time
  • CSVs can be imported into other programs, often when .XLSX files cannot

And now for a word of warning:

If you are working on a spreadsheet with more than one tab, sometimes you might want to select Save As to save a tab as a .CSV (especially if you need to import it into another program).  When you save your data, Excel will give your working tab a new name – and Excel will prompt you to make sure you want to save as CSV because when you do this, you are putting your other data in danger.  If you go edit data in other tabs (same spreadsheet), and then you hit “save” and exit out, Excel will refresh your previously-saved CSV and will not save the rest of your tabs and data. You will have to Save As AGAIN before exiting out, this time as a .XLSX workbook to hold on to the data in ALL of your tabs.   My advice is to always save as a .XLSX (save early and save often!).  When you are confident that you don’t need to make changes, convert the tabs you need into CSVs!

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s