You are here: Home / Archives for June 2009

How to record a macro in Excel 2007

June 28, 2009 By admin Leave a Comment

If you have a task that you perform repeatedly, then you can record all the events to achieve that task (keystrokes, mouse clicks … etc.) in a macro.

To record a macro in Excel 2007 follow these steps.

1. Click on the Developer tab. If the Developer tab is not visible, then check this post on how to show the Developer tab:

2. In the Code group Click Record Macro.

xl7macros1

3. The Record Macro window will open.

xl7macros2

4. Give a name to your macro.

5. Specify a shortcut key(optional).

6. Specify where you want to save the macro.

7. Click OK.

8. Now start performing the task which you want to record. When you are done click Stop Recording.

xl7macros3

9. Your macro is now created and is ready to be used. To run the macro:

  1. Click Macros.

    2. xl7macros4

  2. The Macros window will open.

    3. xl7macros5

  3. Select the macro then click run.

10. You can also run the macro by simply clicking the shortcut key combination.

Filed Under: Excel 2007, Visual Basic Tagged With: Excel 2007, Excel help, Macros, Visual Basic

Average values based on a given criteria in Excel 2007

June 15, 2009 By admin Leave a Comment

To average a range of values that meet a given criteria or condition, you use the AVERAGEIF function.

Syntax

AVERAGEIF(range,criteria,average_range)

Range : The group of cells to be evaluated for criteria.

Criteria: is the criteria in the form of a number, expression, cell reference, or text that defines which cells are averaged.

Average_range: is the actual set of cells to be averaged. If omitted, range is used.

Example:

In the example below we want to average the total sales for a particular salesman (Jim). So our range is B2:B13 and our criteria is “Jim”. The range to be averaged is C2:C13. The formula will be:

=AVERAGEIF(B2:B13;”Jim”;C2:C13)

The result is 1005, i.e. (2300 + 1100 + 200 + 420) / 4

xl7average1

Notes:

1. Cells in range that contain TRUE or FALSE are ignored.

2. If a cell in average_range is an empty cell, AVERAGEIF ignores it.

3. If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.

4. If a cell in criteria is empty, AVERAGEIF treats it as a 0 value.

5. If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.

6. You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. In the previous example if you use “J*” in criteria then both “Jim” and “John” will be included in the average.

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

How to change or adjust the column width in Excel 2007

June 6, 2009 By admin Leave a Comment

1. To adjust the column width to fit the current text, move the mouse pointer to the right border of the column until it changes to a cross as shown and then double click on it.

2. To adjust the column width manually, move the mouse pointer to the right border of the column until it changes to a cross as shown, hold down the left mouse button then drag the mouse until you get the right width.

xl7colwidth1 Before

xl7colwidth2 After

3. To adjust the column width to give it fixed size:

  1. In the Home tab , Cells group click Format then Column Width

    2. xl7colwidth3

  2. The Column Width window will open.

    3. xl7colwidth4

  3. Enter the required column width.

    4. Notes:

    1. The units shown are the number of characters in the default text format.

    2. You can enter any number between 0 and 255.

    3. If you enter 0 the column will be hidden.

Filed Under: Excel 2007, Formatting Tagged With: Excel 2007, Excel help, Formatting