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

The sample workbook will provide a few different scenarios for when you need to calculate YTD in Excel, such as YTD price, profit, or growth rate.

Method 1 – Calculating YTD with SUM Functions

Consider a business company that has sold a number of products in 12 months of a year, with the total selling prices for all months recorded in the sample table. To learn the Year-to-Date selling price per unit quality of the products for each month, use the SUM function by following these steps.

Steps:

• Type the formula in cell E5.

• Press Enter tofind the YTD for the first month.
• 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 and quantity. Here the divided part is cumulative prices for the products, and 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

Method 2 – Applying Excel Combined Functions to Calculate YTD

You can also calculate the YTD Price Per Unit using the combination ofSUM,IF,MONTH, and OFFSETfunctions.

Steps:

• Make some modifications in the Month column to include a specific date. Say you’re taking accounts on the 28th day of each month. So, the first date will be 1/28/2021.
• Type the following formula in cell E5 and press theENTERbutton. You will see the YTDPrice Per Unit inJanuary.

The formula references cells D5andC5 usingthe OFFSET function. It removes the Formula Omits Adjacent Cells error. TheIF functionreturns thePrice per Unit ofJanuaryif the month inB5is the first month of the year. Otherwise, it returns values for the later months of the year.

• Use the Fill Handle to AutoFill the lower cells.

Method 3 – Calculating YTD Using Excel SUMPRODUCT Functions

Steps:

• Type the following formula in cell E5:

• Press Enter to receive the result for the first month.
• Use the Fill Handle to autofill the whole column.

Method 4 – Determining YTD Growth Rate by Using a Dynamic Formula

Let’s compare sales data between two years for fixed successive months with YTD (see example table below). Specifically, the example will determine 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, andMATCHfunctions.

Steps:

• Type the following formula on cell I6:

• Press Enter.
• Under the Home ribbon, select Percentage from the drop-down in the Number group of commands. The decimal value will be turned into a percentage.
• Use the Fill Handle to AutoFill other YTD growth rates for all products.

Method 5 – Calculating YTD Profits by Incorporating Excel Functions

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

Steps:

• Go to cell I7 and insert the following:

• Press Enter.
• Autofill other cells in column I for all other products by using the Fill Handle.

For the example, the input date is May 12, 2021. The MONTH function extracts the month from this date, which formula then uses as the column number up to which the calculation will be executed.

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

Method 6 – Calculating YTD by Using Excel SUMIFS Functions

You can extract year, month, and date values with the help of YEARFRACand DATE functions, and then use them to find the YTD rate. For the sample dataset, selling prices of the products for 10 days have been recorded along with their cost prices. To determine the YTD profit rates for successive 10 days usingthe SUMIFS function, read below.

Steps:

• First, type the following formula in cell F5.

• Press Enter. This results in the fraction or percentage of the days up to the particular date in column B based on 365 days in a year.
• Use the Fill Handle to fill down the whole column F.

The YEARFRAC function returns the year fraction representing the number of whole days between start_date and end_date. The DATE function inside inputs the first day of the year 2021 (listed in E5).

• Go to cell G5 and type the formula below.

• Press Enter.
• Go to the Home tab, Number group, and select the percentage icon (%).
• Fill down the whole column G to get the YTD profit rates for all successive 10 days at once.

Method 5 – Determining YTD Portfolio Returns for Stocks & Bonds

Steps:

• In cell F5, write down this formula:

• Press Enter toget the first value as 0.
• Convert the whole column into a percentage by selecting the percentage icon in the Number section.
• Drag the Fill Handle to AutoFill the other YTD returns for all months.

Method 8 – Merging Excel Functions Together to Compare YTD

To compare the cumulative values between two specific and successive timespans from two different years, you’ll need to combine a few functions. Here, the sales values for all months in 2020 are presented in column C. When you input a value in column D you’ll get the comparative results up to that specific month between the two years. This uses SUM, OFFSET, and COUNTAfunctions.

Steps:

• Type the following formula in cell F11:

• Press Enter and you’re done with the YTD for the year 2020.

• Put the following formula in cell G11.

• Press Enter to finish.

When you 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 and 2021 respectively. This is due to the formula in F11 working through year 2020, but only up to the months filled in 2021.

Method 9 – Creating Pivot Table to Calculate YTD

Consider a table for the sales values of three successive years.

Steps:

• First, select the whole table and choose the Pivot Table option from the Insert ribbon.
• Put Months in the Rows Fieldand the Year headers in the Values Field.
• Put your mouse cursor on any of the sales values in the year 2018 and open Value Field Settings from the options by right-clicking.
• Select the Show Values As tab and Running Total In.
• Press OK to get the cumulative sales value or running total for the year of 2018.
• Repeat this process for the years 2019 and 2020.

• You can see the output that shows YTD for all months for the three years.

How to Calculate YTD Average in Excel

Use theAVERAGE function to calculate the YTD average.

Steps:

• Type the following formula in cell D5.

• Press Enter.
• Drag the Fill IcontoAutoFillthe lower cells.

This calculate the YTD average per month.

Download Practice Workbook

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

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