Adding Subtotals to a Date Field in Microsoft Excel 2010

 

In this article, we will learn how we can add the subtotals to a date field in Microsoft Excel 2010.

To add subtotals to a date field, we will use format cells option to format the date in Month or Year as per the filtration requirement.

Let’s take an example to understand how we can filter the data by the date.

We have data in range A1:C18 in which Column A contains date, column B contains customers’ names, and column C contains Revenue amount.
 
img1
 
We have to put filters as per the Month and year.

Follow below given steps:-

  • Copy the range A1:A18 and paste it right side to Column A
  • Select the date range A1:A18.
  • Copy the date column by pressing the key Ctrl+C on your keyboard.

 
img2
 

  • Select the cell A1 and press the key Ctrl++.

 
img3
 

  • Insert Paste dialog box will appear.
  • Select Shift cells right and click on OK.

 
img4
 

  • Again copy the range and press the Ctrl++, and select Shift Cells Right in the Insert Paste dialog box, and then click on OK.

 
img5
 

  • Change the title of 2 columns to Month and year.

 
img6
 

  • Select the Month Column and press the Ctrl+1.
  • Format Cells dialog box will appear.
  • Go to number tab and select custom.
  • In the type box, enter the format mmmm (to show the full name of month) and click on OK.

 
img7
 
img8
 

  • Select the Year Column and press the Ctrl+1.
  • Format Cells dialog box will appear.
  • Go to number tab and select custom.
  • In the type box, enter the format YYYY (to show the full year) and click on OK.

 
img9
 
img10
 
Now the list is ready to filter the data according Month and year criteria.

  • To insert the Auto Filter, select the cell A1 and press the key Ctrl+Shift+L.

 
img11
 

  • Sort the data as per the year, newest to oldest.

 
img12
 
img13
 
To add the subtotal to a Date field, follow below given steps:-

  • Go to data Tab, and Select Subtotal from the Outline group.

 
img14
 

  • Subtotal dialog box will appear.
  • Select year from the drop down list at each change in.
  • To add the fields, select Sum from the function drop down list.
  • Check to Revenue, and click on OK.

 
img15
 

  • The data will get add as per the date field in Microsoft Excel.

 
img16
 
 



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>