Sat 27 May 2017

Excel Quiz 52

By |Saturday, May 27th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , |0 Comments

Excel Quiz 52 - A Quiz on Formulas Tab in Excel

This quiz checks your knowledge about Formulas tab.

Sat 29 Apr 2017

Excel Quiz 51

By |Saturday, April 29th, 2017|Categories: Quizzes|Tags: , , , , , , , , , , , |0 Comments

Excel Quiz 51 - A Quiz on Page Layout Tab in Excel

This quiz checks your knowledge on Page Layout tab in Excel.

Sat 04 Mar 2017

Solution - Challenge 59 - Clean the Problem Workbook Data

By |Saturday, March 04th, 2017|Categories: Solutions|Tags: , , , , , |0 Comments

Below is a possible solution to the problem Challenge 59 - Clean the Problem Workbook Data

Formula to convert would be which you need to drag down would be

=--SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8237),""),UNICHAR(8236),"")

Data has UNICHAR(8237) and UNICHAR(8236) prefixed and suffixed which need to be replaced by above formula.

Sat 25 Feb 2017

Challenge 60 - Generate a list of 2nd and 4th Saturdays for a Year

By |Saturday, February 25th, 2017|Categories: Challenges|Tags: , , , , , , , |1 Comment

In India, 2nd and 4th Saturdays are very important days as on these days, the banks remain closed. Hence, banks in India are closed on all Sundays and 2nd and 4th Saturdays apart from their holiday calendar.

To calculate Networkdays for banking industry in India needs that the list of 2nd and 4th Saturdays needs to be generated. You know the syntax of NETWORKDAYS -

NETWORKDAYS.INTL(Start_Date, End_Date, [Weekend],[Holidays])

In weekend parameter, we can specify Sunday as weekend and in Holidays list, we can put the holidays and also the 2nd and 4th Saturdays.

Now, the challenge before you is to write a formula which takes Year as Input from A1 and which when dragged down produces the 2nd and 4th Saturdays.

1

Note - The solution to above problem will be published after a month i.e. on 25-Mar-17.

Sat 04 Feb 2017

Challenge 59 - Clean the Problem Workbook Data

By |Saturday, February 04th, 2017|Categories: Challenges|Tags: , , , , , |1 Comment

Download the the workbook from the below link. The Challenge before you is to write a formula to clean the data. If you perform any mathematical operation on the data, it will return #VALUE error. You need to clean the data through a formula.

Challenge 59 - Problem Workbook

The solution to the above problem will be posted after a month i.e. on 4-Mar-17.

 

Sat 28 Nov 2015

Tips & Tricks 120 - Filter by Color Drop Down Menu Greyed Out Problem

By |Saturday, November 28th, 2015|Categories: Tips and Tricks|Tags: , , , , , |3 Comments

Sometimes, you find that though your are having colored cells but Filter by Color is greyed out i.e. it can not be selected.

Following are the possible causes -

1. Your sheets may be grouped. If they are grouped, you will notice word "Group" in the title bar of your Excel workbook.

1

Another way to check whether the sheets are grouped or not is to right click on any tab name and option "Ungroup Sheets" will appear:

1

2. There may be blank lines before a color is encountered in your range. Filter doesn't like blank cells. Hence, be watchful.

3. The workbook may be shared. If this is a shared workbooks, the word "Shared" will appear in the title bar of your workbook.

1

To remove sharing > Review tab > Share Workbook > Uncheck the box in Red zone

1

4. Also if first colored cell appears after approximately 10000 rows, Excel may not detect the color. To overcome this behavior, put a color in the first cell and now it will enable the Filter by Color. Only thing is that this colored cell will be extra in your filtered list.

Sat 07 Jun 2014

Tips & Tricks 9 - Circular Error is Coming..How to find where is the problem

By |Saturday, June 07th, 2014|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

You are working with your spreadsheet and all of a sudden, you get following error -

1

To know where is the problem, click OK on this.

Go to Formulas > Error Checking > Circular Reference

This will list a problem. Work on this problem cell and remove circular reference. If there are more problem cells, Circular References will list a cell. Keep removing till Circular References doesn't have any cell listed. Key thing to remember is that it will not list all cells in one go but will list only one problem cell.

1

There are Circular References problem cells can also be visually located by looking at the bottom left of the Sheet. When sheet contains, 0 Circular References, then this will not appear.

1