The SORT function is an essential function when dealing with larger datasets as it simplifies the data by sorting it with defined parameters. New to the stage, the SORT function cannot be found in versions earlier than Excel 2021.

Syntax

The syntax of the SORT function contains four arguments that ensure that we can rearrange the given dataset as per the specified criteria. The syntax is as follows.

Arguments:

Details of the arguments and their roles are mentioned below.

'array' – This is a mandatory argument that accepts the value of the cell range or array that we wish to sort. The value of the array argument can be a static array, cell reference, or the return value of a function.

'sort_index' – This is an optional argument that indicates the column number or row number that must be used as a basis for sorting the data. If left empty, the default value of the argument is 1 which means the SORT function will sort by the first column or row depending on the input value of the by_col

'sort_order' – This is another optional argument that determines the order in which the data should be sorted. When left empty, the value defaults to 1 which rearranges the data in ascending order. When the value is set to -1, the data is sorted in descending order.

'by_col' – This is the last optional argument that accepts Boolean input values. This argument decides whether we want to sort by columns or by rows. By default, by_col is set to FALSE, which means sorting by rows. To rearrange the input data horizontally by columns, the value of the by_col argument should be set to TRUE.

Important Characteristics of the SORT Function

The most prominent characteristic of the SORT function is that it is dynamic in nature, which means when the data is altered in the input array, the same is reflected in the return data.

Some other noteworthy features of the SORT function are as follows.

• When you use the SORT function, it maintains the relationship between data in different columns or rows, ensuring that data stays correctly aligned even after reordering.
• If the value of the sort_index argument is out of range, the SORT function returns a #VALUE error.
• The sort_order argument only accepts 1 or -1 as the input value. If we enter any other value, the function throws a #VALUE error.
• Only omission or FALSE setting of the by_col argument results in the function sorting by row.

Let's go through some practical examples of the SORT function to learn how it helps us organize and rearrange data.

Examples of SORT Function

We know that the utility of the SORT function is to rearrange data based on columns. Let's delve into a practical example using a sample dataset to further grasp its functionality. Here we have a simple dataset containing names and corresponding scores.

The first instance is a default view of the SORT function where we wish to rearrange the array B2:C5. As the other parameters are left empty, each argument assumes its default value and reorders the data as per the first column in ascending order, sorting by row. We finally have the dataset arranged in alphabetical order of the names.

The next example shows the context of other arguments. The formula takes the data in the cell range B2:C5, sorts it based on the values in the second column (column C) in descending order, and returns the sorted dataset. We finally have the data indicating top scorers to lowest.

With a foundational understanding of the function in place, let's progress to explore different methods and applications to make the most of it.

Example 1 – Simple Use of SORT Function

Imagine you are a teacher and it's the end of term. You now have all the students' grades in a dataset that includes their ID, names, and marks scored in three subjects. You now wish to do basic analysis such as finding out the top-performing student or the lowest marks secured in a subject.

This is a classic use case of the SORT function. The goal is to rearrange the dataset as per marks scored in Maths. The student with the highest marks in Maths must be on top. The formula used will be as follows.

The formula aims to arrange the data in the array A1:E11 based on the marks obtained in maths, indicated by 3 indicating the column number, in descending order.

Now that we know who scored the highest marks in maths, let's move on to finding the lowest marks scored in science. We can again use the SORT function and change the input value of the sort_order argument to 1 indicating the ascending order.

As we wish to sort the dataset based on science marks (Column D), the formula used will be as follows.

Now that we've learned how to rearrange a dataset using a single column, it's important to recognize that when working with extensive data sets, we often require multi-level sorting. In the next example, let's explore how to use the SORT function to rearrange the dataset by multiple columns in a different order.

Example 2 – Multi-level Sorting Using SORT Function

Imagine a scenario where you are managing a database of job applicants for a company's recruitment process. The database contains information about applicants, including their names, years of experience, and the department they are applying for.

Now, you want to sort the applicants first by their department in ascending order (alphabetical order) and then, within each department, sort them by years of experience in descending order (from most experienced to least experienced).

So, to ensure that we are including more than one column, we will use array constants for the input values of the sort_index and sort_order argument. The formula used will be as follows.

The first value in the array is 3 as column C contains the department they are applying for which will be sorted first. The sorting order for column C is defined in the third argument's array as 1, indicating ascending order. Next to be sorted is column B (given as 2 in the first array) – the years of experience, which is to be sorted in descending order (carried out by -1 in the third argument.

Now that you have the dataset sorted, you can forward the grouped applicants to the relevant teams.

Example 3 – Dynamic Sorting with SORT Function

Suppose you have downloaded some stock price data to analyze the daily prices of four stocks over a period. The dataset contains rows representing a different stock. Each entry is of the relevant stock's daily closing price.

We wish to reorder the dataset based on the Stock 2 prices in descending order. As the dataset will be sorted by column, we can set the input value of by_col to TRUE. The formula used will be as follows.

While the above solution successfully arranges the Stock2 data in descending order, it lacks the flexibility to accommodate additional data for future days. To ensure new data for the next days are automatically adjusted in a sorted manner, we can convert the dataset into Excel tables.

To do so, select the dataset (A2:F4) and click on 'Format as Table' on the Home tab. Let's name it 'stockprices'. Now the formula used will be as follows.

As we can see, when an additional column was added for the latest date, the returned dataset dynamically accommodates the new values. Therefore, a combination of the Excel table with the SORT function ensures that your table stays up to date as your dataset evolves.

Example 4 – Using SORT Function with FILTER Function

In this example, you have a large sales data which includes month-wise sales of a product along with the sales quantity and revenue. Your aim is to sort the products based on their total revenue along with the flexibility to choose different months to analyze.

We can use the FILTER function to select data for a specific month and then sort the products within that month. Let's take cell G1 where the user can choose the month. The formula used will be as follows.

This will filter the dataset based on the condition that the "Month" column (Column A) should be equal to the value in cell G1. It selects rows where the "Month" matches the value in G1 and returns the corresponding data from columns A to D.

Now we have the data for the intended month, we will sort the data based on the fourth column, which is "Total Revenue," in descending order (-1). The final formula will be used as follows.

We now have the necessary month-wise data sorted in descending order based on revenue. The next day, what if you need to email the top 3 performing products as per the quantity sold? You can do so using the same SORT function combined with the INDEX function. Let's explore how to do that.

Example 4.1 – Using SORT Function with INDEX Function

As we want the top products based on quality sold, we will use the SORT function to reorder the data based on the values in the third column (column C) in descending order (-1). The SORT function returns a sorted array. The formula used will be as follows.

The next step is to use the INDEX function to retrieve the first 3 rows from the sorted dataset. Putting it all together, the formula used will be as follows.

Specifically, it extracts values from three rows (determined by SEQUENCE(3)) and three columns (determined by {2, 3, 4}) in that sorted range.

You can also use this logic to extract a sorted value from a specific position.

We now know that the SORT function in Excel is an indispensable tool for efficient data organization and analysis.

Practice to discover more applications of the said function and how you can include it in your daily work. While you improve your data manipulation skills with the SORT function, we will work on yet another Excel function to further add to your knowledge.

In Excel, you will find the PV function is quite the handy present value calculator. The type and nature of investment will however determine the variables for the PV function. The three broad categories we'll cover for calculating the present value are annuities, perpetuities, and one-time payouts.

Are you confusing present value with net present value? Let's establish that the two are quite far from equal. How you ask? Read below!

PV Vs NPV

Present value is the current value of an investment now with a projected income stream as per the set interest rate. PV is the figure you calculate when you want to compute, for example, the initial amount of investment to be made to achieve a certain target in a given number of years.

