What is Conditional Formating in MS Excel

What is conditional Formatting?
With conditional formatting you can change data color or
Background color of data by checking condition of data.

Example:-

Suppose you want to set every data greater than 18 year age will be in red then
It will be in red color by using conditional formatting functionality of excel."

How to use Conditional formatting in excel?
Step:-Click on HOME tab.
Step2:-Click on conditional formatting.
Step3:-There are some pre-defined rules will be available you can select.
Step4:-In every rule you set criteria greater, less, equal etc.
Step5:- A popup will appear to set criteria as you wish.
Step6:-done

Note:-
 You can clear conditional formatting also by going there at same location from where you have applied.

Example:-
 In image you will see full step by step explanation below.

What is Chart in MS Excel?

What is chart?
A chart is graphical representation of data. Charts are used for
Trend analysis of given data. With chart some one easily see
Graphical data up and down and other things.

Types of chart in MS Excel?

PIE chart
BAR chart
COLUMN chart
Line Chart
Combo chart

How to create Chart?
Step1:-Select your data
Step2;-Click on INSERT tab on ribbon.
Step3:- Click on chart.
Step4:-Select chart type as you wish.

Example:- Below 4 Graphical animation image is below for give full explanation.

Microsoft Excel How to insert formula in Cell

For inserting formula you have to write sign = in a cell where you want to apply formula. After write = sign you should write a letter . Now formula starting with that letter will be appear.

Example1:- Sum formula 
“=sum(C1,C3,C5)”
“=C1+C3+C5”
“=sum(A2:A20)”

 a)Above sum function will add all given 3 cells.
 b)You can write direct by equal sign.
c)Third one will add values from A2 to A20 i.e. in range addition

 Example2:- Substract formulas 
“=sum(C1,-C3,C5)”
“=C1-C3+C5”
“=sum(A2:A20)”

a)Above sum function will add and subtract.
 b)You can write direct by equal sign.
c)Third one will add and subtract values from A2 to A20 i.e. in range addition subtractions negative values will be subtracted.

What are common MS Excel Formulas?

Common Formulas   
  
Formulas          Example                                          Description

SUM                 =SUM(E1:E18)”                          Add for range E1 to E18
COUNT           =count(E1:E18)”                          Count no of cells in range
MIN                  =MIN(E1:E18)”                            give minimum value from a range
MAX                 =MAX(E1:E18)”                           give maximum value from a range
AVERAGE       =AVERAGE(E1:E18)”                   give average value from a range
LEN                 =LEN(E1)"                                    give text length
SUMIF             =SUMIF(E1:E12,">10",E1:E4)”    perform addition based on criteria.
AVERAGEIF    =SUMIF(E1:E12,">10",E1:E4)”    perform average based on criteria.
NOW               =NOW()”                                      give current date and time
       
       
STRING FORMULA   
   
FIND             =FIND("li","online",1)                  =find(findtext, withintext, startnumber)”
REPLACE     =REPLACE("online",2,2,"of")     =replace(oldtext,startnumber,numberofchar,newtext”)”
LEFT            =LEFT("onlinetutorial",4)             return left 4 char
RIGHT         =RIGHT("onlinetutorial",4)           return right 4 char
ISTEXT        =istext(value)                               return true if value is text
MID              =MID("GURU99",5,9)                  return middle text of given range 5 to 9
       
       
DATE TIME FORMULA
       
DATE            “=DATE(2019,1,4) ”                          return date
DAYS            “=DAYS(E1:E3)”                               give no of days between given date range.
MONTH       “=MONTH("4/1/2019") ”                   return month only
MINUTES    “=MINUTE("12:31") ”                        return minutes from time value
YEAR          “=YEAR("4/1/2019") ”                        return year value
       
NUMERIC FORMULA       
       
ISNUMBER       “=ISNUMBER(E3) ”                  return true if value is numeric
ROUND            “=ROUND(3.18789,4)”             to get round off of given value
MOD                 “=MOD(10,8)”                          return remainder
RAND               “=RAND()”                               generate random number between 0 to 1
PI                      “=PI()”                                      return value 3.14 value of PI
POWER            “=POWER(5,3)”                      “=POWER(number,power)” will give 5^3=5*5*5 from example
ROMAN            “=ROMAN(2018) ”                  convert number to roman number
MEDIAN           “=MEDIAN(9,2,5,1,7) ”             calculate median of numbers as given

What is function/Formula in Excel

What is formula/Function in excel?

In MS Excel formulas are pre-defined or custom function which are evaluated by
Excel application to give a value result.


How to apply formula in excel?

For apply formula on a cell you should write equal sign then write formula.
Because cell start with sign equal “=” will be evaluated by excel to give result value.

Ways to apply formula in MS Excel:-

There are three way to apply formula in excel:-
1. Discrete value  formula:-
Example:- =6+7

2. Discrete cell address  formula:-
Example;- “=A2*D3/A1”

3.Pre defined formulas(functions)
Example: - "=SUM(E1:E3)"

Important point to remember for apply formula:-

Suppose you want to apply formula then please remember BODMAS concept will be applied.
If you do not know BOADMAS then please read BOADMAS concept given below for given example.

BOADMAS example:-

“=(A2 * D2) / 2.”
Expressions( brackets) are evaluated first.
And then division evaluated.
And then Multiplication evaluated.
And in last addition and subtraction occurs.

What are logical functions in MS Excel

Logical functions in MS Excel?

IFERROR

"=IFERROR(5/0,"Divide by zero error")”
To display custom text when error occures.

