Sun 30 Jul 2017

Article 47 - New Functions in Excel 2017

By |Sunday, July 30th, 2017|Categories: Articles|Tags: , , , , , , , , |0 Comments

This is a guest post contributed by Hannah Sharron of  http://spreadsheeto.com/

Microsoft has added six new built-in functions with the release of Excel 2016. These functions are also available in Office 365. In this article, we will take a quick look at three of those new functions.

TEXTJOIN

For a long time, the CONCATENATE has been a standard for many users as a method for joining data strings together. However, with the introduction of TEXTJOIN, Microsoft had refined this process even further.

(more…)

Sat 22 Jul 2017

Excel Quiz 54

By |Saturday, July 22nd, 2017|Categories: Quizzes|Tags: , , , , |0 Comments

Excel Quiz 54 - A Quiz on Review Tab in Excel

This quiz checks your knowledge on Review tab in Excel.

Sat 15 Jul 2017

Challenge 64 - Sum up the Range where a particular alphabet appears

By |Saturday, July 15th, 2017|Categories: Challenges|Tags: , , , |1 Comment

Suppose, you have been given a range like this and you need to find the sum of column B where the alphabet "c" appears alone.

To check your answer, the sum will be 27 for above.

The file related to this challenge can be downloaded from Challenge 64 - Sum up the Range where a particular alphabet appears

The answer to the above solution will be presented after a month i.e. on 15-Aug-17.

Mon 10 Jul 2017

Solution - Challenge 63 - Convert to Date Format

By |Monday, July 10th, 2017|Categories: Solutions|Tags: , , , , , , , , , , , |0 Comments

Below is a possible solution to the Challenge 63 - Convert to Date Format

Put following formula and drag down

=IFERROR(--SUBSTITUTE(A1,",",""),--SUBSTITUTE(SUBSTITUTE(
SUBSTITUTE(A1,",","")," ","*",2),"*",", "))

 

Sun 09 Jul 2017

Article 46 - Creating Pivot Table with Dynamic Range

By |Sunday, July 09th, 2017|Categories: Articles|Tags: , , , , , , , , |0 Comments

The file related to this article can be downloaded from Dynamic Pivot Tables

We all make pivot tables and we also know that every time, the range of data which pivot uses goes beyond the current range, we need to change the data range. It becomes painful and also if you are creating dashboards, it is a poor design. Once you create a dashboard, anybody should be able to refresh the pivot and not worry about changing ranges.

(more…)

Sat 01 Jul 2017

Tips & Tricks 161 - When is Thanksgiving Day in a Year

By |Saturday, July 01st, 2017|Categories: Tips and Tricks|Tags: , , , , , , , , , |0 Comments

Last time, we discussed about finding Labor Day in a given year. This time, it is is about Thanksgiving Day in a year. Thanksgiving day is 4th Thursday in a November.

Hence, earliest possible day when 4th Thursday can happen is on 22-Nov.

(more…)