# Help me tame the dreaded Excel spreadsheet with the most basic of operations **URL:** \ **Category:** Factual Questions\ **Created:** [August 10, 2015, 9:19am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438 "2015-08-10T09:19:34Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Habeed](https://avatars.discourse-cdn.com/v4/letter/h/a587f6/32.png) [@Habeed](https://boards.straightdope.com/u/Habeed)\ **Post date:** [August 10, 2015, 9:19am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/1 "2015-08-10T09:19:34Z")
I have data that looks like this : ,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095, I would like to past the data in excel into a single **column**. No matter what I do, the stupid program wants to stick the data across by **rows**. So I can’t easily even tell how many data points there are, because the later rows are called like “row AA3” and stuff. Also, I’d like to have 1 column with the data, and then another column I would have incrementing to represent time, and then graph it… Online graphing programs want to display this kind of data by rows. Open Office, ditto. Everyone wants to do it backwards and nobody gives me an option I can find anywhere to do it by column. Is there another way I can print the data so that excel will want to stick it into a single column? I can’t use a carriage return between each number because that will eat up far too much space in the terminal… ---
**Author:** ![RadicalPi](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/radicalpi/32/9806_2.png) [@RadicalPi](https://boards.straightdope.com/u/RadicalPi)\ **Post date:** [August 10, 2015, 9:56am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/2 "2015-08-10T09:56:40Z")
I’m not quite sure this is what you need, but the basic way to transpose in Excel is: Select the range of cells. Do Control - C. Right click. Click on “Paste Special.” Select “Transpose.” Click on “Okay.” ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [August 10, 2015, 10:03am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/3 "2015-08-10T10:03:15Z")
I think you’ve got your row/column terms muddled. Columns have letters, rows have numbers. So you’re saying excel is posting that string into multiple columns instead of a single column. Which unfortunately I can’t replicate (I copied that string and pasted it into Excel 2013 and it’s all in cell A1). ---
**Author:** ![Habeed](https://avatars.discourse-cdn.com/v4/letter/h/a587f6/32.png) [@Habeed](https://boards.straightdope.com/u/Habeed)\ **Post date:** [August 10, 2015, 10:24am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/4 "2015-08-10T10:24:05Z")
Yes but I don’t want it in cell A1, I want it to extend down the column. So row 1, row 2, row 3, etc are all numbers. My terms are not muddled. I want there to be just 1 “letter” for everything in the string. ---
**Author:** ![j666](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@j666](https://boards.straightdope.com/u/j666)\ **Post date:** [August 10, 2015, 10:30am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/5 "2015-08-10T10:30:30Z")
What application are the values in now? Paste the values into Word Use Find & Replace, Replace All to change all the commas into paragraph marks [use “^p” in the “with” field] You should be able to Select All, paste into Excel, and get a column of values. If that doesn’t work, in Word, Select All and Convert Text to Table, then paste into Excel ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [August 10, 2015, 10:45am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/6 "2015-08-10T10:45:25Z")
Can you not just do Data \> Text to Columns then Transpose? ---
**Author:** ![BubbaDog](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/bubbadog/32/333_2.png) [@BubbaDog](https://boards.straightdope.com/u/BubbaDog)\ **Post date:** [August 10, 2015, 2:29pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/7 "2015-08-10T14:29:59Z")
> [@Habeed](#): > > I have data that looks like this : > ,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095,4095, > > I would like to past the data in excel into a single **column**. > > \*\*No matter what I do, the stupid program wants to stick the data across by **rows**. So I can’t easily even tell how many data points there are, because the later rows are called like “row AA3” and stuff. \*\* > > Also, I’d like to have 1 column with the data, and then another column I would have incrementing to represent time, and then graph it… > > Online graphing programs want to display this kind of data by rows. Open Office, ditto. Everyone wants to do it backwards and nobody gives me an option I can find anywhere to do it by column. > > Is there another way I can print the data so that excel will want to stick it into a single column? I can’t use a carriage return between each number because that will eat up far too much space in the terminal… Bolding mine. Maybe not what you are looking for but once the excel sheet has the data displayed across columns in one row you can copy it and then “paste special”,“transpose” that row into a single column like you desire. It’s a two step process and it requires manual intervention so if you had a lot of data rows that you wanted to make into columns it would be cumbersome. You could automate it via a VBA macro but I’m guessing that you aren’t familiar with excel subroutines. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 10, 2015, 3:31pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/8 "2015-08-10T15:31:17Z")
\*\*Sandra\_nz \*\*nailed it, but without much detail. The OP hasn’t said if the data is coming into excel via copy / paste or via import, but the differences are slight. If copy/paste: Start with a blank sheet. Paste the whole data blob into cell A1. Select cell A1 then choose Data \> Text to Columns. In the dialog box choose to break columns on commas. [OK] that. Now the data will be in cells A1, B1, C1, …, out to AQ1 or wherever. Select all the data. Copy. Move to cell B2. Select Paste Special \> Transpose. A new copy of the data will be in cells B2, B3, B4, …, B345 or wherever. Delete row 1; you don’t need that set of data any more. The remaining good data is now in cells B1 … B344 or wherever. Add your time data in cells A1-A344. Select the two columns x 344 rows. Choose Data \> Chart \> etc. & create your chart. If import: Don’t just open the text file using Excel. Start with a blank spreadsheet, then choose import. In the import wizard specify break values on commas and you’ll end up with the data in A1 … AQ3 or wherever. Then pickup from there with the directions above. ---
**Author:** ![Habeed](https://avatars.discourse-cdn.com/v4/letter/h/a587f6/32.png) [@Habeed](https://boards.straightdope.com/u/Habeed)\ **Post date:** [August 13, 2015, 3:56am UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/9 "2015-08-13T03:56:33Z")
Thanks. I switched to spaces between each data point, like this : 2501 2484 2469 2455 2440 2425 2410 2396 2382 2367 2353 2338 2324 2310 2297 2281 2268 Excel will accept the string and automatically put each number in it’s own cell. Then you select the horizontal row, cntrl c (cut will NOT work), choose a destination on the row down, and paste special appears…usually. Then transpose. What’s maddening is that excel’s rules seem arbitrary. Cut instead of copy? Too bad. Sometimes it doesn’t stick the string in separate cells. Etc. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 13, 2015, 1:41pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/10 "2015-08-13T13:41:39Z")
The differences in behavior between cut and copy is/are far from arbitrary. Copy captures a rectangular array of values & formatting to be pasted later elsewhere. Cut actually prepares to alter the fabric of the sheet itself. You’re preparing to move the cells themselves, not just the values they contain. When you cut then paste, all external references that point \*into \*the cut-from space will update to point to the corresponding place in the pasted space. If you copy then paste all external references that point \*into \*the copied-from space will remain unchanged and still point into those original cells. It makes sense in the former case that more advanced paste-special options like transpose not be available when pasting cutting versus pasting a copying. If they had been really logically complete in the UI design back in Excel 1.0 this distinction would have been made more explicit by there being two distinct pairs of bookended operations: Cut followed by, say, Move vs. Copy followed by Paste. It would be impossible to invoke Move after Copy or Paste after Cut. But the [Cut or Copy followed by Paste] metaphor already existed in enough apps that they decided they had to maintain backwards compatibility even then. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 13, 2015, 1:51pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/11 "2015-08-13T13:51:18Z")
> [@Habeed](#): > > Sometimes it doesn’t stick the string in separate cells. Etc. Imagine how happy you’d be if you were trying to import content that had been formatted with embedded commas as thousands separators within values and you discovered that Excel automatically parsed the input into separate cells based on both spaces and commas. There are almost always good logical reasons for why things are like they are. An attitude of “This is less than perfect for my immediate use case and is therefore defective” is a really poor mindset to have when operating any complex multi-purpose tool. No matter what you want to do, if the default behavior isn’t close but not quite right, then your desired behavior is only a few clicks away. The thing I’m not understanding about your overall issue is that since you apparently control the format of the data you’re generating to input into Excel, why don’t you just alter whatever generates that data to be in a format that Excel will naturally consume to give the shape of spreadsheet you want? ---
**Author:** ![Habeed](https://avatars.discourse-cdn.com/v4/letter/h/a587f6/32.png) [@Habeed](https://boards.straightdope.com/u/Habeed)\ **Post date:** [August 13, 2015, 1:51pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/12 "2015-08-13T13:51:43Z")
Perhaps. Every other application I have ever used or seen, cut just means “copy all this information and mark the original copy for deletion”. External references…eh. This is the first application I have ever seen that there is any difference in functionality. And, my data doesn’t have external references, so… ---
**Author:** ![Habeed](https://avatars.discourse-cdn.com/v4/letter/h/a587f6/32.png) [@Habeed](https://boards.straightdope.com/u/Habeed)\ **Post date:** [August 13, 2015, 1:58pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/13 "2015-08-13T13:58:02Z")
Microsoft should have the behaviors be the same if a particular data element has no external references. _Or_, all references should be dual ended inherently - excel would silently mark all cells pointed to by other cells on both ends. Moving or copying would then doing the same thing, and essentially an external reference would now update several sets of cells instead of just one, or the last set copied would be the live one. When I click the mouse to select a column, excel should know that when I paste data, I want it to be in column format. For that matter, excel is absolutely garbage for graphing data with default settings. Worse than a 1990s graphing calculator. Given this is the most common task I’ve ever used excel for, this is very sad. Graphs are literally the most backasswards turd imaginable by default. The second most common task, ironically, is using excel as a convenient template for holding text with no computational capabilities at all. I think that’s kind of funny, actually - one of the most common uses for excel isn’t even as a spreadsheet. ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [August 13, 2015, 1:59pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/14 "2015-08-13T13:59:18Z")
Use Matlab or Python/Numpy/matplotlib for manipulating and plotting significant amounts of time series or multivariate data. They don’t care whether data is in row or columns, and can transpose vectors witha single parenthetical command. Excel’s execrable data handling and transformation capabilty is only one of its many deficiencies in use as a data visualization and manipulation tool, which is a role it was never designed to perform and has been only poorly adapted to. Stranger ---
**Author:** ![j666](https://avatars.discourse-cdn.com/v4/letter/j/9de0a6/32.png) [@j666](https://boards.straightdope.com/u/j666)\ **Post date:** [August 13, 2015, 6:33pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/15 "2015-08-13T18:33:51Z")
> [@Habeed](#): > > Moving or copying would then doing the same thing, … No, sometimes one wants the reference to change when the value or formula is copied. ---
**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\ **Post date:** [August 13, 2015, 7:24pm UTC](https://boards.straightdope.com/t/help-me-tame-the-dreaded-excel-spreadsheet-with-the-most-basic-of-operations/727438/16 "2015-08-13T19:24:18Z")
> [@Habeed](#): > > Microsoft should have the behaviors be the same if a particular data element has no external references. _Or_, all references should be dual ended inherently - excel would silently mark all cells pointed to by other cells on both ends. Moving or copying would then doing the same thing, and essentially an external reference would now update several sets of cells instead of just one, or the last set copied would be the live one. … You complain the behavior is not obvious or intuitive and so you propose different behavior that is even more subtle and contingent on even more influences that aren’t apparent at the site of the user’s action. :eek:😕 IOW, I see your reaction to your proposed behavior as “I pasted something and it behaved differently to something else I pasted just because something in some totally different part of this workbook was different. WTF is up with that?? How am I supposed to know what it’s going to do next?” Which would in fact be a very legitimate criticism. As \*\*Stranger \*\*said: Excel is not a database nor is it a text editor. Despite the number of people who want to use it for that. It is a numerical Swiss army knife with all the attendant risks of injury from the fish scaler while using the toothpick.