Home / Formula Types / Array / Makearray

Formula generator for MAKEARRAY function

The MAKEARRAY function returns an array of specified dimensions with values calculated by the application of a LAMBDA function. It takes three arguments: 'rows' (the number of rows in the array), 'columns' (the number of columns in the array), and 'LAMBDA' (a function that defines how each value in the array is calculated).

Formula generator

Spreadsheet AI is the #1 AI for generating and comprehending Excel and Google Sheets formulas. With its advanced capabilities, it goes beyond the basics by providing support for VBA and custom tasks. Streamline your spreadsheet with Spreadshee AI

Product Demo

How to generate an MAKEARRAY formula using AI.

To obtain information on the ARRAY_CONSTRAIN formula, you could ask the AI chatbot the following question: “To obtain the MAKEARRAY formula, you can ask the AI chatbot the following question: "Is there a formula in Excel that allows me to convert data into an array format?"

MAKEARRAY formula syntax

The MAKEARRAY syntax in Excel is used to create an array of values. It has two syntax forms: 1. Single value syntax: MAKEARRAY(value) - This form creates a single-cell array with the specified value. 2. Multiple values syntax: MAKEARRAY(value1, value2, ...) - This form creates an array with multiple cells, each containing one of the specified values. Both syntax forms can be used in various ways, such as assigning the array to a range of cells or using it as an argument in other Excel functions

Use Cases & Examples

In these use cases, we use the MAKEARRAY function to create an array of values. The MAKEARRAY function allows us to easily generate a list or matrix of values based on specific criteria or patterns.

Calculate Sales Revenue


Calculates the sales revenue for each product in a given dataset.


MAKEARRAY(ROWS(data), 1, LAMBDA(i, data[i][1] * data[i][2]))

Calculate Average Grade


Calculates the average grade for a list of students.


MAKEARRAY(1, COLUMNS(data), LAMBDA(j, AVERAGE(data[2:][j])))

Calculate Projected Profit


Calculates the projected profit for each month based on the current revenue and growth rate.


MAKEARRAY(1, COLUMNS(data), LAMBDA(j, data[1][j] * (1 + growth_rate)))

AI tips

Enhance Your Excel Efficiency with AI Tips: Discover our innovative Excel add-in feature, ‘AI Tips.’ Streamline your workflow and boost productivity as AI-powered suggestions offer real-time insights for optimal spreadsheet organization, data analysis, and visualization. Elevate your Excel experience with intelligent recommendations tailored to your unique needs, helping you work smarter and achieve more.

Provide Clear Context

When describing your requirements to the AI, provide clear and concise context about the data you have, the specific task you want to accomplish, and any relevant constraints or conditions. This helps the AI understand the problem accurately.

Include Key Details

Include important details such as column names, data ranges, and specific criteria that need to be considered in the formula. The more precise and specific you are, the better the AI can generate an appropriate formula.

Use Examples

If possible, provide examples or sample data to illustrate the desired outcome. This can help the AI better understand the pattern or logic you are looking for in the formula.

Mention Desired Functionality

Clearly articulate the functionality you want the formula to achieve. Specify if you are looking for lookups, calculations, aggregations, or any other specific operations.


Frequently Asked Questions

  • The MAKEARRAY function is a dynamic array function in Microsoft Excel that allows you to create an array of values based on specified criteria.
  • To use the MAKEARRAY function, you need to provide the criteria for generating the array. This can be done by specifying the starting value, ending value, and step value in the function arguments.
  • Yes, you can use the MAKEARRAY function to create an array with non-numeric values. Simply provide the desired values as arguments in the function.
  • The maximum number of elements that can be generated using the MAKEARRAY function is determined by the available memory in your system. However, it is recommended to limit the size of the array to avoid performance issues.
  • Yes, the MAKEARRAY function can be used with other functions in Excel. You can use the generated array as input for other functions or perform operations on the array directly.