Or for computing the amount to be paid now given the interest rate and future payments. The present value calculation assumes fixed interest rates, payments, and intervals between payments. It can also account for different annuity types (end of period or beginning of period payment).

While net present value also signifies a present value, it is indicative of the profitability of an investment. Both PV and NPV incorporate discounted cash flows. What makes NPV a net figure is the adjustment of the initial investment to outline profitability. As opposed to regular cash flow, NPV deals with uneven cash flow. All in all, NPV calculates the present value of net cash flow over a period of time.

Having outlined the distinctions between the two, we can now proceed to explore the methodology for calculating the present value for investments.

Let's get presenting present values!

Method #1 – PV Formula of Single Cash Flow

Let's start with the least variables and a simple investment concept. This medium calculates the present value of a single payment using the PV function in Excel. The PV function returns the present value figure; the amount that future payments are worth now. To explain the following case example, right now we will just focus on a single instance of a future payment instead of multiple instances.

As an example to carry this out, let's say Cal is targeting to gather $4,000 for a project in 2 years and another $1,000 by the third year. He finds a couple of investment options and wants to weigh out how much he must initially invest in either option. In other words, this initial investment will be labeled as the present value, and the target figure as the future value of the investment.

This formula is for calculating the present value of a lump sum:

And here would be the outcome of this formula:

But with Excel, the PV function can do the job. A very simple formula is shown below employing the PV function for the task:

The first argument requires the interest/discount rate which we have entered as C3. The second argument, denoting the number of payment periods is fed as 3 years here. The next argument is left blank (you will see its use in the upcoming section) and finally, the future value is entered as the fourth argument.

Just as the general present value formula would operate, the PV function has computed the present value of the first investment option as $4,081 indicating the set-up amount that this choice will require.

Also, note that the resulting value of the PV function is negative from the point of view of the investor. For the issuer, the future value will be entered as negative, deriving a positive present value figure. Nevertheless, you can still have the final figure positive by starting the formula with a minus sign like so:

Single Cash Flow with Compound Interest

The compounding frequency in the previous example was yearly. If Cal was faced with investment options with more frequent compounding, this is how the present value computation would be altered:

We'll suppose that the options in the example involve monthly and quarterly compounding respectively which we have incorporated in row 4. The two things in the formula that would be affected by compounding frequency are the interest rate and the number of payment periods.

To be converted into a monthly interest rate, 7% will be divided by 12 (as done in the first argument where C3/C4). Also, the number of periods in 3 years with monthly compounding will be 3 times 12 (reflected in the second argument).

In comparison to $4,081 with yearly compounding, monthly compounding requires $26 less to be invested now.

Method #2 – PV Formula of Series of Cash Flows (Annuity)

Next up, we'll calculate the present value of an annuity in Excel, again courtesy of the PV function. An annuity comprises a series of consistent payments made at regular intervals, whether yearly, quarterly, monthly, weekly, etc. You probably didn't know them as annuities, but popular examples include home mortgage and pension payments.

Compared to a single payment, two elements of the PV function will come into play with annuities: the pmt and type arguments. pmt takes the periodic payment and type refers to the type of annuity – ordinary or due.

An ordinary annuity has end-of-the-period payments while annuity-due has beginning-of-the-period payments. The difference the type brings to the valuation of the annuity is that with annuity-due, each payment is compounded for one extra period.

Let's have a show of the Excel effects of this cash flow with the following case example.

Cal's two options are to either receive a lump sum of $1,000 upfront or an annuity that can pay out $200 a year for the next 5 years. To evaluate which pick would be more favorable, Cal needs to calculate the present value of the annuity. Using the PV function, here's how to calculate the present value of an annuity in Excel:

The first two arguments are 7% interest and 5 payment periods. The pmt argument is filled with the payment per period ($200 in this case, supplied as a negative figure showing outflow for Cal). The future value is disregarded here while the next argument confirms the annuity type as regular or due. 0 is mentioned in the first instance but you may leave the cell blank or skip this argument as it would default to 0 anyway. For annuity-due, this argument will have to be filled as 1, like in the second instance.

The present value of annuity-immediate is $820 and that of annuity-due is $877. By comparison, it would be more favorable for Cal to take up the lump sum of $1,000.

