In my recent post of Fix duplicated Conditional Formatting Rules , I mentioned you should be careful using Conditional Formatting in situations where you have thousands of rows of data or calculations as this can be a drain on performance. …

In my recent post of Fix duplicated Conditional Formatting Rules , I mentioned you should be careful using Conditional Formatting in situations where you have thousands of rows of data or calculations as this can be a drain on performance. …

I did this very thing today, so thought I would share this tip with you as it saves me a lot of time. Say you wanted to have this formula go into column 8 of the range you created in…

A few years ago I picked up on this gem of a tip of Dick Kusleika in his blog at his The Daily Dose of Excel site on using the Table Name prefix of “tbl”. If you are not dropping…

I recently finished a pet project of creating a custom ribbon in XML driven by an Add-In to access my personal macros. These are the ones you collect over the years and use in your day to day work to…

Tagged with: Add-In, CHOOSE, custom Ribbon, CustomUI, Excel Automation, Personal.xlsb, Ron de Bruin, Spreadsheet Guru, SUBSTITUTE, XML

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 bit seeing an Offset formula now which I would only…

I have blogged before about how much I love INDEX (INDEX what’s the Big Deal ). Its powerful, nimble (non volatile so easy on performance load) and just makes so much sense to me given we are working with a…

I have been recently blogging on Array Constants (see below links) and how useful they are for simpler, more maintainable and readable formulas. ttps://www.theexcelfactor.com/using-array-constants-formulas-logic/ https://www.theexcelfactor.com/using-array-constant-list-formulas/ Also I often use SUMIFS which from the top of my head came with Excel…

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. Any budding data analysts out there I urge you to…

Tagged with: INDEX+MATCH

Up until quite recent versions of Excel (I am guessing a little here because I only have Office 365 so always have the latest and forget when I discovered this) if you wanted a dynamic list for your Validation Dropdowns,…

This post is about monitoring the workbook environment for key workbooks or workbook applications especially if they involve VBA, which is my approach to developing key workbooks for clients. You wouldn’t do this for an ad hoc or quick and…

Sometimes you just need to get rid of the gaps in lists! Perhaps you want to use the list for a validation dropdown and you want no gaps in the list so it’s easier to use. Perhaps you just want…

Top