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

Free Microsoft Excel 2013 Quick Reference

Pivot Chart - show 1 row value, but also showing percentage of who

In using a pivot table and pivot chart, how can I only show one row value,
but still be able to show the percentage of the whole set of rows?

Thanks for any help you can provide!


Post your answer or comment

comments powered by Disqus
hi everyone,
i am looking for a code which can be used to change the color of a series in a pivot chart based on it value.like if series value is greater than 60 - green,between 50 - 60,yellow and less than 50 red.
please help.

Hi,

Is it possible to set a chart to use only one set of the available values shown in a PIVOT table. The table is created from an Office data connection and is initially set up to show companies along the top and the side has three date groupings each with a fund value, a performance value and a target value(not relating to the previous fields). What i'm looking to do is programatically insert a chart in place of the target line which takes each value for target against the each company. The reason this is getting done programatically is that i intend on copying this chart object into each target row of each of the three date periods.

This effectively shows the fund value, then the performance value below that and then a chart plotting the target value. This is repeated for each date period. I'll alter the chart width and height to show each target data point under its respective company.

The problem i'm having is that i don't seem to be able to select just the target values as the data for the chart. It selects everything from the pivot table. Is there any way just to select the target row for the chart. I did wonder if there was anyway to confirm the data to the sheet and have it shown as cells instead of the pivot table, then i could quite easily implement this.

Any help is appreciated.

Thanks,

G

Hello everyone.

I've been told several times that I need to start using Pivot Tables & Charts when reporting so I've finally decided to give it a shot in a survey report I'm working on but I've come across an issue I can't find an answer to.

I'm using Excel 07. I've sorted the Category "Response Choice" to the order I'd like but the chart insists on reporting the order backwards. How do I get the chart series order to match the table order?

My 2nd question is, the Pivot Chart shows both the Count & % values from the table (% is there but very small so you can't really see it). I need this chart to only show % values but for the life of me I can't figure out how.

Please help me resist the temptation to use an old style table with a bunch of formulas lol

Thank you in advance.

I'm trying to nto show zer values in a pivot chart. Is this possible to do?

I've tried subsetting the pivot table to only show non-zero values, but this is not effecting the Chart.

i'll fire off straight away:

1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.

2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?

3) am i going about this the wrong way with the objects? should i be after vba code?

thanks all

If I simply publish the pivot chart, the correct percent values are displayed on the y-axis. However, when I publish it with the interactivty option, the percent values are wrong. For example, the actual value is supposed to be 13.7% but graph shows 46496000.00%. The other oddity is that the table published in interactive mode has the correct values.

I have a column in my source data called "TIER" so the values are 1, 2, 3
etc. when I convert to a pivot chart I want those values/bars to be
automatically & permanently assigned a specific color. Eg. Tier 1 = Green,
Tier 2 = Yellow etc.

Can you help?

I have a column in my source data called "TIER" so the values are 1, 2, 3
etc. when I convert to a pivot chart I want those values/bars to be
automatically & permanently assigned a specific color. Eg. Tier 1 = Green,
Tier 2 = Yellow etc.

Can you help?

I've got a chart with some dollar values that are all listed in Column I and get updated each time I add an entry to the sheet.

What I'd like to do is have my chart dynamically adjust to show only the last 25 values I've entered.

I've attempted this with the offset formula but I'm quite a bit confused by it and didn't have any luck.

Column I
 $145.00 
 $140.00 
 $144.55 
 $153.64 
 $143.64 
 $148.19  < Show from this value down to the bottom of the list.
 $138.19  < When new value is added, show from this value to the bottom of the list.
 $128.19 
 $137.71 
 $127.71 
 $145.00 
 $140.00 
 $144.55 
 $153.64 
 $143.64 
 $148.19 
 $138.19 
 $128.19 
 $137.71 
 $127.71 
 $145.00 
 $140.00 
 $144.55 
 $153.64 
 $143.64 
 $148.19 
 $138.19 
 $128.19 
 $137.71 
 $127.71
