08 6210 8500
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 [...]
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 [...]
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 [...]
Ever get the warning that Excel has “run out of resources” or “too many formats” or you simply can’t paste into it anymore. There’s a glitch in Excel 2010 and 2007 (fixed in Excel 2013) Am I affected? Here’s how to check. Open your Excel Files (your important ones), go to the Home Ribbon [...]
Expand the Formula Bar Excel 2007 only By Neale Blackwood New in Excel 2007, you can make the Formula Bar higher. Between the column letters and the Formula Bar there is a line. If you point to the line Excel displays a double-headed arrow. You can click, hold and drag the line down to [...]
Extend the Name Box Excel 2007 only By Neale Blackwood New in Excel 2007, you can change the width of the Name Box to allow you to display long range names. If you point to the right of the drop down arrow in the Name Box you will see a double-headed arrow that you [...]
Pin the File on the list Excel 2007 only By Neale Blackwood In Excel 2007 when you click the round Office symbol in the top, left-hand corner of the Excel screen, the Recent Document files are listed on the right-hand side of the listing. You may notice a symbol of a pin on the [...]
Exit Excel 2007 Excel 2007 only By Neale Blackwood If you want to close Excel 2007 (and you have more than one file open) then you now must click the Round Office Button and select Exit Excel at the bottom of the menu. Clicking the Window Close X button will only close down the [...]
Scroll Wheel and the Ribbon Excel 2007 only By Neale Blackwood In Excel 2007 you can use the mouse scroll wheel to move between the Ribbons. Simply point the mouse anywhere in the Ribbon area and move the scroll wheel to change the active Ribbon. When you first switch over to Excel 2007 this can be a useful [...]
Excel 2007 - New Functions Five new functions By Neale Blackwood There are five new functions in Excel 2007. Funnily enough, four of them were created because users were frequently using array formulas to do the calculations ... so now they are built-in. IFERROR - this allows you to handle errors more elegantly than [...]
Excel 2007 Goto and selections Excel 2007 selection techniques By Neale Blackwood Excel 2007 has simplified the selection of certain types of cells. Previously you needed to go through the Goto dialog and the Special button. Now the options are available on the Home ribbon tab. In the Editing section, at the end of [...]
Enabling Macros in Excel 2007 Automatically allow some macros By Neale Blackwood Enabling macros has changed slightly in Excel 2007. You can avoid having to remember to enable them by having them enable automatically from folders you classify as trusted. To set up trusted folders follow these steps. Click the Round Office button top [...]
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
