Trending February 2024 # Hyperlinks In Excel (A Complete Guide + Examples) # Suggested March 2024 # Top 10 Popular

You are reading the article Hyperlinks In Excel (A Complete Guide + Examples) updated in February 2024 on the website We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 Hyperlinks In Excel (A Complete Guide + Examples)

Excel allows having hyperlinks in cells which you can use to directly go to that URL.

There are many things you can do with hyperlinks in Excel (such as a link to an external website, link to another sheet/workbook, link to a folder, link to an email, etc.).

In this article, I will cover all you need to know to work with hyperlinks in Excel (including some useful tips and examples).

There are many different ways to create hyperlinks in Excel:

Manually type the URL (or copy paste)

Using the HYPERLINK function

Using the Insert Hyperlink dialog box

Let’s learn about each of these methods.

Manually Type the URL

When you manually enter a URL in a cell in Excel, or copy and paste it in the cell, Excel automatically converts it into a hyperlink.

Below are the steps that will change a simple URL into a hyperlink:

Select a cell in which you want to get the hyperlink

Similarly, when you copy a URL from the web (or some other document/file) and paste it in a cell in Excel, it will automatically be hyperlinked.

Insert Using the Dialog Box

If you want the text in the cell to be something else other than the URL and want it to link to a specific URL, you can use the insert hyperlink option in Excel.

Below are the steps to enter the hyperlink in a cell using the Insert Hyperlink dialog box:

Select the cell in which you want the hyperlink

Enter the text that you want to be hyperlinked. In this case, I am using the text ‘Sumit’s Blog’

In the Insert Hyperlink dialog box, enter the URL in the Address field.

Press the OK button.

This will insert the hyperlink the cell while the text remains the same.

There are many more things you can do with the ‘Insert Hyperlink’ dialog box (such as create a hyperlink to another worksheet in the same workbook, create a link to a document/folder, create a link to an email address, etc.). These are all covered later in this tutorial.

Insert Using the HYPERLINK Function

Another way to insert a link in Excel can be by using the HYPERLINK Function.

Below is the syntax:

HYPERLINK(link_location, [friendly_name])

link_location: This can be the URL of a web-page, a path to a folder or a file in the hard disk, place in a document (such as a specific cell or named range in an Excel worksheet or workbook).

[friendly_name]: This is an optional argument. This is the text that you want in the cell that has the hyperlink. In case you omit this argument, it will use the link_location text string as the friendly name.

Below is an example where I have the name of companies in one column and their website URL in another column.

Below is the HYPERLINK function to get the result where the text is the company name and it links to the company website.

In the examples so far, we have seen how to create hyperlinks to websites.

But you can also create hyperlinks to worksheets in the same workbook, other workbooks, and files and folders on your hard disk.

Let’s see how it can be done.

Below are the steps to create a hyperlink to Sheet2 in the same workbook:

Select the cell in which you want the link

Enter the text that you want to be hyperlinked. In this example, I have used the text ‘Link to Sheet2’.

In the Insert Hyperlink dialog box, select ‘Place in This Document’ option in the left pane.

Enter the cell which you want to hyperlink (I am going with the default A1).

Select the sheet that you want to hyperlink (Sheet2 in this case)

Note: You can also use the same method to create a hyperlink to any cell in the same workbook. For example, if you want to link to a far off cell (say K100), you can do that by using this cell reference in step 6 and selecting the existing sheet in step 7.

You can also use the same method to link to a defined name (named cell or named range). If you have any named ranges (named cells) in the workbook, these would be listed in under the ‘Defined Names’ category in the ‘Insert Hyperlink’ dialog box.

Apart from the dialog box, there is also a function in Excel that allows you to create hyperlinks.

So instead of using the dialog box, you can instead use the HYPERLINK formula to create a link to a cell in another worksheet.

The below formula will do this:

=HYPERLINK("#"&"Sheet2!A1","Link to Sheet2")

Below is how this formula works:

“#” would tell the formula to refer to the same workbook.

“Sheet2!A1” tells the formula the cell that should be linked to in the same workbook

“Link to Sheet2” is the text that appears in the cell.

You can also use the same method to create hyperlinks to other Excel (and non-Excel) files that are in the same folder or are in other folders.

For example, if you want to open a file with the chúng tôi which is in the same folder as your current file, you can use the below steps:

Select the cell in which you want the hyperlink

In the Insert Hyperlink dialog box, select ‘Existing File or Webpage’ option in the left pane.

Select ‘Current folder’ in the Look in options

Select the file for which you want to create the hyperlink. Note that you can link to any file type (Excel as well as non-Excel files)

[Optional] Change the Text to Display name if you want to.

You can also do this using the HYPERLINK function.

The below formula will create a hyperlink that links to a file in the same folder as the current file:

=HYPERLINK("Test.xlsx","Test File")

In case the file is not in the same folder, you can copy the address of the file and use it as the link_location.

