The  Anatomy of a Cell

A spreadsheet is a series of vertical arrangements called columns and horizontal arrangements called rows. As a list is made of columns and rows, they intersect. The intersection of a column and a row creates a small box called a cell:  

When Microsoft Excel starts, it creates 16,384 columns and 1,048,576 rows. As a result, a Microsoft Excel spreadsheet has 16,384 * 1,048,576 = 17,179,869,184 cells available.

To access a cell, you can use the mouse or the keyboard. You can click a cell or press an arrow key. You can also right-click a cell. This would display a multi-part menu:

When you use a spreadsheet, for exameple when you enter information in it, you are actually working on a cell. Therefore, at any time, you must always know what cell you are accessing. That is, you must always be able to identify the particular cell you are using. To assist you with this information, each cell has an address, also called a location, also referred to as the cell's primary name.

The location, address, or name of a cell, is gotten by a combination of its column and its row.

The combination of the column's name and the row's label provides the address or name of a cell. When you click a cell, its column header becomes highlighted in orange and the row header of a selected cell is highlighted in orange. To know the name of a cell, you can refer to the Name Box, which is located at the intersection of columns and rows' headers:

Whenever you are working with cells, at least one of them has focus. That cell has thicker borders than the other cells. That cell is referred to as the active cell. To visually identify the active cell, you can just look at the work area and locate the cell with thick borders. In the above screenshot, the active cell is E5.

In VBA for Microsoft Excel, the active cell is represented by an object named ActiveCell.

As mentioned already, before doing anything on a cell, you must be able to identify or recognize the cell. To do this visually, you can just look at, and locate, the desired cell. To programmatically identify a cell, you have various options.

You can identify a cell using the Range object. To do this, in the parentheses of the Range object, pass a string that contains the name of the cell. Here is an example that refers to the cell located as D6:

Sub Exercise()
    Workbooks.Item(1).Worksheets.Item("Sheet1").Range("D6")
End Sub

To get a reference to a cell, declare a variable of type Range. To initialize the variable, identify the cell and assign it to the variable using the Set operator. Here is an example:

Sub Exercise()
    Dim Cell As Range
    Set Cell = Workbooks.Item(1).Worksheets.Item("Sheet1").Range("D6")

End Sub

Cells are referred to as adjacent when they touch each other. To refer to a group of adjacent cells, in the parentheses of the Range object, pass a string that is made of the address of the cell that will be on one corner, followed by a colon, followed by the address of the cell that will be on the other corner. Here is an example:

Sub Exercise()
    Range("B2:H6")
End Sub

You can use this same technique to refer to one cell. To do this, use the same cell address on both sides of the colon. Here is an example:

Sub Exercise()
    Range("D4:D4")
End Sub

Instead of referring to one group of adjacent cells, you can refer to more than one group of non-adjacent cells. To do this, pass a string to the Range object. In the string, create each range as you want but separate them with commas. Here is an example:

Sub Exercise()
    Range("D2:B5, F8:I14")
End Sub

Before doing anything on a cell or a group of cells, you must first select it. Selecting cells is almost equivalent to highlighting a word in a text document. Various means are available to select a cell or a group of cells. You can use the mouse only, the keyboard only, or a combination of the mouse and keyboard.

You can select a cell using the mouse, the keyboard, or a combination:

• To select a cell using the mouse, just click it. To select another cell, click the new one
• To select a cell using the keyboard, since you may have to move focus from one active cell to another, press the arrow keys until the desired cell is selected
• To select a cell based on its name and using the mouse and keyboard, click in the Name Box, and type the name or address of the cell
• To select the first cell of the document using the keyboard only, press Ctrl + Home

To support cell selection, the Range object is equipped with a method named Select. Therefore, to programmatically select a cell, after referencing it, call the Select method. Here is an example:

Sub Exercise()
    Range("D6").Select
End Sub

When you have selected a cell, it is stored in an object named Selection. You can use this object to take an action on the cell that is currently selected.

Instead of only one cell, you may want to perform a common operation on many cells, which means you must select them first. You can use the mouse, the keyboard, or a combination. You can select cells based on columns or based on rows. You can select cells in a particular region; that is, adjacent cells, or you can select non-adjacent cells:

• To select all cells of a column:
  • Click the column header
     
    
  • In the Name box, type the name of a cell of that column and press Enter. Then press Ctrl and the Space bar
• To select all cells of a series of columns, select those columns as seen in Lesson 2 and notice that all cells in the selected columns are selected
• To select all cells of a row:
  • Click the row header as seen in Lesson 3
  • In the Name box, type the name of a cell of that row and press Enter. Then press Shift and the Space bar
• To select all cells of a series of rows, select those rows as seen in Lesson 3 and notice that all cells on the right side of the selected rows are highlighted
• To select cells in the same region using the mouse only, click and hold your mouse on one cell, then drag down or up, left or right, to the last cell in the range
• To select cells in the same region using the keyboard only, press the arrow keys a few times until the cell that will be at one corner is selected. Press and hold Shift. Press the arrow keys left, right, up, or down. If you press the right arrow key, the currently highlighted cell and the cell to its right would be selected. If you press the down arrow key, the current cell and the cell under it will be highlighted. You can also press the left or up arrow key. You can press the arrow keys many times in the direction of your choice. Once you have achieved the selection you want, release the Shift key
• To select non-adjacent cells, click one of the cells. Press and hold Ctrl. Click each of the desired cells. Once the selection is complete, release Ctrl
• To select all cells on a worksheet, you can press Ctrl + A. Alternatively, you can click the button at the intersection of the row header and row headers

To programmatically select a group of adjacent cells, refer to the group using the techniques we saw earlier, then call the Select method.

To programmatically select all cells of a column, access the Columns collection and pass the column name as a string, then call the Select method. Here is an example we saw in Lesson 9:

Sub Exercise()
    Rem This selects all cells from the fourth column
    Columns(4).Select
End Sub

To perform this operation using the name of a column, pass that name as argument. Here is an example that selects all cells from Column ADH:

Sub Exercise()
    Rem This selects all cells from the column labeled ADH
    Columns("ADH").Select
End Sub

You can also perform this operation using the Range object. To do this, use the Range collection. In the parentheses of the colection, enter the name of the column, followed by a colon, followed by the same column name. Here is an example:

Sub Exercise()
    Rem This selects all cells from Column G
    Range("G:G").Select
End Sub

To programmatically select all cells that belong to a group of adjacent columns, in the parentheses of the Columns collection, enter the name of the first column on one end, followed by a colon ":", followed the name of the column that will be at the other end. Here is an example:

Sub Exercise()
    Rem This selects all cells in the range of columns from Column D to Column G
    Columns("D:G").Select
End Sub

To select the cells that belong to a group of non-adjacent columns, use the technique we saw earlier to refer to non-adjacent columns, then call the Select method. Here is an example:

Sub Exercise()
    Rem This selects the cells from columns B, D, and H
    Range("H:H, D:D, B:B").Select
End Sub

To programmatically select all cells that belong to a row, access a row from the Rows collection, then call the Select method. Here is an example that all cells from Row 6:

Sub Exercise()
    Rows(6).Select
End Sub

You cand also use the Range object. After accessing the row, call the Select method. Here is an example that selects all cells from Row 4:

Sub Exercise()
    Range("4:4").Select
End Sub

To select all cells that belong to a range of rows, refer to the range and call the Select method. Here is an example that selects all cells that belong to the rows from 2 to 6:

Sub Exercise()
    Rows("2:6").Select
End Sub

To select all cells that belong to non-adjacent rows, refer to the rows and call the Select method. Here is an example that selects all cells belonging to Rows 3, 5, and 8:

Sub Exercise()
    Range("3:3, 5:5, 8:8").Select
End Sub

To programmatically select cells in the same region, enter their range as a string to the Range object, then call the Select method. Here is an example:

Sub Exercise()
    Range("B2:H6").Select
End Sub

Remember that you can use the same technique to refer to one cell, thus to select a cell. Here is an example:

Sub Exercise()
    Range("D4:D4").Select
End Sub

To select more than one group of non-adjacent cells, refer to the combination as we saw earlier and call the Select method. Here is an example:

