Skip to content

How To Blog

  • How to Retrieve Archived Emails in Gmail How-To
  • Fix “Could Not Find This Item” When Deleting in Windows Help Desk
  • 5 Ways To Get Free Robux 2022 Gaming
  • How to Run Linux on Windows 10 How-To
  • How To Install Bleeding Edge iOS Features Early With Beta Profiles Tutorials
  • How to see Blocked Content on Websites How-To
  • Interesting Features In The Windows 10 Settings You May Not Know About Windows 10
  • Map a Folder to a Drive Letter in Windows Free Software Downloads

2 Ways to Use Excel’s Transpose Function

Posted on October 9, 2020 By blog_786 No Comments on 2 Ways to Use Excel’s Transpose Function

If you do a lot of Excel work at home, work, or home office, you have the option to customize your worksheets exactly the way you want. However, each person has their own quirks and methods of working with Excel. Some people prefer to organize their data into columns and others into rows.

If someone gives you an Excel file and you prefer the data to appear in a different order, you can easily turn columns to rows and rows to columns using Excel’s built-in transpose function. There are two main ways to transpose data in Excel: by copying and pasting, or using the transpose function. While both will carry your data, they work differently, which I will explain below.

Switch using the TRANSPOSE function

Suppose someone gives you an Excel file with column-ordered data and you prefer the data to be rows.

2 Ways to Use Excel’s Transpose Function

Start by choosing the area where you want to move data from columns to rows. Note that in the above example, the data is taken from A1 to B6. This is a 2 by 6 (2×6) datasheet. To select an area to transpose, you need the opposite area or a 6 by 2 (6 × 2) area. Starting at cell A12 (or wherever you want to transfer data), select a 6×2 area.

– /

2 Ways to Use Excel’s Transpose Function

Notice that the cells we selected include A12 through F13, a 6×2 area. With this area selected, click the formula bar above. Before you start typing the formula, make sure the selection is still selected. Now enter the following formula into the formula bar

= transpose (a1: b6)

but don’t press Enter yet. Entering this formula in Excel is different from most other formulas. When you press Enter, you need to hold down the Ctrl and Shift keys. So press Ctrl + Shift + Enter. Your Excel worksheet should now look like this:

2 Ways to Use Excel’s Transpose Function

The keyboard shortcut Ctrl + Shift + Enter will enclose the formula in curly braces. This tells Excel that the output of the formula will be an array of data and not a single cell. Oddly enough, you can’t type the parentheses yourself; you must use the key combination Ctrl + Shift + Enter.

When you use the transpose function to transpose the data, both datasets are linked. This means that if you change the data, for example in A1, it will also change the value in A12. Even if the cells have formulas, the values ??will be updated in both places.

In the example below, I have some data on workers, hours worked and total wages. I used the transpose function to transpose the data.

2 Ways to Use Excel’s Transpose Function

I went ahead and changed one name and hours worked for all people, and as you can see, both datasets are in sync.

2 Ways to Use Excel’s Transpose Function

This also means that if you delete cells or rows of the original data, you will get a reference error in the transposed cells! If you don’t want the two datasets to be related, it is better to use the copy and paste method described below, which duplicates the data rather than concatenating it.

Switch using copy and paste

An easier way to transpose data in Excel is to use the copy and paste function. The data will not be linked, so you can safely delete the original dataset if you want. However, if you make changes to the original dataset, they will not be reflected in the transposed data, since it is just a copy.

Select the data you want to transpose and then right-click and select Copy, or press CTRL + C on your keyboard.

Now right click any empty cell where you want to paste data and click the Transpose button. If you just hover over the transpose button, you will see the data on the sheet in real time.

2 Ways to Use Excel’s Transpose Function

If you change any of the data in the original dataset, it won’t affect the transposed data, and vice versa. That’s all.

–

Office Tips

Post navigation

Previous Post: Quickly Add Date and Time Stamps to an Excel Worksheet
Next Post: How to Use Excel’s What-If Analysis

Related Posts

  • Paste Text into Word Without Formatting Office Tips
  • How to Insert an Excel Worksheet into a Word Doc Office Tips
  • How To Add New Fonts To Microsoft Word Office Tips
  • How to Change the Slide Size in PowerPoint for Better Presentations Office Tips
  • The Best VBA Guide (For Beginners) You’ll Ever Need Office Tips
  • How To Merge Cells, Columns & Rows In Excel Office Tips

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Archives

  • June 2023
  • May 2023
  • April 2023
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • March 2021
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018
  • September 2018
  • August 2018

Categories

  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • Linux
  • Linux Tips
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Windows XP Tips
  • Wordpress

Recent Posts

  • Can I change the file type in Windows 10?
  • How to create line graphs in Google Sheets
  • Best Ways to Create Your Own 3d Printed Lithophanes?
  • Top 5 3D Modeling Apps for iPad Pro
  • How to Fix the “This site can’t be reached” Error in Google Chrome

Recent Comments

  1. How to reset Windows 10 from factory settings - How To Blog on The Easiest Way to Clean Install Windows 10
  2. How to reset Windows 10 from factory settings - How To Blog on 7 Best Programs to Immediately Install on Your New Computer
  3. How to reset Windows 10 from factory settings - How To Blog on What To Do If You Think Your Computer Or Server Has Been Infected With Malware
  4. 9 Best Ways to Fixing Safari Not Working on Mac? - How To Blog on How To Hard Reset a Mac OS X Computer & Reinstall The OS
  5. How to uninstall Avast antivirus on Mac - How To Blog on 4 Ways To Uninstall Apps On Mac
  • MobiMover: An iTunes Alternative for Windows Users Reviews
  • Best pipe trades pro calculator 2020

    Best pipe trades pro calculator 2020

  • How to Create Custom Google Now Commands with Tasker How-To
  • Turn Off Automatic Updates on iPhone iPhone
  • Mark all Your Gmail Messages as “Read” in One Go How-To
  • How to Connect Your Windows pc to a Mobile Hotspot Computer Tips
  • how to turn off PS4 (controller, button and auto) five best ways to do it. Gaming
  • Best ti 84 ce plus graphing calculator 2020

    Best ti 84 ce plus graphing calculator 2020

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version