26 Jun

8 INSIGHTS ON MICROSOFT OFFICE TECHNOLOGY®-(IOMOT)

-By Steven K.M.B Bonsoe – MOSM


This article was written with the most current versions of Microsoft Excel (versions 2016 & 2013) in mind deployed on Windows Operating Systems. However, the principles and concepts herein are applicable to all other versions of Microsoft Excel (97 -2003, 2007, 2010) as well as other spreadsheets like Google Sheets.

Introduction

Many Excel users did not receive a formal spreadsheet orientation before beginning to use Microsoft Excel which is the most popular spreadsheet program in the world. These end users generally lack knowledge, competency, proficiency and mastery of Microsoft Excel. When using Excel, they often call experienced spreadsheet users for help to troubleshoot very common problems. Even their “go-to” resource, who may be acclaimed “Excel gurus”, may offer unprofessional spreadsheet advice and collaborate in creating spreadsheet models that contain very serious spreadsheet errors all because they did not also receive a formal spreadsheet orientation.

My exposure and experience with spreadsheets confirm that spreadsheet productivity and efficiency begin with a formal spreadsheet orientation. Such an orientation is formal in the sense of its structure, content and methodology. For instance, a formal spreadsheet orientation to Microsoft Excel should likely focus on providing a conceptual understanding of workbook and worksheet structure and a step-by-step tour of the Microsoft Excel User Interface (UI).

Managing the active cell is one of the most important topics when learning workbook structure and worksheet structure.  In this article, we will discuss the very essence of the active cell and consider why keeping an eye on the active cell remains a key technique in unlocking and troubleshooting spreadsheet problems in Excel.

Overview of Workbook and Worksheet Structure

It is noteworthy that keeping an eye on the active cell begins with a conceptual understanding of workbook and worksheet structure Microsoft Excel files are called workbooks. A workbook is a collection of one or more related worksheets contained within a single file. Each workbook contains at least one worksheet and can house multiple worksheets. Excel versions 2007 to 2016 have a .xlsx file extension. Prior versions such as 97-2003 use a .xls file extension. Each workbook has a default workbook name [Book1] displayed in the title.

A worksheet is a collection of cells, columns and rows. Cells are formed when a grid of vertical and horizontal lines converge to form boxes for storing related data.  Technically, each worksheet column is actually a collection of cells that starts at the top of the worksheet and goes to the bottom of the worksheet and is identified by a letter. Each row, on the other hand, is a collection of cells which starts at the left edge of the worksheet and continues to the right and is identified by a number. Each box, or cell, on the grid is identified by the intersection of a column and a row. Thus, the first cell in an open worksheet is A1.

Figure 1-Cell A1 is selected. Note that the Exploring Workbook and Worksheet Structure

In Microsoft Excel, the active cell is the selected worksheet cell. It is variously called the current cell or highlighted cell. In previous versions of Excel (97-2003, 2007, 2010), there was a bold black border around the active cell. Current versions 2013 and 2016 place a green border around the active cell. Figure 2 shows a green border around cell B3 which is the active cell. Note that a small dot or period is placed in the lower right-hand corner of the active cell which is called AUTOFILL handle. Positioning the mouse pointer on the AUTOFILL handle and dragging it fills cells with the content placed in the active cell.

Figure 2-Active cell B3 has a green border. Note that the column letter B and row number 3 are highlighted.

When a blank workbook is created from scratch, the active cell is A1. When an existing workbook is closed and reopened, the active cell remains the last selected cell given the active worksheet. It is worthy to note that only one single cell can serve as the active cell at any given point in time. Even though a range or collection of cells may be selected, only one cell can serve as the active cell at that point in time. In Figure 3, cell range C3:F11 is selected but only cell C5, which has a white fill color or shading, is displayed in the Name Box as the active cell.

Figure 3- Cell C5 is the only active cell though a range of cells has been selected.

Why Keep an Eye on the Active Cell?

Keeping an eye on the active worksheet cell is one of the most efficient techniques for preventing or detecting spreadsheet errors and reducing spreadsheet inefficiencies. For instance, commands on Excel’s Ribbon, may be grayed out because the active cell is not in Ready mode. Figure 4 shows that the Status bar is in Edit mode and for that matter, many commands on the HOME tab on the Ribbon are grayed out or inactive. A good understanding of how to manage the active worksheet cell therefore enhances an end user’s troubleshooting capabilities in spreadsheets.

Figure 4-Commands on Excel’s tabbed Ribbon-system are grayed out because Status bar is in Edit mode.

Cell Address, Cell Content and Cell Mode

Keeping an eye on the active cell basically involves paying attention to the cell address, content and status of the active worksheet cell. The cell address is the combination of the column letter and the row number. For example, when the active cell is A1, it means that the active cell is at the intersection of column A and row 1. On the other hand, the active cell can contain a variety of content such as text, numbers, dates, time, formulas and functions, links, comments and formatting attributes. Also, the cell mode of the active cell describes the status of the active cell. Ready mode is the default cell mode of the active cell. Other cell modes of the active cell include Enter, Edit and Point modes.

Figure 5 depicts the three key attributes to keep an eye on when working with the active cell.

Figure 5-The Impact of The Name Box, Formula Bar and Status Bar on an Active Cell

The Name Box, Formula Bar and Status Bar

