< Go Back

Transpose table without zeros

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

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar