Im working on an excel file which will ultimately be used to create a bar chart. I was wondering if there is a way to highlight (change in bar color would be great) specific values (anything above 40) on the chart.
View 2 RepliesI have been measuring variation against some key performance indicators and trying to plot them on a chart i.e., line chart.
Is there any way in Excel that i can highlight in different colours the area between the variable and benchmarks? I want to fill the area say as green when the variable performed better than benchmark (area between the variable line and benchmark line) and as red if variable under performed than benchmark (area between benchmark and variable.
I've got a chart showing values for a set of dates. Can I highlight dynamically the data point on the chart with a specified date? I've fiddled around with formatting but suspect I may have to resort to some code..
View 4 Replies View RelatedHow can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
View 1 Replies View Related I have created a pie chart showing top 10 best selling categories. However, on this pie chart, I want %age of overall sales to be shown.
At the moment say I put in the following
Catalogue 1 100
Catalogue 2 98
Catalogue 3 92
Catalogue 4 85
Catalogue 5 84
Catalogue 6 75
[Code]....
I add Data Labels and select the percentages. This shows at Cat 1 having 12.5% of the sales (100 of 800 sales), Cat 2 having 12.25%, and so on..... However, what you don't see is the following data:
Catalogue 11 60
Catalogue 12 58
Catalogue 13 57
Catalogue 14 57
Catalogue 15 56
So based on 1,088 sales, Cat 1 at 100 is actually only 9.19% of TOTAL sales.
I understand what is happening, the chart is giving the percentage based on the data inputted into the chart.
I did consider adding an 11th category to the chart, which was the total of Catalogues 11-15. Whilst this does give a more accurate percentage figure (as now all sales have been inputted), it makes the chart look ****
Is there a way of either adding the 11th category, and then "hiding" it, so that the chart doesn't display it, but uses it's value to calculate Catalogues 1-10s percentage?
BTW: The reason I ask is that I could be dealing with anywhere from 30 to over 100 catalogues. So it's not ideal showing the others in 1 chart.
I have 100,000 lines of data and an trying to highlight values in column B based on those in column A.
For example if 1 appears in A then several times in B then it should highlight each instance in B where it appears in B. However is 2 appears twice in B - but not in A - then it should not highlight any cel.
Column A is the criteria list I think there are 140 unique values.
Column B is the data I need highlighted there are around 100,000 rows. In there values from column A appear multiple times. I need all of the multiples in B - where they match a value from column A - highlighted.
In Excel 2003, I need to be able to format one column (ie Lead Source) so that if the value in that column is not x,y, or z, it is highlighted or is otherwise visually marked as being out of compliance.
For example, if I have a column that is titled Month, I need to format it such that if the value entered does not match one of the 12 months of the year, it is highlighted.
It is also important to be able to apply this formatting to already existing data, as opposed to as it is being entered (as in list validation). And that is can be easily applied to large sets of data.
I have used conditional formatting in the past to highlight repeated cells, and had tried something similar with this, but am not familiar enough with excel syntax and can't seem to make it fly.
I have rows that contain property identifiers and their owners. One property ID can have multiple owners. I would like to color the rows differently to show each unique property ID with their 1 or more property owners. I have attached a file showing what I would like (with fake data). In excel, I was able to figure out how to highlight the duplicate values, but it only does them in one color. I would like each property ID value to have it's own color - as I show in the attached file. In the file, I have value 1234 as one color, the 4546 values as another color and 2233 values as another.
View 3 Replies View Relatedfind the attached Sheet, where some values are entered in column A with repeated action. What I need that through an excel function the repeated values should get red colour like in the Column C.
View 1 Replies View RelatedI am wanting a Macro or a VBA to look at all of my rows and completely highlight the row after 2 text values are found.
Example: Range A3: P:28, Find in Row A3:P3 that has "Text1" and "Text2"
I'm using the code described down below to highlight 5 of the lowest values in a column. The problem; If at a certain moment the column contains less then 5 values the code errors, so I've to put in a lot of error handlers to fix the problem.
View 4 Replies View RelatedI want to identify the 2 minimum valued greater than 0.00% in rows with percentages only. Then identify the 2 maximum values greater than 0.00% in rows with percentages only. The sheet changes in size (rows and columns) each day. C4 Row 4 is a constant starting place if that is necessary. I have been tweaking this code all night, but no luck. I keep getting a highlighted yellow error in editor @ oRg.Find(What:=iMin, _
I also don't know what an oRg is, I am using 2001, that may be the problem.
I have a work document with multiple rows of information. In each row I need to locate the maximum and minimum values. (The rows aren't long - there are about 10 values in each). I then would like to turn the font of the lowest value in each row red - and the font of the highest value in each row blue. I would also like to be able to skip blank or unimportant rows.
View 2 Replies View RelatedI created a spreadsheet for use by the shipping department where I work. We are trying to prevent pulling and shipping the incorrect item to the customer. This is how it works. The employee downloads a CSV file containing items that have been purchased from our website and imports the file into the excel. Then the item is pulled and the employee scans the item's barcode into the spreadsheet named "SCAN." Formulas and code on the "reference" sheet look for the SKU number in the list from the CSV file while others create a consolidated list of SKUs that have been scanned, SKUs that have a problem and need to go to customer service, and SKUs from the CSV file that have yet to be scanned. Some of the formulas in this workbook have been borrowed from forums like this and altered to fit my needs - I'll admit I'm not even entirely sure how they work.
Occasionally a SKU is scanned in twice and not always sequentially which will effect the total item count. It can be very difficult to find the duplicates in the list.
I would like for any value that appears more than once in column B of the 'SCAN' sheet to be highlighted. I have tried to do this, but to no avail.
Scan Below
FWD: CS?
If FWD: CS, state reason below:
Status
NOT SCANNED
FWD to CS
SHIPPED
3
4
149
0113MSU1018
[Code] ......
I have "Worksheet1" with Columns A and B for IP Addresses, then Column C is for a device name.
I have "Worksheet2" with a range of IP Address from F5:I260
When I enter a new device and assign it the IP addresses on "Worksheet1", I would like the IP Address to highlight in "Worksheet2"
This way I can keep track of which IP address I have used. Excel 2010
The solution can be either in VBA or conditional formatting, if possible.I have product names on column A and weeks as from column B where I have the quantity sold. So, every week I'll have an additional column.
A B C D E ...
Product Week1 Week2 Week3 Week4...
What I need:
If the cell is filled, highlight it in green.
If the gap (empty cells) between weeks is =1, highlight it in yellow
If the gap (empty cells) between weeks is >1 but <2, highlight it in orange
If the gap (empty cells) between weeks is >2, highlight it in red
The attached example better illustrates the needs : Example.xlsx‎
I need to creating a macro to highlight entire rows if a cell in that row (C) contains specific values.
View 4 Replies View RelatedIs there a way i can have a macro find the unique values in these 3 columns and highlight them yellow.
They should find uniques using all 3 columns, not find them within each column.
I included a file which shows what i start off with and what it needs to look like. Also if it matters the contents in the cells are from links. ( i know that matters because you have to put look in values in the code ).
I’m trying to assign building and office keys to various personnel, by appointing different classification levels to each set of keys given out. Very similar in the way an administrator would assign permission levels to individuals.
Question:
•Is there a way to pick up an array of non-sequential numbers from one sheet (values 1 -36)
•Find those values which are assigned to columns laid out as a table,
•Then highlight those found values
I’ve attached a dummy sheet to illustrate my question.
I have not attempted coding this because I wouldn’t know where to start.
Theoretically is this even possible?
I just want to colour duplicate values but want to do it with this Dictionary method
Code:
Sub highlight_Dups()
Dim cell As Range
Dim rng As Range
Dim dict As Dictionary
[Code]....
I have two cells with values that are strings.
I would like to know of a macro that can compare each two cells(start from E2 and compare with E3)to see if the values are NOT equal.
If they are equal, do nothing and if they are NOT, highlight the value.
Example:
compare E2&E3, E4,E5,E6,E7,.... if for example E2&E3, E4,E5,E6,E7,.... each hat same string, do nothing, but if they were not the same highlight it with red.
This code checks two cells and highlights one cell when there is duplicate, I like the opposite one, but don't know what to change!
Code:
Sub CompareCells() Dim r As Range, cell As Range Range
("E2", Range("E" & Rows.Count).End(xlUp)).Name = ("MyRange") Range("MyRange").Select
' clear all colors from selection Selection.Interior.ColorIndex = xlNone
' loop through cells and compare Set r = Selection Set r = Selection.Resize(r.Rows.Count + 1)
For Each cell In r If cell.Offset(1, 0).Value cell.Value Then
cell.Offset(1, 0).Interior.ColorIndex = 3 End If Next End Sub
I am attempting to run a macro that will make conditional formatting I have applied permanent, and then remove the conditional formatting from the cell. My application for this is a master fantasy football cheatsheet, with multiple columns of the same player names ranked in different orders. When a player is drafted I type their name in cell A1, and conditional formatting is setup on so that name (presumably once in each column) will be highlighted/bolded/etc. For example, if I typed "LaDainian Tomlinson" in cell A1, I would be able to find his name in each list/column (from the conditional formatting that will make it stand out), and then run the macro to make this formatting permanent (show he has been drafted), and remove the conditional formatting code, leaving the effects. I found a macro online to do this at the following link, but am having some issues:
When I run the macro on any cells not containing conditional formatting, I get the message: "The formatting based on the conditions in the range (whatever is highlighted) has been made standard for those cells and the conditional formatting has been removed."
This messsage is precisely what I am trying to do, however, when I run the macro on a cell with conditional formatting (when it would actually be beneficial), I get "Run-time error '13' Type Mismatch"
When I go to debug, the error appears to be on the line
If CDbl(rng.Value) = CDbl(FC.Formula1) Then
I have a workbook that has five worksheets listing warehouse inventory items. One worksheet for 2013, 2012, 2011, 2010 and 2009. In column b of each worksheet is a column B with a heading of "Item Number". There are hundreds of item numbers on each worksheet.
I would like to be able to find and highlight item numbers that appear in multiple years. Preferably color coded showing appears in five, four and three years. If that's too difficult than just items that appear in all five years. I tried conditional formatting, but have been unsuccessful.
Lastly, is there a way to list any duplicate item numbers on a new worksheet.
SM extract 8.21.2013 Cust-sample.xlsx
I want to highlight the cells under System Name (col 1) in sheet 1 that are found in Host (col 1) in sheet 2 "OCPtabvHost"
The text isnt in the same format in the 2nd sheet (it is lower case and has additional text). I need to find the duplicate roots
I've been looking for a solution to highlight all duplicates within a certain column across all worksheets in the workbook as the entry is made, no button to search for them. I have found bits and pieces, but can't seem to stitch them together to create what I am looking for, still very green with vba.
My workbook is a loading schedule at a warehouse, so there is a tab for each day (the date being the tab name, ex. '12.04.13'.) Tabs are continually added and removed to progress the calendar, and minimize file size. The column I am searching for duplicates in, is column L (or 12, however you wish to identify it.) If a duplicate value is entered, I want at the very least to highlight the value just entered, and the value elsewhere on the workbook, and if possible, have a msg box pop up stating the location of the other duplicate (or at least the tab (date) the duplicate is on.) The message box is more so for an in your face error check, with a built in GPS. Would it also be possible to only search from row 2 to 100, and ignore any further rows on each sheet?
The point of this, is to locate duplicates to make sure an appt has not been double booked, and both entries can be located to verify which entry has the correct information (carrier, delivery appt, etc.)
I'm finding it hard to explain what I am trying to do (which may be why I can't find a solution through Google), so I have an example. I am using a much larger spreadsheet than this sample(18,000 rows)
ID
First_Name
Last_Name
Company
[Code]....
I want to highlight rows if there are multiple instances of ID and # values - I included a column at the end showing which rows in this data set would be highlighted.
I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:
Column F:
6
6
14
Column L:
3
NA
17
I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.
Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.
I have a stacked-bar- plot: Sequences (Y) as a function of time (X).
The stack colors display different discrete events (E), see 'Quantiles.xls' in the attachment.
Now I've changed the the X-Aches from time to percentage.
I need to analyse these percent-transfomation of the data.
How can I write out the Events as the bar-plot as a function of percentage (like the blue-colored matrix in Quantiles.xls).
i create a chart in excel, is there any way to show only top 5 values "labels" in chart? just only top 5 values,,
maybe this pict can explain what i mean.. Top5.jpg
i need that chart can change automatically when i change the tables data
how do you create a bar chart with values and %? Spreadsheet attached.
View 3 Replies View Related