The below discussion is for Excel 2013. If a cell is selected and it has comments, the comments will be displayed. Sometimes, there may be a need to display picture when you select a cell. I answered this question on Microsoft Community Forum and I have decided to reproduce it here. 1. Right click the…
Excel Quiz 6
Tips & Tricks 76 – Whenever I Refresh a Pivot, its Column Width Changes
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.
Article 10 – Intersection Operator in Excel
Today, I am going to talk about a nifty but not so well known feature of Excel. This is about Intersection Operator in Excel. Before, I go deep in Intersection Operator, I would like to talk about Reference Operators in Excel. There are 3 Reference Operators – 1. Range Operator (represented by Colon) – It…
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…
Challenge 8 – Counting Number of single 1s in Rows in a Grid
Suppose you have a grid like below. The challenge before you is to count number of rows which have only single 1. See the grid, the yellow highlighted ones are containing 1 only once. The answer is 3 for this problem. You have to give me a single formula for the same. The relevant Excel…
Challenge 7 – Formula for Sum of Square of the Digits in an Alphanumeric String
Last challenge was about giving a non-array formula for Sum of Square of the Digits in a Numeric String. Now, the challenge is for an alphanumeric string. This time, there is no restriction that you need to give only non-array formula only. You may give array formula also. Let's say cell A1 contains an alphanumeric…
Article 9 – Overcome WildCard VLOOKUP / MATCH Problem when Target String is more than 255 Characters
Once again, I have got idea to write this article after I responded to this post Vlookup to find URL using wildcard in Excel Microsoft Community. Before I delve into problem statement, I want to start with some basic information. Maximum column width can be of 255 characters, this means that if I select a…
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…