08 6210 8500
Dynamic Data Validation with Tables in Excel Welcome...this is an older post, you may be more interested in newer, more up to date techniques... For single dependent drop downs check out our video HERE For multi-row multi-level drop downs you can check out our blog post and video HERE Check out [...]
Tricking Non-Dynamic Array Formulas into Spilling by Jeff Robson Tricking non-dynamic array formulas into spilling isn't something you'd naturally think of doing. Dynamic Arrays are great but sometimes the formulas that refer to them behave in unexpected ways, and sometimes these don't spill ... without any obvious reason. For example, the DATE function spills, [...]
by Wyn Hopkins How and why to use XLOOKUP It’s August 2019 and we get our first glimpse of this glorious new function. In May 2016 I raised a UserVoice request to create a simpler and safer version of VLOOKUP and INDEX MATCH. As of May 2020 many Office 365 users now have it, and [...]
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 [...]
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 Check out our YouTube channel with weekly videos dedicated to Excel, Power Query and Power BI 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 [...]
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 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)!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) [...]
I recently was asked if there's a way of adding the Celsius symbol to a value in Excel. The answer is yes, and the method can be applied to all sorts of useful symbols / notation. e.g. bullet points, m2, km/hr, bbl, currency symbols etc. This is how.... Pick a blank cell then go to [...]
Last Cell used on a Row Find the reference of the last cell used in a row By Neale Blackwood One of the articles on our site allows you to find the cell reference of the last used cell in a column. You can see it below in the related articles. Someone asked if there [...]
Hyperlink Me! The HYPERLINK Formula (who knew that formula existed?) can be really useful when navigating around large spreadsheets The simplest format is along the lines of =HYPERLINK("#A1","Any Helpful Message") This would jump you to cell A1 of the current sheet. But that's no different to a normal old Hyperlink (via RIGHT-CLICK > Hyperlink or Insert [...]
Pay my invoice
