If your data has a column of dates, you may want to subtotal dates by month and year. However, when using the Subtotal command, the desired result is not being achieved as it creates a subtotal for each day.
You are welcome to download the workbook to practice.
Applies To: Microsoft® Excel® for Windows 2010, 2013, and 2016.
If your data is in a table format, you will first need to convert the table to a normal range of data before following the below steps. To convert your data, from the Table Tools > Design menu, select Tools > Convert to Range. This will remove all table functionality from the data except the table formatting.
To subtotal by month and year following these simple steps:
Select a cell within your date range and from the Data tab select the sort A>Z button to sort your dates in the correct order.
Apply a date format to display your date range as Month and Year. Select your date range and on the Home Tab, select More Number Formats from the General drop down list in the Number section. Once the Format Cells dialogue box is open, select Custom and either enter mmm-yy in the Type box or scroll down to select the right format.
The dates will now be converted and displayed as the month and year only.
Select a cell in the date section, then select the Data tab and click.
Once the Subtotal Dialogue Box is open, select Date from the drop-down list under the At each change in:, select Sum from the drop-down list under Use function: and select the relevant fields under the Add subtotal to: section.
Subtotals are now inserted for each month instead of each day.
Finally, format your dates back to their original format to display the full date again. The subtotals are still only displayed for each month.
As you can see, it is easy to analyze the data when the subtotals are arranged by months and not dates. In that way, you can quickly see the total for each month leading to effective decision making.
The post How to Subtotal Dates by Month and Year in Excel appeared first on Sage Intelligence.
Powered by WPeMatico
