How to Mirror Table in Excel (4 Easy Methods)

We have a large Excel worksheet that contains information about several sales representatives. We want to mirror that table.

Mirror Table in Excel


Method 1 – Use the OFFSET Function to Mirror a Table

Steps:

  • Select cell B13.

Use OFFSET Function to Mirror Table in Excel

  • Use the following formula in the Formula Bar:
=OFFSET(B$13,(-1)*ROW(B3),0)

  • Press Enter.

Use OFFSET Function to Mirror Table in Excel

  • Autofill the function to the rest of the cells horizontally.

  • Autofill the rows vertically.

Use OFFSET Function to Mirror Table in Excel

Read More: How to Rename a Table in Excel


Method 2 – Combine INDIRECT and ROW Functions to Mirror a Table

Steps:

  • Select cell B4.
  • Insert the following formula in the Formula Bar:
=INDIRECT("Overview!" & $B$1 & ROW()-ROW($B$2)+1) & ""

Combine INDIRECT and ROW Functions to Mirror Table in Excel

  • Press Enter on your keyboard.

Combine INDIRECT and ROW Functions to Mirror Table in Excel

  • Autofill the column.

  • AutoFill through the columns.

Combine INDIRECT and ROW Functions to Mirror Table in Excel

Read More: How to Extend Table in Excel


Method 3 – Merge IF and LEN Functions to Copy a Table

Steps:

  • Select cell B4.
  • Use the following formula in the Formula Bar:
=IF(LEN(Overview!B3)> 0,Overview!B3,"")

Merge IF and LEN Functions to Mirror Table in Excel

  • Press Enter on your keyboard.

  • Autofill the IF and LEN functions to the rest of the cells in that table which has been given in the screenshot.

Merge IF and LEN Functions to Mirror Table in Excel

Read More: How to Make an Excel Table Expand Automatically


Method 4 – Apply Keyboard Shortcuts to Copy a Table in Excel

Steps:

  • Select the cell range B3:E10.
  • Press Ctrl + C.

Apply Keyboard Shortcuts to Mirror Table in Excel

  • Select the destination cell.
  • Press Ctrl + V to paste the copied cells.

Read More: How to Use Sort and Filter with Excel Table


Things to Remember

➜ While a value cannot be found in the referenced cell, the #N/A! error happens in Excel.

#DIV/0! error happens when a value is divided by zero (0) or the cell reference is blank.


Download the Practice Workbook


Related Articles


<< Go Back to Edit Table | Excel Table | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo