Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Up down bars line chart Results

For a line chart that displays 2 lines across Jan-Dec, I enabled up/down bars
(Excel 2003). I would like to know if it is possible to show the value of the
up/down bar on the graph (in other words, show the value of the variance
between the 2 lines)? Thx

Hi, I'm making a Line chart in excel with two columns of values. I want to visually compare the two lines and it works great wit up/down bars except for the fact that I want the whole area between the lines filled, not just the bars. The final result I'm trying to achieve should look roughly like this chart (I made this in Paint using the fill bucket ):
http://karen.photodejavu.com/chartexample.jpg

Thanks in advance!

Hiya,

I have a line chart with two lines. I have included down bars from the upper
line. I would like the difference of value between the two lines (i.e. the
height of the down bars) to be shown within the down bars.

Is this possible?

Thanks,

Basil

Ok I'm pretty hopeless with Graphs but nobody else here can create them on Excel!

Using my trusty GCSE Excel knowledge I've managed to create a bar chart to show leases of some properties with e the Building name as a legend and the lease end year as the length of the bar (all bars start in 2008)

All colours legends places etc are ok, problem comes when someone asks for a break date to be added

Basically a tenant can end a lease early on certain dates if they pay a penalty rent (usually 1 year's worth).

I need to find a way of shoing these dates on the bars by breaking up the bar half a year before the date and restartiing half a year after..

I am in no way skilled enough to do that and pens are not allowed!

Eg. X Ltd rents Property A till 2050 but may call off the lease in 2025

Years .......08 09 10.... 25....... 50

Property A |__________| |_____| 2050

======================================//==========================================

Even a line down the middle of the bar at the appropriate year would be good but atm I'm telling them it can't be done!

Any help appreciated

Dan Armstrong
Garner Associates

Hey. Got a problem on making a boxplot in excel 2007.
I used to make excel 2003 to make the boxplot, but now I have the 2007
edition.
I learned from my teacher that I need to use the "High-Low-lines" and the
"Up/down-bars" to make the boxplot.
The problem is that when I made the "Up/down-bars" it will cover the
data-points so I can't see median ect.
Is there anyway to change the order of the chart-figures like to show the
data-point at the top, the up/down-bars in the middle and the high-low-lines
at the bottom just like when u change the order of several objectives?

Sorry my english! Want to show u guys what I mean but don't know a place
where I can place pics of the screenshot

APPARENTLY NOT EVEN MICROSOFT CAN HELP!! WHEN I DRAW A LINE ON MY
SPREADSHEET USING THE DRAWING TOOL BAR, THE LINES DO NOT STAY ON THE
SPREADSHEET AND IF I MOVE THE PAGE UP OR DOWN, THE LINES DISAPPEAR! THIS
JUST STARTED HAPPENING AND I HAVE NO VIRUS. CAN SOMEONE HELP? IT'S PROBABLY
A SIMPLE SETTING SOMEWHERE IN THE PROGRAM.

THANK YOU.

Hi everyone,

This seems like it should be a simple fix, but all the previous suggestions I found do no work.

I have a combined two axis bar/line graph, with four bars representing dollar amounts for a quarter of the year, and the line (against the other axis) represents number of accounts. The quarter is plotted on the X axis. The bars are only taking up 1/3 of the area, but I want them to fill nearly the entire area - I'd leave a small space to seperate. I cannot increase the width of the bar though. I have made both the major and minor units on the x axis 3 months and I have even tried reducing all the Gap settings down to zero with only a slight change. Can anyone offer any advice?

I have attached a copy of the chart.

Thanks,

Jeff

I've created a set of combination bar and line charts as in Jon
Peltier's example using x-y and a dummy series. I've got pretty good
at this until today. I noticed that if I increase the size of the font
on the x-axis (left hand side, up and down since this is a bar chart)
numbers will suddenly appear on the axis in addition to the categories.
The numbers are exactly .5 off of the dummy values. If I reduce the
size to 10pt, the numbers go away.

Where are these coming from and how can I prevent them from showing up?
Thanks

- John

I have a simple line chart with "up/down bars" to show the min, max and average values of a dimension over 4 processes. My question I want the average values at the top, much like the "Category (X) axis labels" are on the bottom. I gather somehow I can align these or create another "Category (X) axis labels"? Just cant get them up there.

See attachment. Just need to get the values of the data labels on top!

Hello. I am currently working on a chart in excel for a psychology project in school. I had to get six people to fill out a paper with several different personality choices that they felt fit me best. They each picked 20 of the list, and I have to talley them up. I want to show them in a bar chart so that they are easily viewable in my report, and have been working at it for two days to get to the point I am at.

So, what I have is 2 columns to the left of my chart. In the "A" column, I have the word that is being counted, and in the "B" column adjacent to that word, I have that cell set up to count the number of instances the word appears in a results chart I made on another worksheet tab. I used the COUNT IF function in order to get my results, which worked well. EX: Cell A1 I have the text word "considerate". The formula in cell B1 is as follows: =COUNTIF(Sheet1!$A$6:$G$25,"suspicious") which gives me my results, going down a cell for each word, a total of 58 cells in each column. I then applied them to a bar graph as shown below. The chart shown below in this picture is the chart I need help with.

http://i1131.photobucket.com/albums/.../excelhelp.png

My information came from this table below:

http://i1131.photobucket.com/albums/...xcelhelp_2.png

Of course, because there is so much data, I need the chart to fit the entire page. I wanted a beautiful, interesting 3D bar chart to display, but unfortunately I do not know whether or not if it's going to actually be effective that way. 2D might be better and I may have to simplify and I am open to suggestions. I would like to keep this sort of color theme because it is matching with colors I have set up for my report in Word.

I was wondering if there was also a way that I could possibly separate this data...like imposing a line chart displaying the smaller amount of hits, like 3 and under, then the bar chart could display the larger amount of hits. I saw there's a way to do so by separating axes somehow but it has been next to impossible for me to find information that can show me how to accomplish this in a manner to which I can comprehend. I am willing to post whatever information it may be that anyone needs in order to help me...I have been working at this chart for two days now and I am so frustrated that I would appreciate any help at all.

Thank you very much.

Hi,

I know how to change a data series so it can be a line graph. This is great if the target is a constant. I have a graph that has 4 columns of data (each with the same 3 series of bars). For example;

The 3 series are - Target, Actual, Planned
The 4 columns are - Quarter 1, Quarter 2, Quarter 3 and Quarter 4

The target for Q1 might be 50, Q2 100, Q3 150 and Q4 200. I want a line to only go through the Actual and Planned columns for that Quarter (i.e. not a line that goes up and down across the graph). As it is, I could draw a line however the target figure can (and knowing my luck, probably will) change from time to time.

Anyone with any ideas would be greatly appreciated.

Thanks,

Ineedcoffee

I have been tasked to try and figure out how to create a bar graph... with horizontal lines ... based on two columns of data... time and dates. The times vary and the dates vary throught the columns. At the top of the list might be a date of ..... 24-Oct-05 and then in the middle of the chart that same date may appear and so on throughout the list. So, the dates and times will vary/overlap down the list. What I am wanting to learn to do, is how do I create a chart that will build a visual chart to allow resource assignments based on dates and times???? I don't think I have GANTT tools/capabilities and I am working in a location that will not allow an XLA download... or I would have done that already.... LOL!

Sorry I don't have much more information. I am new to this chart building idea and am not really sure what to put in this request. I will answer as many questions as I need to get this figured out . I have already tried to use the PivotChart wizard and that doesn't seem to work... Course, I am not sure if it is working properly or not, I just know that I can't get all my dates and times to show up on the chart. It only shows ONE date and ONE time. Which isn't what I want. I only need ONE chart... don't care about lines or bars... I just want to be able to show all the dates and all the times.... for example, the dates range from 24-Oct-05 to 04-Nov-05 (in that format) and the times will range from 1200 am to 1159 pm (in that format). So, you could end up with MULTIPLE times on the same day. Should I just try to get a copy of Project? Or can this be done in Excel without Project or a GANTT chart capability?

Not sure if this helps... but I should could use some direction/assistance. Thanks in advance. Hope I explained it well enough.

Hi,

I have some VBA that conditionally colours bars or points Green / Orange / Red according to a "traffic light" formula. This data all belongs to a single series but I wanted a legend denoting Green = Good, Orange = Warning, Red = Bad. So, used the following code to attempt this but seem to be getting casught up with using NA() as the value. Perhaps someone can point out how to get around this?

Code:
Sub Build_Charts_Click()

'Change Chart Names
'ActiveSheet.ChartObjects("Chart 28").Name = "Chart 4"

'Input data source worksheet Here
DataSource = "Data"
'Input destination (Graph) worksheet Here
GraphSheet = "Graph"
'Input Cell Location data Here
'Max Values
RowNum = 40
ColNum = 98 'Use ASCII Character Reference for Column, not column number
'Input Chart Numbers Here
StartRange = 1
EndRange = 4
' Change this number is you need to move the rows down to facilitate more data
ModifyRow = 0

For ChartNo = StartRange To EndRange
    
    'Initialise Chart
    ActiveSheet.ChartObjects("Chart " & ChartNo).Activate
    
    'Apply placeholder conditional colour data for Legend
    With ActiveSheet.ChartObjects("Chart " & ChartNo).Chart
        
        'Green
        With ActiveChart.SeriesCollection.NewSeries
            .Name = Sheets(DataSource).Range(Chr(ColNum) & RowNum + ModifyRow + 29).Value
            .Values = Sheets(DataSource).Range(Chr(ColNum) & RowNum + ModifyRow + 32).Value
            '.XValues = Sheets(DataSource).Range(Chr(ColNum) & RowNum + ModifyRow + 33).Value
        End With 'Green
        'Orange
        With ActiveChart.SeriesCollection.NewSeries
            .Name = Sheets(DataSource).Range(Chr(ColNum) & RowNum + ModifyRow + 30).Value
            .Values = Sheets(DataSource).Range(Chr(ColNum) & RowNum + ModifyRow + 32).Value
        End With 'Orange
        'Red
        With ActiveChart.SeriesCollection.NewSeries
            .Name = Sheets(DataSource).Range(Chr(ColNum) & RowNum + ModifyRow + 31).Value
            .Values = Sheets(DataSource).Range(Chr(ColNum) & RowNum + ModifyRow + 32).Value
        End With 'Red
        'Add Legend
        ActiveChart.ChartArea.Select
        ActiveChart.HasLegend = True
            
    End With 'ActiveChart

Next ChartNo

End Sub
When running I get an 1004 error "Unable to set Values Property of the series class" (Have bolded the line where this occurs). The cell it is pointing to holds the value NA().

What am I doing wrong?

Hi

This is my first post here, so I hope I've done everything correctly in accordance with the rules My downfall is my "descriptive subject line" because I ran out of room! In any case, here goes:

I'm using Excel 2007 Ultimate Edition under Windows Vista Home Premium. I'm not a "power user" by any stretch of the imagination - my uses for Excel are very limited. However, I'd really like to use it for a new application and I can't figure out how to get out of it a few of things, which I'm sure are there - I just can't figure out where to find them.

I've tried looking these up though Excel's own help files, checked out the Microsoft Knowledge Base and searched here as well. My main problem with each search place is the same as I mentioned above - finding a way to search for what I'm looking for by only using a few short words has been pretty much impossible. So, here I am.

I'll tell you up front why I'm doing this graph just so you don't think I'm trying to scam free information when I could be a business and pay for expensive courses to learn how to do all this stuff. I'm a disabled student who has recently been diagnosed with diabetes and want to create a simple way to keep track of my blood sugar levels every day. To go along with the actual spreadsheet (which was easy to create except for a couple of things) I'd like a graph that I could give to my doctor each visit rather than him thumbing through a notebook (which is what he expected me to provide him). Considering he knows how much of the day I spend on the computer (I study externally because of my disability) then he could well expect this more modern-day approach from me anyway

I went to the Microsoft site and got myself a basic Blood Glucose Chart that someone else kindly put together. I've changed it more than enough now to make it my own, so thank you to the wonderful person who created it. It had a chart included but it didn't do any of the things that I wanted it to do so that's why I am here.

The first thing is this - my Excel spreadsheet is going to be only three columns wide with date, time and then the varied test results in the last column. The number of entries per day will vary and I'm fine with entering the current time and date in the first two columns (I have at least that bit worked out!). The last column will change every time.

I've also managed to work out a colour coded system for the last column of data so that I can easily see if it's too high or too low or just right. I've never really done anything much with Excel before at all except for a budget and the like so this is all new stuff for me! I'm pleased that I got that much worked out!

The spreadsheet data entry area has been set up so that the most recent date is at the top and the oldest down the bottom. This is so I will always have the most recent entries on view at any one time and the older ones further down the page. What I want to be able to do is that once I enter the data on the top row, I want it to automatically create a new blank row above that and make all the current data go one row down. I'm sure I could create some kind of macro to do this, but as I've never created a macro in my life and, even after reading up on it in various places (Excel help section, etc as above), it's all still a bit beyond me I think. So does anyone have any suggestions for how this might work? What sort of macro might I need or is that what I need at all? Also, by creating the line new at the top every time will that include the formatting of the lower line (ie the colours for certain entries)? At the moment it does but will the automation take that away? By creating the new lines manually I find that I have to leave two blank lines at the top or otherwise when I create the new row I create the "header" row properties and not the normal row ones (the header being bold and coloured and informative not blank and noraml-). I'm not sure why it happens that way, it just does! This is the end of only the first question!

:: phew ::

Next question ... The line graphs (and it's a line graph that I'm almost certain I want) that I can create very easily using the Insert --> Line graph button are fantastic, but I'm curious as to why they can't be made permanent? I know they can be created as a template and then it's only one click to get to exactly as I want it to be (which I've done), but I'd like things to be done automatically (had you figured this out yet?). I'd really like a permanent graph (maybe on a separate sheet similar to the one that came with the one from Microsoft?) but that will also automatically update as new data is added. Also, I want to be able to target certain data at a time - say a particular week. At other times I want to see all the data that's in the sheet. Or year to date. You get the idea.

