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 [...]
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
Create, Modify & Remove Trusted Locations in Excel 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 [...]
read more

Unhide Multiple Worksheets in Excel

April 1, 2014
Thumbnail image for Unhide Multiple Worksheets in Excel 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 [...]
read more

Create a Personal Macro Workbook PERSONAL.XLSB

April 1, 2014
Personal Macro Workbook Personal.xlsb 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 [...]
read more

Display Missing Dates in Excel PivotTables

March 25, 2014
Thumbnail image for Display Missing Dates in Excel PivotTables 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 [...]
read more

Excel Custom Number Formats

March 21, 2014
Thumbnail image for Excel Custom Number Formats 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 [...]
read more

Excel 3D Financial Reports

March 12, 2014
Thumbnail image for Excel 3D Financial Reports 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 [...]
read more

Excel Designed Minecraft Cake

March 5, 2014
Thumbnail image for Excel Designed Minecraft Cake 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 [...]
read more

Create Barcodes With (Or Without) Excel VBA

February 27, 2014
Thumbnail image for Create Barcodes With (Or Without) Excel VBA 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 [...]
read more