Share via

Offset Function and something easier.

Anonymous
2025-05-21T17:58:04+00:00

In Excel, I have 5,000+ Rows of Information in 5 Columns. I am trying to make it where I can print then information where at Row 45, it will move to the right and create the same 5 Columns of Info. Basically, trying to do a Phone Book using Excel and saving Paper. I saw one video where is said copy and paste into word, tried it, and it takes FOREVER to do one column adjustment. So would prefer to keep it in Excel.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2025-05-31T05:14:50+00:00

    Hi Jack McLellan,

    Just checking in to see if the above information was helpful. If you have any further updates on this issue, please feel free to post back. If you think my reply is helpful to you, please remember to mark it as an answer.

    Warm thanks,

    Hoang-D - MSFT | Microsoft Community Support Specialist

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2025-06-02T12:32:38+00:00

    I was able to work on this over the weekend, and it worked exactly how I wanted it and needed it to look. Thanks so much your your help on this, greatly appreciate it! :-D

    ~ Jack

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2025-05-27T14:31:00+00:00

    Good Morning,

    I haven't tried it out yet, hoping to sometime this week. Solution 1 looks promising. I'll keep you posted of my progress. :-)

    ~ Jack

    0 comments
  3. Anonymous
    2025-05-27T00:38:16+00:00

    Dear Jack McLellan,

    I hope you're doing well.

    Could you please provide any updates on this matter? If there are any new developments, feel free to share them with us.

    Looking forward to your response.

    Best regards,

    Hoang-D - MSFT | Microsoft Community Support Specialist

    0 comments
  4. Anonymous
    2025-05-21T18:23:15+00:00

    Dear Jack McLellan,  

    Thank you for reaching out regarding your challenge with printing large datasets in Excel. We understand your goal of optimizing the layout to save paper while maintaining readability. Below are step-by-step solutions to achieve this directly in Excel.Solution 1: Using the OFFSET Function for Dynamic Layouts

    1. Purpose:  
      1. The OFFSET function lets you reposition data dynamically. For your phone book layout (5 columns wide), it can shift data to the right after every 45 rows.
    2. Formula Example:

    =OFFSET($A$1, (ROW()-1)*45 + MOD(COLUMN()-1,5), INT((COLUMN()-1)/5)*45, 1, 1)

    1. How to Use:  
      1. Enter this formula in a new sheet starting at cell A1.
      2. Drag the formula across 5 columns and down to cover your data range (e.g., 45 rows × N blocks).
      3. Adjust 45 to your preferred row breakpoint.
    2. Advantage:  
      1. Automatically reorganizes data without manual copy-pasting.

    Reference:

    Solution 2: Simplified Page Layout Setup (No Formulas)

    1. Use Print Titles & Breaks:  
      1. Go to Page Layout > Print Titles.
      2. Under Rows to repeat at top, select your header row.
      3. Insert manual page breaks (Page Layout > Breaks > Insert Page Break) every 45 rows.
    2. Adjust Scaling:  
      1. Reduce scaling (Page Layout > Scale to Fit) to fit more columns per page.
    3. Print Preview:  
      1. Check File > Print Preview to ensure alignment before printing.

    Solution 3: Power Query (For Advanced Users)

    1. Transform Data:  
      1. Use Data > Get & Transform to split data into sections.
      2. Load transformed data to a new sheet for printing.
    2. Benefit:  
      1. Handles 5,000+ rows efficiently with reusable steps.

    Recommended Approach

    • For quick results, use Solution 2 (Page Layout).
    • For automation, implement Solution 1 (OFFSET).

    Let me know if you'd like additional details!

    Best Regards, 

    Hoang-D - MSFT | Microsoft Community Support Specialist

    0 comments