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.
Download Practice Workbook
You can download the Practice Workbook that we’ve used to prepare this article.
9 Suitable Approaches to Calculate YTD in Excel
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 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. We can do this simply by using the SUM function.
➤ First, in Cell E5, type:
➤ After that, press Enter & we’ll find the YTD for the first month.
➤ Next, use the Fill Handle to AutoFill 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.
Read More: How to Sum Year to Date Based on Month in Excel (4 Easy Ways)
2. Applying Excel Combined Functions to Calculate YTD
We can also calculate YTD Price Per Unit using the combination of SUM, IF, MONTH and OFFSET functions. Let’s have a look at the description below.
➤ First, make some modifications in the Month column. Say we are taking accounts in the 28th day of each month. So the first date will be 1/28/2021.
➤ After that, type the following formula in E5 and press the ENTER button. You will see the YTD Price Per Unit in January.
The formula references the D5 and C5 cells 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.
Thus you can calculate YTD using SUM, IF, MONTH and OFFSET functions.
3. Calculate YTD Using Excel SUMPRODUCT Functions
We can find the similar output by using SUMPRODUCT function too.
➤ First, type the following formula in Cell E5.
➤ After that, press Enter & you’ll get the result for the first month.
➤ Next, use Fill Handle again to autofill the whole column.
Read More: Growth Over Last Year Formula in Excel (A Step-by-Step Analysis)
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 January & February to compare sales data between two years- 2020 & 2021. The useful functions for this method are SUM, OFFSET and MATCH functions.
➤ First, in Cell I6, type:
➤ 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 percentage at once.
➤ Thereafter, use Fill Handle to AutoFill other YTD growth rates for all products.
Read More: How to Calculate YTD (Year-To-Date) Average in Excel (4 Approaches)
5. Calculating YTD Profits by Incorporating Excel Functions
We can find YTD values upto a particular month by inserting a specific date by using SUM, OFFSET, ROWS and MONTH functions.
➤ First, in Cell I7, our formula for this criterion will be:
➤ 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 column number upto which the calculation will be executed finally through OFFSET, SUM & ROWS functions together.
Read More: How to Calculate MTD (Month to Date) in Excel (3 Methods)
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 F5.
➤ After that, 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.
➤ 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:
➤ First, move onto Cell G5 now & type the formula below.
➤ Thereafter, press Enter & convert it into 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 very simple formula.
➤ First, in Cell F5, write down the formula below.
➤ Next, press Enter & you’ll get the first value as 0.
➤ After that, convert the whole column into 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.
Thus, you can determine the YTD Portfolio Returns.
Read More: Growth Formula in Excel with Negative Numbers (3 Examples)
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 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. We will be using SUM, OFFSET and COUNTA functions in this regard.
📌 Step 1:
➤ First, type the following formula In Cell F11.
➤ After that, press Enter & you’re done with the YTD for the year2020.
📌 Step 2:
➤ Next, assign the formula in Cell G11:
➤ Thereafter, 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.
9. 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.
➤ 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 Values Field.
➤ After that, 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.
➤ 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 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.
➤ Type the following formula in cell D5, press ENTER and drag the Fill Icon to AutoFill the lower cells.
Thus you can calculate YTD average by using the AVERAGE function.
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 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.
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/).