Sub Exercise()
    Range("D2:B5, F8:I14").Select
End Sub

To select all cells of a spreadsheet, you can call the Select method on the Rows collection. Here is an example:

Sub Exercise()
    Rows.Select
End Sub

Instead of the Rows collection, you can use the Columns collection instead and you would get the same result.

When you have selected a group of cells, the group is stored in an object named Selection. You can use this object to take a common action on all of the cells that are currently selected.

We know that each has a name made of the combination of the name of the column and the name of a row. If you want, you can change the name of a cell. You can even create a name for a group of cells.

To know the name of a cell, you can check the Name Box. To name a cell or to change the name of a cell:

• First click it:
  • In the Name Box, replace the name with the desired name and press Enter
  • On the Ribbon, click Formulas. In the Defined Names section, click Define Name. In the Name text box of the New Name dialog box, type the desired name and click OK
• Click any cell on the workbook:
  • On the Ribbon, click Formulas. In the Defined Names section, click the arrow of the Define Name button. In the New Name dialog box, in the Name text box, type the desired name. In the Scope combo box, accept or specify the workbook. In the Comment text box, type a few words of your choice if you want. In the Refers to text box, click the button . On the workbook, select the cell. On the New Name: Refers To dialog box, click the button .
     
    
     
    Click OK
  • On the Ribbon, click Formulas. In the Defined Names section, click Name Manager. In the Name Manager dialog box, click New... In the Name text box, type the desired name. In the Scope combo box, accept or specify the workbook. In the Comment text box, type a few words of your choice if you want. In the Refers to text box, click the button . On the workbook, select the cell. On the New Name: Refers To dialog box, click the button . Click OK. Click Close

We already saw that, to refer to a cell using its name, you can pass that name as a string to the Range object.

We already know how to select a group of cells. If you select more than one cell, the name of the first cell displays in the Name Box. In most operations, this cannot be useful, especially if you want to perform the same operation on all cells in the selection. Fortunately, Microsoft Excel allows you to specify a common name for the group of selected cells.

To specify a name for a group of cells:

• First select the cells as a group using the techniques we learned for selecting cells. Then:
  • In the Name Box, replace the string with the new name
  • On the Ribbon, click Formulas. In the Defined Names section, click Define Name. In the Name text box of the New Name dialog box, type the desired name and click OK
• Click any cell on the workbook:
  • On the Ribbon, click Formulas. In the Defined Names section, click the arrow of the Define Name button. In the New Name dialog box, in the Name text box, type the desired name. In the Scope combo box, accept or specify the workbook. In the Comment text box, type a few words of your choice if you want. In the Refers to text box, click the button . On the workbook, select the cells that will be part of the group. On the New Name: Refers To dialog box, click the button . Click OK
  • On the Ribbon, click Formulas. In the Defined Names section, click Name Manager. In the Name Manager dialog box, click New... In the Name text box, type the desired name. In the Scope combo box, accept or specify the workbook. In the Comment text box, type a few words of your choice if you want. In the Refers to text box, click the button . On the workbook, select the cells to include in a group. On the New Name: Refers To dialog box, click the button . Click OK. Click Close

After creating a name for a group of cells, to refer to those cells using the name, call the Range object and pass the name as a string.

We saw that you can use a column as a basis to freeze a group of cells on a vertical line and prevent them from moving to the left or right when you scroll the other section. We also saw that you could freeze a row so that the cells above that row would not be scrollable. You can combine these two features and apply them to one particular cell.

To freeze the cells above and on the left side of a cell, click that cell such as Cell D6. On the Ribbon, click View. In the Window section, click Freeze Panes, and click Freeze Panes:

When you do this, the cells in the column from the left and the cells from the other left columns would be fixed. The cells in the row above and the cells from the other top rows would be fixed:

To remove the freezing, on the Ribbon, click View. In the Window section, click Freeze Panes, and click Unfreeze Panes.

To programmatically freeze or unfreeze a cell, call the ActiveWindow object and access its FreezePanes property, which is Boolean. If you set it to True, the window is divided in four parts  based on the cell that either is currently selected or you will have indicated. Here is an example of using it:

