2 Questions on Pivot Tables - 1 tip

Any assistance appriciated. (Excel 2003)

1)    I have a simple Pivot table showing 6 groups with their totals and a 
subtotal eg:
        America    10
        UK           15
        Oz             22  etc.
    Grand Total  125

I want to Pivot Graph this data, no problems.   I want however an extra line 
to show the total on the Secondary Axis.  I have almost got it by placing 
the column in a second time as a Running Total and then as a Line on my 
chart, hiding all the values until the last point.

However I would like if possible the Total as a horizontal line across the 
chart?

2)  I have come across Grouping Dates and the inability to Group if there 
are blanks.  I thaught about generating an invalid date eg 01/01/1900, to 
use that and ignore it later, I thaught about sorting and inserting a row to 
knock the blanks out of my pivot using a macro.....

Then a thaught, I tried linking the data into Access 2003 and writing a 
Pivot Chart query and it worked.  I am VERY happy to discover this, only a 
simple questions, was it possible in Excel....?

Roger Knowles
MOS Master all Versions
England. 


0
2/25/2005 10:14:34 PM
excel 39879 articles. 2 followers. Follow

0 Replies
592 Views

Similar Articles

[PageSpeed] 26

Reply:

Similar Artilces:

Sorting / Pivot Table
Greetings. I'm using Excel 2007 (beta 2), and it's a fine product that I'm slowly learning how to use. Sorry to keep bothering the experts with so many questions. I was wondering if the following can be done, because I haven't found the answer in any help file. Let's say I have a column called HEB and another column called ENG (the English translation of the Hebrew words in HEB). Let's say I translated a certain Hebrew word in row 20055 (there are over 400000 rows in the spreadsheet) as "produce." What I want to do is check and see how I translate...

Line Column on 2 axes that shows stacked colums
I am able to creat a line column graph on 2 axes. That shows $ as columns and % as a line. However I wnat the colunms to be stacked colums. Can this be done? Instead of using the built-in combination charts, you can create your own. Jon Peltier has instructions on his web site: http://www.peltiertech.com/Excel/Charts/ComboChart1.html To change your chart: Select the chart From the Chart menu, choose Chart Type Select the Column chart type Select the Stacked column subtype Click OK In the chart, select the series that you want as a line From the Chart menu, choose Chart Type Se...

Can I set a filter on the source data for a pivot table
I want to exclude certain records from a pivot table... i.e., Buy/Sell Quantity B 5,000,000.00 S 5,000,000.00 B 1,000,000.00 B 10,000,000.00 S 7,000,000.00 B 500,000,000.00 S 200,000,000.00 Only inlcude "B" records. Can a pivot table be set up to filter? Thanks ChrisP You could add the Buy/Sell field to the page area of the pivot table, and select B from the dropdown list there. ChrisP wrote: > I want to exclude certain records from a pivot table... i.e., > > Buy/Sell Quantity > B 5,000,000.00 > S 5,000,000.00 > B 1,000,000.00 > B 10,000,000.00 &...

How to put Time or Date Only in Cell 1) Time HHMMSS 2) Date YYYYMMDD
I need to have a cell = the current time in the format HHMMSS And when I export as Comma Delimited or Fixed Length Ascii, I need for it to remain in that format. Ditto for Date as YYYYMMDD I've played around with both =TIME and =DATE but no luck. Can anyone tell me how to do this? AND, once the forumula is in the cell... how can I get it to update to the current time or date? thanks for any help I formatted my times as hhmmss and my dates as yyyymmdd and then did a file|saveAs. I chose the comma separated values (*.csv) and I got what you wanted. I opened it in notepad to ver...

Running Average #2
How can I create a formula that creates a running average? Example... I have four column headings: Date, Weekly Pay, Weekly Average, Yearly Total. Each week I enter how much I'm paid in the Weekly Pay. The formula under Yearly Total is =IF(B2="","",N(D1)+B2), then I copy the formula down. The Weekly Average would simply be the grand total divided by the number of weeks. What would the formula look like? will this do? =AVERAGE($C$2:C2) copied down -- Don Guillett SalesAid Software dguillett1@austin.rr.com "John Persico" <replytogroup@nospam.com>...

Uploading #2
I have a website developed in Publisher 2003 and want to upload it onto a server. They use FrontPage 2000. Is there an easy way to have these two systems talk to each other? I think you misunderstood your webhost. They probably said that they support Front Page Server Extensions. You can upload using HTTP protocol with the FPSE or FTP protocol if you aren't using a form on your site. Reference the uploading articles here: http://msmvps.com/blogs/dbartosik/archive/category/1921.aspx And perhaps the article: Publisher web publication forms 101: http://msmvps.com/blogs/dbartosik/arch...

