Conditionally Formatting Bars In Bar Graph?
Oct 21, 2011I am building a graph and i want to conditionally format it so that if a bar is less than 24 it wil be green and if it is greater than 24 it will be red. how to do this?
View 1 Replies
ADVERTISEMENT
Group Column Bars In Graph With Specific Sorting?
Jan 7, 2014I'm trying to figure out a way to group columns in a bar graph without displaying them in a cluster, but instead in displaying them individually in a specific order. However, I still want to identify each group with a different color. This is hard to describe, so I'll try my best to illustrate what I'm trying to do with an example. Assume I have three groups of data and within each group I have 2 to 3 items in it with different values. In a list the data would look like this:
Group 1 - Item 1 - $2,000
Group 2 - Item 2 - $1,100
Group 1 - Item 3 - $2,200
Group 1 - Item 4 - $3,200
Group 3 - Item 5 - $1,800
Group 2 - Item 6 - $1,200
Group 1 - Item 7 - $2,500
Group 3 - Item 8 - $2,800
What I want to do is create a bar graph that will still allow me to color all group 1 items as red, all group 2 items as blue, and all group 3 items as green while still showing each bar sorted in the order I've shown above and labeled as items 1 through 8. Then, I want a legend to only show the groups 1 - 3 with their colors. To explain, the graph would look like this:
Bar 1 would be labeled as Item 1, with a value of $2,000, and colored red
Bar 2 would be labeled as Item 2, with a value of $1,100, and colored blue
Bar 3 would be labeled as Item 3, with a value of $2,200, and colored red
Bar 4 would be labeled as Item 4, with a value of $3,200, and colored red
Bar 5 would be labeled as Item 5, with a value of $1,800, and colored green
Bar 6 would be labeled as Item 6, with a value of $1,200, and colored blue
Bar 7 would be labeled as Item 7, with a value of $2,500, and colored red
Bar 8 would be labeled as Item 8, with a value of $2,800, and colored green
Then, the legend would only show the groupings of Group 1, 2, 3, 4 and their associated colors.
(By the way... I know I can do this manually by selecting each bar and customizing the color, but I want an automated way of doing this to reduce maintenance time)
Composite Bar Graph - Total Bar To Be Made Up Of Two Separate Bars?
Nov 26, 2012I have month to month data for how many people have left an organization. I'm interested in tracking three things, how many of them left voluntarily, how many of them left involuntarily, and how many left within 90 days. The way to do it was to create a bar graph with one bar that would indicate total people who left and one bar that would indicate how many left within 90 days. However, I want the total bar to be made up of two separate bars (a composite, see this link for an example of what I mean Graphing- Bar Graphs ) one for involuntary, and one for voluntary.
View 3 Replies View RelatedDisplay Chart Graph Bars In Order Different Than Data Is Sorted?
Feb 21, 2014I am trying to compare the frequency of occurrence of multiple unique values within a data set and graph the results as a pareto. I have the formulas working to identify and count the occurrences, and the chart is designed and displaying the data, however I cannot get the graph to display in descending order.
I am unable to sort the data because it is generated from a series of formulas, the returned values of which cannot be sorted. (its hard to describe, see the attached workbook)
I would like to know how I can display the graph so that it shows the bars (primary axis) in descending order (highest value to left, lowest to right) since I cannot sort the data. i.e. For the attached workbook, I want the bars to display in descending number of occurrences: 81,15,5,5,3,3,2,2.
Data Bars Conditional Formatting
Mar 5, 2012I am using the simple conditional format "Data Bar" to make a progress bar, going from 0-1 (0-100%), works fine .5 is halfway...ect. but what i would like it to do is to change to a different colour only when it reaches the 100% or 1 in this case so you can see quickly that this progress is complete.
View 7 Replies View RelatedApply Conditional Formatting To Bars In Bar Charts
Aug 29, 2006I have created an excel file that contains a lot of worksheets which contain a lot of raw data and charts. There are 5 different tabs for 5 different companies. Each tab currently contains only 1 year of raw data for that company. Then I have another excel worksheet that has nothing but charts on it. Each chart is a bar chart that shows each of the 5 companies. It also shows the current rate for the month for each company and the 12 month average for each company. On the chart there are also 3 lines. There is a standard line that we try to meet each month. There is also a line for the upper control limit and a line for the lower control limit. Here is my problem:
The 2 bar charts (current rate for the month & the 12 month average) are created and updated by the raw data in each of the 5 data sheets.
Based upon the values in each of the cells that create and update the bars on the charts themselves:
I want the bars to be RED if the values are below the lower control limit.
And I want the bars to be GOLD if the values are above the upper control limit.
And I want the bars to be green if the values are in between the upper and lower control limits.
Conditional Formatting - Data Bars Based On Value From Another Cell Using INDIRECT
Apr 11, 2014I'm running into a problem when trying to use INDIRECT in combination with Conditional Formatting Data Bars.
Basically I want to show a Data Bar in a certain cell based on the value from another cell.
It would be easy if I could simply copy the value from the other cell across into the cell in which I want to show the data Bar. Unfortunately I cannot do this as the values in the cells where I want to show the Data Bars can change.
As a solution when using Conditional Formatting Data Bars I figured out I can simply do the following:
> Under 'Minimum' use 'Type': Number with 'Value': Zero.
> Under 'Maximum' use 'Type': Formula with 'Value': =INDIRECT("B'&ROW())
This will pick up the value up from column B in the same row.
> In column B i then have this formula: =A1/C1. In C1 i have the percentage that the Data Bar should be using. By dividing the value in A1 with this percentage B1 will return a value that is used for the "Maximum" setting which will then ensure the Data Bar will display correctly.
For example: If A1 value is 4 and C1 value is 25%. Then the formula in B1 will return value 16. The scale used for the Data Bar is then 0 to 16. Seeing the value in A1 is 4 the Data Bar will correctly show 25%. If I then change the value in A1 to say 2 the scale used will change to 0 to 8 and the Data Bar will still be showing 25%, which is exactly what I'm after. So effectively this means a value from another cell is used to work out the Data Bar.
So all is fine up until this point. The problem arises when I want to apply this same logic to the rest of the column.
If I change the 'Applies To' to the range I want it applied to all the "Maximum" will only be based on the top row value. I'd expect cause I'm using the INDIRECT reference with ROW() it would have picked up the values from each individual row. I could create a new CF rule for every row, but that's not practical as there are way too many rows.
Why does the INDIRECT function with ROW() not work in this case? How to make the CF rule copy down properly?
Conditionally Formatting Dates
Dec 12, 2006I have a workbook (attached) which has two sheets called “Team List” & “Masterfile Data Dump”.
TEAM LIST
This is the sheet that I want to populate when the data dump is a pasted into the sheet “Masterfile Data Dump”
MASTERFILE DATA DUMP
This sheet is where my data dump will be pasted to, columns A to Q and column R is a formula to work out the FTE
Calculation
The reason I want the list to auto populate is that it will change week on week and is a time consuming task keep updating it. I want to be able to type a Team Leaders Name into cell F2 and it populate the list of the agents in that persons team Cells F4:G18 (Name & Surname) along with there FTE (full time equivalent) in Cells I4:I18.
The problem I have is I have no idea how to calculate the FTE sum as the data dump can have one to five shift patterns (rows) for one agent (See my example on the “Master file Data Dump” Sheet of Joe Bloggs. /I have tried to use a VLOOKUP which I have used but this will only return the first value or first rows value of that agents name.
Conditionally Formatting Cells When Selected?
Apr 18, 2012Column A = list of names
O1:P20 MAY contain names
If A1 = Fred
AND O5, O16, P3, P19 = Fred
Selecting A1 changes all instances of "Fred" to RED-BOLD
If A2 = Felix
AND O9 = Felix
Selecting A2 changes "Felix" to RED-BOLD
Etc...
Docking Command Bars? (tool Bars)
Jul 6, 2009In the code below using msoBarTop places the menubar at the top but it seems to just place it in a new row at the right of all menus.
How or can I dock the menubar next to the last menubar?
Conditionally Formatting Highest Valued Cell
Dec 22, 2005I have 7 rows in the same column of type percentage. I'd like to turn
the text of the highest valued cell red. How exactly is that done with
contional formatting (vs. IF statements for each cell)?
Conditionally Formatting Range Of Cells Based On Formula?
Jan 18, 2013I had a list of people on sheet1 of my workbook, and on sheet 2 a table with a list of names and their department.
I started off by wanting to use VLOOKUP within the conditional formatting feature of Excel to find anyone who worked in "finance" in sheet 2. I could get this formula to work on the sheet but as soon as I put it into the conditional format feature it wouldn't work.
Trying to find a work around I added a column in sheet 1 where I inputted the VLOOKUP formula and caused it to display "1" if the person worked in finance. It looked like this:
A: Person's name
B: Value of 1 or 0
The idea was to hide column B and use conditional formatting to say "if column B2 = 1 colour column A2 blue". Now I could get this to work on a single row but couldn't make it work over a range, ie. "if cells A2:A150 have a 1 in the column next to them colour the relevent cell blue"
I feel I'm missing the obvious. I found quite a few answers online but non actually specified how to get the conditional formatting to recognise the relevent cell it should be looking at in a range.
1) How would I get conditional formatting working over a range
2) Whether it is possible to use VLOOKUP within the conditional formatting function of Excel.
Horizontal Bars Scroll Bars!
Mar 6, 2009I am trying to derive the EMI for a Loan Amount and also chart a PIE graph for the same..
Instead of Manually feeding the values such as :
Variable 1 : Loan Amount : Min : Rs 50,000/- Max :Rs 5000000/-
Each Spin (1 Unit) -Rs 25000/-
Variable 2 : Interest Rate : Min : 5% Max : 30%
Each Spin (1 Unit) -0.25%
Variable 3 : Tenure/Duration : Min : 6 months Max : 30 years
Each Spin (1 Unit) - 6 months
I want to have a Scroll-Bar where the values can change Automatically but am not aware of using the same...
Can someone please help me with a small attachment with the above variables attached to it..The values drawn should be displayed as well above the Scroll-Bar and maybe can make the Min and Max more dynamic by storing it somewhere in the Sheet..
I am looking for Non-VBA solution to do the same..
Control Scroll Bars: Remove The Scroll Bars From Just One Worksheet But When Unticked
Jan 13, 2007In the menu via TOOLS-->OPTIONS-->VIEW I am trying to remove the scroll bars from just one worksheet but when unticked, this option affects all of the worksheets in my workbook. Is there a way to just nail this selection down to one sheet via properties?
View 2 Replies View RelatedFormatting Graph Won't Work?
Jan 24, 2012I want to change to format on the axes from % to numbers using the code below but nothing happens?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Range = "Listbox4" And Range("U4") = 2 Or Range("U4") = 5 Then
ActiveSheet.ChartObjects("Chart 5").Activate
[Code]....
References Within Graph Formatting?
Mar 13, 2012Is it possible to title a graph according to a changing column name perhaps?
View 5 Replies View RelatedFormatting Line Graph
Feb 15, 2007how to have different format on a line grapgh. I have a series of data points for sales figures. The first 10 are actual data and the next 5 are projections. I want to have a line graph with solid color format for the actuals and a dashed format for the projections.
View 3 Replies View RelatedConditional Formatting For Series On Graph
Oct 23, 2007I have a table which provides the source data for a Bar Chart style graph. I need the colour of the different Bars to change depending on the values in the table. For example. If the value is over 1% then the bar will change to the colour Red otherwise it will stay Green.
View 3 Replies View RelatedCopy Or Link Formatting From Conditionally Formatted Cell To Another Cell?
Aug 12, 2014I have a column who's content is determined via about 6 nested if statements from data on that row. That cell is then conditionally formatted to a certain color based on the text that is ultimately printed from the nested ifs (simply an extra visual legend for the text). All of this contributing info and about 1000 lines items make a very large and difficult to print page. What I am trying to do is a make a summary sheet that simply takes the index number of these 1000 rows and copies or links the conditional formatting of the mentioned cell onto this number on another sheet. I have already linked this status cell and put it adjacent to the index number which works well in that with two columns I can show the index and status but if I could combine the color of the status cell onto the index cell, it would be even better. I am pretty sure another conditional format for this summary sheet would not be possible or be extremely complex since the contents of the index cell I am conditionally formatting have no bearing on the conditions for the format. Was hoping there is some VBA magic that could simply mimic the conditional formatting from one cell and put it on another.
View 1 Replies View RelatedMake A Scatter Graph That Will Graph The Attached?
Mar 21, 2014I would like to make a scatter graph that will graph the attached. The score would be on the Y axis and the birth date would be on the X axis. This is simple to do by itself but what I would like to do in addition to this is to have the top 25% of the scores a single color, the middle 50% of the scores a second color and then the bottom 25% of the scores to be a third color. And if it is possible to have the ID visible when you move your cursor over a given dot in the graph. Currently when I make a scatter graph the X,Y coordinates show when I hoover the cursor over a dot.
View 7 Replies View RelatedMake A Graph That Will Graph Against Time
Jun 11, 2006How can i make a graph that will graph against time? lets say i have a bank balance like this:
1/2/2000 $500
1/3/2000 $600
3/12/2000 $400
there may be more than one entry on any one given day, or there may not be an entry for 2 weeks. How can i graph the running balance in a way that it will show the timeline just as a calendar year(or however long i selected) and the points are plotting according to their date, not just equally spaced out.
How To Colour Different Bars In Bar Chart
Jan 20, 2014I have a bar chart which compares two columns of data. the data in one column is fixed to 100. the data in the other column varies. a third column states whether each entry is "A" or "M". Is there a way to include the A or M on the bar chart? Perhaps by having a set colour whether the data is A or M?
View 1 Replies View RelatedScroll Bars In A Form
Jul 3, 2009What I need to do is add a scroll bar to my text box in my form. I used the properties of the text box to add a scroll bar but when you preview it you can't see it. When you click somewhere inside the form it appears and is workable but not when it hasn't been clicked on. How do I make the scroll bar appear from the start. this is a vertical one ad is on a text box within the form
View 11 Replies View RelatedVBA - Adding To Command Bars
Mar 13, 2012FYI This is cross posted on the VB Forums at [URL].....
I know that you can use the below code to add buttons to 2 of Excel's menu systems,
Code:
Application.CommandBars("Worksheet Menu Bar").Controls.Add
Application.CommandBars("Cell").Controls.Add
And this should add a command bar control of whatever type specified to either the Add-Ins tab of the ribbon or the right-click menu when clicking on a range of cells.
However, I would like to be able to add some command bar controls to the right-click menu when you click on a group of columns. Is this possible?
Chart - Compare To Bars
Sep 24, 2012The graph function in Excel is driving me insane! Is it possible to do as image below shows? If so, how?
As you can see, I dont want to stack the result, but rather compare two bars with the bigger one in the background.
Remove Title Bars
Sep 27, 2007code to remove the titlebar of a userform in Excel/VBA.
Remove meaning the program user must not beable 2 see the titlebars..
Multiple Menu Bars
Mar 2, 2008I have some code that creates multiple new menu bars for a user which each have there own controls. I need to know which control is selected by the user for onaction. The example below is where there are up to 20 new menu bars with names Tract 1, Tract 2, etc. For each Tract, there is a controlbutton titled Absoprtion and I need to know which "Absoprtion" button was selected to run the "ViewAbsorption". I need to use th For next function because the number of menu bars could vary.
Dim objMenu As Object
Dim objViewMenu As Object
For y = x To 39
Name = "Tract" & y - 19
With Application.CommandBars("Worksheet Menu Bar").Controls
Set objMenu = .Add(Type:=msoControlPopup, temporary:=True)
objMenu.Caption = "&" & Name
End With
With Application.CommandBars("Worksheet Menu Bar").Controls(Name).Controls
Set objMenu = .Add(Type:=msoControlButton, temporary:=True)
objMenu.Caption = "&Absorption Schedule"
objMenu.OnAction = "ViewAbsorption"
Next Y
Hide Command Bars
May 4, 2007I have opened an Excel file (map1.xls) and changed some little things in that file.
after that I open a new Excel file with some VBA code in it.
In ThisWorkbook I have this ...
Combine Quarterly Bars Get Messed Up?
Feb 5, 2014Each quarter should have the bars standing next to each other, i.e. Q1 2013 should stand right beside Q1 2012. Between these pairs of quarter there should be a small space and the the next pair, Q2 2013 and Q2 2012, should be. So how do I do this?
When I use data without stacking several info in the same bar, this isnt a problem but when I have this "aaa, bbb,ccc,ddd" in every bar, this gets messed up.
Bargraph With Grouped And Stacked Bars?
Jan 27, 2010This is the data:
jan09 feb09 mar09 .....
Division 1
Division 2
Division 3
Division 4
Division 5
jan08 feb08 mar08 .....
Division 1
Division 2
Division 3
Division 4
Division 5
The graph would be a bar graph with 2 bars next to each other per month. each bar would be stacked by division. The goal is to compare for example sales in 08 with sales in 09 showing 2 bars next to each other split by division.
