You are here: Home

How to clear conditional formatting in Excel 2007

September 27, 2010 By

To clear conditional formatting from a range of cells follow these steps:

  1. Select the cells for which you want to clear conditional formatting.
  2. On the Home tab, Styles group, click Conditional formatting.
  3. Click Clear Rules.
  4. From the menu to the right Click Clear Rules from Selected Cells.

To clear conditional formatting from the whole worksheet (no selection is required):

  1. On the Home tab, Styles group, click Conditional formatting.
  2. Click Clear Rules.
  3. From the menu to the right Click Clear Rules from Entire Sheet.

Filed Under: Excel 2007, Formatting Tagged With: ,

How to attach a file to Excel 2007 workbook

September 12, 2010 By

It is fairly easy to  insert a file into an  Excel worksheet as an attachment. That file can be a word document, PowerPoint presentation or even another Excel workbook.

Follow these steps:

  1. In Excel Insert tab, Text group click Object.

  2. The Object window will open.

  3. Select Create from File tab.
  4. Select the check box Display as Icon.
  5. Click Browse and select the file you want to attach.
  6. The file will be shown on the worksheet as an icon.

Filed Under: Excel 2007, General Tagged With: ,

AutoFit row heights in Excel 2007

August 2, 2010 By

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

AutoFit Column widths in Excel 2007

July 15, 2010 By

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

How to insert a shape in Excel 2007

March 30, 2010 By

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

How to change Excel 2007 default font

February 21, 2010 By

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

How to summarize data using a pivot table in Excel 2007

February 11, 2010 By

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

EXCEL logical functions : the NOT function

January 21, 2010 By

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 logical functions : the OR function

January 7, 2010 By

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 logical functions : the AND function

January 5, 2010 By

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: , ,
« Older Posts
Newer Posts »