Excel Digest

To change Excel 2007 default font, follow these steps:

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



3. Excel Options window will open.



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.

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.

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. 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. 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. If you select Salesman instead of Product, this is what you get: a pivot table that summarizes total sales by salesman.



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

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.

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.

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.

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.

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

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



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. In the Add-Ins window select the check box that says: Analysis ToolPak, then click OK.

To hide rows based on cell value in Excel 2007 follow these steps:

1. Prepare your data including a header row.
2. In the Home tab, Editing group, click Sort and Filter then select Filter from the menu.
3. Your data will now look like this:

In this example we want to hide rows where the sales are greater than or equal to 2000. I.e. we will only show rows where the sales are less than 2000.

1. Click on the filter arrow next to Sales.
2. Point to Number Filters and select Less Than.



3. The Custom AutoFilter window will open.



4. Enter 2000 in the Combo box to the right and click OK.
5. Now all rows with values greater than or equal to 2000 will be hidden. Here is the final result:

Note:

If your data is text then you will see Text Filters instead of Number Filters in the menu.

To sum times you usually use Excel built-in function SUM. If you have three cells A1, A2 and A3 formatted as time (hh:mm), and you have :

A1 = 02:30

A2 = 10:15

A3 has the formula: =SUM(A1:A2)

then A3 will have the correct values of 12:45.

However if your total times are greater than 24 hours then things get tricky.

If you have :

A1 = 15:30 and A2 = 10:15 then A3 will have 01:45 instead of 25:45. I.e. any hours beyond 24 hours are dropped.

To retain the hours beyond 24, you have two alternatives:

1. If you want to convert the hours beyond 24 into days then use this cell format : dd:hh:mm. The total in the previous example will be 01:01:45 ( 1day , 1 hour and 45 minutes)

2. If you want to keep the hours beyond 24 as is then use this cell format :[h]:mm. The total in the previous example will be 25:45.

In both cases the formula will remain as it is. Only the cell format differs.

See another example below:

More posts on time:

http://www.exceldigest.com/myblog/2009/02/08/how-to-add-or-sum-times/
http://www.exceldigest.com/myblog/2008/12/04/how-to-create-a-custom-time-format/

If you want to copy data from Excel to Microsoft word then follow these steps:

In  Excel

1. Select the worksheet data (range) that you want to copy to a Word document.
2. On the Home tab, Clipboard group, click