Mon 16 Apr 2018

## Excel Quiz 55 - Crossword VIII

By |Monday, April 16th, 2018|Categories: Quizzes|Tags: , , |0 Comments

Time for Crossword Again after a long time. This crossword is interactive and you can use browser to play with this. Click in the Grid to start.

Click on Check Puzzle just above Questions to check your score.

If you want to print the Crossword on a paper to play with, download from here Sep-17 eforexcel.com Crossword

Tue 20 Feb 2018

## Solution - Challenge 69- Need Help in Not Showing Comments

By |Tuesday, February 20th, 2018|Categories: Solutions|Tags: |0 Comments

Below is a possible solution to Challenge 69 - Need Help in Not Showing Comments

These are not comments but Data Validation Messages..

Select the cell where pop up message is appearing > Data tab > Data Validation > The pop message is in the Input Message > Either you can remove messages or just say clear all.

To remove them in block -
1. Select rows 1 to 7 > Data tab > Data Validation > Clear All
2. Select rows 20 to 22 > Data tab > Data Validation > Clear All

Sat 20 Jan 2018

## Challenge 69 - Need Help in Not Showing Comments

By |Saturday, January 20th, 2018|Categories: Challenges|Tags: , |4 Comments

Download the workbook. Whenever I select a cell, the yellow box pops up. I need your help to provide me instructions to remove these.

The solution to above challenge will be published after a month i.e. on 20-Feb-2018.

Tue 16 Jan 2018

## Solution - Challenge 68 - Generate a Particular Sequence

By |Tuesday, January 16th, 2018|Categories: Solutions||0 Comments

Below is a possible solution to the problem Challenge 67 - Generate a Particular Sequence -

This consists of 2 logic.

1. Generate Triangular number. The formula for which is n*(n+1)/2
2. Generate Column Labels (A, B, AA, ZZ....) from numbers, the formula for which is

=SUBSTITUTE(ADDRESS(1,3,4),1,"") where 3 is for column C. So, we need to vary this 3.

The combined formula would be -

Sat 16 Dec 2017

## Challenge 68 - Generate a Particular Sequence

By |Saturday, December 16th, 2017|Categories: Challenges||1 Comment

Study the sequence below and you need to write a formula which when dragged down should generate the below sequence.

For visual representation purpose, the sequence is in 5 columns but you need to generate the sequence in one column only.

You need to generate upto 100 entries.

The solution to this challenge will be published after a month i.e. on 16-Jan-18.

Sat 09 Dec 2017

## Solution - Challenge 67 - Find Duplicates and Show the Count

By |Saturday, December 09th, 2017|Categories: Solutions|Tags: , , |0 Comments

Below is a possible solution to the problem Challenge 67 - Find Duplicates and Show the Count

Use a helper column in which put following formula -

=LEFT(SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),SUBSTITUTE(A2&"-","-","%")),
" ","%",2),FIND("%",SUBSTITUTE(IF(ISNUMBER(FIND("(",A2)),SUBSTITUTE(A2,"("," "),
SUBSTITUTE(A2&"-","-","%"))," ","%",2))-1)

In the last column, put following formula -

=IF(COUNTIF(\$B\$1:B2,B2)=1,COUNTIF(B:B,B2),"")

The solution work can be downloaded from Solution - Find Duplicates and Show the Count

Sat 09 Dec 2017

## Tips & Tricks 167 - Shortcut for Refresh All Pivot Tables Not Working

By |Saturday, December 09th, 2017|Categories: Tips and Tricks||0 Comments

To refresh a Pivot table, the shortcut is ALT+F5. If you want to refresh all Pivots in the workbook, the shortcut is CTRL+ALT+F5. This magical shortcut would do the trick and you don't need any macro to perform this task.

But on most of the computer this shortcut may not work. The reason is most of the CTRL+ALT+Fn shorcut keys are reserved by Intel HD Graphics card. You will need to de-reserve this key combination.

Control Panel > Intel HD Graphics (or anything related to Graphic Card, you can put Graphics in Search of Control Panel) > Options and Support > Disable the hot keys even though CTRL+ALT+F5 is not listed there.

Sat 25 Nov 2017

By |Saturday, November 25th, 2017|Categories: VBA||1 Comment

Below is a Macro which inserts a sheet named "Index" as first sheet and puts down all the sheets names which are clickable.

Sat 18 Nov 2017

## Tips & Tricks 166 - Convert a Number to Weekday Name

By |Saturday, November 18th, 2017|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you want to return 1 = Sunday, 2 = Monday…..7 = Saturday

=TEXT(A1&"Jan2017","dddd")

To show only 3 characters of the Weekday Name

=TEXT(A1&"Jan2017","ddd")

You can add a number to A1 if you want to show some other Weekday Name

Say, if you want to show 1 = Monday, 2 = Tuesday…….7 = Sunday, just add 1 to A1

=TEXT(A1+1&"Jan2017","dddd")

Say, if you want to show 1 = Friday, 2 = Saturday…….7 = Thursday, just add 5 to A1

=TEXT(A1+5&"Jan2017","dddd")

Tue 14 Nov 2017

## Solution - Challenge 66 - Find the Position of Word

By |Tuesday, November 14th, 2017|Categories: Solutions|Tags: , |0 Comments

Below is a proposed solution for the Challenge 66 - Find the Position of Word

Put the following formula and drag down. Let's assume the word "and" in cell D2 -

=LEN(REPLACE(A2,SEARCH(" "&\$D\$2," "&A2),LEN(A2),""))-LEN(SUBSTITUTE(
REPLACE(A2,SEARCH(" "&\$D\$2," "&A2),LEN(A2),"")," ",""))+1

You may download the solution workbook - Solution - Challenge 66 - Find the Position of Word