In above solution, the helper cells are giving us running numbers from 1 to 6 (and 1 to 7). Sometimes, we cannot really use a helper column. See the illustration below to understand how this works.įormula Solution #2 – Using INDEX formula & no helper cells Copy this formula all over and you are done!.Now use INDEX formula to transpose data like this:.Similarly, write numbers 1 to 7 beside it (cells B23:B29).About this, write numbers 1 to 6 (cells D20:I20).That means, the transposed table will have 7 rows & 6 columns. Lets also say myData has 6 rows & 7 columns. Lets say, we have named our original data as myData. Formula Solution #1 – Using INDEX & Helper cells to transpose a table So whenever original numbers change, you must waste precious key strokes & time re-doing the transpose. Go to an empty area and open Paste Special (CTRL+ALT+V)Īlthough this approach works, it creates a copy of your original data. Excel has a built-in feature that lets you transpose data with a single click. The easy solution – use Paste Special > Transpose That is, we want to take all rows in our data & make them columns. Today lets tackle a familiar data clean-up problem using Excel – Transposing data.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |