OFFSET Archives - Xelplus - Leila Gharani

How to Use SCAN Function in Microsoft Excel

Leila Gharani — Fri, 05 Nov 2021 14:44:00 +0000

Learn how to use the new Excel SCAN function! This function is a Lambda helper function. You can use it to "scan" a range and apply a calculation to each cell. In this example you'll learn how to use SCAN to get to-date values and how you can tweak it to return YTD values.

The post How to Use SCAN Function in Microsoft Excel appeared first on Xelplus - Leila Gharani.

Google Sheets QUERY Function Explained

Leila Gharani — Tue, 29 Dec 2020 18:42:00 +0000

The Query function in Google sheets is one of the most powerful Google Sheets functions. It's easy to learn if you follow the rules laid out in this tutorial. You'll be ready to solve any data analysis problem in Google Sheets.

The post Google Sheets QUERY Function Explained appeared first on Xelplus - Leila Gharani.

How a Recursive LAMBDA Works

Leila Gharani — Thu, 17 Dec 2020 10:00:00 +0000

With the new Excel Lambda function you can create your own custom functions that you can reuse. The best thing about Excel Lambdas is you can create a function that loops through many cells. You can do this with ZERO coding! In part 2, we will create a Lambda function that can reference itself.

The post How a Recursive LAMBDA Works appeared first on Xelplus - Leila Gharani.

How to Get Multiple Match Results in Excel Without the FILTER Function

Leila Gharani — Thu, 28 May 2020 09:00:00 +0000

If you don't have the Filter function in Excel, you need to check out this easy formula approach to get multiple match results in Excel. Download the workbook and use the version that fits your problem best.

The post How to Get Multiple Match Results in Excel Without the FILTER Function appeared first on Xelplus - Leila Gharani.

How to Create Excel Dependent Drop Down Lists on Many Rows

Leila Gharani — Thu, 26 Mar 2020 10:00:00 +0000

Create multiple dependent drop-down lists in Excel that you can copy to every row. You'll also learn how to exclude the blank cells on the bottom to make sure each list is restricted to the number of categories available.

The post How to Create Excel Dependent Drop Down Lists on Many Rows appeared first on Xelplus - Leila Gharani.

Expandable Conditional Drop Down Lists in Excel

Leila Gharani — Fri, 20 Jul 2018 07:16:00 +0000

Quickly create dynamic drop-down lists in Excel that are dependent on another list. Automatically exclude blank cells and allow the drop-down to expand as new values are added.

The post Expandable Conditional Drop Down Lists in Excel appeared first on Xelplus - Leila Gharani.

VBA Copy Paste

Leila Gharani — Thu, 19 Apr 2018 17:31:00 +0000

This tutorial shows you how you can copy and paste with VBA. You will learn the copy and pastespecial methods, as well as resize and offset properties in order to fully control what you copy and paste from ranges.

The post VBA Copy Paste appeared first on Xelplus - Leila Gharani.

VBA Writing To Ranges

Leila Gharani — Thu, 12 Apr 2018 16:08:00 +0000

Learn how to use ranges, cells property, offset, named ranges, difference between activecell and selection, and how to reference entire rows or entire columns and also how to use autofit in Excel VBA.

The post VBA Writing To Ranges appeared first on Xelplus - Leila Gharani.

Find Multiple Matches in Excel & Create a Dependent Drop Down List

Leila Gharani — Thu, 15 Mar 2018 10:38:00 +0000

In this tutorial, we will create a dependent drop-down list based on a messy Excel Table. For that purpose, we will extract all records for each category using the combination of INDEX and AGGREGATE. Next, we will fix the column referencing in the table. Finally, we will use a combination of OFFSET and COUNTIF to get a drop-down list restricted to non-empty cells.

The post Find Multiple Matches in Excel & Create a Dependent Drop Down List appeared first on Xelplus - Leila Gharani.

Extract Unique Items for Dynamic Data Validation Drop-Down List

Leila Gharani — Fri, 05 Jan 2018 14:19:00 +0000

Learn how to extract unique items from a list that contains multiple instances of each item, and use this list in a dynamic dropdown so that any time the list expands, your data validation list is updated. The solution will work in any Excel version, no Office 365 required.

The post Extract Unique Items for Dynamic Data Validation Drop-Down List appeared first on Xelplus - Leila Gharani.