In this guide, I'll explain what Microsoft Fabric is in more detail, how it works, and walk you through building a project with it. If you already have an understanding of the platform, you can skip to the Microsoft Fabric project.

Here's what you'll learn about in this guide:

• What is Microsoft Fabric?
• Why you should learn about Microsoft Fabric
• Microsoft Fabric architecture and components
• How to get started by building a simple project
• How to create a workspace in Microsoft Fabric
• How to create a Lakehouse in Microsoft Fabric
• How to use Kaggle API data in Microsoft Fabric
• How to use the Data Wrangler in Microsoft Fabric
• How to perform customer segmentation in Microsoft Fabric
• How to visualize your lakehouse data in Power BI

Prerequisites

To follow along, you will need to have a Power BI license. You can get one for free to practice with using the Microsoft 365 Developer Program.

It would be also be helpful if you have knowledge of Microsoft Power BI and Python.

What is Microsoft Fabric?

Microsoft Fabric is an all-in-one analytics software-as-a-service (SaaS) platform for managing all your data analytics needs and workflows. Microsoft built this end-to-end platform to handle data-related data, from your data storage and migration to your real-time data analytics, data science projects, and data engineering workflow.

But how does it work?

This tool brings together various new and preexisting data tools and technologies—Power BI, OneLake, Azure Data Factory, Data Activator, Power Query, Apache Spark, Synapse Data Warehouse, Synapse Data Engineering, Synapse Data Science, Synapse Real-Time Analytics, Azure Machine Learning, and various connectors.

Why You Should Learn About Microsoft Fabric

The best part of Microsoft Fabric is its simplicity in terms of functionality. Using various technologies together, you can do everything all in one place and focus more on what you can do with it and less on licensing, supporting systems, dependencies, and how to integrate with all these different platforms.

Another benefit of the platform is how it handles your data. This provides and allows you to maintain a single reliable source of information. With Microsoft Fabric’s OneLake, you can have a single, unified data storage.

Microsoft Fabric also has Azure’s OpenAI service integrated into its layer. This way, you can use AI (Co-pilot) to help you discover insights quickly.

Lastly, since it is an all-in-one platform, there is a cost-saving edge since there is no need to subscribe to multiple vendors.

Microsoft Fabric Architecture

Think of Microsoft Fabric as your data estate.

Just like every piece of real estate, Microsoft Fabric has various components in its architecture.

Let’s start by looking at the terminology you'll encounter and need to understand when using Microsoft Fabric's architecture:

Experiences and Workloads:

These refer to the various capabilities of the platform. Every experience on the platform is tailored with a specific user in mind.

Below are some examples of the various experiences/workloads available. You'll notice that each of them are built for a specific purpose, task, and user.

• Data factory: This application gives users over 150 connectors to Lakehouses, warehouses, cloud, and on-premise data sources and orchestrates data pipelines for data transformation. A Lakehouse here refers to a data platform for storing structured and unstructured data. You can also copy your on-prem data to the cloud and load it into OneLake through the Data Factory.
• Synapse data engineering is part of the data engineering experience on the platform. It has some cool features like Lakehouses, built data pipelines, and a Spark engine.
• Synapse data warehouse provides you with a unified and serverless SQL engine. Like your “traditional” data warehouse, you have the full capabilities of your transactional T-SQL features.
• Synapse real-time analytics allows you to stream data from Internet of Things (IoT) devices, telemetry, and logs. You can also use the workload here to analyze semi-structured data using its Kusto Query Language (KQL) capabilities, just like Azure Data Explorer.
• Synapse data science allows you to build, collaborate, train, and deploy fully scalable end-to-end Machine learning (ML) and AI models. You can also carry out your ML experiments in your notebooks and log your models using the Fabric Auto Logging feature. A must-mention tool in this experience is the Data Wrangler, a Fabric graphical user interface for data transformation. With this tool, you can clean your data by simplifying by clicking buttons while the tool automatically generates the Python code for you. It is similar to Power Query.
• Business Intelligence with Power BI helps you quickly turn your business data into insightful analytic reports and dashboards.
• Data Activator allows you to take care of your data observability and monitor workloads in a non-code/low-code way. This tells you when specific data points hit a threshold or match a pattern. You can also automate particular actions and kickoff Power Automates flows when specific conditions occur.
• Copilot in Fabric provides you with an Azure OpenAI Service. This means you can build reports, describe how you want to ingest your data, summarize, explore, and transform your data using the natural language capability of Azure OpenAI.

