Excel Tips & Tricks:
1. There are numerous short cuts available in the sites but as FPA job profile most common short cuts that you explore to ease your job :
2. Control G = One of important function that can use
You will get following features :
- Blank cells in Excel
- Formulas in excel.
- Special function like fill the blank cell ( explained below )
- Find out Row Difference within a second.
-
- Find out Row Difference within a second.
Control G and select Row Difference Option like below screen shot :
Cell will highlighted where difference is there and put colour like below :
3. How to enter text or line item in in cell
Without going to different cell, we can do alt gr and enter together :
Excel Short cust - Alt gr and enter at a same time.You may need F2 for that.
How to select all tabs/worksheet with one shot :
Put cursor in worksheet 1 and then go to worksheet you want to select and then press shift plus mouse right button together
Below is the screen shot where can see all tabs selected
4. How to fill blank cells in excel :-
 |
| Pic 1 |
1. In the above example , want to fill A,B,C and D in the consecutive cells.
2. Select as per Pic 1 and Press Control + G and will get following screen :
3. Press
Special and select as per following screen :
4. Once you click OK and you will get following screen :
5. Then put =and give the link to cell reference and Control+enter
6.See the result below :
Vlookup = One of the most popular function use vlookup.
Formula :
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
5. Use Control D together:
Select the cells and press Control + D, will see following result.
6. How to use Up and Down arrow in excel to impress your reporting :
Step 1 :-
Go to Conditional Formatting ----> New Rule
Select the column where we want to show Up and down arrow :
Step 2 :-
You can change icon below :
7. How to format your number
- Select amount where you want to change the format :
Press
Control 1 ----> Number ------> Custom------> 0"CHF" or INR or USD
Pivot Table Tips :
1. Pivot bring into Classic view.
- Right Click on the Pivot Table.
- Go to Pivot Table Option.
- After Opening message box , Go to Display tab
- Click on Classic Pivot-table Layout option.
2. Repeat Pivot table items in every cells.
It may happen when you are doing pivot of any data , if you have same items one of the column you will not get repeated text / numbers for that column.See Below:
Step 1 :-
Go to PivotTable Option
Step 2 :-
Go to Field Settings option :
Step 3 :-
Tick Repeat Item Labels
Step 4 : -
See below the results after selecting :
3. How to remove "blank" from the Pivot Table.
Step 1 :-
Step 2:
Select the Pivot as below :
Step 3 :-
Do Control H i.e. Find and Replace and you will get following screen and then
(blank) and put
' in replace with:
Step 4 :-
Then click on Replace All and you will get following screen to get rid of (blank) showing in Pivot.
4. How to get rid of long formulas in Pivot , only cell reference will work.
Step 1 : -
When you create Pivot , if you want to do any formula between different rows and columns then you will getting GETPIOVT like below
Step 2 :-
You can avoid and can put cell reference by the following way:
Step 3:-
Click on Pivot table and go Option in the left side of your screen :
Step 4 :-
Untick "
Generate GetPivotData".
5. How to get rid of deleted items in the pivot and still its coming.
Step 1 :-
Have deleted Customer Name E from the data tab.
Step 2 :-
After deleting you will still get in the list.
Step 3 :-
To get rid off from the old data , Go to Pivot Table and right click and get the Pivot Table Option as below:
Step 4:-
Select from the drop down list as
None instead of Automatic and see the results in the drop down list
6.How to refresh Pivot table source data without changing data range
Step 1 :-
Select the range and provide name like following :
In the following example , data named as "SAMPLE"
Step 2 :-
Alt+N+V - shortcut to make Pivot and put SAMPLE as below
So going forward if you add line after that then do not need to Table/Range.
Please note that while doing naming for the table , select extract lines so that it can accommodate the future line items.