I've done a little work on the custom template graph that I created, but I'm gathering that the more permanent graph would be put together differently - well, I'm almost sure? There are, however, two particular things that I'd like to do with it. The number of results throughout a day will change - sometimes one or two results, sometimes five or six (depending on what my results are). When I create the graph, however, I want the width of a section for each day to be the same. So I guess what I need is for the axis at the bottom to be over hours throughout a day rather than just one point on the graph for only each and every test. The latter would result in a graph that would give a false sense of trends - if my blood sugar was high for one day that might be okay. But if I took five tests that day and they are all high compared to one test on a low sugar day then it's going to look like I had a lot of highs and only one low. By comparison in my perfect graph all the highs will still look like only one day and so the one test on one day that was low will show up as being comparable - I had one day with high tests and I had one day with a low test.

So I need to carefully set up that axis on both x and y (I need the "x" to reflect everything from, say, 0 to about 16 - these are reasonable limits of the glucose tests we use in Australia although it does go higher - which I know is different from the results in, say, the USA - and then the "y" date range across the bottom to be time over individual days - as above). This isn't the only thing that I need on my graph, but it's a good start anyway! And if I can learn the big stuff I might be able to work the little stuff out on my own.

Okay, two final questions and they're both frippery but frippery I'd still like ... As I said before, I've colour coded the results so that if I go above, below or between certain numbers then it will fill in the cell with various colours. I tried to mix this with bars (I've just had a look for them and can't find the exact name now, but the bars that vary in length over the width of a cell to reflect the result) so that the coloured bars I have for high, middle and low would be longer or shorter to give me a view of these results immediately but I couldn't find a way to do it. Any ideas on this one? Then I'd like to have the dots in the graph reflect the colour that the fill in of the cell is that it comes from. So high results would not only be high on the chart but red as well. Or perhaps that the fill behind the bars above a certain figure would all be red? So that it was easily seen that every time the chart went into that area it would be bad. And being blood sugar results I have red then yellow then green then yellow then red again because being low is as bad as being high and there are mediocre results as well (hence the yellow). Is any/all of that possible? It's nothing more than just another clear showing of where those results lay in the table but my Doctor needs this, trust me!

