Data Handling

Home/Data Handling

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

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

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

58 Crazy Excel Stories (that are probably happening in your company right now)!

  58 Crazy Excel Stories (that are probably happening in your company right now)!Crazy things happen when you mix untrained users with Excel.It’s not really rocket science.  Crazy things would happen with any complex system if you don’t train people properly.But many companies simply assume that everyone knows Excel, so they don’t need much (if any) [...]

Combine multiple tables with Excel Power Query

How to combine multiple tables with Excel Power Query If you have multiple tables of data in a file and you want to view a single report based on these tables then it can be time consuming and risky to manually copy and paste them into a single table before creating pivot [...]

Stop merging cells!

Stop merging cells! One Microsoft Excel Insider has been quoted as saying that 53% of Excel Users merge cells. There is a standard tool on the toolbar called “Merge and Center”, it “merges” two or more cells into one and then centers the text horizontally within the merged cell. This can cause a number of [...]

SUBTOTAL Strangeness

Introducing SUBTOTAL SUBTOTAL is an incredibly useful function for producing reports … yet it’s relatively unknown. If you have a list of values that are in categories and you want a subtotal for each category, then a grand total at the bottom, it’s so much better & far more reliable than using SUM. The key [...]

Feeling Bloated? How to fix inexplicably large spreadsheets

Sticking with the theme of the health of your spreadsheet, have you ever notice spreadsheet become inexplicably bigger? Suddenly becoming 20MB instead of 2MB. One cause is unused rows and columns. How do I check and how do I fix it? Simple, on each worksheet in your file press End then Home. Your cursor will [...]