This one also follows the same methodology.

Below are the steps to create a hyperlink to a folder:

Copy the folder address for which you want to create the hyperlink

Select the cell in which you want the hyperlink

In the Insert Hyperlink dialog box, paste folder address

You can also use the HYPERLINK function to create a hyperlink that points to a folder.

=HYPERLINK("C:UserssumitDesktopTest","Test Folder")

To use this formula, you will have to change the address of the folder to the one you want to link to.

You can also have hyperlinks which open your default email client (such as Outlook) and have the recipients email and the subject line already filled in the send field.

Below are the steps to create an email hyperlink:

Select the cell in which you want the hyperlink

Enter the E-mail address and the Subject line

[Optional] Enter the text you want to be displayed in the cell.

You can also do this using the HYPERLINK function.

The below formula will open the default email client and have one email address already pre-filled.

Note that you need to use mailto: before the email address in the formula. This tells the HYPERLINK function to open the default email client and use the email address that follows.

In case you want to have the subject line as well, you can use the below formula:

In the above formula, I have kept the cc and bcc fields as empty, but you can also these emails if needed.

Here is a detailed guide on how to send emails using the HYPERLINK function.

If you only have a few hyperlinks, you can remove these manually, but if you have a lot, you can use a VBA Macro to do this.

Manually Remove Hyperlinks

Below are the steps to remove hyperlinks manually:

Select the data from which you want to remove hyperlinks.

The above steps would instantly remove hyperlinks from the selected cells.

In case you want to remove hyperlinks from the entire worksheet, select all the cells and then follow the above steps.

Remove Hyperlinks Using VBA

Below is the VBA code that will remove the hyperlinks from the selected cells:

Sub RemoveAllHyperlinks() Selection.Hyperlinks.Delete End Sub

If you want to remove all the hyperlinks in the worksheet, you can use the below code:

Sub RemoveAllHyperlinks() ActiveSheet.Hyperlinks.Delete End Sub

Note that this code will not remove the hyperlinks created using the HYPERLINK function.

You need to add this VBA code in the regular module in the VB Editor.

For some people, it’s a great feature that Excel automatically converts a URL text to a hyperlink when entered in a cell.

And for some people, it’s an irritation.

If you’re in the latter category, let me show you a way to prevent Excel from automatically creating URLs into hyperlinks.

The reason this happens as there is a setting in Excel that automatically converts ‘Internet and network paths’ into hyperlinks.

Here are the steps to disable this setting in Excel:

In the AutoCorrect dialog box, select the ‘AutoFormat As You Type’ tab.

Uncheck the option – ‘Internet and network paths with hyperlinks’

Close the Excel Options dialog box.

If you’ve completed the following steps, Excel would not automatically turn URLs, email address, and network paths into hyperlinks.

Note that this change is applied to the entire Excel application, and would be applied to all the workbooks that you work with.

There is no function in Excel that can extract the hyperlink address from a cell.

However, this can be done using the power of VBA.

For example, suppose you have a dataset (as shown below) and you want to extract the hyperlink URL in the adjacent cell.

Let me show you two techniques to extract the hyperlinks from the text in Excel.

Extract Hyperlink in the Adjacent Column

If you want to extract all the hyperlink URLs in one go in an adjacent column, you can so that using the below code:

Sub ExtractHyperLinks() Dim HypLnk As Hyperlink For Each HypLnk In Selection.Hyperlinks HypLnk.Range.Offset(0, 1).Value = HypLnk.Address Next HypLnk End Sub

The above code goes through all the cells in the selection (using the FOR NEXT loop) and extracts the URLs in the adjacent cell.

In case you want to get the hyperlinks in the entire worksheet, you can use the below code:

Sub ExtractHyperLinks() On Error Resume Next Dim HypLnk As Hyperlink For Each HypLnk In ActiveSheet.Hyperlinks HypLnk.Range.Offset(0, 1).Value = HypLnk.Address Next HypLnk End Sub

Note that the above codes wouldn’t work for hyperlinks created using the HYPERLINK function.

Extract Hyperlink Using a Formula (created with VBA)

The above code works well when you want to get the hyperlinks from a dataset in one go.

But if you have a list of hyperlinks that keeps expanding, you can create a User Defined Function/formula in VBA.

This will allow you to quickly use the cell as the input argument and it will return the hyperlink address in that cell.

Below is the code that will create a UDF for getting the hyperlinks:

Function GetHLink(rng As Range) As String GetHLink = "" Else GetHLink = rng.Hyperlinks(1).Address End If End Function

Note that this wouldn’t work with Hyperlinks created using the HYPERLINK function.

Also, in case you select a range of cells (instead of a single cell), this formula will return the hyperlink in the first cell only.

If you’re working with a huge dataset that has a lot of hyperlinks in it, it could be a challenge when you want to find the ones that have a specific text in it.

For example, suppose I have a dataset as shown below and I want to find all the cells with hyperlinks that have the text 2023 in it and change it to 2023.

