In your job, are there Excel tasks that need to be done at month end? Here’s how I use a hidden hyperlink to help with my month end routine.
Make Month End Entries
In one of my workbooks, I have to add a couple of entries on a worksheet, before I start with the new month’s entries.
Sometime (frequently!), I forget to do that. That made extra work for me, because I had to go back, find the numbers, and enter them later.
So, to remind myself about that month end step, I made a hidden hyperlink in the workbook.
Start a Hyperlink
To create the Excel hyperlink, I used the Insert Hyperlink command, on the Excel Ribbon’s Insert tab.
- Note: You can also use the Insert Hyperlink keyboard shortcut – Ctrl + K
Insert Hyperlink Dialog Box
When the Insert Hyperlink dialog box opened, I followed these steps:
- First, I chose the option, “Place in This Document”
- Next, I clicked on the MonthEnd sheet name
- I left cell A1 as the cell reference, but you could type a different cell address.
- At the top, for Text to Display, I typed, “Add Month End Entries”
- That text will show in the hyperlink cell,
- It’s more helpful than showing the address it’s linking to!
- Finally, I clicked OK, to close the Insert Hyperlink dialog box.
Hide Link with Conditional Formatting
It’s easy to ignore Excel messages, if you see them all the time!
So, to make this hyperlink catch my attention at the start of the month, I’ll use conditional formatting on the hyperlink cell.
- On the first day of the month, the hyperlink will be visible
- Every other day of the month, the hyperlink will be hidden
Format Hyperlink as Hidden
First, I formatted the cell with white font, and white fill colour.
In the screen shot below, the hyperlink still works, but that all-white cell won’t catch my eye..
Add Conditional Formatting
Next, I added a conditional formatting rule, to show the hyperlink on day 1 of each month.
With the hyperlink cell selected, I used a formula in Conditional Formatting:
- =DAY($B$2)=1
So, if the date in cell B2 is the first day of the month, the conditional formatting will be applied.
The preview in the screen shot below shows the formatting that I selected:
- pink fill colour
- bright blue font, with bold text
See Hyperlink on Month Day 1
In the next screen shot, the date is October 1st, and the hyperlink is visible on the worksheet.
I should definitely catch my attention, with that pink fill colour!
Then, I just click that link, and add the month end entries, instead of forgetting about that step/
Create Hyperlink with Drag & Drop
Another quick way to create a hyperlink with with the “drag and drop” trick, like I did in the video below.
There are written steps on the Excel Hyperlinks page on my Contextures site.
More Hyperlink Tips & Examples
See more HYPERLINK tips and examples on my Contextures website. That page also has videos, and sample files to download.
And there are many Conditional Formatting Examples on my site too!
________________
Hide Excel Hyperlink-Show Monthly-Conditional Format
______________