Excel provides three (3) powerful tools to help end users keep an eye on the active cell when working in the Excel window. These tools are the Name Box, Formula Bar and Status Bar. The Name Box, Formula Bar and Status Bar manage the cell address, cell content and cell mode respectively. A special relationship therefore exists between the Active Cell and these powerful Excel Tools. Figure 6 below depicts the impact of the Name Box, Formula Bar and Status Bar on active cell management and how they help end users keep an eye on the active cell.

Figure 6-Three key things to keep an eye on when working with the active cell.

The Name Box and the Cell Address.

Remember that each worksheet cell is the intersection of a column letter and a row number. It is the combination of this column letter and row number that identifies the cell with an address. When a cell is made active, its cell address is displayed in the Name Box. The Name Box comes with the Formula bar and is an identifier that displays the address of the active cell. No two cell addresses can be displayed simultaneously in the Name Box. The Name Box has varied uses. Apart from displaying the cell address of the active cell, it can be used to assign or define a name to one or more cells creating a named range. In addition, the Name Box can serve as a navigator in selecting worksheet cells or ranges. This is similar to using Excel’s GO TO functionality to “go to” a particular cell or named ranged.

Figure 7-The Name Box contains B3 which is the cell address of the active cell

The Formula Bar and Cell Content

The Formula bar is a powerful toolbar for creating and displaying worksheet formulas and functions. Its very essence is to search and insert formulas and functions into worksheet cells. The Formula bar can be expanded if need be to fully display long formulas in the active worksheet cell.

Figure 8-The Formula Bar can be expanded

The Formula bar may be also used to display, insert and edit a variety of cell content in the active cell. Cell content includes text, numbers, dates, time, formulas and functions. In Figure 9, the active cell A1 displays the text string Revenues in the Formula bar which is similar to the actual cell content in cell A1. Figure 10 also displays the number 10000 both in the Formula bar and cell B1.

Figure 10-Formula Bar displays a number

Figure 11 demonstrates how a formula can be entered directly into a cell (B3) while it is simultaneously displayed in the Formula Bar.

Figure 11-Formula bar displays cell content as formula is entered into cell

However, note that in Figure 12, the content in the Formula bar differs from the content in cell B3. This difference in content reveals that when displaying cell content, the Formula bar will give precedence to showing the formula or function “behind the scene” rather than the “face value” of the active cell (calculated result of the formula). In this case, the face value of the active cell B3 which is 3000 is the result of the formula 10000-7000. Apart from its use to search and insert formulas and functions, this phenomenon explains why this toolbar bears the name “Formula” bar because its primary purpose is to display formulas anytime they are present in a worksheet.

Figure 12-Formula Bar displays formula (=10000-7000) instead of calculated result which is 3000.

One of Excel’s Formula Auditing tools is to display all formulas in a worksheet using CTRL + ~ or the Show Formulas command in the Formulas Auditing group under the FORMULAS tab on the Ribbon.

The Status Bar and Cell Mode

The Status bar which is located below the worksheet area and above the Windows taskbar, displays information about a selected command or operation in progress. For instance, it displays select destination and press ENTER or choose Paste after using the Copy command. The Status bar also houses worksheet view controls such as Normal View, Page Layout View, Page Break command buttons as well as the Zoom Slider. Status bar periodically displays numerical indicators when working with selected cells and ranges containing data.

Figure 13-The Status Bar

The primary function of the Status Bar is to tell us the status or mode of the active cell. The active cell can be in Ready, Enter, Edit and Point modes.

Ready mode indicates that the active cell is ready for any commands or procedures.

Figure 14-Status Bar displaying Ready mode

Enter mode indicates that cell content is being typed into the active cell.

Figure 15-Status Bar displaying Enter mode

Edit mode indicates that changes are being made to the cell content in the active cell.

Figure 16-Status Bar displaying Edit mode

Point mode displays when Point-and-Click Method is used to insert formulas to calculate cell values.

Figure 17-Status Bar displaying Point mode

The Status Bar also displays ongoing procedures and provides information or instructions on how to execute and complete ongoing tasks on the worksheet. For instance, Figure 18 shows how the Status Bar provides information on how to complete a Copy procedure by selecting a destination and pressing the Paste command.

Figure 18-Status Bar provides info on how to complete Copy and Paste procedure

Some Troubleshooting Tips

1. CHECK THE NAME BOX for the cell address of the active cell when multiple cells (range) are selected or highlighted. The active cell is the cell with a white color shading in the selected cells.

Figure 19-The Name Box indicates that B5 is the active cell and it is shaded white

2. Avoid the error of overwriting existing formulas in the active cell by looking intently at the Formula bar and    comparing the content in the Formula bar with the cell content displayed on the face of the active cell.

Figure 20-Formula Bar displays formula (=10000-7000) instead of calculated result which is 3000

3. When most commands on Excel’s Ribbon are grayed out, check the Status Bar to know the mode of the active cell. All commands on the Ribbon become active when the active cell is in Ready mode. Pressing the ESC key on the keyboard repeatedly is the best technique for resuming the active cell to Ready mode, especially if worksheet cells are not protected.

Figure 21-Ribbon commands are grayed out because Status Bar is in Edit mode

Conclusion

We have discussed and demonstrated the very essence of Excel’s active cell and how to keep an eye on it through the lenses of the Name Box, Formula Bar and Status Bar which provide information about cell address, cell content and cell mode respectively. Remember that keeping an eye on the active cell in Microsoft Excel begins with a conceptual understanding of workbook and worksheet structure. Also, a good understanding of the active cell is a prerequisite for the complexities that are inherent in using Visual Basic for Applications (VBA), which is Excel’s programming language for managing Excel objects.