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…
Author: Vijay A. Verma
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…
Tips & Tricks 41 – I have data for many years but I want the sum for only last 12 months
Let's assume that your data is aligned like this and you want to have sum only for last 12 months. This should take care of if future entries are done. Hence, if Jun data is entered, sum up has to start from Jun month. – Use this formula – =IFERROR(SUM(OFFSET(INDIRECT("$B"&COUNTA($A:$A)),0,0,IF(COUNTA($A:$A)-12>0,-12,-COUNTA($A:$A)+1),1)),"") Note – If you have…
Tips & Tricks 40 – I don't want to Show Horizontal / Vertical Scroll Bar(s)
1. Go to Office button > Excel Options > Advanced 2. Uncheck Show Horizontal Scroll Bar / Show Vertical Scroll Bar as per your need. To reverse, check them.
Tips & Tricks 39 – I don't Want to Show Gridlines in the Sheet, how to do..
Excel displays gridlines by default. If you don't want to show gridlines – 1. Go to View Tab 2. Un-check Gridlines box. This is specific to sheet. Hence, if you want want to do for entire workbook, you will have to 1. Go to File > Options > Advanced 2. Select your workbook in "Display…
Article 4 – Dynamic Charting in Excel i.e. Create a Chart which Automatically Updates when Data is Added or Removed
When you create a chart, you select a range. Now assume, one more row of data is added. But that will not be reflected in your chart as your chart had a fixed range. — The Excel for this article can be downloaded from Dynamic Charting — A work around is to select more rows…
Tips & Tricks 38 – I am not able to Drag and drop a Cell
Earlier I was able to hold the bottom right corner of a call and was able to drag and drop it. Now, I am, no longer, able to do it. To solve this – 1. Go to Office Button > Excel Options > Advanced 2. Click Enable fill handle and cell drag-and-drop to mark it…
Tips & Tricks 37 – How Many Characters a String is Containing not considering Blanks?
Say, you have a string like Vijay A. Verma and I need to know how many characters it has. In this case, it has 12 including decimal and leaving blanks aside. Use below formula for the same – =LEN(SUBSTITUTE(A1," ",""))