Go Back   Tech Support Forum > Microsoft Support > Microsoft Office support


Excel Databar Issues

This is a discussion on Excel Databar Issues within the Microsoft Office support forums, part of the Tech Support Forum category. I would like to use the "data bar" feature of conditional formatting (Excel 2007, Windows 7 Ultimate) to show the


Reply
 
Thread Tools Search this Thread
Old 03-20-2017, 10:16 AM   #1
Registered Member
 
Join Date: Jun 2013
Posts: 6
OS: W7 HP SP1



I would like to use the "data bar" feature of conditional formatting (Excel 2007, Windows 7 Ultimate) to show the "progress" of a value in a single cell (say B3) that steadily increases as time passes - ultimately to 100%, as represented by a complete cell fill-in. (Note that this is NOT the usual databar application; i.e. for the purpose of graphically comparing the cell values within a contiguous range with each other).

I would greatly appreciate any assistance or feedback regarding my failure to structure the databar feature to do what I had hoped and expected it would do (i.e. completely fill in cell when criteria are met).

SETUP:
1. Cell B1 (formatted as a date) contains a fixed date of 1/20/2017
2. Cell B2 (formatted as a date) contains today's date (i.e. 'Today( )' )
3. Formula in cell B3 (formatted as number): '=B2-B1'
4. Select cell B3
5. Select 'Conditional Formatting'
6. Select Format Style: 'Data Bar' and choose any one of the six color options
7. Select a Rule Type: 'Format all cells based on their values' (in my case, only one cell)
8. Select Format Style: 'Data Bar'
9. Check 'Show Bar Only' (optional, to hide the cell's value)
10. Select Type 'Number' and Value '0' for Shortest Bar
11. Select Type 'Number' and Value '365' for Longest Bar
12. Click 'OK'

RESULTS:
A. For today's date (3/20/2017 or 2 of 12 months) the cell is filled in approximately 22% (a bit higher than the expected 17%, but acceptable). Note: I am defining fill percentage as the length of the fill color to the right edge of the fill's color gradient, relative to the total width of the cell.
B. However, if the date in cell B2 is manually changed to 1/20/2018 (12 of 12 months), the cell is only filled in approximately 90% (i.e. with about 10% of cell's white background showing), whereas I need (and expected) it to show a COMPLETE cell fill.

COUNTERMEASURES TRIED:
1. Changed Number value for Longest Bar to 200 (result: no change in B3 cell fill, relative to RESULTS: B)
2. Changed Number value for Longest Bar to 1 (no, this is not a typo - result: no change in B3 cell fill, relative to RESULTS: B - this is very surprising and maybe a clue to what I'm doing wrong)
3. Changed Type to 'Percent' with Value as '0' for Shortest Bar; changed Type to 'Percent' with Value as '100' for Longest Bar (result: B3 cell fill showed ~50% fill)
4. From 3 above, changed ONLY the Value for Longest Bar to '1' (result: B3 cell fill still showed ~50% fill, same result as 3)
5. Changed all settings back to those described in steps 10 and 11 in SETUP: (result: same as those described in RESULTS: A)
6. Changed formatting of cells B1 and B2 to 'Number' (result: no change in B3 cell fill, relative to RESULTS: A)
7. Changed formatting of cell B3 to 'General' (result: no change in B3 cell fill, relative to RESULTS: A)
RSquirrel is offline   Reply With Quote
Sponsored Links
Advertisement
 
Old 03-21-2017, 12:28 AM   #2
Team Manager - Networking
 
etaf's Avatar
 
Join Date: Dec 2008
Location: UK, Surrey & West Sussex
Posts: 8,265
OS: W7 & W10



i dont have 2007 available to check - so it maybe a 2007 issue
but i managed to get the fill to work

see attached - created in 2013 and worked as you described
Attached Files
File Type: zip databar.zip (7.6 KB, 3 views)
__________________
Wayne

etaf is offline   Reply With Quote
Old 03-21-2017, 06:33 AM   #3
Registered Member
 
Join Date: Jun 2013
Posts: 6
OS: W7 HP SP1



Wayne,

Thank you for taking the time to respond to my data bar issue. I opened your file and I see that you have accurately reconstructed my setup.

To test your solution (in 2007, I don't have access to 2013) I changed your value in cell B2 from 7/1/2017 to 1/1/2018, expecting/hoping to see a completely full cell. However, there is still about a 10% white space at the right end of the cell.

Maybe I failed to describe the condition I am trying to eliminate (i.e. the white space). It's not the gradient I'm concerned about (apparently Excel 2007 can only display a color gradient for the data bar, whereas later versions have the option of a solid fill), but rather the fact that my data bar (gradient notwithstanding) does not completely fill the cell when the value in the cell equals the longest bar number.

Again, thanks for your feedback, maybe someone who has had experience with 2007 will have a suggestion.
RSquirrel is offline   Reply With Quote
Old 03-21-2017, 06:50 AM   #4
Team Manager - Networking
 
etaf's Avatar
 
Join Date: Dec 2008
Location: UK, Surrey & West Sussex
Posts: 8,265
OS: W7 & W10



7/1/17 to 1/1/18 is not 365 days
its
359
days
=98%
and so does show a gap
even on solid fill , a gradient to white would probably look bigger
__________________
Wayne

etaf is offline   Reply With Quote
Old 03-21-2017, 09:47 AM   #5
Registered Member
 
Join Date: Jun 2013
Posts: 6
OS: W7 HP SP1



Wayne,

Again, thanks for responding, but unfortunately that's not the answer. I set up a test cell where the lower and upper numbers are 0 and 100, respectively (thereby getting around your point about the date span not being exactly 365). I made the cell very long (wide enough to fill the screen, so I could get some good resolution. When the number in the cell is 0, there's exactly 10% fill (as physically measured with a scale on the screen and prorated - bar length vs. cell length), however any cell entry up to 10 doesn't reduce the bar length (i.e. it remains fixed at 10%). Then, as the number in the cell is incremented from 11 to 90, the cell fills proportionally (that is, the bar's length - i.e., the length from the solid color at the left end to the end of the gradient color at the right end - increases to a length corresponding to the cell entry). But any cell entry from 91 and above, still results in only a 90% cell fill (i.e. bar length never exceeds 90%).

From this test, it's becoming apparent that Excel (2007, at least) dictates a minimum bar length of 10% and a maximum of 90%; presumably for visualization purposes, so there may be no workaround for my application.
RSquirrel is offline   Reply With Quote
Old 03-21-2017, 09:59 AM   #6
Team Manager - Networking
 
etaf's Avatar
 
Join Date: Dec 2008
Location: UK, Surrey & West Sussex
Posts: 8,265
OS: W7 & W10



looks like a 2007 issue as excel 2013 works perfectly
0 = empty cell

as you stated this article
explains
https://blogs.office.com/2009/08/07/...in-excel-2010/

2007 spec
Minimum default value
– Lowest Value in Range
– Occupies 10% of cell width

Maximum default value
– Highest Value in Range
– Occupies 90% of cell width
__________________
Wayne

etaf is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
Issues with format in Excel on figures
Hello forum, I'm working as IT-supporter and one of my users have trouble with excel and powerpoint. I'm not an expert in it and have tried to find a solution for this through various google searches, but come up with nothing. I have trouble explaining it perfectly, but I hope you can see the...
Whakapapa Microsoft Office support 0 05-02-2013 06:47 AM
"Too many different cell formats" Excel 2007 - WITH SMALL WORKBOOKS! - Virus?
I know a similar question has been answered here and all over the internet. But they all deal with large workbooks that may actually be over the cell format limit. I am working on small workbooks. Basic tables, maybe 5 different formats at most. Not large worksheets at all. So this error should not...
dolfan1986 Microsoft Office support 2 03-28-2013 03:51 PM
[SOLVED] DVD-writer copying issues or something more?
Hi! Just so you know my knowledge in IT matters is very low. Ok, I'll try to explain best my issue. My new dvd-writer, a LG GH22NS70 SATA, has some issues copying files onto the same pc on which it is connected, most frequently dvd's written with it, but as I noticed today also those media written...
Alesianduke Removable Media Drives 9 05-14-2012 12:07 AM
Merging data from 2 excel spreadsheets
Good day, i have been searching for this answer on the internet without success, here is my problem. i have to excel files which i need to merge together. excel file 1 (call it the master file) has 8 coloumns and 3000 rows of information. coloumn A has a unique part reference, then the...
dundeebat Microsoft Office support 4 01-17-2012 06:16 AM
[SOLVED] Excel 2010 to Excel 5.0/95
I am trying to save some Excel 2010 (.xlsx) files as Excel 5.0/95 format (.xls). In Excel 2010 there are two options for .xls files, one is Excel 97-2003 and the other is 5.0/95. No matter which one I choose, they cannot be read in Excel 95. Is there something I am missing?
System10 Microsoft Office support 4 01-06-2012 07:42 AM

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is on
Smilies are on
[IMG] code is on
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


Post a Question


» Site Navigation
 > FAQ
  > 10.0.0.2


All times are GMT -7. The time now is 05:04 AM.


vBulletin Security provided by vBSecurity v2.2.2 (Pro) - vBulletin Mods & Addons Copyright © 2017 DragonByte Technologies Ltd.
Copyright 2001 - 2015, Tech Support Forum

Windows 7 - Windows XP - Windows Vista - Trojan Removal - Spyware Removal - Virus Removal - Networking - Security - Top Web Hosts

 

Partially Powered By Products Found At Lampwrights.com