08 6210 8500
Select Formula cells only Is there a way to select all cells that have a formula? By Neale Blackwood Yes. Hold the Ctrl key and press the "a" key to select the whole sheet. Then press F5 (GOTO), click the Special button, then click the Formulas radio button and select the types of formula [...]
Multi-criteria SUMIF I use SUMIF to sum a range based on one condition. Is there a way to sum a range based on two or more conditions? By Neale Blackwood One way is to use SUMPRODUCT. The following formula will sum the range C1:C10 based on the contents of A1:A10 and B1:B10. Cell E1 [...]
Rounding to 5 cents Is there a formula to round to the nearest five cents? By Neale Blackwood, 22 Jun 09 You can use the ROUND function to do it. If the number is in cell A1, this formula will work: =0.05 * ROUND(A1/0.05,0) Example: This formula rounds 2.5 cents and 7.5 cents up [...]
3D Sum formula Is it possible to easily add up the same cell across spreadsheets? By Neale Blackwood Yes, these are called 3D formulae. To create a 3D sum formula that sums all the A4 cells in multiple sheets, open a new workbook, click the AutoSum toolbar icon, then click the Sheet2 tab, then [...]
Dynamic Headings Is there a way to change headings based on a cell that has the current month? By Neale Blackwood Yes. If cell A1 contains Feb 03 as a date, then the following formula will create the heading - Report for Feb 03. ="Report for " &TEXT(A1, "mmm yy") Note the space after [...]
Auto Correct Explained How does Excel correct my typing errors? By Neale Blackwood Excel uses AutoCorrect to correct common typing errors. To see which words are included, click the Tools menu and click AutoCorrect. Use the scroll bar to view the combination of letters and symbols that are included. You can add your own [...]
Toolbar overload Help! I've added so many buttons to the toolbar its hard to find the one I'm after? By Neale Blackwood Two suggestions; the first is to right click the toolbar area. You will see a menu of toolbars. It's likely you don't use many of your buttons all the time. So it [...]
Line break within a cell Is it possible to insert a new line within a cell? By Neale Blackwood Yes, when typing in a cell hold down the Alt key and press Enter. This inserts a new line within the cell. You can insert as many lines as you want, but be warned that [...]
Use a Range Name in a single sheet Can I create a range name that only works in a single sheet? By Neale Blackwood Yes. To create a range name that only applies to a single sheet you type in the sheet name following by a "!" then the range name you require. To [...]
Copy quickly between sheets Is there a quick way to copy between sheets in the same workbook? By Neale Blackwood Open another window of the workbook by clicking the Window menu and selecting New Window. You will notice that the sheet name in the title bar will have ":2" added. To see the two [...]
Dynamic Range Name What is a dynamic range? By Neale Blackwood A dynamic range is a range whose size changes as data is added or deleted in an area. You can use this technique to define a range for a Data Validation drop down list or define a variable length data range for a pivot table. [...]
Hiding and unhiding sheets I hide a number of sheets in the workbooks I create because they are used for workings. Is there an easy way to make them all visible at once? By Neale Blackwood The following macro will unhide all the sheets in the active workbook.: Sub UnHideAllSheets() 'this macro unhides all [...]
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
