— The Excel files related to this article can be downloaded from Dependent Drop Down List_Single Words & Dependent Drop Down List_Multiple Words — Creation of dependent data list is a topic which I wanted to write and I have got some time to talk about this. In this article, I will be covering the dependency…
Category: Articles
Article 13 – Generate a Unique / Distinct List out of a List when no blanks in the list / range
Suppose you have a list as given below and now task before you is to generate a list of unique / distinct list from column A. We will also be looking into generating Unique values from Columns A, B and C together which can be generalized to any number of columns. Note – This article…
Article 12 – Overcoming Wildcard Limitation in Sumproduct
In my view, SUMPRODUCT is the king of all Excel Functions and till Excel 2003, it was rightly so. But starting Excel 2007, SUMIF(S) and COUNTIF(S) conspired to dethrone the king named SUMPRODUCT. But once a king, always a king. There are many scenarios where SUMPRODUCT still comes handy and this is still one of…
Article 11 – How to Encrypt Excel Workbook
Note – The article is for Excel 2010 / 2013 versions and doesn't cover any version prior to Excel 2010. Excel 2010 / 2013 uses default 128 bit AES encryption which is considered highly secured. If you are interested in knowing all things about Excel security, I will recommend following MS article – http://technet.microsoft.com/en-us/library/dn194021%28v=office.15%29.aspx —-…
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…
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…
Article 8 – Calculating Percentage Change between New and Old Value
Many times, I get inspiration to write about articles when I post responses to questions on Microsoft Community. The inspiration to write about this has come from following post – Percentages You will also have many occasions particularly when you are asked to compute percentage change between two values. This is very much encountered in…
Article 7 – Generate a Sequence of Numbers
Many times, we have need when we want to generate a sequence of numbers for various purpose. When I started Excel, I simply used to put 1 in A1 and =A1+1 in A2 and dragged down to required number of rows. I, sometimes, still do it. Just old habits die hard. Over a period of…
Article 6 – Convert Text Format into Number Format
This article was long pending after I wrote Covert Number Format into Text Format. This post essentially deals with the fact that if a cell has number in text format, how to convert that value into Number format so that it becomes usable for calculations and for other purposes. (Exclusion – If a number has…
Article 5 – Difference Between Two Dates (Duration) in Terms of Months / Years
DIFFERENCE IN MONTHS There will be business scenarios when you will be asked to come up with duration between two dates in terms of months. — Note the Excel file for this article can be downloaded from DateDiff — Let's Say A1 has From Date of 10/5/2013 and B1 has To Date. You may use…