My blog

My Blog page’s theme is to produce practical blog posts from my Excel consulting with large and small business clients from varied industries. These  topics include spreadsheet design, choosing a sound approach to an Excel based solution, learning from mistakes, VBA programing (or macros), Tables, Pivot Charts, Pivot Tables, advanced formulas, Pivot Query, PowerPivot, Excel User Forms, Excel data cleaning and manipulation, conditional formatting, user friendly features and more.

Hope you find them useful 🙂

Re-usable Print Macro

If your application needs to print dynamic reports, then ideally to save tedious manual print setup, you need a print macro. If your application needs to print several dynamic reports

Read More »

Monitoring the Workbook Environment

This post is about monitoring the workbook environment when working on key workbooks or workbook applications especially if they involve VBA, which is my approach to developing key workbooks for

Read More »

Convert Formula to Values

This is a very simple, but I think very practical little tip.   You need to convert a cell or a range of cells to values.  I don’t know about you,

Read More »

Using Array Constants in your formulas

I find I use this kind of formula a lot… =IF(AND(B5<>{“A”,”B”,”C”,”D”}),”Yes”,”No”) Instead of: =IF(AND(B5<>”A”,B5<>”B”,B5<>”C”,B5<>”D”),”Yes”,”No”) … and imagine if you want to test 20 items for B5 is not equal to? 

Read More »

Dependent Dropdown using INDEX

Update 12/12/2021 Since writing this post in 2017, Excel now has new revolutionary dynamic array formulas that really has overtaken all the workarounds for better dropdown lists we used to

Read More »

Import Data from a pdf document

Many consulting jobs involve taking data from other systems or webpages as text format downloads in  csv or txt files easily ‘imported’ by Excel by just opening the file and

Read More »

Exporting to PDF

I had a small, interesting job last week where the client had an .xlt template with existing in-house VBA to let the user save certifying documents within the an .xls

Read More »