You are here: Home / Archives for Excel

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 : 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 insert current date in worksheet footer

January 27, 2009 By admin

To insert the current date in worksheet footer, follow these steps:

1. In the File menu click Page Setup. The Page Setup window will open.
2. Select the Header/Footer tab.
3. Click the Custom Footer button. The Footer window will open.

footicons

As you can notice the footer is divided into three sections; left, center and right. Above those sections there is a number of icons for page number, pages, date, time and more other icons.

To insert the current date:

1. Click on the section where you want the date to be inserted.
2. Click the Date icon footerdate.
3. The following code will be inserted in the selected footer section: “&[Date]“.
4. Click OK and you will see the current date in the preview window.
5. Click OK again to finish the dialog.

Notes:

1. You can also go to Header/Footer by clicking  Header and Footer in the View menu.
2. Use File –> Print Preview to see how your footer will look like in the printer.
3. You can use a similar procedure to insert the date in the header if you want to.

Applies to: Excel 2003

Filed Under: Customization, Date and Time, Formatting, Printing Tagged With: Customization, Date, Excel, Footer, Formatting, Header, Printing

How to preview a workbook without opening it

June 10, 2008 By admin

If you want to preview a workbook in Windows Explorer without opening it, then you have to save the workbook with a preview picture.

This is how you do it:

  1. In the File menu click Properties. The Properties window will open.
  2. Click on Summary tab.
  3. Select the checkbox labeled “Save preview picture” at the bottom of the window.
  4. Click OK.
  5. Save the workbook.

Applies to: Excel 2003

Filed Under: General Tagged With: Excel, preview picture, Workbook preview

A macro to generate a list of random numbers

June 5, 2008 By admin

Excel has a function to generate random numbers: RAND (), and if you have the Analysis ToolPak add-in installed you will have another function available which is RANDBETWEEN().

However both functions will generate one random number. What if you want to generate a list of random numbers? The immediate answer will be: use Autofill.

This is OK if you want a small list, but what if you have to generate say a list of 1000 random numbers?

The macro shown below will loop through to generate any number of entries you specify. It will also give you an option to specify the number of digits you want in the generated number.


Sub GenNumbers()
Dim i As Integer, Row As Integer, Col As Integer
Dim NoRows As Integer, NoDigits As Integer
Dim MinNum As Double, MaxNum As Double
Dim NumFormula As String
Col = 1 ‘ Write random numbers on column A
‘Get the number of rows and number of digits entered by the user
NoRows = ActiveSheet.Range(“B1″).Value
NoDigits = ActiveSheet.Range(“B2″).Value
‘Calculate minimum and maximum numbers to be generated
MinNum = WorksheetFunction.Power(10, (NoDigits – 1))
MaxNum = WorksheetFunction.Power(10, NoDigits) – 1
‘Construct the formula to be entered in the cells.
NumFormula = “=randbetween(” & MinNum & “,” & MaxNum & “)”
‘ The loop to generate the numbers
i = 0
For Row = 3 To NoRows + 2
i = i + 1
ActiveSheet.Cells(Row, Col).Value = i
ActiveSheet.Cells(Row, Col + 1).Formula = NumFormula
Next Row
End Sub

How to use the macro

1. Switch to Visual Basic Editor and Insert a new module.
2. Copy the code into the new module.
3. Close Visual Basic Editor and return to your worksheet.
4. In cell B1 of your worksheet enter the number entries you want.
5. In cell B2 of your worksheet enter the number digits you want.
6. Go to Tools -> Macro -> Macros, you will see the list of available macros.
7. Select GenRandomNum and run it.
8. You will get an output like this:

genrandnum.gif

Applies to: Excel 2003

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

How to collect and paste multiple items

May 29, 2008 By admin

The Microsoft Office Clipboard allows you to collect text and graphic items from any number of Office documents or other programs and then paste them into any Office document. Examples are Microsoft Excel, Microsoft Word and Microsoft PowerPoint.

