How to Break Links in Excel

You might have come across a situation in which you maintained an external link to another Excel Workbook and somehow that Workbook gets deleted or replaced or that information is no longer required to be updated. Then every time you open the Workbook it shows the Broken Link Error.

You might be thinking how to fix the same?

So, in that case, you need to break the links in Excel in your original file.

Breaking existing links in Excel 2016 can be achieved by selecting Data Tab on the Toolbar and then clicking on Edit Links (highlighted) in Connections group. Refer below image


Once you will click on Edit Link Excel will show up all the external references used in the Workbook.

You can select any of the source and click on Break Link. If you need to select all the links at once press Ctrl + A else hold the Ctrl Key and select multiple links as required.


The Edit Links button will appear disabled if there are no external references present in your Excel Workbook.

If you have defined a name for the link in your Workbook, then on breaking the link the defined name will not get removed automatically. You need to remove it separately by Name Manager button present under Formula tab – defined names section.

Click on the Name Manager will open the dialog box as shown in image below with all the Links defined with a Name. You need to select the entry and click on Delete button (highlighted)


Let us now understand this process by referring to an example of Worksheet Links. This will be useful for those who are new users to Excel and still in the learning phase.

Consider a case of an IT Company where a Manager needs to update Total Project Value by referring Project Value Realization Sheet. The data of Project Value Realization Sheet is linked with Cost Savings Sheet. Whenever any employee updates the Projected Cost in Savings Sheet it reflects in Project Realization Sheet.

Refer below image of a folder containing specified files


Project Realization Sheet contains month wise breakup of the savings figure along with total savings achieved. Note – The External Reference link (highlighted) in image below to the other workbook named – Cost Savings Sheet in the Cell D6 and similarly for Cells D7 and D8 respectively.


Cost Savings Sheet contains Project wise monthly saving data which is being updated by different employees. It also contains the Total savings for all the months in Cell F6 which is an external Reference to Cell D10 of Project Value Realization Sheet.


Both the files are at a specific location inside a folder. Suppose if the Cost Savings file gets moved to other location or deleted. You will get below message while opening the Project Realization Sheet.


Either you can continue or try editing the links. Once you click on Edit Links you will observe below screen showing Error: Source Not found


Now to remove this error message completely from appearing every time you open the Workbook you need to Break Link. Observe the button of Break Link in above screen. Once you will click on Break Link, system will give you a warning that any external references will be permanently converted to their existing values. This means that the data will no more get updated once you change the referenced sheet.


On clicking Break Links, Excel will remove the selected reference from Workbook. Observe the Cell D6 contains no external reference now. Refer below image


In this way, you may Break as many links present in a Workbook by simply selecting the links or selecting all the links at once.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

You might have come across a situation in which you maintained an external link to another Excel Workbook and somehow that Workbook gets deleted or replaced or that information is no longer required to be updated. Then every time you open the Workbook it shows the Broken Link Error.

You might be thinking how to fix the same?

So, in that case, you need to break the links in Excel in your original file.

Breaking existing links in Excel 2016 can be achieved by selecting Data Tab on the Toolbar and then clicking on Edit Links (highlighted) in Connections group. Refer below image


Once you will click on Edit Link Excel will show up all the external references used in the Workbook.

You can select any of the source and click on Break Link. If you need to select all the links at once press Ctrl + A else hold the Ctrl Key and select multiple links as required.


The Edit Links button will appear disabled if there are no external references present in yours Excel Workbook.

If you have defined a name for the link in your Workbook, then on Breaking the Link the defined name will not get removed automatically. You need to remove it separately by Name Manager button present under Formula tab – defined Names Section.

Click on the Name Manager will open the dialog box as shown in an image below with all the Links defined with a Name. You need to select the entry and click on Delete button (highlighted)


Let us now understand this process by referring to an example of Worksheet Links. This will be useful for those who are new users to Excel and still in the learning phase.

Consider a case of an IT Company where a Manager needs to update Total Project Value by referring Project Value Realization Sheet. The data of Project Value Realization Sheet is linked with Cost Savings Sheet. Whenever any employee updates the projected Cost in Savings Sheet it reflects in Project Realization Sheet.

Refer below image of a folder containing specified files


Project Realization Sheet contains month wise breakup of the savings figure along with total savings achieved. Note – The External Reference link (highlighted) in an image below to the other Workbook named – Cost Savings Sheet in the Cell D6 and similarly for Cells D7 and D8 respectively.


