1. ABSOLUTE FUNCTION
We always use to copy and paste cells containing formulas. But various times we do not want a cell reference to change when we copy it to other cells. So unlike relative reference, in absolute function the cell reference remains static.
An absolute function is completed with the use of adding $ to it.
Suppose when you copy a formula containing reference to A1 cell and paste it other cells, it will take relative reference (corresponding cell will get copied). That means, when you paste it in a column wise, A1 shall become A2, A3,A4,A5…… And when you copy it row wise, it becomes B1,C1,D1,E1…..
So we can stop it by use of $.
$A$1 It will lock row A and Column 1
$A1 It will lock only row A
A$1 will lock only column 1
We generally use $A$1 for crating formulae that contains reference.
The shortcut to get absolute function is press F4
So next time don’t waste your time and use absolute function. J
2. COPYING VISIBLE CELL ONLY
An accountant need to work on sheets containing large no. of data. Many times we hide certain rows and columns, and when we copy that data, the all the hidden rows and columns also get copied. I find it very irritating.
So is there any formula for copying only the visible cells.
Yes we can do it with the help of following steps.
Step 1 :- Select the data you want to copy (i.e visible cells)
Step 2: Press F5
Step 3: Click on special, down on GO TO pop up
Step 4: Check Visible cell only. Only visible cell get selected for copy.
Step 5: Copy and paste it. Only visible cell get copied & pasted.
3. PASTE SPECIAL –COPY ONLY FORMAT
Every time I take an interview of junior Accountants, I ask them WHAT IS PASTE SPECIAL IN EXCEL? , Seven out of ten time I get wrong answer.
It is one of the most useful function when you are working with lot of data contain formulae and formatting. It gives us option to paste only a particular character of the cell without copying the whole features of cell like copy only values, comments, Formats etc.
We are discussing here a situation when we like to copy only the format of a cell without copying the its contents. We can do it with the help of following steps.
Step 1 copy the cell ( control +C )
Step 2 take the cursor to cell where you want to paste it and right click. (use arrouw keys and press right click key on keyboard next to windows key)
Step 3 you can see the paste special function and click on it (Press S)
Step 4 A new pop up Screen With name PASTE SPECIAL will get open, check on FORMATS ( use arrow keys)
Step 5 Press Ok, only the format will get copied. ( use tab key and Enter key)
Like that we can copy only values, formula etc. with the same steps.
4. V LOOK UP AND H LOOK UP
It is always helpful for level of management, whether you are junior level accountant or a Chartered Accountant. To sort out data and to process it quickly, the VLOOK UP, H LOOL UP function is always handy.
Due to its importance, I have explained it with an example in this Article
5. WORKING WITH TEXT
An accountant is not a person dealing with only numbers, if so then he is mathematician. As we know the modern definition of accounting is “It is system of Information”. So If there are numbers, there will be some text.
So these following sets of formulae are handy in dealing with text data.
CHANGE THE CASE OF THE CONTENTS TO LOWER OR UPPER OR PROPER
Lower is used for changing text into lower case
Upper is used for changing text into upper case
Proper function converts the first letter of each word into Capital case
Ex- use proper (he is coming) to get He Is Coming
CLEAN TEXT DATA WITH TRIM
When you find a lot of space between words in a cell, you can clean up the extra space with trim function
EX- use trim ( he is coming) to get he is coming
COPY THE EXACT CHARACTER FROM RIGHT, LEFT OR CENTRE OF A TEXT
We always find data in a cell having lot of unnecessary things like a cell is containing “MR. Ramesh Kumar” and we only want “Ramesh Kumar” then we can use right (Mr. Ramesh Kumar,12) to get Ramesh Kumar . Here we used 12 since Ramesh Kumar has 12 character from right.
If we need only Mr. then we can use left(Mr. Ramesh Kumar,3) to get Mr.
Similarly we can use centre function.
CREAT DROP DOWN LIST OF TEXT
With the help of Alt and down arrow key you can make drop down list of all text which you have already entered in the column, so that you can easily pick the text again for your use.
6. WORKING WITH NUMBERS
ROUND A NUMBER TO NEAREST EVEN OR ODD NUMBER
Often we find data in decimal/fraction form, we can use even or odd or int function to convert it into a integer.
Ex- use ODD( 42.9) to get 43
Use even(42.9) to get 44
Use int(42.9) to get 42
MAX AND MIN FUCTION
It is used to get the maximum value and minimum value from the given values
If function is used when want output with some condition like number more than 5000
Use IF(Criteris, True value, False Value)
Ex:- IF(D4>5000,yes,No) to get yes or no
7. PIVOT TABLE
One of the most important tool for presentation of data processed by you from above formulae. It Presents the given data in more meaningful way by help of tables. To learn this please use this link