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 🙂
Autocomplete Workaround for Validation Dropdowns
As might know there is no autocomplete functionality or any other features such as different font sizes with good old Data Validation cell dropdowns. On a client’s project I worked
Rediscovering templates for new workbooks and worksheets
I have been having a 1st World problem. For as long as I can remember, I create a new worksheet and the default cell vertical alignment is set to bottom
Personal Macros – Personal.xlsb or Add-In.xlam?
My last blog was on accessing personal macros from my custom ribbon. This blog is about where to store these personal macros. These personal macros are the ones that save
My Custom Ribbon using Formulas to Create XML code
Update 11/08/21 I received an email from Clem who has written an awesome Add-In called Ribbon Icons which looks like below, to address the painful process I whinge about below
VBA to Change Pivot Table Source
I recently worked on a project that had three distinct and large datasets for different geographical areas in three workbooks. Starting with the first workbook and data set in a
Tip for excluding Outlier data in Pivot Charts
Last week I implemented Pivot Charts for a client with three very large data sets, one of which was 30K rows and the other two being > 10K rows. There
Tip for creating syntax in VBA for Worksheet formulas
Anyone who has used VBA to create formulas in cells knows it can be a little tedious and tricky to get the syntax right for Worksheet formulas. Tricky because string
Check range for specific characters – Customizing the Ribbon
Update 11/08/21 Well two interesting recent developments have occurred regarding this post! Microsoft MVP Rick Rothstein kindly contacted me about much more elegant code than I have below, trying to
Use VBA Arrays to gather messy data
This post is about a common business problem of grabbing messy data from a transactional document and updating a data set to collect history. So in this post we will
Replace All – take care with using it
There is a dangerous little trap in using Find & Select dialog on the Home Tab and then using Replace All. I use this a lot and most of the
Save time using Excel Templates to upload to your accounting software – Part B Using Autofilter for bank entries
So this post is the 2nd part of my recent post on how Excel can automate data entry to your accounting system. See here for Part A. This example concerns automating
Using Excel Templates to upload to an accounting software – Part A The Cash Book
I have been putting off blogging about this topic because it’s unwieldy to explain and involves work done many years ago that I would do better now. I cringe a