How do I use traffic lights in excel

I am wanting to use traffic lights in excel that change colour based on the 
result of a variance cell, ie if the result of the cell is 10 make the 
traffic light green, if it is 20 make the traffic light amber, if the result 
is 30 make the traffic light red. How do I do this?
0
Shorty (9)
12/23/2004 12:35:03 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
1242 Views

Similar Articles

[PageSpeed] 22

Shorty

Format>Conditional Formatting>Cell Value is:

Note: you can add up to 3 conditions(4 if you count default)

Gord Dibben Excel MVP

On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com>
wrote:

>I am wanting to use traffic lights in excel that change colour based on the 
>result of a variance cell, ie if the result of the cell is 10 make the 
>traffic light green, if it is 20 make the traffic light amber, if the result 
>is 30 make the traffic light red. How do I do this?

0
Gord
12/23/2004 1:16:55 AM
As answered in microsoft.public.excel.worksheet.functions:

You can use conditional formatting to change the colour of a cell. There 
are instructions in Excel's help, and here:

   http://www.contextures.com/xlCondFormat01.html

Or you could create a traffic light chart. Andy Pope has instructions on 
his web site:

   http://www.andypope.info/charts/trafficlight.htm



Shorty wrote:
> I am wanting to use traffic lights in excel that change colour based on the 
> result of a variance cell, ie if the result of the cell is 10 make the 
> traffic light green, if it is 20 make the traffic light amber, if the result 
> is 30 make the traffic light red. How do I do this?


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
12/23/2004 1:23:37 AM
Another method might be to use a wingding character that is solid round, say 
char(197) and use conditional formatting and color the character red, amber 
or green for each of your numbers.  Set conditional formatting to "formula 
is" and set the reference to your cell with the condition (<=10,f or 
instance) and then set the font to red.  Adjust as needed.
Gary

"Debra Dalgleish" wrote:

> As answered in microsoft.public.excel.worksheet.functions:
> 
> You can use conditional formatting to change the colour of a cell. There 
> are instructions in Excel's help, and here:
> 
>    http://www.contextures.com/xlCondFormat01.html
> 
> Or you could create a traffic light chart. Andy Pope has instructions on 
> his web site:
> 
>    http://www.andypope.info/charts/trafficlight.htm
> 
> 
> 
> Shorty wrote:
> > I am wanting to use traffic lights in excel that change colour based on the 
> > result of a variance cell, ie if the result of the cell is 10 make the 
> > traffic light green, if it is 20 make the traffic light amber, if the result 
> > is 30 make the traffic light red. How do I do this?
> 
> 
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
> 
> 
0
GaryRowe (86)
12/23/2004 2:31:03 AM
this can be pretty complicated. Its best if someone can 
show you this in person.  Is there anyone where you work 
who can show you how to use conditional formating?


>-----Original Message-----
>I am wanting to use traffic lights in excel that change 
colour based on the 
>result of a variance cell, ie if the result of the cell 
is 10 make the 
>traffic light green, if it is 20 make the traffic light 
amber, if the result 
>is 30 make the traffic light red. How do I do this?
>.
>
0
anonymous (74722)
12/23/2004 3:05:54 AM
Hi Jose,

"Jose" <anonymous@discussions.microsoft.com> wrote in message 
news:024f01c4e89c$50ebcf30$a301280a@phx.gbl...
> this can be pretty complicated. Its best if someone can
> show you this in person.  Is there anyone where you work
> who can show you how to use conditional formating?

That person would be hard pushed to better Debra Dalgleish's excellent 
tutorial - see Debra's earlier response for the link.

Visit also the following link to Debra's site for a wealth of other 
material:

        http://www.contextures.com/tiptech.html


---
Regards,
Norman


0
normanjones (1047)
12/23/2004 3:23:30 AM
Reply:

Similar Artilces:

Duplicate entries in excel spreadsheet
I have lists of attendees to an annual event. I've taken the lists and combined them, and sorted them by first and last name alpha. I want to keep the names and corresponding collumns for those that attended my event more than once and dump the rest. Is there a way to sort them quickly as i have over 15,000 names. Cheers! I'd insert two new columns before the list, say A and B. In the first data row in B, say B2, I'd enter a formula that combines the first and last names to create a field the uniquely identifies each record: =C2&D2 and copy it down to all records. N...

Excel file size reduction
Can anyone tell me, how can a file size be reduced. For one possible problem (and solution) see http://www.contextures.com/xlfaqApp.html#Unused -- Regards, Tushar Mehta, MS MVP -- Excel www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <fb5801c3f1c5$04b46b50$a601280a@phx.gbl>, anonymous@discussions.microsoft.com says... > Can anyone tell me, how can a file size be reduced. > > "Stacie Pierson" <anonymous@discussions.microsoft.com> wrote in message news:<fb5801c3f1c5$04b46b50$a601280...

Query using two separate query totals
I'm trying to create a query using a table of user information and two separate queries with totaled hours over a period of time. Each separate query has a total number of hours used between a start date and end date. It is possible that a user can use hours from either query. I would like to be able to have the totals information from each hours query sorted by the contact's last name. I so far have the contact's Name, the total hours from one query, and the total hours from another query. When I run the query, it shows the information from the first query and c...

Supplemental info to "HELP-excel date malfunction
Here's some additional info to the earlier post on the date cell function malfunction. Been isolated to the function to insert the date (cell format/date). It seems the application cannot interpret the system date properly. Always inserts/interprets the date as CurrentMonth-01-2011, instead of actual date. I cannot use the application this way. I am using the 'general' setting in cell format, then entering the string Month(abr)-day [eg= dec-17] instead of letting application enter the date automatically. Is there a patch available for this issue? -- Visit Family Rad...

Urgent Help Required on Excel Macro Problem
Hi there, We are facing one problem from around 2 months.Some of our users have XF_SIC.A virus found in their pc.XF_SIC.A virus generally found in excel workbook as macro virus.This virus create one excel 4.0 macro sheet with "very hidden and very protected" attributes in excel 97 workbooks which can't be removed as it is not seen.every time when infected excel 97 file opens it infect the default startup excel file(Book1 placed in XLSTART folder) and then every excel file that will be open will have infection as default file book1 infected.now our antivirus software mcafe...

EXCEL 97
Hi there! Ive created a linked spreadsheet in 2003 and am unable to open in 97. The error msg i get is "unable to open - insufficient memory". Any clues how i can get around this little dilema??? Thanks in advance! ...

Excel question ?
Hi, can anyone help me out with this .... I have excel 2003 table and sheet inside of it have some formulas. I would like to paste text data from notepad to that sheet without of erasing any of the containing formulas which are like I said already there. Thanx Hi Steve You could create a textbox from the ">View >Toolbars > Drawing menu, then paste your information in it. The textbox can be size and position any place on your sheet. HTH John "Steve" <steveaa@dzemail.com> wrote in message news:id0ci1$mh2$1@localhost.localdomain... > Hi, can anyone help me o...

The value become wrong when get value by using AppleScript while other worksheet is active.
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I just want to send a bug report. Summary: The value become wrong when get value by using AppleScript while other worksheet is active. Description: 1. Create an AppleScript file with below script. tell application "Microsoft Excel" activate set OriginalWorkbook to workbook "Workbook1" set OriginalData to string value of column 1 of row 1 of worksheet 1 of OriginalWorkbook end tell 2. Launch Excel 2008 and create a Workbook called "Workbook1". 3. Enter time data "11...

