Showing the "total" value with a stacked BAR chart

Does anyone know how I can get excel to display the total 
in a stacked bar chart?  I tried the solution that 
someone posted for a stacked column chart, but that 
doesn't seem to work with stacked bars.  The third 
series, representing the total, just keeps plotting 
itself vertically while the other two series plot 
horizontally.  The result, as you can imagine, is a mess 
and not what I was going for at all.

Thanks, Anne   
0
anonymous (74717)
3/2/2004 6:39:19 AM
excel.charting 18370 articles. 0 followers. Follow

3 Replies
674 Views

Similar Articles

[PageSpeed] 38

Hi Anne

You can add a third 'invisible' series with stacked bars as well a
stacked columns, but to avoid the additional series orienting itsel
horzontally, you'll have to use an invisible bar, not a line. This ha
a couple more steps:

Add a third series to the chart whose values are the sum of the colum
elements
Right click on the new series an set the series axis to 'secondary'.
Right click on the series again, select format data series, select th
data labels tab, check 'value'
Right click on the series again, select format data series, select th
patterns tab. set line to 'none' and pattern to 'none',
Delete the secondary axis
If necessary, delete the additional series from the legend

The attached is an exampl

              Attachment filename: stacked bar.xls             
Download attachment: http://www.excelforum.com/attachment.php?postid=46078
--
Message posted from http://www.ExcelForum.com

0
3/2/2004 6:53:30 AM
You can try the following:

If your values are located in A1:C10, with column A bearing the label
and columns B and C containing the series to be stacked, then ad
column D (Total), where you type =sum(Bx:Cx) to sum the value columns.

Create a staked bar graph for A1:D10
Click on data series corresponding to column D - Format Data Seires
Axis, and choose secondary axis

Now add a data label to this series - with value
Choose the data series again ->Format Data Series->Patterns ->Set lin
and area to none.

Clear the secondary axis and teh legend entry.

- Leni

--
Message posted from http://www.ExcelForum.com

0
3/2/2004 6:57:24 AM
Hi Anne -

To embellish Nicky's and Lenin's solutions to allow the axis scaling to 
work automatically, we'll use one more column. Let A be the category 
labels, B and C be the stacking values, D be a column of zeros, and E be 
the sum of B and C. Make your stacked bar chart with A:D, then use Rob 
Bovey's Chart Labeler or John Walkenbach's Chart Tools to add the data 
in column E as data labels to the series of zeros in column D.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Anne wrote:
> Does anyone know how I can get excel to display the total 
> in a stacked bar chart?  I tried the solution that 
> someone posted for a stacked column chart, but that 
> doesn't seem to work with stacked bars.  The third 
> series, representing the total, just keeps plotting 
> itself vertically while the other two series plot 
> horizontally.  The result, as you can imagine, is a mess 
> and not what I was going for at all.
> 
> Thanks, Anne   

0
jonxlmvpNO (4558)
3/2/2004 1:25:06 PM
Reply:

Similar Artilces:

column totals on stacked column chart
Hi. Is there a good way to get the column totals on a stacked column chart to appear above the columns? All I have gotten so far is the individual pieces of the column to show their values inside of the column, but the user wants the column total, above the column. Thanks. Mark Hi, Assuming your chart is a 2d stacked column you could use dummy data series plotted on the secondary axis to display the total. Create yourself a helper column on your worksheet that sums the values of each column. Add this to the chart. At first it will be an extra portion on each stack. Select the serie...

i need to calculate the total time from a start time to and end t.
-- bob =end_time - start_time and format as time -- HTH RP (remove nothere from the email address if mailing direct) "Bob" <Bob@discussions.microsoft.com> wrote in message news:78B8ECC1-531D-49B9-A423-DAB674C8AF51@microsoft.com... > > -- > bob ...

