How to Mirror Table in Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

While working with Microsoft Excel, sometimes we need to mirror a table for the convenience of our work. Mirroring a table in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn 4 quick and suitable ways to mirror a table in Excel effectively with appropriate illustrations.


Mirror Table in Excel: 4 Suitable Ways

Let’s assume we have a large Excel worksheet that contains information about several sales representatives of the Armani Group. The name of the sales representatives, the Identification Number of the Sales representatives, the types of Products, and the revenue earned by the sales representatives are given in Columns B, C, D, and E, respectively. From our dataset, we can easily apply the OFFSET, IF, INDIRECT, LEN, and ROW functions in Excel to mirror a table. Here’s an overview of the dataset for today’s task.

Mirror Table in Excel


1. Use OFFSET Function to Mirror Table

Using the OFFSET function to mirror a table in Excel is an easy task. We will mirror a table from our dataset using the OFFSET function. This is a time-saving task also. Let’s follow the instructions below to mirror a table!

Step 1:

  • First, select cell B13 for the convenience of our work.

Use OFFSET Function to Mirror Table in Excel

  • After selecting cell B13, type the following formula in the Formula Bar,
=OFFSET(B$13,(-1)*ROW(B3),0)
  • Where B$13 is the reference, (-1)*ROW(B3) is the rows, and 0 is the cols of the OFFSET function.

  • Hence, press Enter on your keyboard.
  • As a result, you will be able to get the return of the OFFSET function, and the return is Joe.

Use OFFSET Function to Mirror Table in Excel

Step 2:

  • After that, autofill the OFFSET function to the rest of the cells horizontally, which has been given in the screenshot.

  • Again, autofill the OFFSET function to the rest of the cells vertically.

Use OFFSET Function to Mirror Table in Excel

Read More: How to Rename a Table in Excel


2. Combine INDIRECT and ROW Functions to Mirror Table

In this section, we will use the INDIRECT and ROW functions to mirror a table in Excel. From our dataset, we will use these two functions to mirror a table in a new worksheet named INDIRECT from Overview. Let’s follow the instructions below to mirror a table!

Step 1:

  • First of all, select cell B4 for the convenience of our work.
  • After selecting cell B4, type the following formulas in the Formula Bar,
=INDIRECT("Overview!" & $B$1 & ROW()-ROW($B$2)+1) & ""

Combine INDIRECT and ROW Functions to Mirror Table in Excel

  • Hence, press Enter on your keyboard.
  • As a result, you will be able to get the return of the INDIRECT and ROW functions, and the return is Sales Rep.

Combine INDIRECT and ROW Functions to Mirror Table in Excel

  • After that, autofill the INDIRECT and ROW functions to the rest of the cells horizontally in that row, which has been given in the screenshot.

Step 2:

  • Similarly, repeat step 1 to mirror the rest of the columns in INDIRECT from Overview, which has been given in the screenshot below.

Combine INDIRECT and ROW Functions to Mirror Table in Excel

Read More: How to Extend Table in Excel


3. Merge IF and LEN Functions to Mirror Table

Now, we will use the IF and LEN functions to mirror a table in Excel. From our dataset, we will use these two functions to mirror a table in a new worksheet named IF from Overview. Let’s follow the instructions below to mirror a table!

Steps:

  • First, select cell B4 for the convenience of our work.
  • After selecting cell B4, type the following formulas in the Formula Bar,
=IF(LEN(Overview!B3)> 0,Overview!B3,"")

Merge IF and LEN Functions to Mirror Table in Excel

  • Hence, press Enter on your keyboard.
  • As a result, you will be able to get the return of the IF and LEN functions, and the return is Sales Rep.

  • After that, 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


4. Apply Keyboard Shortcuts to Mirror Table in Excel

In this section, we will mirror a table in Excel using keyboard shortcuts. This is an easy and time-saving task. From our dataset, we will use the keyboard shortcuts to mirror a table. Let’s follow the instructions below to mirror a table!

Steps:

  • First, select cells range from B3 to B10. Hence, press Ctrl + C on your keyboard simultaneously to copy those cells.

Apply Keyboard Shortcuts to Mirror Table in Excel

  • After that, press Ctrl + V on your keyboard simultaneously to paste the copied cells. As a result, you will be able to mirror the table which has been given in the screenshot below.

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 Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all the suitable methods mentioned above to mirror a table will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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