Everyday we come across some problem for which we try, research for hours and once solved, looks so easy in MS Excel.I like to keep a note of it and share with others, so people can use them.These notes are not necessarily my own writing but are the one which I came accross during my research over the net to find out solutions.

## Saturday, December 18, 2004

### MS Excel:Quickly Navigate Multi-Sheet Workbooks

## Friday, December 17, 2004

### MS Excel:Search for an Asterisk, Question Mark, or Tilde

So, to find an asterisk, Edit>Find, and then type ~* in the Find dialog box;

to find a tilde, type ~~;

to find a question mark, type ~?.

## Thursday, December 16, 2004

### MS Excel:Change Text Case

UPPER: converts all text to uppercase

LOWER: converts all text to lowercase

PROPER: converts text to The First Letter Of Each Word Is Capitalized.

If cell A1 contains the text: sameer lal, the following formula will produce Sameer Lal.

=PROPER(A1)

You may want to then select the cell or the range, Copy and then Edit>Paste Special (then choose Values) to change the contents of the cells from functions into the values produced by the functions.

## Wednesday, December 15, 2004

### MS Excel: Formula Error and What It refers To

Common Formula Errors You’ll See in EXCEL

What Appears in the Cell and What happened? :

#DIV/0!

Appears when the formula calls for division by a cell that either contains the value 0 or, as is more often the case, is empty. Division by zero is a no-no.

#NAME?

Appears when the formula refers to a range name that doesn't exist in the worksheet. This error value appears when you type the wrong range name or fail to enclose in quotation marks some text used in the formula, causing Excel to think that the text refers to a range name.

#NULL!

Appears most often when you insert a space (where you should have used a comma) to separate cell references used as arguments for functions.

#NUM!

Appears when Excel encounters a problem with a number in the formula, such as the wrong type of argument in an Excel function or a calculation that produces a number too large or too small to be represented in the worksheet.

#REF!

Appears when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over the cells referred to in a formula.

#VALUE!

Appears when you use the wrong type of argument or operator in a function, or when you call for a mathematical operation that refers to cells that contain text entries.

## Tuesday, December 14, 2004

### MS Excel:Remembering Cell Names

Press [F3] - (a list of range name appear)

DOUBLE CLICK the range name that you want to include in formula.

## Monday, December 13, 2004

### MS Excel: Quick Click Movement

Select a cell:

Double-click the bottom edge of the selected cell,

(Take cursor to botton edge and when it becomes + sign made of four arrows)

and you're taken to the last filled cell below the selected cell in the current column.

This trick works for rows nd column and in all four directions.

So, for example, if you want to find the last filled cell or the next empty cell in the current row:

Select a cell:

Double-click the right edge of the cell.

## Sunday, December 12, 2004

### MS Excel:Range Names Used In Formulas

SUM(Value) instead of SUM(E2:E20)

For example, enter the name Quantity for Column C and Price for column D and value in column E.(highlight the column from C2:C20 and then type the name in the Range name text area at the left of the fx symbol and

**hit Enter**).

Then enter the formula =Quantity*Price in column E cells.

Now, as you fill in numbers in the Quantities and Price columns, Excel automatically calculates Values in the third column.

## Saturday, December 11, 2004

### MS Excel: Avoid error displays in formulas

but you may want to avoid the messages.

You can do so by using an IF() function to check for an error.

The formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),"",A1/B1)

## Friday, December 10, 2004

### MS Excel: Indirect Function

The following formula utilizes the INDIRECT function to create the range reference used by the SUM function(Cell B2 to B6 carry individual marks for each subjecton different sheets named after students referred in B2 to D2):

=SUM(INDIRECT(B2&"!$B$2:$B$6"))

Note that the ampersand operator to join the student name with the cell reference (expressed as text).Refer the example below:The cell B2 contains the text Sam,the SUM function returns the sum of the range Sam!B1:B6.

## Thursday, December 09, 2004

### MS Excel: Copy formula shortcut

.Same thing you can perform in right cell to the cell of formula by

*pressing Ctrl+R, in our case, B3.*

*This tip is contributed by Pramod Jain*

## Wednesday, December 08, 2004

### MS Excel:Cell Counting Techniques

Excel provides many ways to count cells in a range that meet various criteria:

- The DCOUNT function. The data must be set up in a table, and a separate criterion range is required.
- The COUNT function. Simply counts the number of cells in a range that contain a number.
- The COUNTA function. Counts the number of non-empty cells in a range.
- The COUNTBLANK function. Counts the number of empty cells in a range.
- The COUNTIF function. Very flexible, but often not quite flexible enough.

