Uploaded January 2025 | Updated September 2026, 1 week ago
Great tip from @operlandsen in the comments:
You can define the name to refer to this formula: =!A1 (assuming the active cell is in Sheet1 cell A2). Then you can keep the Workbook level scope for the name and it will work in all worksheets.
—————
👀In this video, you’ll uncover a practical and efficient way to use named cells, specifically the ROWABOVE approach, to tackle running totals and conditional formatting in Excel while avoiding the pitfalls of the OFFSET function. The tutorial starts with a common error in running totals c and presents a clever fix using the N function combined with ROWABOVE, demonstrating how to create robust running totals that dynamically adjust when rows are inserted or deleted—eliminating the dreaded #REF! errors.
The magic lies in defining and leveraging named ranges like ROWABOVE and applying these to both calculations and conditional formatting. This makes formulas traceable and avoids the inefficiencies of volatile functions like OFFSET. The video also covers conditional formatting tricks to highlight discrepancies dynamically, enhancing usability and auditing capabilities. Whether you’re a spreadsheet wizard or a casual user, these tips are sure to boost your Excel skills!
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
https://pbi.guide/book/
Connect with me
wyn.bio.link
Great tip from @operlandsen in the comments:
You can define the name to refer to this formula: =!A1 (assuming the active cell is in Sheet1 cell A2). Then you can keep the Workbook level scope for the name and it will work in all worksheets.
—————
👀In this video, you’ll uncover a practical and efficient way to use named cells, specifically the ROWABOVE approach, to tackle running totals and conditional formatting in Excel while avoiding the pitfalls of the OFFSET function. The tutorial starts with a common error in running totals c and presents a clever fix using the N function combined with ROWABOVE, demonstrating how to create robust running totals that dynamically adjust when rows are inserted or deleted—eliminating the dreaded #REF! errors.
The magic lies in defining and leveraging named ranges like ROWABOVE and applying these to both calculations and conditional formatting. This makes formulas traceable and avoids the inefficiencies of volatile functions like OFFSET. The video also covers conditional formatting tricks to highlight discrepancies dynamically, enhancing usability and auditing capabilities. Whether you’re a spreadsheet wizard or a casual user, these tips are sure to boost your Excel skills!
The team and I at Access Analytic develop Power BI and Excel solutions for clients in Australia and deliver training around the World. https://accessanalytic.com.au/
Did you know I've written a book "Power BI for the Excel Analyst"?
https://pbi.guide/book/
Connect with me
wyn.bio.link
