Workbooks

Workbooks Fundamentals

Introduction

We have seen that a document in Microsoft Excel is made of one or more worksheets. In reality, a document in Microsoft Excel is called a workbook. In other words, a workbook is the group of worksheets that belong to the same document. This also means that when you start a document in Microsoft Excel, you actually start a workbook. When you save the document, you are said to save a workgroup. When you open a document, you are said to open a workbook. Based on this, for the rest of our lesson, we will use the word "workbook" to refer to any document in Microsoft Excel.

1. Re-start Microsoft Excel
2. To close the current document, click the system close below the first one
    
   

When you start Microsoft Excel, it directly creates a workbook for you. You can use that workbook as you see fit. At any time, you can create a new workbook. To support the ability to create workbooks, Microsoft Excel provides many templates. The default workbook with blank cells is just one of the templates. Instead of using the default workbook, Microsoft Excel provides many designed and ready-to-use workbooks with complete functionality. To create a workbook based on the samples provided by Microsoft Excel, click the Office Button and click New. This would display the New Workbook dialog box. In the left frame, under Templates, you can click a category. In the middle frame, click one of the button to see a preview in the right frame:

If you see a template you like, click it and click Create. If none of the templates suits you and if you are connected to the Internet, in the left frame, under Microsoft Office Online, click a category and select a template in the middle frame. Then click Download. You can also check for new files on the Microsoft Office web site.

1. To create a workbook based on a template, click the Office Button
2. In the left frame of the New Workbook dialog box, click Installed Templates
3. In the middle frame, click Blood Pressure Tracker
4. Click Create
5. To add another workbook based on a template, click the Office Button
6. In the left frame of the New Workbook dialog box, click Installed Templates
7. In the middle frame, click Time Card and click Create
8. To add one more workbook from on a template, click the Office Button
9. In the left frame of the New Workbook dialog box, click Installed Templates
10. In the middle frame, click Expense Report and click Create

A workbook is primarily a document like any other in Microsoft Windows. This means that you can create a new workbook or you can open an existing workbook as we saw in the first lesson. Because Microsoft Excel is a multiple document interface (MDI) application, you can create or open many workbooks at the same time and be limited only by the memory on your computer. In fact, Microsoft Excel allows you to work on various workbooks at the same time as if they were one. For example, you can transfer the contents of columns or cells from one workbook to another on the same screen.

As mentioned already, Microsoft Excel is a multiple document interface (MDI). This means that the application allows you to create or open many documents, be able to switch from one to another, or be able to display all of them sharing the same screen.

If you create or open many workbooks and while you are working on them, each is represented on the taskbar by a button. You can click the button of the desired workbook on the taskbar to access it. As an alternative, on the Ribbon, you can click View. In the Window section, click Switch Windows and click the desired document. The workbook you are currently using would have a check mark on it:

To display many workbooks in the work area of Microsoft Excel, after creating or opening them, on the Ribbon, click View. In the Window section, click Arrange All. This would display the Arrange Window dialog box. From there you can select one of the radio buttons:

• Tiled: The workbooks would display side by side:

• Horizontal: Each workbook would display horizontally

• Vertically: The workbooks would display side by side:

• Cascade: The workbooks would be presented one on top of the other:

To access a workbook:

• You can click its title bar
• On the Ribbon, click View. In the Window section, click Switch Windows, and select its name from the list

1. To access one of the workbooks, on the taskbar, click BloodPressureTracker1
2. To access another workbook, on the Ribbon, click View. In the Window section, click Switch Windows, and click TimeCard1 from the list

If you create or open many workbooks and while you are working on them, each is represented on the taskbar by a button. You can click the button of the desired workbook on the taskbar to access it. As an alternative, on the Ribbon, you can click View. In the Window section, click Switch Windows and click the desired document. The workbook you are currently using would have a check mark on it:

One of the most valuable features of Microsoft Excel views is that you can juxtapose two or more workbooks to share the same screen. After creating or opening at least two workbooks, to let them share the screen allocated to Microsoft Excel, on the Ribbon, click View. In the Window section, click View Side by Side. This would open the Compare Side by Side dialog box. From there, click the workbook that will share the screen with the current workbook:

After making the selection, click OK. Each workbook would be displayed each horizontally while they are sharing the work area of Microsoft Excel. Each workbook would have a title bar on its top, the vertical and scroll bars:

To access a workbook:

• You can click its title bar
• On the Ribbon, click View. In the Window section, click Switch Windows, and select its name from the list

To close a workbook, you can click its system Close button.

1. On the Ribbon, click View if necessary.
   To view the workbooks side by side, in the Window section, click View Side by Side
2. In the Compare Side by Side dialog box, click the ExpenseReport1 and click OK
3. Close each workbook without closing Microsoft Excel
4. When asked whether you want to save, click No

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.

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
    
   
6. Scroll down to be able to see Row 54:
    
   
7. In the Window section of the Ribbon, click Freeze Panes and click Unfreeze Panes

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.

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
    
    
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

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.

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

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
    
   
4. Save the file

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

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.
    
   
5. Click OK
    
   
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
12. Select cells F3:I16
     
    
13. On the New Name - Refers To dialog box, click the selection button
     
    
14. Click OK
     
    
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
     
    
18. Save and close the file