## Tuesday, December 07, 2004

### MS Excel:Calculate the day of the year and days remaining

The formula below returns the day of the year for a date in cell A1:

=A1-DATE(YEAR(A1),1,0)

Note: Excel automatically formats the cell as a date, so change the number format to another option (like General).

To calculate the number of days remaining in the year (assuming that the date is in cell A1), use the following formula:

=DATE(YEAR(A1),12,31)-A1

## Monday, December 06, 2004

### MS Excel:Count AutoFiltered Rows

To display a permanent count of the visible rows in an AutoFiltered list, create a formula using Excel's SUBTOTAL function. The first argument for SUBTOTAL specifies the type of operation (an argument of 2 displays a count of the visible cells in a range).The formula in cell which will display visible rows is:

=SUBTOTAL(2,A6:A3000)

The formula counts the number of visible cells in the range (minus the header row). Apply different filtering criteria, and the formula updates to show the new count. The SUBTOTAL function only works for AutoFiltering and outlining. If you hide rows manually, it won't return the correct result.

## Sunday, December 05, 2004

### MS Excel:Calculating a conditional average

For example, an instructor might calculate student grades by averaging a series of test scores but omitting the two lowest scores. Or you might want to compute an average that ignores both the highest and lowest values.

In cases such as these, the AVERAGE function won't do, so you must create a more complex formula. The following Excel formula computes the average of the values contained in a range named "scores," but excludes the highest and lowest values:

=(SUM(scores)-MIN(scores)-MAX(scores))/(COUNT(scores)-2)

Here's an example that calculates an average excluding the two lowest scores:

=(SUM(scores)-MIN(scores)-SMALL(scores,2))/(COUNT(scores)-2)

## Saturday, December 04, 2004

### MS Excel: Word Count

Sub NumberOfWords()

Dim NumberOfWord As Long

Dim RangeArea As Range

Dim Str As String

Dim Num As Long

For Each RangeArea In ActiveSheet.UsedRange.Cells

Str = Application.WorksheetFunction.Trim(RangeArea.Text)

Num = 0

If Str <> "" Then

Num = Len(Str) - Len(Replace(Str, " ", "")) + 1

End If

NumberOfWord = NumberOfWord + Num

Next RangeArea

MsgBox NumberOfWord

End Sub

## Friday, December 03, 2004

### MS Excel: Delete Those Unwanted Sheets

Right-click on the sheet tab. Select Delete from the shortcut menu.

## Thursday, December 02, 2004

### MS Excel: Add a Row or Column Easily

Drag across the row numbers or column letters in the worksheet frame to select the number of rows or columns you want to insert.

Then, you press [Ctrl]+ (use the plus sign on the numeric keypad) to insert entire rows or columns at that spot. You press [Ctrl]- to delete entire rows or columns.

## Wednesday, December 01, 2004

### MS Excel:Copying Format

To copy formatting to several nonadjacent cells or ranges, repeat the same steps, only double-click the Format Painter button. This way, the mouse pointer will remain a paint brush until you press [Esc]

## Tuesday, November 30, 2004

### MS Excel:Protecting Cells

## Monday, November 29, 2004

### MS Excel:Prevent Duplicate entries within a range of cells

Select cell A1.

Choose "Validation" from "Data" menu.

Choose "Settings" tab.

Choose "Custom" from "Allow" drop-down list.

In the Formula box, enter the following =NOT(OR(COUNTIF($A$1:$A$10,A1)>1))

Choose the "Error Alert" tab in order to set an alert style.

Set Style to Stop (the alert message with a Stop sign on it).

In the Title box, enter what you want to appear in the Error Alert's title bar (for example, "Duplicated Entry"); in the Error Message box, enter the message box text ("Please type an entry not entered previously," for example).

Choose "OK".

Back in the worksheet, select cell A1;

then, using the square AutoFill handle, drag to fill cells A2:A10.

## Sunday, November 28, 2004

### MS Excel:Separating first name and last name

The formula for extracting the first name is =LEFT(A1,FIND(" ",A1))

The formula for extracting the last name is =MID(A1,FIND(" ",A1)+1,LEN(A1))

This tip can be used for many other purposes and comes out very handy.

## Saturday, November 27, 2004

### MS Excel:Setting Non-scrolling Rows and/or Columns

Position your cell pointer to define the rows and columns to freeze.

Go to the Window menu and choose Split or Freeze Panes.

Return to the Window menu to Remove Split or Unfreeze Panes.

