08 6210 8500
Depreciation is one of those concepts that sounds easy and simple … until you try to put it into a financial model! In this blog, we start with Excel’s standard depreciation functions, then show two much better, more functional and more elegant ways to calculate depreciation. 1. Excel Functions Excel has some [...]
Display Sheet Name Is there a formula to insert the sheet name into a cell? By Neale Blackwood Yes. In cell A1 insert the formula =CELL("filename", A1). In another cell, enter =MID(A1,SEARCH("]",A1)+1,LEN(A1)-SEARCH("]",A1)) The first formula will display the file's full path. The second formula will display the sheet name. This is also a useful [...]
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 [...]
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 [...]
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 [...]
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 [...]
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 [...]
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. [...]
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 [...]
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
