I am putting together a graph and my manager wants the companies with a zero value in the bar chart to stand out (0 = really good). The only thing I could think of was making the individual x-axis label's font turn green if value was zero. Is there any way to accomplish this? (Additional: This chart will be updated every month with new data so I do not want to change by hand every month).
View 4 Replies
Is it possible to hide some of the number labels on an axis; e.g. in on the chart below to hide the X-axis label 96? Or only start numbering from 97 while the chart displays from 96. Auto Merged Post;Well I have a solution to hiding the first value on the excel X axis values labels just using a custom number format;
[Black][>96]General;
but is there anyway of formating more than a couple of the number labels individually?
I am creating a pie graph. This graph contains % of business areas which meet certain criteria. Some business areas meet 0%.
I dont want to see those that are 0%,
I know I can go to number format and use this custom format: 0%;;;
This gets rid of the 0's
However, it does not get rid of the Business area category name.
How do I remove both data label and category name?
I only want to show those values greater than 0%
I have two graphs, graph 1 and graph 2.
The y-axis of graph 1 is set to Auto.
My question: is there anyway to get graph 2's y-axis to change to whatever graph 1's y-axis is automatically?
Excel 2012 user.
I am trying to create two vertical axis bar graphs with the Primary axis for period Q1 to Q4 and the secondary axis for FY. I have attached what I have done so far and have successfully converted all but one FY bar graph to secondary axis. Once I try to select the last one (dark gray bar called "2011 FY" on the very far right), the FY bars disappears.
My main purpose is to get both Q1-Q4 & FY on the same graph and the reason I plotted FY on the secondary axis is because I didn't want the FY $ scale to massively skew the entire graph.
Sample Report 06JAN14 - Bar graph with two axis.xlsx
Two graphs with different scale x-axis.
Graph No. 1 with smaller time frame, Daily.
Graph No. 2 with larger time frame, Weekly.
Objective: Add vertical line to mark the same times in both graphs.
Trouble is both graph already utilise the secondary axis. VBA is allowed.
Right now I have this:
Screen Shot 2013-11-29 at 1.02.47 PM.jpg
How can I put another label under each bar (NOT outside end or inside end or any additional label. This is an actual label? For example, under the first blue bar labeled 56.7%, I also want to put N = 50 below the X-axis, above "Optimal." I also want to put N = 25 for the red bar immediately next to it labeled at 75.7%.
This is not a label, as it is below the X-axis.
An example of this can be found here (scroll down for second chart)
[URL]
Using the table, draw a stacked chart that has both the project name (A, B etc) and Month on the x-axis (below all the projects). the stacked chart should contain the count for cat1, cat2, cat3 and cat 4 for each project (in different but consistent numbers for each month).
I need this for a presentation today,
OctoberNovemberDecemberProject Cat. 1 Cat. 2Cat. 3Cat. 4Cat. 1 Cat. 2Cat. 3Cat. 4Cat. 1 Cat. 2Cat. 3Cat. 4A
00236111110001B
C
D
E
F
0012230182600425G
H
I
02114001602210J
K
L
I'm trying to plot a function with specific values marked on the X axis (or at least distributed according to the said values). Namely, I have something like this:
And while it does plot the Y's as I want them to be plotted, X'es are just used as labels, which causes 0 and 100 to be equally apart as 933,9 and 944. How can I plot it, then, so that I simply select my desired Y values, their corresponding X values and get the plotted function?
Hey guys, been searching around for a while now, yet to find an answer to this.
I have 3 columns of data ......
I want to create grouped labels on a chart x-axis of the type created automatically when creating a pivot chart. For example: I have projects, samples and sample yields. There are multiple samples in a project so I have a sheet that looks something like this below:-
Project,Sample,Yield
2006ANG01,CTY001,1%
2006ANG01,CTY002,1.5%
2006ANG01,CTY003,1.2%
2007ANG03,CTY456,2%
2007ANG03,CTY457,1.9%
I would like to create a chart that has Project and Sample as the x-axis where the samples are all grouped into their project. Pivot charts do this nicely, but how to do it without a pivot chart? As you will see from the attached file I can get both Project and Sample on the x-axis, but the Projects are overlapping.
I have a chart that feeds from dynamic ranges which contain whole rows of null enteries displayed as #N/A. The charts do not plot these enteries (as it shouldn't) however it does display the category label even if it too is #N/A. Is it possible to remove the category label if all data for that row is null. ie the label is also removed from the chart.
View 2 Replies View RelatedI have a data set where a number of pieces of technology are listed in rows. For each piece of technology it can relate to a "Network" or a "Terminal" and the columns are yes/no as follows:
Tech Network? Terminal?
Tech 1 Yes No
Tech 2 Yes Yes
Tech 3 No No
Tech 4 No Yes
When I plot this on a pivot chart, I get the attached chart, where the x-axis relate to the hierarchies, i.e. Yes/Yes, Yes/No, No/Yes or No/No. As you can imagine, this is not very useful for the viewer as it's not clear what the yeses and nos refer to. It would be much better if I could rename the axis labels: "both"; "network only"; "terminal only"; or "neither".
Is this possible? And is there a solution that doesn't require me to change the source data? I'm happy to use a power pivot if that would make things easier.
Has anyone ever succeeded in offsetting category labels in a chart by surpressing the first category label. I am plotting monthly financial data over several years and want to show category labels for the month ends which coincide with quarterly month ends (i.e Mar-06, Jun-06, Sep-06, Dec-06). If I select 2 tick marks between categories I get Jan-06, Apr-06, Jul-06, Oct-6) because my first month and category label is Jan-06. Is there a way to offset displaying the first category label by two tick marks and start with Mar-06 and then go every two tick marks between category labels?
View 3 Replies View RelatedI have a set of data which I need to plot both unordered and ranked.
For the unordered (as is) data, a line graph is fine, as it gives the category labels along the x axis.
For the ordered data, i have a row with the ranking in, and then plot an xy scatter graph, so the values increase.
This obviously has the rank numbers along the x axis, not the corresponding category labels.
Is there anyway to force this? I know there are chart labelling add-ins on here, but our IT dept blocks the download.
The attched file is a very small example.
I have a dynamic column chart that may contain positive or negative (or both) values. The columns contain Data Labels positioned 'Outside End'.
I want to avoid the Data Labels (for the negative values) overlapping the x axis. Is there a way to do this via VBA?
I know how to update the chart axis via vba linked to cell values, but I can't figure out a formula I can use to calculate the minimum which takes into account the distance needed to avoid the overlapping problem.
I produced several charts in Excel 2007. They display just fine when using Excel 2007, but when we try to open it in our office computers (which uses Excel 2010), the Axis and Legend Labels simply vanish.I know there's these fixes for Excel 2007, deleting a buggy MS hotfix or installing the KB2597962 fix. But the problem is, I need it to display in our Excel 2010 computers.
View 6 Replies View RelatedI recently switched to Excel 2010 and have a rather peculiar problem.Every week I update a bunch of charts in different workbooks. By update, I pretty much mean just shifting the range over one column or down one row to incorporate newly added data.
So I right click the chart, select "Select Data", update all the Data series ranges and then I click on the Horizontal (Category) Axis Labels button "Edit" to update the Axis label range.
I do all that then press OK. The data series have updated, but the axis labels haven't. So then I do the procedure again for the Axis Labels, hit OK again and voila: It worked.
But I ALWAYS have to do this procedure twice. It will NEVER update the axis labels the first time around. Even though the little preview window below the edit button show the labels correctly.
I am having a problem formatting a chart in Excel 2010. My chart has multi-level category axis labels, and I would like to have a vertical grid line separating each major group of categories. In Excel 2003, I could right-click on one of the gridlines and then specify the spacing I wanted between gridlines. In Excel 2010, as soon as I indicate that I want multi-level category axis labels, I get a vertical gridline between each category and I am unable to alter the spacing. If I deselect the multi-level axis label option, I can adjust the spacing between the vertical gridlines, but the axis multi-level label functionality is lost. Is there a way to fix this problem without having to resort to using the drawing tools or text boxes to achieve the desired results.
View 3 Replies View RelatedI am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels.
Sub ShowA2()
Application. ScreenUpdating = False
num = Sheets.Count
Sheets("X-Axis").Activate
Range(Columns(1), Columns(256)).Select
Selection.EntireColumn.Hidden = False
For a = 1 To 5
Sheets(num - a).Activate
If ActiveSheet.Name = "A2 Data" Then
Columns("A:Q").Select
Range("A10").Activate
Selection.EntireColumn.Hidden = False
Sheets("X-Axis").Activate
Columns("A:E").Select......................
I am attempting to create a staffing calendar that will use conditional formatting to alert the coordinator by highlighting the cell when certain thresholds are reached.
The spreadsheet has three tabs, with 'Master' being the final copy.
In the 'Magic' tab, I was able to get it work with a rolling count but I am having trouble recreating it when the count is stationary and not updating itself line by line aside the person's name.
What I'm preferably looking to have happen is for the 'Name' to light up when it appears more than 3 times in a week in the Day fields, more than 1 time a week in the O/C field and more than 7 times a month in the Night field.
the attached document. I wish to try and conditionally format Sheet1!D:D depending on if Sheet2!E:E displays a particular letter, when; Matching Sheet1!A:A to Sheet2!A:A Matching Sheet1!B:B if <11 to Sheet2!B:B where cell equals text "Small (7-10)" if Sheet1!B:B is >10 then where Sheet2!B:B equals text "Mid - Large (11-16)"
Matching Sheet1!C:C if cell contains "Hcap" to Sheet2!C:C where cell equals "Handicaps". If Sheet1!C:C doesnt contain "Hcap" match to Sheet2!C:C where cell equals "All Races".
To use an example, if E:E in the cell adjacent to all the matches above equals N then the cell in Sheet1!D:D turns red. Cell D52 would become red. D88, D91, D92, D93 and D96 would also become red. Here was my attempt at the formula, no idea if I was close to the answer.
I'm looking for a way to apply conditional formatting to a dynamic range, as the number of rows will vary each week.
Col A could be used as a reference, as it will always have a number in it, but wherever it stops is where I would need the conditionally formatted range to cut-off as well.
The actual range that would need formatting applied to it is Columns F-I where:
For any values < 1.00 (it should also cover blanks, which should be treated as zeros), red highlighting is applied & For any text that reads "Flagged" or "Not Reported", they would need to become bold
Additionally, I would like to start the formatting on Row 2 of the sheet in question, named "1010version"
I wish to compare 2 sets of data in 2 cells and in the third cell i wish to both display the difference and format in a colour depending on the outcome of the difference. Is this possible? or is there another solution?
e.g cell A1 is 21
cell B1 is 26
cell C1 is the difference and if the difference is greater than say 2 then it would colour red if equal to 1 then yellow and if less than 1 as in 0 -1 etc it would colour green. I have been very successfully using conditional formatting to colour a cell that has the data inputted but cant now do it for the formula cell. The context is pupil progress but that i guess is irrelevant really.
Is there a way to conditionally format a range of cells from VBA?
I want the cells to turn red yellow or green when i type into that cell r, y, or g.
I can easily select the range of cells, but dont know how to format them.
I have a problem where I need to answers from a look up table to return an empty cell if the vlookup table returns a 0 or #N/A. This has to be the case as the answers are used in the creation of a csv file.
It would be easier for me to put in conditional formatting rather rewriting 40+ formulas and copying them down 1000 rows.
Is there a method in vba to format cells to blank?
Is there a method for formating numbers in a label on a userform?
View 7 Replies View RelatedWhat I am attempting to do is have my spreadsheet automatically detect and highlight personnel working seven days in a row. There are 63 total columns, the first one being for the person's name. The next 31 columns are a full month's dates in which their day shifts are recorded, while the final 31 are the full month's dates for night shifts worked. Days worked are listed as a "1" in the cell beneath the respective shift's date, while days off go down as "3". Currently I am using conditional formatting with the following formula, which highlights cells in red:
=ISNUMBER(FIND("1111111",(H7&I7&J7&K7&L7&M7&N7&O7&P7&Q7&R7&S7&T7&U7&V7&W7&X7&Y7&Z7&AA7&AB7&AC7&AD7&AE7&AF7&AG7&AH7&AI7&AJ7&AK7&AL7)))
I created the conditional formatting formula to apply to the row for the first person listed, and then copied and pasted the formatting only for the rest of the personnel rows. The issue I'm having is that while it will highlight the seventh work days, it will often highlights all cells prior to the grouping which has seven straight days of work. It also tends to highlight single or multiple cells in red which do not fit the pattern, and whose rows may not even have seven straight days of work.
I need to do conditional formatting based on the highest percentage (value) in each row.
[1st highest value (green coloring), 2nd highest (Orange color), 3rd highest (blue coloring), if it has the letters "NT" then (Red color)]
But to break the tie, if the value of the 1st column is equal to the value of the 2nd column, 1st will be considered as the highest value. If the value of the 2nd column is equal to the value of the 3rd column will be considered 2nd largest value.
I wish to compare two sheets [Sheet1 and Sheet2] within one workbook and format the second sheet conditionally, based on an exact match to the first sheet.
Cell by cell, I can add the specific conditional formatting rule to each cell but this is tedious and its hard to see where I have applied the conditional format and where not.
So I tried to construct a macro to apply to a range of cells.
I tried to assign the logical result of the compare test to a cell outside of the range[lets say on Sheet2, A1] , and then format the range cells based on that logical value. I think my error is in conditional format criterion but I am a bit stuck.
[Code] .....
