How to Find Excel Phantom Links - Part 2

Some time ago I've written about some techniques to eliminate Phantom Links in Excel.
Well, the steps described in my previous article - Here - had always solved my problems... until 2 days ago!

While I was updating a workbook, I had to change a database source from 2016 to 2017 (different Excel file but with same structure), so I just clicked the Edit Links button and that Change Source. As soon as Excel had changed all the links to the 2017 database file, I unexpectedly realized that a link to the 2016 database file was still showing in the Edit Links dialog box!
I've tried every single tactic mentioned on my previous article but I just couldn't find and kill the "Phantom link". I was exchanging some thoughts with a colleague who mentioned a couple more tactics that could solve the problem.

• Search for formulas referencing external workbooks:
  Right-click on any sheet tab >> left-click Select All Sheets >> press Ctrl+F and in the Find What field enter  =[    or  !
  
  This will look through all sheets for formulas referencing external workbooks. You have assure that there are no hidden sheets.

• Conditional formatting formulas:
  Go in to Conditional Formatting >> Manage Rules  >> Show Formatting Rule for: this worksheet and remove the rules which refer to the external link

Excel Tip: Enter the same data in multiple cells at once

How to enter the same data into multiple cells of your worksheet at once ?

If you want to enter the same values into multiple cells of your worksheet at once, you can enter it into one and copy it into each of the other cells, however a quicker solution allows you to enter the same thing into all of those cells at once.

Just take the following steps:

• Highlight all of the relevant cells (you can use the mouse to highlight a range, or click on each of the individual cells while holding down the Ctrl key)
• While they are all highlighted, type what you want to enter in all cells and...
• Instead of hitting Enter, hit Ctrl + Enter
• The information will now appear in all of the selected cells.

That's it.


Do you like this free website? Please share this page on Google+ or Facebook.

Count number of UNIQUE values in Excel

Count number of UNIQUE values in Excel

Problem: I have the following data range with duplicate values in column C and I want to retrieve the number of unique entries to cell J2. What can I do ?

Well, you could obviously do it manually by using the Remove Duplicates function or a Pivot Table. The setback is that if you add new entries to the original data range you'd have to run the Remove Duplicates function or create/update the a Pivot Table again.

Solution: Add the following excel formula into cell J2 and it will automatically count the number of unique entries on range C2:C11.

=SUM(1/COUNTIF(C2:C11);C2:C11))

Press CTRL + SHIFT + ENTER.

The result formula in cell J2 will be:

{=SUM(1/COUNTIF(C2:C11);C2:C11))}


Note: The curly braces {} indicate that this is an array formula. Do no type these yourself. They will disappear when/if you edit your formula.


Explanation of the formula Step-by-Step:
The COUNTIF function will create an array, stored in Excel's memory, that looks as follows:

