08 6210 8500
Shading rows automatically I've seen spreadsheets with shading on every second row. Is there an easy way to do this, other than manually formatting the rows? By Neale Blackwood Conditional Formatting is the easy way to achieve this effect. Using alternate shading makes reading wide spreadsheets easier. First select the range you want the [...]
Turning off Drag and Drop automatically I have an input spreadsheet that I distribute to users. I have used sheet protection to ensure users can only change input areas. The problem is users are still able to drag and drop within the input areas, which causes #REF! errors. Is there a way to stop [...]
Financial Year formula Is there an easy formula to determine the financial year from a date? By Neale Blackwood The following formula will calculate the financial year from a date in cell A1, assuming a July to June financial year that uses June year to refer to the financial year. For example, June 2005 [...]
Hiding and unhiding quickly I frequently hide and unhide the same sets of rows in my sheet, so that the detail doesn't print on my reports. Is there an easier way to do this? By Neale Blackwood The feature that would make hiding and unhiding specific rows easier is called Group. Select the rows [...]
3D SUMIF formula Is it possible to do a 3D SUMIF? By that I mean to SUM a column of values based on the codes in another column through multiply sheets? By Neale Blackwood Yes, but the formula is complicated. Assume three sheets named, Jul, Aug and Sep. In a fourth sheet you want [...]
Format error cells Is there an easy way to highlight all the cells that have error messages By Neale Blackwood There are a couple of ways. Select cell A1, press F5, click the Special button. Select the Formulas radio button and uncheck all the entries under it except Errors. Click OK. You now have [...]
Protect and unprotect sheets macro I frequently protect sheets without using a password to ensure users don't accidentally change something they shouldn't. Is there an easy way to unprotect all the sheets in a workbook and then protect them again? By Neale Blackwood There is no built-in method to protect or unprotect all the [...]
Today's date formula I use =NOW() to update today's date in a spreadsheet. The problem is that it includes a fraction for the time of day. How can I get just the date without the associated fraction? By Neale Blackwood Excel treats dates as numbers, and the time of day is treated as a [...]
Change default Cell comments font size How do I change the default font size of cell comments? By Neale Blackwood You need to change a Windows setting to achieve this. Please note this will change the font size of other objects defined as "ToolTip" in applications. You may want to change it back, so [...]
Using MAX and MIN instead of IF function I must calculate the time-and-a half and double-time hours from a total overtime figure - in column C. The first two hours of overtime are time-and-a-half, the rest is double-time. Can you provide IF functions to do the calculations? By Neale Blackwood Many of the questions [...]
Formula to stop Error messages I have a formula that returns an error message. Can I get it to display a blank cell instead? By Neale Blackwood Let's assume that cell A1 has some text in it and B1 has a number in it. A1 sometimes contains numbers and then we need to multiply [...]
Stop zeroes displaying on line Charts I have a line chart which has all the months of the year displayed. Each month is linked to a data cell. This means that future months display zero values. The line chart drops down to zero for these future months. Instead of having to change the chart [...]
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