Microsoft Excel has provided a wide range of solutions to calculate YTD(Year-to-Date) under different criteria. In this article, you’ll be able to know all possible & fruitful methods to determine YTD with ease.
Download Excel Workbook
You can download the Practice Workbook that we’ve used to prepare this article.
8 Suitable Approaches to Calculate YTD in Excel
1. Calculating YTD with SUM Functions
Let’s get introduced to our dataset first. Assuming, a business company has sold numbers of products in 12 months of a year & the total selling prices for all months have been recorded. Now we want to know the Year-to-Date Selling Price per unit quality of the products for each month.
➤ In Cell E5, type:
➤ Press Enter & we’ll find the YTD for the first month.
➤ Now use the Fill Handle to fill down the whole column to know other YTD for all months.
The formula here is working through the division between two cumulative values for price & quantity. Here the divided part is cumulative prices for the products & the divisor part is cumulative sums of the quantity of the products.
2. Calculating YTD with the Help of SUMPRODUCT Functions
We can find the similar output by using SUMPRODUCT functions too.
➤ The SUMPRODUCT formula for our output Cell E5 will be:
➤ Press Enter & you’ll get the result for the first month.
➤ Now use Fill Handle again to autofill the whole column.
3. Determining YTD Growth Rate by Using Dynamic Formula- Combination of SUM, OFFSET & MATCH Functions
Now we’re going to compare sales data between two years for fixed successive months with YTD. We’ll find out the YTD for the months January & February to compare sales data between two years- 2020 & 2021.
➤ In Cell I6, type:
➤ Press Enter.
➤ Under the Home ribbon, select Percentage from the drop-down in the Number group of commands. The decimal value you’ve got will be turned into percentage at once.
➤ Now use Fill Handle to autofill other YTD growth rates for all products.
4. Finding out YTD Profits by Incorporating SUM, OFFSET, ROWS & MONTH Functions
We can find YTD values upto a particular month by inputting a specific date too.
➤ In Cell I7, our formula for this criterion will be:
➤ Press Enter.
➤ Autofill other cells in Column I for all other products by using Fill Handle.
Here, our input date is 12 May, 2021. The MONTH function extracts the month from this date & our formula then uses this month number or serial of the month as column number upto which the calculation will be executed finally through OFFSET, SUM & ROWS functions together.
5. Calculating YTD by Using SUMIFS Functions
In this section, we’ll extract year, month & date with the help of YEAR & DATE functions and then apply them to find the YTD rate. In our dataset, selling prices of the products for 10 days have been recorded along with their cost prices. We’ll determine the YTD profit rates for successive 10 days.
📌 Step 1:
➤ In Cell F5, type:
➤ Press Enter & with this function, we’ll get the fraction or percentage of the days up to the particular date in Column B based on 365 days in a year.
➤ Use Fill Handle to fill down the whole Column F.
Here YEARFRAC function returns the year fraction representing the number of whole days between start_date & end_date. We’re using the DATE function inside to input the 1st date(1/1/2021) of the year 2021.
📌 Step 2:
➤ Let’s move onto Cell G5 now & type:
➤ Press Enter & convert it into percentage by selecting Percentage from the drop-down in the Number group of commands under the Home Tab.
➤ Fill down the whole Column G & you’ll get the YTD profit rates for all successive 10 days at once.
6. Determining YTD Portfolio Returns for Stocks & Bonds
This is the easiest part now to calculate YTD portfolio returns as it requires very simple formula.
➤ In Cell F5, we have to type:
➤ Press Enter & you’ll get the first value as 0.
➤ Now convert the whole column into percentage by selecting Percentage format from the drop-down in the Number group of commands under the Home tab.
➤ Use Fill Handle to autofill the other YTD returns for all months.
7. Merging SUM, OFFSET & COUNTA Functions Together to Compare YTD
To compare the cumulative values between two specific & successive spans of time from two different years, this method is suitable enough. Here, the sales values for all months in 2020 are present in Column C & when you’ll input a value in Column D from the beginning you’ll get the comparative results up to the specific months between two years.
📌 Step 1:
➤ In Cell F11, type:
➤ Press Enter & you’re done with the YTD for the year2020.
📌 Step 2:
➤ Assign the formula in Cell G11:
➤ Press Enter & now you’re done to & prepared to input data in Column D.
With these steps, we’re setting the function to display the outputs in both of the cells F11 & G11 so that when we’ll input data in Column D, then Cell F11 as well as G11 will simultaneously show YTD values up to the specific months too for the years of 2020 & 2021 respectively. Thus we’ll be able to compare cumulative sales values between those two data upto a particular time in both years.
8. Creating Pivot Table to Calculate YTD
In our final method, we’ll apply Pivot Table to calculate YTD. We have a table for the sales values of 3 successive years.
➤ Select the whole table & choose Pivot Table option from the Insert ribbon.
➤ Put Months in the Rows Field and the Year headers in Values Field.
➤ Put your mouse cursor on any of the sales value in year 2018 & open Value Field Settings from the options by right-clicking the mouse.
➤ From the Show Values As tab, select Running Total In.
➤ Press OK & you’ll see the cumulative sales value or running total for the year of 2018.
➤ Similarly, do this process for the years of 2019 & 2020.
Finally the output the data will be as seen in the picture below through which you can easily compare YTD upto a particular month for different 3 years.
I hope all these methods mentioned above to calculate Year-to-Date(YTD) will prompt you to apply in your regular Excel works. If you got any questions or feedback please let us through comments. You can check out our other informative & useful articles related to Excel functions on this website.