Microsoft Excel
Microsoft Excel Features and Functions
- Developed by Microsoft for Windows, macOS, Android, iOS, and iPadOS
- Offers calculation and computation capabilities
- Includes graphing tools
- Supports pivot tables
- Features a macro programming language called Visual Basic for Applications (VBA)
- Excel 2016 has 484 functions
- 360 functions existed prior to Excel 2010
- Functions are classified into 14 categories by Microsoft
- 386 functions can be called from VBA as methods of the object WorksheetFunction
- 44 functions have the same names as VBA functions
Macro Programming with VBA
- VBA programming allows spreadsheet manipulation that is awkward or impossible with standard techniques
- VBA code is written using the Visual Basic Editor (VBE)
- VBA code can automate tasks like formatting and data organization
- VBA code interacts with the spreadsheet through the Excel Object Model
- VBA was removed from Mac Excel 2008 but restored in Mac Excel 2011
Charts and Graphs
- Excel supports charts, graphs, and histograms generated from specified groups of cells
- Pivot Charts can be linked directly to a Pivot table for dynamic updates
- Charts can be embedded within the current sheet or added as separate objects
- Charts can assist in visualizing design requirements and selecting the best design
- Excel also supports the creation of dynamic and interactive charts
File Formats in Microsoft Excel
- Excel is backwards-compatible with traditional binary formats
- Most versions of Excel can read CSV, DBF, SYLK, DIF, and other legacy formats
- Support for some older file formats was removed in Excel 2007
- OpenOffice.org has created documentation of the Excel format
- Two epochs of the format exist: the 97-2003 OLE format and the older stream format
- XML Spreadsheet format was introduced in Excel 2002
- It is a simple, XML-based format missing some advanced features like storage of VBA macros
- The intended file extension for this format is .xml, but it also handles .xls files correctly
- Excel 2007 introduced new file formats
- The default format is .xlsx, a ZIP compressed archive with XML text documents
- .xlsx is the primary replacement for the former binary .xls format
- .xlsm is an Excel Macro-enabled Workbook with macro support
- .xlsb is an Excel Binary Workbook that stores information in binary form for faster document handling
- .xls is the main spreadsheet format that holds data, charts, and macros
- .xla is an add-in (VBA) that adds custom functionality
- .xlb is the file extension for custom toolbar settings
- .xlc is a chart created with data from an Excel spreadsheet
- .xld is used in older versions of Excel
Using Excel with Other Applications and External Data
- Windows applications like Access, Word, and Excel can communicate and use each other's capabilities
- Dynamic Data Exchange (DDE) is a common method to send data between applications on Windows
- OLE Object Linking and Embedding allows one application to control another for formatting or calculating data
- Financial markets often use DDE to connect to important financial data services
- Applications can be embedded in each other, like a PowerPoint presentation embedded in an Excel spreadsheet
- Excel users can access external data sources through Office features like .odc connections
- Excel can accept real-time data through programming interfaces and add-ins like Power Plus Pro
- Dynamic Data Exchange (DDE) allows data flow between Excel and other applications
- Network DDE extended the protocol to exchange data between spreadsheets on different computers
- Real Time Data (RTD) is technically superior to DDE but has been slow to gain acceptance
- APIs have been developed to open Excel spreadsheets in various applications and environments
- ActiveX controls and plugins like Adobe Flash Player can open Excel documents on the web
- The Apache POI project provides Java libraries for reading and writing Excel spreadsheet files.