Category: Formulas

Here is a quick but really useful coffee break tip. I used this handy INDEX + MATCH based formula the other day and thought it was a good one to share… Say you have this typical accounting situation where you …

Dynamic sum formula for summing between two dynamic columns Read More »

Tagged with: ,

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.  …

Alternatives to Conditional Formatting Read More »

Tagged with: , , , , , , ,

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 …

Entering a relative formula in VBA using the macro recorder Read More »

Tagged with: ,

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 …

Using a Table Name prefix for productivity Read More »

Tagged with: , , , ,

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 …

My Custom Ribbon using Formulas to Create XML code Read More »

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

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 …

Save time using Excel Templates to upload to your accounting software – Part A The Cash Book Read More »

Tagged with: , , , , ,

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 …

Using INDEX to help you dynamically calculate a moving average Read More »

Tagged with: , ,

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 …

More Useful Array Constant Formulas Read More »

Tagged with: , , ,

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 …

INDEX, What’s the big deal? Read More »

Tagged with:

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 …

Using Array Constants in Formulas again – OR logic Read More »

Tagged with: , , ,

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

Validation Dropdown lists based on Table Columns ranges Read More »

Tagged with: , , ,

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 …

Monitoring the Workbook Environment Read More »

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