Cost Savings sheet contains Project wise monthly saving data which is being updated by different employees. It also contains the Total Savings for all the months in Cell F6 which is an external Reference to Cell D10 of Project Value Realization Sheet.


Both the files are at a specific location inside a folder. Suppose if the Cost Savings file gets moved to other location or deleted. You will get below message while opening the Project Realization Sheet.


Either you can continue or try editing the links. Once you click on Edit Links you will observe below screen showing Error: Source Not found


Now to remove this error message completely from appearing every time you open the Workbook you need to Break Link. Observe the button of Break Link in above screen. Once you will click on Break Link system will give you a warning that any external references will be permanently converted to their existing values. This means that the data will no more get updated once you change the referenced sheet.


On clicking Break Links Excel will remove the selected reference from Workbook. Observe the Cell D6 contains no external reference now. Refer below image


In this way, you may Break as many links present in a Workbook by simply selecting the links or selecting all the links at once.

Hope you understand this feature now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!

How To Add Text After A Formula In Excel

You might think that creating and using a drop down list in Excel is only something for the “experts” because it’s too complicated. Actually, creating and choosing items in a drop down list can be really easy, and there’s no programming knowledge needed. Today we’re going to show you how you can easily and simply create a drop down list in Excel and some practical ways you can use them.

Why would you want to create and pick items in a drop down list?

Drop down lists are really helpful when you often enter the same information in a column. Let’s say you keep track of your bills in Excel, and you have a column for whether or not it’s been paid. You could use the values of Not Paid, Payment Sent and Cleared. You can use drop downs when tracking elements of a project or work that you need to get done. Or, you can use it for the kids’ names on a chore list. Drop down lists can make using your spreadsheet faster, can keep you more organized and can reduce typing mistakes.

Drop down lists are actually one of the most simple tools to use in Excel, but they are more of a hidden feature since they can’t be accessed by a simply labelled single click. This is why so many people haven’t attempted to use them, instead typing the entries to each cell when they could make their lives so much easier with drop downs.

This feature is useful in business settings, for simple home management, financial planning and especially for entrepreneurs or freelancers who tend to have projects with many moving parts.

When you want a breakdown of your data in a visual format, you can use your drop down entries to create a pivot table. It is pretty easy too, and we’ll cover that in another article.

Creating your first drop down list

Be patient with yourself for the first few times you try this. The steps aren’t intuitive until you get used to it, but when you do, it will take you less than three minutes. We’ve tried several methods to create drop down lists and this one is the easiest since it involves the least amount of manual typing or different commands that you may be unfamiliar with.

We’ll make the drop down list with the Data Validation tool.

To begin, enter the values you want in your drop-down list in one column in a sheet. We are going to use the kids’ chores as an example here. By the end of this tutorial, you’ll be able to create your own drop down lists and duplicate them.

Next, highlight all items in the chores you have listed and name the list. Naming the list is done by simply clicking the box that says A13 in the image above and Chores on the image below and typing in the name of your list, in this case, Chores.

Next, select the cell where you want your drop-down list to be. After it’s selected we’re going to go to the Data Validation option in the Data tab. After you choose Data Validation, you will see a dialog box like the one below.


In this box, you will then be able to choose your “Validation criteria.” For this example, choose List from the selections.

The next step is to use the Chore list name that we created for the cells that had the chore names in them. Make sure to UN check the box that says Ignore Blank. The box with In cell Drop down should remain checked, since this is how you get to use this cool feature.

Simply type = Chore in the Source box, as in the example in the below image.

You can even add a message to prompt yourself or others to choose an option. This is done in the Input Message tab. It allows for a long message, but remember the small space that it will be displayed in when you type out what you want it to say. The display box will grow if more text is added, in the case of creating instructions, or more extensive notes. For this project, let’s keep it short.


You’ll also have to type Chore in the Title box to make sure it displays correctly.

Now click OK!

You can see in the below image that the drop down box has been created and when the cell that contains it is selected, the desired message is displayed in a small yellow box. Note that the small arrow to the right of the cell that allows you to choose options is only visible when the cell is selected.

What’s awesome is that you only need to go through that process once if you’re going to re-use the same list. You can simply select that cell that has the drop down list in it, copy it and paste it in another cell.

