IMAGE: Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 2019, 365
 TheExcelAddict.com

December 17, 2020
Hi fellow Excel Addict,
 
I messed up

Francis Hayes - Learn to work smarter with Excel at TheExcelAddict.comEmbarrassingly, I have learned that the tip I sent you last week (Easily Create A Hyperlinked Listing Of All Files In A Folder) doesn't work in Chrome, the world's most popular browser. Neither does it work in Microsoft Edge nor Internet Explorer.

How could I have missed that?

Well, I have been using Firefox for many years. I tried using Chrome as my browser a few years back and didn't like it, so I switched back to Firefox and have been using it ever since.

Any time I needed to create a hyperlinked listing for one of my folders, this process has always worked for me.

When I was writing the tip, I decided to try it in a few other browsers.

I copied my folder path into Internet Explorer. Instead of the folder listing opening in the browser as it does in Firefox, IE redirected me back to the folder in File Explorer.

When I copied the folder path into the Microsoft Edge browser, it gave me a hyperlinked listing the files in the browser. When I clicked the link, Edge offered to download the file. So I mistakenly made the assumption that everything was fine without copying the listing of links from the browser into Excel.

When I copied the folder path into Chrome, it too gave me a hyperlinked listing of the the files in the browser. Again, I mistakenly assumed that it was working.

However, as I discovered last week, the majority of my readers are using the Chrome browser and this tip doesn't work for them because the links get messed up when you paste them back into Excel.

There is an extra C:\ at the beginning of each hyperlink.

Hyperlink Path Error in Microsoft Excel 2007 2010 2013 2016 2019 365

Unfortunately Excel's Find and Replace can't be used to correct hyperlinks.

The only option I have ever used to fix bulk hyperlinks is a macro that I wrote many years ago.
Sub ChangeAllHyperlinkNames()
    Dim hLink As Hyperlink
    Dim oldTxt As String
    Dim newTxt As String
  
    oldTxt = InputBox("Find ...")
    newTxt = InputBox("... and replace with...")
        For Each hLink In ActiveSheet.Hyperlinks
            hLink.Address = Replace$(Expression:=hLink.Address, _
             Find:=oldTxt, Replace:=newTxt, _
             Compare:=vbTextCompare)
        Next hLink
End Sub

If you use macros, you can copy this macro into your Personal Macro Workbook, and use it to fix the hyperlinks in your listing.

For the above example you would need to replace C:\C:\ with C:\

Other than using a macro, it seems the only option to make this work is to use Firefox for this one task.

Do you have any other suggestions?

Francis Hayes - Learn to work smarter with Excel at TheExcelAddict.comWishing you a Happy and Safe Holiday Season

Sorry for that mess up and sorry for my rambling today but since we are only about a week away from Christmas, I want take a minute to wish everyone who is celebrating Christmas or any other holiday/festival over the next few weeks, safe and joyous festivities.

I will be taking a few weeks away from my newsletter for my Christmas break, but I will be back in the new year with more time-saving tips for you around the middle of January.

I do appreciate you taking time today to join me for another 'Excel in Seconds' tip.

Please help share my newsletter on your social media accounts — and with your colleagues or any other Excel users you know who want to get smarter with Excel.

Have a great day, keep safe and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com
 

Send Email. Connect With Customers. Grow Your Business.

With AWeber, you get all the email marketing tools you need to create and send beautiful and engaging emails. For a behind-the-scenes look at how you can use AWeber, sign up to our Test Drive email series:
 
Missed my last newsletter?

Click Here to View it Online


Having a positive attitude can help us in difficult times.
I hope today's quote will help foster a positive attitude in you today.

Quote of the Day

"If people are doubting how far you can go,
go so far that you can’t hear them anymore."

-- Michele Ruiz --
If you have a favourite quote, send it to me and I may post it in my newsletter.

THIS WEEK'S 'EXCEL IN SECONDS' TIP
 

Quickly Communicate Your Numbers Visually

To some people, numbers can be more easily interpreted when seen in a more visual manner.

Excel's Data Bars is a simple way to quickly show your numbers visually without having to go through the process of creating a chart or using some other graphical method.

Data Bars Before And After in Microsoft Excel 2007 2010 2013 2016 2019 365


To show Data Bars along with your numbers, you simply select the numbers and, from the Home tab, click Conditional Formatting, Data Bars and choose a color scheme.

Data Bars Step By Step in Microsoft Excel 2007 2010 2013 2016 2019 365

Once you have applied them, Data Bars can be edited using Home, Conditional Formatting, Manage Rules....

If you don't want the Data Bars to overlap your numbers, simply adjust the column width.

Prevent Data Bars Overlapping Numbers in Microsoft Excel 2007 2010 2013 2016 2019 365



To remove the Data Bars, select the cells with the Data bars, click Home, Conditional Formatting, Clear Rules, Clear Rules from Selected Cells.

To share this tip with your friends and
colleagues, choose one of these options...
 
Quickly Communicate Your Numbers Visually

 
Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are EVER not completely satisfied with anything I recommend, please let me know and you will get your money...GUARANTEED. You can't lose.
 
 
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630