And no.. doing this manually is not an option.

You can do that using a wonderful feature in Excel – Find and Replace.

With this, you can quickly find and select all the cells that have a hyperlink and then change the text 2023 with 2023.

Below are the steps to select all the cells with a hyperlink and the text 2023:

Select any cell which has a hyperlink in it. You will notice that the Format gets visible in the box on the left of the Format button. This indicates that the format of the cell you selected has been picked up.

Enter 2023 in the ‘Find What’ field and 2023 in the ‘Replace with’ field.

In the above data, it will change the text of four cells that have the text 2023 in it and also has a hyperlink.

Note: This technique works as Excel is able to identify the formatting of the cell that you select and use that as a criterion to find cells. So if you’re finding hyperlinks, make sure you select a cell that has the same kind of formatting. If you select a cell that has a background color or any text formatting, it may not find all the correct cells.

While Hyperlinks are useful, there are a few things about it that irritate me.

For example, if you want to select a cell that has a hyperlink in it, Excel would automatically open your default web browser and try to open this URL.

So let me quickly show you how to get rid of these minor irritants.

Select the Cell (without opening the URL)

This is a simple trick.

After a second, you’ll notice that the hand cursor icon changes into the plus icon, and now when you leave it, Excel will not open the URL.

Instead, it would select the cell.

Now, you can make any changes in the cell you want.

Neat trick… right?

This is another thing that might drive you nuts.

Here is a quick fix.

This happens when these cells have the wrap text enabled.

There are useful things you can do when working with hyperlinks in Excel.

In this section, I am going to cover some examples that you may find useful and can use in your day-to-day work.

Example 1 – Create an Index of All Sheets in the Workbook

If you have a workbook with a lot of sheets, you can use a VBA code to quickly create a list of the worksheets and hyperlink these to the sheets.

This could be useful when you have 12-month data in 12 different worksheets and want to create one index sheet that links to all these monthly data worksheets.

Below is the code that will do this:

Sub CreateSummary() 'This code can be used to create summary worksheet with hyperlinks Dim x As Worksheet Dim Counter As Integer Counter = 0 For Each x In Worksheets Counter = Counter + 1 If Counter = 1 Then GoTo Donothing With ActiveCell .Value = x.Name With Worksheets(Counter) .Range("A1").Value = "Back to " & ActiveSheet.Name chúng tôi Sheets(x.Name).Range("A1"), "", _ "'" & chúng tôi & "'" & "!" & ActiveCell.Address, _ ScreenTip:="Return to " & ActiveSheet.Name End With End With ActiveCell.Offset(1, 0).Select Donothing: Next x End Sub

You can place this code in the regular module in the workbook (in VB Editor)

This code also adds a link to the summary sheet in cell A1 of all the worksheets. In case you don’t want that, you can remove that part from the code.

You can read more about this example here.

Note: This code works when you have the sheet (in which you want the summary of all the worksheets with links) at the beginning. In case it’s not at the beginning, this may not give the right results).

Example 2 – Create Dynamic Hyperlinks

But you can also use a little bit for Excel formula trickery to create dynamic hyperlinks.

By dynamic hyperlinks, I mean links that are dependent on a user selection and change accordingly.

For example, in the below example, I want the hyperlink in cell E2 to point to the company website based on the drop-down list selected by the user (in cell D2).

This can be done using the below formula in cell E2:

When you change the selection using the drop-down list, the VLOOKUP result will change and would accordingly link to the selected company’s website.

This could be a useful technique when you’re creating a dashboard in Excel. You can make the hyperlinks dynamic depending on the user selection (which could be a drop-down list or a checkbox or a radio button).

Here is a more detailed article of using Dynamic Hyperlinks in Excel.

Example 3 – Quickly Generate Simple Emails Using Hyperlink Function

As I mentioned in this article earlier, you can use the HYPERLINK function to quickly create simple emails (with pre-filled recipient’s emails and the subject line).

Single Recipient Email Id

Multiple Recipients Email Id

For sending the email to multiple recipients, use a comma to separate email ids. This would open the default email client with all the email ids in the ‘To’ field.

Add Recipients in CC and BCC List

Add Subject Line

You can add a subject line by using the &Subject code. In this case, this would add ‘Excel is Awesome’ in the ‘Subject’ field.

Add Single Line Message in Body

This would add a single line ‘I love Excel’ to the email message body.

Add Multiple Lines Message in Body

To add multiple lines in the body you need to separate each line with %0A. If you wish to introduce two line breaks, add %0A twice, and so on.

Here is a detailed article on how to send emails from Excel.

Hope you found this article useful.

You May Also Like the Following Excel Tutorials:

You're reading Hyperlinks In Excel (A Complete Guide + Examples)

Generative Ai In Copywriting: A Complete Guide In 2023