Workspaces

Workspaces are similar to Power BI’s workspace. Here, you can share and collaborate with others and create reports, Warehouses, Lakehouses, dashboards, and notebooks.

Capacity Unit (CU)

A CU is the ability of your resource to perform or produce an output.

Now we'll look at the various components of Microsoft Fabric's architecture.

OneLake

OneLake is the central data repository for Microsoft Fabric that stores the data in Delta Lake format. Think of it as OneDrive for your data. This repository allows you to explore and find data assets in your organization.

One exciting thing is Shortcuts, which allows you to share or point to data in other locations in OneLake without moving or duplicating the data. This removes any case of data redundancy.

Lakehouses vs Warehouses

While both "houses" hold data, some differences exist between Lakehouses and Warehouses in Microsoft Fabric.

For starters, a Lakehouse can store any data type, whether structured or unstructured. It is, however, stored in the Delta format by default. The Delta format is a storage layer that offers ACID (Atomicity, Consistency, Isolation, Durability) transactions. A Warehouse, on the other hand, is more suited for structured data.

Lakehouses also support Notebooks. So you can work with various languages from PySpark to SQL and R. Warehouses, on the other hand, only use SQL.

Keep in mind, though, that Fabric provides you with two types of Warehouses: SQL Endpoint and Synapse Data Warehouse.

• SQL Endpoint is auto-generated when a Lakehouse is created. This mean you can have a SQL-based experience and can query Lakehouse data using T-SQL language.
• Synapse Data Warehouse is more of your traditional SQL engine. So you can use it to create and query data out of OneLake.

How to Get Started With Microsoft Fabric – An End-to-End Project Example

To get a glimpse of how the Fabric platform works, we will build a little project.

We'll create a Lakehouse to store a mall dataset from Kaggle using the Kaggle API. We will also transform our data using Data Wrangler. Then, we will perform customer segmentation on our data based on the customer's annual income and spending score using the KMeans clustering algorithm. This will allow us to group the customers into various categories like low income earners that don't spend, average income earning customers, and high income customers who do not spend much.

Let's get started.

How to Enable Fabric

The first thing we need to do is to log into Microsoft Power BI. Here, we will activate Microsoft Fabric's capabilities for our workspace.

To do this, follow these steps:

First, navigate to the capacity settings in the admin portal. The admin portal is where administrators control and manage the various Power BI features.

Admin Portal of Microsoft fabric

Then under the Tenant setting tab, look for Microsoft fabric tab.

Under that tab, enable the Users can create fabric items toggle to on. Once you've done that, select Apply.

Now your environment will be set up and the various services should appear at the bottom left of your screen.

Now you can see all the services like Power BI, Data Factory, and so on.

How to Create a Workspace in Microsoft Fabric

We'll use a mall customer segmentation dataset from Kaggle for this demo. This data, as mentioned in Kaggle, was created for the purpose of learning customer segmentation concepts.

Let's talk a little bit about the dataset. Imagine you have a supermarket mall and each customer has a membership card. You also have a data catalog of each customer with basic information like their customer ID, age, gender, annual income and spending score.

Now we want to segment these customer into various groups so we can improve customer loyalty, understand the customers better, and more effectively target our marketing strategy.

To achieve this, we will use the spending score assigned to each customer to define their purchasing power.

To get started, you'll need to create a new workspace. You can do that by following these steps:

1. Head to your Microsoft Fabric home page.
2. Select workspaces and click on New Workspace.
3. Give your workspace a name – I'm calling mine FabricMall.
4. Click on Advanced to view the dropdown options and select Trial if you are making use of your Fabric trial.
5. Click Apply.

How to create a workspace in Microsoft fabric

The next thing you want to do is to create a Lakehouse for your data.

How to Create a Lakehouse in Microsoft Fabric

To create a Lakehouse, first click on New within your workspace. This will display a list of various tasks you can do within your workspace.