Keeping track of and totaling hours worked
I keep track of my daily work hours using Excel. I enter starting time in one column and ending time in the next. I would like for the next column to calculate the total hours worked and rounded to the next 1/10th of an hour. I know it can be done, but I haven�t figured it out yet. Any help would be very much appreciated. Popeye -- Popeye ------------------------------------------------------------------------ Popeye's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30454 View this thread: http://www.excelforum.com/showthread.php?threadid=501284 Hi Popeye 1/1...

Crosstab query totals
I have Query1 that returns 2 fields: CatID Step1Decision CatID can be 01 thru 29 Step1Decision can be N,X,P,D,R Query1: SELECT qryGrievances.CatID, qryGrievances.Step1Decision FROM qryGrievances, qryStartEnd Query2 uses Query1 joined to qryCategories to return all the categories and their description plus those in Query1 Query2: SELECT qryCategories.CatID, qryCategories.CatDesc, Query1.Step1Decision FROM qryCategories LEFT JOIN Query1 ON qryCategories.CatID = Query1.CatID Query3 uses Query2 in a crosstab so the Step1Decision (N,X,P,D,R) become column headings and cate...

how do I get a cummulative total in excel
I have a spreadsheet where I add daily totals. One of my columns is for a percentage for the day. At the bottom of the page I need a month to date average percentage. How can I do this. I have tried....(i.e. =AVERAGE (L4:L34)). This is giving me an average of all 31 days of the month instead of just the 5 that have accumulated. Can you help me fix this? Excel ignores blank cells when averaging so if you have only 5 cells with data, the average for L4:L34 will be only for those 5 cells. Gord Dibben MS Excel MVP On Wed, 6 Jan 2010 14:36:02 -0800, luvkoffee <luvkoffee@d...

Assigning running totals to one cell
Hi. I track numbers on a daily basis over a period of a month (keeping running list of the numbers). Generally it is a smaller number subtracted from a larger number an that result is then subtracted from the next day's value, then tha value from the next days value, etc. etc. As I generate a new number for each new day, I want that new value t be assigned to the same cell in a daily report that I have to submit. Currently I have 31 copies of the same report (representing each day o the month) that corrilate with each days result. But...there must be an easier way to do this. T...

Totaling and sorting by criteria
I built a report, with the headings PO, Description, Qty, Unit Price, and Extension (which is unit price x Qty.) The report is built in the query to sort by PO, which it does. I need to make it so a text box adds and totals the "Extension" value for records matching the same PO. Thanks ...

accounts receivable summary report, need totals
i am using RMS 2.0. The Accounts Receivable - Summary report is great, but it doesn't have totals. I would like to have totals for 1-30, 31-60, 61-90, 90+, Total Any idea how I can do this ? I have pasted the code for the report. //--- Report Summary --- // Begin ReportSummary ReportType = reporttypeCustomer ReportTitle = "Accounts Receivable (Summary) " PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = True OutLineMode = True Groups = 0 GroupDescription = "Total" DisplayLogo = True L...

Total from a Query to a Form
Hello, I have several queries that have count totals from certain fields in a table. I would like to be able to insert those totals (just the numbers) on certain places in another form for a manager's summary view. How can I make this possible. Thanks in advance. On Sep 25, 8:34 am, Chris <chris.ho...@gmail.com> wrote: > Hello, > > I have several queries that have count totals from certain fields in a > table. I would like to be able to insert those totals (just the > numbers) on certain places in another form for a manager's summary > view. How can I ma...

Sales line totals not adding up
Have a weird situation. We have a sales invoice that has an incorrect total. I added the totals of the line items and I am off. Has anyone seen this before? Thanks. Marisol, While entering the transaction, something would have happened to this transaction. Just get the users log out, run check links on Sales Transaction Work Tables. Then, Go to the transaction,see if it's corrected, if not, you can delete the transaction and re-enter the same. -- Thanks Janakiram M.P. MCP-GP http://janakirammp.blogspot.com "Marisol" wrote: > Have a weird situation. We have a sale...

