Skip to content

How To Blog

how to

  • how to watch youtube tv on roku Cool Websites
  • Find, View and Analyze BSOD Dump Files How-To
  • How to Convert an ODT File to Word the Easy Way How-To
  • How to Enable Safe Driving Mode on JioMeet How-To
  • how to schedule a text message on iphone iPhone
  • How To Install Only Safe Browser Extensions How-To
  • How to Make a GIF from a Video the Easy Way How-To
  • 10 Best Chrome Flags to Enable to Improve Your Browsing Experience Reviews

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

  • How To Edit Or Modify a PowerPoint Template Office Tips
  • How To Fix Outlook Keeps Asking For Password Issue Office Tips
  • How to Use Excel’s Speak Cells Feature Office Tips
  • How to Use If and Nested If Statements in Excel Office Tips
  • How To Insert An Animated GIF In PowerPoint Office Tips
  • How To Recall An Email In Outlook Office Tips

Leave a Reply Cancel reply

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

  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • How-To
  • iOS
  • iPad
  • iPhone
  • Linux Tips
  • macOS
  • MS Office Tips
  • Networking
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • January 2023
  • December 2022
  • 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
  • July 2021
  • June 2021
  • May 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • 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

2021 Android ) Apple apps Best browser change Chrome Closing words Concluding remarks Download Error Facebook From Google Image Internet Keyboard Mac make Movies Music online Packaging phone? Photos Print Search Shortcuts sites Switch Time Tips Tricks turn using Video Videos Watch What With Word Working? your YouTube

  • How To Clear Outlook Cache Office Tips
  • Can I still see my Spotify wrapped 2022? Computer Tips
  • How To Build a Basic Web Presence If You Have No Coding Skills Cool Websites
  • These Services Are Free During Quarantine How-To
  • The Definitive Guide to Battery Charging Featured Posts
  • The Best Mac Keyboard Shortcuts to Learn OS X
  • Replace Command Prompt with PowerShell and Vice Versa in Windows 10 Windows 10
  • Fix Black Screen on Windows 10 Laptop with Intel HD Graphics Windows 10

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Manage Cookie Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage vendors Read more about these purposes
View preferences
{title} {title} {title}
Go to mobile version