Mirroring data

Question

In Excel, I have data in A1:D24. In E1 I have =A1:D24 to mirror that range.

Question 1) I'm calling that mirroring, but what's the technical term for that?

Question 2) I want to mirror A1:D24 three times across the columns; is there a more efficient method than putting =A1:D24 in each of E1, i1, & M1?

Question 3) The mirrors display 0 in cells that are empty in the source range; how do I instead have the mirrors display empty cells as empty?

Answer 1

@Richard Tillman

Not the most elegant solution, but try this one.

=LET(mirror,HSTACK(A1:D24,A1:D24,A1:D24),IF(mirror<>0,mirror,""))

Answer 2

1. The technical term in Excel is a reference to a cell or range. In VBA and Excel documentation, A1:D24 is an A1-style range reference.
2. For repeating that same block across columns, the context does not provide a built-in method more efficient than placing the copied range again in another location. A practical option from the provided guidance is to copy and transpose only when the goal is to rotate rows and columns, or consider a PivotTable if the goal is to view the same data from different angles. For a straight repeated side-by-side mirror, no more efficient built-in method is described in the provided material.
3. The 0 values are expected when a direct link formula points to blank source cells. If the goal is only to make those mirrored blanks appear empty, use this display setting:
   1. In Excel, go to File > Settings > Advanced.
   2. Scroll to Display options.
   3. Clear Show a zero in cells that have zero value.
   That hides displayed zeroes so those cells appear blank. If changing formulas is acceptable, an IF formula can return an empty string instead of 0, for example:

      =IF(A1="","",A1)
   

   For a date-formatted cell, this also avoids showing 1/0/1900 when the source is blank.

---

References:

• Refer to Cells and Ranges by Using A1 Notation
• Transpose (rotate) data from rows to columns or vice versa
• Appearance of empty cells when transferred via formula to the master Excel workbook - Microsoft Q&A
• leaving a formula answer blank in Excel - Microsoft Q&A