MS Excel:Quickly Navigate Multi-Sheet Workbooks

Right-click the the arrow buttons at the bottom left-hand side of the screen (the sheet tab navigation tool). You'll get a clickable menu of all the sheets in the workbook.

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

Since these characters have special meanings in Excel, you will need to precede them with a tilde to be able to search for them successfully.
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 ~?.

MS Excel:Change Text Case

Excel provides three handy functions to change the case of text.
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.

MS Excel: Formula Error and What It refers To

When you get an error in your formula cell, you’ll need to determine what caused the error and fix it.
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.

MS Excel:Remembering Cell Names

When you're doing a formula and can't remember the range names that you need, you can:

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

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

MS Excel: Quick Click Movement

If you Want to find out where a column ends or where the next blank cell is in the column.

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.

MS Excel:Range Names Used In Formulas

Named ranges let you calculate the total for all cells in a range with a formula such as

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.

MS Excel: Avoid error displays in formulas

Sometimes a formula may return an error message
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)

MS Excel: Indirect Function

Excel's INDIRECT function accepts a text string as an argument,and then evaluates the text string to arrive at a cell or range reference.Assume that cells B2,C2,D2 on our Master worksheet hold the student's name.
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.

MS Excel: Copy formula shortcut

If you have some formula in cell A3, go to cell A4 and press Ctrl+D and the formula will be copied there in cell A4.Similarly, cover cell A3 to A15, and press Ctrl+D, the formula will be copied from A4 to A15

.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

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.

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

If you've ever had to figure out which of the year's 365 days a particular date falls on, or how many days remain in the year, you've probably found that Excel lacks functions to perform the calculation. But you can create formulas to do the job.
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

MS Excel:Count AutoFiltered Rows

AutoFiltering a list hides rows that don't meet your filter criteria. After applying filtering criteria, Excel shows the record count on the status bar--but this value disappears when the sheet is calculated.
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.

MS Excel:Calculating a conditional average

In the real world, a simple average often isn't adequate for your needs.
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)

MS Excel: Word Count

Here is VBA Macro to count number of words in Excel spreadsheet as MS Excel does not have built in word counter like MS Word:

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

MS Excel: Delete Those Unwanted Sheets

Deleting the whole sheet, Excel makes it quick and painless:
Right-click on the sheet tab. Select Delete from the shortcut menu.

MS Excel: Add a Row or Column Easily

In Excel, the easiest way to insert or delete entire rows or columns is to use keyboard shortcuts: [Ctrl]+ and [Ctrl]-.
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.

MS Excel:Copying Format

Sometimes you'll want to copy only a range's formatting. The easiest way to do this is to use the Format Painter button (it's the one that looks like a paint brush on the Standard toolbar). To do so, first select the cell or range whose formatting you want to copy, then click the Format Painter button. When the mouse pointer changes to a paint brush, select the range to which you want to copy those formats. When you release the mouse button, Excel will paste the formats to that range and the mouse pointer will return to normal.
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]