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 29 Aug 2015

Challenge 24 - Sum a Range Conditionally where Range Inputs are variables

By |Saturday, August 29th, 2015|Categories: Challenges|Tags: , , , , , |1 Comment

--- Excel related to this challenge can be downloaded from Challenge - Sum a Range Conditionally ---

Suppose, you have values in column A and D1 and D2 contains the range references. D3 contains the condition value for greater than. In this example, you will need to sum up between A4 and A8 where values are > 30.

Of course, A4, A8 and 30 are variables and driven by values in D1, D2 and D3.

Challenge - Sum a Range Conditionally

You may post your answers in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 28-Sep-15.

Sat 22 Aug 2015

Tips and Tricks 106 - Find the nth Largest Number when there are duplicates

By |Saturday, August 22nd, 2015|Categories: Tips and Tricks|Tags: , , , , |0 Comments

You know the LARGE function which can find the nth largest value. Hence, if you have a series like below -

1

And you give =LARGE(A1:A10,3), you get the answer as 18

Now, if we have a series like below

1

Now, you give =LARGE(A1:A10,3) and now the result is 24. The reason is that large function gives the nth largest value in a sorted array. Hence, LARGE function will sort the above array as {24,24,24,22,22,18,18,9} and 3rd largest is 24.

But actually you want the unique 3rd largest which is 18 as the answer.

The formula for such case would be

=LARGE(IF(FREQUENCY($A$2:$A$10,$A$2:$A$10)<>0,$A$2:$A$10),3)

Sat 22 Aug 2015

Excel Quiz 19 - Excel Specifications and Limits Quiz - Part I

By |Saturday, August 22nd, 2015|Categories: Quizzes|Tags: , , , , , , |0 Comments

Excel Quiz 19 - Excel Specifications and Limits Quiz - Part I

This is a quiz on Specifications of Excel and Limits in Excel for Excel 2007 and onwards

Sat 22 Aug 2015

Tips & Tricks 105 - Column Chart with Primary and Secondary Axes

By |Saturday, August 22nd, 2015|Categories: Tips and Tricks|Tags: , , , , , , |0 Comments

You want to draw a column chart with two data series and one you want to make a primary and one you want to make secondary.

Below, you have drawn a normal column chart in first chart. The second series (orange one), you have converted to secondary axis. Now, it has overlapped the first series. You have tried all options to adjust Series Overlap and Gap Width but still first data series is not visible.

If you want to overcome this, read on.....

1

1. Right Click on Chart > Select Data > Add
Give a name to this series, let's call it Dummy1.
Put a value large enough in Series Values: box. The default populated is ={1} which you can make to ={80} for my case. The purpose is to make this series visible. Hence, you need to choose this value appropriately.

2. Now, right click on Dummy1 > Format Data Series > Primary Axis

3. Right Click on Chart > Select Data > Edit Dummy1. Put ={0} in Series Values: box

1

4. Move Dummy1 to between our original two series.

1

5. Add another series with name Dummy2 and put ={0} in Series Values: box.

6. Move Dummy2 to after Dummy1 and before Sales2.

1

7. Now, delete Dummy1 and Dummy2 from your legends.

You have a perfect looking column chart.

1

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…)

Sat 15 Aug 2015

Challenge 23 - Make Bar Chart to Show Performance

By |Saturday, August 15th, 2015|Categories: Challenges|Tags: , , , , |1 Comment

This time challenge is to create a bar chart like below to show the Target Planned and Target Achievement.

Related Excel you can download here Salesman Performance Challenge

Salesman Performance

You may post your solution in comments section.

Note - Solution to this challenge will be published after 1 month i.e. on 14-Sep-15.

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…)

Mon 10 Aug 2015

Solution - Challenge 20 – Find Number of Friday the 13th between Two Given Dates

By |Monday, August 10th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

Below is a possible solution to the challenge - Challenge 20 – Find Number of Friday the 13th between Two Given Dates

The formula for finding this would be -

=SUMPRODUCT((TEXT(ROW(INDIRECT(A1&":"&A2)),"ddd")="Fri")*(TEXT(ROW(INDIRECT(A1&":"&A2)),"dd")="13"))

Sat 08 Aug 2015

Tips & Tricks 104 - Hide a Sheet Securely

By |Saturday, August 08th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , , , , |0 Comments

You want to hide a sheet so that nobody else can open that. This may arise in some situations like you have a dump of employee data which has sensitive information like salary, last rating, age etc. You want to keep a copy of this in your workbook but you want to refer to only few fields which are non-sensitive in nature. Then, you can resort to below trick -

1. Save your sheet as macro enabled i.e. with .xlsm extension.
2. ALT+F11 to open VBA window (This can also be opened by taking right click in the tab and choosing View Code)
3. Locate your workbook name in Project Explore window which is in top left corner. If this is not available, you can either do CTRL+R or View > Project Explorer
4. Click your sheet.
5. You have Sheet Properties window below Project Explorer. If not available, do F4 or View > Properties Window
6. Last option in Properties Window is Visible, which you should set to 2- xlSheetVeryHidden

1

7. Go back to your workbook / sheet in Project Explorer. Right Click and Insert Module. - This is an important step and many people generally forget about this step.
8. Tools > VBA Project Properties > Protection tab - Check Lock Project for Viewing and put passwords.

1

Now, your sheet can be unhidden only by those persons who know the password for your VBA project.