Microsoft Excel Tips and Tricks - Computergaga

Home / Archives for Power Query

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…] about Power Query Custom Data Types

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…] about Import Multiple Excel Files with Multiple Sheets in Excel

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…] about Advanced Excel Skills

Import Multiple Excel Files from a Folder into a Single Excel File

In this tutorial we look at how to import multiple Excel files into Excel from a folder using Power Query.
It is amazing how simple this process can be with Power Query and modern day Excel. However it can still come with the odd hiccup.
In this example I want to import all of the Excel files from the folder shown below.
Folder containing multiple Excel files
Ideally this folder would contain Excel files ONLY. But when you have teams of people working on a shared area, things happen.
Somehow a PDF file and a JPG (picture of me) have appeared in the folder.
This can create complications with the query we will create to import the Excel files. So we will build in some protection so that the process works seamlessly. Both for now, but also for future imports.
In the future it will be a case of clicking the Refresh button. And any changed files, or additional files will all be imported again without complication. [Read more…] about Import Multiple Excel Files from a Folder into a Single Excel File