Excel2010

EXTRA OPTIONS

For different situations, we need to use different tools. And, there are many tools in MS. Excel that facilitate data processing. It is very difficult to include all Excel tools in such a small book. Therefore we are going to briefly explain some useful tools and commands in this chapter.

8.1 Data Validation

You can help users to enter accurate and appropriate information into worksheets with MS. Excel’s Data Validation feature. Data validation can restrict the type of information that is entered in a cell and can provide instructions for the user on entering information.

Example 8.1: Back to your teacher’s worksheet. To improve it, he asks you to restrict the data entrance. Because the school uses a 10-grade system, he wants to be able to enter only the whole numbers between 1 and 10.

	A		B		C	D	E
1	ID		Name		Exam 1	Exam 2	Average
2	1	Stephen		Milligan	9	8	8.5
3	2	Samuel		Neff	5	6	5.5
4	3	Brendan		Hara	7	10	8.5
5	4	Jeremy		Petersen	9	7	8
6	5	Todd	Rafferty		10	10	10
7	6	Kevin	Schmidt		9	10	9.5
8	7	David	Shadovitz		10	8	9
9	8	Pete	Thomas		8	7	7.5

Figure 8.1: Math lesson exam results

Analysis and Solution:

Before you open the Data Validation dialog box, you must select the range of cells to which you want to apply validation. Select the range C2:D9 range. Now, you can open Data Validation from the Data Tab. In the Settings tab of the Validation dialog box, you can enter validation criteria.

You can choose the type of data that you want to enter in your selected area from the Allow drop down menu. For this example,

select Whole number type. From the Data drop down menu, you can enter the range of the values, depending on the conditional criteria. Set the minimum value to 1 and maximum value to 10.

Figure 8.2: Data Validation

132	Microsoft Excel

Input Message:

You can enter a title and an input message that will be displayed when you select the cell(s) in the range. If you clear the check box, Input Message will be disabled.

Figure 8.3: Data Validation: Input Message

Error Alert:

You can also enter a title and an error message that will be displayed when someone enters an incorrect value. There are three options; Stop, Warning, and Information in the Style drop down menu.

Stop: Prevents you from entering an incorrect value.

Warning and Information: displays the error message and asks if you are sure or not. If you say OK, it’ll accept the value.

	Figure 8.4b: Data validation error message
If you clear the check box at the top, the error alert will			Figure 8.4a: Data validation Error Alert
be disabled.

Extra Options

133

Figure 8.5:

Data validation allow options

8.1.1 Data Validation Allow Options

In data validation dialog box we have different tools for different purposes. In the previous example, we had decimal numbers between 1 and 10. On the other hand, in another situation

Using the list option, you might want to restrict the (or select from list of) birthplace entries, for the cities from your country

Again using the list option, you can restrict class entries to allow only specific classes.

Using date option, you might want to restrict the birth date entries, between two dates

Again using date option, you might want to restrict the birthdate entries. You can allow registering only the people who are 18 or older.

8.2Freeze and Split Panes

While working with large worksheets, you sometimes, need to see different parts at the same time. It can be difficult to go forward and backward continuously. Microsoft Excel offers you two different options for such situations: Freeze and Split.

8.2.1 Splitting Panes

You Split a worksheet in order to view and scroll different parts of it independently. Splitting a worksheet into panes allows you to view different parts of the same worksheet side by side. It is useful when you want to copypaste data between different areas of a large worksheet.

Figure 8.6: Horizontal and Vertical Split buttons

134	Microsoft Excel

Select the cell where you want the screen to split and click the Split button from View Window tab

To set only the horizontal pane, drag the split box (located above the vertical scrollbar) down.

To set only the vertical pane, drag the split box (located to the right of the horizontal scrollbar) left.

Figure 8.8: Split panes

Figure 8.7: View tab Window

When you want to remove a split, you can double click on it, or click Split button again from the View tab.

8.2.2 Freezing Panes

To keep row and column labels or other data visible as you scroll through a
sheet, you can Freeze the top rows and/or left columns. The frozen rows and
columns do not scroll but remain visible. But other rows or columns are
automatically hidden while you move through the rest of the worksheet.			Figure 8.9: View Window tab
How to freeze;
Select the top-left most cell that will not be a part of the frozen panes.
From View tab Window, choose Freeze Panes (Figure 8.10).
To unfreeze it back, choose Unfreeze Panes from View Window tab.
Note: Tables automatically show the titles in place of column headings. In the
figure below, you can see column G, but other columns contain Table titles.
			Figure 8.10: Freeze Panes

Figure 8.11: Titles in the place of column headings

Extra Options

135

Example 8.2:

For example, to freeze the top row and the left column in the Figure 8.12, select the cell C2, and then click Freeze Panes from View Window tab. Thick lines will appear on the intersecting corner of the selected cell representing the freezing point. Later on, while scrolling down or right the Name and Surname columns, together with the column headings (Exam1, Exam2, etc.) line will remain visible while the rest of the sheet moves.

Thick lines continue all the row and column

Figure 8.12: Freezing panes

8.2.3 Displaying a workbook in more than one window

Sometimes, instead of using split, you may want to open a workbook in different windows. You also can display a single workbook in more than one window. For example, if you have a workbook with two worksheets, you may want to display each worksheet in a separate window in order to process the two sheets simultaneously. All the window-manipulation procedures described previously still apply. Choose View Window New Window to open an additional window for the active workbook.

