Excel Session 3 in Hindi – (Revision of Session 2)

~ Formulas
~ Formatting
~ Custom Formatting
~ Conditional Formatting

Click here for Presentation

Click here for Excel File


How to Enter a formula
~ Click cell where you want formula results to appear
~ Type = and an expression that calculates a value using cell references and arithmetic operators
~ Cell references allow you to change values used in the calculation without having to modify the formula itself
~ Press Enter or Tab to complete the formula

Basic Formulas
~ Sum of values in column or row =SUM(cells/range)
~ Average value in column or row =AVERAGE(cells/range)
~ Minimum / Lowest value in column or row =MIN(cells/range)
~ Maximum / Highest value in column or row =MAX(cells/range)
~ Ranks for range or in column or row =RANK(value, cells/range)
~ Total count of numeric values in column or row =COUNT(range)
~ Total count of fill with values in column or row =COUNTA(range)

Relative and Absolute Cell References
~ A relative cell reference adjusts to its new location when copied or moved to another cell. Relative cell references are the default in Excel.
Example: the formula ‘=B2+C2+D2’ in E1 was copied to the cells below. The cell reference changes to match each row number.
~ An Absolute cell reference contains a dollar sign ($) before the column letter, row number or both. Coordinates preceded by the dollar sign ($) do not change when copied or moved to a new cell. In this example, $A$12 is an absolute cell reference. When the formula was copied from E2 to the cells below, that reference did not change.

Conditional Formatting

Conditional formatting in Excel is a tool that allows you to change the appearance of a cell or range of cells based on the contents of the cells.

There are several types of conditional formatting rules built into Excel and many more options available through customization.

Choose one of the built-in rules or choose ‘More Rules’ for customizable options

Great for finding zero values, values outside normal ranges, negative values, duplicates, and other specific amounts

Also can be used for finding dates and text values

How to export HTML Table into Excel via PHP

Create one variable and save whole table as string to that variable and just add below headers lines

//Header information
$header = "Header1" . "\t";
$header .= "Header2" . "\t";

//Reading the data thro' POST
$data= $_POST['header1']. "\t";
$data .= $_POST['header2'] . "\t";

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=xyz.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

download sample file