Free Microsoft Excel 2013 Quick Reference

smart charts

Is it possible to have a chart's source data cover ten series, but to chart
only the series where values are populated, i.e. if there is no value and
just blank text, it wont be charted?

I'd like the entire chart including the legend to be smart enough to exclude
those series.

is there any way to do this?

Help would be greatly appreciated!

thanks,
Dave


Hi,

I've been trying to implement a task that is out of my humble MS Excel knowledge and any help will be much appreciated.

I have a table with integer numbers that is dynamic. It means that if I change a cell value outside this table its cells change to zero or an integer number. For example, if I change the value /in my case: date/ of the outside table cell all table cells that don't meet that requirement return zero and the rest return integer numbers. The table is constructed date-by-date, so the zero values come at the top of it, followed by integer numbers only, no zero values. My purpose is to construct a chart that takes only the integer numbers of that table and avoids the zero values at its top.

I look forward for any ideas...

Regards,
kras

I have a very simple line chart with two rows of data, and columns (dates) stretching out several months. Without having to constantly revise the source data, is there a way to have the chart automatically ignore cells with empty data?

For example, if every column is a day in July, I wouldn't want the chart to continue to the 31st.... only to today... and so on...

Thanks in advance!!

Hello,

I have employees table with the following fields (Excel 2010):
field1, employeeID, ManagerID, field3, field4, field5, EmployeeName.

The following code generate smart art org chart.
My problem: If the are more than 1 root it does not add the second root in level 0 (separate tree).

How can I force new tree for each root ?

Thank you for your help

'Source is current open worksheet, 
'Source=ThisWorkbook.Sheets(name of the current list)

Private Sub CreateDiagram(Source As Worksheet)

    Dim oSALayout As SmartArtLayout
    Dim QNode As SmartArtNode
    Dim QNodes As SmartArtNodes
    Dim Line As Integer
    Dim PID As String      'identification of parent node
    Dim oshp As Shape
    
    
    Set oSALayout = Application.SmartArtLayouts(88) 'reference to organization chart
    Set oshp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(oSALayout)
    
    Set QNodes = oshp.SmartArt.AllNodes
    For i = 1 To 5      'delete all included nodes
        oshp.SmartArt.AllNodes(1).Delete
    Next
    
    'looking for root(s)
    Line = 2
    Do While Source.Cells(Line, 1) <> ""
        If Source.Cells(Line, 2) = Source.Cells(Line, 3) Then
         
         
         Set QNode = oshp.SmartArt.AllNodes.Add
                     
           QNode.TextFrame2.TextRange.Text = Source.Cells(Line, 6) 
                    
            PID = Source.Cells(Line, 2)
            Source.Rows(Line).Delete
            Call AddChildNodes(QNode, Source, PID)
        Else
            Line = Line + 1
        End If
    Loop

End Sub

Private Sub AddChildNodes(QNode As SmartArtNode, Source As Worksheet, PID As String)
    Dim Line As Integer
    Dim Found As Boolean
    Dim ParNode As SmartArtNode
    Dim CurPid As String 'ID of current parent node
    
    Line = 2
    Found = False    'nothing found yet
    Do While Source.Cells(Line, 1) <> ""
        If Source.Cells(Line, 3) = PID Then
            Set ParNode = QNode
            Set QNode = QNode.AddNode(msoSmartArtNodeBelow)
            QNode.TextFrame2.TextRange.Text = Cells(Line, 6)
            CurPid = Source.Cells(Line, 2)
            If Not Found Then Found = True 'something was find
            Source.Rows(Line).Delete
            Call AddChildNodes(QNode, Source, CurPid)
            Set QNode = ParNode
        ElseIf Found Then    'it's sorted,so nothing else can be found
            Exit Do
        Else
            Line = Line + 1
        End If
    Loop
    
End Sub

Public Sub test()
Call CreateDiagram(Sheet1)
End Sub


