08 6210 8500
Pivot Tables and Grouping Dates I use a Pivot Table to summarise a sales transaction database. I want the data summarised by month. When I use the date to summarise the data, it adds all the years together. How do I fix this? If you just summarise your data into a Pivot Table, it [...]
Cumulative Summing I use cumulative balance columns which add the figure to the left of the cell to the value in the row above the cell. When I insert a row I have to copy the formula from the row above to the new row and to the row below for the column to [...]
Dualing Screens I have recently upgraded to a computer with dual screens. Is it possible to display Excel across both screens and have one screen with one workbook and the other screen with another workbook? By Neale Blackwood To expand the Excel window across screens, first click the Restore Down button on the Excel [...]
Printer Trays in VBA I have recorded a macro to print my sheets. I need to print some sheets single-sided and others double-sided. Is it possible to create a macro to do this? By Neale Blackwood Macros don't have many limitations, but changing printer settings is one of them. The solution is to set [...]
Save All Open Files I work with a model that has many files and links between the files. When I make structural changes that affect links I have to go through and save all the open files to make sure the changes are kept. Is there a way to save all the open files [...]
Link sheet name to cell value Is it possible to link the sheet name to a cell's contents? I want the sheet name to be whatever I enter into cell A1. By Neale Blackwood There is no built-in Excel function or feature to do this. You have to use an event macro. The macro below [...]
Change page orientation on all sheets Is there a quick way to change the page orientation from portrait to landscape on all the sheets in a workbook? By Neale Blackwood Yes. Right click on the sheet name tabs, click Select All Sheets. Now click the File menu, select Page Setup, then click the Page [...]
Counting weekdays Is it possible to figure out how many occurrences of a certain weekday there are in a specific month? I need to figure out how many Thursdays in each month of the year. By Neale Blackwood Yes, but the formula has 6 IF functions in it. Assume cell A2 has the date [...]
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 [...]
List Range Names Is there a way to list all the range names in a workbook? By Neale Blackwood Yes. Go to a cell where you want the list to start, then press the F3 key to open the Paste Name box and click the Paste List button to paste all the names used. [...]
Access Analytic Knowledgebase Articles, Case Studies, White Papers, Success Stories and more
Tips on debugging formula Have you got any tips to debug a formula that is displaying an error? By Neale Blackwood Excel has an Auditing toolbar that allows you to trace cell links. This toolbar has a Trace Error button. Excel XP and 2003 have built-in Auditing cell notes that examine and report on [...]
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
