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.