You are here: Home / Archives for July 2009

How to handle comments in Excel 2007

July 24, 2009 By admin 5 Comments

All commands (icons) that deal with comments are found in the Review tab, Comments group.

xl7comments1

To insert a new comment:

1. Select the cell where you want to insert the comment.
2. Click New Comment.
3. A new comment will be created. Type the required text then click outside the comment box.
4. The comment box will disappear but a small indicator will show on the top right corner of the cell.

xl7comments2

To show a comment:

1. Select the cell where you have the comment.
2. Click Show / Hide Comment.
3. If you have multiple comments then you can click Show All Comments to show all.

Note:

If the comment is already shown and you click Show / Hide Comment, then the comment will be hidden.

To edit a comment:

1. Select the cell where you have the comment.
2. Click Edit Comment.
3. The comment box will be shown. Click on it then edit the text.

To format a comment:

1. Select the cell where you have the comment.
2. Click Edit Comment.
3. The comment box will be shown.
4. Select the text that you want to format then right-click.

xl7comments3

5. Form the context menu select Format Comment.
6. in the Format Comment dialog box, select the formatting options that you want.

To move or resize a comment:

1. Select the cell where you have the comment then show the comment as explained previously.
2. Click the border of the comment box to show the sizing handles.
3. To move the comment, drag the border of the comment box, or press one of the arrow keys depending on which direction you want to move.

xl7comments4

4. To resize the comment, drag the handles on the sides and corners of the comment box.
xl7comments5

To copy a comment to another cell:

1. Select the cell where you have the comment.
2. In the Home tab, Clipboard group, click Copy (or just right click on the cell and select copy).
3. In the Home tab, Clipboard group, click the arrow below Paste, then select Paste Special (or just right click on the cell and select Paste Special).
4. In the Paste Special dialog box , under Paste, select Comments then click OK.

To delete a comment:

1. Select the cell where you have the comment.
2. Click Delete Comment.
3. The comment box and the comment indicator will both disappear.

To print a comment:

To print a comment check this post.

Filed Under: Excel 2007, Formatting, General, Printing Tagged With: Conditional Formating, Copy, Excel 2007, Excel help, Formatting, Printing

How to print comments in Excel 2007

July 24, 2009 By admin 11 Comments

To print comments in Excel 2007:

1. Show all comments or just the comments you want to print. How?

2. In the Page Layout tab, Page Setup group, click the dialog box launcher (see the mouse pointer in the image below).

xl7commentsp2

3. The Page Setup window will open.

4. Click on the Sheet tab.

xl7commentsp1

5. In the Comments box, select As displayed on sheet or At end of sheet depending on how you want to print the comments.

6. Click Print.

Notes:

1. If you select As displayed on sheet, then only the comments that are shown will be printed. So make sure that your comments are shown on the worksheet first. How?

2. To see how comments are printed, you can click Print Preview before you click Print.

Filed Under: Excel 2007, General, Printing Tagged With: Excel 2007, Excel help, Printing

How to freeze the top row in Excel 2007

July 21, 2009 By admin Leave a Comment

If you have a worksheet with a heading row at the top, then you may want to freeze the heading row while scrolling throw the data rows below it. To achieve that follow these simple steps.

1. In the View tab, Window group click Freeze Panes.

xl7panes1

2. From the drop down list select Freeze Top Row.

xl7panes2

3. A dark line will show below the header row indicating where the freeze is established. The row above the line is frozen, and the rows below the line are scrollable.

xl7panes3

Filed Under: Customization, Excel 2007, General Tagged With: Customization, Excel 2007, Excel help

How to freeze panes in Excel 2007

July 16, 2009 By admin 4 Comments

If you have a large worksheet, then you may want to freeze the worksheet at one point while scrolling throw the rest of the rows below it. To achieve that follow these steps.

1. Select the top row of the area which you want to be scrollable (or just a cell in that row). I.e. all rows including and below the selected row will be scrollable, all rows above the selected row will be frozen.

2. In the View tab, Window group click Freeze Panes.

xl7panes1

3. From the drop down list select Freeze panes.

xl7panes4

4. A dark line will show above the selected row indicating where the freeze is established. The rows above the line are frozen, and the rows below the line are scrollable.

xl7panes5

Filed Under: Customization, Excel 2007, General Tagged With: Customization, Excel 2007, Excel help

Average cells based on multiple criteria in Excel 2007

July 9, 2009 By admin Leave a Comment

To average cells based on multiple criteria in Excel 2007 you use the AVERAGEIFS function.

Syntax

AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…)

Average_range is the range of cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.

Example:

In this example we will average the total sales for Jim in quarter 2 (olive green rows). So our criteria are:

  1. “Jim” in column B.
  2. “Q2″ in column C.

Our formula will be like this:

=AVERAGEIFS(D2:D23;B2:B23;”Jim”;C2:C23;”Q2″)

This formula returns a value of 1094, which is (2540+1250+178+408)/4

xl7averageifs

Notes:

  1. If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
  2. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
  3. Each criteria_range must be the same size and shape as sum_range.
  4. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.

Filed Under: Data, Excel 2007, Formulas, Functions Tagged With: Excel 2007, Excel help, Functions