Module B5: Elaborating Outputs for Better Presentation

1. Microsoft Excel 2007: Basics

Microsoft Excel is a popular spreadsheet software package. The new results-oriented user interface, which was introduced in Microsoft Excel 2007, makes easier to work in Excel. In Excel 2007, commands and features are organized on task-oriented tabs that contain logical groups of commands and features.
Since the user interface of Excel 2007 is very different to that of earlier versions, even regular users need to familiarize themselves with its new features and looks.

1.1     Result-Oriented User Interface

Layout of the main menu and the contents of the first menu tab ‘Home’ are as follow:

Many dialog boxes have been replaced with drop-down galleries that display the available options. Descriptive tool-tips or sample previews are provided to help users choose the right option. If, for example, you click on ‘Paste’, Excel displays a drop-down list of galleries with active options depending on which items are available in the clipboard.

The Office Clipboard can store up to 24 items. If the mouse is on the ‘’icon, which is located at the bottom right corner of the ‘Paste’ menu, ‘instant help’ for the Clipboard will be displayed. If the mouse pointer hovers over ‘Paste’, the tool-tip will be displayed as following.

If the Clipboard area, which is located at the bottom of the ‘Paste’ menu, is clicked a clipboard pane will be displayed to show all the items in the Clipboard that can be pasted into the document.

Microsoft provides additional online help for the clipboard.

For every activity being performed in the new user interface – whether it is formatting or analyzing data – Excel provides the tools, tips and help that are most useful for accomplishing the tasks.

1.2     New File Formats in Microsoft Excel 2007

The previous versions of Excel files (from Excel 2.1 to Excel 2003) use‘.xls’ for Excel (data) files, ‘.xla’ for add-ins, and ‘.xlt’ for templates. Excel files with extension ‘.xls’ can store data sheets, chart sheets and micro sheets. In Excel 2003, ‘.xml’ is used for XML-based[1] spreadsheet or data files. In Office Excel 2007, the following formats and file extensions are used to distinguish different file types. These new file types also offer improved security.

Excel Workbook .xlsx The default Office Excel 2007 XML-based file format. It cannot store VBA macro code or Microsoft Excel 4.0 macro sheets (.xlm).

Excel Workbook (code) .xlsm The Office Excel 2007 XML-based and macro-enabled file format.
It stores VBA macro code or Excel 4.0 macro sheets (.xlm).

Excel Binary Workbook .xlsb The Office Excel 2007 Binary file format (BIFF12).

Template .xltx The default Office Excel 2007 file format for an Excel template.
It cannot store VBA macro code or Excel 4.0 macro sheets (.xlm).

Template (code) .xltxm The Office Excel 2007 macro-enabled file format for an Excel template.
It stores VBA macro code or Excel 4.0 macro sheets (.xlm).

Excel Add-In .xlam The Office Excel 2007 XML-based and macro-enabled Add-In, a supplemental program that is designed to run additional code.
It supports the use of VBA projects and Excel 4.0 macro sheets (.xlm).

The following file types (or filename extensions) of previous versions of Excel are still valid Excel files in Office Excel 2007. They can be opened or saved without converting them into 2007 format.

Excel 97-2003 Workbook .xls The Excel 97 – Excel 2003 Binary file format (BIFF8).

Excel 97-2003 Template .xlt The Excel 97 – Excel 2003 Binary file format (BIFF8) for an Excel template.

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.

Any Excel file which has been created in an earlier version of Excel can be opened and saved in the original file type. However, to open Excel 2007 format files in earlier versions of Excel, optional file filters must first be installed in the earlier version of Excel.

1.3     Data Handling Capacity

Excel 2007 allows us to explore large amounts of data in worksheets. Excel 2007 supports 1,048,576 rows by 16,384 columns per worksheet (or 234, i.e., 17 billion cells). This is the equivalent of one million cases with sixteen thousand variables each. Since no household survey data set will ever be this large, we can be sure that any household survey data set can be imported into Excel where we can conduct further analyses in an environment that is more familiar to most education planners and administrators.

As seen above, the Excel 2007 Worksheet is 1024 times larger than an Excel 2003 worksheet. Although Excel 2007 files can be opened in Excel 2003, the contents of Excel 2007 worksheets that are located outside the Excel 2003 boundaries (65,536 rows x 256 columns) cannot be loaded into Excel 2003.

There are several other important improvements in Excel 2007.

  1. An unlimited number of formatting types can be used in Excel 2007, compared to 4,000 in Excel 2003.
  2. The number of cells that can be referenced by a formula in a single cell has increased from eight thousand to as many references as can be stored in system memory.
  3. Maximum memory support has been increased from 1 GB to 2 GB.
  4. Excel 2007 supports up to 16 million colors.
  5. Excel 2007 supports dual-processors and multithreaded chipsets.

These enhancements have improved the general performance of Excel, especially when using computers that have advanced chipsets, and calculations are much faster in large, formula-intensive worksheets.

1.4     Selected Statistical Functions

There are altogether 346 built-in functions under 12 different categories in Excel 2007 including 82 Statistical functions.

The choice of Excel functions in analyzing household survey data depends on the experience of the user and the types of output needed to generate. Below is a list of functions that may be most useful for analysing a database or refining the SPSS Statistics output tables:

Detailed descriptions of these functions and examples are available in the online help of Microsoft Excel 2007, and will not be described in detail here.


Comments are closed.