How to Calculate YTD (Year to Date) in Excel [9 Simple Ways]

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel has provided a wide range of solutions to calculate YTD (Year-to-Date) in Excel under different criteria. In this article, youâ€™ll be able to know all possible & fruitful methods to determine YTD with ease.

How to Calculate YTD (Year to Date) in Excel: 9 Simple Ways

In the workbook, weâ€™ll try to show you some different applications ofÂ YTDÂ using some Excel functions. YTD price, profit, growth rate, etc. are discussed in this article. Please follow the next sections to get the best idea of how to calculate YTDÂ in Excel.

1. Calculating YTD with SUM Functions

First, letâ€™s get introduced to our dataset. Assuming, a business company has sold a number 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. We can do this simply by using the SUM function.

ðŸ“Œ Steps:

âž¤ First, type the formula in cell E5.

`=SUM(\$D\$5:D5)/SUM(\$C\$5:C5)`

âž¤ After that, press Enter & weâ€™ll find the YTD for the first month.

âž¤ Next, use the Fill Handle to AutoFill the formula to the whole column.

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. Applying Excel Combined Functions to Calculate YTD

We can also calculate the YTD Price Per Unit using the combination ofÂ SUM,Â IF,Â MONTH, and OFFSETÂ functions.

ðŸ“Œ Steps:

âž¤ First, make some modifications in the Month column. Say we are taking accounts on the 28th day of each month. So the first date will beÂ 1/28/2021.

âž¤ After that, type the following formula in cell E5 and press theÂ ENTERÂ button. You will see the YTDÂ Price Per Unit inÂ January.

`=IF(MONTH(B5)=1,D5/C5,SUM(OFFSET(\$D\$5,0,0,1,1):D5)/SUM(OFFSET(\$C\$5,0,0,1,1):C5))`

The formula references cells D5Â andÂ C5 usingÂ the OFFSET function. It removes the Formula Omits Adjacent Cells error. TheÂ IF functionÂ returns theÂ Price per Unit ofÂ JanuaryÂ if the month inÂ B5Â is the first month of the year. Otherwise, it returns values for the later months of the year.

âž¤ Next, use the Fill Handle to AutoFill the lower cells.

3. Calculate YTD Using Excel SUMPRODUCT Functions

We can find a similar output by using the SUMPRODUCT function too.

ðŸ“Œ Steps:

âž¤ First, type the following formula in cell E5.

`=SUMPRODUCT(\$D\$5:D5)/SUMPRODUCT(\$C\$5:C5)`

âž¤ After that, press Enter & youâ€™ll get the result for the first month.

âž¤ Next, use Fill Handle again to autofill the whole column.

4. Determining YTD Growth Rate by Using Dynamic Formula

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 of January & February to compare sales data between two years- 2020 & 2021. The useful functions for this method are SUM,Â OFFSET, andÂ MATCHÂ functions.

ðŸ“Œ Steps:

âž¤ Type the following formula on cell I6.

`=SUM(OFFSET(E5,1,1,1,MATCH(\$I\$5,\$F\$5:\$H\$5,0)))/SUM(OFFSET(B5,1,1,1,MATCH(\$I\$5,\$C\$5:\$E\$5,0)))-1`

âž¤ Next, press Enter.

âž¤ After that, 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 a percentage at once.

âž¤ Thereafter, use Fill Handle to AutoFill other YTD growth rates for all products.

5. Calculating YTD Profits by Incorporating Excel Functions

We can find YTD values up to a particular month by inserting a specific date by using SUM, OFFSET, ROWS, and MONTH functions.

ðŸ“Œ Steps:

âž¤ First, in cell I7, our formula for this criterion will be:

`=SUM(OFFSET(\$C\$6,ROWS(\$B\$7:B7),0,,MONTH(\$C\$4)))`

âž¤ After that, press Enter.

âž¤ Thereafter, 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 the column number up to which the calculation will be executed finally through OFFSET, SUM & ROWS functions together.

