Count Functions in MS Excel

Count Functions in MS Excel
Count, Counta, Countblank, Countif

Count - Count only numeric value in a range not text
Counta- Calculates all except blank and including space in a range
Countif - Count no. of cell within a range that meet the given condition.
Countblank - Count blank cells space will be counted as zero.
























BACK TO CHAPTER 1

Concatenate in MS Excel

Concatenate in MS Excel

CONCATENATE is a text function used to join two or more text strings into one string.

Syntax: CONCATENATE(text1, [text2], …)


Format Painter in MS Excel

Format Painter in MS Excel

Format Painter copies formatting from one place and applies it to another quickly. It is usefull in Tables, Value and Header formatting. Double click the Format Painter button to apply the same formatting to multiple cells.

FIRST 10 MS EXCEL KEYBOARD SHORTCUTS

FIRST 10 EXCEL MS KEYBOARD SHORTCUTS

1. Ctrl+Z Undo
2. Ctrl+Y Redo
3. Ctrl+C Copy
4. Ctrl+V Paste
5. Ctrl+S Save
6. Ctrl+N New Workbook
7. Ctrl+O Open
8. Ctrl+F Find
9. Ctrl+P Print
10. Ctrl+A Select All

FIRST FIVE TEXT FUNCTIONS - PROPER, UPPER, LOWER, LEN, TRIM

MS Excel First Five Text Functions
PROPER, UPPER, LOWER, LEN, TRIM
















SUM, AVERAGE, PRODUCT, POWER (AN INTRODUCTION TO EXCEL MATH FUNCTIONS)

MS Excel Math Functions
SUM, AVERAGE, PRODUCT, POWER

Formula
Input
Output
SUM
=SUM(3,4,5)
12
AVERAGE
=AVERAGE(2,4,6)
4
PRODUCT
=PRODUCT(2,3,4)
24
CARET
=POWER(2,2)
4

MS EXCEL- Relative Reference and Absolute Reference

MS EXCEL- Relative Reference and Absolute Reference

Relative references
A relative cell reference in a formula, such as A1, is based on the relative position of the cell that contains the formula and the cell the reference refers to. If the position of the cell that contains the formula changes, the reference is changed. If you copy or fill the formula across rows or down columns, the reference automatically adjusts. By default, new formulas use relative references. 



For example, if you copy or fill a relative reference in cell D5 to cell D6, it automatically adjusts from =B5*C5 (shown in the above photo) to =B6*C6 (shown below.)



Absolute references
An absolute cell reference in a formula, such as $E$4, always refer to a cell in a specific location. If the position of the cell that contains the formula changes, the absolute reference remains the same. If you copy or fill the formula across rows or down columns, the absolute reference does not adjust. By default, new formulas use relative references, so you may need to switch them to absolute references. For example, if you copy or fill an absolute reference in cell D6 to cell D7, it stays the same in both cells: =$E$4.


For example, if you copy or fill an absolute reference in cell D6 to cell D7, the relative cells automatically adjusts from =(B6*C6)*$E$4 to =(B6*C6)*$E$4, however notice that the absolute reference remains the same in both cells: =$E$4.