repost: split excel columns
Thank you for the feed back on the below question. When
using "text to columns" it seems to create new columns and
the info. in one column can be split but I was looking to
essentially create two columns in one. For instance,
within column D which was widened, I wanted two columns in
rows 5:15. I'm trying to convert something into an excel
template that is too long and don't want to change any of
the column widths already in place. I know you can do this
in Word when transferring an excel table. You would right
click on the cell and there is a function called split
...Sum a column that meets two criteria
I need to sum a column of numbers if it matches two different criteria.
I can set up the SUMIF easily for meeting one criteria, but I need to
also sum the column if it meets that criteria, and another. For
A B C
1 150 ABC MS1
2 200 DEF MS0
3 100 LMN MS0
4 125 ABC MS1
5 175 LMN MS1
6 225 DEF MS0
I need to have a formula that would say <<Sum column A IF column B =
"DEF" AND column C = "MS0">>. (and so forth for the other
I know there has to be a way to do this, probably using a combination
of an IF and SUMIF functions - but i keep...Lookup two columns
I want to compare the contents of two (adjacent) cells in one sheet with two
adjacent cells in another sheet (within one workspace) and if the *pair* of
cells are the same, deliver the value in the cell a few columns along (if
you know what I mean - like lookup but comparing two cells). The cells are
If you are comparing A1-B1 sheet 1 to A1-B1 sheet 2, then
=IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1),"They match","no match")
If you have to "lookup" A1-B1 against the whole columns of A and B on
sheet2, then kyou co...Nice Column Graph
So I have data like this:
Year Month #start #end
2001 1 4 2
2001 2 6 5
2001 3 7 1
2001 4 5 4
2001 5 2 6
I'd like to make a column chart where the year and month are on one
axis and then I have a column for each #start and #end for each
month/year pair. Is there a way to tell the chart wizard that I want
to use those two columns for the x axis and then the #start and #end
columns for the other?
Remove the 'Year' and 'Month' text from the 2 cells and then select and
use the chart wiza...Calculate the % increase for two columns
I have a pivot table, the data was first display by date, i know i can use
the grouping function to group data into monthly basis. But I want to know
that can I set the formula to calculate the months difference between, say
the sales amount of June & July, and the % of the difference??
If you have a grouped field, you won't be able to add a calculated item
to the pivot table. In the source data, you could add a column to
calculate the month for each record.
Refresh the pivot table, and add the new field
Add another copy of the Data field to the data area
Right-c...Count column difference
Using MSExcel 97.
I have two columns of data
e.g. A1: A4, containing values 5,10, 3, 6
B1:B4, containing values 3, 8, 7, 4
I wish to perform a count (e.g. in C5) of the number of rows where the
value in column A exceeds the respective value in column B (in this
case count = 3, as A1>B1, A2>B2, and A4>B4).
Just cannot get my formula right. Tried using an array (but difficult
when comparing the difference between two columns), and COUNT.
Thanks in advance for any suggestions.
~~ Message posted from http://www.ExcelTip.co...Text to Columns from drop down list update
I need to perform a Text to Column conversion from a drop down list, but I
also need the extracted value to be updated if the value in the list is
Drop down list has 2 values:
If the user selects 1 I can easily extract out to another cell the value 1
using Text to columns, however if the user then changes the choice to 2 the
text to columns extraction is not updated to 2.
Is there a way to update changes in the original cell using text to columns?
Or is there another way I can assign a value to a drop down menu choice in a
different cell while havi...Where free 2 sigma statistical process control chart, free?
where can I download a free statistical process control chart template?
I have a sheet in which in row 3 there is data like this:
ColumnA B C D E
Customer name 1-30 30-60 60-90 90-270 270-300
what is want is that 270-300 should move in front of 1-30, I mean t
say that column F should move in front of Column B but after column A
movement should be on basis that in column F, it is written 270-300
and columns may change , i mean to say that sometimes there is no 30-6
and 90-270 column
columns may increase or decrease
do we have a solution to this
thank u al
Message posted from http...Validation
I would like to have combo box functionality for the data validation feature
in Excel 2000. This doesn't seem to be available in the native validation
setup dialog box. Am I missing something? I would like to display the
validated items list in the leftmost column and have column(s) of
description display to the right of each list item (so I can tell which list
item I should select). Suggestions?
Debra Dalgleish has instructions for creating dependent DV lists.
Gord Dibben Excel MVP
On Tue, 08 Jun 20...Constant List
Is there an easy way to return the values of the ChartType
constants? Excel 97 does not seem to list them in help and
I have not been able to find them in google.group
Select xlChartTypes in the Classes pane of the object browser. The
constants are named in the members panel. If you select one, it provides
the numeric equivalent at the bottom of the object browser, e.g.,
Const xlArea = 1
Member of Excel.XlChartType
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
> Is there...Excel --- Physical dimensions of a chart object
Is there a way to know the physical dimensions of a chart object on a
worksheet? I have data in several worksheets and I would like all my
charts to be of the same size (obviously this means that data with a
smaller range of values will be more spread out than data with a larger
range of values).
This is just an example:
n = ActiveSheet.Shapes("Chart 1").Width
m = ActiveSheet.Shapes("Chart 1").Height
MsgBox (n & " BY " & m...Controlling Gap Width for BAR Chart
I have the following data for example.
A = 2
C = 4
I can create a 2 D Bar chart from this quite easily.
Now If I make the data to
A = 2 , 3
B= 3 , 4
C = 4 , 5
I do want to plot the data on the basis of first two values as
mentioned in the first example, but I want to control the width of the
Bar from the value given after comma. Please help.
On Sat, 24 Feb 2007, in microsoft.public.excel.charting,
Fazi <firstname.lastname@example.org> said:
>I have the following data for example.
>A = 2 , 3
>B= 3 , 4
>C = 4 , 5
>I want to plot the data on the basis of firs...What is the best chart for this....
When I want to compare how each salesperson's sales contributes to Total
Sales, I can use a pie chart. But what do I use when I want to compare how
each salesperson performed against the *average* of total sales?
I am envisioning a vertical bar chart with a horizontal line at the average,
but I need a solution that can be generated by a macro.
Any advice? Thanks in advance.
That's how you would do it, a column chart with a horizontal line. On this web page, I give a
couple techniques to achieve the horizontal line:
http://peltiertech.com/Excel/Charts/AddLine...How do I add y-axis labeling on the right side of chart?
I'd like to add an axis with same values on the right side of the chart.
You'll need to have at least two series in order to enter a secondary axis.
On one of the series, change the Axis to Secondary axis.
"DavidNHFCU" <DavidNHFCU@discussions.microsoft.com> wrote in message
> I'd like to add an axis with same values on the right side of the chart.
How can I force the y-axis at the bottom of my chart to appear for all 12
months when I only have Jan, Feb and March figures to put in the first thre...excel chart showing movable graph at what point we make a profit
in a business would like to make a chart based on accumulated Earnings &
Profit/Loss for year to date, perhaps movable showing our break even point
and by moving chart to a another earnings figure what profit we should make.
For example put in earnings for YTD Profit/Loss YTD then I can move the
earnings part of the chart to show what our profit would be for a given
If For example if we earn $200 for year so far and profit is $0 but any
amount over that is 18% profit so I can move chart line to say $240 and it
will say what profit I will make.
Hope ..."Print to Scale" Excel 2007 Charts
In Excel 2003, one could double-click a chart, print, and the chart would
auto-size to fill a page.
I am unable to print my newly-created Excel 2007 charts so they auto-size to
print one-to-a-page (now requires several time-consuming steps for
reformatting titles, axes, etc.).
I do not have a "print to scale" option in my page setup.
Printing to a Ricoh Aficio AP610N. Thanks so much.
I will assume we are talking of an embedded chart (one sitting on a
worksheet) and not a chart sheet.
In Excel 2003 (all earlier) you did not double click, you just single
clicked to...Bar Chart (2D) won't let me 'gap' one bar behind the other?
Operating System: Mac OS X 10.6 (Snow Leopard)
I have a nice, functioning bar chart that has 2 data fields: ACTUAL and BUDGET. <br><br>Actual is on one axis and Budget is on the 2nd axis. <br><br>These charts look nice side by side but I want one bar to be partly behind the other. <br><br>So, I select a data set. <br>
Choose Format Data Series <br>
Overlap... (and no matter what value I choose, I can't overlap)???? <br><br>But, if I can adjust the Gap Width. <br><...Stacked and single column in same chart?
How can I do a chart with a stacked column beside a single column? When I build a stacked column chart, any new source data I add
wants to put it in the same stacked column.
Use one of the links on this page. You need to set up the data so the
single column is in a stacking position with no other columns of data.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
> How can I do a chart with a stacked column beside a sing...Move data from column to rows HELP!!!
Hi thanks for taking the time to look at my problem, currently i have
column that has thousands of rows of information in it, it looks lik
numbers that go on into mabye the 5000-6000 range
what i need to do is have that data moved So it looks like this
A | B | C
40432 | 32432 | 532
432654 | 523 | 523
3432 | 52432 | 111
532543 | 532532 | 222
So on and so on,
so instead of 1 column with 6000 lines it ...not repeating text boxes in reports with columns
I am trying to create a report with columns without repeating certain text
boxes. Here is an example of what I would like to create:
[Date] "Month1" [Date] "Month2"
[Product] "Product1": [quantity] [value] [quantity] [value] [quantity]
[Product] "Product2": [quantity] [value] [quantity] [value] [quantity]
[Product] "Product3": [quantity] [value] [quantity] [value] [quantity]
[Product] "Product4": [q...Changing Exch5.5 GAL columns in Outlook client view
Sorry if the subject line is a bit cryptic; anyway, in the Outlook client, if
you display the 'Address Book' you get the following columns - name, business
phone, office, title, company, alias, e-mail type, & e-mail address. How do
you change it so you show the display name, business, extension, cell phone,
& internet email address?
Basically, how/where do you change the columnar data the client sees?
Thanks, Mike Lawson
Goto View | Columns and there u can manage views...
"Mike Lawson" wrote:
> Sorry if the subject line is a bit cryptic; anyway, i...Excel CSV leaving out empty columns from row 17 onwards
Excel omitting commas in random ways !!
Anyone come across this ?
When I save this file in csv using excel 2003
A,,C,D,,21-Nov-06,27-Nov-...Can anyone help with this chart?
I would like to have the following values/labels on the y and x axis of any
style of chart:
X-axis: July, August, September etc for a full financial year;
Y-axis: FB, AP, DR, LA, PC.
OR the other way around.
Presently the Y-axis simply displays the labels 'Jan Jan Jan Jan Jan' etc.
Thank you. Any help would be much appreciated.
Check out Jon Peltier's article on creating a vertical category axis:
*Remember to click "yes" if this post helped you!*
"A little stuck" wro...MATCH function; comparing letter grades in columns
I am currently comparing the contents on two cells in columns H & I
(for example, cells H10 and I10). The contents are letter grades
(i.e., A, A-, B+, B, etc., through E). I have my function tell me if
there is any difference between the two columns and report the result
(i.e., =93no change,=94 rating decrease,=94 or =93rating increase=94) in th=
adjoining column, J10. I use the MATCH function, and since it can=92t
distinguish between the letter grades, I have placed the letter grades
in cells M2 through M13. My function is as follows: