Rogue links troubling your workbooks. You have visually inspected them and still you have no clue where to find them, then read on… Approach 1 – Easiest Approach Bill Manville offers a free Add-in which finds all links given in your Excel. The utility is a very popular one and it should be must have…
Article 21 – Clipboard Error in Excel – We couldn't free up space on the Clipboard
Many times when working in the Excel following errors may be encountered – > We couldn't free up space on the Clipboard. Another program might be using it right now > The clipboard cannot be emptied. Another program might be using the clipboard
Article 20 – Miscellaneous Errors in Excel – Sending Command to Excel, Greyed Out Options (Not Applicable to Windows 10)
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…
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…