Then select More options and select Lakehouse.

Selecting Lakehouse under "More options"

Then give it a name, like FabricMallLake, and click on Open notebook.

Click on New notebook and Open. You can rename your notebook at the top left corner of your notebook. The notebook is similar to the Jupyter notebook experience.

Notebooks in Fabric

How to Use Kaggle API Data in Microsoft Fabric

Notebooks allow us to write, visualize, and execute code. Within the Notebook, we will use Python to perform a customer segmentation on our data in Microsoft Fabric.

First, import Kaggle using the command below:

!pip install Kaggle

Next, you'll need to import your operating system and connect to the Kaggle API.

import os
os.chdir('/lakehouse/default/Files')
os.environ['KAGGLE_USERNAME'] = 'bennyifeanyi'
os.environ['KAGGLE_KEY'] = '050019167fbe0027359cdb4b5eea50fe'
from kaggle.api.kaggle_api_extended import KaggleApi
api = KaggleApi()
api.authenticate()
api.dataset_download_file('vjchoudhary7/customer-segmentation-tutorial-in-python', 'Mall_Customers.csv')

In the code above, os.chdir('/lakehouse/default/Files') represents our File API path. Also remember to replace the username and API Key with your own.

Now import Pandas. This will allow you to read your file.

import pandas as pd
df = pd.read_csv("/lakehouse/default/" + "Files/Mall_Customers.csv")
df.head()

But before we start segmenting our customers, let's transform our data by exploring the data wrangler.

How to Use the Data Wrangler in Microsoft Fabric

One of the most exciting things about this notebook is that you can perform data cleaning tasks without writing code using the Data Wrangler.

To do that, click on Data on the ribbon and select Transform DataFrame in Data Wrangler.

We will perform the following transformations:

• We will convert the gender column to lowercase.
• We will also rename the columns with special characters like the dollar sign, brackets, and a dash. This is because I noticed Fabric finds it hard to handle these characters at the moment.

To do these transformations, follow these steps:

Under the Operation tab, select Convert text to lowercase.

Pick the column – Gender in this example – and select Apply. This will convert your Gender column to lowercase and automatically generate the codes.

Data wrangler: Formatting text

Similarly, under the schema tab, select rename columns.

Rename Annual Income (k$) to AnnualIncome, and Spending Score (1-100) to SpendingScore.

Once you’re done with the transformation, click Add code to notebook.

Data wrangler: Rename column

Back in the notebook, we can visualize our data using the code below:

sparkdf = spark.createDataFrame(df_clean)
display(sparkdf)

Within the chart element created, select Customize chart. Pick the columns you want and select Apply.

Charts in Data Wrangler

Once that's done, we can save the data in the Lakehouse using this code below:

sparkdf.write.format("delta").mode("overwrite").saveAsTable("malldatadf")

Saving data in Lakehouse

How to Perform Customer Segmentation in Microsoft Fabric

For our customer segmentation, we will use the KMeans clustering algorithm to segment the customers based on their annual income and spending score.

K-means clustering is an unsupervised machine learning algorithm. It groups similar data points in your data based on underlying observations, similarities, and input vectors.

We will do this by importing our libraries, applying our K-means by training the K-Means clustering model, and visualizing the clusters of customers based on their annual income and spending score.

We will also include and show the centroids of each cluster, providing insights into the distribution of customers in the dataset.

The centroids here refers to the center points of the clusters found by our algorithm. This is calculated as the average of all the data points in that cluster. When we visualize the clusters, the centroid will be represented with a distinct symbol or color.

Run this code to achieve this:

