Phantom Links in Excel Workbooks

Ever open up a worksheet and have an "Update Links" dialog box pop up and, even after searching, you can't find the link?

I have one worksheet that this kept happening in. Even trying to break the link wouldn't work.  Tom Urtis has provided a number of different methods to fix the problem and you can click here to see his solutions. He has a great tutorial on it.

If the first couple of his solutions don't work you and don't want to get into macros and you know it is a phantom link (so it won't impact your spreadsheet) then try my solution.

Click the Update Links icon in the dialog box  and Excel will display an  an Edit Links dialog box and it shows the data source. I tried breaking the link and I still got the Update Links message so I persevered.
Under the Edit Links dialog box is a Start Up Prompt icon.
Click on it and you can tell Excel to stop displaying the Update Links message when opening the file and to NOT update the links automatically.
It's not a perfect solution but at least you get rid of that annoying dialog box.

Go To Special

My primary use  for Go To Special  has been to select only the visible data in subtotals and pivot tables; however, it has a lot of other uses.
To learn more about this feature,click here to read my guest blogger's entry.

Creating a Gantt Chart in Excel

Cam't afford fancy software for project scheduling? Try using Excel.

The image below is the end result we will achieve.

In this example, we took each activity and provided a sideways bar to visually show when that activity occurred within the total project. 

To accomplish this we will be using some logic functions as well as conditional formatting.

The project example we are going to use is below:

To read the whole article please click here....

Copying Only the Cells that Contain Data

In my last blog entry I talked about using Go To Special to find blank cells. In this entry, written by guest Joe Helstrom, we cover using it to find blank cells as a useful way to only copy cells that contain data.

 Have you ever had a couple of columns of data that you wanted to combine into one column?  What do you do when there are blank spaces in some of the columns of data?  Many will cut and paste the data from one column to the other, then manually delete the rows containing blanks.  This begs the question; Is there an easier way?
Of course there is! One way is to use the GoTo Special feature in Excel.  This feature is located in the Home menu, under Find & Select on the far right hand side of the toolbar. 

Click here for an example of how to do this.

Deleting Blank Sheet Rows

Deleting Blank Sheet Rows

When you import or download data, you frequently end up with a lot of blank rows that are interspersed among the data. You could sort and then delete all the blank rows or you could filter all the non-blanks but, in addition to being time-consuming, sometimes it does not display the data the way you want.

If you select the column that you are interested in and then click on Find & Select and then select  Go To Special .. and  then select Blanks -  you will find that this is a more efficient way. Then select the dropdown arrow beside Delete on the Cell tab and select Delete Sheet Rows.  To see an example of how this works please click here.