I have a pie chart to present my data.
I have data label to show the information.
Because I update data daily and value change a lot, the data label change position to overlap each other.

Are there any way to have data label have fix location or smart enogh do not overlap each other while data change?

Thanks in advance.

Inung

I have a line chart using date as data label.
Excel is very smart to figure out it is a date time.
Since it knows it is a date data, Excel auto fill the wekend as data label.
For example, I collect data daily from Monday to Friday. There is no data during the weekend. On the chart, Data label shows weekend date.
May I turn it off?

Your information is great appreciated.

Inung

Can anyone instruct on how to create charts like Microsoft's Competency Wheel Chart?

I am not even sure what kind of chart it is exactly...

At first blush it appears to be a flavor of donut chart, except that the data in the rings are dependent on each prior ring.

It does look like a good candidate for a hierarchy list type of Smart Art... but I have had no luck there either.

It does contain information that could be reproduced in a pie of pie format, but I am hoping for the a graphic approximating the image.

Any assistance would be greatly appreciated!
Have a great day.
-s.

Hi All,

I need to produce a line chart across three months, but show the data points
by week.

my data points each have a date attribute (ie 5/21/2006). I don't know a
smart way to do this other than making a new table (week 1, week 2, etc) and
lumping the data that way.

Thanks as Always! Kurt

Hi. I want to make an animation from excel charts. I have already written a
Visual Basic program that saves the charts as static gif files. I don't know
much about animation, but when I tried to create a short video using a gif
animation program, the file size was big because I didn't know a smart way to
tell the animation program that the excel files were mostly similar from one
image to the next.

Is there a particular gif animation program that works well with charts
exported from Excel (for example, reading the different parts of the excel
chart as separate "objects" for animation)?

Thanks,

Parke

Hi,

I am making a custom chart and hits the problem mentioned in the
article from Peltier:
http://peltiertech.com/Excel/ChartsH...stomTypes.html

It says: "Another problem with a custom chart type, or any chart with
customized formatting. If a chart element with custom formatting is
removed from the chart, then added again, the custom formatting is
lost, and the element takes on the default formatting for that element
in the default chart."

Is there a smart way to solve this problem? Could the best thing to do
be some VBA-coding and to keep track on the change-event from the chart
and then reapply the custom chart type?

I know perfectly well that I can't plot negative values on a log chart. That
doesn't mean that my data doesn't have negative numbers in it. Is there any
way that anybody knows to disable this irritating message? My productivity
drops 100% because every time I scroll around in a sheet that happens to have
a negative number on a log chart somewhere, this irritating dialog pops up.

I don't want to have to build my own log charts that ignore negative numbers
(I could, but what's the point of a built-in log chart if you can't use it).
I don't want to have to write formulas for cells to make the offending
negative value "invisible" to the chart (we've tried dozens of ways, and they
all fail to be user-friendly at some level). What I want is to tell Excel
that I'm smart enough to know that when the chart has a hole in it, it's
because it couldn't plot the data. What's so hard about that?

This has frustrated me for years. Can anybody help?
--
Roger Stout, PE
Senior Research Scientist
ON Semiconductor

Hi,

I user JWalk Chart Tools to add lables to my bar chart that are
different than the actual values. By bars represent salaries whereas
the lables in each bars represent the salary increase since last year.

JWalk work fine... at least it does if you do not have hidden rows in
your data series.

I have about 100 charts that points to the same data series. The
issue I have is that I have to filter my list based on a different
criteria for each of the 100 charts. When you use a filter, Excel
simply hides rows and the excel chart is smart enough not to include
the hidden rows in the chart

However, I can't say the same thing for JWalk Chart Tools, IT DOES NOT
take into account the hidden row and as a result it uses the label
values of both visible and hidden rows. This of course is not
acceptable.

Any ideas on how to have JWalk (or other tools) to take into account
hidden rows?

HELP

- GuyRoch