Sub Freezing()
    ActiveWindow.FreezePanes = True
End Sub

We saw how to use a column to divide the groups of cells in two vertical sections. We alsos saw how to divide the cells into two horizontal groups. In both cases, the division made it possible either to scroll from one of the sections or even to move the dividing bar itself to make one section bigger than the other. Instead of dividing based on the columns or rows, you can use a cell and split the cells into four scrollable groups.

To split the cells into four groups, click a cell such as Cell E12. On the ribbon, click View. In the Window section, click Split. This would display two bars crossing each other:

The user can scroll in one of the groups. To increase the width or the height of some sections, you can position the mouse on one of the bars or on the intersection of the bars, then click and drag in the direction of your choice until you get the sizes you want:

Then release the mouse.

To remove the splitting bars, double-click one of the bars or their intersection.

To programmatically split a worksheet, use the ActiveWindow object and access its Boolean Split property. To split, set this property to true:

Sub Splitting()
    ActiveWindow.Split = True
End Sub

To un-split, set this property to False.

To show the limits of a column, it (the column) displays borders left and right it. In the same way, to show where they start and where they end, the rows display horital bordered lines. These borders are also referred to as gridlines. If you want, you can show or hide the gridlines.

To hide the gridlines of cells of a worksheet, on the Ribbon:

• Click Page Layout. In the Sheet Options section, under Gridlines, remove the check mark on View:

• Click View. In the Show/Hide section, remove the check mark on the Gridlines check box

To show the gridlines again:

• In the Sheet Options section of the Page Layout tab of the Ribbon, under Gridlines, put a check mark on the View check box
• In the Show/Hide section, of the View of the Ribbon, click the Gridlines check box

To programmatically show or hide the gridlines, call the ActiveWindow and access its DisplayGridlines property. This is a Boolean property. If you set its value to True, the gridlines appear. If you set it to False, the gridlines disappear. Here is an example of using it:

Sub Exercise()
    ActiveWindow.DisplayGridlines = False
End Sub

We were introduced to columns in Lesson 2 and to rows in Lesson 3. We also saw the columns start in the top section by column headers and the rows start on the left by row headers. The column headers and the row headers are characteristics of a worksheet. This means that you can show or hide the column headers or the row headers for a worksheet while the headers are hidden or shown for another worksheet.

By default, the column headers and the row headers display on their worksheet. To hide the headers:

• On the Ribbon, click View. In the Show/Hide section, remove the check mark on the Headings check box:

• Click Page Layout. In the Sheet Options section, under Headings, remove the check mark on View:

To show the headers again:

• In the Show/Hide section, of the View tab of the Ribbon, click the Headings check box
• In the  the Sheet Options section of the Page Layout tab of the Ribbon, click the Headings check box

To programmatically show or hide the headers, get the ActiveWindow object and access its DisplayHeadings Boolean property. To show the headers, set this property to True. To hide the headers, set the property to False. Here is an example:

Sub ShowHeadings()
    ActiveWindow.DisplayHeadings = False
End Sub

A cell uses a combination of the width of its parent column and the height of its parent role to determine its size. This means that the width of a cell is the width of its column and its height is that of its row. Therefore, to change or specify the size of a cell, you use the technique of setting the width of columns or the height of cells as we saw in Lessons 3 and 4.

In future lessons, we will see how to enter values in cells. We will find out that it is not unusual that the content of a cell goes beyond its normal size. To show the hidden content of a cell, you can resize it.

As seen in Lesson 2:

• To control the width of all cells of a column, position the mouse on the separating line between two column headers. Drag left or right until you get the desire width. Then release the mouse.
• To change the widths of cells of a group of columns, first select the columns, whether in a range or randomly. Position the mouse on the column header border of one of the selected columns. Click and drag left or right in the direction of your choice until you get the desired with. Then release the mouse.
• If a cell under a column header displays the width you want, click the box that has the desired width. Then:
  • Double-click the separating line between the column header
  • On the Ribbon, click Home. In the Cells section, click Format and click AutoFit Column Width
