I'm trying to find a way to alternate between two colors (white, grey, for example) in a stacked columns plot. Each stacked column should begin with a grey layer then white, then grey and so on. These plots will be created and changed frequently so I cannot manually do this every time.
View 1 RepliesI am wanting the colors of the bars in a stacked chart to change based on the date in the X Axis. As the current date arrives, the stacked bar needs to change to various blue shades. If the date is in the future, the stacked bar needs to be various grey shades.
View 2 Replies View RelatedI have two ranges of cells containing names of chemicals, call the first one "Product" and the second one "Byproduct".
Also, there are two other ranges of cells, called "Productvalue" and "Byproductvalue". These cells contain the numerical values of products and byproducts. How can I plot both the products and byproducts in the same graph using xlBarClustered in different colors?
I have to create a chart XY Scatter plot in excel. I have to differentiate the data points color based on the another column "category". Now I can change the data points color manually. Since the number of points is huge, is it possible to color the data points based on a column?
View 5 Replies View RelatedWhen I add a table in Excel, I can choose from Table Styles, different styles that set one color for even rows and another color for odd rows.
Is there a way to say to this Table Style that assign one color for rows that have the same string in column A in consecutive rows and another color when the string in colum A changes?
I mean,
If A1= XYZ, A2=XYZ assign blue to row 1 and 2.
If A3:A9=FTG assign green to rows 3 to 9.
If A10:A13=LLKF assign blue again to rows 10 to 13.
If A14:A22=WUR882 assign green again to rows 14 to 22.
and so on.
I am trying to create a 100% stacked column which has a superimposed line chart over the top of it.
Basically % of yes and no for two separate datasets, but which share the same characteristics. The x-axis is months of the year.
CSP Yes
CSPNo
All Yes
All No
Apr-13
6
19
[code]....
I can create 2 separate charts. One for the yes/no of one set as a 100% stacked column. I then use 'no fill' on the No column to stop this from being seen.
I can create a 100% line chart for the second data type, and 'no fill' the 100% line that appears.
However, I can't merge the two charts to show the Yes % for one dataset as bars and the Yes % of the other as a line.
I am using Excel 2010 on XP.
Is there any way that i can copy a row of data, but paste each individual cell that has been copied into every other column, or every 3rd column, or every x number column.
I want to do this as i have 3 column headers repeated across spreadsheet representing each month. and would like to paste into the relevant column i.e budget actual difference budget actual difference budget actual difference.
So I can paste each value under the actual column.
I have a stacked column graph showing data for 5 categories for 4 years (4 columns, 5 categories each).
I would like to display a Total above each column. I was able to do this on a single chart by just adding a text box and manually adding the total amounts. However, I would like this to be updated dynamically, as the data will be changed frequently.
Is it possible to:
(a) Have the total display as a data lable, or
(b) Have a text box containing a cell reference
I'm trying to copy and paste range in alternate columns from one worksheet to another. I can record this macro, but I believe it'll be really long because I have 21 alternate columns to copy and paste. What I'm trying to do:
-copy range B9:B41 in workbook 'Channel OU template' then paste values only in range BI9:BI41 in workbook 'final'
-copy range D9:D41 to range BK9:BK41
-F9:F41 to BM9:BM41
..and so on until the last column AP9:AP41 to CW9:CW41
Basically it's just simple copying and pasting from alternate columns. This is the really basic code that I have just for one column:
Sub copy()
Windows("Channel OU template").Activate
Sheets("sheet1").Select
Range("b9:b41").copy
Windows("final").Activate
Sheets("ou").Select
Range("bi9").PasteSpecial xlPasteValues
End Sub
Had a quick browse through the forums for an answer but as it is quite hard to describe i cant quite find the answer.
Basically I need to split some cells but they have stacked text in them i.e
Cell a1 shows:
666666
part 77777 x 20
5x s452563
Cell b1 shows:
1x 254684564
3x 4481211111 & 5 ea g8373
etc.
When i run the text to columns function i only get the first line of the data, i could ideally like to split the data by spaces and/ or line breaks.
According to my provided data, draw a stacked chart with double columns, each bar includes four types of data(Internet, PBS,Branches and Mail-in/Fax-in) and the x-axis are 1Q06, 2Q06 and 3Q06 repectively. It is urgent,pls provide the template for me. Many Many to all of you.
View 6 Replies View RelatedI am trying to get an Excel chart to include columns AND stacked columns in the same chart with no success. I have attached the Excel chart where I could only get stacked columns and I had to use a line chart for the secondary axis. I could not figure out how to get the secondary axis to be a set as a column just like the primary axis. I thought this could be done in Excel, I have searched the forums and tried to play around with the gap width and/or include a blank column but I still haven't been able to get this to work.
View 5 Replies View RelatedI have an array that is 1 row high by 16 columns wide.
Each cell may contain a positive value, or a zero.
I need a formula to find the "Minimum value that is greater than zero" in
columns 1,3,5,7,9,11,13 and 15.
=MIN(A1,C1,E1,G1,I1,K1,M1,O1) will always return the zero value while I
need the minimum value that is greater than zero.
If I use nested IF functions to exclude zeroes I run foul of the max of 7
allowed.
I have a spreadsheet that I would like to loop through column "C" and if criteria is met copy and paste A:C on sheet1 to sheet2 over multiple columns alternately. What I mean by alternately is that I would past the first row in column A6 then the second in E6 then the third in A7 and so on until all items are copied. I purposely left a blank column between both columns of information. I've tried sorting/and advance filtering and couldn't get it to work.
Example:
loop through column "C" If I have the Letter "A" copy data to column "A" and "E" alternately back an forth until I no longer meet the criteria. I start putting data on the 6th row due to header information in rows 1-5.
... If column "C" is the letter "B" copy to column I,M,Q,U
Lastly I could always have less rows of information than I do columns. SO the last column could be empty. I always sort my data by column "C" so data will be sequential.
I am trying to resolve a calculation issue where I want to sum accross columns depending on an entry in the column immediately preceeding. The layout is an Attendance sheet, The columns are for the days of the Month ( 1 - 31 ) and the rows are the Months. There are 2 columns associated with Each day. The first column is for the type of Time Off ( Vacation, Sick, Personal, etc ) the column next to it records the number of Hours some one took off. The work book has a Sheet for Each Employee and a running total needs to be maintained for the amount of "off time" each employee takes by the various time off categories. I have tried setting up range names but this won't work as there will be multiple sheets. I believe the problem is the mixture of Text and Numeric data but could not resolve.
View 3 Replies View RelatedI would like to create a chart with stacked columns. Usually, the data on the Y-axis is some kind of numerical data, such as budget. I would like to have the Y-axis have non-numerical data (= nominal categories).
Example :
Take the following data :
category | productname
red berry
red carrot
green cabbage
green apple
The ouput format i want is a chart which shows on the horizontal axis the categories red and green, and stacks in each category the right productnames on top of eachother; preferably each productname should have a different colour and showing data labels (e.g. "1", "green","apple" could be 1 specific data label).
I don't know how to achieve this, because if you display data as "count of productnames", then automatically you get something like a block with value 2, which doesn't show the different productnames inside that block.
Can the desired chart be made with Excel somehow?
I have 2 columns og data, for example
120
120
120
120
220
220
220
220
320
320
320
320
420
420
420
420
I want to plot them via a chart. But here is the problem..
My code.....................
My pallet lost color-coding - if I hover over each little scare it displays the names for the colors and if I click on them they color the cells with the right colors, but the palette itself lost the visual display of colors except for 8 colors: black, blue, red, magenta, yellow, cyan, and white.
I use color-coding of cells a lot and I find it difficult to work without visual clues. At least the hover-support allows me to get the work done, but with difficulty.
I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...
1. Vacation Type (will only have a text value of either "Cold" or "Warm")
2. Vacation Started (will always have a date *x/xx/xxxx)
3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)
4. Number of Days (currently has ALL truly blank cells)
THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE
Here's what I would like the macro to do...
Scenario 1 - for "Cold" values
Find "Cold" text values in the "Vacation Type" column
"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.
The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.
A) IF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.
OR..............................
When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?
View 1 Replies View Relatedi have attached a copy of an excel file and if you look at the end i am trying to write an IF statement that reads if J = "2-0" then column M = 12-(K) or 12--4 so answer is 16 and then column N = -column M or -16 and then if J = "2-1" then M = 6-(K) or 6--0 = 6 and then N = -M .....
so if J was "2-1" and (K) -4 then the answer given for column M would be 6--4=10 and column N = -10
I need to sum the contents of every fourth cell in row A .... ie sum(A1 + E1 + I1 ... BY1)
Can I do this via the formula bar, or do I need to use a VBA routine?
I do an analysis that contains 100 or more rows with 1 of 2 row labels in column A; "existing" or "retrofit." There may be numerous successive rows labeled "existing" in a given place where there is no retrofit. Rows labeled "existing" contain existing equipment details such as area, equip description, operating cost. Rows labeled "retrofit" contain recommended efficient replacements with details such as area operating cost and savings. I have to display total cost and savings. Right now I use the awful method, F1+F3+F4+F5... for existing and F2+F6... for retrofit. This is a terrible method, time consuming, prone to error, etc. Is there a way to total rows with specific labels when the row labels are not consistently alternating? Would like to attach an example spreadsheet but... the permissions say I may not, for some strange reason.
View 9 Replies View RelatedI have data of about 3176 rows,
But after every record one row is blank,
I have to manually delete each blank row.
How to delete it in one Go.
Please Find the attachment....
I want to reference to a range of tables depending on the value of a separate cell. Problem is that I don't know how to insert this into the range part of the lookup formula.
EG
=vlookup(a12,NAMED RANGE AS PER CELL c9,2,false)
Cell a12 is the lookup reference which is fine.
Named range is set-up and working fine.
Cell c9 is the description of the named range - season_indices_Asda_Cream
I want that to be changeable by he user so they can change the name in cell c9 from a drop down list so that the lookup formula redirects to the alternate named range...
Im wondering if their is a formula to return the results in a Hlookup but adjusted for the cell 1 cell to the right?
For instance if Row 1 Column one contains "Tom Jones" that the forumula knows to return the value in row 2 column 2 instead of row 2 column 1.
I have a row range to sum only alternate cells.
How can i do this job by means of formula.
I have a single column of data and need to convert it into 2 columns, by alternating each row:
1
2
3
4
5
want to convert to:
1
2
3
4
5
6
I've seen a previous post from 2007 where someone asked for a formula to do the reverse of this. 'Domenic' provided a formula =INDEX($A$2:$B$6,INT((ROWS(D$2:D2)-1)/2)+1,MOD(ROWS(D$2:D2)-1,2)+1) which converted
a
1
b
2
c
3
d
4
[code]....
how to reverse this formula?
I have a huge excel file and every row is repeated, e.g.:
john smith 10 23
john smith 10 23
bob jones 11 22
bob jones 11 22
etc..
So I want to delete every second row.
Is there a command to do that?
I have been using a lot of sumproducting lately with multiple conditions to extract data. Lately I have noticed that though it is a good way to extract data there is a lot of calculation time involved in it. The Excel workbooks that i make are in a database like format where there is 1 sheet usually a data dump which has data from one column to the 200th column and rows being filled with data points till the 10000th row. Data headers in the columns are usually like Date(ColumnA), Tenure(ColumnB), Person, Type, etc and then from Column Z onwards there are columns which contain Data in the form of numbers like Number of cases, Number of this and number of that.
Now usually when creating a dashboard of this data for performance management I use the sumpoduct formula to retrieve data. It normally has conditions in it like for some given date ranges, Tenure ranges, People ranges extract x data for me. For Example something like this
=SUMPRODUCT((Sheet2!$A$4:$A$4898>=VALUE($E$3))*(Sheet2!$A$4:$A$4898