• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Archives for Formulas

How to Calculate Age in Excel

There are a few methods to calculate age in Excel using formulas. In this tutorial, we will explain how to calculate age in Excel in years from a date of birth.

The formulas detailed can be used to return the difference between two specified dates for other reasons, such as length of service at an organisation, or the difference between two business transaction dates.

Download the calculate age sample file to practise.

How to Calculate Age in Excel?

Let’s get straight into this. The easiest way to calculate age in Excel is to use the method shown in this shorts video. It uses the TRUNC and YEARFRAC functions in a simple age formula.

The formula returns the difference between the date of birth and the current date in years.

=TRUNC(YEARFRAC(C3,TODAY(),1))
Simple age formula in Excel
[Read more…] about How to Calculate Age in Excel

Case Sensitive Lookup with XLOOKUP in Excel

The XLOOKUP function in Excel does not perform a case sensitive match by default. So, the values “London”, “LONDON”, and “london” would all result in a match with the XLOOKUP function.

So, how do you perform a case sensitive XLOOKUP formula?

Let’s find out. Read on or watch the video tutorial.

XLOOKUP does not Perform an Exact Match

In this example, the XLOOKUP function is returning the first matching value for the status of each individual and the [Status] in tblRates. The corresponding [Rate] is then returned by the formula.

This XLOOKUP formula is ignoring case when matching the lookup value to the text in the lookup array. This is best demonstrated by the incorrect rate returned for “Sally” in cell D4.

XLOOKUP does not conduct a case sensitive match by default

The formula has matched the status of “Sally” to the upper case status value from tblRates. However, we require an exact match on the lower case value found in row 4 of tblRates. The correct rate returned for “Sally” should be 350.

How to Make XLOOKUP Case Sensitive?

[Read more…] about Case Sensitive Lookup with XLOOKUP in Excel

How to Copy a Formula Down in Excel

When using formulas in tabular data in Excel, you will need to copy a formula down the entire column, so that each row has that same formula.

There are a few techniques to copying formulas in Excel, and this article will detail what you should be aware of, and why you would apply each specific technique.

Use the following links to jump to a specific section in the article.

  • How to Copy Formulas in Excel
    • Copy down without formatting
    • Copy a formula fast to all cells in a column
    • Use the Fill Down button or keyboard shortcut
    • Excel uses Relative Cell References
    • Copying formulas to non-adjacent cells
    • The magic of Ctrl + Enter
  • How to fix a cell when copying a formula down
    • Absolute reference in Excel
    • Mixed references in Excel
    • Using a named range
  • Entire column formulas in Tables
  • Using an array formula

Download the sample file to practise.

[Read more…] about How to Copy a Formula Down in Excel

How to Compare Dates in Excel

Everyone works with dates in Excel. Often a spreadsheet will contain multiple date columns. And a common question is “how to compare dates in Excel?”.

In this tutorial, we explain how dates are stored in Excel and see many examples of comparing dates including the use of numerous functions from our Excel functions list.

Download the sample file to follow along with the practical examples.

How does Excel Store Dates in the Cells?

Excel stores dates as whole numbers known as serial numbers. Each day, one is added to the serial number of the previous date.

For example, the Excel date, or serial number, for the 1st May 2023 is 45047.

[Read more…] about How to Compare Dates in Excel

LIVE Currency Exchange Rate in Excel

The rich data types make it easy to get a live currency exchange rate in Excel. You can then convert currencies with an Excel formula that updates when the exchange rate changes.

[Read more…] about LIVE Currency Exchange Rate in Excel
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Interim pages omitted …
  • Go to page 19
  • Go to Next Page »

Primary Sidebar

Recent Posts

  • How to Calculate Age in Excel
  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
  • Aggregate Rows in Power Query
  • How to Copy a Formula Down in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·