Excel Day 2 Training Workbooks, worksheets, cells, Rows and columns
Object Model of Excel (Hierarchy)
>Application
>Workbook
>Worksheet
>Range/Charts/Shapes/Tables/Forms
Excel Vocabulary
Cell = Intersection of Column & Row
Worksheet = Sheet = all the cells in one sheet
Workbook = file = all the Sheets
In Excel, we have different versions. Each version has multiple updates, but we should consider one important update which is the change in numbers of rows and columns in Excel.
Below table tells us how many rows and columns for different versions of excel.
| Version | Rows | Columns | Last Column id |
| 97-2003 | 65,536.00 | 256.00 | IV |
| 2007 | 1,048,576.00 | 16,384.00 | XFD |
| 2010 | 1,048,576.00 | 16,384.00 | XFD |
| 2013 | 1,048,576.00 | 16,384.00 | XFD |
| 2016 | 1,048,576.00 | 16,384.00 | XFD |
| How many cells we have in Excel 2016? |
| Total Rows*Total Columns=Total Excel Cells |
| 1,048,576*16,384=17,179,869,184.00 |
Excel can be saved in multiple formats
1.Excel file formats
2.Text file formats
3.Other formats
Source: https://support.office.com/en-us/article/save-a-workbook-in-another-file-format-6a16c862-4a36-48f9-a300-c2ca0065286e
Excel file formats
|
Format |
Extension |
Description |
|
Excel Workbook |
.xlsx |
The default XML-based file format for Excel 2007-2013. Cannot store Microsoft Visual Basic for Applications (VBA) macro code or Microsoft Office Excel 4.0 macro sheets (.xlm). |
|
Strict Open XML Spreadsheet |
.xlsx |
An ISO strict version of the Excel Workbook file format (.xlsx). |
|
Excel Workbook (code) |
.xlsm |
The XML-based and macro-enabled file format for Excel 2007-2013. Stores VBA macro code or Excel 4.0 macro sheets (.xlm). |
|
Excel Binary Workbook |
.xlsb |
The binary file format (BIFF12) for Excel 2007-2013. |
|
Template |
.xltx |
The default file format for an Excel template for Excel 2007-2013. Cannot store VBA macro code or Excel 4.0 macro sheets (.xlm). |
|
Template (code) |
.xltm |
The macro-enabled file format for an Excel template in Excel 2007-2013. Stores VBA macro code or Excel 4.0 macro sheets (.xlm). |
|
Excel 97- Excel 2003 Workbook |
.xls |
The Excel 97 – Excel 2003 Binary file format (BIFF8). |
|
Excel 97- Excel 2003 Template |
.xlt |
The Excel 97 – Excel 2003 Binary file format (BIFF8) for an Excel template. |
|
Microsoft Excel 5.0/95 Workbook |
.xls |
The Excel 5.0/95 Binary file format (BIFF5). |
|
XML Spreadsheet 2003 |
.xml |
XML Spreadsheet 2003 file format (XMLSS). |
|
XML Data |
.xml |
XML Data format. |
|
Excel Add-In |
.xlam |
The XML-based and macro-enabled Add-In format for Excel 2007-2013. An Add-In is a supplemental program that is designed to run additional code. Supports the use of VBA projects and Excel 4.0 macro sheets (.xlm). |
|
Excel 97-2003 Add-In |
.xla |
The Excel 97-2003 Add-In, a supplemental program that is designed to run additional code. Supports the use of VBA projects. |
|
Excel 4.0 Workbook |
.xlw |
An Excel 4.0 file format that saves only worksheets, chart sheets, and macro sheets. You can open a workbook in this file format in Excel 2013, but you cannot save an Excel file to this file format. |
Text file formats
| Format | Extension | Description |
| Formatted Text (Space-delimited) | .prn | Lotus space-delimited format. Saves only the active sheet. |
| Text (Tab-delimited) | .txt | Saves a workbook as a tab-delimited text file for use on another Microsoft Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. |
| Text (Macintosh) | .txt | Saves a workbook as a tab-delimited text file for use on the Macintosh operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. |
| Text (MS-DOS) | .txt | Saves a workbook as a tab-delimited text file for use on the MS-DOS operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. |
| Unicode Text | .txt | Saves a workbook as Unicode text, a character encoding standard that was developed by the Unicode Consortium. |
| CSV (comma delimited) | .csv | Saves a workbook as a comma-delimited text file for use on another Windows operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. |
| CSV (Macintosh) | .csv | Saves a workbook as a comma-delimited text file for use on the Macintosh operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. |
| CSV (MS-DOS) | .csv | Saves a workbook as a comma-delimited text file for use on the MS-DOS operating system, and ensures that tab characters, line breaks, and other characters are interpreted correctly. Saves only the active sheet. |
| DIF | .dif | Data Interchange Format. Saves only the active sheet. |
| SYLK | .slk | Symbolic Link Format. Saves only the active sheet. |
Note: If you save a workbook in any text format, all formatting is lost.
Other file formats
| Format | Extension | Description |
| DBF 3, DBF 4 | .dbf | dBase III and IV. You can open these files formats in Excel, but you cannot save an Excel file to dBase format. |
| OpenDocument Spreadsheet | .ods | OpenDocument Spreadsheet. You can save Excel 2010 files so they can be opened in spreadsheet applications that use the OpenDocument Spreadsheet format, such as Google Docs and OpenOffice.org Calc. You can also open spreadsheets in the .ods format in Excel 2010. Formatting might be lost when saving and opening .ods files. |
| Portable Document Format (PDF). This file format preserves document formatting and enables file sharing. When the PDF format file is viewed online or printed, it retains the format that you intended. Data in the file cannot be easily changed. The PDF format is also useful for documents that will be reproduced by using commercial printing methods. | ||
| XPS Document | .xps | XML Paper Specification (XPS). This file format preserves document formatting and enables file sharing. When the XPS file is viewed online or printed, it retains exactly the format that you intended, and the data in the file cannot be easily changed. |
