Suppose you have numbers in range A1:A100 and you want to sum up top N values =SUMPRODUCT(LARGE($A$1:$A$100,ROW(1:10))) In case, you want to ignore 0 values (and blanks) =SUMPRODUCT(LARGE(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10))) Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there…
Category: Tips and Tricks
Tips & Tricks 68 – Sum Bottom N Values in a Range
Suppose you have numbers in range A1:A100 and you want to sum up bottom N values =SUMPRODUCT(SMALL($A$1:$A$100,ROW(1:10))) In case, you want to ignore 0 values (and blanks) =SUMPRODUCT(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10))) Both the above formulas will function only if there are at least N values as per ROW(1:N). Hence, for above formulas, it would work only if there…
Tips & Tricks 67 – Show the Complete Data in Chart even though Data is Filtered
By default, charts in excel show filtered data only. To show complete data i.e. to ignore filtering – 1. Right click on Chart and click on Select Data. 2. Click on Hidden and Empty Cells at the bottom 3. Select Show data in hidden rows and columns. Now, you will have the result which you…
Tips & Tricks 66 – Show the Complete Area of a Worksheet as only Limited Area is being shown
If you can see only limited area of a worksheet 1. Check if the sheet is protected. This you check by going to Review tab and see if Unprotect Sheet Button is appearing. – If Unprotect Sheet Button is appearing sheet is protected. You need to click it and if password is asked, you need…
Tips & Tricks 65 – Show a Limited Area of Worksheet
You have a worksheet and you want to show only a limited area to users. Say you want them to see only A1:G50 and remaining you don't want to show them. Use following steps – 1. Click column H so that entire column is selected. Now CTRL+SHIFT+Right Arrow key so that all columns will get…
Tips & Tricks 64 – Show a 0 in place of Blanks in Pivot Table
When you create a pivot table and if a source cell has no value (i.e. cell is blank), in pivot table, it will show a blank. All other results in pivot table are carrying numeric values and you see blanks. It is odd though it doesn't impact the results. But you still prefer to show this…
Tips & Tricks 63 – Short Cut Key for Opening Goto Special Dialog Box
Remember EGS (EGGS with one G missing) Just Press ALT and hold it. Now Press E and leave it, then press G and leave it and then press S and leave S as well as ALT. And you will have Goto Special Dialog Box.
Tips & Tricks 62 – Set the Scroll Area of a Worksheet
If you want to set a scroll area in a worksheet, for example A1:G50. User will not be able scroll beyond row 50 and column G. To do this – 1. Right Click on a Sheet tab and click View Code. OR Press ALT+F11. Now VBE Editor will open. 2. Set A1:G50 in Scroll Area…
Tips & Tricks 61 – Roman Representation of Numbers
Use ROMAN function. Hence ROMAN(56) will give LVI. ROMAN works only for numbers 1 to 3999.
Tips & Tricks 60 – Remove numbers from string
To remove numbers from a string (for example Vij1aY A. V4er7ma8 contains numbers which are not required), we can use nested SUBSTITUTE function to remove numbers. Use below formula assuming string is in A1 cell – =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"") Note – Since this formula is in multiple lines, hence you will have to copy this…