Ctrl + left arrow to go to the end of the sheet
Ctrl + right arrow to go to the start of the sheet from the end
ctrl + shift + right arrow to highlight the whole row
ctrl + shift + up or down arrow to highlight whole column
press the alt key to highlight shortcuts to different sections if
we press those particular buttons we will be able to navigate to
the respective section
Basic formulae
Any formula in Excel starts with =
=cell1 + cell2 for sum
=cell1 – cell2 for subtraction
=(cell1+cell2+cell3)\3 note: here if we don’t use brackets to add
the cells then according to Bodmas rule it will first calculate the
division from the latest cell then add the remaining cells
Ex: =1+2+3+4/4 Here it will first divide 4 by 4 then it will do the
addition
=sum(starting cell to end cell) to add a series of cell
Ex: =sum(b4b9) Note the above shown is syntax and it should
be the same or else it won’t work
=AVERAGE(starting cell to end cell) to know the avg of a series
of cell
Find and Replace:
It is used to find and replace values of cells on a larger scale
Ctrl + f will open a box where we can input the value to find the
value all over the table or we can also find the value closest to
the cursor and it is the same for replacing value.
We also have adv find and replace if we click on options btn in
the pop up we have a lot of adv find and replace options such
as
We have a option if we want to find and replace in sheet or
rows and if we want to lock any particular formula
Other options like match cases where it will only find and
replace if cases match with the input
We have match entire cell content which is important bcz lets
suppose we have two cells with values piece and one piece and
I want to change piece into leg piece then if I don’t select this
option then xl will change both piece and one piece into
Chapter-3 Text Formatting
Text functions
[Link] data left right center
[Link] data lower case upper case proper case
[Link] unwanted space
[Link] text strings together
Sorting data
1. Transposing data, columns to rows
2. Text to columns
3. Delimiting data
4. Count functions
If we copy paste data from other applications such as note pad
or word then xl will save the data in one selected rows. If we
want to divide the data then we can do it by selecting the data
and select data column then select text to column and then we
get 3 steps for conversion we should select delimited option
then we select the factor like space tab semi colon or comma to
be the factors where the text will be divided then if we want to
add all items into one column then we should select the all
items and copy while pasting we should go with special paste
and we will have a option known as transpose which will
arrange the data in to one column
