All posts by Jeff Rummel
Spreadsheet menus
The user interface for Excel looks like most modern software, with the capabilities organized into menus. These menus are displayed in a few different ways, and the organization of the menus varies from one version of Excel to another. This is also true in the most current versions of Excel for Windows and for MacOS – some things are similar, but much of the organization is different (to “feel” like that computer’s general interface). There are also numerous context menus in Excel that are accessed with a right-click of the mouse. Depending on where the mouse is and what you are doing, Excel tries to provide a smaller menu with common tasks. Here are the major menu tabs, but Excel also provides the ability to add more menu tabs, so your installation of Excel may have these extra add-ins.
The File menu
This tab controls file operations (open and save), printing and the options for how Excel is configured.
Add the Analysis Toolpack to the Data menu
By default, Excel does not install the Analysis Toolpack to the Data menu. This can be fixed easily with the add-in manager, that is found in the File menu.
The Home menu
This tab has commands for editing, fonts, alignment, number formats, styles and some more sophisticated editing features for a spreadsheet, such as sorting and filtering.
Filter a database
Turning on the filter for a database allows sorting and filtering that is helpful in investigating the details of a data set.
The Insert menu
This table is where pivot tables are created and where the drawing layer is accessed, for charts and for drawing. Hyperlinks and symbols are also created with this menu.
Scatterplots
The Chart Menu has commands for creating scatterplots. One type of scatterplot just displays the data in the order it appears in the range.
There is also a way to create a scatterplot that relates two columns of data – an XY scatterplot.
The Page Layout menu
This tab allows for the spreadsheet to be formatted for printing. There is also controls for themes and for arranging objects in the drawing layer.
The Formulas menu
This tab has menus for all the formulas built into Excel. Cell references (defined names) can be controlled here. There are also commands for checking for formula errors and recalculating the spreadsheet.
The Data menu
This tab has commands to connect an Excel spreadsheet to external data sources for reporting and graphing that data. There are also commands for sorting, filtering, outlining and data cleaning. Some of the more powerful and useful features for analysis are also located in this menu tab.
Descriptive statistics
The Analysis Toolpack has a command to create the descriptive statistics for a data set.
Histogram Tool
In the analysis toolpack, there is a command that will create a histogram.
The Review menu
This tab has proofing and language commands and also allows comments to be placed on cells. Spreadsheets can also be protected and shared from this menu.
The View menu
This tab allows you to alter the view of the spreadsheet on the screen (while Page Layout controls printing). This is also where access to macros is located.
Spreadsheet calculations
Once there are values in a spreadsheet, those values can be manipulated. The typical manipulation starts with an equal sign, and then the details of the calculation – most simply addition, subtraction, multiplication (using the star) and division (using the slash). These simple calculations can then be extended using these features.
Excel built-in functions
Excel has a library with many, many functions. A function consists of the name, an open parenthesis, the parameters required and then close parenthesis. For example, =AVERAGE(1,4,10) would calculate 5, the average of the three numbers.
Create a histogram with the frequency function
Instead of using the Analysis Toolpack histogram command, a histogram can also be created using the =FREQUENCY() function.
Macros
You have the ability to write your own functions or to automate tasks using the macro language built into Excel. Macros are written in VBA (visual basic for applications), but there are compatibility issues between the Windows and OS/X versions of Excel.
Add-ins for Excel
Even more complex features can be added to Excel with the use of add-ins. Some are provided with Excel but not installed, so if you have a new copy of Excel, you will have to learn a little about add-ins. There are also commercial extentions, and one that is used at the Goizueta Business School is the Decision Tools Suite from Palisades Software. This add-in provides a number of very useful tools (optimization, simulation), but is not offered in a version compatible with Mac OS/X.