Free Guide to Installation, Fixing and Troubleshooting

TOCOL function is used to transform an array into a single column. The TOCOL function lets you rearrange values in 2D cell ranges to a single column. It’s handy for combining data across multiple columns and rows into a single column. Syntax: TOCOL(array, [ignore], [scan_by_column]) Here, · array : The array or reference to return as a column. · ignore : [Optional] Use the values listed below to specify whether to ignore certain types of values. By default, no values are ignored. o 0 : Keep all values (default) o 1 : Ignore blanks o 2 : Ignore errors o 3 : Ignore blanks and errors · scan_by_column : [Optional] Scan the array by column. By default, the array is scanned by row. Scanning determines whether the values in the function result are ordered by row or by column. Suppose you have the following data in cells E1:H6. To convert this data to a si...

How to Split a Text in Multiple Columns in Excel

The TEXTSPLIT function is a new function that is available in Microsoft Excel 365. It is used to split a text string into separate values based on a delimiter character. The resulting values are returned as a dynamic array. Syntax: TEXTSPLIT(text, delimiter, [number]) Here, text" is the text string that you want to split. "delimiter" is the character that you want to use as the delimiter. This can be a single character or a string of characters. "number" (optional) is the maximum number of values to return. If this argument is not provided, all values will be returned. Suppose you have some text strings in column D. If you want to split those text strings into separate values based on the comma delimiter, you can use the following formula: This will return the values in each row as a dynamic array. You can then use this dynamic array in other formulas and functions.

How to Take a Portion of an Array in Excel

In Excel, TAKE function is used for this purpose. The TAKE function in Excel 365 is a dynamic array function that returns a specified number of elements from the beginning of an array or from a specified position within an array. Syntax: TAKE(array, count, [start]) Where, array: the range or array from which to return elements. count: the number of elements to return. start: (optional) the position within the array from which to start counting. If omitted, the function will start counting from the first element of the array. Here is an example of using the TAKE function to extract the first 2 columns and 3 rows from a list of numbers: This would return the array with 1st 3 rows and 2 columns. Note that the TAKE function returns a dynamic array that automatically spills into neighboring cells if the output is larger than a single cell.

How to Get Stock Market Price Information in Excel

The STOCKHISTORY function is a new dynamic array function introduced in Excel 365. It allows you to retrieve historical stock prices and other information for a specified range of dates and a stock symbol. The syntax for the function is as follows: STOCKHISTORY(stock_symbol,[start_date],[end_date],[interval],[headers],[property]) Where: · stock_symbol: The symbol of the stock you want to retrieve information for. This can be a text string, cell reference or a range of cells. · start_date: (Optional) The start date of the period you want to retrieve information for. This can be a date or a reference to a cell containing a date. If omitted, the function will default to one year ago from today's date. · end_date: (Optional) The end date of the period you want to retrieve information for. This can be a date or a reference to a cell containing a date. If omitted, the function will default to today's...

How to Sort Data by Column in Excel

The SORTBY function in Excel sorts a range or an array based on the values in one or more columns. It takes two arguments: an array or a range, and one or more columns by which to sort the data. The function returns a sorted array or range. Syntax: SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...) Where: · array is the array or range to sort. · by_array1 is the array or range by which to sort an array. · sort_order1 is the optional sort order for by_array1, either 1 (ascending) or -1 (descending). · by_array2 and sort_order2 are optional additional arrays and sort orders to use for sorting if there are ties in by_array1. Here's an example of how to use the SORTBY function to sort a table of data by two columns: In this example, the function sorts the range D2:F11 ba...

How to Sort Array in Ascending or Descending Form in Excel

The SORT function is used to sort an array. It is used to sort a range or array in ascending or descending order based on one or more columns, returning a sorted array of values, and does not modify the original range or array. Syntax: SORT(array, [sort_index], [sort_order], [by_col]) The arguments are: array: The range or array to be sorted. sort_index (optional): The column number or array of column numbers to sort by. If not specified, the function sorts by the first column. sort_order (optional): A 1 or -1 indicating ascending or descending order respectively. If not specified, the function sorts in ascending order. by_col (optional): A boolean value indicating whether to sort by columns (TRUE) or rows (FALSE). If not specified, the function sorts by columns. For example, we have a exam marksheet of 10 students. To sort them from low to high, we can use the function as follows: The function returns the marksheet in ascending order based on marks. Note that, SORT f...

How to Generate Sequential Array in Excel

