How To Create User Interface In Excel

The rectangular grid of rows and columns described in Excel Spreadsheets is only one part of the Excel user interface. The entire interface is as follows:

Excel User Interface

Figure 1 – Excel User Interface

This is the layout used in Excel 2007. The layout in Excel 2010 and Excel 2013 and later versions of Excel are almost identical. The key components are as follows:

Title Bar – contains the name of the workbook. The default is Book1 (and then Book2, etc.). This is replaced by the filename once the Excel workbook is saved.

Worksheet Tabs – a list of all the worksheets in the workbook. By default, these are labeled Sheet1, Sheet2, etc. You can navigate to any worksheet in the workbook by clicking on that worksheet tab. You can also use the four small arrows to the left of the worksheet tabs for navigation purposes. The first arrow is used to go to the first worksheet, the second to go to the previous worksheet, the third to go to the next worksheet and the fourth to go to the last worksheet. You can change the name of any of the worksheets by doubling clicking on its tab and then entering a new name. You can add a new worksheet by clicking on the rightmost worksheet tab icon . You can also change the order of the worksheets in the list by left-clicking on a worksheet tab and dragging it to a new location in the list. You can access other capabilities by right-clicking on any of the worksheet tabs or the worksheet tab arrows.

Ribbon Tabs – the top-level menu items. In the example above this consists of Home, Insert, Page Layout, Formulas, etc. The actual choices can change depending on the state that you are in. To access most capabilities in Excel you click on one of these ribbon tabs. For each tab, a different ribbon will be displayed. In Figure 1 the Home ribbon is displayed. This tab provides access to the most common Excel capabilities.

Ribbon – a collection of Excel capabilities organized into groups corresponding to some ribbon tab. For example, the Home ribbon displayed in Figure 1 is organized into the Clipboard, Font, Alignment, Number, etc. groups. Each group consists of one or more icons corresponding to some capabilities in Excel. For example, to center the content of a cell in a worksheet, click on that cell and then click on the center icon in the Alignment group on the Home ribbon. We use the following abbreviation for this sequence of steps: Home > Alignment|Center.

In a similar manner, you can merge two neighboring cells by highlighting the two cells and selecting Home > Alignment|Merge & Center; the two cells are combined and any content placed in the merged cell will be centered. Also, cells, rows, columns and worksheets can be inserted, deleted and formatted using Home > Cells.

There are also shortcuts for some icons. E.g., to center the contents of a cell, you can click on that cell and then enter Ctrl-E.

To get some idea of the purpose of an icon, place the mouse pointer over that icon (without clicking) and a tooltip will appear to provide some information about the icon.

Some of the groups on a ribbon are accompanied by a small arrow (to the right of the name of the group). When you click on this arrow you will be presented with a dialog box that provides you with various options to choose from. E.g. clicking on the arrow for the Font group on the Home ribbon brings up a dialog box with tabs labeled Number, Alignment, Font, Border, etc. Each tab in the dialog box presents you with a different set of options for formatting the range of cells that are currently highlighted in the worksheet. For example, to specify that you want numbers in the highlighted cells to be displayed with 3 decimal places, you select the Number tab and then the Number option and finally fill in 3 in the box specifying the number of decimal places.

Some icons within a group are also accompanied by a small downward arrow. When you click on this arrow you will be presented with a vertical list of options. E.g. clicking on the Insert icon in the Cells group in the Home ribbon brings up the choices Insert Cells…, Insert Sheet Rows, Insert Sheet Columns, Insert Sheet.

Some groups also contain scrollable drop-down lists accompanied by a downward arrow. E.g. clicking on the arrow to the right of the Font drop-down list in the Font group on the Home ribbon, presents a scrollable list of available fonts (Arial, Time New Roman, etc.) to choose from.

Office Button – the icon in the upper left side of the Excel 2007 interface that allows you to open, save and print workbooks. When you click on this icon you will be presented with a menu of options. In addition to opening, saving and printing workbooks, there is a button called Excel Options. Clicking on this button displays a dialog box that offers you the ability to change various configuration parameters. It also contains the Add-In option that we will describe later.

Excel 2010 and later versions of Excel do not use the Office Button. Instead, they provide the same functionality using the File tab. The File tab is the first ribbon tab in versions of Excel starting with Excel 2010 and is located to the left of the Home tab.

Quick Access Toolbar – contains frequently used icons and is located in the upper left-hand corner of the display (just to the right of the Office Button in Excel 2007 and above the File and Home tabs in versions of Excel starting with Excel 2010). Initially, the toolbar contains the Save, Repeat and Undo icons. You can add or delete icons from this toolbar by clicking on the small downward arrow at the right end of the toolbar to display a customization dialog box.

Active Cell – displays the currently referenced cell. This is the cell which you last clicked on with the mouse or moved to. This cell is highlighted on the display.

Name Box – contains the address of the active cell. You can navigate to another cell simply by entering the address of that cell in the Name Box and pressing the Enter key.

Formula Bar – contains the contents of the active cell. When this is a formula, the formula appears here while the value of the formula appears in the cell. You can optionally click on the f_x symbol located just to the left of the Formula Bar to bring up a dialog box that helps you find the appropriate function as well as the arguments for this formula.

Vertical/Horizontal Split Controls – used to split the worksheet. The vertical split control is a small rectangular box located just above the vertical scroll bar. If you move the control downward, the display of the worksheet splits in two so that you can see two different parts of the worksheet at the same time. If you move the control back to its original position the two parts reunite and only one view of the worksheet is displayed.

The horizontal split control is located just to the right of the horizontal scroll bar and works in a similar manner. If you move the control to the left the worksheet display splits horizontally into two parts.

Status Bar – contains certain information, including by default the sum, count and average of any highlighted range. It also contains the zoom and zoom slider, which are used to increase or decrease the size of the worksheet display. You can customize what information appears on the status bar by right-clicking on it to display a customization dialog box.