To use the Office Clipboard you must open it in the task pane of an Office program.

  1. On the Excel menu click Edit.
  2. In the Edit menu click Office Clipboard.

    3. offclip.gif

  3. The Office Clipboard will open on the task pane.

    4. offclip1.gif

Now whenever you copy an item, it will appear on the Office Clipboard with the latest item on top.

To copy an item to Excel:

  1. Click on the cell where you want to copy the item.
  2. Click the required item on the Clipboard.

Notes:

  1. You can have up to 24 items collected in the Clipboard. If you copy more than that the oldest item will be dropped automatically.
  2. You can control the Clipboard behavior by using the Options drop down menu at the bottom of the Clipboard.

Applies to: Excel 2003

Filed Under: Data Tagged With: Clipboard, Data, Excel, Excel help, Office Clipboard, paste multiple items

How to format a cell based on the value of another cell (conditional formatting)

May 18, 2008 By admin
  1. Select the cells for which you want to add conditional formatting.
  2. On the Format menu, click Conditional Formatting.
  3. The Conditional Formatting dialogue will open.

    4. xcformatf2.gif

  4. In the first combo box select Formula is.
  5. In the text box next to it write the formula as follows: =$A$1<100. An equal sign followed by the cell reference followed by a comparison or a condition.
  6. Click the format button, the Format Cells dialogue will open.

    7. xcformat.gif

  7. Select the formatting you want to apply when the cell value meets the condition e.g. you can set the font color to red.

In this example, if the value of cell A1 is less than 100, then the color of the selected cells will be red.

Applies to: Excel 2003

Filed Under: Data, Formatting Tagged With: Conditional Formating, Excel, Excel Formatting, Excel help, Formatting, other cell

How to apply a cell format to other cells

April 18, 2008 By admin

1. Select the cell which you have already formatted.

2. Click Format Painter Format Painter in the standard toolbar.

3. Click the cell on which you want to apply the format. The format of the first cell will be applied to the new cell.

4. If you want to apply the format to more than one cell then select all the cells before releasing the mouse button (drag the mouse pointer across the cells).

Tip.

If you have none adjacent cells or ranges for which you want to apply the format, then:

  • Double click the format painter. This way it will remain active until you deactivate it again.
  • Apply the format as many times as you want (see 3 and 4 above).
  • When you are done double click the format painter to deactivate it.

Applies to: Excel 2003

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

How to create a custom toolbar

April 12, 2008 By admin
  1. Click Tools then click Customize .The Customize window will open.
  2. Click the Toolbars tab.

Toolbar Window1

  1. Click New. The New Toolbar window will open.

Toolbar Window2

  1. Enter a name for your toolbar then press OK. This will give you an empty toolbar.
  1. Click on the Commands tab of the Customize window.

Toolbar window 3

  1. As an example, and under Categories click on Insert.
  2. A list of relevant commands will show on the right
  3. Drag commands from the list and drop them on your new toolbar.
  4. Your toolbar will now look like this:

myToolbar

  1. Drag the new toolbar to the toolbars area at the top of the window.

Applies to: Excel 2003

Filed Under: Customization Tagged With: Customization, Excel, Excel help, Toolbars

How to print part of a worksheet

April 10, 2008 By admin

If you have a worksheet and you only want to print part of that worksheet then follow the steps below:

Method 1

  1. Click File then click Page Setup .The Page Setup window will open.
  2. Click the Sheet tab.

    3. Print Area0

  1. In the Sheet tab click Print Area
  2. Now go to your worksheet and highlight the cells you want to print. The Print Area in the Page Setup window will be updated automatically with the cells references.
  3. Now every time you print the worksheet only the specified area will be printed.
  4. To print the whole sheet, clear the Print Area before you start printing.

Method 2

  1. In your worksheet, highlight the cells you want to print.
  2. Click File –> Print Area –> Set Print Area.
  3. The print area is now set to your selection.
  4. To print the whole sheet, Click File –> Print Area –> Clear Print Area.

Print Area1

Applies to: Excel 2003

Filed Under: Data, Printing Tagged With: Data, Excel, Excel help, Printing
« Older Posts