# From Several Columns to a Single Column with a Formula

This formula is useful in case you have a data table in the following format and you want to transform it to “Preferred Results” format.

A-Data-1 | B-Data-1 | C-Data-1 | D-Data-1 |

A-Data-2 | B-Data-2 | C-Data-2 | D-Data-2 |

A-Data-3 | B-Data-3 | C-Data-3 | D-Data-3 |

A-Data-4 | B-Data-4 | C-Data-4 | D-Data-4 |

**Preferred Results:**

A-Data-1 |

A-Data-2 |

A-Data-3 |

A-Data-4 |

B-Data-1 |

B-Data-2 |

B-Data-3 |

B-Data-4 |

C-Data-1 |

C-Data-2 |

C-Data-3 |

C-Data-4 |

D-Data-1 |

D-Data-2 |

D-Data-3 |

D-Data-4 |

**Excel Formula:**

**$A$1:$D$4** – change to your data range

**A$7:A7** – change to the first cell where your results data begins. In our example that would be the cell holding A-Data-1 data.

Apply the formula to the first data results cell and fill down.

=INDEX($A$1:$D$4,MOD(ROWS(A$7:A7)+ROWS($A$1:$D$4)-1,ROWS($A$1:$D$4))+1,INT((ROW()-ROW($A$7))/ROWS($A$1:$D$4))+1)