YEARFRAC Excel Formula

Author Zaheer    Category Formulas     Tags ,

In Microsoft Excel, formatting options are the ways in making your worksheets easier to recognize the significant data, thus, it is the way to read every data easier within the spreadsheet. These formatting features can improve the effectiveness and appearance of the Excel spreadsheet.

Using the AutoFormat in Spreadsheet Formatting

Spreadsheets contain hundreds of data. Thus, it is merely impossible and difficult for beginners to search for information they desire if they are not familiar with the basic of the Excel spreadsheet. Through the AutoFormat, the formatting of spreadsheet is definitely effective. In addition, by making the most significant information within the spreadsheet can stand out through the good option of font size, font style, background color and other reliable formatting options.

If these are all new to you and you are not comfortable with the available formatting options, there is an easy way to format your spreadsheet, which is the Auto Formatting. Thus, the beginners can definitely use the Auto Format options which give the worksheet a proficient appearance and make it easier to read.

Moreover, there are seventeen available styles of Auto format. The said styles affect some areas of formatting and they are as follows: borders, number formatting, background colors, alignment, patterns, fonts, alignment, and row and column size.

Choosing the Right Style

Bear in mind that not the entire styles are suitable for most spreadsheets. In addition, these are the three uses of selecting the style of AutoFormat.

  • Used only within the presentation of the slide show.
  • Printed and provided to the Excel audience as hand-outs.
  • Emailed to several associates

Thus, you can adjust the styles after or before it is being applied to the spreadsheet. Moreover, you have to click option buttons in dialog box of the AutoFormat.

Modify YourChosenStyle

After applying styles, you can now modify it through the options of regular formatting, which is located under Format Menu as well as through the formatting tool bar. There are hundreds of people who use Auto Format options as the beginning point. Thus, they also utilize the regular option of formatting in modifying their selection to exactly meet their needs.

Applying the AutoFormat Style into Your Spreadsheet

  • Choose the data in the spreadsheet that the user wants to format.
  • Select ‘Format > AutoFormat’ from menus and the Auto Format dialog box would appear.
  • Scroll from lists of style.
  • Select the style that you would like to use.
  • Click the ‘OK’ button.

Functions and formulas are the two most often used features in Microsoft Excel. This article will cover the use of one of the functions, which is Excel’s YEARFRAC function. YEARFRAC function is used to calculate the fractions of certain years, which is represented by quantity of the total days between 2 dates – the end_date and the start_date. Bear in mind that you have to use YEARFRAC function in identifying the ratio of the whole year’s obligations or benefits in assigning the specific terms. If ever the function returns the error value #NAME? or if it is not merely available, then you can load and install Analysis Tool Pak add-in. You can employ the syntax of the YEARFRAC function, which is the ‘YEARFRAC(start_date,end_date,basis)’.

YEARFRAC is one of the most sophisticated DATE function in Microsoft Excel.

  • Start_date is the date, which represents the beginning day.
  • End_date is the date, which represents the ending day.
  • Basis is kind of day counts basis that would be use in the calculation.

REMINDERS in Using the YEARFRAC Function in Excel

Be reminded that dates must be entered through DATE function in Microsoft Excel or as the result of other functions or formulas. For instance, use this formula DATE(2009,6,26) for the 25th day of June, 2009. However, when dates are considerably entered as texts, then problems will occur.


  • Microsoft Excel store dates as chronological serial numbers, therefore, dates are used to calculate.
  • The entire arguments are considerably truncated to certain integers.
  • If the values for the end_date or the start_date aren’t valid dates, then the YEARFRAC function return #VALUE! errors value.
  • If the basis is greater than four or if the basis is less than zero, then the YEARFRAC function return #NUM! error value.

Post comment