Thanks

I have two pivot charts based off two pivot tables. The legend format does not show how the pivot table format is displayed.

The rows Month Notified and Month Resolved should show as MMM-YY but one shows MMM and the other MM/DD/YYYY.

How do you get the format in a legend to match?

I've attached an example.

Hi,

I have a minor problem. Each day for my job I have to execute a SQL query in SQL a server database and then copy the data into a excel spreadsheet. Once the data is there I then create a number of pivot charts based on that data (which is the number of calls made by certain customers over a 24 hour period). The first time i create these charts it works fine. However, for the next day when I copy the data over again (and try and refresh the pivot table with the the new ranges) the graph is completely out of shape. I believe the problem is that for each day the data comes in various sizes (e.g. sometimes its 2000 rows, sometimes its 3000 rows).

Is there any way that when I refresh my pivot table data my pivot chart will refresh properly also? As it is getting a pain having to recreate the charts each time.

I want to add a gridline on a chart that shows a custom value, but I can't
figure out how to do it. Any help would be appreciated. Thanks.

Hi,

Posted this in the wrong forum so repost here.

I wonder if it is possible to use a Vlookup in conjunction with some logical expressions to not just match a specified value, but also return a value in the specified column if it is equal to or greater than a value?

I have a main worksheet that looks like this:

A B C
Date Price Shares Owned
06/08/2005 59.11
05/08/2005 59.11
04/08/2005 58.9
…..

I then have another worksheet that details dates when purchase transactions took place:

A B C D E
Ref Purchase Date No Shares Price Per Share Total Shares
144 25/04/2005 50 53.3 50
145 27/05/2005 25 54.94 75
146 29/06/2005 25 56.37 100

In my main worksheet, for each ‘Shares Owned’ Cell I would like to be able to perform a lookup against the purchase date and return the value in column E if the date is equal to or greater than the purchase date.

Any help would be appreciated!

I have published pivot chart with interactive option checked but it
never worked fine. Viewing the page on web shows this error message:

Failed to get data. If available, errors returned from the provider
are listed below.
* Error opening data file *
{file_path_xxx_cahce.xml}

Can any one specify what is the problem, how to resolve it and how can
I show pivot charts interactively on web. Seems that pivot charts need
to generate xml cache file but how they will generate, should I publish
it on some server ? I am using Office 2003 with all updates on my
system and viewing page from my local system (not hosted on any
website).

Hello

Is there a way for me to crate a pivot chart in Excel where the data is in a
secured access database.

By default (ie using system.mdw) the Access security allows a user view
permissions on the tables. When connecting to the database the tables are
visible, but the next step where the fields get shown doesnt happen, no
fields can be selected.

Is this a security issue? Is there a way around this?

Thanks
A

I have a spreadsheet that I need to use the auto filter feature to display a
particular subset of data. I then want to create a pivot chart with the
limited data, but using different column and rows than the one filtered.
Whenever I try this, my pivot chart picks up the entire spreadsheet contents,
not just the filtered data.

I'm on Excel 2007.

I have a pivot table and a pivot chart on sheet1.
Then I copied this sheet within the same workbook.
The copied sheet is sheet2.

The pivot chart on sheet2 keeps referring to the pivot table on sheet1.

I clicked on the pivot chart to change the source but it's grayed out.
I even clicked inside the chart for it to show the SERIES formula and you can edit it, but you can't press enter to apply the changes.

This enquiry is not about changing the Pivot Table data source. It's for changing the Pivot Chart data source.
I know how to change the data source of a pivot table.

I've tried copying and pasting both the pivot table and pivot chart together, but the copy of the pivot chart keeps pointing to the original pivot table.

I know I can just delete the copied chart and create a new one. but it's a lot of work.
I know i can just the chart format as a template and then apply it to a newly created chart, but it doesn't always work well and then i still have to adjust the new chart size manually.
I'd a lot of charts. So creating each of them again manually is quite a bummer.

