Microsoft Excel
continues to introduce new functions that make work easier, faster, and more
intelligent. These new functions help you manage large data sets, clean your
information, perform advanced calculations, and automate complex steps. Many
users find older Excel functions difficult because they require long formulas.
However, these new Excel functions are designed to make tasks simple for
everyone, even beginners.
This tutorial
explains the 10 latest Excel upgrade functions using very easy wording. Each
function includes a clear definition, sample data, purpose, and example
formula. Every section is written in complete sentences without short forms,
ensuring that beginners can understand everything without confusion. By learning
these functions, you will improve your Excel skills and handle your data with
more confidence.
Let us begin
learning each function step by step.
Table of Contents
1. BYCOL Function
Definition
The BYCOL
function runs a calculation on each column of a selected range. It examines one
column at a time and returns one result for each column. This function is
helpful when you want to apply the same calculation across different columns
without writing separate formulas for each one.
Sample Data
Purpose
This function helps you calculate totals, averages, maximums, or
any other summary for each column in a very easy way.
Example: Calculate total of each column.
Formula: =BYCOL(B2:D4, LAMBDA(x, SUM(x)))
Explanation
- The function looks at each column inside B2 to D4.
- It picks column Q1 first, sums all values, and gives the result.
- Then it moves to Q2, performs the same calculation, and continues until all columns are completed.
- The result is a clean list of totals for Q1, Q2, and Q3.
2.
BYROW Function
Definition
The BYROW
function performs a calculation across each row. It examines one row at a time
and gives a result for that row. This function prevents you from writing
separate formulas for each row.
Sample Data
Purpose
You can use this function to calculate total marks, averages, or
any other result for each student or each record.
Example: Total score per student.
Formula: =BYROW(B2:C4, LAMBDA(r, SUM(r)))
Explanation
- The function examines the first row of marks for Ali and adds them.
- Then it moves to Sara and calculates her total marks.
- Finally, it does the same for Zain.
- It automatically returns one result for each row.
3.
CHOOSECOLS Function
Definition
The CHOOSECOLS
function selects specific columns from a dataset. It allows you to extract only
the columns you want and ignore the rest.
Sample Data
Purpose: Select Columns Name and Salary
This function
is perfect when you want to create a smaller table from a larger one.
For example, if you want only the Name and Salary columns.
Example
Formula: =CHOOSECOLS(A2:D3, 2, 4)
Explanation
- The function copies only column 2 (Name) and column 4 (Salary).
- It ignores the ID and City columns.
- The result is a clean two-column table.
4.
CHOOSEROWS Function
Definition
The CHOOSEROWS
function extracts only selected rows. You tell Excel which row numbers you
want, and it returns them in order.
Sample Data
Purpose
This function
is useful when you want to show only the first, last, or specific rows from a
dataset.
Example: Picks the rows 1 and 3
Formula: =CHOOSEROWS(A2:B4, 1, 3)
Explanation
- The function picks row 1 (Pen) and row 3 (Bag).
- It leaves out row 2 (Book).
- The result is a smaller list with only the selected rows.
5.
DROP Function
Definition
The DROP
function removes a specific number of rows or columns from a dataset. You can
remove rows from the top or columns from the left.
Sample Data
Purpose
This function
is used when you want to clean your data by removing unnecessary rows or
headers.
Example: Drop First Row
Formula: =DROP(A2:C4, 1)
Explanation
- This formula removes the first row from the dataset.
- The result starts from the second row (Sara) and continues down.
6.
EXPAND Function
Definition
The EXPAND
function increases the size of a range to a required number of rows or columns.
You specify the new size, and Excel fills empty spaces automatically.
Sample Data
Purpose
It is useful when you need a fixed-size range, especially for
reports or charts.
Example: Expand the lists to 5 rows
Formula: =EXPAND(A2:A3, 5, 1)
Explanation
- The original range has 2 values.
- The function expands it to 5 rows.
- The extra rows are filled with empty cells.
7.
HSTACK Function
Definition
The HSTACK
function stacks two or more ranges side-by-side in a horizontal direction. It
joins multiple tables into one wider table.
Sample Data
Example: Joins both Tables Horizontally
Formula: =HSTACK(A2:B2, E2:F2)
Explanation
- The function joins both tables horizontally.
1 2 3 4
8.
MAKEARRAY Function
Definition
The MAKEARRAY
function creates a new array (table) by using a formula. You decide how many
rows and columns the table should have, and the function fills each cell using
a calculation.
Example: Create a 3×3 multiplication table:
Formula: =MAKEARRAY(3, 3, LAMBDA(r, c, r * c))
Explanation
- The function fills the table using the multiplication of row number and column number.
1 2 3
2 4 6
3 6 9
This function is extremely powerful for generating automated patterns and grids.
9.
SCAN Function
Definition
The SCAN
function returns running or progressive results after each step. Instead of
giving one final outcome, it shows the result after each calculation in the
sequence.
Sample Data
Purpose
This function is used to create running totals or running averages.
Example: Running Total
Formula: =SCAN(0, A2:A4, LAMBDA(total, x, total + x))
Explanation
- The function starts with a total of 0.
After 10 → 10
After 20 → 30
After 30 → 60
The result becomes: 10, 30, 60
10.
TAKE Function
Definition
The TAKE
function returns only the top or bottom rows or columns from a range. You can
specify how many rows you want to keep.
Sample Data
Purpose
This function is great for creating summary reports or showing only
the top-performing items.
Example: Take Top 2 Rows
Formula: =TAKE(A2:B4, 2)
Explanation
- The formula keeps only the top two rows.
- It shows Product A and Product B and their sales.
Conclusion
These 10 latest
Excel functions make your work extremely simple and powerful. They replace long
formulas, reduce manual work, and help you clean and organize data more
efficiently. They also prepare you for modern Excel skills that are required
today in jobs such as data analyst, accountant, office administrator, teacher,
or business owner.
By practicing these functions, you will learn how to:
- Summarize data in rows and columns
- Extract important information
- Clean tables
- Create dynamic reports
- Generate automated tables
- Perform advanced calculations easily
0 Comments