8.2.4 Synchronous Scrolling two workbooks

Sometimes, instead of using split or freeze, you want to compare data from two different workbooks. For such situations you can open two workbooks at the same time, then use the View side by side button from Window group in View tab. If you also select Synchronous Scrolling, when you scroll down in one window the other window also scrolls simultaneously.

Figure 8.13: Synchronous Scrolling

8.3 Group and Outline

Microsoft Excel can create an outline for your data to let you show and hide levels of details with a single mouse click. Firstly, your data should be proper for outlining. That means, as in Figure 8.14, you should have all the formula cells at the same columns and/or rows properly. If your formulas are not placed in the same columns or rows, you can still use Auto Outline but it may not produce your intended result.

If you want to use Auto Outline, firstly, you should have data that is grouped like: Company, Division, Department, Budget Category, Budget Item, etc. Then, you should design your formulas as in that order so that it can easily be outlined. Here is an example:

136	Microsoft Excel

Example 8.3:

We have a list of classes, and each class has a list of lessons. At the end of each lesson, we have an average formula for each lesson and a General Average formula at the end. Now, if you want to create an outline for this table.

Figure 8.14b: Auto Outline

Figure 8.14a: 11th grade annual report

You click somewhere in the table, then all you need to do is to click Auto

Outline from Data Outline Group tab.

Figure 8.15a: The list after outlining

As you see in this figure, after Auto Outline, Excel creates the Outline according to your formulas and places some plus and minus signs for each. When you click on any minus sign, Excel collapses relevant columns and hides the details of that outline. If you click on any plus sign, Excel expands the details of it. In the following figure, Math, Physics and Chemistry exam details are hidden but the Computer Exam details are shown.

Figure 8.15b: Math, Physics and Chemistry lessons collapsed

Extra Options

137

Example 8.4:

If you don’t want to use Auto outline or you don’t want to outline the entire table, you can outline only the parts that you wish. For example, you can group columns B, C and D together.

Before applying Group and Outline, first, select any range of cells from columns B to C. After that, select Group from Data Outline Group tab. The Group dialogue window will be displayed. Select the Columns radio button and Press OK.

In a worksheet, there can be only one range of Group and Outline. So, if you need to group two different ranges, use two worksheets.

Figure 8.16: Grouping Columns

After you press the OK button, you group columns B, C and D together. You can show or hide these columns using the minus or plus buttons above the column headings.

Example 8.5:

If you have vertical groups like class names, you can add the formulas automatically using SubTotals button from Data Outline.

Figure 8.17: Using Subtotal

Select the entire table first, and then, use SubTotals button. It will show you the SubTotal dialog box.

138	Microsoft Excel

Figure 8.18: Using Subtotal command to prepare the outline of a table

From the “At each change in” box select the Class, then select the Average function and select the columns that you add into Subtotal list. It will automatically create the outline and all necessary formulas for you.

As you might guess, in order for Excel to outline properly, the column that you select in “At each change in” box (Class column) must be sorted.

8.4 Using Watch window

Sometimes, when you work with large amount of data and you need to consult some data frequently, it’s suggested to use the Watch window. So, you don’t have to move around and hunt data in that mess.

In the next figure, if you need to consult some currencies frequently, you can add them in the Watch window so when you move around, you can see and reach your favorites easily.

8.5Comment

Sometimes, especially when you share a workbook with a group of people, you may want to explain your worksheet to others. You can insert comments and when they point at the cell, Excel’ll show automatically the comment they are attached to.

Figure 8.19: Watch window

Double click on a cell in the watch window to jump to it directly.

Extra Options

139

Figure 8.20: Adding Comments

Figure 8.21: Editing Comments

Figure 8.22a: Using pictures in comments

Figure 8.22b:

Changing Comment Shape

8.5.1 To Insert a Comment:

1.Right-click the cell you want to attach a comment to

2.Select Insert Comment from the popup menu.

3.Type in the comment in the box

4.Click anywhere outside the comment area when you are finished.

To Edit a Comment:

1.Right-click the cell that contains the comment you want to edit.

2.Select Edit Comment from the popup menu.

3.Edit the comment.

4.Click anywhere outside the comment area when you are finished.

To Delete a Comment:

1.Right-click the cell that contains the comment you want to delete.

2.Select Delete Comment from the popup menu.

8.5.2 To Format a Comment:

1.First, right click on the cell and from the popup menu select the Shown/Hide comments

2.Then right click on one of the borders of the comment

3.Select Format Comment from the shortcut menu.

4.Adjust the comment from the Format Comment dialog box.

Example 8.6:

You can insert a picture in the comment box.

Select Colors and Lines tab in the Format Comment dialog box.

Click the Color drop-down list and

select Fill Effects.

In the Fill Effects dialog box, click the Picture tab and

then click the Select Picture button to specify a graphics file.

8.5.3 To change Comment Shape

Normally, a comment is rectangular in shape. But you can change it.

First, add the ‘Change Shape’ button into the QAT, from Drawing Tools.

Then, make your comment to be shown always and select it.

Finally, press the Change Shape button and select the new shape.

140	Microsoft Excel