Joshua earned an MBA from USF and writes mostly about software and technology.
The TAKE function was added to Excel in the spring of 2022. You can now use this tool to duplicate a table or array of data in your workbooks. The formula only requires three arguments to complete the duplication in the following syntax:
A description of each argument is listed here below:
- Array- The Array is the area that needs to be duplicated.
- Rows- This is the number of rows that will display.
- Columns- This is the number of columns that will display.
See how to use the TAKE function in the example below where I duplicate a table and add formatting.
In this example, I want to duplicate the whole table above and have it displayed in cell I2 to the right of the table. To start off I first type the being of the function formula.
This is followed by selecting the range of cells to be duplicated followed by a comma.
Next, the number of rows and columns must be indicated with a comma to separate these arguments. The formula is ended with a closing parenthesis.
After the formula is entered, the data from the selected range will appear without formatting. Note that if you duplicate cells that don't have data a zero will appear in those cells.
This data will change and update immediately when the parent table updates. What doesn't update is the formatting of the duplicated table. The good news is that you can copy the formatting from the original table with the format painter.
To copy the formatting from the original table select the table range of the original table and click on the format painter button.
Select the whole range of the duplicated table and the formatting will transfer.
See the results below of the child table created by the take function and original table range.
While this example is a simplified case, the TAKE function can be used for a variety of reasons. Suppose that you have a report that is repeated throughout a workbook in multiple sheets. Instead of using macros or a large number of formulas to update the tables, it may be simpler to use the TAKE function to do the same task with one formula.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2022 Joshua Crowder