10 AMAZING
EXCEL
Finance
Business
Partnering
ARRAY FUNCTIONS Institue
Introduction
Array functions in Excel are powerful tools that allow users to manipulate data more efficiently and
effectively. Functions like VSTACK and HSTACK enable users to combine data vertically or horizontally,
making it easier to organize large datasets. With TOCOL and TOROW, users can transform arrays into a
single column or row, perfect for reformatting data for analysis. The TAKE and DROP functions allow for
quick extraction or removal of specific rows or columns from arrays, while TEXTSPLIT simplifies breaking
down text into arrays based on delimiters. Additionally, CHOOSECOLS and CHOOSEROWS offer
flexibility by enabling users to select specific rows or columns, and UNIQUE makes deduplication
seamless by returning only distinct values from an array. Together, these functions streamline data
handling, providing users with greater control and precision in Excel.
1. VSTACK: Combines multiple arrays vertically by stacking them on top of each other.
2. HSTACK: Merges multiple arrays horizontally by placing them side by side.
3. TOCOL: Converts a range or array into a single column.
4. TOROW: Converts a range or array into a single row.
5. TAKE: Extracts a specific number of rows or columns from an array starting from the top or left.
6. DROP: Removes a specific number of rows or columns from the top or left of an array.
7. TEXTSPLIT: Splits text into an array based on a specified delimiter.
8. CHOOSECOLS: Selects and returns specified columns from an array.
9. CHOOSEROWS: Selects and returns specified rows from an array.
10. UNIQUE: Returns a list of unique values from a range or array, removing duplicates.
VSTACK
VSTACK returns the array formed by appending each of the array arguments in a
row-wise fashion.
=VSTACK(array1,[array2],...)
HSTACK
HSTACK returns the array formed by appending each of the array arguments in a
column-wise fashion.
=HSTACK(array1,[array2],...)
TOCOL
Returns the array in a single column.
=TOCOL(array, [ignore], [scan_by_column])
TOROW
Returns the array in a single row.
=TOROW(array, [ignore], [scan_by_column])
TAKE
Returns a specified number of contiguous rows or columns from the start or end
of an array.
=TAKE(array, rows,[columns])
DROP
Excludes a specified number of rows or columns from the start or end of an
array. You might find this function useful to remove headers and footers in an
Excel report to return only the data.
=DROP(array, rows,[columns])
TEXTSPLIT
Splits text strings by using column and row delimiters.
=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_e
mpty], [match_mode], [pad_with])
CHOOSECOLS
Returns the specified columns from an array.
=CHOOSECOLS(array,col_num1,[col_num2],…)
CHOOSEROWS
Returns the specified rows from an array.
=CHOOSEROWS(array,row_num1,[row_num2],…)
UNIQUE
Returns a list of unique values in a list or range.
=UNIQUE(array, [by_col], [exactly_once])
With SORT Function
These functions are only
available in Excel for
Microsoft 365 (Windows and
Mac) and Excel for the web.
Know Your Coach
Hi, I’m Syed Nadeem a seasoned Strategic Finance Leader with
over 20 years of cross-industry experience, I specialize in guiding
finance professionals through every career stage.
My Coaching Philosophy: Mindset Matters
I firmly believe in the power of mindset, attributing 90% of career
success to it. My coaching extends beyond traditional finance
training, focusing on nurturing leadership and communication
skills essential for the dynamic financial sector.
Strategic Business Partnering: Creating Value
I excel in aligning financial strategies with business objectives,
leveraging my experience to offer invaluable insights for effective
decision-making. My approach is rooted in value creation
through strategic business partnerships a little bit truth honest
Enroll Now and Become a Strategic Finance Leader