97% of marketers believe that content writing is an essential marketing approach, and 60% of marketers report bringing in new consumers as a result of their content marketing efforts. Copywriting has become essential to digital marketing as businesses strive to increase their online presence and attract more customers. Effective copywriting can assist a company in

Attracting potential customers

Increasing brand awareness

Driving sales.

However, creating high-quality and engaging content can be time-consuming and challenging. This is where generative AI comes into play. We prepared this guide to inform businesses about the benefits of generative AI in copywriting, its use cases, best practices, and limitations, and to provide insights for incorporating the technology into their content creation strategies.

What is Generative AI?

Generative AI falls under the artificial intelligence (AI) umbrella and can generate new content, such as text, images, or videos. After being trained on large amounts of data, these models can learn to generate output indistinguishable from human-created content. ChatGPT, for instance, is a cutting-edge generative AI model capable of producing natural language text in response to a prompt or input. It keeps 570 GB of data, which is made up of numerous written content like books, articles, websites, etc., in addition to 176 billion attributes.

Figure 1. Visualization of how generative AI tools can be used in different types of content creation

Generative AI models require a large amount of training data and computing resources to function appropriately. Once trained, the model can generate new content by taking a set of input parameters and producing an output.

For example, if you enter the prompt “Once upon a time,” a generative AI model may generate a new story beginning with that phrase. Here’s an example of what the model could produce:

Figure 2. Example of a content creation using ChatGPT-3 with the prompt “Once upon a time”

4 benefits of AI content generators in copywriting 1- Speed up the writing process 2- Overcome writer’s block

Writer’s block happens when a content writer feels trapped and unable to generate fresh concepts or strategies. Copywriters can overcome writer’s block and discover new directions by employing AI to generate a list of probable ideas or prompts.

Copywriters can concentrate on other areas of writing, like editing and fine-tuning the content, by employing AI to generate headlines, taglines, and other copy elements. This can lighten the writer’s cognitive burden and simplify generating fresh concepts and strategies.

3- Reduce costs

External contractors or agencies may be eliminated with the help of generative AI. Businesses can avoid the high costs associated with hiring external copywriters or agencies by using AI to generate copy, which can be especially costly for small businesses or startups.

4- Create a consistent brand image

By learning a company’s language patterns and style, generative AI can ensure constant messaging, leading to more coherent marketing activities and a stronger brand image. AI-generated copy components can also guarantee messaging coherence across various campaigns and media, strengthening the brand’s message and values for boosted engagement and loyalty.

Top 6 use cases of generative AI in copywriting 1- Website content & blog posts

AI can generate a first draft of the article or blog post, which can serve as a starting point for human writers to refine and customize the content as needed. Additionally, AI can assist in the research process by identifying relevant sources and citations to support the article’s claims and arguments.

2- Social media posts

AI models can find topics, keywords, and content formats that resonate with the target audience by examining consumer data and social media trends. They can then generate posts that align with these customer insights. Each social media platform’s character limits, hashtags, and multimedia formats can also be modified to match the needs for AI-generated posts.

By employing AI to generate social media posts, businesses can give managers more time to work on other parts of social media strategy, such as engagement, analytics, and community management.

3- Product descriptions 4- Search Engine Optimization (SEO)

Search engine optimization (SEO) is accomplished through a combination of technical and creative strategies, such as:

optimizing content for keywords

constructing high-quality backlinks

increasing site speed and mobile-friendliness

providing a clear and easy-to-navigate site structure.

Generative AI tools can identify the most relevant keywords and phrases for the target audience and incorporate them into the content to improve its ranking on search engines, which is crucial for SEO. Besides, by analyzing the content and linking structure of the website, generative AI can suggest new opportunities for linking, improving the overall flow of the content, and increasing its search engine ranking.

5- Content for mobile apps

By generating content that is optimized for the mobile experience, generative AI can aid in the creation of content for mobile apps. AI models can identify the types of content and formats that are most effective in engaging users on mobile devices, such as short-form videos or personalized notifications, by analyzing user behavior and app usage data. Furthermore, AI-generated content can be tailored to meet the unique needs of each mobile platform, such as:

Screen size (e.g., 320px — 480px for mobile devices)

Device type (e.g., smartphones, tables)

User interface.

This can help to improve the user experience and increase app engagement.

6- Personalized e-mails

AI models can identify customer preferences, behaviors, and purchase history by analyzing customer data and generating content tailored to their needs and interests. This can include personalized subject lines, product recommendations, and offers tailored to each customer’s unique journey.

So, we asked one of the generative AI tools, ChatGPT, to write e-mails for different companies in the healthcare industry to introduce a new product. Here is the prompt:

“A company wants to introduce their new product developed using VR technology that helps to understand Parkinson’s disease better; however, they don’t want to be generic in introducing their product to other businesses in the healthcare market, rather they want to send personalized e-mail to the companies. What would be some e-mail examples?”

5 best practices for implementing generative AI in copywriting 1- Fine-tune the model

