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

INDEX
~ Formulas
~ Formatting
~ Custom Formatting
~ Conditional Formatting

Click here for Presentation

Click here for Excel File

Formula

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

Cell Counting Techniques – Excel

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.
  • An array formula. Useful when the other techniques won’t work.

Continue reading Cell Counting Techniques – Excel

Summing hh:mm Time Values in Excel

Question

We have a column of cells in an Excel spreadsheet containing times like, 3:15, relating to the time spent on a task. At the bottom of the column I’ve used AutoSum to add up the times, but the result appears as either a date or funny number. What do I do to get a total in hours and minutes?

Answer

The formula you created with AutoSum is fine.

All you need do is apply an appropriate format to its result:

  • Select the cell containing the total
  • From the Format menu, choose the Cells command
  • From the Category list, select Custom
  • In the Type box, input the format [h]:mm
    (note the use of square brackets).
    If you want to show seconds in your total, input
    [h]:mm:ss
    (this format is listed in Excel’s Custom formats)
  • Click OK.