import numpy as np
import seaborn as sns
import matplotlib.pyplot as plt
from sklearn.cluster import KMeans
from sklearn.preprocessing import StandardScaler
from sklearn.preprocessing import MinMaxScaler
X = df_clean[['AnnualIncome', 'SpendingScore']]
# Feature normalization
scaler = MinMaxScaler()
X_scaled = scaler.fit_transform(X)
kmeans = KMeans(n_clusters=5, init='k-means++', random_state=42)
kmeans.fit(X_scaled)
plt.figure(figsize=(10, 8))
for cluster_label in range(5):  # Loop through each cluster label
cluster_points = X[kmeans.labels_ == cluster_label]
centroid = cluster_points.mean(axis=0)  # Calculate the centroid as the mean position of the data points
plt.scatter(cluster_points['AnnualIncome'], cluster_points['SpendingScore'],
s=50, label=f'Cluster {cluster_label + 1}')  # Plot points for the current cluster
plt.scatter(centroid[0], centroid[1], s=300, c='black', marker='*', label=f'Centroid {cluster_label + 1}')  # Plot the centroid
plt.title('Clusters of Customers')
plt.xlabel('Annual Income (k$)')
plt.ylabel('Spending Score (1-100)')
plt.legend()
plt.show()

Here's the output:

Performing Customer Segmentation in Microsoft Fabric

The result of our analysis shows that our customers can be grouped into 5 clusters:

• Cluster 1 (Purple) are low income earners with a low spending score.
• Cluster 2 (Blue) are low income earners with a high spending score.
• Cluster 3 (Red) are average income earning customers with significant spending scores.
• Cluster 4 (Orange) are high income customers who do not spend much at the mall. They’re probably not satisfied with the services rendered.
• Cluster 5 (Green) are high income customers with a high spending score.

We can also save our prediction as a new dataset using this code:

# Create a new DataFrame to store the clustering results
cluster_df = pd.DataFrame(data=X, columns=['AnnualIncome', 'SpendingScore'])
cluster_df['Cluster'] = cluster_label
sparkclusterdf = spark.createDataFrame(cluster_df)
sparkclusterdf.write.format("delta").mode("overwrite").saveAsTable("clusterdatadf")

Customer segementation prediction

Want to take a look at the notebook? You can download it from my GitHub.

How to Visualize Lakehouse Data in Power BI

Now we can decide to visualize our data on a dashboard within Fabric.

Head back to the FabricMall workspace and select the semantic model type of the FabricMallLake Lakehouse.

semantic model type of the FabricMallLake LakeHouse

Then select Manage default semantic model.

Manage default semantic model In Microsoft Fabric

Pick your dataset, click Confirm, and then select New Report.

Let's visualize the average age in our data. To do this, click on the card visual and drag the age into this card. This will automatically create a visual showing the average age in your dataset.

Power BI service in Microsoft Fabric

Just like in Power BI Desktop, you can create your measure, build your report, and publish your dashboard. You can learn more about how to create visuals in Power BI using this free freeCodeCamp YouTube data analysis video.

Alternatively, you can open Power BI Desktop, and connect to your Lakehouses from Onelake data hub.

Connect to your Lakehouse in Power BI

Where Can I Learn More about Microsoft Fabric?

Though Microsoft Fabric is a pretty new data platform, I hope you can tell that this tool will help you ease the way you and your team consume, analyze, and get insight from your data.

To learn more you can start with the fabric official documentation or any helpful YouTube tutorial like Francis’s Fabric course. I would also advise you to start with freeCodeCamp's Fabric publication tags if you want a compilation of resources.

Lastly, if you’re new to data analysis, start your journey today with freeCodeCamp’s Data Analyst Bootcamp for Beginners on YouTube. It covers everything from SQL, Tableau, Power BI, and Python to Excel, Pandas, and real-life projects building.

If you enjoyed reading this article and/or have any questions and want to connect, you can find me on LinkedIn, Twitter and do check out my articles on freeCodeCamp.

Microsoft Excel is one of the most widely used spreadsheet applications, especially in finance and accounting. This is partly because of its easy UI and unmatched depth of functions.

In this article, you will learn:

• What Excel formulas are
• How to write a formula in Excel
• What Excel functions are
• How to work with an Excel function
• Lastly, we'll take a look at dynamic Excel functions.

What do I need to install on my computer to follow this article?

To follow along, you will need to have Microsoft Excel installed on your computer. We’ll use a Windows computer for this article.

How can I install Microsoft Excel on my computer?

Follow these steps to install Microsoft Excel on your Windows computer:

1. Sign in to www.office.com if you’re not already signed in.
2. Sign in with the account associated with your Microsoft 365 subscription. You can also try out Office for free as well.
3. Once signed in, select “Install Office” from the Office home page. This will automatically download Microsoft Office onto your Windows computer.
4. Run the installer to set up Microsoft Office and select "Close" once you're done.
5. Once done, select the “Start” button (located at the lower-left corner of your screen) and type “Microsoft Excel.”
6. Click on Microsoft Excel to open it.
7. Accept the license agreement, and let's get started.

What are Excel Formulas?

An Excel formula is an expression that carries out an operation based on the value of a cell or range of cells. You can use an Excel formula to:

• Perform simple mathematical operations such as addition or subtraction.
• Perform a simple operation like joining categorical data.

It's important to understand two things: Excel formulas always begin with the equals "=" sign and they can return an error if not properly executed.

What Operators Are Used in Excel Formulas?

There are four different types of operators in Excel—arithmetic, comparison, text concatenation, and reference. But for most formulas, you’ll typically use these three:

Arithmetic operators

Comparison operators

Text concatenation

Here you have just the ampersand “&” sign for joining text.

How Can I Create an Excel Formula?

Let's take a simple scenario using one of the arithmetic operators.

In math, to add up two numbers, let's say 20 and 30, you will calculate this by writing: 20 + 30 =

And this will give you 50.

In Excel, here is how it goes:

1. First, open a blank Excel worksheet.
2. In cell A1, type 20.
3. In cell A2, type 30.
4. To add it up, type in = 20 + 30 in cell A3.

How can I create an Excel formula?

5. Then, press ENTER on your keyboard. Excel will instantly calculate this and return 50.

How can I create an Excel formula? Adding numbers in Excel

I mentioned earlier that every formula begins with the equal "=" sign. That's what I meant. To write a formula, you type the equal to sign followed by the numeric values. This also applies to cases of subtraction, division, multiplication, and exponentiation.

Let's take another simple scenario using one of the comparison operators. Assume we want to find out if 30 is greater than 40.

In Excel, here is how we would do it:

1. Type in =30>40
2. Press ENTER.

How can I create an Excel formula? Comparison operators

3. This will return a FALSE because 30 isn't greater than 40. Excel uses TRUE and FALSE for logical statements, the same way we human says yes and no.

How can I create an Excel formula? Comparison operators

Lastly, let's take another simple scenario using the text concatenation operator – the ampersand “&” sign. This works with your string data types and you use it to join text.

Assume we have "Welcome", "To", and "FreeCodeCamp" all in different cells—A1, A2, and A3—of your worksheet. We would type =A1&” “&A2&” “&A3 to join them.

The space in quotes “ “ represents that we want a space between our words.

How can I create an Excel formula using the ampersand “&” sign

Another tip: the formula bar shows the formula used to generate a value.

What Are Excel Functions?

Excel functions are predefined inbuilt formulas that perform mathematical, statistical, and logical calculations and operations using your values and arguments.

For Excel functions, you should know that:

• They’re formulas, so yeah, they start with the equal "=" sign as well.
• The order is very important.

There are over 500 functions available. You can find all available Excel functions on the Formulas tab on the Ribbon.

All available Excel formulas and functions

But why use a function when you can just write a formula?

Here are some benefits of Excel functions.

• To improve productivity and effectiveness.
• To simplify complex calculations.
• To automate your work.
• To quickly visualize data.

What Makes Up Excel Functions?

Unlike formulas, Excel functions are made up of a structure with arguments you need to pass.

Every function:

• Starts with the equals "=" sign
• Has a name. Some examples are VLOOKUP, SUM, UNIQUE, and XLOOKUP.
• Requires arguments which are separated by commas. You should know that semicolons are used as separators in countries like Spain, France, Italy, Netherlands, and Germany. You can, however, change this via the Excel setting.
• Argument with the square brackets [] are optional
• Has an opening and closing parenthesis.
• Has an argument tooltip which shows you what you should pass.

There are some exceptions. For example,

• The DATEDIF doesn't show in Excel because it is not a standard function and gives incorrect results in a few circumstances. However, here is the syntax:

DATEDIF(birthdate, TODAY(), "y")

• Functions like PI(), RAND(), NOW(), TODAY() require no argument.

How to Use Excel Functions

Let's look at a few functions:

How to Use the SUM() Function in Excel

According to the documentation, the SUM function adds values. Here is the syntax:

Excel Sum Syntax

Let's assume that we have a line of numbers from 1 to 10 and we want to add it up. To achieve this, we will just type =SUM(A1:A10). The A1:A10 simply returns an array of number that are situated on cell A1 to A10 which are A1, A2, and A3 up through A10.

How to use Sum in Excel

Since the second argument is optional, that means a sum(A10) will return a value. In our case, it will return just 10 since A10 has the value 10 in it. Give it a try.

If you were writing this using the addition operator, you would have written:

=1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10

or

=A1 + A2 + A3 + A4 + A5 + A6 + A7 + A8 + A9 + A10

This doesn't look very productive or efficient.

How to Use the TODAY() Function in Excel

According to the documentation, the TODAY function displays the current date on your worksheet. It also requires no argument. Here is the syntax:

How to use Today() in Excel

Excel displays the current date automatically according to your computer's date and time setting. The same goes for the NOW() function, which displays the current date and time.

How to Use the CONCATENATE() Function in Excel

Let’s look at a text function. You use CONCATENATE to join two or more text strings into one string together.

Here is the syntax:

Excel CONCATENATE() Syntax

Let's assume we want to join “This is” with “freeCodeCamp” – but in your cell, you have just "freeCodeCamp."

freeCodeCamp Excel

If you’re going to write a string inside a formula, you must write it inside quotes like this “ “.

Why?

This way, Excel wouldnt think you're trying to write another function.

How to use Excel CONCATENATE function

This will return the phrase “This is freeCodeCamp”

How to use CONCATENATE() in Excel

How to Use the VLOOKUP() Function in Excel

This is one of Excel’s most interesting and commonly used formulas or functions. You use it to find a value in a table or range by row.

Here is a scenario:

We have a simple table that shows various films along with their genre, lead studio, audience score %, profitability, rotten tomatoes %, worldwide gross and year. I would use just the first 10 rows of the sample data from this GitHub Gist.

Flim dataset

I want that, whenever I type in a movie in the yellow cell, the year should get displayed in the green cell. Let's use VLOOKUP to find it.

This is the VLOOKUP syntax:

Excel Vlookup Syntax

Besides writing your formulas in the cell, you can also write them using the Excel Insert Function (fx) button, which is close to the formula bar.

Let's try this.