To improve the precision and relevance of the generated content, fine-tune the generative AI model using a modest amount of brand-specific training data.

2- Generate ideas

Instead of depending solely on AI to generate the full piece of content, use the technology to provide ideas and inspiration for content development, such as brainstorming topics or angles.

3- Incorporate human oversight

Think of generative AI tools as a complement to human content writers rather than a replacement. Thus, include human editors to ensure the AI-generated content is correct, appropriate, and consistent with the brand’s messaging and style.

4- Track the performance of the model

To ensure that the generative AI tool is accurate and useful over time, it should be improved constantly by incorporating suggestions from human writers, tracking its performance, and upgrading its training data.

5- Avoid overreliance on the model

To guarantee that the material is relevant and effective, avoid relying too heavily on the generative AI tool. Instead, combine it with other tools and techniques, such as keyword research, market analysis, and customer feedback.

3 limitations of generative AI in copywriting

While generative AI has many benefits in copywriting, it has some limitations that may have a significant impact on the content produced.

1- Inadequate fact-checking process

Since AI models are only as good as the data they are trained on, written content generated by AI could not always be accurate or based on dependable sources. Due to inaccuracies or inaccurate information, a brand’s reputation or trust may suffer as a result.

2- Limited linguistic capabilities 3- Narrow understanding of the context

The ability of generative AI models to produce high-quality content does not imply that they fully comprehend the context in which the material is being created. This could lead to creating content that is off-topic or irrelevant to the audience.

If you have any questions on generative AI, don’t hesitate to contact us:

Begüm Yılmaz

Begüm is an Industry Analyst at AIMultiple. She holds a bachelor’s degree from Bogazici University and specializes in sentiment analysis, survey research, and content writing services.





Ln In Excel (Formula, Examples)

