Images loading below. While you wait, can you answer this Excel related tip? Guess the answer then click SEE ANSWER to see how it can be done.

1. How do you copy from Excel (see 1 below) and paste into Word (2) WITHOUT seeing the filter button, gridlines, comment notes and more?

Guess and then view answer to see how it can be done!

2. What is the shortcut to ONLY select the visible cells? Guess, then click 'See Answer' as it can be done.


Transpose vs Unpivot

One of the most common data cleanup issues is changing the structure of the data. Perhaps rows need to become columns and columns rows. In some cases you actually may need to turn a report back into a database. The terminology for this is often mistaken which means when you try and find a solution you use the wrong tools in Excel. The terminology to get right is transpose vs unpivot.

Transpose (turn rows into columns and columns into rows)

When you transpose, you want everything to change around. So below the title and dates move from rows to columns. The total number of cells used also stays the same. So the Original is 4 columns by 5 rows (20 cells) and the Transposed is 5 columns by 4 rows (20 cells). You have just swivelled them around.

Transpose vs Unpivot

The easiest way to Transpose data in Excel is to use the Paste Special Transpose button. You can learn all about Paste Special in the Excel beginner course.

Unpivot (you need to normalise the data)

An unpivot reformats the data to make it easier to use in Excel. So below you will notice it ‘creates’ additional rows and makes sure that each bit of data appears in its own line. It will generally create a new column/s and increase the overall number of cells used i.e. going from

  • 4 columns by 5 rows (20 cells) to
  • 3 columns by 13 rows (36 cells)
Transpose vs Unpivot

We have found that most times you actually want to do an Unpivot rather than a Transpose. The problem is that performing an unpivot has been very difficult in Excel for years. However, now the new Get & Transform tool (aka PowerQuery) allows you to do this very easily.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Transpose cells

Data Cleanup in Excel