I have an Excel workbook, some stock symbols have smart tags, some don't.
Have checked to make sure stock symbols are capped, smart tags option is on,
checked the work book, closed & opened the file ...WTR is one that doesn't
show up. It's traded on the NYSE.

Thanks.

I know perfectly well that I can't plot negative values on a log chart. That
doesn't mean that my data doesn't have negative numbers in it. For years
I've been trying to outsmart Excel so that it effectively ignores negative
data on log plots, but it seems to me that it should simply happen (like it
does in most other mathematical tools). If the user isn't smart enough to
know that he can't plot negative numbers on a log scale, well, warn him by
default, if you must, but allow the skilled user to disable the message! It
is incredibly annoying, and it reduces my productivity by 100% when I'm
analyzing very typical data in thermal tests.

I can turn off just about anything other automatic stuff else that bothers
me (like converting cells with "@" into email addresses, for crying out loud
- that took me a long time to figure out how to disable). What's wrong with
a "don't show me this again" checkbox on the "Negative values cannot be
plotted correctly on a log chart." warning dialog? What's wrong with simply
ignoring the negative data and not plotting it, just like you do with #NA and
#VALUE and all those other unplottable values?

I don't want to have to build my own log charts that ignore negative numbers
(I could, but what's the point of a built-in log chart if you can't use it).
I don't want to have to write formulas for cells to make the offending
negative value "invisible" to the chart (I've tried dozens of ways, and they
all fail to be user-friendly at some level, like making OTHER dependent
formulas fail gracelessly). What I want is to tell Excel that I'm smart
enough to know that when the chart has a hole in it, it's because it couldn't
plot the data. What's so hard about that?

This has frustrated me for years. Can you tell?
--
Roger Stout, PE
Senior Research Scientist
ON Semiconductor

----------------
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 suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...excel.charting

I am setting up a template spreadsheet that will automatically do
calculations and graph groups of data into a single scatter plot chart. I
have a defined set of 13 groups of between 6 & 150 data points each that I
reproducibly collect each day. So I have set up formulae and a chart to
automatically calculate and plot as the data are entered.
The problem that I have is that each day the number of data points in
each group will vary. So I either have too many data points, so some don’t
get graphed, or I have too few data points and I get a graph of zeros after
the real data has run out. I figure the first problem is easily solved by
just setting up more rows of formulae then I’ll ever need for each group.
But I can’t get by the second, if there is a formula in a cell that is within
a chart’s data range it will graph the value as a zero even if the cell
appears empty.
I am wondering if there is any way to set up, for example, an “if”
funtion that will return a value that won’t be graphed by Excel, or to make
the chart smart enough to not graph data that isn’t “real”? Or is there some
way to set up my functions better?
I am working from the assumption that there is no reason for me to have
to tinker and manually do the same analysis over and over. I am trying to
work smarter not harder. Any help will be appreciated.

Hi - it appears that you can't use the X-Y chart labeller when using the new "smart object" embedding in PPT 2007 - is there something I'm missing? This seems to be because chart and data sheet are linked but not in the same workbook (while in 2003 they are in the same file).

Is there a new version of the chart labeller that will work, or do I have to embed the old way in 2003 to preserve this functionality?

Thanks, any help is much appreciated.

i'm editing someone else's work and they had created chart templates which still work fine... except for the colors.

we've changed the color scheme so that when we apply the old styles, text comes up as lime green. i was wondering if there is a way in vba to edit the color application. tell it that the text color is custom color no. 4 instead of 5?

they are MS Excel objects in VBA in a sheet. when i look in the "Chart Type" under Custom, the charts are "Built In" [normally one might expect to find them under "user-defined"].

anyway,this guy was super smart and if i have to start from scratch, it will be a much bigger project.

Ideas?

excelvbascreenshot1.jpg

