Microsoft Office Training — Microsoft Office Online Training

Create Hyperlinked List of Files Using VBA

Create Hyperlinked List of Files in a Folder Using VBA

by Philip Treacy on April 16, 2014

So you have a load of PDF’s, or scanned documents, or some nice pictures of your stamp collection*, and you want to get this list into Excel. But you also want to be able to click on that file name/link and have the document/image open up in Excel. Easy. * I don’t have a stamp [...]

Macro Enabled Excel Templates

by Philip Treacy on April 9, 2014

in Excel VBA

What You Can Do With Macro Enabled Templates This is up to you. Using a template allows you to design a workbook layout to look how you want. Using macros gives you the power to customize that template. Let’s say you are creating invoices and each invoice has a new, unique number. VBA can keep [...]

Trusted Locations

April 9, 2014

If you want to Excel to stop asking you if you want to enable macros every time you open a macro enabled workbook, you should think about using a trusted location. What’s That? A trusted location is a folder either on your computer or the network that you have told Excel you trust. Basically you [...]

Unhide Multiple Worksheets in Excel

April 1, 2014

I’m sure most of us have experienced frustration at Excel’s refusal to allow us to unhide multiple sheets at a time. This is no April Fools’ joke! I know it’s seems illogical to allow the hiding of multiple sheets but not the un-hiding. Ho, hum….here are a couple of easy fixes: 1. Custom Views A [...]

Create a Personal Macro Workbook PERSONAL.XLSB

April 1, 2014

When you create a macro in Excel, by default, the macro works only in the workbook that contains it. But if you need to use a macro in multiple workbooks what do you do? You can use the PERSONAL.XLSB workbook. This is a hidden workbook into which you can store macros for use in any [...]

Display Missing Dates in Excel PivotTables

March 25, 2014

Q: How do you get the PivotTable to show the missing dates in your data? A: Not as easily as it should be, but here are a couple of workarounds you can use. Option 1: If you don’t care how Excel formats your dates The limitation of this option, as you will see, is that [...]

Excel Custom Number Formats

March 21, 2014

One of the most useful features in Excel are custom number formats yet they remain largely underused. In my Excel TV interview this week I shared a tip (at 52min) which uses Custom Number formats inside the TEXT function, and coincidently Jordan Goldmeier (of Excel blog; Option Explicit VBA) also used Custom Number formats in [...]

Excel 3D Financial Reports

March 12, 2014

Last week I wrote about how I used Excel to design a Minecraft cake. The response to that post was bigger than I expected and it was nice to know that many of you use Excel for extraordinary things too. One of the emails I received was from my friend and Excel extraordinaire, Roberto Mensa [...]

Excel Designed Minecraft Cake

March 5, 2014

Each year our sons choose a cake they want me to make for their birthday. Now, I am no baker and their cake of choice is usually something elaborate they find on the internet, and from there I talk them down to something I think I can manage. Last weekend our eldest son, Conor, turned [...]

Create Barcodes With (Or Without) Excel VBA

February 27, 2014

Barcode Types & Uses There are LOTS of barcode types used for a multitude of things. Various internationally recognised standards exist (and some not standardised) for things like retail stock tracking, mail and parcel tracking, ISBN numbers, warehousing, pharmaceutical packaging and many other uses. Like creating secret spy messages to leave pinned up around the [...]