The SEQUENCE function is used to generate a sequence of numbers in an array. It is available in Excel 365. The syntax of the SEQUENCE function is as follows: SEQUENCE(rows,[columns],[start],[step]) Here, rows is the number of rows in the sequence. It can be a positive integer or a reference to a cell containing a positive integer. columns is the number of columns in the sequence. It can be a positive integer or a reference to a cell containing a positive integer. If omitted, the default value is 1. start is the starting value of the sequence. It can be a positive or negative integer, or a reference to a cell containing a positive or negative integer. If omitted, the default value is 1. step is the increment value between each number in the sequence. It can be a positive or negative integer, or a reference to a cell containing a positive or negative integer. If omitted, the default value is 1. Here is an example of using SEQUENCE function: The output of th...

How to Scan an Array and Return Custom Result in Excel

The SCAN function is a new dynamic array function available in Excel 365. It is used to apply a function to a sequence of values and accumulate the results. The function takes two arguments: the first argument is the sequence of values to apply the function to, and the second argument is the function to apply. Syntax: SCAN(sequence, function) The sequence argument can be a range of cells, an array, or a reference to another formula that returns a sequence of values. The function argument is a formula that takes two arguments: the accumulated result so far, and the current value in the sequence. For example, suppose you have a column of numbers and you want to calculate the cumulative sum of those numbers. You can use the SCAN function with a lambda function to achieve this: This formula starts by accumulating the first value in the sequence, which is the first cell in the range D1:D12. It then applies the lambda function to the accumulated value and the second value in the se...

How to do Custom Mathematical Operations in an Array in Excel

Using REDUCE function with LAMBDA, we can do the job. The REDUCE function in Excel 365 Dynamic Array is used to reduce a range or array of values to a single value using a specified operation. It is similar to the SUM function, but allows for more complex operations such as finding the maximum or minimum value. Syntax: =REDUCE(array, operation) Here, operation can be one of the following: SUM: Returns the sum of the values in the array. PRODUCT: Returns the product of the values in the array. AVERAGE: Returns the average of the values in the array. MAX: Returns the maximum value in the array. MIN: Returns the minimum value in the array. For example, we have a column with some numbers. If we want to get the sum of only even numbers in one cell and sum of odd numbers in another cell, we can do it by using the REDUCE function: Fig.1: For Even Numbers Fig. 2: For Odd Numbers Note that the REDUCE function can only be used with the Excel 365 Dynamic Array...

How to Generate a Random Array in Excel

The RANDARRAY function in Excel is used to generate an array of random numbers. It takes two arguments: the first argument is the number of rows in the array, and the second argument is the number of columns in the array. Syntax: RANDARRAY(rows, columns, [min], [max], [integer]) Here, Rows: It specifies the number of rows to be included in the array. If this argument is not specified, it defaults to 1. Columns: It specifies the number of columns to be included in the array. If this argument is not specified, it defaults to 1. Min (optional): It specifies the minimum value for the random numbers in the array. If this argument is not specified, it defaults to 0. Max (optional): It specifies the maximum value for the random numbers in the array. If this argument is not specified, it defaults to 1. Integer (optional): It specifies whether the random numbers should be integers or decimals. If this argument is not specified, it defaults to FALSE, which means that decimals will be used....

How to Omit Particular Values from an Array in Excel

A function called ‘MAP’ function is used to omit specific values from an array. The MAP function is available in Excel 365 dynamic array formulas. It allows you to apply a function to each element of an array and return the resulting values in a new array. Syntax: MAP(function, array1, [array2], ...) Here, function is the custom function to apply to each element of the array(s). This argument can be any valid Excel function or a user-defined function (created using the LET function, for example). For example, we have an array of numbers and alphabets mixed. We can remove/omit the alphabets by using the MAP function like this: Note that the MAP function will automatically spill the result into adjacent cells as a dynamic array. If you are using an older version of Excel that does not support dynamic arrays, you can use the INDEX function.

How to Assign a Variable inside Formula in Excel

The LET function is a new feature introduced in Excel 365. It allows you to define and name intermediate calculations within a formula, making it easier to read and maintain complex formulas. It’s syntax is: LET(name1, value1, [name2, value2], ..., expression) The first argument specifies the name of the first calculation, and the second argument is the value or formula used in that calculation. You can have multiple name/value pairs separated by commas to define additional calculations. The last argument is the final expression that uses the intermediate calculations to produce the result. Within the expression, you can refer to the intermediate calculations by their assigned names. Here's an example of using the LET function to calculate the sum of the squares of the first 10 positive integers: In this example, we define the variable "n" to be 10, then use the SEQUENCE function to create a sequence of the first 10 positive integers. We assign this sequence to ...

How to Combine Two or More Rows in Excel

