You are here: Home

AutoFit row heights in Excel 2007

August 2, 2010 By admin Leave a Comment

If you have a number of rows in your sheet and each one has a different size of data. For example one row may have a wrapped text or a text with big font size that does not match with  the current row height, then you can adjust the row height.

To adjust the height of each row to fit  the entered data:

  1. Select the rows to be adjusted.
  2. In the Home tab, Cells group, click Format.
  3. From the Format menu Click AutoFit Row Height.

Alternatively you can do the same by following these steps:

  1. Select the rows to be adjusted.
  2. Point to the lower border of anyone of the selected rows (at the left of the row where the row number is). The mouse pointer should change to a cross with vertical arrows at the top and bottom.
  3. Double click on the row border.

Before:

After:

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

AutoFit Column widths in Excel 2007

July 15, 2010 By admin Leave a Comment

You may have a number of columns in your sheet and each one has a different size of data. For example one column may have only a 2 digit sequence number but another column has a long text.

To adjust the size of each column to fit  the entered data:

  1. Select the columns to be adjusted.
  2. In the Home tab, Cells group, click Format.
  3. From the Format menu Click AutoFit Column Width.

Alternatively you can do the same by following these steps:

  1. Select the columns to be adjusted.
  2. Point to the right border of anyone of the selected columns (at the top of the column where the column label is). The mouse pointer should change to a cross with horizontal arrows to the left and right.
  3. Double click on the column border.

Before:

After:

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

How to insert a shape in Excel 2007

March 30, 2010 By admin Leave a Comment

To insert a shape (rectangle, circle, line … etc.) follow these steps:

  1. Select the Insert tab, then Click Shapes.

    2. XL7Shapes1

  2. A drop down menu will show different types of shapes.

    3. XL7Shapes2

  3. Select the shape you want. The mouse pointer will change to a cross like this :XL7Shapes3
  4. Draw the shape by pressing  the left mouse button then dragging the mouse.
  5. Release the left mouse button when finished.

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

How to change Excel 2007 default font

February 21, 2010 By admin Leave a Comment

To change Excel 2007 default font, follow these steps:

  1. Click the  Microsoft Office Button.XL7OffButton
  2. Click Excel Options (at the bottom right of the menu).

    3. XL7Font2

  3. Excel Options window will open.

    4. XL7Font1

  4. In Excel Options window (left side) click Popular.
  5. Under When creating new workbooks (see the red rectangle in the image above) , specify the required font face and font size.

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

How to summarize data using a pivot table in Excel 2007

February 11, 2010 By admin Leave a Comment

Pivot table is a feature of Excel that takes a long list of data, summarizes it into a shorter list that is more easy to analyze and assimilate.

The best way to explain how to create a pivot table in Excel 2007 is by using an example.

Below is a list of products, salesmen and total sales for a product / salesman.

XL7Pivot1

To create a pivot table to summarize the total sales by product, you perform the following steps:

  1. Prepare your data table and make sure you have columns headings.
  2. Select the data range or simply click on a cell within the range.
  3. In the Insert tab click Pivot Table. The window Create Pivot Table will open.

    4. XL7Pivot2

  4. Make sure that Table/Range points to your correct range then click OK.
  5. Now you will get a rectangular area to the left that represents the pivot table, and a field list to the right.

    6. XL7Pivot3

  6. In the Pivot Table Field List to the right select Product and Total Sales. The pivot table that summarizes total sales by product will be created as follows:

    7. XL7Pivot4

  7. If you select Salesman instead of Product, this is what you get: a pivot table that summarizes total sales by salesman.

    8. XL7Pivot5

  8. You can plot a chart from the pivot table as shown below.

XL7Pivot6

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

EXCEL logical functions : the NOT function

January 21, 2010 By admin Leave a Comment

The NOT function, one of many EXCEL logical functions, is used to evaluate a single logical condition, passed as an argument to the function.  It will return the opposite of that argument. I.e.:

  1. If the evaluated logical condition is TRUE, the function will return FALSE.
  2. If the evaluated logical condition is FALSE the function will return TRUE.

Syntax

NOT(logical)