Note: As mentioned earlier, annuity payments may be at any regular time intervals (such as yearly, monthly, etc. If it were monthly, let's assume, the calculation will require adjustments with the interest rate and number of payment periods (just like how we saw with compound interest). Following is the adjustment made in the formula for you:

For monthly payouts, rate is divided by 12 and nper is multiplied by 12.

For different intervals, the values would be:

• Biannually: 2
• Quarterly: 4
• Monthly: 12 (as shown above)
• Weekly: 52

Method #3 – PV Formula of Perpetuity

Now that you are familiar with annuities, we can transition into the how and what of perpetuities. An annuity with an indefinite number of payment periods is a perpetuity. In essence, the present value of a perpetuity is the present value of the future cash flows (no principal involved).

While there are some examples of past and present perpetuities (e.g. perpetual bonds, Gordon Growth model for stock valuation), they are rare. But even so, here's a simple example to compute the present value of a perpetuity in Excel.

For evaluating the price of two different perpetual bonds, we do not need to use the PV function. Instead comes the simple formula:

The coupon amount is divided by the discount rate and that results in the present value of the perpetuity. Since the payments are infinite, there is no consideration of the number of payment periods.

You could be questioning how we can assess the present value of perpetuities if the payouts are indefinite. That is because as per the time value of money, payments received way ahead in the future have dwindling and very low value enough to be defined in the present.

With that, we have concluded our chapter on calculating present value in Excel. Unlock more Excel chapters by reconnecting with us. We've got a book-load!

The MATCH function proves especially invaluable when handling large datasets, as it enables swift and efficient identification of relative data locations, sparing the need to manually scour the entire dataset for the desired information.

Syntax

The syntax of the MATCH function contains three arguments out of which one is an optional argument.

Arguments:

'lookup_value' – This is a mandatory argument that accepts the value we want to find within a given cell range or array. The input data in the lookup_value argument can be a number, text, or logical value. It can also be a cell reference containing the data.

'lookup_array ' – This is also a required argument as it contains the cell range or array in which we want to search for the lookup_value. The value of the lookup_array must be a one-dimensional array either vertical or horizontal. It is important to note that the lookup_array must be sorted in ascending or descending order for the MATCH function to work correctly with certain match_type.

'match_type' – This is an optional argument that determines which type of match we wish to perform. The acceptable input values for the argument are -1, 0, or 1 and when left empty, the default value for this argument is 1. When the value is set to 1, the MATCH function locates the largest value that is less than or equal to the lookup_value.

The lookup_array should be sorted in ascending order. If set to 0, it indicates that we are looking for an exact match. If it is set to -1, the function searches for the smallest value that is greater than or equal to the lookup_value. The lookup_array must be sorted in descending order.

Now that we know all the possibilities of using the above-mentioned arguments, let's look at a few characteristics of the MATCH function before we dive into some exciting applications and examples.

Important Characteristics of the MATCH Function

One of the most notable features of the MATCH function is that it is not case-sensitive. Some of the other aspects of the function that are noteworthy are as follows.

• When the MATCH function cannot locate the value, we are looking for, it returns a #N/A error.
• One limitation of the MATCH function is that it only works with text up to 255 characters in length.
• In case of a duplicate or more than one occurrence of the lookup_value in the data, the MATCH function returns the first match.
• When match_type is set to 0 and the data in the lookup_value argument is a text string, we can also use wildcard characters such as the question mark (?) and the asterisk (*). The question marks represent any single character while the asterisk is used to match any sequence of characters before or after. If you wish to locate an actual question mark or asterisk, simply precede it with a tilde (~).

Examples of MATCH Function

Let's begin with understanding all the arguments and how they contribute to the MATCH function. Here we will take a sample dataset and use different input values for each argument allowing us to gain deeper insights into the function's workings.

In the first two examples, the input value of the match_type is set to 1. In the first case, we are looking for the value '110' in the array B1:F1. As the lookup_value is not in the dataset and the match_type is set to 1, the MATCH formula looks for the largest value that is less than 110 in the array. As a result, the function returns 5 as the array is sorted in ascending order.

In the next instance, we are looking for the value '100' and the remaining argument values remain the same. As a result, the MATCH function returns a  #N/A error as there is no value that is equal to or is less than 100.

The third example is an ideal case scenario, where we are looking for an exact match for the value '103' in the array. The MATCH function locates the same and returns the position of the lookup_value.

In the last two examples, we are exploring the behavior of the MATCH function where the value of the match_type is set to -1. In such scenarios, the MATCH function identifies the smallest value that is equal to or greater than the lookup_value. So, we can see that when we are looking for '1000', the function returns an error as there is no data bigger than 1000 in the dataset. On the other hand, when the lookup_value is 990, the MATCH function returns 5 for '995' which is the smallest value that is greater than '990'.

Example 1 – Simple Use of MATCH Function

Suppose you own a furniture store that sells various products such as bookcases, sofas, etc. You now have the dataset which contains details such as product IDs, product names, and total sales for each product to date.

You now wish to determine the ranking of the product based on their sales using the product IDs.

We can take cell E1 where the user can enter the product ID to look for. We can then use the MATCH function to determine the position or row number of the product ID. As we are looking for an exact match, the formula used will be as follows.

This is extremely useful when the dataset is large. As the current dataset is sorted from highest sales to lowest, the result obtained from the MATCH function also provides us with the product's ranking or popularity based on sales.

Furthermore, it is evident that the product IDs are crafted in a way where the initial three letters of the product ID align with the initial three letters of the product name. In case the user does not remember the entire product ID, we can take advantage of the wildcard characters supported by the MATCH function. Next, we'll show you how to do that.

Example 2 – Using Wildcard with MATCH Function

To simplify the user experience, we can ask them to just enter the initial three letters of the product name. Subsequently, we modify the lookup_value argument, where we combine the user-entered first three letters of the product with the wildcard character '*'. This means that the MATCH function will look for a value that begins with the user-entered text followed by any number of characters. The formula will be as follows.

The asterisk has served as a buffer for the remaining characters of the product code "SOF234PQR" beyond the characters entered in E1. As observed in the previous example and stated in the characteristics, the MATCH function is case-insensitive. In the next example, let's explore the possibility of using the MATCH function in a case-sensitive manner.

Example 3 – Making MATCH Function Case-sensitive

Suppose you are a marketing manager planning a fresh marketing campaign for your company, and you wish to collaborate with social media influencers to promote the campaign. You have a dataset that includes details of potential influencers, such as their name, username, and the number of followers they have on social media.

To make it easy to filter out, you now wish to identify the position of the influencer based on their username.

As we can see, the usernames contain both uppercase and lowercase letters, making it clear that relying solely on the MATCH function would be insufficient. Instead, we will use a combination of the EXACT function with the MATCH function.

The EXACT function will search for an exact case-sensitive match of the lookup_value. If it finds a match, the EXACT function returns TRUE, else it returns FALSE. The formula used will be as follows.

The EXACT function returned an array of TRUE and FALSE indicating where it found a perfect case-sensitive match. Now, we can use the MATCH function to look for "TRUE" in the previous array. The formula used will be as follows.

So, together, the EXACT function helps us figure out if there is a perfect match, and the MATCH function helps us find where that perfect match is in a list.

There have likely been multiple occasions when you've had to compare two columns to determine matches and differences. Employing the MATCH function is one effective method for accomplishing this task. Let's learn how to use it in the next example.

Example 4 – Comparing Two Lists Using MATCH Function

In this scenario, we have an upcoming technical training program that is open to all teams in the company. We now have two lists of email addresses, the first list contains the email addresses of the technical team, and List 2 contains a list of email addresses of employees who have been invited to a special training program.

The goal is to determine which email addresses from the training program list are a part of the tech team and which ones are not.

First, the MATCH function compares each cell in column B with all the data in column A. The formula used is as follows.

We drag and drop the formula till the end of the dataset.

The next step is to check if the return value of the MATCH function is an error (specifically, a #N/A error). If there is an error, the ISNA function returns TRUE; otherwise, it returns FALSE. The formula will be as follows.

The last step is the simplest where we enclose the above formula in a basic IF function. It checks the return value of the ISNA and MATCH functions. If the value is TRUE, meaning the email address in column B was not found in column A), it displays "Not in Tech Team"; otherwise, it displays "Member of the Tech Team." The complete formula will be as follows.

In case, the columns to be compared contain case-sensitive values, combine the logic mentioned in the previous example to improve this formula.

Example 5 – Using VLOOKUP with MATCH Function

Suppose you work in the HR department, and you have an Excel dataset containing information about employees, including their names, departments, and salaries. Now, you want to retrieve an employee's salary based on their name.

The easiest and the most preferred option is to use the VLOOKUP function. The formula used will be as follows.

Problem solved. Now, due to some reasons, you had to delete Column B containing the details about 'Department'. Upon deleting the column, the above formula adjusted the value of the table_array but returned the value from the third column, which is 'Joining Date'.

This is a limitation of the VLOOKUP formula where if we add or delete columns, the function returns incorrect data. In this case, if we delete column C as well, the function will return a #REF! error. To avoid this issue, we can use the MATCH function to dynamically determine the column index for the VLOOKUP function.

For instance, in this example, we are looking for values in the column named 'Salary (USD)'. Using the MATCH function, we can dynamically determine the column index by finding the position of "Salary (USD)" in the first row (row 1) of the data. This ensures that if we add or remove columns, the formula will still work correctly. To find the column number, the formula used will be as follows.

Now, we can use this in the above-mentioned VLOOKUP function. The formula will be as follows.

Now, even if we add or delete columns in the data table, the formula will still correctly retrieve the salary because it uses the MATCH function to determine the column index dynamically based on the column header 'Salary (USD)'. The same logic can be applied while using the HLOOKUP function.

Alternatively, we can fix the column again using the MATCH function but use the INDEX function to return the match against the lookup value. The MATCH and INDEX functions are often used as a duo and now you'll see why. Use the formula below as a base to retrieve data relevant to the lookup value without facing trouble from dynamic changes:

Since the functions aren't restricted to retrieving data from the 3rd column, the result won't be subject to fluctuations with changes in the dataset. The MATCH function locates an exact match of the value in G1 in A2:A11 as 2. The INDEX function takes that position and returns the corresponding value from C2:C11 which is 55000.

Practice more yourself to discover how the MATCH function fits into your work and projects. While you uncover more exciting applications, we will work on yet another Excel function to add to your repository. Stay tuned for an even broader range of Excel tools at your fingertips.

Students, here's a little tip for you. Instead of punching figures over and over in a calculator, you can enter all your problems (please keep them numerical) on a spreadsheet and have expressions with powers calculated in no time for multiple instances.

So, you could be one of such students immersed in math, a professor creating Q&A for your valuable (albeit frustrating) math book, a doctor in the making first trying to make head and tail of scientific research, or a side hustler deciding on financial investments. Either way, you are here to get conclusions or make informed decisions based on this simple mathematical model and we'll show you how Excel factors in.

Exponents, thou rise to power!

Method #1 – Using POWER Function

The first method covers using exponents in Excel with the POWER function. The POWER function raises a number to a power and returns the result. It is as simple as that, so we'll take a look at a sample formula with the POWER function to calculate the value of a number raised to a power:

The POWER function takes a number each for a base and an exponent. Column B contains a list of numbers we will be using as the base for the exponents while column C has all the respective exponents.

The number 4 in B3 is raised to the power 5 and so the calculation results in 1024. The remaining results of the POWER function can be seen below:

That is how to use the POWER function on a basic level. Now we'll show you how to incorporate the POWER function into more of a complex calculation, part of which requires raising a value to another specified value. Let's say that, as an example, we are calculating the future value of investments with the formula:

The number of time periods is the required exponent here and we can place the POWER function in the formula to raise (1 + r) to the power n like so:

The POWER function takes the base (1 + r) as (1 + C3/12) which is the annual interest rate divided by 12 months for monthly compounding frequency. "n" which is the exponent is set as (D3*12) which is 5 years times the compounding frequency in a year (i.e. 12 again). The POWER function does the work here and the result is then multiplied by the present value in B3 to return the future value as $4253.

Method #2 – Using Caret Symbol

Use the caret symbol (^) to apply exponents in Excel. Caret is a quick and simple way of raising a numerical base to a power where simply the base and the exponent are separated by a caret readily available on your keyboard. Our example case below will illustrate the usage of the caret in applying an exponent to a base and returning the relevant result in Excel. Starting with the following formula:

The number 4 in B3 makes the base for the exponent in C3 which is 5. The base is followed by the caret and the exponent, with the result returned as 1024.

The caret symbol can just as easily be part of an intricate formula where you need to use exponents. Drawing from the earlier case example, let's use the caret this time to raise a component in the calculation of the future values of investments to the power of a defined time period:

Again, we take the base of (1+C3/12) and raise it to the power (D3*12) using the caret. This expression equals 1.417 which is multiplied by B3 (i.e. 3000) to land at the future value of $4253 in 5 years with the interest compounded monthly.

Method #3 – Using Format Cells Option

With the Format Cells settings, you can add exponents in Excel. This method does not calculate the base and its power. It does not even change the actual value in the cell. Format Cells will only alter the formatting of a cell without performing a calculation or changing the value. Only Format Cells can display an exponent as a superscript. The steps given ahead highlight how to use Format Cells to display an exponent in a cell in Excel:

• Enter in the target cell the value for the base along with the exponent. E.g. for arriving at "E = mc²", we will enter "E = mc2" as shown below:
• 
• Change the format of the cell to the Text format by clicking on the Number Format bar and selecting the Text
• 
• Edit the target cell (in the Formula Bar or by double-clicking the cell itself) and select the value you want to display as an exponent. In the given case example, we have selected 2 to display it as a square.
• 
• Right-click the cell and select the Format Cells option or use the Ctrl + 1 keys to open the Format Cells dialog box.
• 
• Mark the Superscript checkbox in the Effects
• The Preview box will show you the top alignment and decrease in the appearance of the font of the sample text.
• 
• Hit the OK button in the dialog box. The value selected in the cell will be superscripted.
• Press the Enter key to apply the format change.

Your choice of text will show as an exponent:

Notes:

The format of a cell requires a change to the Text format when superscripting part of a cell's value. If the full contents of the cell are to be in superscript, there is no need to switch the format of the cell first.

Any value can be superscripted: numeric, alphabetic, or alphanumeric.

Those were all the methods of doing work the exponent way in Excel. Now you can easily and quickly raise any number or numeric calculation to a power to arrive at the computed result or simply superscript any value in place. While you're powering on with powers, we'll power up another handy guide to exponentially raise your Excel skills.

The LOOKUP function offers two forms which are vector form and array form. The details of each are discussed further in the article. Beyond its functionality to look for an exact match, the function also offers to look for an approximate match.

Syntax

The LOOKUP function encompasses two different syntaxes and each one of them provides a distinct functionality. Depending on the nature of our data retrieval needs, we can opt for the syntax that provides greater flexibility in addressing different lookup scenarios.

The first one is called the vector form which accepts and deals with single array data. The syntax of the vector form of the LOOKUP function is as follows.

Arguments:

The vector form of the LOOKUP function is primarily used for simple one-dimensional lookups where we have separate columns/rows for lookup and result values. It accepts three arguments out of which two are mandatory.

'lookup_value' – This is a mandatory argument that accepts the value that we are looking for. The data in the lookup_value can be a variety of data types, including numbers, text, logical values, as well as names or references that point to specific values.

'lookup_vector' – This is also a mandatory argument as it contains the cell range or array where the LOOKUP function searches the lookup_value. Usually, the lookup_vector is a one-row or one-column cell range. However, if the input value includes more than one row or column, the LOOKUP function still considers the first row or column.

'result_vector' – This is an optional argument where the input value is a cell range which is ideally a single row or column range. As it is an optional argument, when it is left blank, the LOOKUP function returns the matched value found in When result_vector is defined, the function locates the match in lookup_vector and returns the corresponding value from the result_vector.

The second form of the LOOKUP function is called the array form. In this variant, the LOOKUP function finds the given value in the first column or row of the array and returns the corresponding value from the last column or row of the array.

The syntax of the array form of the LOOKUP function is as follows.

Arguments:

This form only accepts two arguments, and both are mandatory. The details of each are mentioned below.

'lookup_value' – This argument accepts the value that we wish to search for in the array.

'array ' – This includes the value of the array or cell range where we wish to search the lookup_value.

Note: One essential point to note about the lookup_vector and array arguments is that the LOOKUP function assumes that the data in the given arguments is arranged in ascending order. In the case of the array argument, if there is more than one row or column, the first column or row of the array must be sorted in ascending order.

Important Characteristics of the LOOKUP Function

One of the basic characteristics of the LOOKUP function is that it is case-insensitive. Other noteworthy features of the LOOKUP function are as follows.

• When the value in the lookup_value argument is smaller than the smallest value in the lookup_vector or first row or column of the array, the LOOKUP function throws a #N/A error.
• In another scenario, if the LOOKUP function cannot find the given lookup_value, it returns the closest smaller value within the array or lookup_vector.
• When using the array form of the LOOKUP function, if the array argument contains more rows than columns or an equal number of rows and columns, the function searches for the desired value in the first column. This essentially means that the LOOKUP function behaves like the HLOOKUP function.
• On the other hand, if the array argument includes more columns than rows, the function looks for the desired data in the first row which is a characteristic of the VLOOKUP function.

Although the LOOKUP function imitates features of the HLOOKUP and VLOOKUP functions, the latter two allow us to perform indexing both vertically and horizontally, while LOOKUP only returns the value from the last row or column.

Examples of LOOKUP Function

Let's start by using diverse inputs for the arguments of the LOOKUP function. This will help us grasp its fundamental functionality and both of its forms. Here we have taken a sample dataset that contains employee ID numbers, names, along with their ages (in years).

Let's take cell E1 which contains the data we are looking up. So, in this case, we are looking up Employee ID 105.

The first instance shows the vector form of the LOOKUP function when the optional argument result_vector is left empty. As we are looking for '105' in the cell range A2:A12, the function simply returns the matched value.

In the next case, the value of result_vector is mentioned. The LOOKUP function finds '105' in the given cell range and returns the corresponding value from cell range B2:B12 as "Daniel Brown".

The last instance showcases the array form of the LOOKUP function where it looks for '105' in the first column and returns the corresponding value from the last column.

Example 1 – Simple Use of LOOKUP Function

Imagine you are in the market for a new phone, and you have downloaded a price list of the latest models in an Excel spreadsheet. Your aim now is to explore the price details of the different models you are considering. This way, you can check if it fits your budget.

This is a classic case of the LOOKUP function which will allow you to search for a particular mobile phone model and retrieve its corresponding price. The formula used will be as follows.

The user can enter the lookup_value in cell E1. For instance, if we wish to check the price of "Samsung Galaxy", we will enter the same in cell E1. The LOOKUP function scans the cell range $A$2:$A$11 which is the Mobile Model column to find the exact match. Once the match is found, it will return the corresponding price (500 USD) for the model.

Now you can keep changing the model name in cell E2 and quickly retrieve the price. This will help you in your decision-making. Now, consider a scenario where you would like to perform a reverse lookup. Imagine you have a predetermined budget and are interested in finding out which mobile phone model has a price slightly below that budget.

We can use the LOOKUP function again. This time, it can be used to search for an approximate match in terms of price and return the mobile model that fits just under your specified budget. It is important to note that when the LOOKUP function does not find an exact match, it matches the closest smaller value.

We can ask the user to enter the budget in cell E4 and the formula used will be as follows.

By entering 810 USD as the lookup_value in cell E4, the LOOKUP function searches for the mobile model that is equal to or less than 810 USD. In this case, it should return "iPhone 12" with a price of 800 USD, however, it does not.

The issue is the basic characteristics of the LOOKUP function that it assumes and necessitates that the value in lookup_vector is sorted in ascending order. Here, the lookup_vector refers to $B$2:$B$11 containing the prices. Let's sort the values and repeat the formula.

Now you have the correct return value.

As we are already aware, the LOOKUP function comes in two distinct forms. In the preceding example, we observed the functioning of the vector form. Let's delve into the array form of the LOOKUP function in the subsequent example.

Example 2 – Using Array Form of LOOKUP Function

Suppose we have the complete product details stored in an Excel sheet where each row in the dataset represents a different product, and the columns provide various attributes and characteristics of each product. Now, we wish to retrieve the product price based on the product ID.

As we already know, in its array form, the LOOKUP function dynamically adjusts its behavior according to the dimensions of the array provided. In this case, the number of rows is more than the total columns, therefore the function looks for the lookup_value within the first column which is Product ID in our case. The return value will be data from the last column of the array, situated at the corresponding position matching the product ID which is the Price column.

This is a perfect case scenario to use the array form of the LOOKUP function. Let's take cell G1 where the user can enter the Product ID which will be the input value for the lookup_value argument. The formula used will be as follows.

Although the LOOKUP function gave us the required value, it is recommended to use the VLOOKUP function or HLOOKUP function depending on the data especially since they do not require data sorting.

Example 3 – Identifying Latest Stock Prices with LOOKUP Function

Suppose you are looking to invest in a few stocks and have been tracking the stock prices of three companies: Apple (AAPL), Google (GOOG), and Microsoft (MSFT). You have downloaded some historical stock data, including the date, and closing price for each trading day, which gets updated at the end of each day.

Now, combing through the data is tedious, therefore the goal is to easily find the latest closing price for any of these companies based on their stock name.

As we already know that one of the features of the LOOKUP function is when the lookup_value is not found in the lookup_vector, it returns the next value on the lesser side.

Additionally, the LOOKUP function operates under the assumption that the data is sorted in ascending order, therefore it returns the last value when the lookup_value is greater than all the values found in the lookup_vector. We will use this characteristic of the LOOKUP function to our advantage.

Let's take cell F1 where the user can enter the Stock name. Now, we will check if the value in F1 matches any value in cell range B2:B13.

As we can see, we have an array comprising TRUE and FALSE values, signifying matches, and non-matches, respectively. By dividing 1 by the array, we get an array of 1s for matches and errors (#DIV/0!) for non-matches.

Now we use the above array as the input value for the lookup_vector argument. The next step is when the LOOKUP function searches for 2 in the above array.

Since it will not be found, it defaults to the last 1. This corresponds to the latest "GOOG" entry. The formula will be as follows.

We finally have the latest closing price for "GOOG" which is $2795. The same logic can be applied to find the last non-empty cell in a column.

Example 4 – Finding Last Non-Blank Cell in Column

Lastly, one of the glories of the LOOKUP function is being able to find the last non-blank cell with it. Finding the last entry in a database can be useful for tracking stock prices (as we've seen in the prior example), weather conditions, delivery status, or (arguably the most important one) the latest show or movie on Netflix. Cue list:

Changing the condition in the lookup_vector, this is the formula we will use to find the last non-empty cell in Excel with the LOOKUP function:

The lookup_vector is set as 1/(B:B<>"") resulting in a spill array that gives us 1 for every row in the column that is a non-blank cell (first 9 cells are non-blank) and a #DIV/0! error for every blank cell.

With the lookup_value as 2, LOOKUP will default to its nature of returning the closest lesser value which as per LOOKUP's ascending order assumption will be the last 1 in the array, effectively the last entry in the dataset. With the last 1 chosen, the result_vector will be the corresponding value from column B (defined by B:B).

The final entry has been returned as "Spy Ops".

Now that you have a fair understanding of the LOOKUP function, it must be clear that it provides great functionality to streamline tasks such as data manipulation and data retrieval. Practice and identify more use cases of the function in your daily tasks. While you ace the LOOKUP function, we will work on another useful Excel function to add to your knowledge base.

What would necessitate the removal of any form of brackets in data? A special character when used as part of a text string may result in data discrepancy or as an inhibitor in data processing. Say there's a list of phone numbers with area codes enclosed in round brackets and the system used for contact cannot process characters other than numbers. This will require that the parentheses be ditched for consistency and smooth processing. Let's make this clearer with an example.

The dataset here contains phone numbers that can do without parentheses (for reasons said above). Also, column D is populated with the city name and then the state name bracketed, separated by a comma. We need the data sorted with the city and state in separate columns and before we launch into splitting columns with the comma delimiter, the parentheses need a goodbye. Today's tutorial will give them their farewell.

Do note that while we can say parentheses and define them collectively, they are two separate characters: an opening parenthesis and a closing parenthesis. And that is how we'll get rid of them; one by one. Spoiler says we have a couple of methods that remove them collectively.

Note: Other than the LEFT and FIND functions method (which removes the parentheses and its contents), all other 3 methods remove every instance of the stated parenthesis from a cell's value. You can set the instance number in the SUBSTITUTE function to remove a particular occurrence (as shown later).

Parentheses terminator mode – activated!

Method #1 – Using Find and Replace Feature

Remove parentheses with the Find and Replace feature in Excel. Use Find and Replace as a search bar to locate the parentheses, remove them, and replace them with user-supplied text. As of now, we aim to have the parentheses erased.

Therefore, they will be replaced with an empty text string. Our guess is, like our sample data here, the brackets in your data contain other text between them. Which is why the opening and closing parenthesis will have to be replaced one after the other to retain the contents within.

You have the idea; we can move on to the steps. Below are the details on using the Find and Replace feature to remove parentheses from data in Excel:

• On the worksheet, select the data that contains the parentheses you want to remove.
• In the Home tab, click on the Find & Select button from the Editing Select Replace from the drop menu. You can opt for the keyboard shortcut instead – press the Ctrl + H keys.
• 
• In the Find and Replace dialog box, type the opening parenthesis in the Find what
• Hit the Replace All command button.
• By doing this, you are leaving the Replace with field blank so that the parenthesis can be switched with blank text and consequently, removed.
• 
• Click the OK button of the pop-up detailing the number of replacements made.
• 
• Now you'll be back to the Find and Replace dialog box.
• Empty the Find what field and enter the closing parenthesis this time.
• Select the Replace All
• 
• The pop-up window will appear again with the text replacements.
• Press the Esc key twice to close both dialog boxes.

Returning to the worksheet, the selected data will now be free of the opening and closing parentheses:

Looking at our case example, suppose we want to slash away the parentheses and their contents which will leave us with just the city name instead of city, state. You can still take to Find and Replace for that. Simply enter the opening and closing brackets with an asterisk in between. The asterisk plays as a wildcard, covering any number of characters present between the parentheses. Enter (*) in the Find what box and make the replacements.

There we have removed the state name by replacing the parenthesis, contents included, with blank text:

Notes:

In this sample data above, we only have the city and state name separated by parentheses, without a comma. If the comma was there, in place of "(*)" we would have entered ", (*)" to eliminate the comma and space character along with the brackets and their contents.

The useful bit about using Find and Replace to remove the parentheses-enclosed contents is that the feature will find the position of the brackets automatically in a cell. Unlike the LEFT/RIGHT and FIND functions which remove the enclosed contents from the cell's left or right.

Method #2 – Using SUBSTITUTE Function

Use the SUBSTITUTE function in Excel to remove parentheses from your data. The SUBSTITUTE function is used to replace specific text with other text. That presents us with the perfect opportunity to switch the parentheses in the data out for blank text.

The function itself is straightforward and you don't have to replace the opening and closing parentheses one by one; they can both be replaced using a single formula. Given below is the formula used for removing parentheses in text values with the SUBSTITUTE function:

For the ease of applying a single formula, we have used nested SUBSTITUTE functions. This way, we can add the opening and closing parentheses (enclosed in double quotes) as the values to be replaced by empty text string (denoted by twin double quotes ""). Starting with B2, we can see that the formula has worked fine with numeric text as well as alphabetic text in C2. We have the contact numbers without the area codes enclosed in brackets and the state names only separated by commas.

Copy-pasting the return values to the original dataset and clearing up the redundant columns gives us:

Note: As per our case example, which contains a single instance of parentheses, we have left the instance_num argument empty. If, e.g., there are 2 sets of parentheses in a cell and you want the second set removed, enter the last argument of the SUBSTITUTE function as 2. Not defining this argument will remove both sets from the cell.

Method #3 – Using LEFT & FIND Function

Find out how to remove parentheses and their contents in Excel with the LEFT and FIND functions. Use this option for clearing parentheses and their contained value from a cell. This is the function version of clearing the parentheses part of a cell as we have also seen with the Find and Replace feature.

The enclosed data is on the right side of the cell, and we need the FIND function to locate the opening parenthesis and return the value to the left of it. See the formula ahead which uses the LEFT and FIND functions to erase parentheses along with their contents:

The FIND function searches the position of the opening bracket in C3 with the starting position of the search defined as 1 (i.e. FIND will begin its search from the first character of C3). The LEFT function is set to return the value in C3 that is on the left of the opening parenthesis. -1 in the end ensures that LEFT's return value is without the opening parenthesis itself. Here we have all the city names without the state name and its brackets:

If the bracketed value in your data is on the left, the formula will have to be tweaked to return the value to the right of the closing parenthesis. Here's a sample formula you can go by to remove parentheses and their contents from the left of a cell:

Since we need the value from the right, we incorporate the RIGHT function. The return value is to be the parentheses-free text in C3. The number of characters is set by the LEN and FIND functions where FIND gets the position of the closing bracket as 10 and LEN counts the total characters as 19.

Therefore, RIGHT delivers the 19-10 characters that are on the right side in C3. In column C, after the closing parenthesis, there is a space character which will also be returned as part of the value. To eliminate that, we have fixed a -1 in the end. The outcome of this formula is shown here:

Method #4 – Using VBA Function

Now we'll show you how to remove parentheses using VBA in Excel. VBA programs Office applications into performing tasks. For our case example, we can use a code in VBA to remove just the parentheses in the selected data without altering anything else. The code and the detailed steps for this task to be carried out in Excel are given ahead:

• Select the target data containing the parentheses.
• Use the Visual Basic icon in the Developer tab's Code group to launch the Visual Basic You can also use the Alt + F11 keys to do this.
• 
• In the editor, select the Insert Then select Module from the drop menu.
• 
• You will now have access to a Module window.
• In the Module window, copy-paste the code from below:

• Sub RemoveParentheses()
  Dim ws As Worksheet
  Dim rng As Range
  Dim result As Range
  Set ws = Application.ActiveSheet
  Set rng = Application.Selection
  For Each cell In rng
    cell.Value = Replace(cell.Value, "(", "")
    cell.Value = Replace(cell.Value, ")", "")
  Next
  End Sub

  The code replaces both the opening and closing parentheses in the selected cells with blank text using the Replace function in VBA.

• Hit the Run button in the editor's toolbar (highlighted above) to activate the code.
• The code will do its work in the background, on the worksheet.
• Close the Visual Basic

All the selected data will be free of the parentheses:

Remove parentheses from data? Covered. Remove parentheses with their contents? Covered. Formula, tool, and VBA method? Covered, covered, covered. Yes, we've got you covered with all the easy tricks. But this is just a bunch in a sea of many. So much remains uncovered. We've all got our job cut out. Ready? Tricky? Go!

A function may not be dedicated to the cause, but we are and we have 3 ways you can make numbers part way from your datasets. Do not be intimidated by the length of the formulas, (you only have to copy-paste, isn't that so?)

Any curiosity thirst will be quenched by the superfine minutiae of how the formulas work and alternatives to not having access to the later functions. We're going to crack this mystery and here's the cracker (or crackee, more like):

Column B holds product codes in alphanumeric style. And not a consistent one. You can see that the numbers are at both ends in some categories and at one end in others. Let's say Company A wants to homogenize the codes for smooth data entry and readability of sales records.

Yes, number extraction is the mine work for today and the usual methods like Text to Columns cannot work because we're not dealing with a single format here.

All three methods given in this tutorial, as you will observe, can work on extracting numbers from values whether the numbers are interspersed or in different formats.

Extraction goggles on, let's go!

Method #1 – Using TEXTJOIN Function

Use the TEXTJOIN function to only extract numbers from a cell's value in Excel. The TEXTJOIN function combines a range of text strings. While that sounds like the converse of wanting to separate numbers, in effect, the TEXTJOIN will be used to concatenate the numbers dispersed in a text string.

Word of caution, skip to the next section if your Excel is older than 2019 or you are sans MS 365 subscription since the TEXTJOIN function is only available for the mentioned.

So now we make alphabetic strings disappear? Somewhat, yes. Find out how the formula below returns just the numeric part of a text string with the TEXTJOIN function:

Firstly, the ROW and INDIRECT functions are used to generate a spill series of numbers from 1 to the number of characters in B4 (measured by the LEN function). LEN counts the number of characters as 9 in this case. The & operator makes this expression 1:9. The ROW function is used to return a row number and will take the reference from the INDIRECT function, making the spill values 1 to 9.

Then the MID function returns 1 character of B4 for every number spilled by the ROW and INDIRECT functions e.g. 1 returns the first character of B4 as "5", 2 returns the second character as "3". Therefore, this part of the formula returns the entire text string in B4 as a spill, readying each character to be checked separately as a number.

The IFERROR function loops through every character and multiples it by 1. The theory is that the numeric content of the spilled values can be multiplied by 1 and will return as they are.  A letter of the alphabet cannot be multiplied and will return an error.

When faced with an error, the IFERROR function will return blank text "", ruling out the entire alphabetic string. What is left are the numbers and blank cells. Finally, the TEXTJOIN function collates the numbers by joining (without a delimiter) all the values left in the spill range, ignoring blank cells (set by "TRUE" in the second argument).

Note: After typing the whole formula, (for Excel 2010 – 2019), enter this array formula with the Ctrl + Shift + Enter keys. MS 365 users can just hit the Enter key as with regular formulas.

Method #2 – Using Custom Formula

Extract just the number component from a cell in Excel with the help of a formula. As mentioned, this formula is an alternative for non-Excel 2019 or MS 365 users since earlier Excel versions do not support the TEXTJOIN function.

The base of this formula is the same as the one above up until the MID function but additionally requires the IF, SUM, SUBSTITUTE, SUMPRODUCT, LARGE, INDEX, ISNUMBER to get similar results.

Since this takes up a little more space (subjectively) than other formulas, we'll break it down bit by bit. This is the formula, and the explanation follows:

Breakdown:

For explanatory purposes and to tie everything in later, we'll call this section of the formula "core calculation". It starts with the number series by the ROW and INDIRECT functions and ends with the SUMPRODUCT function so here we go.

Again starting with the ROW and INDIRECT functions, a series of numbers is generated from 1 to the number of characters in B4 which is 9. The ROW function places the consecutive numbers for the reference returned by the INDIRECT function. We get 9 rows of spilled number series starting from 1 like so: {1;2;3;4;5;6;7;8;9}

Every number in this series is the starting point for the MID function for returning 1 character from the value in B4 as: {"5″;"3″;"-";"H";"A";"L";"-";"4″;"1"}

Then there's the ISNUMBER function to check each character from the MID function as a number and return TRUE or FALSE:
{TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}

Adding the double unary operator converts these Boolean values into 1 and 0 respectively, prepping to operate as an array formula for the INDEX function. Multiplying these 1s and 0s with the number series generated by the ROW and INDIRECT functions will give us the position of the numbers in the cells, the other characters will result in 0.

{1;2;0;0;0;0;0;8;9}

The LARGE function returns the kth largest value. K here is the number series from 1 to 9. Hence, we get INDEX's output in descending order. This will stack the zeros to one side, getting the position of the numbers together.

{9;8;2;1;0;0;0;0;0}

The value the MID function is using to return characters is 0&B4, joining 0 to B4's value as the MID function returns the characters as per the defined positions (shown above). This added 0 and +1 as MID's second argument will help avoid the #VALUE! error which will arise when MID is to return the 0th position from B4. As of now, the 0th position will become 0+1 i.e. the 1st position which will return the concatenated 0.

{"1″;"4″;"3″;"5″;"0″;"0″;"0″;"0″;"0"}

Here's the masterstroke that begins flipping things around. This part of the function places a regular 10 and raises it to a power. The exponent is fed by the number series by ROW and INDIRECT going like 10¹, 10², and so on resulting in:
{10;100;1000;10000;100000;1000000;10000000;100000000;1000000000}

Divided by 10 and we’re at:
{1;10;100;1000;10000;100000;1000000;10000000;100000000}

You can see at the end of the formula that the "10" expression has been multiplied by the MID function and will result in: {1;40;300;5000;0;0;0;0;0}

This is where the core calculation ends. Have it known that we've tried the SUM function in place of SUMPRODUCT with no problems. Anyhow, when the SUMPRODUCT function adds it up, we get (hallelujah) 5341 which is what we're looking for and you can very well stop here as the formula works fine to separate the numbers from a string and return them:

The hitch arises if your input range also contains values that don't have any numbers. Let's show you what happens:

We've changed the top two product codes to be without numbers and the formula returns 0. The only problem is that the 0 can be mistaken to be the only number in the code. In that case, let's use the complete formula to give us empty text when there are no numbers in the input string. Moving on to further breakdowns.

Now the SUBSTITUTE function will replace any occurrence of the digits 0 to 9, one by one, in B4 with a blank space. This will spill in a horizontal range. Since B4 doesn't have any 0s, B4 will be returned as is. B4 does have the number 1 and so in the second value of the output, 1 will be replaced by a blank space. In this way, all the digits will be checked:
{"53-HAL-41″,"53-HAL-4″,"53-HAL-41″,"5-HAL-41″,"53-HAL-1″,"3-HAL-41″,"53-HAL-41″,"53-HAL-41″,"53-HAL-41″,"53-HAL-41"}

The LEN function counts the number of characters in each value of the spilled result. With no 0, we had all 9 characters in the first value. In the second value, 1 was replaced by empty text, leaving 8 characters and so on.

{9,8,9,8,8,8,9,9,9,9}

Subtract this result from the total number of characters, again counted by LEN. We get the number of missing characters and thus the count of numbers present in B4.

{0,1,0,1,1,1,0,0,0,0}

To bring the tally together, the SUM function will suffice and result in 4.

And at last, the complete formula is brought together by the IF function. In plain words, the formula says that if the count of numbers is not greater than zero, return empty text (see the last argument of the formula). If the count of numbers is greater than zero, perform core calculation. Since the count of numbers in B4 is 4 and is greater than 0, the core calculation has landed us at the final output which is 5341.

B5 as shown above does not have numeric data and results in a blank cell now.

Pro tip: Notice we have a few instances with only 3 digits extracted as Excel automatically eliminates leading zeros from numbers. A quick solution is to add the TEXT function to the formula so we can have the leading zeros without default removal. Use this formula to extract numbers from a cell and keep leading zeros:

The TEXT function has been added and the format we have supplied is "0000" to keep the resulting number at 4 digits even if it leads with 0s.

Method #3 – Using VBA

Learn how to extract only numbers from a cell using VBA in Excel. VBA programming is used to automate tasks in Excel. By now you would know that there is no direct function in Excel that can get this job easily done for us. Thus, we shall plant a function on our own!

Using a code involving the IsNumeric function in VBA, we will set up a function to extract numbers from a range of text strings. The following steps will demonstrate in complete detail how to utilize VBA to get only numbers from a cell:

• Open the Visual Basic editor by pressing the Alt + F11 If you have the Developer tab enabled, you can also use the Visual Basic button in the Developer tab's Code group.
• 
• In the editor, click on the Insert tab and select Module from the drop-down menu.
• 
• Copy and paste this code from below in the Module window:

• Function ExtractNumbers(CellRef As String)
  Dim StringLength As Integer
  StringLength = Len(CellRef)
  For i = 1 To StringLength
    If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
    Next i
  ExtractNumbers = Result
  End Function

  The code uses the Mid function to operate with the IsNumeric function in VBA to extract numbers from within a cell's value. The function name in the first line of the code ("ExtractNumbers" in this case) is the name you will use on the worksheet so be mindful of that.

• After inserting the code, close the Visual Basic
• Enter the function name in the target cell on the worksheet and it will show up in IntelliSense.
• 
• Select the function and refer the cell containing the text string you want to extract numbers from.

Enter the function and it will extract the numbers from the relevant cell:

And that readers, is how numbers are extracted from a cell in some handy Excel ways. More handyman Excel services are coming up your way giving you all the fine tools to chisel your way through with just the perfect Excel trick. Ready? Tricky? Go!

While using the TAKE function, the user can choose the number of rows or columns to be extracted from the beginning or the end of the array.

Syntax

The syntax of the TAKE function consists of three arguments and is structured as follows.

Arguments:

The TAKE function accepts two mandatory arguments while the third is optional. The role of each input argument is stated below.

'array' – This is the first and only mandatory argument of the TAKE function. It accepts the value of the original cell range or array from which we wish to extract the rows or columns. The input value of the array argument can also be a static array or a return value from another function.

'rows' – This is a mandatory argument that indicates the number of rows we wish to extract from the original The argument must be mentioned in the formula (by way of a comma) even if it is left empty. When the value of the rows argument is left empty, the TAKE function returns all the rows from the original array.

Another aspect of the rows argument is if the input value is a positive number, the function retrieves the row(s) from the top of the array. Conversely, when a negative value is given, it extracts row(s) from the bottom of the array.

'cols' – This is an optional argument that accepts the value of the number of columns to be extracted from the input The positive value of the cols argument indicates that the TAKE function will extract the column(s) from the left of the array. Meanwhile, a negative value suggests that column(s) must be extracted from right to left. If the cols argument is left empty, the function returns all the column(s) from the input array.

Important Characteristics of the TAKE Function

One of the basic features of the TAKE function is that it creates a new array containing the specified rows and columns. Furthermore, the TAKE function offers several notable features, outlined below.

• If the value of the rows or cols argument is greater than the input array, the TAKE function returns all the rows or columns of the complete input array.
• If the input array is empty, the TAKE function returns an array of 0s depending on the rows and columns argument.
• When the array argument is left empty, the function throws a #VALUE! error.
• When the rows or cols argument is set to 0, the TAKE function results in a #CALC! error.

With the aforementioned features in consideration, let's move on to comprehending the workings of the TAKE function and how it interacts with data.

Examples of TAKE Function

To better understand the TAKE function in Excel, let's start by using different input values for each argument to demonstrate how it functions and the outcomes it produces. This will help us grasp its mechanics and utility more comprehensively.

Here, we have taken a small dataset that consists of 3 rows and columns.

In the first example, only the input value for the first two arguments is used, and the cols argument is left empty. As a result, the TAKE function extracts the first 2 rows from the range B1:D2, while returning all columns.

The next example showcases the same logic but for columns. Here, as observed, we have skipped the input value for the rows argument. As intended, the function extracted all rows and the first 2 columns from the range B1:D3.

The subsequent instance is a classic example where the input values for all arguments are used. The TAKE function considers the number of rows and columns and extracts the first row and 2 columns from the given dataset.

In the last example, as the input values for both the rows and cols arguments are empty, the TAKE function simply returns the entire input range B1:D3 as the output.

Now that the basic functionality of the TAKE function is clear, let's explore more examples to showcase its capabilities to retrieve specific portions of information from larger datasets.

Example 1 – Simple Use of the TAKE Function

Suppose you are organizing a few workshops that have limited seating and follow a first come, first serve enrollment process. Due to its popularity, you have received several applicants which are now stored in an Excel dataset that includes information such as the applicant's names, contact details, workshop preferences, and enrollment timestamps.

After the application period concludes, you intend to extract the first 5 applicants as they are sorted as per enrollment timestamp and notify them about their application status.

Using the TAKE function, we can easily extract the first 5 applicants and the relevant columns. Subsequently, we can leverage the extracted subset for any further use. The function used will be as follows.

This will extract the first 6 rows from the dataset A1:D16 along with the last 3 columns as we do not need the enrollment timestamps column. We are extracting the first 6 rows which include the row header and the first 5 applicants.

This exemplifies a classic application of the TAKE function. Let's delve deeper to uncover additional scenarios where the same function proves valuable.

Example 2 – Calculating Average of Last N Numbers with TAKE Function

Suppose we wish to analyze the stock prices of a company over a period, and we want to calculate the average closing price of the last N trading days to identify recent price trends. We downloaded the stock price trading data which keeps updating each day.

We can use the TAKE function to extract the last N days of data and then use the AVERAGE function to calculate the average. Let's say we wish to calculate the average closing price for the last 5 days. The formula to extract the intended values will be as follows.

Now we can simply use the AVERAGE function as follows.

We have obtained the desired average closing price, but it's important to acknowledge a limitation in this approach. With the stock price dataset expanding daily, the formula needs to be adaptable and encompass the newly added rows to remain effective.

There are two ways we can achieve that. One way is to format the existing dataset as a Table in Excel. Select the dataset which is $A$1:$E$11 and press the Ctrl + T shortcut. Alternatively, choose Format as Table option from the Home Tab.

After we press OK, the table is created. Now, to rename the table as per your preference, go to the Table Design tab, select the existing name in the Table Name box, and overwrite it with a new one. We have named it 'stockprice'.

Now the formula used will be as follows.

As we can see, an additional row has been added and the formula dynamically encompasses the most recent 5 rows.

Another way to achieve this is by combining the FILTER function with the TAKE function. We can include the entire column E as the input array and FILTER the range of cells that are non-empty. We can then use the TAKE function on the filtered data and extract the last 5 values.

The function will be as follows.

Now, we can easily calculate the average of the filtered data. The formula will be as follows.

As observed, we now have the average closing price for the last 5 days and the formula remains unaffected even after incorporating an additional row. As a practice, try to use the combination of FILTER and TAKE functions to extract the last day's data when the High price was more than 115.

Example 3 – Using SORTBY and TAKE Function

Suppose you have a dataset containing movie titles, user ratings, release years, and genres. You now wish to extract the highest and lowest-rated movies within different genres from a huge dataset.

To achieve this, we will begin by sorting the dataset based on user ratings. Subsequently, we will use the TAKE function to extract both the top and bottom movie names.

The following formula will rearrange the "Movie Title" column (A2:A16) based on the corresponding user ratings in the "User Rating" column (B2:B16) in descending order (-1). This means the movies will be arranged from highest to lowest user ratings.

We can now easily extract the first and last movie names from the sorted data indicating the highest and lowest rated. The formula used will be as follows.

Let's go a step further and get rid of the need to use two different formulas. This can be achieved by using an array as an input for rows. The formula used will be as follows.

The array input {1; -1} tells the TAKE function to extract the first and last row from the sorted array. The semicolon will spill the result vertically.

Example 4 – Using TAKE Function on Separate Datasets

Imagine you own a retail store that sells electronics, clothing, and home goods. You now have sales data for different products under each category, organized in separate datasets. The aim is to identify the top 3 selling products across all categories for a special promotional campaign.

As there are separate datasets for each category, the first step is to use the VSTACK function to vertically combine the three category datasets, creating a merged dataset. The formula used will be as follows.

Now, we can reorder the combined dataset using the SORT function based on the Units Sold column (column 2) in descending order (-1). The formula will be as follows.

Now, the dataset is sorted in a manner where the product with the highest quantity of units sold is on the top. In the final step, we can use the TAKE function to extract the first 3 rows indicating the top 3 selling products. The final formula will be as follows.

The same logic can be used to combine data from different worksheets or combine them horizontally using HSTACK function and then use the TAKE function to extract the desired data.

TAKE Function vs DROP Function

Suppose you have a dataset related to soccer league standings of various teams. The data includes details such as team name, matches played, number of wins, draws, losses, and the points earned by each team. The aim is to extract the top 5 teams in the standings and remove the last 5 teams.

As we already know, the TAKE function can be used to extract the required rows and columns. Therefore, we can use the TAKE function to extract the top 5 rows representing the top teams. The formula used will be as follows.

On the contrary, the DROP function can be used to remove the required rows and columns. So, we can utilize the DROP function in this case and remove the last 5 teams which have the minimum points. The function used will be as follows.

The function, therefore, excluded the last five rows indicated by the negative input value.

Both the TAKE and DROP functions showcase complementary features used in data manipulation in Excel where in both cases we can extract a subset from a larger data. One function retains the data while the other removes it.

The TAKE function helps us retrieve the desired rows and columns, yet its constraint lies in its exclusive extraction of neighboring rows and columns. To extract non-adjacent rows or columns, we can use the CHOOSEROWS function or CHOOSECOLS function.

Delve deeper into the manifold applications of the TAKE function and unlock its versatile potential to elevate both your Excel proficiency and data analysis skills. Meanwhile, we bring to you yet another Excel function to expand your repertoire of tools and techniques.