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…
Tips & Tricks 45 – Lock Cells Containing Formulas
1. CTRL+A to select entire Sheet or select top left corner between row 1 and column A. 2. Right Click > Format Cells > Protection = Click Locked Check box to remove tick mark from Locked to make all cells unlocked. 3. CTRL + G and click on Special. 4. Click on Formulas and OK…
Tips & Tricks 44 – Locate Hidden Rows and Columns
To locate hidden rows and columns, you can have following approaches – 1. Locate them manually. Hence, if after row 4, row 6 comes,row 5 is hidden. 2. ALT+EGSY and Enter. It will select all visible cells and it will mark hidden rows and columns with highlight. But on a white background, it may be…
Tips & Tricks 43 – Last Day of the Month for a Given Date
Suppose, you are given a date say 10/22/14 (MM/DD/YY) and we want to have the last date of the month for the given date. Hence, you needs an answer of 10/31/14. The formulas to be used in this case – =EOMONTH(A1,0) =DATE(YEAR(A1),MONTH(A1)+1,0) =DATE(YEAR(A1),MONTH(A1)+1,1)-1
Tips & Tricks 42 – Inputting Numbers Larger than 15 Digits
Sometimes, you may have a situation where you need to input numbers having more than 15 digits. For example, Bank Account Nos. What is 15 Digits Limit – Excel has number precision of 15 digits, it means that if you input numbers > 15 digits, 16 digits onwards, it will be 0s. Hence 123456789123456789 will…