Dynamically consolidate multiple ranges

By Yury Tokarev 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 [...]

3 Easy Steps to Manage your Data Fields

3 Easy Steps to Manage your Data Fields Want to control which data fields to keep in Power Query when removing other columns? When using the ‘Remove Other Columns’ transformation in Power Query (‘Get & Transform’ in Excel 2016+) the query editor hard-codes the remaining column names in the Advanced Editor. This is fine if your [...]

Excel’s 3 Best Kept Secrets

What are Excel's 3 Best Kept Secrets? Excel has seen great advancement in the last 10 years and every user who spends their days re-organising data can benefit hugely from Excel's 3 best kept secrets. 1. Power Query If you or members of your team use Excel a lot then you are missing out massively [...]

Dynamic Data Validation with Tables in Excel

Dynamic Data Validation with Tables in Excel Why is Excel returning an error message? Why doesn’t my formula work? What did I do wrong? The answer to these common questions may surprise you. Many spreadsheets I’ve come across have a common problem – inconsistent data entry. If someone enters “United States of America” [...]

Get & Transform to the rescue!

Get & Transform to the rescue! Is it a bird? Is it a plane? No it's way better than that, it's Excel! Get & Transform, also known as Power Query, gives Excel users super powers. Here's the latest scenario where it has helped out and also is an opportunity for me to demonstrate a method [...]

Excel Dependent Drop Downs

  Dependent Drop Down Box How to do one of those trickier tasks in Excel: set up a drop down box that is dependent on the result of another drop down box. The scenario If coffee is selected from drop down A then I want a list of coffee types to appear in drop [...]

Easy Cumulative totals in Tables

Easy Cumulative totals in Tables The single greatest advancement in Excel in the last 10 years was the introduction of Tables.  Yet although Tables were introduced in Excel 2007 there is still a huge number (I'd even say the majority) of Excel users that don't know how to create tables or understand what they do. This [...]

Master INDEX MATCH in 60 seconds

Great news! XLOOKUP is going to replace INDEX MATCH eventually but in the meantime….. We've written a number of articles in the past around how INDEX MATCH is a technically better option compared to VLOOKUP. However, it is a trickier formula and therefore not as widely used. In May 2016 Wyn, added a new [...]

7 Mischievous Adventures in Excel

This eBook has some great ideas on how to have more fun in Excel than you can poke a kitten at (usually at your colleague’s expense!) *evil laugh* If you’d like some ideas for some mischievous adventures that will mess with your friend’s head (and their models), download this now! You'll learn things [...]

2017-03-08T03:53:54+08:00Keyboard, Productivity|

Excel Shortcuts: 22 of the very best and most handy!

A collection of our favourite keyboard shortcuts in Excel. Pin it up on your wall & share it with your friends. Shortcuts to save time and maximise productivity including: Creating charts faster Faster formatting Navigating a large spreadsheet and 19 more!        

2018-09-03T12:23:14+08:00Keyboard, Productivity|