Shared Excel Templates

by Robert Hind Use Shared Excel Templates Smart use of Excel (or other Office applications) should feature use of templates. In a business those templates should be "Workgroup" templates (common templates available across the business). Workgroup templates offer Efficiency, Consistency, Accuracy, Automation and Professionalism. The purpose of this article isn't to expand on the importance [...]

Power Query and a file selector Macro

by Wyn Hopkins Power Query and a file selector Macro   If you have an Excel file containing Power Query and need to send it to colleagues, read on. If they need to be able to change where the source data for the Query is being pulled from then read on. If you [...]

Revealing Power BI’s Hidden Gems

by Wyn Hopkins Did you know about these hidden Power BI features? It seems unfortunate that for such an awesome product there are a few user interface features that are very difficult to discover. I have trained hundreds and hundreds of users in Power BI, and I get to experience people hitting these issues over [...]

2019-12-09T16:04:26+08:00Power BI, Power Query|

HYPERLINK and XLOOKUP to jump to a result

by Wyn Hopkins Use HYPERLINK and XLOOKUP to jump to a result   XLOOKUP will return a range so we can use it inside other formulas such as HYPERLINK to be able to jump to our result. Want to learn more? Jump on to one of our courses We've trained over 1,000 people from all [...]

Financial Modelling using Dynamic Array Functions – no Copying & Pasting!

by Jeff Robson Financial Modelling using Dynamic Array Functions Best practice financial modelling has always been to enter your formulas in blocks: enter a formula, then copy this across and possibly down also, making sure you have your absolute and relative references set correctly. This is very useful because it is faster to build a [...]

How to use Icon Sets in Power BI and Excel

If you find the default Icon settings confusing in both Excel and Power BI, you're not alone! In July 2019 conditional formatting Icon Sets were added to Power BI and it caused us to revisit how they work in Excel. In Excel you would highlight a range of numbers and choose Conditional Formatting : Icon [...]

Dynamically consolidate multiple ranges

Dynamically consolidate multiple ranges when loading Excel files from Folder in Power Query We've come across a task where we need to consolidate a client's budget data from Excel files, each representing a division, where any of the files may have multiple similarly structured tabs with sub-divisional data. In our project, each subdivision [...]

Share This