The VStack function in Excel is used to combine multiple arrays or rows into a single combined array. The resulting array or range will have the same number of columns as the original arrays or ranges, and the number of rows will be the sum of the number of rows in the original arrays or ranges. Syntax: VSTACK(array1,[array2],...) For example, let's say you have three ranges A1:E1, A3:E3 and A5:E5 that contain some data. You can combine these three rows like this: The resulting range will be a 5x3 range, with the values from A1:E1, A3:E3 and A5:E5. Note that the VStack function is available only in Excel 365 and later versions. In earlier versions of Excel, this functionality can be achieved using the TRANSPOSE and HSTACK functions.

How to Combine Two or More Columns in Excel

The HSTACK function in Excel is used to combine two or more columns by stacking them next to each other. It is available as part of Excel's dynamic array formula feature and can be used to dynamically create a new array by stacking multiple ranges horizontally. Syntax: HSTACK(range1, range2, ...) For example, let's say you have three ranges A1:A5, C1:C5 and E1:E5 that contain some data. You can combine these three columns like this: Here, the HSTACK function has combined the ranges horizontally to create a new array that contains all the data from the three ranges. The resulting array has three columns (three from each range) and five rows.

How to Omit Some Data in Excel

The FILTER function in Excel is a dynamic array function that allows you to filter a range of data based on one or more criteria and return a filtered array that matches the criteria. The function can be used to filter both rows and columns. Syntax: FILTER(array, include, [if_empty]) where: · include: The criteria or conditions to be used for filtering. · if_empty: [Optional] The value to return if the filtered array is empty. Here are some examples of how to use the FILTER function: 1. Filter rows based on one condition: This formula filters the range A1:E5 based on the condition that values in column E are smaller than 50 and returns the filtered array. 2. Filter rows based on multiple conditions: This formula filters the range A1:E5 based on the conditions that values in column E are smaller than 50 and values in column B, C, D are greater...

How to Change Columns into Rows and Vice-versa in Excel

The TRANSPOSE function in Excel is used to switch the orientation of a range of cells or an array. It converts rows into columns and columns into rows. This can be helpful in situations where you want to perform operations on rows as columns and vice versa. The syntax for the TRANSPOSE function is: TRANSPOSE(array) For example, you have a range of cells A1:E3 that contains some data. You can use the TRANSPOSE function to switch the rows and columns by typing the following formula in a cell: The transposed data is returned. Note that the TRANSPOSE function must be entered as an array formula, which means you need to press Ctrl + Shift + Enter instead of just Enter after typing the formula in the formula bar. Alternatively, you can use the new dynamic array feature in Excel 365 to automatically spill the transposed data into multiple cells.

How to Expand Columns and Rows in Excel

The EXPAND function is a new dynamic array function introduced in Excel 365. It is used to automatically expand an array formula output range based on the number of rows and columns returned by the formula. Syntax: EXPAND(array, [rows], [columns], [pad_with]) Here, · array: The array or range of cells to drop rows or columns from. · rows: The final number of rows after being added · columns: The final number of columns after being added · pad_with: Value to use for new cells (optional) Suppose you have a table of data in cells A1:E5, and you want to add two more columns and last two rows. You can use the following formula: This formula will return a new array with two rows and two columns added with the original range. For matrix calculation in Excel, this function is us...

How to Remove Specific Columns and Rows from a Large Array in Excel

In Excel 365 with dynamic arrays, the "DROP" function is used to remove specified rows or columns from a range or array. It returns a new array with the specified rows or columns removed. Syntax: DROP(array, num, [type]) Here, · array: The array or range of cells to drop rows or columns from. · num: A number or an array of numbers indicating the row or column index to drop. If type is not specified, num specifies the row index to drop. If type is set to 1, num specifies the column index to drop. · [type]: Optional. A logical value that determines whether to drop rows or columns. Set to 0 or omitted to drop rows, set to 1 to drop columns. Suppose you have a table of data in cells A1:E5, and you want to remove column A, and last two rows. You can use the following formula: This formula will return a new array that excludes the last two rows and first colum...

How to Extract Specific Columns from a Large Array in Excel

The CHOOSECOLS function is a dynamic array function introduced in Excel 365. It allows you to select specific columns from a given range or array and returns a new array with the selected columns. Syntax: CHOOSECOLS(array, col_index1, [col_index2], ...) Here, array is the range or array from which you want to select columns and “col_index1, [col_index2], …” are the column indices you want to select from the array. You can enter up to 252 indices. Here's an example usage of the function. Suppose you have a table of data in cells A1:E5, and you want to extract columns A, B, and E. You can use the following formula: This will return a new dynamic array with columns A, B, and E of the original table.

How to Extract Specific Rows from a Large Array in Excel