Thank you all in advance for reading through all of this and I hope someone or a few people out there are able to help me. I will be most grateful, although what I can offer is return is probably not very much. But I will be happy to offer this whole spreadsheet/workbook to any other diabetics (or those who know diabetics) out there who are interested in it and would be happy for someone to serve it on their sites for that purpose.

Much appreciation.

Kind regards

Vicki

Here is my dilemma. I made a pretty nice Gantt Chart (thanks to you Mr.
Peltier) which I'm fairly proud of. I stuck the thing in a worksheet so I can
have names and other information like how much per week the people on the
chart are working and the start dates and end dates. I would like to "lock"
down the chart in the worksheet so the bars on the chart will always line up
with the names and other data next to them. Is this possible?

I have a 2-D clustered column chart that shows the mean value of 4 different
samples using chunky blue coloured bars.

What I would also like to do is, on the same graph, show the standard
deviation of each sample on the relevant blue bar, such that a vertical line
is inserted in the middle of each of the blue bars that shows 1 standard
deviation up the way and one standard deviation down the way (i.e. so that it
looks like 2 letter "T"'s, symmetrically above and below each other).

I've not explained that very well but I hope someone knows roughly what I'm
trying to do - its really a typical statistical chart that shows both mean
and standard deviation on the same graph.

I can post an image if this site allows and if it would be useful.

Regards

Using Excel 2003. Plotting a line-column chart using two columns of data
"Actual" & "Target". How can I change the order of the series, so that the
bar becomes the line and the line becomes the bar, without changing the
location of the data?
Excel help has me select a data series, click on "Format", "Selected Data
Series", "Series Order" and use the "Move Up"/"Move Down" buttons.
However, these buttons are grayed out (since I can only select one series
there is only one series showing in the "Series Order" window).
What am I missing? Or, is there another way of accomplishing this?

Thank you,
Tim

Hi All,

I am generating a chart (Stacked Bar) where number of series is fixed
(6) but number of data points vary and as a result the scale of the Y
axis keeps on changing every time it is refreshed.

I also have three lines on top of the chart which I need to show to
define boundaries within which the data points should lie. One at the
origin (coinciding with X Axis), other at 50% and the last one at
-50%.

My Y axis will never go beyond 100% and -100% but because the X aixs
labels keep on expanding as I select more data to plot, the graph
moves up and down and the lines changes their position.

Is there anyway, I can keep the line static?

Please help

Thanks in advance
Anshuman

Ok, bare with me here and refer to the attached image for reference.

I need to resize a table along with a chart(bar graph) so that it fits to a specific "measurement". Basically, everything looks exactly the way I want it to now, but my problem is that I need to grab each chart(with table) individually and put them into a word document, WITHOUT resizing. I don't want the heights of my vertical axis to get skewed when I paste the image into the word document.

My word document has 1 inch side margins, which leaves me a total size of 6.5" as a max distance of the largest table(in the picture, the table corresponding to the blue has the widest distance. As you see in the picture, the blue chart alone is a width of 8.19", meaning the table included snapshot must be at least 8.5", 2" too large. Therefore to fit it, the image has to be shrunk down.

My only solution: essentially, I could shrink the charts both proportionally so that the largest is 6 inches, and therefore the blue chart's table would be about 6.5" and no greater, therefore when I pasted these two separate pictures in, the vertical axes would be the same exact height. That is my goal. Unfortunately, In order for this to work, I'll also need to decrease the font size of the tables, as well as the width of the columns so that it lines all up pretty like it does now. I will then need to manually stretch the actual plot size so the bars are perfectly over the corresponding amounts. That is a LOT of work for such a dumb problem to have. Especially, because I have to do this for about 30 different workbooks.

There HAS to be an easier solution than mine to this problem. Please ask if you have any questions or I was unclear.

P.S. The way the image is grabbed from excel is essentially like a print screen, except it captures the exact dimensions of the chart in excel. i.e. you can't just zoom out and take a print screen so everything is smaller.

Ok, bare with me here and refer to the attached image for reference.

I need to resize a table along with a chart(bar graph) so that it fits to a specific "measurement". Basically, everything looks exactly the way I want it to now, but my problem is that I need to grab each chart(with table) individually and put them into a word document, WITHOUT resizing. I don't want the heights of my vertical axis to get skewed when I paste the image into the word document.

My word document has 1 inch side margins, which leaves me a total size of 6.5" as a max distance of the largest table(in the picture, the table corresponding to the blue has the widest distance. As you see in the picture, the blue chart alone is a width of 8.19", meaning the table included snapshot must be at least 8.5", 2" too large. Therefore to fit it, the image has to be shrunk down.

My only solution: essentially, I could shrink the charts both proportionally so that the largest is 6 inches, and therefore the blue chart's table would be about 6.5" and no greater, therefore when I pasted these two separate pictures in, the vertical axes would be the same exact height. That is my goal. Unfortunately, In order for this to work, I'll also need to decrease the font size of the tables, as well as the width of the columns so that it lines all up pretty like it does now. I will then need to manually stretch the actual plot size so the bars are perfectly over the corresponding amounts. That is a LOT of work for such a dumb problem to have. Especially, because I have to do this for about 30 different workbooks.

There HAS to be an easier solution than mine to this problem. Please ask if you have any questions or I was unclear.

P.S. The way the image is grabbed from excel is essentially like a print screen, except it captures the exact dimensions of the chart in excel. i.e. you can't just zoom out and take a print screen so everything is smaller.


No luck finding an answer? You could always try Google.