Posts Tagged ‘Office’

Excel Tip: Compare two lists (Method 1) July 17th, 2013

Vinod Kumar

One of my friend recently had this requirement where he wanted to compare two lists and find out which were the values from second list that are not in first list. This was an interesting requirement and we kept playing with Excel to come up with close to 3 different methods. After that task was complete, I thought it is worth every bit to bring all these methods over my blog for future reference. So have fun !!!

Method 1: Using MATCH() function.

Method 2: Using VLOOKUP() function

Method 3: Using PIVOTTABLE.

Let us look at the dataset in hand. It is a list of companies with some random revenue numbers listed for two years. Interestingly there are differences in both the dataset. Now the task is simple. We want to check companies in “List B” which are not in “List A”. So let us get started.

As mentioned earlier, the Method 1 is to use MATCH() function. Here is a neat trick, when you type “=MATCH(“ –> Press “CTRL+A”.

Voila, this is the magic dialog we get. This will guide us for various parameters for that given function. This trick can be done with any function which has parameters values.

Lookup Value => in our data set it is the first cell D3 from List B.

Lookup array => in our data set it will be A3:A10. Please note that we will use the same range and as we move through the list of companies. Hence press F4 to get $A$3:$A$10. This is an very important step.

Match_Type => In our example we will have 0 for exact match.

Press “OK” now.

Now we will use the cross arrow trick we used before. Move to right corner of this cell till you get the cross arrow as shown below. Now double click. This will fill the rest of the rows till the end is reached.

I didn’t mention the significance of the numbers earlier. Match() function is returning the relative position of the values based on comparision to first list. So here you can see “Johnson n Johnson” is in the 1st ordinal in our array. So is the case for others too.

The irritating factor here is the ERROR “#N/A”. The simple method is to prefix our function with the IFERROR() function. In our case, if we get n error we are going to print the text “Missing”.

Now use the cross arrow trick again. And now the final output looks like below.

So I plan to write the next two methods tomorrow. Want to guess them? If they are correct, I will approve the comments after making the posts :) … Do let me know if you want me to write on anything.

Continue reading...


Excel Tip: Remove All blanks from a Excel table July 16th, 2013

Vinod Kumar

This is one of the simplest tips among those I have shared over this blog till date. Let me explain you the scenario. We have the following table / range / data set inside Excel and the problem with these dataset is that, we have spaces in between and this is causing us problems for filter, moving between cells and many more. So our objective is to remove the rows having space. I have seen people use “CTRL+Down Arrow” to goto a row with space, delete that row – press CRTL+Down Arrow and repeat this process till you reach the last row.

This for sure is manual and intensive repetitive activity. In this blog post we will do the same operation efficiently in few clicks. Quite an handy option to know.

When you are at in this screen, Select the range on “B column” just like shown below. And now hit the “CTRL+G”. This bring the dialog as below. Select “Special…

In the following dialog select “Blanks” and press “OK”.

Voila, now you can see that from our dataset the Blank rows are highlighted as shown below.

Just right Click and select “Delete” –> “Table Rows”. Now that is as simple as it gets.

We have successfully deleted all the rows with blanks and our final dataset looks like:

Excel is an awesome tool and there are many such functions that make it interesting. Hope you will use this shortcut in your day-today work and hope you found this trick useful. Do let me know if you want me to write on anything specific and I would love to explore and show you few tricks.

Continue reading...


Excel Tip: Largest values from Dataset (using Functions) July 8th, 2013

Vinod Kumar

If you haven’t read my post on “Excel Tip: SUM Top 5 values”, then this blog is an extension to that blog post. Do make sure to read it there because we will be using the same concept here in this tip.

Our dataset is simple as shown in figure below. We want to get the Max times for each city in a second list. How can we do this. The most easiest method I have seen is people resorting to PIVOT. If you want that implementation, do let me know and I will post that separately.

Just like in our previous post (Excel Tip: SUM Top 5 values), we used LARGE function there. Here we will use the MAX function with a conditional operator of IF. So in our example, we getting the MAX of values from the range B3:B8 where the value of D3 needs to be in the range A3:A8. Simple concept.

