Power Query Archives - Microsoft Excel Tips and Tricks

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

[Read more…]

Aggregate Rows in Power Query

There are two main reasons you should consider aggregating rows in Power Query: creating a pivot-style report or reducing the number of rows and the granularity of your data without writing complex formulas.

This tutorial will teach you how to aggregate rows in Power Query, exploring how to aggregate both numeric and text values.

Download the practice file to follow along.

[Read more…]

Power Query Custom Data Types

This tutorial will take you through the new Power Query custom data types in Excel.

What does this all mean?

Well, you can now create your own data types in Power Query similar to the ones you see on the Data tab (Stocks, Geography, etc).

Stocks and Geography data types on the Data tab

These rich data types allow us to store many columns of data in just one column. This frees up space on the worksheet.

All columns of data are easily accessible using formulas or by simply typing the column name.

In this tutorial, you will see how to create Power Query data types and how to access their data with formulas.

[Read more…]

Import Multiple Excel Files with Multiple Sheets in Excel

In this post, we look at how to import multiple Excel files that contain multiple sheets into one Excel table.

Importing multiple files from a folder is one of the most popular functions of Power Query. A common question I get is “what about if an Excel file had multiple sheets”.

In this post, we answer that question.

You can download the files used in the tutorial to follow along.

Watch the Video

[Read more…]

Advanced Excel Skills

Excel is an extremely powerful program used by businesses all over the world. Users with advanced Excel skills are in huge demand.

If you are wondering “how to improve my Excel skills?”, you are in the right place. The list below is a collection of advanced Excel tips to enhance Excel skills and make you an Excel superhero.

Advanced Excel Formulas

If you asked someone, what are advanced Excel skills? You can be sure that advanced Excel formulas would be a common response.

Formulas are what drive an Excel spreadsheet. Formulas provide the muscle for a spreadsheet to analyse, manipulate, convert and look up data with incredible speed and power.

An advanced Excel user would need advanced formula skills. They would have a commonly used list of functions that they know well, but also the skills and confidence to adapt to any situation requiring formulas.

Ultimate VLOOKUP function guide

5 examples of the groovy SUMPRODUCT function

The INDIRECT function – 5 great examples

4 alternatives to nested IF formulas

List of Excel functions

Learn more advanced Excel formulas

[Read more…]