{3;1;2;1;1;3;1;2;1;3} - (three Jones's, one Kivell, two Jardine, one Gill and so on...)

This reduces to:
{1/3;1/1;1/2;1/1;1/1;1/3;1/1;1/2;1/1;1/3}

This array constant is used as an argument for the SUM function, giving a result of
1/3+1/1+1/2+1/1+1/1+1/3+1/1+1/2+1/1+1/3 = 7 (Jones; Kivell; Jardine; Gill; Sorvino; Andrews; Thompson)

Compare 2 Lists in Excel and Extract Records

How to compare 2 Lists in Excel and extract records using a array Formula.

There are several ways to compare two Excel lists. The VLOOKUP Function is probably the most used by most excel users. This video that I'v found at YouTube.com , shows how you can use an array formula to compare two Excel list and extract either the items that are in both list or the items that are only in one of the list.

It's a very interesting technique that can be quite useful for medium/advanced excel users.

How to Find Cells containing Formulas in Excel

Everyone that uses excel on their daily business certainly came accross with spreadsheets with several cells containing formulas. When these spreadsheets have not been created by us, it can be quite a nightmare to identify all the cells that contain formulas.

Excel "Go to Special" is a very usefull tool when it comes to find cells with formulas. Selecting the "Formulas" radio button will enable all cells that contain formulas. After that you can just change the background color or formating of the enabled cells which will stand out the cells with formulas.

Step by step to stand out the cells with formulas in your worksheet:

1 - Go to ‘Home’ tab > ‘Find & Select’ > ‘Go To Special’. Or just press  'F5' > 'Special' to open the "Go To Special" dialog box.

2 - Select the 'Formulas' radio button, as shown in the picture above and four checkboxes are now enabled (Numbers, Text, Logical, Errors). These checkboxes refer to the return type of the formulas

Example: If you check only the 'Numbers' checkbox then it will search only the cells which contain formulas whose return type is a number.


3 - Now click 'OK' and ALL cells that contain formulas get selected. Please note that in this example all cells with formulas have been selected because we've kept the 4 checkboxes marked.


4 - The last step which will highlight each cell in your spreadsheet that contains a formula is: without clicking anywhere on your speadsheet, change the background color of all the selected cells.


5 - Done. Now all formulas cells are clearly identified.

How to display the week number and year code in Excel

Sometimes we need to aggregate data by week and the Excel function - WEEKNUM works like a charm. But there are situations where we have data referring to diferent years and in these situations we have to associate the week number to the respective year.

For these situations we can combine WEEKUM and TEXT Excel functions. The formula would be as follows:

=TEXT(A1,"yy")&TEXT(WEEKNUM(A1),"00")

How to display the week number and year code, step by step:

1. Enter 01-01-2014 on cell A1 (date from which will be retrieved the week number and year code)
2. Enter the above formula in cell B2
3. Press Enter
4. B2 should now contain the value  - 1401 - the first two digits "14" refers to year 2014, and  the last two "01" refer to week number of date 01-01-2014

Examples:

Now you can play arround with the formula to retrieve year code and week number in the sequence you need.

Retrieve the week number followed by the year code:

=TEXT(WEEKNUM(B3),"00")&TEXT(B3,"yy")

Retrieve the week number and year code with a "-" in the between:

=TEXT(WEEKNUM(B3),"00")&"-"&TEXT(B3,"yy")

Save a workbook as read-only - Excel

Every now and then I'm asked how to save an Excel file as read only and/or password protected.
I'm not talking about workbook or worksheet protection but the entire file itself.


You have your file ready and you want to save it but password protected.
Hot to do it  - Step by step.

1 - File > Save As
2 - Select the folder where you want to save it
3 - Choose a name for the file
4 - Click the Tools button (check picture bellow)
5 - Select General Options...

As soon as you've finished Step 5,the following form will pop up and you'll have to decide how do you want to protect your file.

Option #1:  Excel to suggest "Open as Read Only" when you open the excel file

If you only want Excel to suggest to open the Excel File as Read-Only the next time to open the file... just select the "Read-only recommended" option. (see red arrow above). Do not enter any password!

Option #2: File Modify Password Protected

An excel workbook that is Modify Password Protected can be opened but cannot be edited unless you enter the password.

Here is how to do it, step by step:
1 - Enter your passwod in the first empty field - "Password to modify:".
2 - Click OK
3 - A new form will appear, so that you can confirm your password
4 - After entering the same password that you have chosen in the previous step, click OK
5 - Finally, click Save

Option #3: File Open Password Protected

This is the strongest file protection option that you can have. Setting your excel file password protected will  assure that nobody can see see its content unless they enter the password to open it.

Here is how to do it:
1 - Enter your passwod in the first empty field - "Password to open:".
2 - Click OK
3 - A new form will appear, so that you can confirm your password
4 - After entering the same password, click OK
5 - Finally, click Save

Next time you try to open this file, you'll need to enter the password, otherwise it won't open.


Note: Both Options #2 and #3, in case you  have selected the "Read-only recommended" option, Excel will ask if you want to open the file as read-only! Click "No" in case you want to be able to make changes in the file.

Double click formula to follow link?

Some Excel users often link several workbooks by using formulas that references a cell in another file.
When there is a need to follow the formulas arround, the least experienced users will open the several linked workbooks manually but most of the medium/high experienced users will just use the double click function.
So, what's this double click function I am talking about ?

Imagine the following scenario:

• You are working on Workbook_A which Sheet1/Cell A10 uses a value from Workbook_B/Sheet1/Cell C20; 
• You are working on Workbook_A and Workbook_B is closed;
• If you double click Workbook_A/Sheet1/Cell A10 with your mouse left button it will open the formula for editing! So, how can you open the source file (Workbook_B) with a double click on Workbook_A/Sheet1/Cell A10 ?

• In case you are using Excel 2010 or higher go to File > Options > Advanced > Untick the option for "Allow editing directly in cells"
• Now, go to Workbook_A/Sheet1/Cell A10 and double click it with your left mouse button!
• There you go! Workbook_B is now open and Sheet1/Cell C20 is selected.

PROS:

This excel "trick" can be quite helpfull for those Excel users that usually work with several linked workbooks because it will save all that time that would be needed to search for the source file, open it and finally find the right cell.

CONS:

Well, there are two cons for this "trick":

1. While the "Allow editing directly in cells" option is unticked you won't be able to edit the cell just by double clicking it. You have to use the F2 button to edit cells.
2. If you have a comment as well as a formula, it will open the comment instead! You can overcome this CON with a "floating" comment using Data Validation.