If you press a simple Enter you will get a value of 0. Remember the magic key (Ctrl+Shift+Enter) after you finish the formula and voila. You get exactly what you wanted. A typical output is below.

Now to copy this formula across E4 and E5. Find the cross arrow in E3 right hand- corner location. Now “Double Click” once you find the cross arrow.

Now that task automatically fills the range and you can see we have our desired output in less than a minute.

Hope you are enjoying the series of Excel Tips and tricks I am showing over the blogs. Do let me know of topics that will interest you and I will try to cover them here. Have a great day and learn something new every single day.

Continue reading...


Excel Tip: SUM Top 5 values July 2nd, 2013

Vinod Kumar

Since Excel Tips have been most sought after posts from a productivity part, I thought let me introduce an interesting tip in this blog post. Have you ever had a requirement to get the “Average of Top 5” or “Sum of Top 5” values from a list? What is the most common method that you used? I have seen most people use the sort function to order the values in descending order and just select the range of values to find SUM, Average and so on. So this post is a twist to that requirement. You can also apply the tip to other functions like Average which makes this post even more interesting.

Our  Dataset 

The dataset is simple. Seen below is a list of some random Sales figures and our requirement is to get the SUM of Top 5 Sales figures in D3 cell.

To start with, let us first find our top Sales figure first. This can be done using the LARGE function. The second argument “k” is to give the offset. Value of 1 means we need the top sales number. This is shown below.

The output can be seen as below:

Duplicate LARGE Function

Now that we figured out how to get the 1st, 2nd, 3rd and so on. We can just duplicate the function on the same range and make our SUM. It is as simple as it gets.

As you can see this for some reasons doesn’t look right. We seem to be duplicating and if we again look at the LARGE function. It takes just a value and doesn’t take a range.


Since SUM is taking in only values and not arrays. We need to find a way to pass some sort of array. Here we have SUMPRODCT function that takes in a set of array to work with. And to take a range of values top 5 (1+2+3+4+5), I have created a temp range with these values.

Super cool – Isn’t it?

What will happen if we do the same with SUM function?

You can see that for some strange reason the top 1 value was returned and it didn’t do a SUM for the array.


Here is the magic shortcut (CTRL+SHIFT+ENTER) – Edit our cell from above “=SUM(LARGE(B3:B10, C2:C7))” and press our magic shortcut.

Now there is something interesting that happens. Now the SUM of values is done for an array of values. The CTRL+SHIFT+Enter has many interesting use. Here I have shown one of them.

Since the array is shown using the {} – this doesn’t work if add the parenthesis manually. A small extension to this formulae is to add the array of {1,2,3,4,5} manually and then hitting our magic key CTRL+SHIFT+Enter also can do the trick.

Hope you enjoyed this tip and do let me know if you have used this in the past anytime. I will try to bring some more use of arrays in future posts.

Continue reading...


DAX: Date and Time functions June 26th, 2013

Vinod Kumar

We have been looking at various DAX related content in this blog in the past few weeks, I thought let me start off by bringing out some of the commonly used DAX functions to light. Many are most useful and seem to work just like normal Excel functions. In this blog we will look at Date and time functions available with DAX.

The Date and Time functions convert string or number to a datetime value or convert a datetime value to a number representing the different part of the date time, such as year, month, day of a week, hour, minute.

The datetime argument can be an explicit datetime value, a column of datetime data type or an expression that evaluates to a datetime value. The result depends on the locale and date time setting which affects whether the datetime value is interpreted as mm-dd-yyyy, or as dd-mm-yyyy.

The value of the datetime argument can be in any of the proper datetime formats. Here are some example datetime formats:

  • "August 1, 2012"
  • "Sep 12, 2012"
  • "3-4-2012"
  • "2012-03-04"
  • "03/04/2012"

DATE Function

Syntax: DATE (yyyy, mm, dd)

Return type: DateTime

DATE function generates a date value from the separately provided value of year, month, and date. The year, month, and date arguments are rounded to integer if non- integer values are presented. The arguments can have explicit values, or expression evaluates to the value. The resulting date is generated using the following logic:

If yyyy is less than 1900,
     Resulting year = 1900 + yyyy
     Resulting year = yyyy
If mm is between 1 to 12,
     Resulting month = mm
     Resulting month = beginning of the resulting year + mm
If dd is between 1 and end of the resulting month
     Resulting date = dd
     Resulting date = beginning of the date of the resulting year + dd



The above example returns February 1, 1909.


The above example returns December 24, 2009


Syntax: DATEVALUE (date_text)

Return type: DateTime

DATEVALUE function converts date value represented in a text string to date value in DateTime type. The resulting date depending on the locale setting which controls how to interpret certain date format


=DATEVALUE ("8/1/2012")

The result in US will be August 1, 2012. The result in India will be January 8, 2012.

TIME Function


Similar to DATE and DATEVALUE functions, TIME function converts the provided numbers for hour, minute, and second to a time value, TIMEVALUE converts a string time value to a time value of datetime type.

Syntax: TIME (hour, minute, second) and TIMEVALUE (time_text)

Return data type: datetime

DAY Function

MONTH Function

YEAR Function

WEEKDAY Function

WEEKNUM Function

Syntax: DAY(datetime)

MONTH (datetime)

YEAR (datetime)

WEEKDAY (datetime, <return_type>)

WEEKNUM (datetime, <return_type>)

Return data type: integer

These functions return the day number of the month (1-31), the month number of the year (1-12), the year, the day number within the week (0-6 or 1-7), or the week number within the year (1-53) from the datetime value passed to the argument. The return_type argument for the WEEKDAY function specifies when a week starts and whether the first day needs to return 0 or 1. If not specified, 1 is the default value.

  • 1 Week begins on Sunday (1) and ends on Saturday (7)
  • 2 Week begins on Monday (1) and ends on Sunday (7)
  • 3 Week begins on Monday (0) and ends on Sunday (6)

The return type for WEEKNUM function specifies whether a week starts on Sunday or Monday.

  • 1 Week begins on Sunday. Weekdays are numbered 1 through 7
  • 2 Week begins on Monday. Weekdays are numbered 1 through 7

EDATE Function

Syntax: EDATE (start_date, numberofMonths)

Return data type: datetime

EDATE returns the datetime before or after the number of months from the start_date. Useful for calculating the maturity date or due date that needs to be on the same day as the start_date. When the resulting day goes beyond the end of the month, the end of the month is returned.


=EDATE("3-4-2013", 3)

It returns June 4, 2013.

=EDATE("March 30, 2013", -1)

It returns February 28, 2013.

EOMONTH Function

Syntax: EOMONTH ( start_date, numberOfMonths)

Return data type: datetime

EOMONTH returns the datetime of the end of the month before or after the specified number of months from the start_date. Useful for calculating due date that needs to be the end of the month.


=EOMONTH("3/4/2013", 1)

It returns April 30, 2013.

HOUR Function

MINUTE Function

SECOND Function

Syntax: HOUR (datetime)

MINUTE (datetime)

SECOND (datetime)

Return data type: Integer

These functions return the number of hours (0-23), minute (0-59), and second (0-59) from the input datetime value.

NOW Function

Syntax: NOW()

Return data type: datetime

NOW returns the current date and time. Only updated when we refresh PowerPivot data. This is similar to Excel function.

TODAY Function

Syntax: TODAY ()

Return data type: datetime

TODAY returns the current date. Similar to NOW function, once the value is populated, it will not change until you refresh the PowerPivot workbook. This functions similar to Excel function.


Syntax: YEARFRAC(start_date, end_date, <basis>)

Return data type: decimal

YEARFRAC returns a fraction of the days between the start_date and the end_date to the total number of days in a year. <Basis> arguments defines the type of day count

Basis Description

  • 0 US (NASD) 30/360
  • 1 Actual/actual
  • 2 Actual/360
  • 3 Actual/365
  • 4 European 30/360

Hope you learnt the typical use of various functions with DAX. In future posts, we will take a look at other functions and how they work with DAX. Do let me know if you want me to take any other functions next.

Continue reading...