Over a period of time, Excel has become a complex piece of software. Many new features have been introduced and on top of that increasing requirement for security has introduced further complexity for Excel developers. Many times, users report many different kind of errors while using Excel. Following are the common ones – 1. An…
Author: Vijay A. Verma
Article 19 – How to Check if a cell contains a date
Checking for dates in Excel can be a very interesting problem as Excel stores dates as numbers. For example, 4-Jul-15 is stored as 42189. To elaborate further on this, 1-Jan-1900 is treated as 1. The difference between a date and 1-Jan-1900 +1 is the serial number of that date. hence, in case of 4-Jul-15, it…
Challenge 22 – Another Friday the 13th Challenge – Find Actual Dates themselves
Challenge 20 was about finding the number of Friday the 13th between two given dates. This challenge is about actually listing down the Friday the 13th dates themselves. Suppose, you been given 2 dates. A1=1/1/2014 A2 = 12/31/2015 The challenge before you is to write a formula which can be dragged down and will list…
Solution – Challenge 19 – Make Comparative Horizontal Bar Graph
Below is a proposed solution to the challenge – Challenge 19 – Make Comparative Horizontal Bar Graph 1. Download the Excel sheet from the challenge. 2. Put "F" without quotes in D1 and put following formula in D2 and drag down =-C2 3. Now select A1:B9, hold CTRL and select D1:D9. 4. Insert the bar…
Excel Quiz 17
Tips & Tricks 101 – Get Column Name for a Column Number
Let's suppose, you have a number in A1 and you want to get the column Name for that. Hence, if A1=1, you want "A" Hence, if A1 =26, you want "Z" Hence, if A1=27, you want "AA" and so on. The formula to derive the column name would be be – =SUBSTITUTE(ADDRESS(1,A1,4),1,"")
Tips & Tricks 100 – Get Sheet (tab) Name, Workbook Name and File Name through a formula
There are many situation while working in Excel that you need to get the name of the sheet. (Note – For formulas to work, the workbook must be saved at least once) The formula to retrieve file name would be – =CELL("filename",$A$1) The formula to retrieve the sheet name would be – =REPLACE(CELL("filename",$A$1),1,FIND("]",CELL("filename",$A$1)),"") Note –…
Article 18 – LTRIM and RTRIM through Excel Formulas
So, we have TRIM function in Excel. Almost all programming languages provide LTRIM and RTRIM functions also but Excel doesn't provide LTRIM and RTRIM. The same is provided in VBA but most of the Excel users are not using VBA. They are simple folks who want to accomplish their day to day job through Excel…
Challenge 21 – Male Female Pie Chart with Pictures
This time, you have a challenge to create a Male Female Percentage chart with pictures of Male and Female to denote the labeling of pie slices. There are 2 pie charts for your challenge. The files related to this can be downloaded from following links – Male_Female_Pie_Chart Excel File | Male Picture -1st Pie | …
Solution – Challenge 18 – Find the Longest Word in a List
Below is a proposed solution for the challenge – Challenge 18 – Find the Longest Word in a List Use following formula – =INDEX(A1:A10,AGGREGATE(15,6,ROW(A1:A10)/ ((MAX(INDEX(LEN(A1:A10),,))=LEN(A1:A10))),1)) The solution file can be downloaded from following – Solution – Challenge 18 – Longest Word