Access
Hi I have designed a simple database to collect sales information about manufacturing Kitchens and wardrobes. On my main form (created from my main sales table) I have 2 subforms as follows: Type Qty Price Total 1 2 2000 4000 2 2 4000 8000 3 2 3500 7000 That is the 1st subform I created and the total comes from Qty x Price. The 2nd subform is the same with wardrobe prices - total is the same setup. On my main form I want to total the totals for the first subform and then total the totals from the second subform an...

Pivot Tables: Don't show data for detail, but still show subtotals
Hi there, Is it possible to show a pivot table with detail but have the data only show for the subtotals? I am trying to create a report that combines a staff list from HR to a budget list from Finance. It needs to show staff in post vs Budget. But the budget only applies to the department/Band combinations - not individuals. I have been asked to include names of people in the report so managers can see the breakdown of each person's WTE against the budget - here's an example of what I am thinking it might look like: Dept Band Position Name ...

sort by month with a monthly total
Hi, What i'm wanting to do is to set a spreadsheet that sorts a range of rows by date order, but then at the end of each month have a total line under the last entry for that month before going straight on the first entry of the next month. I can use a command button to sort the rows, but how do you keep the totals rows in the same position (after each month). and i must be able to add new rows for previous months aswell Hope you can help, Thanks in adavnce. sdg, You can use Data - Subtotals, with a total on a change in the month column. You should remove the subtotals before s...

Sale batch report with the total quantity sold per item
How i can setup a sale batch report with the total quantity sold per item in the given batch # range for each item Example: Batch :45 Item :464343 Qty 12 Item :678445 Qty 67 etc. here is a query that will give that result for the current day: select t.storeid, batchnumber, itemlookupcode, sum(e.quantity) qty from [transaction] t join transactionentry e on t.storeid=e.storeid and e.transactionnumber=t.transactionnumber join item on itemid=item.id where time>convert(char,getdate(),101) group by t.storeid, batchnumber, itemlookupcode order by t.storeid, batchnumber, itemloo...

I need to show the last number in a rows in the total pending row
I have a column where I list pending cases each month and I need to show the last number in the total pending cell at the bottom of the row. I can use an offset fomula which works if the pending cases begin in row 1. However, I am not able to get it to work if the pending cases begin in rows 4 or 5 as an example. Any suggestion? Rows don't have a bottom.............guess you meant Column. Last number in column A =LOOKUP(99^99,A:A) Last anything in column A =LOOKUP(2,1/(A1:A65535<>""),A1:A65535) Gord Dibben MS Excel MVP On Wed, 28 Jan 2009 16:30:08 -0800, Jen S ...

Help
I'll bet this is really obvious and simple, but here goes: For months now I have been trying to track down why viewing all transactions in the register show a correct balance, and when viewing only unreconciled transactions the amount is always off by (68.89). This has spanned several months of balancing and is consistently the same amount. It's like there are invisible transactions that do not get calculated when switching from reconciled to unreconciled views. No, there is no single transaction that totals 68.89. I am using MM2002, if that helps. Thanks, -Ajay PGP key ID = 0x...

Transferring a Sum total w/o the background formula
"How do I transfer a sum total from one cell to another spreadsheet w/o transferring the formulas behind it? When I paste the sum I usually get !ref because the formulas are related to data in the original spreadsheet. Max Copy the cell but then use PasteSpecial | Values to get the value rather than the formula Regards Trevor "Max in NJ" <anonymous@discussions.microsoft.com> wrote in message news:BA3C77DF-5715-4E62-BA6E-62CDB083FC2E@microsoft.com... > "How do I transfer a sum total from one cell to another spreadsheet w/o transferring the formulas behind it?...

