Category: Formulas

Alternatives to Conditional Formatting

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. 

Tagged with: , , , , , , ,

Entering a relative formula in VBA using the macro recorder

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

Tagged with: ,

Using a Table Name prefix for productivity

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

Tagged with: , , , ,

My Custom Ribbon using Formulas to Create XML code

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: , , , , , , , , ,

Save time using Excel Templates to upload to your 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 bit seeing an Offset formula now which I would only

Tagged with: , , , , ,

Using INDEX to help you dynamically calculate a moving average

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

Tagged with: , ,

More Useful Array Constant Formulas

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

Tagged with: , , ,

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

Tagged with:

Using Array Constants in Formulas again – 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 but what about simplifying OR logic situations. Try… =OR(B5={“A”,”B”,”D”}) will

Tagged with: , , ,

Validation Dropdown lists based on Table Columns ranges

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,

Tagged with: , , ,

Monitoring the Workbook Environment

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

Tagged with: , , , , , , , , ,

No more gaps – a formula to remove spaces in lists or empty rows in ranges

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

Tagged with: , , ,
Top