When we are working with some application, we use same method to finish some task. After sometimes we think for an alternative method to perform our task more faster and easy way. However, it will come with experience and take sometime to know the smarter way of doing the things.
Now, its not required years of experience in Excel to work smarter! Spending sometime on reading this chapter will make you to execute the tasks smarter way and it will improve the productive.
Excel Tips – How Tos:
1. How to Display Formulas Instead of Results in Excel Cells?
You Can Toggle The Formulas And Results By Pressing ” Ctrl-~ ” (The Second Key Stroke Is Tilda(~)).
2. How to Display the Cell Value Instead of Formulas in the Formula Bar?
You Can Highlight The Address And Press “F9” To Display The Values.
3. How to Highlight All Cells Referenced by a Formula?
You can select a Cell with Formula and Press “Ctrl-
4. How to Highlight the Formulas that Reference a Cell?
You Can Select All Cells With Formulas Reference The Current Cell By Pressing “Ctrl-]”
5. How To Add Content Or Formatting To Multiple Sheets At Once?
You Can Select The Multiple Sheets Together And Add The Content Or Format The Cells
Note: Do Not Forget To Un-group The Sheets, Once You Are Done With The Grouped Activities.
6. How to See Toggle the Relative and Absolute References?
You Can Activate A Cell (By Double Clicking the Cell or Pressing F2) And Press “F4” To Toggle The Relative And Absolute References
7. How to Auto Fill the Cells?
To fill a list or series across columns or down rows type Jan in any cell and place your mouse pointer over the bottom-right corner of the cell (until the mouser pointer changes to a small black cross). Then Left click and drag down or across.
This can also be done with Numbers, Weekdays, Quarters, user-defined lists or any text that ends in a number e.g. Department 1.
8. How to Add Hidden Text to Formulas?
You can use N() formula. For example instead of “=5000+2000”, you can use “=5000+2000+2500 +N(“Oranges from Store A+Apples from Store B+ Banana From Store A”)”.
The N() function will convert any text to zero, so it helps you to put notes kind of text in your formula for future reference
9. How to Repeat the Same or Previous Operation?
You Can Press F4 To Repeat An Operation.
10. How to View All Named Ranges in Sheet?
Set zoom level of less than 40%, You can view all the named ranges.
11. How to fill the same content in a whole range?
Select the range, Enter the value and Press Ctrl+Plus+ Enter
12. How to attach active workbook to a new email?
13. How to Freeze or UnFreeze in the Sheet?
14. How to add Filter and remove Filter in the Sheet?
Note: If you want to filter for particular row, Select the particular row and then press Alt+D+F+F
15. How to Paste the Values (Paste Special Values)?
16. How to Transpose the Data (Paste Special Transpose)?
17. How to Auto Fit Rows and Columns ?