Sat 31 Oct 2015

Tips & Tricks 116 - Count the Number of Words in a Cell / Range

By |Saturday, October 31st, 2015|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

Suppose you have been given the following and you need to count the number of words in a cell or in a range.

1

Formula for calculating number of words in a cell -

=LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+(TRIM(A1)<>"")

Formula for calculating number of words in a range -

=SUMPRODUCT(LEN(TRIM(A1:A100))-LEN(SUBSTITUTE(TRIM(A1:A100)," ",""))+(TRIM(A1:A100)<>""))

1

Sat 31 Oct 2015

Excel Quiz 24 - Financial Functions

By |Saturday, October 31st, 2015|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 24 - Financial Functions

A quiz about financial functions in Excel

Sat 31 Oct 2015

Tips & Tricks 115 - Insert Fixed Current Date and Current Time

By |Saturday, October 31st, 2015|Categories: Tips and Tricks|Tags: , , , , , |0 Comments

We all are aware about today() and now() formulas which insert current date and current date/timestamp. But these change with every recalculation of your worksheet.

But if you want to enter the current date and time which doesn't change with recalculation i.e. it gets fixed, then following Excel shortcuts can be used.

Current Date - CTRL+:

Current Time - CTRL+SHIFT+:

Current Date & Time - To insert the current date and time, press CTRL+; (semi-colon), then press SPACE, and then press CTRL+SHIFT+; (semi-colon).

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

Sun 25 Oct 2015

Solution - Challenge 26 – Find Sum given multiple OR conditions across columns

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

Below is a possible solution to the challenge Challenge 26 – Find Sum given multiple OR conditions across columns.

Put following formula for the result -

=SUMPRODUCT((COUNTIF($F$1:$I$1,$A$2:$A$20)+
COUNTIF($F$2:$I$2,$B$2:$B$20)>0)*($C$2:$C$20))

The workbook illustrating the solution can be downloaded from Solution - Challenge 26 – Find Sum given multiple OR conditions across columns.

Sat 24 Oct 2015

Challenge 28 - Create Pascal's Triangle

By |Saturday, October 24th, 2015|Categories: Challenges|Tags: , , , , , , , |1 Comment

Pascal Triangle is very famous in number theory. Interested persons can read about Pascal's triangle at following Wikipedia article - https://en.wikipedia.org/wiki/Pascal%27s_triangle

Anyway, whether read it at Wikipedia or not, below is Pascal's triangle. One look at it and you will understand the pattern.

1

Now, the challenge before you is following -

1. Put 1 in L2.

2. You need to write a formula which can be put in the row below and dragged left/right and down to create Pascal's Triangle.

1

You may post your answer in comments section.

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

Sun 18 Oct 2015

Excel Quiz 23

By |Sunday, October 18th, 2015|Categories: Quizzes|Tags: , , , |0 Comments

Excel Quiz 23

A general quiz on Excel

Sat 17 Oct 2015

Tips & Tricks 114 - COUNTIF for non-contiguous range

By |Saturday, October 17th, 2015|Categories: Tips and Tricks|Tags: |0 Comments

All of us love COUNTIF. And it is very easy to do - just say =COUNTIF("A1:A100",">5") and it finds all the values within the range A1 to A100 which are greater than 5. But what if I wanted the result for only A3, A8 and it should omit other cells. Try putting in following formula -

=COUNTIF((A3, A8),">5") and it will give you #VALUE error.

A possible solution is

=(A3>5)+(A8>5)

What happens if you need to do for A3, A4, A5, A8, A24, A40, A45, A89. Now, you will have to use a formula like -

=(A3>5)+(A4>5)+(A5>5)+(A8>5)+(A24>5)+(A40>5)+(A45>5)+(A89>5)

The formula becomes cumbersome as the number of cells increase. In this case, you can use below formula. This single formula can take care of contiguous (like A3:A5) and non-contiguous ranges both -

=SUM(COUNTIF(INDIRECT({"A3:A5","A8","A24","A40","A45","A89"}),">5"))

Sat 17 Oct 2015

Tips & Tricks 113 - Drag and Drop a Picture in Excel Sheet

By |Saturday, October 17th, 2015|Categories: Tips and Tricks|Tags: , , , , , , , , |0 Comments

Try dragging and dropping a picture in a worksheet - What happens. You will notice a plug sign which means that a picture will be added. But when you release the cursor to drop the picture in Excel sheet nothing happens.

Reason - Excel doesn't support dragging and dropping the picture in a worksheet.

Now, what to do. If you are dealing with too many pictures, it is cumbersome to use Insert > Picture command.

Fortunately, there is a work around in place. Microsoft Word supports dragging and dropping of pictures. Also good thing is that, you can select all your pictures in one go and drop in Word document.

Now, you can either drag and drop the pictures from the Word document in Excel sheet or you can cut/copy and paste from Word to Excel sheet.

Side Note - OpenOffice / Libre Office supports drag and drop to their respective spreadsheet programs. (Note, but you can not drag or copy and paste pictures into Excel from Open Office / Libre Office spreadhseets like you can drag or copy / paste from Word)

Sun 11 Oct 2015

Solution - Challenge 25 – Prepare a Project Status Chart with Sliders

By |Sunday, October 11th, 2015|Categories: Solutions|Tags: , , , , |0 Comments

At the outset, let me thank How-to Make an Excel Project Status Spectrum Chart from where I learnt this kind of chart and inspired to set this challenge. My challenge is almost the same except that this is not spectrum but three distinct range of RAG are there.

Below is a possible solution to Challenge 25 – Prepare a Project Status Chart with Sliders.

(more…)