How do I pivot a range of cells, i.e. from column to row?
How do I pivot/transpose the data from a range of cells from, say, a column
to a row? I imagine it should be very simple, but I haven't found out how
yet. For instance:
1 data 1
2 data 2
3 data 3
4 data 4
column# 1 2 3 4
data1 data2 data3 data4
office 2003 excel
How can I merge two rows into one row? Also... where is that in help? I
spent about 45 minutes trying to find it.
if you want to merge A1:A10 and B1:B10, select C1:C10 and type in C1
=A1&B1 and ctrl+enter.
Neo Cheung wrote:
> if you want to merge A1:A10 and B1:B10, select C1:C10 and type in C1
> =A1&B1 and ctrl+enter.
Thanks... works great. Now, how do I get it to be just the data and not
the reference to the other rows.
To eliminate the formula leaving only the value ---
select column C, Ctrl+C (edit, copy), ...Hide/Delete entire rows based in the content of one cell
Hello all. I have a spreadsheet that is over 500 rows long. As it is I have
no use for all of the rows at the same time and have to keep hiding and
showing them as need arises. Is there a macro to hide chunks of it based on
the value of one cell of the row? In other words, en each row I will have a
formula like =if(a1=0,"HIDE","") and this value will tell the macro wether to
hide the row or not. I tried case.select but it takes a LONG time and I would
have to write a piece of code for every line. FYI, the rows that need hiding
will be in sequence, in other words, fro...Auto height rows
I am working in Excel 2000 for Windows with cells that are
NOT merged. The individual cells have varying amounts of
data (anywhere from a few characters to over 1000
characters). All text is wrapped. When I double click on
the row to auto height, it adjusts the height but the
printed version still cuts off text. In most cases the row
height does not come anywhere near 409.5 pixels.
I saw the problem with bottom aligned text, but all my
cells are top aligned.
I am working with 19 files that include at least four
worksheets with 300+ rows of data. It's getting time
consuming to re...Why have I got some repeating rows after a Excel 2003 sort
I copied and sorted an existing worksheet which had been obtained from an
unknown source. I found some of the rows were duplicated after the sort, but
with the number column showing different results from the original row and
The original worksheet had no duplicates.
Why would this happen? I have used older versions of Excel for years and
this has never happened before.
are you sure there were no hidden rows in the original data?
"Jayne" <Jayne@discussions.microsoft.com> wrote in message
news:D5C7F6BE-EE34-434A-B829-68B2FA6C4AD2@...I need excel to only use the last 26 rows in a chart
I have a large spreadsheet that has data in it that is entered weekly. I
would like for the chart to grab the most recent 26 weeks of data
automatically. Does anyone know of a formula that will do this?
If you haven't already, you may want to establish dynamic ranges for your
data set. This will allow you to automatically update your chart(s) as new
data is entered It will also allow you to plot the last number of days/weeks
that you want.
This post explains how to set up dynamic range names and plot user specified
number of days. It should get you started on dynamic range ...Every Other Row
Is there a way to get Excel to calculate Standard Deviation on every
other row (there are 2400 rows, so manually putting them in isn't really
** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software! Free Support at
One way to try ..
Assume the source data is in A1:A2400 in Sheet1
In a new Sheet2
Put in A1: =OFFSET(Sheet1!$A$1,ROW(A1)*2-2,)
Copy A1 down to A1200
This'll extract every other row from Sheet1's A1:A2400
(i.e. from Sheet1's A1, A3, A7, A9, ... A2399)
Then just put...make row height a little bigger in a wrapped cell
I've got a sheet with columns at work
date text (sometimes multi-line wrapped) $ nnnn.nn
I can wrap text no problem but I hope approximate double spacing so I
don't have to put a blank row between each item. I need the space
between items so the text isn't squashed all together for a court form
I guess I want to be able to say height of cell necessary for the
wrapped text however much it might be plus n for each row height.
I can't find a way. Is it possible?
Excel 2002 Win xp
Thanks for your help.
If you're using merged cells, then this w...Rows and Columns
I am using Excel XP
Each worksheet has 65,536 rows and 256 columns according to specifications.
Is there a way to change these number to a smaller amount?
I know I can hide rows and columns, but wondering if I can not have them to
The short answer is NO. Just don't format more than you need.
"EZDOIT" <Please@smile.com> wrote in message
> I am using Excel XP
> Each worksheet has 65,536 rows and 256 columns according to
>...Need help formatting rows
I am using excel 2007
I would like to know how to do the following if possible
have column A add column B and put the total in column C and then put the
amount of column C in column A of the next row.
EX: 1+1=2 in row 1
then automatically have it put 2 in row 2 column A
Thanks in advance if anyone can help.
Cell C1: = +A1 + B1
Cell A2: = +C1
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
> Hello everyone,
> I am using excel 2...adding rows #2
How do I add rows without changing cell location in the formula.
The data for the formula is always located in cell B4 and if I
add a row on top of B4, I want the formula to remain with B4.
Right now, if I add a row, the cell in the formula goes down
Hope for help
If I understand, you're adding a row higher than B4. This shifts B4 down,
but you want the "new" B4 to be the data for the formula. If this is the
case you could try this:
That is, as long as you don't add a new top row.
...Crosstab Row Heading Issues
I have Access 2003 and when I do a cross tab I cannot bring in more than 3
field values for row headings - my coworker (who has Access 97) can bring in
>3 row headings. Is this a paremter setting in Access 2003?
I believe that the Wizard can't handle more than 3; however, you can do more
with SQL. Try creating a crosstab with the three headings, them modifying it
at SQL level to add more.
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> I have Access 2003 and...Can Excel sort rows by color?
I want to sort a spreadsheet by the color of the rows.
Here's a website that will help you accomplish this:
> I want to sort a spreadsheet by the color of the rows.
Grant, have a look here
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Grant" <Grant@discussions.microsoft.com> wrote in mes...format group of rows based on condition
I've seen answers to similar problems, but nothing as complex as this:
I have the following set-up (simplified)
Type City Store
actual Portland TB
bid Portland TB
actual Memphis AU
bid Memphis AU
bid Memphis AU
I need to format in VBA (since this is over 3000 rows deep), in the
Insert a row in between a change in the cities column. Then surround the
grouping by city with a bold outline. Color the row with the bid gray and
color the rows in between the cities blue.
The sheet has...highlight consecutive duplicate rows (re-sending)
My apologies if anyone responded to this when I originally posted. I
didn't download enough headers and I can't recapture the original....
I want to highlight any row that has the same data as the previous row.
For example, if A:3=A:2 and B:3=B:2 and C:3=C:2, I want to highlight
Row 3. I also want to repeat this logic throughout the spreadsheet.
Can I do with this with Conditional Formatting? Or, would I need to use
VBA...in which case, how would I code this?
Thanks in advance.
I have no idea if anyone responded to your earlier post, but you could check
You can ...repeat rows at top
Once you've created your "standard" colum headings, how do you repeat
them on every page
Message posted from http://www.ExcelForum.com/
"al_rwli >" <<email@example.com> wrote in message
> Once you've created your "standard" colum headings, how do you repeat
> them on every page
File > Page Setup > Sheet tab
Print titles: Rows to repeat at top
File>page setup>sheet and put the reference in rows to repeat at top
"al_rwli ...Sum Function Without Including Hidden Rows?
I'm using Excel 2000 and have *hidden* several rows that include numeric
data. I want to do a sum function on some of the columns intersecting
those rows, but when I insert an AutoSum function, it includes data in
the visible rows AND the hidden rows. Is there an easy way to perform
a sum function that *only* includes the rows that are *-visible?-*
(Which rows are hidden/visible are subject to constant change)
Thanks for your help!
Message posted from http://www.ExcelForum.com/
You would need a UDF for that or if there is a pattern (like if every other
column/row is hidden)
th...Advanced Sorting (4000 rows) followup Q.
Green John blue cats $50.00 Happy
Green 2 John blue cats $50.00
Green 2 blue John cats $50.00
Green 2 blue cats John $50.00
Green 2 blue cats $50.00 John
What I want: For example, I want "John" to be sorted to
the first column for each row.
Problem: In my original table, there are over 20+ Columns
and 4,000 rows. Sorting by row, three at at time would
take forever, especially since I have over 100 different
databases that I need to do this on.
Without sorting by rows three at a time, what's the best
solution? If there's no way to do this in excel without a
t...Windows Media Center crashes repeatably when adjusting TV settings
I have Windows Media Center running on Vista SP2, on a brand new
CyberPower PC with an Avermedia TV tuner card.
In Windows Media Center, the following sequence of selections always
causes it to crash:
Configure your TV or Monitor
Any idea what's wrong?
-- Steven L.
Look at the Event Viewer to find out clues.
"Steven L." <sdlitvin@earthlinkNOSPAM.net> wrote in message
> I have Windows Media Center running on Vista SP2, on a brand new
...Problems inserting rows
How can I insert a row in Excel when the assistant says it's not possible?
The assistant says that inserting rows would shift data off the worksheet,
rather into the next blank rows
XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
Remember to look for comments and merged cells.
> How can I insert a row in Excel when the assistant says it's not possible?
> The assistant says that inserting rows would shift data off the worksheet,
> rather into the next blan...VBA to adjust Row Height
I am new to writing VBA and I am having trouble writing code that
achieves the following:
If text in D2>115 characters then adjust row D's height to 30, else
row height remains 20
followed by the same if then statment for rows E thru M (i.e., If
M2>115 Then Row M height = 30
Any suggestions would be greatly appreciated.
you are tlaking about columns but you want to change the row height?
I asume you're talking about row 2 (in your example?). Note you can't
change the heigth of a row individually per column. If you change it it
will apply for all columns.
So maybe ...Returning the last value in a row
What formula do I need to return the last known value in a
ROW 1 contains 5 values in COL a,b,c,d and e
ROW 2 contains 2 values in COL a, b is empty,c,d is empty,
e is empty
How can I return the last value in each respective ROW
(COL e and COL c)?
I think I need something to jump from one colum to the
next and stopping when it finds a value but still
continues to the end of the specific amount of columns
just in case there is another value
Hope I explained this well
if you have no blank columns in between use something like
for row 1 ...Copy cells to variable number of rows
I need to copy a formula (that a macro has entered at W3 & X3) down to the
last row for that particuliar wk. I've figured out how to get the number of
rows for that wk( in this case i=93, i will change for each wk) but can't
figure out the syntax for the Range:
Range("W4: ? ").Select
Range("W4: ? ").Select
"Acct Supr - DCTC" wrote:
> I need to copy a formula (that a macro has entered at W3 & X3) down to the
> l...Autofit Row Height #3
I have a column on sheet 1 that is set to wrap text so that the row height
increases and decreases as more text is entered into the cell. This works
Users enter a number on sheet two where there is a lookup function that
returns the appropriate text from sheet 1. The problem is that when the text
is returned by the function, the row height does not adjust to fully display
all of the text in the cell. Is there a way of automating this?
Any help would be appreciated.
...Copy & Paste lost row heights
I have three price list spreadsheets that I want to combine into one
All of the layout column widths are the same as are the row heights
However when I try to copy and paste one price list onto the bottom o
another all of the row heights are lost and the product images that
inserted alongside each description are banked up one after the other
Paste special may be the answer but I cannot find the answer if ther
is one. There is a lot of work in resetting row heights nad movin
images so if there is a way of pasting exactly what I see on the scree
on one spreadsheet into another spreadsheet the...