6. Calculating YTD by Using Excel SUMIFS Functions

In this section, weâ€™ll extract year, month & date with the help of YEARFRACÂ & 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 usingÂ the SUMIFS function.

ðŸ“Œ Step 1:

âž¤ First, type the following formula in cell F5.

`=YEARFRAC(DATE(E5,1,1),B5,1)`

âž¤ After that, press Enter. 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.

âž¤ Next, 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:

âž¤ Move onto cell G5 & type the formula below.

`=SUMIFS(D:D,E:E,YEAR(B5),F:F,"<="&F5)/SUMIFS(C:C,E:E,YEAR(B5),F:F,"<="&F5)-1`

âž¤ Thereafter, press Enter & convert it into a percentage by selecting Percentage from the drop-down in the Number group of commands under the Home tab.

âž¤ Finally, fill down the whole column G & youâ€™ll get the YTD profit rates for all successive 10 days at once.

7. Determining YTD Portfolio Returns for Stocks & Bonds

This is the easiest part now to calculate YTD portfolio returns as it requires a very simple formula.

ðŸ“Œ Steps:

âž¤ First, in cell F5, write down the formula below.

`=\$E5/\$E\$5-1`

âž¤ Next, press Enter & youâ€™ll get the first value as 0.

âž¤ After that, convert the whole column into a percentage by selecting Percentage format from the drop-down in the Number group of commands under the Home tab.

âž¤ Later, use Fill Handle to AutoFill the other YTD returns for all months.

8. Merging Excel 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 presented 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. We will be using SUM, OFFSET, and COUNTAÂ functions in this regard.

ðŸ“Œ Step 1:

âž¤ First, type the following formula in cell F11.

`=SUM(OFFSET(C5:C16,,,COUNTA(D5:D16)))`

âž¤ After that, press Enter & youâ€™re done with the YTD for the year 2020.

ðŸ“Œ Step 2:

âž¤ Next, assign the formula in cell G11.

`=SUM(D5:D16)`

âž¤ Thereafter, press ENTER & now youâ€™re done.

With these steps, weâ€™re setting the function to display the outputs in both of the cells F11 & G11. When we input data in column D, cells F11 as well as G11 will simultaneously show YTD values up to the specific months for the years 2020 & 2021 respectively. Thus weâ€™ll be able to compare cumulative sales values between those two data up to a particular time in both years.

9. Creating Pivot Table to Calculate YTDÂ

In our final method, weâ€™ll apply the Pivot Table to calculate YTD. We have a table for the sales values of 3 successive years.

ðŸ“Œ Steps:

âž¤ First, select the whole table & choose Pivot Table option from the Insert ribbon.

âž¤ Next, put Months in the Rows FieldÂ and the Year headers in the Values Field.

âž¤ After that, put your mouse cursor on any of the sales values in the year 2018 & open Value Field Settings from the options by right-clicking the mouse.

âž¤ Thereafter, selectÂ Show Values As tab >> Running Total In.

âž¤ Later, 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, you can see the output through which you can easily compare YTD to a particular month for different 3 years.

How to Calculate YTD Average in Excel

Excel has a function to calculate the YTDÂ average too. We will useÂ theÂ AVERAGE function to calculate theÂ YTDÂ average. Letâ€™s have a look at the following description. We will use the monthly total price only for this purpose.

ðŸ“Œ Steps:

âž¤ Type the following formula in cell D5, and press ENTER.

`=AVERAGE(\$C\$5:C5)`

âž¤ Drag the Fill IconÂ toÂ AutoFillÂ the lower cells.

Thus you can calculate the YTDÂ average by using theÂ AVERAGEÂ function.

Concluding Words

In the end, I hope all these methods mentioned above to calculate YTD or Year to Date in Excel will prompt you to apply in your regular Excel works. If you have 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.

<< Go Back to YTD Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.Â  In this role, he creates techy content all about Excel... Read Full Bio