Total from Header
I have a report that has companys with items sold. The report shows the company name as a header and then items in the detail section. There are 20 companies and I want the report to show the number of companies in the footer but when I try to do a sum it totals based on the details since the query shows the company name with each detail. Is there a way to just show a total of the companies from the header? Add a text box either on Report Header or Page Footer and enter the following formula on its Control Course =Count([NameOfTheFiledContainingCompanyNames]) hth Adnan -- Please pos...

The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks!
The code below worked for a few days and just totally stopped working. What can I do to make sure it continues to work? Thanks! ----- Private Sub Worksheet_Change(ByVal Target As Range) Dim C As Range, D As Range Set D = Intersect(Range("A:A"), Target) If D Is Nothing Then Exit Sub For Each C In D On Error Resume Next Target.Offset(0, 6).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column E Target.Offset(0, 7).FormulaR1C1 = "=RC[-2]&RC[-5]" ' - For Column F Target.Offset(0, 9).FormulaR1C1 = "=IF(ISNA(VLOOKUP(RC7,I.O.! R2C1:R5...

Viewing Query Total in Form
I have a form where I input purchases for customers. I also have a query that I use to make a report as a reciept to show the totals for items input. I have tried to make a text box to show the information from the total field in the query. However when I open the form it says "name?" is thre anyway to show the total from the query? Thank you for your help Chase W. ...

Detailed Sales Report at Head quarters. On hand items shows total orginazation quanity not store quanitiy.
Hello, I am trying to customize the Detailed sales report to show the Onhand quantity for each store. When I select the "On Hand" Column it shows the total for the entire orginization. I want something that reflects just the Quanitiy On Hand at each location. I noticed that the Item -> Snapshot store Quanity list does this. I attempted to move the chuck of code setting the DynamicItem in the snapshot report over to the Detailed Sales Report but am recieving errors. I am not sure if the DynamicItem is support in reporttype = salesreport. I am in the process of registering...

Error Total sales report hour sales
Hi all, Got a hourly sales report from the microsoft customer source which gives a wrong total sales because it is selecting more items than sold during a certain time. Can anybody tell me what needs to be change in the qrp. report below: Jeff2 //--- Report Summary --- // Begin ReportSummary ReportType = reporttypeSales ReportTitle = "Detailed Sales Report by hour" PageOrientation = pageorientationLandscape WordWrap = False ShowDateTimePicker = False OutLineMode = True Groups = 2 GroupDescription = "" DisplayLogo = True LogoFileName = &q...

Help with Sales Totals
Help with Sales Totals I have a sales log for all our sales people, about 25 people. In it we list by row the Date, the Sales Rep, the Part Number and the Sale Amount. This is a running log that starts at the first of the year and is closed out at the end of the year. For example: The sales log says Date Rep Part $ 4/5 Steve B 10.00 4/5 Brian B 15.00 4/5 Steve A 5.00 4/5 Steve A 10.00 4/6 Brian B 15.00 I can not change this log or add anything to it. Each day, on a separate sheet I have to log the Total Amount for each Sales Rep by Part. What I would like to be able to do is have a se...

Different Operators in Grand Totals of Pivot Tables
Hello all, Is [How is] it possible to display a sum in the column grand total and an average in the row grand total of a pivot table? Hi, Show us some sample of your pivot table layout - the fields and their order. For example, if you have two field you can run Averages for one and Sums for the other. But there are other alternatives using Calculated Fields and Items. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "DF" wrote: > Hello all, > Is [How is] it possible to display a sum in the column grand total and > an average in the row gra...

Excel 2K
Hi, I'm stuck. I know very little about Excel but am trudging along. I've got a sheet set up to track times spent flying. I've it to figure time in the air just fine when I put in the start and stop times, but at the bottom I want it to total the actual air time. My problem is that after it tops 24 hours it goes back to zero. Is there a way to get it to count days as well? For instance if I've got listings that indicate 5 eight hour flights, the total at the bottom shows at 8. I'd like it to indicate 1 day-8 hours-0 minutes. Thankz. Anchor's aweigh, Th...