Compiling dataset using an XSD that has import and include tags
Hi,I am trying to create a DATASET object using an .xsd file that has an import tag. The imported .xsd in turn has an include tag for another .xsd. Is it possible to create a dataset based on such an xsd using the XSD.exe or are there any other tools available for this? FYI, the xsd tool works for this schema creating classes if the the path of the IMPORTED xsd is explictly given on the command line. But if I run it with the '/d' directive, it fails saying that the SchemaLocation attribute has an invalid URI. Please let me know any information on this. Thanks Rupa have you tri...

Publishing web site using ProntPage 2003
I noticed that when I publish web site to the server after I made changes, some of the files in the server are not changed. But some of them do get changed. The unchanged files are not used in the web but still stay in the server. I have no control of this. Can I force the server to match exactly the web in my PC when I publish (delete all the extra files)? Thanks. You probably got the wrong newsgroup, this is excel Regards, Peo Sjoblom "jeffrey" wrote: > I noticed that when I publish web site to the server after I made changes, > some of the files in the server ar...

Excel2000: Is it possible to use named ranges as chart's series ranges
Hi I have a table, with a row added weekly, and a chart based on those data. But chart must display data from last 6 week only, i.e. chart's data series must be defined dynamically. Of course I can do it, using some hidden dummy sheet, where data for last 6 week are automatically collected, and which serves as source table for chart. But is there a way to avoid this - p.e. by using dynamic named ranges? I tried to enter range names into Values field for Chart Series, but I got 'The formula you typed contains an error' message. Is there some trick for it, or it is hopeless? OK, ...

