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," ",""))
Tips & Tricks 36 – Highlight Unique / Duplicate Rows
If you want to highlight Unique / Duplicate Rows, follow following steps 1. Select the data range where you want to apply this formatting. 2. Home Tab > Conditional Formatting > New Rule 3. Select Format only unique or duplicate values. In Format all: box, you can select duplicate / unique. 4. Format appropriately and…
Tips & Tricks 35 – Highlight Cells which are different from first Column Cells in a Range (And for Row Cells also)
Let's say you are having data as given below and you want to highlight all the cells in columns after first column where cell values are different. Confused??? B1 is not different from A1 where C1 is different from A1, hence C1 will be highlighted. In row 2 both B2 and C2 will highlighted and…
Tips & Tricks 34 – Get Sheet Name through Formula
Before getting this, make sure that you file has been saved at least once as this formula is dependent upon the file path name which can be pulled out by CELL function only if file has been saved at least once. Use following formula – =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"") Make sure that A1 is used in the formula….
Tips & Tricks 33 – Generate Week WORK Day Names Mon to Fri or Monday to Friday
1. Put "Mon" without quotes in a Cell. Let's assume this is in cell A1 here. If you want to generate full names of the day, put "Monday" without quotes. Note – The start day can be any WORK day. "Mon" is chosen for illustration purpose only. The start day will be whatever is put…