I have been trying to record a macro to copy a row to another worksheet then clear the contents (I dont wish to delete it, but leave it intact as a blank row) of that row in its current worksheet. Problem is that it always deletes the formula and formatting in that row. Is there any known workaround for this so that it just deletes the rows values and leaves everything else intact.

I'm having a strange issue which I'll do my best to describe. Basically I've built a formula to find the latest date that a mating pair of animals had a litter. I used the process described here: http://www.get-digital-help.com/2009...-in-excel/#all but modified for my own sheet. It seemed to be working well, I used the formula builder to make it and the value it said was the result was 40786 (or 8/13/11 when formatted as a date), however when I hit enter it came up as 0 in the cell. Confused I clicked on the cell with the formula builder still up and again it shows the result in the lower right of the box as 40786. It's probably something stupid I've done, but for the life of me I can't figure out what I messed up. I'll attach a image showing what I'm seeing and the Excel file to this message. Any help would be greatly appreciated.

Picture 2.jpg

For those who are wondering, the formula uses the "IF" statement to scan the "source" column of the animals for a number that matches the breeding pair (breeders are highlighted in blue) and then returns the DOB. Then the MAX formula should return the max (latest) date of birth that it finds for a litter that corresponds to that breeding pair. Or at least that was what I was thinking. Again, any help is greatly appreciated.

Hello,

Can anybody say - why I'm not allowed to filter values in Pivot chart? I would like to be able to see on a pivot chart the variable I selected only, cause looking to all series at once - makes analysis complicated.

Can you please suggest a way of making filtering possible?

J.

I've created a Pivot Chart in which I want to display travel by various folks to mulitple sites. The Site field is a Page field. If I set the Pivot Chart to a Custom -- Floating Bar style it displays fine, but if I change the Page field selection the chart reverts back to a stacked bar style.

I've tried setting the default chart type to Floating Bar (this doesn't take), and naming a custom style that is Floating Bar (and setting the pivot chart to this custom style -- but again the style does not hold on a change to the Page field).

I ran into this a few years ago and wrote some event macros to reformat the chart when the sheet is activated and when it calculates, but it seems there should be a simpler (built-in) way. Am I missing something obvious? Is there a way to make the Floating Bar style stick in a pivot chart without resorting to VBA updating?

I have 6 Pivot Charts in WB1 and would like to move all of them to WB2. How do I do it? I tried standard move/copy and it does it but the Pivot Charts becomes Static. How would I move/copy w/o effecting any dynamics of the Picot Charts. I have no problem moving/copying the Pivot Tables to WB2. I am trying to avoid recreating charts in WB2. Appreciate your help as always. Thanks!!

Hi,

I wonder if it is possible to use a Vlookup in conjunction with some
logical expressions to not just match a specified value, but also
return a value in the specified column if it is equal to or greater
than a value?

I have a main worksheet that looks like this:

A B C
Date Price Shares Owned
06/08/2005 59.11
05/08/2005 59.11
04/08/2005 58.9
…..

I then have another worksheet that details dates when purchase
transactions took place:

A B C D E
Ref Purchase Date No Shares Price Per Share Total Shares
144 25/04/2005 50 53.3 50
145 27/05/2005 25 54.94 75
146 29/06/2005 25 56.37 100

In my main worksheet, for each ‘Shares Owned’ Cell I would like to be
able to perform a lookup against the purchase date and return the value
in column E if the date is equal to or greater than the purchase date.

Any help would be appreciated!

--
dazman
------------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...o&userid=25903
View this thread: http://www.excelforum.com/showthread...hreadid=393674

Hi

I have a cell b1 with a formula entered in which is =sheet1!a1 but when I exit the cell it is not updating to the Sheet 1 A1 value but instead showing me the formula. it does seem to work if I enter in the formula into a different cell.

Any Ideas?

I also have a macro which creates a report from various source and outputs one sheet with all the figures. It works perfectly except I would prefer it to not jump from one screen to the next while it is producing the report any ideas on how to just stay on a sheet and have a msgbox say that the report is finished?


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