Excel 2016

Home/Excel 2016

Top tips for Power Query Data Types

by Eric Marshall Assigning Data Types Assigning data types is an essential step when working with Power Query in Excel or Transform Data in Power BI Desktop. Here are some useful tips on how to change data types while maximising the efficiency of your models: Tip #1: Don’t Change Types Until the End of a [...]

Excel tips & tricks to boost productivity

by Wyn Hopkins Boost your productivity with these shortcuts With over 20 years' experience in Excel we've picked up a few tips and tricks. We'll show you the shortcuts, features and formulas that will speed up your daily use of Excel. From SWITCH to Dependent Drop downs,  Grouping to Conditional Formatting,  clever validation lists and [...]

Power BI & Excel Virtual Classroom

Virtual Classroom - Power BI & Excel We're 100% still open for business and set up to deliver our highly rated Power BI and Excel "in - person" training via the magic of the internet! This is great, as not only do Perth clients continue to get access to our training courses, folks around Australia [...]

The Rise and Fall of the escalation formula

By Robert Hind Does your contract Rise and Fall let you down? When you're providing products or services in a long term relationship there is usually a contract which will typically include an Escalation (Rise and Fall) formula. The intent of the Escalation Formula is that cost increases or decreases should be passed on so [...]

Power Query and a Key Word search

By Wyn Hopkins Power Query and a Key Word search (Episode 1) This demonstration came about due to a real life scenario where I needed to categorise a transaction listing (like a bank statement for example) so that I could generate a summary report of categories of spend. All sorts of tips can [...]

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 [...]

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

By Wyn Hopkins 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 [...]