how to use this video converter for mac to convert mac videos.
Step 1: Free download [b][url=http://www.videoconverterformac.com]Video Converter for Mac[/url][/b] from the above link and install it on your Macintosh: Step 2: Click "Add files" button to input videos to be converted. Step 3: Under profile option, choose the ouput video file format you want to convert to. Step 4: Click "convert" button to get start convert video for Mac. As we can see, this Video Converter for Mac is easy to use, if you like it, just free download this [b][url=http://www.videoconverterformac.com]Video Converter for Mac[/url][/b] and enjoy it by ...

'Save As' hangs Excel XP 2002 SP3
I have a problem in our office. For the most part Excel works fine. We can open files and save them as long as we dont use the 'Save As' feature and try to save it to a Network Drive. Files can be saved locally then have to be copied to the Network Share. Of course my first reaction was 'Permissions Issue'. BUt each user has full access to the folder. They can right click and create a new Excel file and rename it. BUt if they open that same file then try to 'Save As' back to the same folder Excel hangs. Using the 'Save' feature works just fine. ...

Excel Frequently Used Worksheets
I am looking for a funtion in Excel similar to the MS- Word menu option "WORK". In Word, this feature keeps a separate list of frequestly used documents that could be used a default docs such as a monthly report, letter(s), project headings/sections, etc. This list is separate from the standard 1-9 last opened documents. In Excel I see this feature as a great way to keep a similar set of spreadsheets that one must access frequently, but in the recently used list (1-9), could get rolled off. I looked through all the commands in Excel and could not find it at all. As far a...

using data from another cell
I would like to type a name in one cell(1) then check in list (different column) if the same name is there, and if it is there get a value of another cell locate in the same row where. Can some one help me? Create your second list and use VLOOKUP as follows: Name is typed in: A1 List of names and values in: C1:D10 =VLOOKUP(A1,$C$1:$D$10,2,FALSE) "leo" <reani1996@hotmail.com> wrote in message news:OiO5fCTWDHA.2268@TK2MSFTNGP11.phx.gbl... > I would like to type a name in one cell(1) then check in list (different > column) if the same name is there, and if it is t...

Converting excel files to doc files
Is it possible to convert an excel file over to doc format? Alvin, You can open an Excel file with Word, then save it. Word saves as doc files (if that's what you mean). Be sure to type the doc extension, or Word will overwrite your original xls file. -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "Alvin" <anonymous@discussions.microsoft.com> wrote in message news:0c2501c4316d$5e715b20$3a01280a@phx.gbl... > Is it possible to convert an excel file over to doc format? Dear Earl, Not good... My excel file...

Avoid using Exit
In the following article the author recommends avoiding the use of EXIT commands in the body of procedures: http://www.fmsinc.com/free/NewTips/VBA/Avoid_Procedure_Exits.html If you want to follow this rule, I think you will end up with a lot of nested if-then structures. My question: is this rule common practice among the pro's? Thanks, Lars There is no hard rule for the 'pro's'. However, when giving advice to questions on this discussion group, I would agree with the advice in that article from fms. To avoid long nested procedures, you can do someth...

Office Tabs -
One chinese guy developed one application that creates TABs in Excel Word and PowerPoint: OfficeTabs. http://www.officedocumenttab.com/ Everione is using -> Microsoft should take a look. I think its not so hard to have something in the Office Pack. It would be very useful for everyone. thanks ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestio...

Excel combinations from groups
I have the following problem with excel. I have a varied amount of groups of names and each group contains a different amount of names. I need excel to list all the possible combinations from each group. The combination size also varies. EG Group 1 Group 2 Group 3 Dave Fred Janet Harry Bob Selina Jim jane Jo The group sizes vary and the amount of group vary. I need excel to make various combinations eg In a "2" combination Dave can go with Fred or Janet. Fr...

excel help 04-09-10
I have a table (range a5:s33) that is changing constantly and its being updated. I need cell I1 and I2 to give me the date (I1) and time (I2) when any of the cells within the range are modified....any help?...thanks Try the following Sheets Event-Macro: ----------------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, [A5:S33]) Is Nothing Then [I1] = Date [I2] = Time End If End Sub --------------- Micky "jcheko" wrote: > I have a table (ran...

Crosstab Query with query parameter used to create a report
My crosstab query using a query parameter works fine; however, once I try to use it to create a report I am asked multiple times to re-enter my query parameters. The report generates the correct results; however, why do I need to re-enter my query parameter multiple times?? Thanks.. Open the crosstab in design view, click on menu Query - Parameters. This opens another window. Type in your parameter names and data type. Save. -- KARL DEWEY Build a little - Test a little "JJ_Access" wrote: > My crosstab query using a query parameter works fine; however, once I try to ...

newbie to SQL trying to do a report in Excel
I have a SQL query I want to write to pull data to add weely node utilization percentages. The db is area--->headend--->router--->routerports--->transmitter--->nodes--->nodeNames also node--->utilization (utilization:nodeID, %, date) (nodes(nodeID:transmitterID, date created parentnodename) (NOdeNames:nodenamesID,NodeID) in my excel report I want to add a new column showing the node data for each week. Can I get the percentages from SQL into a text file then import into Excel showin...

Exclusion dictionary for excel 2003
Hi all, is there any way of having an exclusion dictionary for excel 2003? Thanks, Try this http://word.mvps.org/FAQs/General/ExcludeWordFromDic.htm Raul Queiroga wrote: > Hi all, > is there any way of having an exclusion dictionary for excel 2003? > > Thanks, ...

Excel 2003 - VBA
Hi Guys: I'm drawing a blank and need some help. I have a table with row 3 containing sheet names (currently pointing at 106 sheets). In rows 4-250 I want to put formulas that will look for something (located in Column B) in each of the sheets named in row 3. The first cell in the target sheet contains the last row with valid data. Example: Cell "D3" = P02-05 Cell "B5" = 123456789IBM In cell "D5" I want to generate a formula that will look in sheet P02-05, search for the contents of "B5" throughout the sheet and return the Value in colum...

excel-vb interface
hi! just want help on how i can integrate vb with excel, i want to use vb to create a macro that when clicked, a sheet of some sort pops up, the user inputs data into this screen, then the data is then transferred. and accumulated on excel... excel will be my database dump.... i would also like to learn how to use the "tabs" activeX control in vb... how can i put values into them? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Hi, A simple solution would...