You have a number 123456789 and you quickly want to apply Thousand Separator Custom Format with Two Decimal Places. Hence, you are seeking an answer of 123,456,789.00. Excel has already provided a short cut to do the same – CTRL+SHIFT+!
Author: Vijay A. Verma
Tips & Tricks 53 – Print Comments in the Sheet
1. You need to show the comments which you want to print or you can show all comments in the sheet. To show the comments Show Comments in the Sheet 2. First step is mandatory for printing the comments. Only those comments will be printed which are shown in the sheet. 3. ALT+PSP 4. After…
Tips & Tricks 52 – Show Comments in the Sheet
1. If you want to show all comments in the sheet – 1.1 Go to Review tab and click on Show All Comments. 2. If you want to show selected few comments (not all) in the sheet – 2.1 Click a cell where the comment is. 2.2 Go to Review tab and click on Show / Hide…
Tips & Tricks 51 – Not able to see few rows / columns what to do
There are two possibilities – 1. Those rows (columns) are hidden. 2. Rows (columns) width is very small. To uncover them – 1. Press CTRL+A to select entire sheet. 2. Double click on white line joining two numbers for row (two column alphabets in case of columns) and all these rows (columns) will be uncovered….
Tips & Tricks 50 – My Developer Tab is Missing, how to show
1. File > Options > Customize Ribbon 2. Check Developer
Article 5 – Difference Between Two Dates (Duration) in Terms of Months / Years
DIFFERENCE IN MONTHS There will be business scenarios when you will be asked to come up with duration between two dates in terms of months. — Note the Excel file for this article can be downloaded from DateDiff — Let's Say A1 has From Date of 10/5/2013 and B1 has To Date. You may use…
Tips & Tricks 49 – My Column Numbers are 1,2,3….rathar than A, B, C…..
It means your R1C1 reference setting has become enabled. If you haven't done it manually, then it means that you have opened a sheet which was R1C1 enabled and any sheet opened after that will be opened in R1C1 mode only. To go back to A,B,C column notations which is called A1 style referencing. 1….
Tips & Tricks 48 – Multi Column VLOOKUP
You know VLOOKUP, one of the most loved function of Excel. The syntax is VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Here look_value can be a single value not multiple values. Now, you are having a situation where you want to do vlookup with more than 1 values. For the purpose of illustrating the concept, let's say we have 2 values…
Tips & Tricks 47 – Monitor the Value of Cells
If you are working in a big sheet and want to monitor values in a cell which is far or in other worksheets, you can make use of Watch Window. 1. Go to Formulas Tab and click on Watch Window in Formula Auditing section. 2. Now Watch Window will appear. 3. Click on Add Watch to…
Tips & Tricks 46 – Microsoft QUERY Error – the data source contains no visible tables
Sometimes, people prefer to use Microsoft Query to extract data from Excel sheets and you encounter this error – The Fix – Query will recognize only those tables whose range has been named. It will not recognize any range which has been made into Tables. Hence, only named ranges are considered as tables for Query…