• If many columns have some cells width a desired width, select those cells as we saw earlier. Then:
  • Double-click the separating line on one side of the column headers
  • On the Ribbon, click Home. In the Cells section, click Format and click AutoFit Column Width
• To precisely set the width of cells under a column:
  • Right-click the column header and click Column Width...
  • Click a column header or any of its cells. On the Ribbon, click Home. In the Cells section, click Format and click Column Width...
• To specify the same width for many columns:
  • Select a range of columns. Right-click either one of the column headers or inside the selection and click Column Width...
  • Randomly select a group of (non-adjacent) columns. Right-click one of the column headers and click Column Width...
  • Select the columns, whether in a range or randomly (non-adjacent). On the Ribbon, click Home. In the Cells section, click Format and click Column Width...
  Any of these actions would open the Column Width dialog box. From there, accept or enter the desired value and click OK

To undo any of these actions:

• On the Quick Access toolbar, click the Undo button
• Press Ctrl + Z

In Lesson 11, we saw how to programmatically specify the width of a column. You can use that technique to set the width of cells under a column.

In Lesson 3, we saw different techniques of setting or controlling the heights of rows. Actually, these apply to cells on the right side of the rows. As a reminder:

• To change the height of the cells of a row, click the lower border of a row header. Drag up or down until you get the desired height. Then release the mouse
• To change the height of cells on the right sides of a group of rows, first select the rows. Position the mouse on the bottom border of one of the selected rows. Click and drag up or down in the direction of your choice until you get the desired height. Then release the mouse.
• If the cells on the right side of a row header are too short or too tall, to change their height:
  • Double-click the bottom border of the row header
  • Click the row header. On the Ribbon, click Home. In the Cells section, click Format and click AutoFit Row Height
• To precisely specify the height of cells of a row:
  • Right-click the row header and click Row Height...
  • Click a row header or any box on its right side. Then, on the Ribbon, click Home. In the Cells section, click Format and click Row Height...
• To precisely set the same height for cells of many rows:
  • Select a range of rows. Right-click either one of the row headers or inside the selection and click Row Height...
  • Randomly select a group of (non-adjacent) rows. Right-click one of the row headers and click Row Height...
  • Select the rows. On the Ribbon, click Home. In the Cells section, click Format and click Row Height...
  This would call the Row Height dialog box where you can type the desired value and click OK or press Enter

To undo any of these actions:

• On the Quick Access toolbar, click the Undo button
• Press Ctrl + Z

In Lesson 12, we saw how to programmatically specify the height of a row. That's the same technique you use to set the height of cells that belong to the same row.

As mentioned already, when Microsoft Excel starts, it creates billions of cells for you. As we will see in the next lessons, you can then use the cells to enter the values you want in your spreadsheet. As a list displays its values, you may want to insert a value between two existing cells.

In Lesson 2, we saw that you could insert a new column to get new cells on the left side of a series of existing cells. Instead of using the column to perform this operation, you can do it from inside the cells.

To add a new column:

• Right-click a cell that belongs to the column that will follow the new column and click Insert... This would open the Insert dialog box. To insert a new column, click the Entire Column radio button before clicking OK:

  

• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow button under Insert and click Insert Sheet Columns
• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Cells... This would open the Insert dialog box. To insert a new column, click the Entire Column radio button and click OK

In Lesson 11, we saw how to programmatically create a new column. As a reminder, here is the example we used:

Sub CreateColumn()
    Columns(3).Insert
End Sub

When you perform any of these operations, a new column would be created on the left side of the column whose cell was clicked or right-clicked:

To add more than one column, first select the cells, either in a range:

Or at random:

Then:

• Right-click one of the selected cells and click Insert... In the Insert dialog box, click the Entire Column radio button and click OK
• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Sheet Columns
• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Cells... This would open the Insert dialog box. To insert a new column, click the Entire Column radio button and click OK

When you perform one of these actions, if the cells were selected in a range, the same number of columns would be created on the left side of the range that was selected. If the selected cells where not adjacent, new columns would be created on the left side of the column of each cell that was selected or on the left side of each group of cells that were selected.

