Excel - Automatically Resize Print Range - Episode 1756

Microsoft Excel Tutorial: Automatically Resize Print Range in Excel | OFFSET Function. Welcome to another episode of the MrExcel podcast! In today's episode, we will be discussing how to automatically resize the print range in Excel as formulas change. This question was sent in by Michael, who has a great application for sending out statements to tenants. The application uses a formula from Mike Girvin's "Ctrl+Shift+Enter" book, and as the data changes, different records are extracted from the worksheet. Michael's main concern is that he wants the print range to extend when there are more records, but not be too large when there are no records. The solution to this problem involves using the OFFSET function, which allows for dynamic resizing of a range in response to a formula. In this case, we will be using the COUNT function to determine the number of rows in the print range, and then adding 18 to account for the headings starting in row 18. To set the print range, we first need to go to the Formulas tab and then click on Name Manager. Here, we will find the name called Print_Area, which is currently referring to a specific range. We will change this to the OFFSET function, starting from cell A1 and moving down 18 rows plus the count of TenantStatement. We will also specify the range to be 6 columns wide, as seen in the report. This will automatically adjust the print range as the data changes. By using the OFFSET function, we can avoid the need for macros and have the print range automatically extend based on the data. This may seem like a complicated solution, but it is a useful tool to have in your Excel arsenal. Thank you to Michael for sending in this question and thank you for tuning in to another episode of the MrExcel podcast. Don't forget to subscribe and stay tuned for more helpful tips and tricks for mastering Excel. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-... Table of Contents: (00:00) Resizing Print Range with Formulas (00:17) Application using formula from "Ctrl+Shift+Enter" book (00:29) Solution using OFFSET function (01:06) Setting the print range (01:16) Explanation of OFFSET function (02:14) Solving the problem (03:10) Explanation of OFFSET function again (04:11) Final solution and demonstration (05:00) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Count of column A Ctrl+Shift+Enter book Formulas returning blank sales Name Manager Offset function Offset function explanation Print range extension Resize the Print Range as Formulas Change Set print area Solving the problem Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads... Michael has an Excel workbook for printing statements. When the customer changes, there are a different number of records visible thanks to some fairly complex array formulas. How can he adjust the print range to automatically include extra data? Today's episode sets up a print range that dynamically resizes using the OFFSET function.