Numpty Combo Box Questions
Hi all Just trying to get my head round these combo boxes! I seem to have got the hang of controlling the record display when the down arrow is clicked. Managed to get correct columns visible with captions etc. How do I vary the fields displayed when the combo box is inactive. Once a selection has been made and the user has left the combo box, I would like to display more than one field. Also, is it possible to sort the display order of the records by user selection. I would like the user to be able to re-order the combo box by either of two fields prior to a selection being...

Add phone, fax & email to Buyer ID table
If the buyer ID table contained the buyer's phone, fax & email, then it would be easy to add these to the PO form. If a vendor has a question, they certainly want to connect to the right person. The only workaround is to use en employee number for the buyer ID and hope the RW match is exact. ---------------- 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 Micro...

Excel Spreadsheets Reformat Question
I have a disk full of Excel files; I suspect they were created by exporting from an accounting program to Excel to share the financial information with others. Each of these files needs to be PDFd and Bates stamped for production in a legal matter. The problem is this: every single one of these Excel files is not formatted to display all of the information - in order to get a readable page, the columns need to be reformatted to display all of the information contained in each of them, then saved. There are literally hundreds of these files; would there be an easier way of gett...

Locate 1 Customer Account record by multiple names
Multiple entry lines for names on accounts. Many of our customers are same households with different last names. We would like to be able to put both names on the account and be able to locate the account by either name. ---------------- 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....

