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 🙂
Google Sheets and Google Forms
It’s good to check out how Google Sheets are doing every now and then. In my view, it just isn’t as powerful a tool as Excel. Not sure how to
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
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
VBA code to check range for specific characters
Update 11/08/21 Well two interesting recent developments have occurred regarding this post on VBA code to check range for specific characters. Microsoft MVP Rick Rothstein kindly contacted me about much
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