Grouping Items By Date in A Pivot Table Excel

Excel Pivot table offers many useful functions. One of them is to combine items into groups.  Grouping items in Pivot table is very simple. Just select the range where you need to group and choose Pivot Table Tools-> options-> Group-> Group Selection. If the field contains dates, then Excel can automatically group them into months, Year, Quarter or the three.  Below is the procedure to group different dates into Monthwise summary or year wise summary.
Steps to Group Dates into Months, Year and Quarter in Excel Pivot Table:
Let us start with using a example on the same. Below is the sample data which has two columns of data. Date and Total Sale Value. The table has 20 rows and cover dates between January 2011 to May 2012.
The desired final output is to summarize the sales information by months.
Step 1: Create the pivot table using the table.  The initial pivot table looks almost similar to the raw data.

Step2: To group item by month, right click any cell in date column of the pivot table and select Group from the shortcut menu.  A Grouping dialog menu appears.
Step3: In the list box Select Months and Years and verify that the starting and ending dates are correct and Click OK.
The date Items in the pivot table are grouped by years and by months. Even you can group the dates by quarter and years too. Below the final output of Grouping by month and year, quarter and Year.

Returning Last Row value in Excel

There are situations we update the worksheet frequently by adding new data to its Columns. We need to reference the last row of the columns  which is the value most recently added. Finding the last row or column can be achieved through Excel Formula Index or Using the VBA code.  Below is the Example for finding the last row reference using Excel formulas.

Sample data:

For finding the last column reference in G2 we need to write the formula"=INDEX(B:B,COUNTA(B:B))" similarly finding the last row for the column C we need to use the formula "=INDEX(C:C,COUNTA(C:C))" and For D column the formula is =INDEX(D:D,COUNTA(D:D)).

Explanation on the above example to find the last row reference:

The above Excel Formulas use COUNTA function to count the number of non empty cells in column C. This value is used as the second argument for the INDEX function. In the above example, in column B the last value is in row 5 and COUNTA function returns 6 and INDEX function returns the 6th value in the column.

This works good for most of the situation. But not for all conditions. If the column has one or more empty cells interspersed, determining rge last non blank cell is a bit more challenging because COUNTA function do not count the empty cells.  In this situation we need to use array formula to return the content in the last non empty cell.

Below is the sample formula considering above sample data.

=index(C1:C600,max(ROW(C1:C600)*(C1:C600<>""))) 

The above return the last non empty in the first 600 rows of Column C even if column C contains blank cells.  Please note that it is a array formula and hence you need to press Ctrl+Shift+Enter to enter the formula. You can change the row range or column other C. If the last non empty cell occurs in a row beyond row 600, you need to change the two instances of 600 to a larger number. The fewer the rows referenced in the formula, faster the calculation speed.

Below is the another formula to return last non empty cell in a row(in this case row 1):

=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))

To use this array formula for a different row, change the three 1:1 row references to correspond to the correct row number.

Creating Drop Down list in Excel

Excel provides a function create a drop down list in any of the cells in Excel work sheet without the need of any  EXCEL VBA codes.  The Drop down list can be created using data validation features. The drop down content might be a text,numeric values or dates.

Step to create drop down list in Excel using Data validation feature:

• Enter the list of items in a range of excel sheet. 
• Select the cell where you want to create drop down list
• Choose Data->Tools->Data Validation
• In the data validation dialog box, click Settings tab
•  In the Allow drop down list, select list
• In the source box, specify the range that contains the list
• Make sure "In-Cell Dropdown" option is checked and click OK

Note: 

1.If your list is short, then you can type the list items separated by commas in the Source box in the Data Validation dialog box instead of selecting range

2. In the earlier versions of Excel, list of items required to be in the same sheet as the cell that contains drop down list. In Excel 2010, the list can be in the any sheet. So if you are planning to share the date with others who use older version of Excel, then ensure the list of items range is in the same sheet.

3. Alternatively, you can put the list on Any sheet as long as it is a named range.  You can Define a range by choosing Formula->Defined Names->Define Name to define the name of the range.

DATEDIF Function in Excel

DATEDIF  function calculates the difference between two dates and expresses the result in terms of months, days or years. Even though it is very useful function Excel, there is no documentation in excel help documentation.  This tip will show you how to use DATEDIF function.
DATEDIF function syntax:
=DATEDIF(date1,date2,interval)
Date 1 and Date 2 are standard dates or a reference to a cell that contains a date. Date 1 should be earlier than  or equal to Date 2. Third argument Interval is a text string that specifies the unit of time that will be returned.
Various valid interval codes are as follows:
m: The number of complete months between two dates Date1 and Date2
d: The number of days between two dates Date 1 and Date 2
y: The number of complete years between Date 1 and Date 2
ym: The number of months between Date 1 and Date 2. This interval excludes years. So it works as though the two dates are in the same year.
yd: The number of days between Date 1 and Date 2. This interval excludes years. So it works as though the two dates are in the same year.
md: The number of days between Date 1 and Date 2. This interval excludes both year and month.  So it works as though Date 1 and Date 2 are in same month and the same year.

Example for DATEDIF functions: Below is the sample excel sheet with two dates

Formula used here are:

=DATEDIF(A2,B2,"m") which return 73
=DATEDIF(A3,B3,"d") which return 2226
=DATEDIF(A4,B4,"y") which returns 6
=DATEDIF(A5,B5,"ym") which returns 1
=DATEDIF(A6,B6,"yd") which returns 34
=DATEDIF(A7,B7,"md") which returns 4.
This formula works on Excel 2000, Excel XP, Excel 2003, Excel 2007 and Excel 2010 without any extra addons.