IF
"=IF(ISNUMBER(22),"Yes", "No")
Display custom text based on condition true or false.

AND
“=AND(1 > 0,ISNUMBER(1))
For check multiple condition in AND function.

OR
"=OR(D8="admin",E8="cashier")
Check condition true of a given multiple conditions.

TRUE
“=TRUE()

NOT
"=NOT(ISTEXT(0))


IFNA
"=IFNA(D6*E6,0)
To display alternate value if NA occures.

NESTED IF
"=IF(A1="Sunday","GO for movie",IF(B1="Saturday","enjoy weekend","Do your work"))

Nested if is not different from if condition . In nested if if condition occurs in the other outer if.

What is grouping and un-grouping in MS Excel

What is grouping and un-grouping in MS Excel?

As the name of this grouping means a large data will be grouped
Based on some values . That value will head of the related data.

Example:-

In given example you will see that we have some country with some state
And we want to see all state in group with related country then we apply grouping
On country.

How to apply grouping in excel?

Below steps are given. All steps are also shown in given gif example.

Step1:-Click on DATA tab.
Step2:-Select all column and row of data.
Step3:- Click group data button.
Step4:- A  window will be open then select row and click ok.
Step5:- There are 3 options. Group and ungroup and subtotal.
Step:6:- Click sub total button to set criteria of count and summation of grouping data.

All above steps are shown in below GIF image:-

What is data filter in MS Excel

What is data filter?
Data filter is functionality to apply search criteria. Suppose you want to search some data with some specific name then you will be able to search.

Example:- Suppose you have data with multiple bank and a column you have bank then you can apply
Filter on bank column to see data of state bank only or any other bank which you will select.

How to apply filter in MS EXCEL?
Steps are given below:-

Step1:-Click on DATA tab in EXCEL.
Step2:- Select column to apply filter you want.
Step3:- Click on Filter option now filter will be applied on selected column.
Step4:- At first column you will see filter drop down will be seen .
Step5:- Click on this to search some specific name and select to filter out.
Step6: -You can apply here custom filter also by clicking on filter drop down.

Please see in GIF  example:-

What is data validation in MS Excel

What is data validation in MS Excel?
Data validation is good functionality to validate data to avoid mistakes or error at the time of
Data processing. Suppose a person entering data of PAN card then you can define length 10
For PAN card number entry. So this will help to write exact 10 text otherwise by mistake


How to apply data validation in MS Excel?

Ans:-Below steps are given to apply data validation.

Step1:- Click on DATA tab in EXCEL.
Step2:- Select a single cell or cell range where you want to apply data validation.
Step3:- Click on data validation drop down and then click on data validation .
Step4:- Now you will se a window with some tabs as below.
Step5:- Select validation criteria for your data in setting tab.
Step6:- Click on Error Alert tab in .
Step7:- Click on OK tab to save validation.

Now test your cells on which you have applied data validation.

Please see below example in GIF image for above data validation implementation.

How to Add Subtract Multiply And Divide In Excel

How to Add, Substract , Multiply and divide in Excel? 

Example1:- Sum formula
“=sum(C1,C3,C5)”
“=C1+C3+C5”
“=sum(A2:A20)”

a)Above sum function will add all given 3 cells.
b)You can write direct  by equal sign.
c)Third one will add values from A2 to A20 i.e. in range addition

Example2:- Subtract formulas
“=sum(C1,-C3,C5)”
“=C1-C3+C5”
“=sum(A2:A20)”

a)Above sum function will add and subtract.
b)You can write direct  by equal sign.
c)Third one will add and subtract values from A2 to A20 i.e. in range addition subtraction
negative values will be subtracted.

Example3:- Multiply formulas
“=C1*C3”
“=PRODUCT(C1:C3)”
“=PRODUCT(C1:C3, 3)”

a)Above multiply function will multiply direct
b)Multiply in range C1 to C3.
c)Multi ply in range from c3 to c1 and after in last multiply total with 3.

Example4:- Divide formulas
Note:  There is no DIVIDE function in Excel.
“=C1/C3”
divide  C1 with C3.

Microsoft Excel File Saving Compatibility

Suppose you are using MS Excel of latest version and If you are not using new features of MS Excel then you should Save file with version 2003 .xls format for compatibility to open older version of MS excel software."

Microsoft Excel Basic Shortcuts

Ctrl + P used to open the print dialogue window .
Ctrl + N creates a new workbook .
Ctrl + S saves the current workbook .
Ctrl + C copy contents of current select.
Ctrl + V paste data from the clipboard .
Ctrl +Alt+Enter for break line in same cell to write data .
F2 to blink cursor in cell.

Microsoft Excel Basic introduction

What is MS Excel? 

"MS Excel is application software which come with MS office software bundle.With MS Excel you Can manage numerical data with a lot of functionalities Such as searching sorting filtering etc. " 

What is MS Excel workbook? 
When you open MS Excel software if will create a book which have multiple spread sheet.

 What is Spreadsheet? 

Spreadsheet is collection of cells created by columns and rows to store information in tabular form.

 What is cells? 
A small unit of spread sheet made by row and column is called cell. Every cell have their ID.

Need of MS Excel?

 "Now a days MS Excel is very famous software to create data in file for the purpose of searching Sorting and graphical representation of data."

 How to install this software in computer? 

"With Microsoft office software kit you install this excel software. You can also buy it online from Microsoft corporation for your computer."

 Microsoft excel version? 

"There are multiple version of excel because every release of MS office software bundle have Different excel also."

 Note:- In this tutorial we are going to use MS Excel 2007 . Same practice you can do on Excel 2010 and 2013 also.