So in my previous post I was banging on about the new Dynamic Array formulas only fairly recently released to Office 365 Excel. I reckon FILTER() is the star but as I used it, I became a little disappointed as it seemed like you could only return ALL the columns in the range you are …
Use the new FILTER() formula and retrieve only the columns you want Read More »
We recently put solar panels on our roof and so I have become interested in solar energy and how it all works. If anything lends itself to being set out in a spreadsheet or financial model it is this subject! I developed what started off as a simpler spreadsheet to help decide the best energy …
Here is a quick but really useful coffee break dynamic sum formula 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 want to give the user the flexibility to choose the 2 ‘From’ …
Dynamic sum formula for summing between two dynamic columns Read More »
If you are up for a bit of Excel humour, you may enjoy this YouTube from Matt Parket’s stand up routine on Excel spreadsheeting
Pivot Tables collisions or can be a real problem. A meeting is coming up in a few minutes and suddenly your beautifully crafted Pivot Table dashboard report that’s been working so well for months just doesn’t update and won’t tell you why or where the problem is.
Following on from my recent post of Fix duplicated Conditional Formatting Rules , I mentioned Debra Dalgaleish’s website page had some great VBA to fix duplicated Conditional Formatting rules. I tweeked this code just a bit so that all you have to do is select anywhere in any Table in the active worksheet and it …
Macro to fix Duplicated Conditional Formatting rules in Tables Read More »
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 Table in its own worksheet, I created a worksheet of seven Pivot Tables to provide seven Pivot Charts needed in their own sheet. This could …