Create a Mirrored Image in Excel

This tutorial describes the use of Excel functions to create a mirrored or flipped image of a dataset in Excel. It also demonstrates how to flip a picture in Excel.

A mirrored image of a dataset

We can create a horizontal or vertical mirrored image of a dataset. When it is mirrored horizontally it looks like the example image below:

When it is mirrored vertically it looks like the example image below:

How to create a mirrored image of a dataset

We use three Excel functions to create a mirrored image of a dataset: OFFSET, ROW, and COLUMN functions.

We first explain what each function does.

The OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a given reference.

Syntax:

OFFSET(reference, rows, columns, [height], [width])

The function has three required arguments and two optional arguments as explained below:

  • Reference. This is a required argument. It is the reference from which we want to base the offset. It must refer to a cell or a range of adjacent cells.
  • Rows. It is required. It is the number of rows, up or down, that we want the upper-left cell to refer to. It can be either positive or negative. When it is positive, it means the rows are below the starting reference. When it is negative, the rows are above the starting reference.
  • Columns. It is required. It is the number of columns, either to the right or left, that we want the upper-left cell of the result to refer to. It can either be positive or negative. When it is negative, the columns are on the left of the starting reference. When it is positive, the columns are on the right of the starting reference.
  • Height. It is optional and refers to the height in the number of rows that we want the returned reference to be. It must be a positive number. If it is omitted, it is assumed to be the same height as the reference.
  • Width. It is optional and refers to the width in the number of columns that we want the returned reference to be. It must be a positive number. If it is omitted, it is assumed to be the same width as the reference.

The ROW function

The ROW function returns the row number of a reference. For example, the following formula returns 10:

=ROW(A10)

Syntax:

ROW([reference])

The function has one optional argument as explained below:

  • Reference. It is optional and refers to the cell or range of cells for which you want the row number. If it is omitted, it is assumed to be the reference of the cell in which the function is contained.
  • If the reference is a range of cells, and ROW is entered as a vertical array, the row numbers are returned as a vertical array.

The COLUMN function

The COLUMN function returns the column number of a reference. For example, the following formula returns 1 because column A is the first column:

=COLUMN(A10)

Syntax:

COLUMN([reference])

The function has one optional argument as explained below:

  • Reference. It is optional and refers to the cell or range of cells for which we want to return the column number.
  • If it is omitted, it is assumed to be the reference of the cell in which the function is contained.
  • If the argument is a range of cells, and the COLUMN function is entered as a horizontal array, the function returns the column numbers of the reference as a horizontal array.
  • If the function is not entered as an array, it returns the number of the leftmost column.

Application of the functions in creating a mirrored image of a dataset

We will use the dataset below to demonstrate how the functions can be applied in creating both a horizontal and vertical mirrored image of a dataset:

Create a horizontal mirrored image of a dataset

To create a horizontal mirrored image of the dataset we use the steps below:

  1. Open the worksheet that contains the dataset.
  2. Select cell I1 and type in the following formula:
=OFFSET($G1,0,(-1)*COLUMN(A1))
  1. Press the Enter key and copy the formula across to cell N1 by dragging the fill handle. This creates a mirrored image of the header row.
  1. Drag the fill handle down to cell N6 to copy the formula in the range that is the same height and width as the source range.
  1. With the dataset selected, double-click each column boundary to fit the data.
  1. Select the data in column I and click the Decrease Decimal button several times until all the decimal places are hidden.

We now have a horizontal mirrored image of our dataset as shown below:

Explanation of the formula

=OFFSET($G1,0,(-1)*COLUMN(A1))
  • In cell I1, this formula evaluates to =OFFSET($G1,0,-1) and returns the data in cell F1 which is Days Employed.
  • As the formula is copied to other cells, it updates and returns the data in different cell ranges of the dataset.

Create a mirrored vertical image

To create a mirrored vertical image of the dataset we used in the previous example, we use the steps below:

  1. Open the worksheet that contains the dataset.
  2. Select cell A9 and type in the following formula:
=OFFSET(A$7,(-1)*ROW(A1),0)
  1. Press the Enter key and copy the formula across to cell F9 by dragging the fill handle. This creates a mirrored image of the bottom row of our dataset.
Table

Description automatically generated
  1. Drag the fill handle down to cell F14 to copy the formula to the range that is the same height and width as the source range.
  1. Adjust the width of the columns and the decimal places in range F9:F13 as explained in the previous example.

We get the mirrored vertical image of our dataset:

Explanation of the formula

=OFFSET(A$7,(-1)*ROW(A1),0)
  • In cell A9 the formula evaluates to =OFFSET(A$7,-1,0) and returns 00005 which is the value in cell A6.
  • As the formula is copied to other cells it updates accordingly and returns values in different cell ranges of the dataset.

Create a mirrored image of a picture imported into Excel

We can import a picture into an Excel worksheet and flip it horizontally or vertically as needed.

We use the following image to explain how we can create both a horizontal and vertical mirrored image of a picture:

Create a horizontal mirrored image of a picture

We use the following steps:

  1. Import the picture into an Excel worksheet using any of the options available in the Illustrations group of the Insert Tab:
  1. Select the imported picture and hold down the Ctrl key and drag horizontally across the worksheet to create a copy of the image.
Chart, bar chart

Description automatically generated
  1. With the copy of the picture selected, click Picture Format >> Arrange >> Selection Pane Arrow >> Flip Horizontal.

The picture is flipped horizontally:

Chart, bar chart

Description automatically generated

Create a mirrored vertical image of a picture

We use the following steps:

  1. Import the picture into the worksheet using any of the options in the Illustrations group.
  2. Select the imported picture hold down the Ctrl key and drag down the worksheet to create a copy of the image.
Chart, bar chart

Description automatically generated
  1. With the copied image selected click Picture Format >> Arrange >> Selection Pane Arrow >> Flip Vertical.

The picture is flipped vertically:

Chart, bar chart

Description automatically generated

Conclusion

In this tutorial, we have explored different ways that we can use to create a mirrored image in Excel. We can create a horizontal or vertical mirrored image of a dataset using the OFFSET, ROW, and COLUMN Excel functions.

We create a horizontal and vertical mirrored image of a picture using the Flip Horizontal and Flip Vertical options on the Selection Pane of the Arrange group on the Insert tab.