Complete Microsoft Excel (Basic) Course

Microsoft Excel

Introduction

Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical Data.

Parts of a Spreadsheet Software

1. Cell
2. Row
3. Column
4. Sheet
5. Workbook

Basic Formulas

1. Sum/Add          - =SUM(value)
2. Subtract             - =value1 – value2
3. Multiply/Product - =PRODUCT(value)
4. Divide - =value1 / value2
5. Percentage - =value1 * value2 %
6. Average - =AVERAGE(value)
7. Count - =COUNT(value)
8. Maximum - =MAX(value)
9. Minimum -   =MIN(value)
10. If             - =IF(logical_test,[value_is_true],[value_if_false])
11. Multiple Ifs -        
    =IF(logical_test,IF(logical_test,IF(logical_test,[value_is_true],[value_if_false])))

Formulas for Accountancy

1. GST Calculation - Taxable Value * (100 + GST) %
2. Taxable Value - Invoice Value[1+(GST Rate)/100]

Custom Sheet Formulas

1. Static Formula – can’t change the value.

                Ex - =6+5

2. Dynamic Formula – can change the value as per the formula

                Ex - =A1+A2

References

1. Relative Reference - A relative reference in a formula refers to a cell location that is relative to the position of the formula.

            Ex - =A1+B1

2. Absolute Reference - An absolute reference in a formula refers to a fixed cell location that does not change when the formula is copied or moved to another location.

            Ex - =$A$1+$B$1 (Use $ sign or F4 key)

Useful Shortcuts

1. Ctrl + T         - Convert range into Table
2. Ctrl + ;                - Current Date
3. Ctrl + Shift + ; - Current Time
4. Ctrl + Shift + $ - Currency Format
5. Ctrl + Shift + % - Percentage Format
6. Ctrl + Shift + ! - Decimal Format (2 Digits)

Course Created by Vatsal Saxena (Full Stack Developer, Enkidu.in)