Power Query (Get & Transform) – Video Introduction Part 2

Power Query (Get & Transform) - Part 2 In part 1 I demonstrated how Power Query can quickly consolidate all of the files in a folder and bring that data into Excel. In this 6 minute video I follow on from that introduction and add an extra transformation step to get the dates I need. Once [...]

Power Query (Get & Transform) – Video Introduction Part 1

Power Query (Get & Transform) Part 1 Power Query is amazing at linking to data sources and cleansing / transforming that data as it's loaded into Excel (or Power BI Desktop). Watch this 4 minute video and see how you can consolidate all of the files from a folder instantly using Power Query. Build the transformation steps [...]

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

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

Custom Visuals – Power BI – 2 of the best

One of the great things about Power BI visualisations is the fact that they are open source and clever generous folk are developing fantastic tools for free that can help us all deliver a more enjoyable experience to the end users. Custom Visuals Gallery Two of my current favourites are both by OKVIZ and are [...]

2017-03-08T03:53:54+00:00 Power BI|

Master INDEX MATCH in 60 seconds

INDEX MATCH is great but GETMATCH would be better I'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, so I've added a new formula suggestion to the Excel [...]

Power Query Parameters – How to use Named Cells as flexible inputs

Power Query Parameters and Named Cells By Wyn Hopkins Power Query is the best thing to happen to Excel since Tables were introduced in Excel 2007 When developing Power Query solutions for others to use, or when needing parameters that can be changed easily (e.g. source file or date) you can store these variables [...]

2017-03-08T03:53:54+00:00 Power BI|

VBA & DropBox causing Excel to crash on close

Excel Warning Box By Wyn Hopkins In a nutshell: Save yourself a lot of wasted time and turn off DropBox badges.... If you ever experience a VBA Password box appearing after you close Excel and then the dreaded "Microsoft Excel has stopped working" message then there may be a simple solution...... After much debugging [...]

2017-03-08T03:53:54+00:00 VBA – Macros|

Fix MYOB Account Numbers Exported to Excel

Fix MYOB Account Numbers Exported to Excel It used to be simple to export data from MYOB AccountRight to Excel.  With more recent versions, the export has changed which means all your formulas looking for particular account numbers won't find them.  Here's how you can fix MYOB account numbers. The Cause When MYOB [...]

2017-03-13T14:49:44+00:00 Data Handling, Functions|

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+00:00 Keyboard, 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!          

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

When one Excel session just isn’t enough!

By Wyn Hopkins This simple tip may save you a lot of time and effort, but it must be used carefully. With the arrival of Excel 2013 we were finally able to easily split Excel between two screens and happily copy and paste between the two with full paste capability and without the worry of worksheet [...]

Share This