You can stop Pivot from doing this behaviour. Take following steps – 1. Right Click on Pivot Table > PivotTable Options 2. Uncheck Autofit column widths on update.
Category: Tips and Tricks
Tips & Tricks 75 – What is DATEDIF and it is not available in my Excel
DATEDIF calculates the difference between two dates in terms of COMPLETED Days, Months and Years. Note the emphasis on COMPLETED. It means that if elapsed years or months or days is 1.78, the answer would be 1 only. Hence, it gives only integer and doesn't round them. A good documentation exists on following link http://www.cpearson.com/excel/datedif.aspx…
Tips & Tricks 74 – We have AVERAGEIF. What about MEDIANIF and MODEIF?
Excel doesn't provide MEDIANIF and MODEIF. You will have to use Array formulas to achieve these functionality. Let's assume that our data is like below – To calculate MEDIANIF and MODEIF, enter below formulas i.e. not by pressing ENTER after entering your formula but by pressing CTRL+SHIFT+ENTER. This will put { } brackets around the formula…
Tips and Tricks 73 – Use Conditional Formatting to Highlight Duplicate Cells
Suppose your data is in range A2:A100 and you want to highlight all those cells which are duplicates. 1. Select A2:A100 and Home Tab > Conditional Formatting > New Rule 2. Put following formula after clicking "Use a formula to determine which cells to format" =COUNTIF($A$2:$A$100,A2)>1 3, Click on Format Button to format the cells…
Tips & Tricks 72 – Sum Every Nth Row
If your numbers are in range A1:A100, use below formula =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) Above formula is for every 2nd row. Replace 2 with N. Hence, for every 5th row – =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0)) This is a generic formula and will work for any range. If you range is B7:B50, your formula would become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Tips & Tricks 71 – Sum Every Odd Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)<>0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)<>0))
Tips & Tricks 70 – Sum Every Even Row
If your range is A1:A100, use following formula. =SUMPRODUCT((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0)) This is a generic formula, hence if your range is B7:B50, your formula will become =SUMPRODUCT((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
Tips & Tricks 69 – Sum Top N values in a Range
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…
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…