When you’re ready to assign the tasks to the kids, select the cell that the list is contained in. After it’s selected and you can see the little arrow to the right of the cell, just click the arrow. This will bring up all the selections in the list. You can choose any of the options that are available, but once you click the arrow, you can’t leave the cell blank unless you included a blank cell in your previous list of chores.

You can get creative in any way you’d like with your drop-down lists. This is an excellent way to have your response options already filled in and all you need to do is make one click.You can even have multiple lists on one spread sheet. For example, you can create another list of how well the kids did their chores, like Great, Done and Please Redo.

When you’re ready for a more advanced trick (it’s not that advanced!) you can make your lists on another tab in your workbook, so the original list is no longer visible on the same page as where you have your drop-down lists.

How to Create a Spreadsheet in Excel

You might have heard a lot about Microsoft Excel software or seen financial or business analysts working on the same making you think that it’s a tough thing to conquer. Well, if you think so it is absolutely wrong even you can be the master of Excel by using Microsoft Support or referring to our online Blog posts along with some self-practice.

Microsoft Excel is the most widely used spreadsheet software across the world. You need to have a Microsoft Excel software license or if you are a new user you can download Excel software – trial version for a limited period along with a Computer running Microsoft Windows. We will be using Microsoft Excel 2016 version for this post.

So, are you ready to create your first Spreadsheet?

Now let us begin our tutorial for step by step activities involved in the process. Firstly, you need to open the Microsoft Excel Application from the start menu or you can even type command excel.exe in the Windows Run menu which can be enabled by pressing Windows key + R

Refer below images



You will observe that Excel application is starting up for use and you will get the welcome screen displaying different workbook templates.



You need to select Blank Workbook from the available templates and you will observe a blank spreadsheet with Columns and Rows defined.


Columns are labelled with capital letters A, B, C, D and so on (Refer red box in below image) whereas Rows are labelled with numeric 1,2,3,4 and so on (Refer Green box in below image) till last limit of the spreadsheet.

Refer below image


The empty space seperated by grid lines are called as Cells. Each cell is uniquely identified by its address consisting of Column Name and Row Number. The first cell in spreadsheet is identified as A1 where in A represents Column A and 1 represents Row 1. Similarly, in the above image highlighted cell will be termed as E8. You can also refer the cell address of any cell by selecting it and refering the field above column A.

If you want to create another spreadsheet then you need to click on File menu and select New from the menu. You will get the same Welcome Template screen from where you can select the Blank Workbook or desired template.

Refer below image


On the upper side of the spreadsheet you are provided with a Toolbar having various tabs such as Home, Insert, Page Layout, Formulas, Data, Review and View. Each Tabs are further divided into Groups. Each Group has it’s own set of buttons required for different features.

Refer image below


Check the first group in Home tab – Name of Group is Clipboard and it contains tools such as Paste, Cut, Copy and Format Painter. Similarly, other tabs have their own set of groups and functionalities.

Now let us proceed with entering some data on our newly created spreadsheet.

You can prepare your grade sheet on Excel. Type in Name at Cell A1, DOB – Cell B1, Class – Cell C1.

Mention your name, date of birth, class in cells A2, B2, C2 respectively. Now in Cell A5 type Subjects followed by subject Names from Cell A6:A9. Enter the heading as First Sem, Mid Sem, Final Exam in cells B5, C5, D5 respectively. Enter Marks in cell range B6:D9 for each term exams.

Refer below image


You can observe that some of the text (Subject Names) is overlapping to column B. So, in this case you need to increase the column width by simply dragging the line between Column A and B.

Refer below image


Now if you want to total your marks enter below command in the cell B10

=B6+B7+B8+B9

This will add up all the values in defined cell. Similarly write command in Cell C10 ( =C6+C7+C8+C9) and D10 ( =D6+D7+D8+D9) to get total of each term exams.

Refer below image


So, you prepared your first spreadsheet and now you need to give a name to your spreadsheet and save it on your computer. Click on the File tab and select Save As. You will observe the screen showing options to save it in One Drive or on your local PC marked as This PC. Select the option of This PC and desired folder to save file, give your file a name such as My First Spreadsheet and click on Save.

Refer below image


The name of your spreadsheet will appear on the top of the Toolbar (Highlighted).


So now you are Ready with your first Spreadsheet!!

Hope you understand the process now and do not forget to try out yourself for sample case as described in the post.

Please comment for any queries you have or any valuable suggestions.

Happy Learning!