The techniques of inserting columns that we saw in Lesson 2 and above are used to add a complete column. Sometimes, you will want to create, add, or insert a new cell between two existing cells. To perform this operation vertically, you would ask Microsoft Excel to consider a cell, move all cells under it by one step down. Once this is done, you would be presented with a new empty cell where the other was.

To insert a cell vertically between two cells:

• Right-click the cell that will be pushed down along with the cells under it, and click Insert...
   
  
• Click the cell that will be pushed down along with its bottom neighbors. On the ribbon, click Home. In the Cells section, click the arrow button under Insert, and click Insert Cells...

Any of these actions would call the Insert dialog box. From it, to insert a cell and push the other cells down, click the Shift Cells Down radio button:

When you click OK, each cell would be pushed down up to the cell that must be created:

In the same way, you can first select cells in a column, either in a range or randomly, and insert new cells.

To undo any of these actions:

• On the Quick Access toolbar, click the Undo button
• Press Ctrl + Z

In Lesson 3, we saw that you could add a new row to get a new series of cells aligned horizontally. Those operations can also be performed from cells.

To add a new row:

• Right-click a cell that belongs to the row that will be below the new row and click Insert... This would open the Insert dialog box. To insert a new row, click the Entire Row radio button and click OK
• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow button under Insert and click Insert Sheet Sheet Rows
• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Cells... This would open the Insert dialog box. To insert a new column, click the Entire Column radio button and click OK

Any of these actions would add a new row above the cell that was selected.

To add more than one column, first select the cells, either at random or in a range

Then:

• Right-click one of the selected cells and click Insert... In the Insert dialog box, click the Entire Row radio button and click OK
• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Sheet Rows
• Click a cell. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Cells... This would open the Insert dialog box. To insert a new column, click the Entire Row radio button and click OK

If the cells were selected in a range, the same number of rows would be created above the cells. Imagine the selected cells where not adjacent:

If you insert the rows, a new row would be inserted above each selected row:

Any of these techniques adds a complete row to the list. Sometimes, you will only want to add a new cell. You can ask Microsoft Excel to push some cell to the right and create room for one or more new cells.

To insert a cell horizontally between two cells:

• Right-click the cell that will be pushed to the right along with the cells on its right, and click Insert...
• Click the cell that will be pushed to the right along with its right neighbors. On the ribbon, click Home. In the Cells section, click the arrow button under Insert, and click Insert Cells...

Any of these actions would call the Insert dialog box. From it, to insert a cell and push the others to the right, click the Shift Cells Right radio button.

To undo any of these actions:

• On the Quick Access toolbar, click the Undo button
• Press Ctrl + Z

Besides adding cells, a list maintenance also consists of deleting or moving cells. In Lesson 2, we saw how to delete one or more columns. The problem is that, when you delete a column, all of its cells a lost. In the same way, if you delete a row, all of its cells are lost also. Sometimes you want to remove only one or more cells but keep the other cells of the same column or the same row. Fortunately, Microsoft Excel supports various techniques of removing cells from a list.

Instead of deleting a whole column, you can delete just one cell or more cells. Because a cell is surrounded by other cells, you must indicate what would happen to the other cells or how they would adjust to he new empty spot.

Before deleting a cell, you first make it active. Then you specify if, by removing it, the cells on on its right would be moved to the left to close the left empty space. The alternative it to draw the cells from under it up. When the cell has been removed, and the cells from the right side have been move left, Microsoft Excel adds a new cell from the last column. If the cells have moved up, Microsoft Excel creates a cell in the last position of that column.

To delete a cell:

• Right-click the cell and click Delete...
   
  
• Click the cell. On the Ribbon, click Home. In the Cells section, click Delete and click Delete Cells...

This would display the Delete dialog box. To 

To delete more than one row, first select the rows, whether in a range or randomly. Then:

• Right-click one of the rows (whether one of the row headers or a box on the right side of one of the selected rows) and click Delete
• (After selecting the rows,) On the Ribbon, click Home. In the Cells section, click Delete and click Delete Sheet Rows

To undo any of these actions:

• On the Quick Access toolbar, click the Undo button
• Press Ctrl + Z