Pivot Table Month Grouping ? ? ?

Hi All

I basically have 2 data columns, the first is a date in the format dd/mm/yy
and the second are sales values for that day.

My question is this: When I create a Pivot Table from this, is there a way
to show the data grouped by month without having to add extra columns to the
data (e.g. =month(a1) =year(a1))

Many Thanks in Advance

John


0
Johns2003 (3)
10/7/2004 6:39:09 AM
excel 39879 articles. 2 followers. Follow

3 Replies
576 Views

Similar Articles

[PageSpeed] 4

Hi
in the pivot table open the pivot table menu and choose 'Group / sort'

--
Regards
Frank Kabel
Frankfurt, Germany

"John" <Johns2003@hotmail.com> schrieb im Newsbeitrag
news:4164e48c$0$15892$cc9e4d1f@news.dial.pipex.com...
> Hi All
>
> I basically have 2 data columns, the first is a date in the format
dd/mm/yy
> and the second are sales values for that day.
>
> My question is this: When I create a Pivot Table from this, is there
a way
> to show the data grouped by month without having to add extra columns
to the
> data (e.g. =month(a1) =year(a1))
>
> Many Thanks in Advance
>
> John
>
>

0
frank.kabel (11126)
10/7/2004 7:05:28 AM
Add the date field to the row area of the pivot table
Right-click on the Date field button
Choose 'Group and Show Detail' > Group
Select Years and Months
Click OK

John wrote:
> I basically have 2 data columns, the first is a date in the format dd/mm/yy
> and the second are sales values for that day.
> 
> My question is this: When I create a Pivot Table from this, is there a way
> to show the data grouped by month without having to add extra columns to the
> data (e.g. =month(a1) =year(a1))

-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
10/7/2004 11:58:08 AM
Thanks Guys !

That Worked a Treat !!!

Cheers ! ! ! !


"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:41652F50.3090402@contexturesXSPAM.com...
> Add the date field to the row area of the pivot table
> Right-click on the Date field button
> Choose 'Group and Show Detail' > Group
> Select Years and Months
> Click OK
>
> John wrote:
> > I basically have 2 data columns, the first is a date in the format
dd/mm/yy
> > and the second are sales values for that day.
> >
> > My question is this: When I create a Pivot Table from this, is there a
way
> > to show the data grouped by month without having to add extra columns to
the
> > data (e.g. =month(a1) =year(a1))
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
Johns2003 (3)
10/7/2004 6:46:36 PM
Reply:

Similar Artilces:

Summarising tables data
Hi i have about 10 sheets and in each sheet there is lot of info but from Row 17 to 48 there is a table (columns B till M) row 17 and 18 are headers and 19 to 48 are the values to be filled. in all sheets its the same case i want all these data to be clubbed into one sheet (summary sheet). how can i do this, just want data to be placed Something like this Sub consolidateem() For Each sh In Sheets If sh.Name <> "Sheet1" Then With Sheets("sheet1") dlr = .Cells(Rows.Count, "B").End(xlUp).Row + 1 sh.Range("b19:m48").Copy .Cells(dlr, "b"...

Hidden Field in Datasheet view for a table
Hello All, when I go to Table Design, there is a column named Field 1 (it is text). When I switch to Datasheet View, it becomes hidden. Any ideas on how to solve the problem? Thanks in advance Sam Berry "Hidden" as in zero-width, or "hidden" as in "off-screen"? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is ...

excel to make the days cary over month to month automaticly
I WOULD LIKE EXCEL TO IMPUT THE DATES OF CALEDAR IN THE EXAM:A CULUM IN SPRED SHET 1 THEN CARY IT TO THE SPREDSHET2 THE NEXT MONTH AND SO ON Re-post with a better description after using a spell-checker. Also drop the ALL CAPS please. Gord Dibben MS Excel MVP On Sat, 19 Apr 2008 04:07:00 -0700, GARY <GARY@discussions.microsoft.com> wrote: >I WOULD LIKE EXCEL TO IMPUT THE DATES OF CALEDAR IN THE EXAM:A CULUM IN SPRED >SHET 1 THEN CARY IT TO THE SPREDSHET2 THE NEXT MONTH AND SO ON ...

converting word tables to excel #2
I have a document that describes a new piece of software. The cost for each feature is described in a word table. I would like to extract these tables (with a makro?) into an excel sheet automatically. Any ideas anybody? Eckard If this is a one-time task, you can open the Word document, copy the table, and paste it into Excel. Eckard Buchner wrote: > I have a document that describes a new piece of software. The cost for each > feature is described in a word table. I would like to extract these tables > (with a makro?) into an excel sheet automatically. Any ideas anybody? -- De...

Routing Groups and multipe sites/domains
Can Exchange servers from a domain (DomainA) in a site (Site 2) be placed in the same routing group as Exchange servers from (DomainB) in the same site (Site 2)? The purpose to consolidate (Domain B) into (Domain A) within (Site 2), so DomainA spans Site 1 and Site 2. There are Exchange servers for (Domain A) in (Site 1) already. Are there any potential drawbacks that would steer one towards creating a new routing group and managing another connector during the consolidation process? Thank You. Tom <Tom@discussions.microsoft.com> wrote: >Can Exchange servers from a domain (Do...

Save Custom Table/Report?
Hello, I have a project file that I open as Read Only from a Sharepoint directory. I have a custom table and report that I need to regenerate every time I open the file. Is this because I have it opened as Read Only? Is there a way for me to save a custom table and/or report locally, so that I can apply it to the shared file I open? Thanks, WtS Wonko -- The next time you open the file, regenerate the custom Table and custom Report, then click Tools - Organizer and copy the Table and Report into your Global.mpt file. This will make the custom Table and Report visible...

store the results of a form calculation in a table field
I have read the replies to other similar queries and understand the 'whys' of not storing calculated data. I may be too narrow in my thinking, but can't see any other way of achieving what I need: I am storing geographical (Latitude and Longitude) coordinate data, in the format Degrees, Minutes, Seconds (DMS) with each in its own field. Later in the process I am converting from DMS to decimal using a calculated field in a form. All good so far. For data integrity purposes, I need to be able to prove that each entry is absolutely unique. What I was trying to do to achiev...

how do I show filter information on a pivot chart in excel2007?
In the previous version of Excel that I used you had the option to show Pivot Chart buttons which detailled how the chart had been filter. I can't find a replacement for this in 2007 so if someone prints the chart it isn't comprehensive in that you can't tell how it's been filtered. I create a lot of these for less experienced users and this info can be critical since descisions are made based on these charts. Can anyone help please? -- ATB Russ Hi, See the reply to my question about the same issue (added 16/9). That works. You just need to modify the macro for every s...

Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly. I have a spreadhseet: Date Data 2/1/99 1.15 9/1/99 2.42 16/1/99 1.24 through to..... 25/2/05 3.54 How can i calculate the average of every month between 1999 and 2005? In other words, i need something which looks up all the weeks in a particular month (ie Jan 2001) and averages them out into a table: 1999 2000 ......... 2005 Jan 2.45 6.42 Feb 3.64 etc etc. I was thinking vlookup, but don't know how to average in a vlookup. I would ...

How to have a date range equal a particular month
What is the expression for converting multiple dates mm/dd/yyyy to the coinsiding month? It is according to how you are going to use the data but try this -- Format([YourField], "yyyymm") This will give you 200711 for this month. Format([YourField], "mmmm yyyy") This will give you November 2007 for this month. -- KARL DEWEY Build a little - Test a little "tvillare" wrote: > What is the expression for converting multiple dates mm/dd/yyyy to the > coinsiding month? Format(DateField, "yyyy-mm") as stri...

Pivot Table Integrity Problems
I have built a spreadsheet and several pivot tables. Each pivot table is based on the first table to save memory. At least twice a week when I try to open the file I get a message "Unable to read". When the file is repaired I get the following results; was discarded due to integrity problems, on every table I have. I am currently using Excel 2000. Any ideas on how to fix this would be greatly appreciated. Scott ...

When refreshed, some Pivot Table columns retain formatting, some d
I have not had trouble with retaining column widths and column heading formats (font size, word wrap, alignment) in Pivot Tabl. When refreshed, they retain formatting. Here's the weird part, when I add or insert a new data column into the Pivot Table by checking from field list, that new data column does not retain column heading formats (font size, word wrap and alignment settign) like the original columns do. It does however preserver its column width and number formatting. Why is that? How do I tell Excel to preserve column heading formats for newly added ones? I...

pivot table help #8
Hi, I have a pivot table with following fields: Product, Party, Qty, Rate, Amt Product, Party are row fields, Qty, Rate, Amt are Data fields Everything works fine in the pivot table, and Qty, Rate and Amt have their own subtotal rows. I just want the Rate to have a weighted average. Lets say Hydrochloric Acid (Product) - Party1 - Qty:4 - Rate:100 - Amt:400 - Party2 - Qty:8 - Rate:90 - Amt:720 The subtotals for this row appear as: Subtotal: Qty:12 - Rate:190 - Amt:1120 Qty:12 is correct because qty is 4 and 8 bottles that is 12 Amt:1120 is correct because 400+720 = 1120 But Rate sub...

Contact Table
I added a few custom schema field attributes in CRM within the Contact Table, now I receive an error that states that I have exceeded the SQL Server limit of 8000 bytes. I have looked at the Contact Table and there are rows that I do not need within the ContactBase table. I am looking to find out how I can remove my changes or remove the rows that I do not need. -Thanks to everyone that has responded. James, I'm afraid that there isn't a way to remove columns once they are added. Short of doing a database restore of the various DB's, you don't have many options. T...

Extracting Pivotable Selections
In a field, if you elect to only include certain items, then when you select all of those items the table displays (Multiple Items) rather than (All). Is there a way to obtain a list of the items that are included (or excluded) from the Multiple Items group? I know how to see the list, but is there a way to move that info to cells in the spreadsheet? The following code will print the page field items that aren't hidden: '============================ Sub ListFieldsVisible() 'lists visible page field items on a new worksheet Dim ws As Worksheet Dim pt As PivotTable Dim pf A...

Tables Refuse to 'Hide'
I have 10 tables (out of 20-ish) that refuse to remain hidden even after I've clicked 'Hidden' in the properties window and applied the change. They will hide, but when I close and reopen the app, they'll be visible again. Sounds weird. Whenever I encounter weird-Access-behavior, I do a compact on close (well, I actually have this option set as default on all my databases as a precaution). Not sure which version of Access you are using, but check this out: http://www.brighthub.com/computing/windows-platform/articles/25840.aspx -- Ryan--- If this info...

wrap text in a chart data table #2
Hi, Im have created a graph, which ive had no problem, but i when i click the option to "show data table" i cannot get the titles to wrap to the box, i have searched but i cannot find an option to wrap the text as i need it for a presentation. I do not want to just simply alter the font size if possible. Is there anyway of doing this? ...

cross administrative group move windows 98
We recently installed exchange 2003 in our organisation. We are now running in mixed mode with exchange 5.5. We are not able to do a cross administrative group move for mailbox of all windows 98 users. (from exchange 5.5 to exchange 2003). Any idea? Eric Lajeunesse <eric.lajeunesse@saputo.com> wrote: > We recently installed exchange 2003 in our organisation. We are now > running in mixed mode with exchange 5.5. > We are not able to do a cross administrative group move for mailbox > of all windows 98 users. (from exchange 5.5 to exchange 2003). > Any idea? Ask in th...

HOw Insert Table on Header and Footer (excel 2000)
Is there anyone who can help with my problem. I want to insert Table format on Header and footer...is this possible... it is a great pleasure if you can help me with this tnx... ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Do you mean File|page setup|Header/Footer tab? In xl2002, you can insert pictures. Maybe you could save your table as a picture and insert that. (If you're using xl2k or before, maybe you can print data that will serve as headers/footers, ...

New Google group
A new Google group has been formed for the purpose of discussing the new Visual Studio Express products. http://groups-beta.google.com/group/Visual-Studio-Express The products are currently in beta testing and include Visual Basic 2005 Express, Visual C# 2005 Express, Visual C++ 2005 Express, Visual C++ 2005 Express, Visual J# 2005 Express, Visual Web Dev 2005 Express and SQL Server 2005 Express. They can be downloaded free of charge from http://lab.msdn.microsoft.com/express/default.aspx ISTM that the MS Express beta NGs would be a better venue. -- Grace + Peace, Peter N Roth Eng...

Pivot table problem #4
Hi, I am encountering some problem with pivot table's data after i refreshed the pivot table. I have a template with grouping and setting done, however, when i paste new data into the raw data worksheet and refresh the pivot table, the grouping in my pivot table dissapear. But, when i check from the drop down box, i can see my grouping but it doesn't appear in my pivot table. Anyone know why? Dear Joanne Could it be that in the new data that you add to your base data table there are items which fall outside of the groupings you have set? I have had problems with this in the...

Group disappeared from Google Groups
This Newsgroup disappeared from Google Groups today. Hope temporarily, not permanently Never mind - but from now on to find this group in Googlegroups, have to search by its full name: microsoft.public.windows.vista.general I for all these years would just enter partial: "vista.general" and it would popup in search results, but not today. Have to enter full name. Strange In article <eqJqlJs9KHA.2248@TK2MSFTNGP05.phx.gbl>, Stan Starinski <China@stealsUSJobsPatentsSoftwareMusicVideo> wrote: > >This Newsgroup disappeared from Google Groups today...

Pivot table assistance
I have a pivot table that I update each month with monthly client information and when I hit refresh the chart, the information that is in the datasheet for March is not updated on the pivot report. Also is there a way of updating pivot charts each month without having to go in and reset the datasheet boundaries which apparently is not working correctly for me. Thanks in advance for any advice. Hi base the pivot table on a dynamic range name rather than by using cell references ... check out www.contextures.com/tiptech.html on how to create a dynamic range name and lots of other grea...

Report made of a query cross table
Hi everyone It must be very simple for you guys but i can't find it: How to make a report, made of a query cross table, but only show records that the total of all months are diferent than zero (0). Negative and positive values. That report sums the sales for each person, each months and i want to know who sell and who's not selling. Tks in advance Pedro Post the SQL of your cross tab query. It is possible that all you have to do is add a having clause to it. Generically that might look something like: TRANSFORM Sum(SomeValue) as MonthlySum SELECT Person FROM SomeT...

Pivot
I have a pivot linked to a query with name of the client in the row items and sum of sales in the data items. It is sorted by sales. I'd like to show only the top and bottom 20 people. Is there a way to do it in the pivot? ya simple de-select the items u dont wish to show! -- Hope it Helps! Jim India "Ian" <anonymous@discussions.microsoft.com> wrote in message news:1bef01c4bd16$b3766ef0$a601280a@phx.gbl... > I have a pivot linked to a query with name of the client > in the row items and sum of sales in the data items. It > is sorted by sales. I'...