Decimal Places #2
I am trying to use the following formula to display a text version of a number: =TEXT(cell,"#.#") To show numbers in a 0.0 format. However, when I get to one figure 20.0 - it displays it as 20. rather than as 20.0 (which is how I would like it to be displayed). Does anyone know how to display the information in this format?? Try this: =TEXT(cell,"0.0") Hope this helps. Pete On Jul 25, 9:50 am, BoRed79 <BoRe...@discussions.microsoft.com> wrote: > I am trying to use the following formula to display a text version of a number: > > =TEXT(cell,"#....

After 10.1.5 update, word entoruage ok. Excel and Powerpoint crash
Hello All, Previously running Office v.x 10.1.3 and decided to download and install 10.1.4 and 10.1.5. Things seemed ok but then Excel and PowerPoint will unexpectedly quit after the splash screen and seeing the menu options. Word and Entourage work just fine. I've rebooted and also run First Aid to repair permissions. Still the same. I even reinstalled 10.1.5. Please help! This is driving me crazy! In article <_vubb.122055$mp.62781@rwcrnsc51.ops.asp.att.net>, "Tech Guy" <nospam@nospam.com> wrote: > Hello All, > > Previously running Office v.x 1...

Outlook for newsgroups #2
Is it possible to use Outlook 2003 as the application for newsgroups in stead of Outlook Express? Thx CreloX "CreloX" <crelox_bmw@hotmail.com> wrote in message news:utSpu94EGHA.524@TK2MSFTNGP09.phx.gbl... > Is it possible to use Outlook 2003 as the application for newsgroups in > stead of Outlook Express? > > Thx > CreloX No. -- John Blessing http://www.LbeHelpdesk.com - Help Desk software priced to suit all businesses http://www.room-booking-software.com - Schedule rooms & equipment bookings for your meeting/class over the web. http://www.lbetool...

Maintaining the size of the excel tables
I have been trying to insert tables from excel into my publisher file. I tried to insert them as an object and they didnt look good. I used pase sepcial and inserted them as enhanced pictures and other time as an excel sheet. However, no matter what I do I cant get these tables to look the same as they are in the excel sheet. They dont maintain the size and neither the look. Either the table looks too loose or the letters and numbers looks bigger than the orginal. Any tips or advices please ? Maybe the solution would be to create a table in Publisher, copy the cells in Excel, p...

How to delete .txt files from \Program+Files\Microsoft+SQL+Server\MSSQL.1\MSSQL\LOG
Hi All, We have SQL 2005 and would like to automate deletion of .txt files from \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. We have Maintenance plan running which creates .txt files. Any suggestions/ideas? Thanks IA On Aug 12, 10:06=A0am, hayko98 <vardan.hakop...@gmail.com> wrote: > Hi All, > We have SQL 2005 and would like to automate deletion of .txt files > from \Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG folder. We > have Maintenance plan running which =A0creates .txt files. > Any suggestions/ideas? > > Thanks IA ...

Referencing Data in a Pivot Table
I have created a pivot table which shows me total money spent in various accounting categories. I want to reference the sums this creates to subtract them from a total budget to give me a remaining total. Unfortunately, as the pivot table is updated, cell references change. This means that the total I used as say total spent on fuel may become total spent on salary. Can anyone help? You can use the GETPIVOTDATA function to extract data. Look in Excel's help for information and examples for your version of Excel. Brian wrote: > I have created a pivot table which shows me total mon...

Fringe payroll question
Hello, I want to report a life insurance premium the employer pays on behalf of the employee. This premium would appear on the W-2 (over 50K face value). I would like to set this up as a benefit, but I do not want this show up in the GL when payroll is posted. I also want to avoid having to manually edit W-2s at year-end. ideas? -- thanks Greg. You're right -- it's a fringe question. Unfortunately, unless you use a custom report, I don't see how you're going to accomplish this need. Why not have the debit and credit for the benefit hit the same account? What are ...

free / busy question
I am just wondering what the expected behaviour is when using outlook 2003 to schedule a meeting & viewing the "scheduling" tab,as far as the info that is displayed when I hover my mouse over an invitee's time block. If I hover over my own appointments, I see the details of the appointment (unless it's marked private - then I just see "private appointment". If I hover over an invitee's appointment, I do not see any details at all (no pop-up box). I am just trying to find out if this is normal behaviour, or should I be able to see details of other u...

reply to all #2
When you "reply to all" is there a way, other than deleting your name, to keep it from sending your response back to you. ...

=?iso-8859-1?Q?=A3_symbol_transposes_to_pound_sign_=28hash_=29?=
I have a sent an email showing a =A3 in front of the=20 amount. The receiver sees this as a pound sign (hash) in=20 front of the amount. What causes this and how do I=20 prevent it happening? ...

calss derived from CWnd #2
I created a customed control, the control's class is derived from CWnd. In this control class, I add a child control, a button, then, I create a dialog based application from app wizard, show the custom control in the main window. run the application, when change the custom control's size, the child button is flashing, why? if the custom control class is derived from CDialog, run application is fine. thanks james_li CWnd mostly means to hook up to Windows windows not to use in a very direct way. If you prefer a better CDialog try using ATL/WTL's (harder thought) Edernity ---...

chart question #2
I have 2 sheets: (more as time comes). Jan 05 - sheet 1 - sales problems - support problems - operations problems - finance problems Feb 05 - sheet 2 - sales problems - support problems - operations problems - finance problems I need to graph these 2 sheets with same categories but different total percentages of the month using the chart bars to show the changes for the same category (side by side) in a year... confused yet? :) can you give me a hint on how to do this and the formula if there are any. Well, if it were me, I'd probably put the data that you are graphing on the...

RMS 2.0 ER Diagram
Is there an Entity Relationship Diagram available for RMS 2.0 without purchasing it? Can one get an empty database or script of a database for review? Hi Paul... Look at this link. https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;938434 Rgds Rodrigo "Paul" wrote: > Is there an Entity Relationship Diagram available for RMS 2.0 without > purchasing it? Can one get an empty database or script of a database for > review? Rod, thanks for your reply but the link requires that I be a Customer or Partner: I need to either purchase RMS or become a Pa...

Noob question about parsing a BSTR
I have a BSTR something like this: firstHalf.secondHalf I want to break this up into two separte BSTRs using the '.' as a delimeter. So one BSTR will contain everything before the '.' and another containing everything after the '.' How do I do this? Make a CString and then use CString.SpanIncluding/Trim/Find or other related methods. --- Ajay You can use CString here Copy your BSTR in the CString by CString str (bstrYourString) then Seperate out the strings using CStrings method one way is like int idotPos = str.ReverseFind('.'); CString strFir...

NTBackup question
Good morning! Quick question regarding the use of NTBackup to backup the Information Store in both Exchange 2003 SP2 and Exchange 2007 SP1 running on Windows Server 2003. Question Number 1: I am curious as to the VSS Provider | VSS Writer used for this. Let's assume that there is nothing else on the system (think Acronis) that *might could* have a VSS Provider...so, we are talking only the "Microsoft Software Shadow Copy provider 1.0". I do the good ole "vssadmin list providers" and see what is there. I then do the "vssadmin list writers" ...