LN Function in Excel (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

LN in Excel

LN function in Excel is the mathematical trigonometry function used to calculate the natural logarithm of a number. LN denotes the logarithm statement, and as per the syntax, we need to put any number of logarithm values we need to find. The LN value of 1 is 0, and the LN value of e is 1, so each value coming after these will have an LN value of more than 0 if any number is used. We can also choose any exponential or raise to power value in the syntax.

Logarithm means, “How many of one number do we multiply to get another number?”

Example: How many 5s do we need to multiply to get 125?

Answer: 5 * 5 * 5 = 125. We need to multiply the number 5 three times to get 125. Therefore, the logarithm is 3.

Mathematically, we can write it as the below one.

The logarithm of 125 with a base number of 5 is 3.

A Relationship Between Logarithms & Exponents

Here exponent says how many times you need to multiply the base value.

Now, the logarithm equation becomes as the below.

5? =625

Therefore logarithm answers the question, “What exponent do we need for one number to become another”.

It is a built-in function in Excel that returns the natural logarithm of a given number.

Natural Logarithm: Base “e.”

e is an Euler’s Number that is equal to 2.718282. This is called a “natural logarithm”.

LN Formula in Excel

Below is the LN Formula in Excel.

Explanation of LN Function in Excel:

The LN function in Excel has one argument, i.e. number.

Number: The number that you give to get the natural logarithm number.

The natural logarithm of a number is the opposite of the EXPONENTIAL function. The below example illustrates the same.

Therefore, the exponential number of number 1 is 2.718282, and the natural logarithm number of 2.718282 is 1.

How to Use LN Function in Excel?

The LN function in Excel is very simple and easy to use. Let us understand the working of the LN function in Excel by some LN Formula examples.

You can download this LN Function Excel Template here – LN Function Excel Template

There is no day-to-day example to explain the LN function in Excel with live examples. In this article, I will brief you about how this works and the error types one by one.

Example #1

You cannot supply any negative numbers to the LN function. If you supply the negative number to this function, it will throw the error as #NUM. Any number that is equal to or less than the zero LN function cannot return the value against it.

By applying the formula in cell C2.

Similarly, it is applied to other cells in that column to get the desired output.

Example #2

You cannot supply the number zero too to the LN function. If you supply the number zero to this function, it will throw the error as #NUM. Similar to the negative number LN function requires the number to be greater than zero; otherwise, we will get a number error.

By applying the formula in cell C2.

So the result will be:

Similarly, it is applied to other cells in that column to get the desired output.

Example #3

You cannot supply anything other than a rational number to the LN function. If given so, it will give the error as #VALUE! We cannot supply any operators, symbols, etc. Anything other than a positive number, will give the error as #VALUE!

By applying the formula in cell C2.

So the result will be:

Similarly, it is applied to other cells in that column to get the desired output.

Example #4

The value we need to give to the LN function starts with anything greater than zero. Any number that is greater than zero it will give the natural logarithm number. In order to make LN work, we need to give a number that is greater than zero.

By applying the formula in cell C2.

So the result will be:

Similarly, it is applied to other cells in that column to get the desired output.

Example #5

Natural logarithm number works exactly the opposite of exponential function. This function is the inverse of the EXP function in Excel where =EXP (1) is equal to 2.718282 and =LN (2.718282) is equal to 1.

By applying the formula

The output will be :

Things to Remember

LN requires only positive numbers. The supplied number cannot be zero or negative.

To learn how LN works, first, you need to learn the basics of logarithms and refer to logbooks.

The supplied values should be a positive number and cannot supply any of the operators and symbol values. So it cannot be a non-numeric value.

The natural logarithm is related to the EXP function. In EXP, the value of a base number is raised to an exponential number, and in the logarithm, we need to know the exponential number to get the desired number.

Recommended Articles

This has been a guide to LN in Excel. Here we discuss the LN Formula in Excel and How to use the LN Function in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

Abs In Excel (Formula, Examples)

ABS Function (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

ABS in Excel

The ABS Function in Excel converts any negative number into a positive value. It is one such function we can use in any mathematical operation with some expectation of getting a negative value. The values obtained from the ABS function are the absolute values just by removing the minus (-) sign from the calculated value; for this, we need to apply the ABS function in the value or put the formula inside the ABS.

ABS Formula in Excel:

Below is the ABS Formula.

The Formula of the ABS function is very easy, and that includes only one parameter, i.e. number.

Number: This is the required parameter. The number you wish to get is the absolute number without its sign.

How to Use the ABS Function in Excel?

This ABS function is very simple and easy to use. Let us now see how to use the ABS Function with the help of some examples.

You can download this ABS Function in Excel Template here – ABS Function in Excel Template

Example #1

From the list of numbers, convert all the negative numbers to positive numbers using the ABS function.

Apply the ABS function in Excel to get the absolute numbers without its sign.

The result will be:

If you look at the positive numbers, it returns the same value. There are no signs of positive numbers, so it returns the number as it is.

Example #2

From the above example, we have learned how to convert negative to positive numbers. Now take the same example, but the thing is if the number is negative, convert it to positive and if it is not negative, show it as a positive number.

This is done by using the IF condition. IF condition is checking whether the given number is less than zero or not (If (A<0,). If the number is less than zero

Then it converts the negative number to a positive number by using the ABS function (ABS (A2))

And if the number is not negative.

Then the result will be shown as a “Positive Number”.

Example #3

From the below table, calculate the target vs actual report for the sales team. You are provided with the target and actual achieved number. You need to calculate the variance and calculate the variance percentage.

In order to calculate the variance, we can use the formula as = Actual – Target.

This would give us the variance value.

In order to get the variance % value, we use the formula =Variance / Target *100.

This will be given us a variance % value.

The problem with this generic formula is that we always get negative numbers if the actual value is less than the targeted value, affecting our variance percentage. Therefore, to eliminate these negative numbers, we can use the ABS function to get the absolute values.

I just applied the ABS function before I do the calculation of Actual – Target. The result is converted to the absolute value by ABS.

One more interesting thing here is as soon as we get a positive variance value, the variance percentage is automatically converted to positive percentages.

Example #4

Without using ABS, we can convert it into a positive number also. These are smart tricks for using Excel.

Convert negative numbers by multiplying them with -1.

Use Paste Special as an option.

Convert Negative Numbers by Multiplying with -1.

The basis of mathematics lies in understanding the signs of multiplication and those signs of multiplication are as follows.

Since we are trying to convert negative numbers to positive ones, we can use the last method, i.e. Minus * Minus = Plus

By using the above method, convert the below numbers to absolute numbers.

Multiply all these numbers by -1 to get the positive values.

Use Paste Special to Convert Negative Numbers to Positive Numbers.

Enter -1 in one cell and follow the below steps.

Step 1: Copy the value -1, which we have entered in cell D3.

Step 2: Now select all the negative values.

Step 3: Open and paste the special dialogue box using ALT + E + S and select the Multiply option.

Step 4: Now press ok. All the negative values converted to positive values in the same range, and the result will be the same as the below image.

Things to Remember

ABS can accept only numeric values. Anything other than the numeric value, the error will be #VALUE!

No changes will happen to positive numbers. Only negative values are converted to positive values.

VBA code to ABS function is chúng tôi (-650).

Sub ABS_Example

Dim MyValue as Double

MyValue = Application.WorkSheetFunction.ABS(-650)

Msgbox MyValue

End Sub

Recommended Articles

This has been a guide to ABS Function. Here we discuss the ABS Formula and how to use the ABS Function along with practical examples and downloadable Excel templates. You can also go through our other suggested articles –

A Complete Guide To Tensorflow Dataset

Introduction to TensorFlow Dataset

Basically, TensorFlow acts as a collection of different kinds of dataset and it is ready to use in another word, we can say that it is one kind of framework that is used for Machine Learning. The main purpose of the TensorFlow dataset is that with the help of the TensorFlow dataset, we can build the pipeline in a machine learning application.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

What is TensorFlow Dataset?

Profound learning is a subfield of AI or we can say that machine learning is a bunch of algorithms that is propelled by the design and capacity of the cerebrum. Basically, TensorFlow is used for the AI framework or we can say that machine learning framework and it directly gives the straightforward solution to implement the AI concept.

You can utilize the TensorFlow library due to mathematical calculations, which in itself doesn’t appear, apparently, to be all through the very novel, yet these assessments are finished with information stream charts. These hubs address numerical tasks, while the edges address the information, which as a rule are multidimensional information exhibits or tensors, which are imparted between these edges.

TensorFlow Dataset Example Model

Let’s see the example of the Tensorflow dataset as follows:

Datasets is another approach to make input pipelines to TensorFlow models. This API is considerably more performant than utilizing feed_dict or the line-based pipelines, and it’s cleaner and simpler to utilize.

Normally we have the following high-level classes in Dataset as follows:

Dataset: It is a base class that contains all the methods that are required to create the transformed dataset as well it also helps initialize the dataset in the memory.

TextLineDataset: Basically we need to read the line from the text file, so for that purpose we use TextLineDataset.

TFRecordDataset: It is used to read the records from the TFRecord files as per requirement.

FixedLengthRecordDataset: When we need to read the fixed size of the record from the binary file at that time we can use FixedLengthRecordDataset.

Iterator: By using Iterator we can access the dataset element at a time when required.

We need to create the CSV file and store the data that we require as follows: Sepailength, SepalWidth, SetosLength, SetosWidth, and FlowerType.


In the above-mentioned input value, we need to put it into the CSV file which means we read data from the CSV file. The first four are the input value for a single row and FlowerType is the label or we can say the output values. We can consider a dataset of input set is float and int for the output values.

We also need to label the data so we can easily recognize the category.

Let’s see how we can represent the dataset as follows:


types_name = ['Sepailength','SepalWidth',' SetosLength',' SetosWidth']

After the training dataset, we need to read the data so we need to create the function as follows:


def in_value(): ………………… return({'Sepailengt':[values], '……….'}) Class Diagram for Estimators

Let’s see the class diagram for the Estimators as follows:

Estimators are an undeniable level API that diminishes a significant part of the standard code you recently expected to compose when preparing a TensorFlow model. Assessors are likewise truly adaptable, permitting you to abrogate the default conduct on the off chance that you have explicit prerequisites for your model.

We can use two ways to build the class diagram as follows:

Pre-made Estimator: This is a predefined estimator’s class and it is used for a specific type of model.

Base Class: It provides complete control over the model.

Representing our Dataset

Let’s see how we can represent the dataset as follows:

There are different ways to represent the data as follows:

We can represent datasets by using numerical data, categorical data, and ordinal data, we can use anyway as per our requirement.


import pandas as pd_obj data_info = pd_obj.read_csv("emp.csv") row1 = data_info.sample(n = 1) row1 row2 = data_info.sample(n = 1) row2


In the above example we try to fetch the dataset, here first we import the pandas to implement the AI program after that we read data from the CSV file as shown, here we have an chúng tôi file and we try to read the data from that file.

The final output or we can say that result we illustrated by using the following screenshot as follows.


Similarly, we can display the second row same as above.

Importing Data TensorFlow Dataset

Let’s see how we can import the Data TensorFlow dataset as follows:


import tensorflow as tf_obj A = tf_obj.constant([4,5,6,7]) B = tf_obj.constant([7,4,2,3]) res = tf_obj.multiply(A, B) se = tf_obj.Session() print( se.close()


By using the above we try to implement the TensorFlow dataset, here first we import the TensorFlow as shown, and after that, we write the two different arrays A and B as shown. After that, we make the multiplication of both arrays and store results into res variables. In this example, we also add a session and after the complication of the operation, we close the session.

The final output or we can say that result we illustrated by using the following screenshot as follows.


Freebies TensorFlow Dataset

Basically, the Tensorflow dataset is an open-source dataset that is the collection of datasets we can directly use during the machine learning framework such as Jax, and all datasets we can set by using the TensorFlow as per requirement.

It also helps us to improve our performance.


From the above article, we have taken in the essential idea of the TensorFlow dataset and we also saw the representation of the TensorFlow dataset. From this article, we saw how and when we use the TensorFlow dataset.

Recommended Articles

We hope that this EDUCBA information on “TensorFlow Dataset” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

Text To Columns In Excel (Examples)


Text To Column option in Excel is available in the Data menu tab under the Data Tools section, which is used for separating text available in a cell or column to the columns by splitting them with different criteria.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Text to columns in Excel is used to split the contents of a cell into two or more columns using the fixed-width or delimiter method.

For instance, if you have a list of fruits and their prices written together in a single cell like “Banana ($0.85), Orange ($1.50), Apple ($1.75)“, then you can use the “Text to Columns” tool to split this data into separate columns for each fruit and its price.

Where is Text to Columns in Excel?

The “Text to Columns” option is present in the “Data tools” of the “Data” tab”.

You can use delimiter or fixed-width options depending on the data and how you want to split the text.





Delimited Splits text based on a specific character, such as commas, tabs, spaces, semicolons, hyphens, etc. If you have a cell with “Olivia Alex,456 Main Street, Austin, USA”, you can split it into name, address, and location. This method is quick and easy to use.

Fixed Width Splits text based on a specific character count, dividing the values into specific columns. If you have a 9-lettered name and want to split it at the 6th character. This method is useful when dividing text with a specific character count.

How to Use Text to Columns in Excel?

You can download this Text to Columns in Excel Template here – Text to Columns in Excel Template

Example #1 Delimited Option

Purpose: Split the given names into First Name and Last Name.

Consider the data below, which contains a few individuals’ phone numbers. Here, First Names and Last Names are present in a single column. We want to split each name into First and Last Names using the delimited option.


Note: If you do not insert another column after column A, then the other portion of data will overwrite in the adjacent column. For example, the first name will appear in Column A, and the last name will appear in Column B by replacing the phone number.

Note: In our case, a name is separated by space. So, we have to select the Delimited option.

Step 4: Select “Space” from the delimiters.

Note: This example has spaces as delimiters.

Note: A “Data preview” window at the bottom of the “Convert Text to Columns Wizard” dialogue box will display the result.  We can see how the delimiters will affect the data or, in other words, how the result will look.

Note: If you don’t select a destination cell, it will overwrite your existing data set in the first column with the first name and the last name in the adjacent column. Choose a different destination cell to keep the original data intact.

The result is displayed below. Here, the first name appears in column A, and the last name appears in column B.

Step 6: Make the column name more specific, as shown below.

Let’s take another scenario where you want to keep the original data intact and want to display First Name in Column B and Last Name in Column C.

Step 1: Insert two columns after column A.

Example #2 Fixed-width Method

Purpose: Split date and time.

The below data set contains details of the students who filled out a Google form. Column A includes data in date and time format, like 4/1/2023 at 9:58:34 AM. Here, we want to separate the date and time into columns, i.e. 4/1/2023 in one column and  9:58:34 AM in another.


Step 1: Insert a column between Column A & Column B.

Step 4: Create a break line in the desired position. Here, we have created a line between date and time.

Note: The instruction for column break is already given in the dialog box, like how to create, delete and move a broken line. The column break allows us to set the field width (character count) as how we want to separate the data. We can divide it into two columns, i.e. Date format in one column and time format (AM/PM) in another column format, or we can have a date in one column, time in another, and AM-PM in another one. We can also see how the data will look in the preview section.

The result is displayed below. Here, the time is separated from the date.

Example #3 Delimited Option

Consider the below example. Column B contains audit details along with the employee’s name and ID. Here, all data is in one single column. We want to display the employees’ IDs, first names, and last names in three columns. A hyphen and space separate the data in Column B, so we will use the delimited option to split it.

Step 1: Insert three columns between column B and Column C

Step 3: Select “Delimited” from the “Original data type”, as shown below.

Step 6: Select “General” from the “Column data format” and the “Destination” cell.

The result is displayed below. Here, the original data of column B is split into three columns, and we have named it ID, first name, and second name, respectively.

Things to Remember

The keyboard shortcut to access Text to Columns in Excel is Alt + A + E.

Use the delimited method when the text strings contain commas, spaces, semicolons, and other special characters.

Identify the correct delimiter while using the delimited feature. For instance, in “Karen, Wilson”, the delimiter is a comma (,).

Use the fixed-width method to separate text using character count.

A Data Preview window is at the bottom of the Text to Columns wizard dialog box. After choosing the format options, the data preview will display how your data will look.

Always insert columns equal to the number of data you want to separate.

In fixed width, create an arrow in the desired position to split the data.

The final result of the Text to Columns in Excel is static. You must repeat the process to get updated results if there are any changes in the original data.

Select the proper destination cell where you want the result. Otherwise, it can lead to overwriting of data in the existing column.

If you want the original data, make a copy or select another destination cell.

Assignment for You: 2-Minute Challenge!

You have got 2 minutes to put your skills to the test and see if you can solve this problem on your own. Don’t worry. You’ve got this! You are already familiar with all the steps, so let’s see how quickly you can complete this assignment.

To complete the assignment, please download this template.

You can download this Assignment Template here – Assignment Template

In the template, we have provided a list of usernames, followers, and engagement rates for an influencer campaign. Your task is to separate this information into different columns using the “Text to Columns” tool in 2 minutes.

Recommended Articles

This article has been a guide to Text to Columns in Excel. Here we discuss its uses and how to convert Text to Columns in Excel with some examples and downloadable Excel templates. You may also look at these useful functions in Excel –

Update the detailed information about Hyperlinks In Excel (A Complete Guide + Examples) on the website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!