Home

Workbooks and their Worksheets
 

Working With the Worksheets of a Workbook

 

Freezing a Row

In Lesson 2, we saw that you could 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. In Lesson 3, we 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. 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.

Practical Learning: Freezing a Row
  1. Open the RTHS4.xlsx workbook
  2. Click Cell D6
  3. On the Ribbon, click View
  4. In the Window section, click the Freeze Panes button and click Freeze Panes
  5. Press Ctrl + Home
     
    Freezing a Cell
  6. Scroll down to be able to see Row 54:
     
    Frozen Cells
  7. In the Window section of the Ribbon, click Freeze Panes and click Unfreeze Panes

Splitting the Interface

In Lesson 2, we saw how to use a column to divide the groups of cells in two vertical sections. In Lesson 3, we 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. 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.

Practical Learning: Splitting the Rows
  1. Click Cell E12
  2. In the Window section of the Ribbon, click the Split button
     
  3. Position the mouse on the intersection of the split bars
  4. Click and drag up and left
     
     Split
  5. Scroll in the top-left frame
  6. Scroll in the bottom-right frame
  7. In the Window section of the Ribbon, click the Split button
  8. Save the file
 
 
 

Cells Names

 

Introduction

In Lesson 2, we saw that each had a name made of 1 to 3 letters. We also saw that each row had a label that could be considered its name. In our introduction to cells, we saw that Microsoft Excel uses a combination of the name of the column and the name of a row to specify the name of a cell. While you cannot change the name of a column or the label on a row, Microsoft Excel allows you to change the name of a cell. In fact, you can select a group of cells and name them. You have various options.

Naming a Cell

We saw that a cell, each cell, has a name, which is also its location. At any time, 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 Cell Selection. On the workbook, select the cell. On the New Name: Refers To dialog box, click the button .
       
      New Name
       
      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 Cell Selection. On the workbook, select the cell. On the New Name: Refers To dialog box, click the button . Click OK. Click Close

Practical Learning: Naming a Cell
  1. Open the DAWN Report1.xlsx file
  2. To name a cell, click cell C2
  3. Click in the Name Box. That highlights C2. Type MainTitle and press Enter
     
    Naming a Cell
  4. Save the file

Naming Cells

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 Cell Selection. 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 Cell Selection. 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

Practical Learning: Naming Cells
  1. The DAWN Report1.xlsx file should still be opened.
    Select cells A3:D16
  2. On the Ribbon, click Formulas. In the Defined Names section, click Define Name
  3. In the Name text box of the New Name dialog box, type EREpisodes
  4. In the Comment section, type These are cases that brought a few patients to the emergency rooms at various hospitals in the country. The drug names refer to the types or categories of drugs that were consumed.
     
    New Name
  5. Click OK
     
    Name
  6. Press Ctrl + Home
  7. In the Defined Names section of the Ribbon, click Name Manager...
  8. In the Name Name dialog box, click New...
  9. In the Name text box, type RelatedDeaths
  10. In the Comment text box, type: These are cases of deaths that occurred as a result of drug consumption or abuse.
  11. On the right side of the Refers to text box, click the selection button Selection
  12. Select cells F3:I16
     
    Name
  13. On the New Name - Refers To dialog box, click the selection button Selection
     
    New Name
  14. Click OK
     
    Name Manager
  15. On the Name Manager text box, click Close
  16. To review names, select cells A3:D16 and see the Name Box
  17. Select cells F3:D16
     
    Cells Names
  18. Save and close the file
 
 
   
 
Previous Copyright © 2007-2009 FunctionX, Inc. Next