Functions

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 [...]

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 [...]

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. [...]

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 [...]

SUM function to handle inserted rows and columns When I insert a row between the data and the SUM function Excel will usually amend the SUM function to include the new row. Sometimes, however, it doesn't do it. Is there a way to automatically include the row above the cell with the SUM function? [...]

Array formula to SUM rounded values I format many of my reports to zero decimal places. This means that in some cases the SUM function will show a different total than the displayed numbers indicated. If I format two numbers 5.5 and 7.5 with zero decimal places, they will display as 6 and 8 [...]

Refering to cells within a range name I use range names for rows. How do I reference a specific cell in a named row? By Neale Blackwood When you have a range name that refers to a whole row, its value will depend on where you use the name. Assume row 2 is name [...]

Access Analytic Knowledgebase Articles, Case Studies, White Papers, Success Stories and more

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 [...]

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 [...]