Function: XMATCH

Function: XMATCH

The XMATCH function is a Lookup & Reference function that returns the relative position of a lookup value from a specified array or range. In this guide, we’re going to show you how to use the XMATCH function and also go over some tips and error handling methods....
Function: XLOOKUP

Function: XLOOKUP

The XLOOKUP function is a Lookup & Reference function that can find values in a table or a range by row. Similar to the better-known VLOOKUP function, you can use this function to do things like looking up the name or price of a product by its code number. In this...
How to avoid formatting change in CSV files in Excel

How to avoid formatting change in CSV files in Excel

Data formatting can get mixed up when you open a data file with CSV format. In this article, we are going to show you how to avoid formatting change in CSV files in Excel. Download Workbook If you need to prevent numbers from changing into dates when entering data,...
How to consolidate text with Pivot Table in Excel

How to consolidate text with Pivot Table in Excel

In this article, we are going to show you how to consolidate text with Pivot Table in Excel 2013 or newer. Download Workbook If you are using Office 365, also see our alternative approach for consolidating text in Excel. Thanks to the dynamic array functions like...
How to consolidate text by a condition in Excel

How to consolidate text by a condition in Excel

In this article, we are going to show you how to consolidate text by a condition in Excel. Download Workbook Please note that we will be using the new dynamic array functions UNIQUE and FILTER in this post. These functions currently are only available to Office 365...
How to create a 3D Map in Excel

How to create a 3D Map in Excel

In this article, we are going to show you how to create a 3D Map in Excel. Download Workbook What is a 3D Map? 3D Maps were first introduced as part of Power Maps as an add-in for Excel 2013. Along with Power Query. Power Maps can be useful for reporting purposes. In...
Excel Mouse Tips & Tricks

Excel Mouse Tips & Tricks

In this article, we are going to show you some mouse shortcut tips and tricks that will enhance your Excel productivity. For keyboard shortcuts please see: 10 Most Useful Shortcuts in Excel Mouse tips and tricks for Excel window and structure Close All Click on the...
How to get a list of file names in Excel

How to get a list of file names in Excel

Tracking and managing data across multiple files can be challenging. With Power Query, you can get the file names from a specific folder and all its sub-folders. In this article, we are going to show you how to get a list of file names in Excel. Download Workbook We...
How to use the "Pick from drop-down list" feature in Excel

How to use the "Pick from drop-down list" feature in Excel

Dropdown lists are a common example of data validation used in organizing data input. In this article, we are going to show you how to use pick from drop-down list feature in Excel. Download Workbook The “Pick from drop-down list” feature generates a...