Editing Microsoft Excel (section)

== Data storage and communication ==

=== Number of rows and columns ===
Versions of Excel up to 7.0 had a limitation in the size of their data sets of 16K (2<sup>14</sup> = {{val|16384}}) rows. Versions 8.0 through 11.0 could handle 64K (2<sup>16</sup> = {{val|65536}}) rows and 256 columns (2<sup>8</sup> as label 'IV'). Version 12.0 onwards, including the current Version 16.x, can handle over 1M (2<sup>20</sup> = {{val|1048576}}) rows, and {{val|16384}} (2<sup>14</sup>, labeled as column 'XFD') columns.<ref>{{cite web|date=May 23, 2014|title=The 'Big Grid' and Increased Limits in Excel 2007|url=http://msdn.microsoft.com/en-us/library/aa730921.aspx|access-date=April 10, 2008|website=microsoft.com|archive-date=December 7, 2013|archive-url=https://web.archive.org/web/20131207150655/http://msdn.microsoft.com/en-us/library/aa730921.aspx|url-status=live}}</ref>

=== File formats ===
{{Infobox file format
| name          = Excel Spreadsheet
| icon          = 
| logo          = 
| extension     = .xls, (.xlsx, .xlsm, .xlsb – Excel 2007)
| mime          = application/vnd.ms-excel
| type code     = 
| uniform type  = com.microsoft.excel.xls
| magic         = 
| owner         = [[Microsoft]]
| genre         = [[Spreadsheet]]
| container for = 
| contained by  = 
| extended from = 
| extended to   = 
| standard      = 
}}

Up until the 2007 version, Microsoft Excel used a proprietary binary file format called Excel Binary File Format (.XLS) as its primary format.<ref>{{cite web|date=February 26, 2008|title=How to extract information from Office files by using Office file formats and schemas|url=http://support.microsoft.com/kb/840817/|url-status=live|access-date=November 10, 2008|website=microsoft.com|publisher=Microsoft|archive-date=May 14, 2008|archive-url=https://web.archive.org/web/20080514083958/http://support.microsoft.com/kb/840817}}</ref> Excel 2007 uses [[Office Open XML]] as its primary file format, an XML-based format that followed after a previous [[XML]]-based format called "XML Spreadsheet" ("XMLSS"), first introduced in Excel 2002.<ref name="xml">{{cite web|url=http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx|title=XML Spreadsheet Reference|work=Microsoft Excel 2002 Technical Articles|publisher=MSDN|date=August 2001|access-date=November 10, 2008|archive-date=October 2, 2008|archive-url=https://web.archive.org/web/20081002081358/http://msdn.microsoft.com/en-us/library/aa140066(office.10).aspx|url-status=live}}</ref>

Although supporting and encouraging the use of new XML-based formats as replacements, Excel 2007 remained backwards-compatible with the traditional, binary formats. In addition, most versions of Microsoft Excel can read [[comma-separated values|CSV]], [[DBase|DBF]], [[Symbolic Link (SYLK)|SYLK]], [[Data Interchange Format|DIF]], and other legacy formats. Support for some older file formats was removed in Excel 2007.<ref>{{cite web|url=http://blogs.office.com/b/microsoft-excel/archive/2006/08/24/deprecated-features-for-excel-2007.aspx|title=Deprecated features for Excel 2007|publisher=Microsoft—David Gainer|date=August 24, 2006|access-date=January 2, 2009|archive-date=July 17, 2011|archive-url=https://web.archive.org/web/20110717141312/http://blogs.office.com/b/microsoft-excel/archive/2006/08/24/deprecated-features-for-excel-2007.aspx|url-status=live}}</ref> The file formats were mainly from DOS-based programs.

==== Binary ====
[[OpenOffice.org]] has created documentation of the Excel format. Two epochs of the format exist: the 97-2003 [[Object Linking and Embedding|OLE]] format, and the older stream format.<ref>{{cite web|url=http://sc.openoffice.org/excelfileformat.pdf|title=OpenOffice.org's documentation of the Microsoft Excel File Format|date=August 2, 2008|access-date=July 28, 2009|archive-date=May 9, 2008|archive-url=https://web.archive.org/web/20080509063852/http://sc.openoffice.org/excelfileformat.pdf|url-status=live}}</ref> Microsoft has made the Excel binary format specification available to freely download.<ref>{{cite web|url=http://download.microsoft.com/download/0/B/E/0BE8BDD7-E5E8-422A-ABFD-4342ED7AD886/Excel97-2007BinaryFileFormat(xls)Specification.xps|title=Microsoft Office Excel 97 – 2007 Binary File Format Specification (*.xls 97-2007 format)|year=2007|publisher=Microsoft Corporation|access-date=February 16, 2008|archive-date=December 22, 2008|archive-url=https://web.archive.org/web/20081222093136/http://download.microsoft.com/download/0/B/E/0BE8BDD7-E5E8-422A-ABFD-4342ED7AD886/Excel97-2007BinaryFileFormat(xls)Specification.xps|url-status=live}}</ref>

==== XML Spreadsheet ====
{{main|Microsoft Office XML formats}}
The ''XML Spreadsheet'' format introduced in Excel 2002<ref name="xml" /> is a simple, XML based format missing some more advanced features like storage of VBA macros. Though the intended file extension for this format is ''.xml'', the program also correctly handles XML files with ''.xls'' extension. This feature is widely used by third-party applications (e.g. ''[[MySQL]] Query Browser'') to offer "export to Excel" capabilities without implementing binary file format. The following example will be correctly opened by Excel if saved either as ''Book1.xml'' or ''Book1.xls'':

<syntaxhighlight lang="xml">
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Example</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>
</syntaxhighlight>

==== Current file extensions ====
{{Main|Office Open XML}}

Microsoft Excel 2007, along with the other products in the [[Microsoft Office 2007]] suite, introduced new file formats. The first of these (.xlsx) is defined in the Office Open XML (OOXML) specification.

{| class="wikitable"
|+Excel 2007 formats
!Format
!Extension
!Description
|-
|Excel Workbook
|{{code|.xlsx}}
|The default Excel 2007 and later workbook format. In reality, a [[ZIP (file format)|ZIP]] compressed archive with a directory structure of [[XML]] text documents. Functions as the primary replacement for the former binary .xls format, although it does not support Excel macros for security reasons. Saving as .xlsx offers file size reduction over .xls<ref>{{Cite book|last=Fairhurst|first=Danielle Stein|url=https://books.google.com/books?id=oSXWBgAAQBAJ&pg=PA324|title=Using Excel for Business Analysis: A Guide to Financial Modelling Fundamentals|date=March 17, 2015|publisher=John Wiley & Sons|isbn=978-1-119-06245-5|language=en}}</ref>
|-
|Excel Macro-enabled Workbook
|{{code|.xlsm}}
|As Excel Workbook, but with macro support.
|-
|Excel Binary Workbook
|{{code|.xlsb}}
|As Excel Macro-enabled Workbook, but storing information in binary form rather than XML documents for opening and saving documents more quickly and efficiently. Intended especially for very large documents with tens of thousands of rows, and/or several hundreds of columns. This format is very useful for shrinking large Excel files as is often the case when doing data analysis.
|-
|Excel Macro-enabled Template
|{{code|.xltm}}
|A template document that forms a basis for actual workbooks, with macro support. The replacement for the old .xlt format.
|-
|Excel Add-in
|{{code|.xlam}}
|Excel add-in to add extra functionality and tools. Inherent macro support because of the file purpose.
|}

==== Old file extensions ====
{|class="wikitable"
|-
!Format
!Extension
!Description
|-
|[[Spreadsheet]]
|{{code|.xls}}
|Main spreadsheet format which holds data in worksheets, charts, and macros
|-
|Add-in ([[Visual Basic for Applications|VBA]])
|{{code|.xla}}
|Adds custom functionality; written in [[Visual Basic for Applications|VBA]]
|-
|Toolbar
|{{code|.xlb}}
|The file extension where Microsoft Excel custom toolbar settings are stored.
|-
|Chart
|{{code|.xlc}}
|A chart created with data from a Microsoft Excel spreadsheet that only saves the chart. To save the chart and spreadsheet save as .XLS. XLC is not supported in Excel 2007 or in any newer versions of Excel.
|-
|Dialog
|{{code|.xld}}
|Used in older versions of Excel.
|-
|Archive
|{{code|.xlk}}
|A backup of an Excel Spreadsheet
|-
|Add-in (DLL)
|{{code|.xll}}
|Adds custom functionality; written in [[C++]]/[[C (programming language)|C]], [[Fortran]], etc. and compiled into a special [[dynamic-link library]]
|-
|Macro
|{{code|.xlm}}
|A macro is created by the user or pre-installed with Excel.
|-
|Template
|<code>[[Xlt (file format)|.xlt]]</code> <!-- using {{code}} either breaks the link or makes it invisible -->
|A pre-formatted spreadsheet created by the user or by Microsoft Excel.
|-
|Module
|{{code|.xlv}}
|A module is written in VBA (Visual Basic for Applications) for Microsoft Excel
|-
|Library
|{{code|.DLL}}
|Code written in VBA may access functions in a DLL, typically this is used to access the Windows API
|-
|Workspace
|{{code|.xlw}}
|Arrangement of the windows of multiple Workbooks
|}

=== Using other Windows applications ===
Windows applications such as [[Microsoft Access]] and Microsoft Word, as well as Excel can communicate with each other and use each other's capabilities. The most common is [[Dynamic Data Exchange]]: although strongly deprecated by Microsoft, this is a common method to send data between applications running on Windows, with official MS publications referring to it as "the protocol from hell".<ref name="Newcomer" /> As the name suggests, it allows applications to supply data to others for calculation and display. It is very common in financial markets, being used to connect to important financial data services such as [[Bloomberg L.P.|Bloomberg]] and [[Reuters]].

OLE Object Linking and Embedding allows a Windows application to control another to enable it to format or calculate data. This may take on the form of "embedding" where an application uses another to handle a task that it is more suited to, for example a [[PowerPoint]] presentation may be embedded in an Excel spreadsheet or vice versa.<ref name="Schmalz">{{cite book|title=Integrating Excel and Access|chapter=Chapter 5: Using Access VBA to automate Excel|author=Schmalz, Michael|chapter-url=https://books.google.com/books?id=SGH10fniqmsC&pg=PT71|isbn=978-0-596-00973-1|publisher=O'Reilly Media, Inc|year=2006}}{{cite book|title=Integrating Excel and Access|chapter=Chapter 5: Using Access VBA to automate Excel|author=Schmalz, Michael|chapter-url=https://books.google.com/books?id=SGH10fniqmsC&pg=PT71|isbn=978-0-596-00973-1|publisher=O'Reilly Media, Inc|year=2006}}</ref><ref name="Cornell">{{cite book|title=Excel as Your Database|author=Cornell, Paul|chapter=Chapter 5: Connect to other databases|page=[https://archive.org/details/excelasyourdatab0000corn/page/117 117 ''ff'']|chapter-url=https://books.google.com/books?id=qjkglmBy_l4C&pg=PA117|isbn=978-1-59059-751-4|year=2007|publisher=Apress|url=https://archive.org/details/excelasyourdatab0000corn/page/117}}</ref><ref name="DeMarco2">{{cite book|title=Pro Excel 2007 VBA|chapter=Excel's data import tools|page=43 ''ff''|chapter-url=https://books.google.com/books?id=gtPQMxO8XuoC&pg=PA43|publisher=Apress|author=DeMarco, Jim|isbn=978-1-59059-957-0|year=2008}}</ref><ref name="Harts">
{{cite book|title=Microsoft Office 2007 Business Intelligence: Reporting, Analysis, and Measurement from the Desktop|author=Harts, Doug|chapter-url=https://books.google.com/books?id=MVFldXVKpj8C&pg=PA171|year=2007|chapter=Importing Access data into Excel 2007|isbn=978-0-07-149424-3|publisher=McGraw-Hill Professional}}</ref>

=== Using external data ===
Excel users can access external [[Computer file|data sources]] via Microsoft Office features such as (for example) {{code|.odc}} connections built with the Office Data Connection file format. Excel files themselves may be updated using a Microsoft supplied [[ODBC]] driver.

Excel can accept data in real-time through several programming interfaces, which allow it to communicate with many data sources such as Bloomberg and Reuters (through addins such as [[Power Plus Pro]]).

* DDE: "Dynamic Data Exchange" uses the message passing mechanism in Windows to allow data to flow between Excel and other applications. Although it is easy for users to create such links, programming such links reliably is so difficult that Microsoft, the creators of the system, officially refer to it as "the protocol from hell".<ref name="Newcomer">{{Cite web|url=https://www.drdobbs.com/architecture-and-design/faking-dde-with-private-servers/184409151?pgno=3|title=Faking DDE with Private Servers|first=Joseph M.|last=Newcomer|website=Dr. Dobb's|access-date=February 17, 2023|archive-date=February 25, 2023|archive-url=https://web.archive.org/web/20230225011410/https://www.drdobbs.com/architecture-and-design/faking-dde-with-private-servers/184409151?pgno=3|url-status=live}}</ref> In spite of its many issues DDE remains the most common way for data to reach traders in financial markets.
* Network DDE Extended the protocol to allow spreadsheets on different computers to exchange data. Starting with Windows Vista, Microsoft no longer supports the facility.<ref>{{Cite web|url=https://learn.microsoft.com/en-us/windows/win32/ipc/about-network-dde|title=About Network DDE – Win32 apps|website=learn.microsoft.com|date=January 7, 2021|access-date=February 17, 2023|archive-date=February 17, 2023|archive-url=https://web.archive.org/web/20230217170348/https://learn.microsoft.com/en-us/windows/win32/ipc/about-network-dde|url-status=live}}</ref>
* Real Time Data: RTD although in many ways technically superior to DDE, has been slow to gain acceptance, since it requires non-trivial programming skills, and when first released was neither adequately documented nor supported by the major data vendors.<ref>{{Cite web|url=https://learn.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function|title=How to set up and use the RTD function in Excel – Office|website=learn.microsoft.com|date=May 5, 2022|access-date=February 17, 2023|archive-date=February 17, 2023|archive-url=https://web.archive.org/web/20230217170345/https://learn.microsoft.com/en-us/office/troubleshoot/excel/set-up-realtimedata-function|url-status=live}}</ref><ref>
{{cite book|last=DeMarco|first=Jim|title=Pro Excel 2007 VBA|year=2008|publisher=Apress|location=Berkeley, CA|isbn=978-1-59059-957-0|url=https://books.google.com/books?id=gtPQMxO8XuoC&pg=PA225|page=225|quote=External data is accessed through a connection file, such as an Office Data Connection (ODC) file (.odc)}}
</ref>

Alternatively, [[Microsoft Query by Example|Microsoft Query]] provides [[Open Database Connectivity|ODBC-based]] browsing within Microsoft Excel.<ref>
{{cite book|author1=Bullen, Stephen|author2=Bovey, Rob|author3=Green, John|name-list-style=amp|title=Professional Excel Development|year=2009|publisher=Addison-Wesley|location=Upper Saddle River, NJ|isbn=978-0-321-50879-9|url=https://books.google.com/books?id=VnegO0pMYlIC&pg=PA665|page=665|edition=2nd|quote=To create a robust solution, we always have to include some VBA code ...}}
</ref><ref>{{cite web|url=http://proc.isecon.org/2000/602/ISECON.2000.Wehrs.pdf|archive-url=https://web.archive.org/web/20100821074925/http://proc.isecon.org/2000/602/ISECON.2000.Wehrs.pdf|url-status=dead|archive-date=August 21, 2010|title=An Applied DSS Course Using Excel and VBA: IS and/or MS?|first=Wehrs|last=William|year=2000|work=The Proceedings of ISECON (Information System Educator Conference)|page=4|access-date=February 5, 2010|quote=Microsoft Query is a data retrieval tool (i.e. ODBC browser) that can be employed within Excel 97. It allows a user to create and save queries on external relational databases for which an ODBC driver is available.}}
</ref><ref>[http://office.microsoft.com/en-us/excel/HA100996641033.aspx Use Microsoft Query to retrieve external data] {{webarchive|url=https://web.archive.org/web/20100312111200/http://office.microsoft.com/en-us/excel/ha100996641033.aspx |date=March 12, 2010 }}</ref>

=== Export and migration of spreadsheets ===
Programmers have produced [[Application programming interface|APIs]] to open Excel spreadsheets in a variety of applications and environments other than Microsoft Excel. These include opening Excel documents on the web using either ActiveX controls, or plugins like the [[Adobe Flash|Adobe Flash Player]]. The [[Apache POI]] [[open-source]] project provides [[Java platform|Java]] libraries for reading and writing Excel spreadsheet files.

=== Password protection ===
{{Main|Microsoft Office password protection}}

Microsoft Excel protection offers several types of passwords:
* Password to open a document<ref>{{cite web|url=http://office.microsoft.com/en-us/word-help/password-protect-documents-workbooks-and-presentations-HA010148333.aspx#BM1|title=Password protect documents, workbooks, and presentations|publisher=Office.microsoft.com|access-date=April 24, 2013|archive-date=February 12, 2014|archive-url=https://web.archive.org/web/20140212134311/http://office.microsoft.com/en-us/word-help/password-protect-documents-workbooks-and-presentations-HA010148333.aspx#BM1|url-status=live}}</ref>
* Password to modify a document<ref>{{cite web|url=http://office.microsoft.com/en-us/word-help/password-protect-documents-workbooks-and-presentations-HA010148333.aspx#BM3a|title=Password protect documents, workbooks, and presentations|publisher=Office.microsoft.com|access-date=April 24, 2013|archive-date=February 12, 2014|archive-url=https://web.archive.org/web/20140212134311/http://office.microsoft.com/en-us/word-help/password-protect-documents-workbooks-and-presentations-HA010148333.aspx#BM3a|url-status=live}}</ref>
* Password to unprotect the worksheet
* Password to protect workbook
* Password to protect the sharing workbook<ref>{{cite web|url=http://office.microsoft.com/en-us/excel-help/password-protect-worksheet-or-workbook-elements-HP005201059.aspx|title=Password protect worksheet or workbook elements|publisher=Office.microsoft.com|access-date=April 24, 2013|url-status=dead|archive-url=https://web.archive.org/web/20130326192849/http://office.microsoft.com/en-us/excel-help/password-protect-worksheet-or-workbook-elements-HP005201059.aspx|archive-date=March 26, 2013}}</ref>
All passwords except ''password to open a document'' can be removed instantly regardless of the Microsoft Excel version used to create the document. These types of passwords are used primarily for shared work on a document. Such password-protected documents are not [[encrypted]], and data sources from a set password are saved in a document's [[Header (computing)|header]]. ''Password to protect workbook'' is an exception – when it is set, a document is encrypted with the standard password "VelvetSweatshop", but since it is known to the public, it actually does not add any extra protection to the document. The only type of password that can prevent a trespasser from gaining access to a document is the ''password to open a document''. The cryptographic strength of this kind of protection depends strongly on the Microsoft Excel version that was used to create the document.

In ''Microsoft Excel 95'' and earlier versions, the password to open is converted to a 16-bit [[Key (cryptography)|key]] that can be instantly cracked. In ''Excel 97/2000'' the password is converted to a 40-bit key, which can also be cracked very quickly using modern equipment. As regards services that use [[rainbow tables]] (e.g. Password-Find), it takes up to several seconds to remove protection. In addition, password-cracking programs can [[brute-force attack]] passwords at a rate of hundreds of thousands of passwords a second, which not only lets them decrypt a document but also find the original password.

In ''Excel 2003/XP'' the encryption is slightly better – a user can choose any encryption algorithm that is available in the system (see [[Cryptographic Service Provider]]). Due to the CSP, an ''Excel'' file cannot be decrypted, and thus the ''password to open'' cannot be removed, though the brute-force attack speed remains quite high. Nevertheless, the older ''Excel 97/2000'' algorithm is set by the default. Therefore, users who do not change the default settings lack reliable protection of their documents.

The situation changed fundamentally in ''Excel 2007'', where the modern [[Advanced Encryption Standard|AES]] algorithm with a key of 128 bits started being used for decryption, and a 50,000-fold use of the hash function [[SHA1]] reduced the speed of brute-force attacks down to hundreds of passwords per second. In ''Excel 2010'', the strength of the protection by the default was increased two times due to the use of a 100,000-fold SHA1 to convert a password to a key.