Sun 25 Oct 2015

Article 28 - How to Unhide all Tabs (Worksheets)

By |Sunday, October 25th, 2015|Categories: Articles, VBA|Tags: , , , , , , , , |0 Comments

If you have many hidden worksheets and you want to hide them in one go, Excel doesn't provide an options to do so. There are many ways to to accomplish the same.

Option 1 - Use Custom Views

1. Unhide all of your sheets (you may use Option 2 or Option 3 below for the same also)
2. Views tab > Custom Views > Add and give this view a name (for example "ShowAll")

(more…)

Sat 10 Oct 2015

Article 27 - Remove Leading Apostrophe

By |Saturday, October 10th, 2015|Categories: Articles, VBA|Tags: , , , , , |0 Comments

Leading apostrophe is one of the widely used features of Excel. This is mostly used to make a cell treat its content as a character. It can also come through import of .csv files from various third party systems or data entries can be made with leading apostrophes. If you enter a number of more than 15 significant digits, Excel will immediately convert this to scientific notation. But there are business situations where you deal with numbers larger than 15 digits like credit card numbers which may be 16 digits. Now if you input credit card numbers as numeric, you will not be able to capture all 16 digits, so a solution is to enter them with leading apostrophe. It forces the cell to treat the entry as character and in character format you can show these 16 digits.(You can enter 32,767 characters in a cell though only 1024 characters will be displayed)

(more…)

Sat 26 Sep 2015

Article 26 - Converting Amount into Words (Indian Currency - Rupees and Paise)

By |Saturday, September 26th, 2015|Categories: Articles, VBA|Tags: , , , , , , , |4 Comments

Below is the VBA function where you can convert a given amount into Words on the basis of Indian Currency. India utilizes a system which is based on Hundred, Thousand, Lakh.....unlike English system which is based on Hundred, Million, Billion..(Indian Numbering System)

Below are numerical equivalent of Indian words

Lakh - 1,00,000
Crore - 1,00,00,000
Arab - 1,00,00,00,000 (more…)

Sat 12 Sep 2015

Article 25 - Reverse FIND / SEARCH & MID Function

By |Saturday, September 12th, 2015|Categories: Articles|Tags: , , , , , , |0 Comments

Excel doesn't offer any function for reverse FIND / SEARCH & MID function. VBA does offer a function INSTRREV but this article is for non-VBA folks or folks who do not want to use VBA in their spreadsheet.

Hence, the only option before us is to build them through formulas. Let's build a reverse FIND / SEARCH function.

Note - There are two differences between FIND and SEARCH which you must make a note of.

(more…)

Sat 29 Aug 2015

Article 24 - Convert a Number to a Month Name

By |Saturday, August 29th, 2015|Categories: Articles|Tags: , , , , , , |2 Comments

Use below formula to generate named 3 lettered month like Jan, Feb....Dec

=TEXT(A1*30,"mmm")

Replace "mmm" with "mmmm" to generate full name of the month like January, February....December in any of the formulas in this post.

(more…)

Sat 15 Aug 2015

Article 23 - Correcting Excel (MS Office) Errors Post Windows 10 Upgrade

By |Saturday, August 15th, 2015|Categories: Articles|Tags: , , , , , , , , , , , , , , |0 Comments

Last Updated - 29-Sep-15

Arrival of Windows 10 is something which everybody waited for and users had been loving it. But few users have been experiencing problems with MS Office and particularly with Excel post upgrade to Windows 10. Since, Excel is one of the most important productivity tools which all of us use every day, these problems are impacting productivity as well as delivery to customer. While MS Office on a fresh installation of Windows 10 is working great but for users who upgraded to Windows 10, some of them are feeling unlucky.

(more…)

Mon 10 Aug 2015

Article 22 - Detect corruption of MS Excel worksheet and Recover it

By |Monday, August 10th, 2015|Categories: Articles|Tags: , , , , |7 Comments

--- This post is contributed by Priyanka Chauhan of Stellar Info. This is not an endorsement of the product on behalf of eforexcel.com ---

Microsoft Excel is used worldwide majorly for business purposes. It is the simplest tool which helps in maintenance of records, timelines and tasks in a table based format. Most of us have used or currently use the row-column cell based approach of MS Excel in our day to day working. However, an Excel workbook can get as complicated as a mysterious maze if one gets lost. And that is primarily contributed to the high level of sophistication this simple looking software offers in terms of formula computations, macros and VBA code. This article will be focusing on a very important aspect of working with Excel sheets – their corruption and recovery from it.

(more…)

Tue 04 Aug 2015

Article 21 - Clipboard Error in Excel - We couldn't free up space on the Clipboard

By |Tuesday, August 04th, 2015|Categories: Articles|Tags: , , , , , , , |8 Comments

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

(more…)

Tue 04 Aug 2015

Article 20 - Miscellaneous Errors in Excel - Sending Command to Excel, Greyed Out Options (Not Applicable to Windows 10)

By |Tuesday, August 04th, 2015|Categories: Articles|Tags: , , , , , , , |0 Comments

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 error occurred when sending commands to the program
2. Microsoft Excel has stopped working
3. Excel is not responding
4. A problem caused the program to stop working correctly.  Windows will close the program and notify you if a solution is available.
5. Some options missed out / greyed out in tab or right click - Copy and Paste Greyed out (more…)

Sun 02 Aug 2015

Article 19 – How to Check if a cell contains a date

By |Sunday, August 02nd, 2015|Categories: Articles, VBA|Tags: , , , , |5 Comments

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 is 42189.

Hence, if you write 42189 and 4-Jul-15 in two different cells, Excel has no built-in mechanism to differentiate between these two.

(more…)