Split gives you areas that you can scroll within.If you've chosen Freeze Panes your cell pointer will move out of the frozen pane area if you attempt to move beyond it.

## Friday, November 26, 2004

### MS Excel:Displaying Cell Formulas Rather Than Results

Shortcout:

Keyboard shortcut Press Ctrl and ~(Tilde sign)

From Menu:

Tools Options View tab Window Options section, check Formulas.

## Thursday, November 25, 2004

### MS Excel:Insert and Auto Sum

Excel will try to guess what cells you want to total up, either the cells immediately above or to the left - but you can change the range of cells to whatever you need.

## Wednesday, November 24, 2004

### MS Excel:Check For Numbers Stored In a Text Format

1. Go to Tools Options.

2. On the Error Checking tab, select the Enable Background Error Checking and Number Stored As Text check boxes, and click OK.

Now, when Excel finds a number in a cell formatted as text, it will place a green error indicator in the upper left corner of the cell. To convert cells to a number format, select the cell, click the Error Checking Options button displayed to the left of it, and select Convert To Number.

## Tuesday, November 23, 2004

### MS Excel:Turn the Auto calculation- ON and OFF

You may temporarily want to turn off the automatic calculation option while working with large worksheets in Microsoft Excel. This allows you to enter all the formulas without having to wait for a recalculation. To do this:

- Open the Tools menu and click Options.
- In the Options dialog box, click the Calculations tab.
- Then, click on the radio button labeled Manual, and click OK to close the dialog box.
- If you want to check a calculation while you are working, simply press F9.

When you finish a large worksheet, don’t forget to turn automatic calculation back on.

## Monday, November 22, 2004

### MS Excel: Data Selection

**Move entire columns:**Highlight the column letters. Hold down the Shift key. Drag.

**Move entire rows:**Highlight the row numbers. Hold down the Shift key. Drag.

**Data selection of multiple areas:**Press the Control key while using the mouse to select multiple cells, data ranges, columns, rows, or worksheet tabs.

**Modify the area of a selected range:**If you use the mouse to highlight a range of too many or too few cells, use the Shift and Arrow keys to modify your selection.

**To select a single column:**Put the cursor in the column then control-spacebar, or click on the column letter.

**To select a single row:**Put the cursor in the row then shift-spacebar, or click on the number.

**To select the entire worksheet:**Control A or click on the blank cell in upper left corner.

**To select a range:**Click the cell in the upper left corner. Hold down the shift key. Click the opposite corner.

**To select multiple ranges:**Press Control then repeat the above procedure in another area.

**To select all worksheets:**Right click on any tab and choose Select All Sheets.

## Sunday, November 21, 2004

### MS Excel: Which formula to use

The sumproduct has it's own limitation of being used to return only number but for numbers, I find it most suitable.

Before writing formulas, always break it parts, see the results of that part and then merge parts together.

## Saturday, November 20, 2004

### MS Access(or MS Excel):Mathematical order of operations

Answer is 17 or 32

It is 17.

Remember this order:

B E D M A S

B = ( ) Brackets

E = 2raise to power 3 Exponents=8

D = / Division

M = * Multiplication

A = + Addition

S = - Subtraction

## Friday, November 19, 2004

### MS Excel:INDEX and MATCH to Look up Data

Say, you want to look up the salary of a person by using the person's name. To do this, use a combination of the INDEX and MATCH functions as under:

=INDEX($B$3:$D$7, MATCH("Lal",$B$3:$B$7,),3)

This formula uses cells B3:D7 and looks up Lal's salary in the third column. The formula returns 4000.

## Thursday, November 18, 2004

### MS Excel: Magic Sumproduct Formula

Here in this example, I have used both, Sumproduct and Array formula Sum to show the results from both.

Essentially, in A8 and A13 , we are finding out sum of quanity which are greater than 20 with "Sam" in Area "a" and in A10 and A15 , we are finding out count of records of quanity which are greater than 20 with "Sam" in Area "a" both by SumProduct formula and array formula of Sum(where you have to enter Ctrl+Shift+Enter to enter the formula):

You can perform many calculations using this magic Sumproduct formula without ever bothering to go for array formula.

## Wednesday, November 17, 2004

### MS Excel: Entering a line break within a cell

Press ALT+ENTER

Type in the second line. Do the same again if you have more lines to enter.

Press ENTER when you are done typing.

## Tuesday, November 16, 2004

### MS Excel: Find out overdue days

To try this:

In a worksheet, type the due date in cell A1, using slashes to separate day, month, and year.

