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 🙂
INDEX, What’s the big deal?
The big deal is the INDEX formula is simply AWESOME! Put it together with MATCH and you have an even powerful formula which I call the INDEX + MATCH combo.
Transposing Data with INDEX / MATCH combo
Transposing Data with INDEX / MATCH combo what’s this all about? OK imagine this – you have done the right thing as a budding data analyst and created your sales
Using Array Constants in Formulas – OR logic
This is a short PS post to my earlier post on using Array Constants in Formulas basically to simplify them. The focus of the earlier post was avoiding Nested IFs
Validation Dropdown lists based on Table Columns ranges
Update 12/12/2021 Since writing this post in 2017 Validation Dropdown lists based on Table Columns ranges, Excel now has new revolutionary dynamic array formulas that really has overtaken all the
Re-usable Print Macro
If your application needs to print dynamic reports, then ideally to save tedious manual print setup, you need a re-usable print macro. If your application needs to print several dynamic
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
No more gaps – a formula to remove spaces in lists or empty rows in ranges
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
Convert a Formula to Values
This is a very simple, but I think very practical little tip. You need to convert a formula to values in cell or a range of cells to values. I
Learning to use Pivot Tables
This is a general post for anyone out there not using Pivot Tables. Do yourself a favor, you need to! I am also going to do a similar post shortly
Using Array Constants in your formulas
Array constants can help simplify 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
Dependent Dropdown using INDEX
Update 12/12/2021 Since writing this post in 2017 about using a method to create a dependent dropdown using INDEX, Excel now has new revolutionary dynamic array formulas that really has
Import Data from a pdf document
Who has to 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