Excel Trivia | Everything about Excel Function and Formulas: Basic

Operators are widely used when you don't want to use formula or don't know the proper formula for any operation.

Calculation operator including Plus, Minus, Substation, Division, Percentage, Power, Exponential are mathematics function that you can use in the mathematic operation. Mathematical operators are calculating in a sequence of PEDMAS(Percentage, Exponential, Division, Multiplication, Addition, Subtraction) order and you can divide in order by using parenthesis.

Arithmetic operators

The arithmetic operator calculates basic arithmetic calculations. It a manual calculation way of the formulas like Sum, Mul, Div etc

Arithmetic Operator	Meaning	Example
Plus (+)	Addition of two or more values	=1+2+3
Minus (-)	Subtraction of two or more values	=9-5-3
*Asterisk ()**	Multiplication of two or more values	=583
Forward Slash (/)	Division of the one value by other value	=15/3
Percentage sign (%)	Find percentage of the value by hundred	20%
Caret (^)	Power of the value	5^3
Parenthesis ()	Can calculate first parenthesis	=5*(9/2)

Examples:

Calculation of the 7+(6*5^2+3)
You have to use equal to sign before the equation starts in excel

we calculate this equation step by step as per PEMDAS rule
Step 1 Excel will calculate the values or equation in parenthesis. Here in the parenthesis, the first calculation is exponent value
Step 2 It will calculate multiplication values
Step 3 The addition in the parenthesis. now parenthesis values are completed
Step 4 addition with out of the parenthesis

Comparison Operators

Comparison Operator Compares two values and gives the result as TRUE and FALSE
IF the comparison is valid then it shows TRUE otherwise it shows FALSE

Comparison Operator	Meaning	Example
Equal (=)	Equal to	=A1=B1
Greater Than (>)	Greater than	=A1>B1
Less Than (<)	Less Than	=A1<B1
Greater Than Equal To (>=)	Greater and Equal	=A1>=B1
Less Than Equal To (<=)	Less and Equal	=A1<=B1
Not (<>)	Not Equal To	=A1<>B1

Examples

The comparison operator is used to compare two values. It will return TRUE or FALSE in result

We have taken two values and checks conditional operator

Step 1 Equal to: Here Value 1 and Value 2 both are different so result is FALSE

Step 2 Greater: Here Once value 1 is not greater than value 2 so result is FALSE but for second option value 1 is greater than value 2 so result is TRUE

Step 3 Less : It is opposite of the greater. Here value 1 is less than value 2 so result is TRUE and for second option Value 1 is not less than value 2 so result is FALSE

Text Operator

Text operator is used to combine or join two or more words as a string.

Text Operator	Meaning	Example
Ampersand (&)	Concatenates two values into one	=”Nathan”&” Moska” Will return Nathan Moska

The Amparsand operator is same as the function Concatenate

Reference Operator

Reference operators used in formulas for range selection and range modification

Reference Operator	Meaning	Example
Colon (:)	To select range from one specific cell to another	A1:A5
Comma (,)	Combines Multiple references to one reference	=(A1:A5,B1:B5)
Space ( )	Intersect operator, Common values from the two ranges	A1:B5 B3:C5

Reference operators used to modify the formula

Colon(:) The colon operator is used to select the range of the data

This is known as a range from A1:B4

Comma(,) The comma operator is used to join two or more ranges

This is the two ranges and selected using comma operator
(A1:B4,D1:E4)
If you are using operator you should use parenthesis.

Space( ) The space operator is used to intersect the cell value from the two different ranges

The sum of the intersect cells are shown
From the range A1:B4 and B3:C4 only two cells are interset(B3 and B4)

And sum of these two values are shown in the result

In Microsoft Excel cell is a rectangle box in which you can write, edit and modify or format a data as per your requirements.
Here we are going to see some of the details of the cell, row and column

What is cell?

Cell is a rectangle box which has an address like A1, B2, C3...

Start new line in a cell

Use Alt + Enter to start a new line in cell.

How much Rows and Columns in a sheet?

Just press left mouse button to the intersection of the column and row

See in the image when you press left mouse button in the place of circle will show you how much rows and columns in the sheet
Here is 1048576 Rows and 16384 Columns.
So here 1048576*16384=17179869184 cells in a single sheet.

Row

Row is a horizontal bunch of cell in a spreadsheet

Remove extra lines from the cell

Just double click below the row line

In this image double click below row number 6.
When you take a cursor to line blow 6 a plus sign appeared then press double click. This will remove extra space between the lines in the cell.

Column

Column is a vertical bunch of cell in a spreadsheet

Remove extra spaces in column

Just double click on the topmost row line
In this image double click after column B
When you take a cursor to top of the B column on the partition a plus sign appeared then press double click. This will remove extra space between the lines in the cell.
You can also use TRIM Function to remove extra spaces

Remove blank cells

Step. 1 Select the Row or column in which you want to delete the blank rows or columns
Step. 2 Press Ctrl+G a Go To window will open
Step. 3 In go to window select special
Step. 4 Then select blanks. it will select all blank cells of the row or column
Step. 5 Then right-click on any selected cell and select entire row or entire column
Step. 6 It will remove all blank cell.

Insert or delete cell/row/column?

Insert the cell

Step 1. Select cell
Step 2. Right-click(Or ctrl + + sign)
Step 3. A small window will open

Step 4. Select the option where you want to insert a cell. Shift Cell Right Will shift the existing cell to the right side. Shift Cell Down will sift the existing cell to the downside

Step 5. Select the option Entire Row or Entire Column to insert Row or column(Short cut to Insert Row "Select one row and press ctrl + +sign" and for Column "Select one Column and press ctrl + +sign")

Delete the cell

Step 1. Select the cell

Step 2. Right Click(Or ctrl + -sign)

Step 3. A small Window Will open

Step 4. Select the option which cell you want to delete. Shift Cell Left will delete existing cell to the left side. Shift Cell Up will delete the existing cell to the upside.

Step5. Select the option Entire Row or Entire Column to delete Row or column(Short cut to Delete Row "Select one row and press ctrl + -sign" and for Column "Select one Column and press ctrl + -sign")

Merge cell

To combine more than one cell is known as merging the cell.
Step 1. Select the cells which you want to merge
Step 2. Click on the merge cell from the alignment section of the ribbon.
Merge Across
Merge across will merge the columns only
Unmerge Cell
By simply applying unmerge cell, you can unmerge all selected area.

How To Use Operators | Microsoft Excel