I am trying to combine data from multiple worksheets and make a chart. I have about 200 keywords in every worksheet (about 50), and some of them repeat themselves through worksheets and some don't. For every keyword, I have an associated value in the next column that I want to portray over time (each worksheet is for a different period).

So what I need to figure out is how to be able to pick any 10 keywords from the worksheets and put them in a line chart where I can see the associated value for each period for every worksheet so I can compare my keywords' efficiency. The tricky part is that some worksheets do not contain the keyword and other worksheets contain the keyword in a different cell than the previous wsheet.

I probably confused the heck out of you, but if someone is smart enough to answer this, I will admire you forever. Thank you

QUESTION: How do you get a chart to continually update its data range to only include cells that have information in them?

I post multiple charts on my blog and web site daily. They involve data that is recorded every day and therefore the range must be extended with the new data.

Currently, I go in to the chart, choose "Select Data," and then edit the range to include the new information. I know this cannot be the "smart" way to do this.

Thanks for the help.

I have an Excel workbook, some stock symbols have smart tags, some don't.
Have checked to make sure stock symbols are capped, smart tags option is on,
checked the work book, closed & opened the file ...WTR is one that doesn't
show up. It's traded on the NYSE.

Thanks.

I am setting up a template spreadsheet that will automatically do
calculations and graph groups of data into a single scatter plot chart. I
have a defined set of 13 groups of between 6 & 150 data points each that I
reproducibly collect each day. So I have set up formulae and a chart to
automatically calculate and plot as the data are entered.
The problem that I have is that each day the number of data points in
each group will vary. So I either have too many data points, so some don’t
get graphed, or I have too few data points and I get a graph of zeros after
the real data has run out. I figure the first problem is easily solved by
just setting up more rows of formulae then I’ll ever need for each group.
But I can’t get by the second, if there is a formula in a cell that is within
a chart’s data range it will graph the value as a zero even if the cell
appears empty.
I am wondering if there is any way to set up, for example, an “if�
funtion that will return a value that won’t be graphed by Excel, or to make
the chart smart enough to not graph data that isn’t “real�? Or is there some
way to set up my functions better?
I am working from the assumption that there is no reason for me to have
to tinker and manually do the same analysis over and over. I am trying to
work smarter not harder. Any help will be appreciated.

Hi All,

I need to produce a line chart across three months, but show the data points
by week.

my data points each have a date attribute (ie 5/21/2006). I don't know a
smart way to do this other than making a new table (week 1, week 2, etc) and
lumping the data that way.

Thanks as Always! Kurt

Hi,

I have another query.....is there a simple way to merge the date data shown in the excel spreadsheet attached so that the date appears as a typical date i.e 1/12/2007?

I also want to create several charts with the data. Is there a smart way to do this rather than scroll down to each cell and highlight, scroll down to each cell and highlight? I have over 35,000 lines of data in my spreadsheet

Thanks,

Shakeydude

Hello all,

I have about 50 columns I would like to make identical charts for, with different data based on the column. I am a VBA noob, and this is way beyond my abilities. So first off, I don't even know If it's possible.

However, I have a sneaking suspicion one of you smart fellows out there one excelforum.com knows how to solve my problem.

Anyways, back to my problem. I would like a code such that I can do it all once, and then "drag" the chart across to all the columns (i.e. have the chart code in a while loop for a range of columns). The attached sheet 2 posts down shows information on two tabs and shows the example chart something similar to what I would like. I would also like to position the chart and reletave to a cell in the coloumn while making the cahrt the same width as the column.

You will notice I used X error bars to make lines for my LCL,CL,and UCL control limits. I am sure there is a more elegant way to make a line from a single datapoint.

Thanks in advance!

I am trying to create a bar chart with a subset in Excel 2000.

For example, I want a bar that shows total growth in smart phone sales (50%) and company xs growth is 31% of that 50%. The problem I am running into is that Excel add the two rows and show the main bar as 81%.

What I would like is that the main bar should be 50% and in that 31% should be the shaded area.

Thanks.