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.

how to calculate ytd in excel

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.

Read More: How to Sum Year to Date Based on Month in Excel


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))

Applying Excel Combined Functions to Calculate YTD

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.

Calculate YTD Using Excel SUMPRODUCT Functions


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.

Determining YTD Growth Rate by Using Dynamic Formula


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.

Calculating YTD Profits by Incorporating Excel Functions

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.

Read More: How to Calculate MTD (Month to Date) in Excel


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.

Calculating YTD by Using Excel SUMIFS Functions

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.

Determining YTD Portfolio Returns for Stocks & Bonds


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.

Merging Excel Functions Together to Compare YTD

📌 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.

Creating Pivot Table to Calculate YTD 

📌 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.

calculate ytd with pivot table


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.

How to Calculate YTD Average in Excel

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


Download Practice Workbook

You can download the Practice Workbook that we’ve used to prepare this article.


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
Nehad Ulfat

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

2 Comments
  1. I still have a question
    When you want to see in a pivot table e.g YTD3 but for one line there are no entries in period 3 the pivot table shows for this period till YTD 2 how can I create a total into the zero entries to come to YTD3

    • Thank you for your inquiry, INGE. It is not really clear from your inquiry what you want. I believe you want to know how we can calculate the running total when a month’s entry is zero. In that situation, the Pivot table will add 0 to the previous running total, displaying the same prior running total as before. I hope this clarifies your concerns. If you have any further queries or wish to share your documents, please post them on our Exceldemy Forum (https://exceldemy.com/forum/).
      Regards
      Aniruddah

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo