**How To Convert Rows To Column In A Table Without Zeros- Using The Transpose Function**

Hey! So you have this large chunk of data and you just discovered that it has to be in the reverse direction, right?

The **TRANSPOSE **feature has a number of unique attributes.

- The
**TRANSPOSE**feature enables you to set your worksheet in your peculiar pattern. - The
**TRANSPOSE**function is good for**REORIENTATION**of data that is felt to be in an awkward row and column pattern due to the size.

Perhaps you’ve tried to **TRANSPOSE **the values on the table but keep having that awry zero-snag!

If your table contains blank cells, you can seamlessly **TRANSPOSE **the table by using a basic formula that integrates the **TRANSPOSE** and **IF** functions. In this example, the basic formula in Cell is given below.

**Basic formula****=TRANSPOSE****(IF(C7:J8="","",C7:J8))**

**EXAMPLE **

**Step 1**

Type the data into the spreadsheet as shown below.

*Figure 1- How To Convert Rows To Column In A Table Without Zeros- Using The Transpose Function*

**Step 2**

Type **=TRANSPOSE(IF(** on the ** fx** bar. Highlight the data as shown in figure 2 below. You will notice that the Cells are

**C7 and J8**. This means that you can also type the cell at one extreme end

**(C7- SALES REP CELL),**

**put a colon**, and then type the cell at the other extreme

**(J8- A BLANK CELL).**

**Figure 2- How To Convert Rows To Column In A Table Without Zeros- Using The Transpose Function**

Now, complete the content on the ** fx** bar by typing this

**=””,””,C7:J8))**

The **basic formula** as stated becomes **=TRANSPOSE****(IF(C7:J8=****"",""****,****C7:J8))**

**Step 3**

Now, since there are eight rows and eight columns, we will highlight 16 cells as shown below.

**Figure 3- How To Convert Rows To Column In A Table Without Zeros- Using The Transpose Function**

**Step 4**

Type the **basic formula** or simply paste it. Now, **PRESS AND HOLD** the **CRTL BUTTON+ SHIFT BUTTON and press ENTER**. You have **TRANSPOSED** the data in **figure 4 **as shown below without having zeros.

Now we are done. But you might want to ask why we keeping having zeros when **TRANSPOSING. **

Take a look at the basic formula in **figure 5. **The absence of this =””,”” will cause Excel to fill a blank cell with zeros. However, its presence in the basic formula causes Excel to replace the zeros with blank cells.

**Figure 4- How To Convert Rows To Column In A Table Without Zeros- Using The Transpose Function**

**Figure 5- How To Convert Rows To Column In A Table Without Zeros- Using The Transpose Function**

## Leave a Comment