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 our [...]
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 [...]
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 [...]
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 [...]
A report is more useful if it is formatted well Steps: Highlight the numbers you want to format Right Click - Format Cells Custom > then copy and paste (or type) the following into the Type box: #,##0_);[Red](#,##0);-??_) Done If you want to format other numbers with the same format you can always right [...]
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 [...]
Saving a Default Template By Wyn Hopkins, 26 Jun 15 (updated 30/09/2016) Why? Life can be very repetitive. If you find yourself making the same changes to Excel every day such as setting custom number formats, fonts, cell styles, colours, print headers and footers then you should set up a template. Even better than this [...]
Display Day of the Week How can I display the day of the week in a cell instead of the date? By Neale Blackwood, 18 Jun 09 To have the day of the week displayed in a cell based on another cell, use one of these formulae (this assumes that the date you are [...]
Last Day of the Month + Useful Date Formula How do I display the last date of a month in a cell? By Neale Blackwood There are two methods. The first uses Excel's series feature. If you enter two dates, for example, 31/1/03 in A1 and 28/2/03 in B1, you select both cells and [...]
Reach out to the Access Analytic team Monday to Friday on (08) 6210 8500 or submit your contact information and we will be in touch.
Get Ahead of the Game – Book Your Training Now!
Wyn has been awarded MVP status by Microsoft every year since 2017 and is the author of the book “Power BI for the Excel Analyst”. He is known worldwide for sharing his knowledge via the Access Analytic YouTube channel with over 5 million views and 90,000+ subscribers.
He gained his Chartered Accountant qualification at PwC in 2000 and has extensive Business Analyst experience with HBOS and Barclays in the UK plus multinational clients in Perth.
Wyn is skilled in Excel and Power BI development, Power Query automation, management reporting and is an experienced Excel and Power Bl trainer.
Pay my invoice