1. Write =Vlookup( on the green cell.
2. Click on fx. A dialogue box will pop up showing all the arguments this formula needs.
3. Input the value for each argument.
4. Lookup_value (required argument): This is what you want to find. In our case, that is the movie “Youth in Revolt” which is in cell B1.
5. Table_array (required argument): This is just asking you for the table that contains the data. You give it the entire table, which in our case is A4:H13
6. Col_index_num (required argument): This is asking you for the column number of the table you gave. In our case, we want the year. This is in column 8.

7. Range_lookup (optional argument): Lastly, we pick if we want an approximate match (TRUE) or an exact match (FALSE).

   • TRUE means approximate match, so it returns the closest or an estimate.

   • FALSE means exact match, so it returns an error if it's not found.

8. We would go for the FALSE because we want the exact match.

How to use VLOOKUP() in Excel

9. Click on "OK." Excel will return 2010.

However, you can write this in the cell by typing in =VLOOKUP(B1,A4:H13,8,FALSE) in your cell.

How to use VLOOKUP() in Excel

Tips and Rules When Writing Excel Functions

When writing our function, Excel provides some formula tips.

1. The Argument tooltip doesn't leave until you close the last parenthesis.
2. The formula bar shows your formula.
3. The argument you are currently writing is always dark. Take a look at the lookup_value in the image below.
4. The square brackets [] tell you it is optional.
5. Lastly, the colour code – our B1 is in blue, and cell B1 is in blue to guide us on what cell or table was picked. The same thing will happen to A4:H13 when we pick it as our table_array argument.

Vlookup and Excel formula bar

How to Work with Nested Functions in Excel

A nested function is when you write a function within another function. For example, finding the average of the sum of values.

The first tip when writing a nested function will be to treat every function individually. So address the first function before addressing the second. A pro tip would be to look at the argument tooltip when writing it.

Let's take a simple scenario.

We have two arrays of numbers. Each has the scores of students in the class. I want to add the two arrays before I get the average.

Let's get started.

1. Type in your = followed by the average.
2. The number one will be the sum of the scores from class one.
3. The number two will be the sum of the scores from class two.

Nested Function: Excel AVERAGE and SUM syntax

Excel AVERAGE syntax

Finally, don't forget the closing parenthesis.

Thus, the formula will be =AVERAGE(SUM(B3:B8),SUM(D3:D8)).

How to Work with Dynamic Array Functions in Excel

Dynamic array functions are formulas associated with spill array behaviour.

Before now, you wrote a function and it returned just a single input. We call these kinds of functions legacy array formulas.

Dynamic array functions, on the other hand, will return values that will enter the neighbouring cells. A few examples of dynamic array functions are:

• UNIQUE
• TEXTSPLIT
• FILTER
• SEQUENCE
• SORT
• SORTBY
• RANDARRAY

Let's look at the UNIQUE formula.

How to Use the UNIQUE() Formula in Excel

The unique formula works by returning the unique value from an array or list. Let's use the movie sample data from this GitHub Gist. This table contains 77 rows of film excluding the heading.

Flim data GitHub

Let's try to get the unique years from our dataset – that is, years without duplicates.

To do this:

1. Type =UNIQUE(
2. Select the entire array of values from the year column: =UNIQUE(H2:H78)

How to use Excel UNIQUE formula

3. Close the parenthesis and press Enter.

Though the formula was written in a single cell, the returned value got spilled into the cells below it. That's the spilled array behaviour.

Excel dynamic array formula

How to Create Your Own Functions in Excel

Microsoft Excel released a bunch of new functions to make user more productive. One of these function was the LAMBDA function.

The LAMBDA function lets you create custom functions without macros, VBA or JavaScript, and reuse them throughout a workbook.

The best part? you can name it.

How to Use the LAMBDA() Function in Excel

This LAMBDA function will increase productivity by eliminating the need to copy and paste this formula, which can be error-prone.

Here is the LAMBDA syntax:

Excel LAMBDA function

Lets start with a simple use case using the movie sample data from this GitHub Gist.

The Movie dataset

We had a column called "Worldwide Gross", lets try to find the Naira value.

1. Create a new column and call it "Worldwide Gross in Naira".
2. Right below our column name, Cell I2, type =lAMBDA(
3. LAMBDA requires a parameter and/or a calculation.

The parameter means the value you want to pass, in our use case we want to change the gross value. Lets call it gross.

The calculation means the formula or function you want to execute. For us, that will be to multiply it with te exchange rate. At the moment, that's 670. so lets write gross * 670.

How to use LAMBDA function in Excel

4. Press Enter. This will return an error because, gross doesnt exist and you need to let excel know of these names.

Using Excel LAMBDA

5. To make use of the newly created function, you need to copy the syntax written.

Writing function with Excel LAMBDA

6. Go to the formula ribbon and open the name manager.

Excel name manger using the LAMBDA function

7. Define the name manger parameters:

8. The name is simply what you want to call this function. I am going with NairaConvert.

9. The scope should be workbook because you want to use this function in the workbook.
10. The comments explains what your function does. It is acts as a documentation.
11. In the refer to, you should paste the copied function syntax.

Excel name manager

8. Press Ok.

9. To use this new function, you call it with the name you defined it as—NairaConvert—and give it the gross which is our worldwise gross on G2.

Custom function with LAMBDA

10. Close the parenthesis and press Ok

Calculating with Excel LAMBDA

Where Can I Learn More about Excel?

There are a ton of resources for learning Microsoft Excel nowadays. So many that it is hard to figure out which ones are up-to-date and helpful.

The best thing you can do is find a helpful tutorial and follow it to completion, instead of attempting to take several at once. I would advise you to start with freeCodeCamp's Microsoft Excel Tutorial for Beginners - Full Course, which is available on YouTube.

You should also join communities like the Microsoft Excel and Data Analysis Learning Community. However, if you’re looking for a compilation of resources, check out freeCodeCamp's publication Excel tags.

If you enjoyed reading this article and/or have any questions and want to connect, you can find me on LinkedIn or Twitter.