In cell A2, type =DATEDIF(A1,TODAY(),"d")

and press ENTER.

The overdue period (in days) will be displayed in cell A2.You can replace "d" with "m" or "y" to find out period in months or years.Same way, you can replace Today() with a date field to find difference between those two fields like, invoice date and payment date.

## Monday, November 15, 2004

### MS Excel: Record Counting Based on Multiple conditions

For example:

The worksheet carries three columns, A, B and C.A is for name of student, B for marks and C for gender(M/F).

Your sheet looks like

A ------B -----C

Mary -- 60 -- F

Sam -- 70 -- M

Tim -- 65 -- M

Rudy -- 80 -- F

Jack -- 75 -- M

For this, you want to see Number of Males getting more than 70.

The formula will be(Write this in the cell you want the result):

=SUM(IF($B$2:$B$6> =70, IF($C$2:$C$6="M",1,0)))

Press CTRL+SHIFT+ ENTER.

The result in this case is

**2**.

## Sunday, November 14, 2004

### DateTime Format and .NET

The format function is:

FormatDateTime(date, format)

You could use:

Format DateTime(Now,”M/dd/yyyy”)

Or any other similar formatting argument.But Microsoft provides lot of predefined VBScript functions designed to reduce coding time:

When specifying the format argument, you can either type the Visual Basic constant name (name in left column), or the constant's corresponding value (0 - 4, from the middle column). They do the same thing, it's just less typing if you use the value.

I find using numbers very fast and useful. Like;

Format DateTime(Now,2)

## Saturday, November 13, 2004

### Server Variables and .NET

- Retrieves the user IP Address

IP = Request.ServerVariables ("REMOTE_ADDR")

- Retrieves the remote host IP Address

- Retrieves the page domain name

serverName = Request.ServerVariables("server_name")

- Retrieves the referer url

- Gets the browser type

- Retrieves page url

- Retrieves request mehtod .. get or post

- Retrieves server port

- Retrieves current page

- Retrieves server software

Consult the Microsoft Documentation for a full description of all the Server Variables.This handy list is a for commonly used server variables including referred above:

- ALL_HTTP
- AUTH_TYPE
- CONTENT_LENGTH
- CONTENT_TYPE
- GATEWAY_INTERFACE
- HTTP_ACCEPT
- HTTP_ACCEPT_LANGUAGE
- HTTP_CONNECTION
- HTTP_USER_AGENT
- HTTP_COOKIE
- HTTP_HOST
- HTTP_COOKIE
- HTTP_REFERER
- LOGON_ACCEPT_ENCODING
- PATH_INFO
- PATH_TRANSLATED
- QUERY_STRING
- REMOTE_ADDR
- REMOTE_IDENT
- REMOTE_HOST
- REQUEST_METHOD
- SCRIPT_MAP
- SCRIPT_NAME
- SERVER_NAME
- SERVER_PORT
- SERVER_PORT_SECURE
- SERVER_PROTOCOL
- SERVER_SOFTWARE
- URL

## Friday, November 12, 2004

### Various Articles on .NET

Using DateTime Picker With Null Support with MS Access and ASP.Net

Selecting, Confirming and Deleting Multiple DataGrid items in ASP.NET

CodeSnip: The Basics of MS Access with ASP.NET

CodeSnip: Bidirectional Sorting of a DataGrid in VB.NET

### .NET and MS Access Query

SELECT tblshipment.*, IIf(([D1]=2 And [D2]=6),((DateDiff("h",[orderdate],[shipmentdate]))-48),IIf(([D1]=2 And [D2]=7),((DateDiff("h",[orderdate],[shipmentdate]))-24),DateDiff("h",[orderdate],[shipmentdate]))) AS DD, Format([shipmentdate],"w") AS D1, Format([orderdate],"w") AS D2, DateDiff("h",[orderdate],[shipmentdate]) AS DDxFROM tblshipment;

By this, I am first finding out hours difference in order date and shipment date and then, looking for, if order date was Friday and shipment date is Monday, then reduce 48 hrs from time difference or if order date was Saturday and shipment date is Monday, then reduce 24 hrs from time difference, to give effect for weekend closure.

### MS Excel: Finding unique entries in a column

Assume Account Numbers are in column A and we created column F for this formula:

=IF(COUNTIF($A$1:A2,A2)>1,0,COUNTIF($A$1:A2,A2)) and copying it till the last account number row.

This gave value 1 for each unique account and 0 for repeatition.Just addition of this column provided me by number of unique accounts.