Logical is a condition that evaluates to TRUE or FALSE.

Examples:

A1 = 6

B1 = 10

=NOT(A1>5) : returns FALSE.

= NOT(A1>10): returns TRUE.

=NOT(A1=B1): returns TRUE.

You can nest the NOT function within another logical function as follows:

=IF(NOT(A1=B1) ;”Valid numbers”;”Invalid numbers”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value: “Valid numbers”, because the NOT function will evaluate to TRUE.

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

EXCEL logical functions : the OR function

January 7, 2010 By admin Leave a Comment

The OR function , one of many EXCEL logical functions, is used to evaluate a number of logical conditions, passed as arguments to  the function.  The function will return a single value of TRUE or FALSE.

Syntax

OR(logical1, logical2 ………)

logical1 and logical2 are conditions that evaluate to TRUE or FALSE. The first argument is required the second one is optional.

You can have up to 30 arguments in EXCEL 2003 and 255 in EXCEL 2007.

Examples:

A1 = 6

B1 = 10

=OR(A1>5;B1>10) : returns TRUE.

= OR(A1>10;B1>10):returns FALSE.

=OR(A1>5;B1>5): returns TRUE.

You can nest the OR function within another logical function as follows:

=IF(OR(A1>5;B1>10) ;”Valid numbers”;”Invalid numbers”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value : “Valid numbers”, because the OR function will evaluate to TRUE.

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

EXCEL logical functions : the AND function

January 5, 2010 By admin Leave a Comment

The AND function , one of many EXCEL logical functions, is used to evaluate a number of logical conditions, passed as arguments to  the function.  The function will return a single value of TRUE or FALSE.

Syntax

AND(logical1, logical2 ………)

logical1 and logical2 are conditions that evaluate to TRUE or FALSE. The first argument is required the second one is optional.

You can have up to 30 arguments in EXCEL 2003 and 255 in EXCEL 2007.

Examples:

Cell A1 = 6

Cell B1 = 10

Cell C1 :

=AND(A1>5;B1>10) : returns FALSE.

=AND(A1>5;B1=10): returns TRUE.

=AND(A1>5;B1>5): returns TRUE.

You can nest the AND function within another logical function as follows:

=IF(AND(A1>5;B1>10) ;”Valid Range”;”Invalid Range”)

If you put this formula in C1 for example, and A1 and B1 have the values shown above, then C1 will have the value : “Invalid Range”, because the AND function will evaluate to FALSE.

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

EXCEL logical functions : IF function

January 2, 2010 By admin 2 Comments

The IF function, one of many EXCEL logical functions, is used to evaluate a logical condition, passed as an argument to the function.  The function will then return one of two values specified as the second argument or the third argument in the function. It will return the second argument if the test evaluates to TRUE or the third argument if the test evaluates to FALSE.

Syntax

IF(logical test , value if TRUE, value if FALSE)

Logical test:  is a condition that evaluates to TRUE or FALSE.

Value if TRUE : is the value to be returned by the function if  logical test evaluates to TRUE.

Value if FALSE : is the value to be returned by the function if  logical test evaluates to FALSE.

Examples:

A1 = 6

B1 = 10

=IF(A1>5; “Valid value”; “Invalid Value”): returns “Valid value”

=IF(A1>B1;A1-B1;B1-A1) : returns 4. This function will always return a positive number.

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

How to install and load the Analysis Toolpak in Excel 2007

December 27, 2009 By admin Leave a Comment

To install and load the Analysis Toolpak in Excel 2007 follow these steps:

  1. Click the Office button. XL7OffButton
  2. Click Excel Options, the Excel Options window will open.

    3. XL7Addin1

  3. In the left pane of Excel Options click Add-Ins, The right pane will show a list of Active and Inactive  Add-ins.
  4. At the bottom of the right pane there is a combo box (drop-down) labeled Manage. Select Excel Add-Ins from the drop-down then click GO. The Add-Ins window will open.

    5. XL7Addin2

  5. In the Add-Ins window select the check box that says: Analysis ToolPak, then click OK.

Filed Under: Customization, Excel 2007, General Tagged With: Customization, Excel 2007
« Older Posts