Excel TV ExcelTV
Excel Functions & Formulas

Index Function In Excel To Move Columnar Lists To A Grid

Updated
Index Function In Excel To Move Columnar Lists To A Grid

Sometimes we have analyze or sort through data all of which is in a single column. The problem occurs when not every entry in that column represents the same type of data. For example, you might have names, addresses, email IDs and so on, all in just one column. It is really difficult to analyze it without turning it into a grid. And there might be no way to extract the data in any other format from your source.

Don’t feel that you’re stuck! There is a way to save you from hours and hours of cut and paste. And this is exactly what Szilvia Juhasz (aka XSzil) is here to teach us today.

So, let’s begin.

1 – The Data In Excel Is Sometimes Ugly

Suppose the data we have has name of a person, followed by address line 1 and address line 2. And it is all arranged in a three-line layout followed by one blank cell for each person in the data. An illustration of the data is below.

1

2 – The Setup – Create a Grid In Excel

The first step to converting the columnar data into a grid is a follows:

  1. Label columns of the grid with 1, 2 and 3. Yes, these represent the fact that each data point has 3 items.
  2. Label the rows using 0, 4, 8 and so on. It represents the cells each data point ends on.

While labeling the rows looks like a manual task, it actually isn’t. The fact that the data has the exact same layout means that the jumps will always be of 4. One can use Autofill to get the series in seconds.2

3 – Using The Index Formula In Excel

We now plug in the following formula in the cell corresponding to (0, 1) in our grid, and then drag it to cover the entire grid:3

Note the following about the formula:

  1. We select the column with the 3-line data and turn its reference into an absolute one. This is to fix it when the data is dragged across.
  2. We fix the column of reference on 0, and the row of the reference on 1.

4 – The LookUp Results

It works! As you can observe in the image below, it picks up the names in column numbered 1, address line 1 in column numbered 2, and address line 2 in column numbered 3.4

And there you have it, your columnar list in a grid, ready for cleaning, sorting and analysis.

What’s next?

Remember all that extracted data sitting in some forgotten folder? It is time to make use of it now that you know how to give it structure in seconds.

And do not forget to share this wonderful technique with your colleagues and loved ones.

Written by

Jamani Arsalan
Jamani Arsalan

Consultant, International Professional Services

  • Project Management
  • Statistical Analysis
  • Health Analytics
I am a consultant, currently based in Middle East, at an international professional services firm. My work largely revolves around project management, and statistical analysis. And my professional interests include developing my knowledge within the discipline of health analytics.

Read more articles by Jamani Arsalan

Editorial standards

Fact Checking & Editorial Guidelines

Every article on Excel TV is held to a published editorial standard. The goal: accurate, current, and useful — without filler.

  1. Expert review. Drafts on technical Excel topics are reviewed by a contributor with hands-on, working knowledge of the feature being covered.
  2. Source validation. Claims about Excel behavior are tested in current Microsoft 365 builds. Third-party product claims are sourced from the vendor's own documentation.
  3. Disclosure. Affiliate links, sponsorships, and any commercial relationships that influenced a piece are disclosed in-line and at the foot of the article.
  4. Updates. Articles are revisited when Microsoft ships changes that affect the content. The most recent revision date is shown on every post.

Spot a problem? Email editor@excel.tv and we will look at it.

Subject-matter review

Reviewed by Subject Matter Experts

Technical Excel articles are reviewed by contributors with verifiable, hands-on experience in the topic — not generalist editors.

  • Qualified reviewers. Reviewers include Microsoft Excel MVPs, working business-intelligence practitioners, and Excel TV editorial staff. See each author's page for credentials.
  • Current to a known Excel build. Procedural articles state which Excel version they were validated against. Where Microsoft has since changed behavior, the article carries an inline update note.
  • Clarity check. Reviewers verify steps are reproducible by a reader at the assumed skill level — not just technically correct in a vacuum.

Want to contribute or review for Excel TV? See the about page.

Comments (1)

Historical comments preserved from the WordPress archive. Commenting is no longer active.

  1. Amey

    Yah,

    This is a good way, i have used this type of data for example for teaching recording macros. This technique worked though. You guys are awesome