Pivot Table Grouping By Date - Formatting Inconsistency

Apr 18, 2014

It's a given that formatting doesn't alter underlying values in all areas of Excel, merely changing the way cells look. But in the attached I show two pivot tables using exactly the same data with the sole exception that the first one has the date numbers formatted as numbers whereas the 2nd PT has the date numbers formatted as dates.

With the 2nd PT it will accept the Group by month functionality whereas the 1st PT won't.

On the face of it PTs seem to take more notice of the way a number is formatted when it comes to grouping rather than treating the number as a date which it can group.

View 1 Replies


ADVERTISEMENT

Pivot Table Grouping - Range To Show Last Date Only

Oct 23, 2013

Have a simple Pivot table that i have grouped in 14 day buckets.

Currently, the date range looks like this:

1/21/2013 - 2/3/2013

However, I want the range to show the 'last' date only.

Is there a simple way to do this? I just can not see it in the format cells menu...

View 2 Replies View Related

Pivot Table - Grouping Items (Date) In Report Filter?

Nov 11, 2013

I have a pivot table with the following items;

Report Filter = Project Names & Dates (filtered on 2 fields)

then the pivot table of data shows
Dept, Sum of Hours, Sum of Total ($), Sum of Days

When you click on the drop down to adjust the filter for the Date it gives you the whole list of dates, day by day as it is in the source data.

Is there a way to make it in the filter by month and year, the way it would in an ordinary filter. So if I wanted to have the pivot table show only the values with an October date I can, rather than having to deselect all and then individually check the boxes for the 1st through to the 31st of October.?

View 2 Replies View Related

Using VBA For Pivot Table Grouping

Aug 7, 2006

i have try to find the VBA source to group a range of value (not manual group it)
say example

1-10, 11-20, 21-30 and etc..

i have try to using macro to record my grouping step to a pivot table as below

Selection.Group Start:=0, End:=30, By:=10

but anyway, i think this is not a good idea.

any other suitable VBA code rather than the above code for the pivot table grouping?

View 4 Replies View Related

Pivot Table Grouping

Feb 8, 2007

I have a large spreadsheet, which has a pivot table, this is grouped by Date (Months & Years). However whenever I refresh the table the grouping is removed. I try to re group the date, but I get a message saying "Cannot Group that selection"

View 2 Replies View Related

Sub Grouping In Pivot Table

Sep 15, 2007

The attached is the format of a pivot table i have made from a data. MY primary group is the REGION inside the region i want to group as per the LOCATION HEAD.

This report automatically sorts as per the location id.

How is it possible under region i want to group according to the location head and total. i DONT WANT TO CHANGE THE SEQUENCE OF THE COLUMS AND THE FORMATTING

View 9 Replies View Related

Pivot Table Dynamic Grouping

Sep 15, 2014

I have a table with outstanding days for Funds owed by multiple accounts which dynamically changes everyday I am looking to group this table by >5days, 6 to 14 days , 16 to 40 days ,>40 to 90days and >90days consistently even though the table will change dynamically every day..

View 4 Replies View Related

Grouping Data In A Pivot Table

Oct 31, 2008

from the Forum on building a macro and was kindly told that a Pivot Table would do the job better, which it does. However, my table has a column of several dates, I was wondering the best way to group the dates into months rather than showing the full date, eg, September instead of 16/09/08 or August instead of 01/08/08.

View 4 Replies View Related

Pivot Table Disable Grouping

Jan 3, 2009

I have the attached Pivot table. When i create this pivot table, it groups some of the "A# and Name"s.

e.g.
"A277003 / VICTORIA WILSON"
"A444302 / YASEMIN KELES"

Is there for the pivot table to not group by the A# & Name and to list the A# and names on each row...

Please see Output sheet to see exactly how I would like it?... But i would like this to be done in the pivot table so i can create a macro..... to do this..

View 7 Replies View Related

Grouping Dates In Pivot Table

Jan 9, 2009

I am having issues grouping dates by month in the attached example. The date field is formatted as text. When I attempt to group in the pivot table, I am not given the grouping dialog box. I can't figure out why.

View 8 Replies View Related

Grouping Patients By Age In A Pivot Table

Jan 12, 2009

I know how to get the pivot table in the my database to group patients by age. However it groups them like this:5-14
15-24
25-34
35-44
45-54
55-64
65-74

Medically it doesn't make sense to group pediatric patients with adolescents and adolescents with adults. What I would like to do is to get the pivot table to group patients the way I want them. Something like this:5-12
13-18
19-25
26-35
36-45
46-55
56-65
66-75
76-85

Is there a way to get a pivot table to do this? Or is there another solution?

View 12 Replies View Related

Pivot Table - Grouping Months

Jan 5, 2012

I'm trying to create a pivot table to figure out categories of expenses over the last year. The problem is when i create this pivot table on the horizontal i see all the days of the year, as opposed to categorized by month. I've tried to 'group' the months together but not very successfully. HOW to do this grouping elegantly?

I've attached 2 screenshots.

Pic 1 - Original State of the table with all the days on the horizontal

Pic 2 - an attempt to group by month, gone wrong

View 1 Replies View Related

Grouping Data In Pivot Table

Mar 28, 2012

I'm looking for a quick way of counting the proportion of records in certain number ranges e.g.

Number of records in range 1 to 50, 51-100, 101-500 etc.

i have a pivot table but I'm just not sure how to create these groupings. Is a pivot the right way to do it - or should I be doing something else with the source data.

View 4 Replies View Related

Pivot Table Not Grouping Like Items?

May 24, 2012

I've created a pivot table that is not grouping "like" items. I have verified all fields are numbers using the =isnumber() formula. All items present with the "true" value. Just in case, I tried doing text to columns and refreshing the data but that did not work either. All items are formatted the same. All data fields have values.

View 1 Replies View Related

Excel 2010 :: Pivot Table Grouping

Apr 28, 2014

I had a workbook in Excel 2003 that i just moved to 2010. In the 2003 file I had 2 pivot tables, one each on a worksheet, looking at the same data, just grouped differently. One yearly, one quarterly.

Now in the 2010 workbook whenever I change the grouping on pivot table, the other one changes also. It's like they are linked together or something.

View 3 Replies View Related

Pivot Table / Chart Not Grouping Same Entries

Feb 4, 2010

I have recently noticed a problem with a pivot table and assoicated pivot chart. The table has numerous columns from date, location and one entitled route. The route column has a value which is either a number or 'other'. The formatting of the column has been set to general (although the same problem was present under text). The problem is as follows:

When a new row of data is entered where the route is the same value as previous rows it should be grouped together. However when I go into my pivot table and look at the options for route I find the same value repeated twice. ie a route value of 155 will be listed once at the top and then lower down the list. A temporary fix is to rename all values of 155 to x155 and then back to 155. This then has everything grouped again under one value in the pivot table. As soon as new entries are added though the problem repeats itself. Does anyone know why this might be?

View 4 Replies View Related

Monthly Totals *without* Pivot-table Grouping

May 20, 2008

I have an expense ledger that looks essentially like this but spans seven months and counting:

HTML 18-Apr-08pizza$10.00Food
14-Apr-08book$12.50School
13-Apr-08milk $3.99Food
13-Apr-08soap $3.99Grooming
I have been using pivot tables with great success to summarize my data according to categories, payment methods, etc. But now that I want to track monthly spending for each category, it is not working out. Grouping the date fields doesn't work for me for the following reasons:
1. After grouping, the pivot table treats Jan as the oldest month for sorting purposes even though it isn't so in my data set.
2. Based on #1, I believe that the pivot table’s monthly grouping works like the month() function, which returns the month without the year attached. This will not work for my data, which will likely span more than a year eventually.
3. When I choose to group the date fields in one pivot table, they also get grouped in other tables where such grouping is undesirable.
Can I use sumif() or sumproduct() in conjunction with a pivot table or some other way to tabulate my monthly totals?

View 9 Replies View Related

Grouping Dates Into Weeks Without Using A Pivot Table

Oct 16, 2009

I have a large spreadsheet which lists individual dates over two years and circulation figures next to each date. Sometimes there are two entries for a particular date e.g. 1/10/08 there were 150 readers of magazine A and 200 readers of magazine B. When displaying this on a graph it give a bar for each day, whereas I would like a bar for each week.

Is there a formula to convert the individual dates into weeks and then total the circulation figures for that week?

Column A = Dates
Column B = Circulation Figures

View 9 Replies View Related

Multi Level Grouping For Pivot Table

Dec 23, 2008

I am looking for some assistance to make pivot tables with multiple data values against a control item. A sample excel file is attached herein with. I am quite new to pivot tables was unable to find out a solution self.

View 2 Replies View Related

Excel 2007 :: Pivot Table Grouping And Filtering

Sep 10, 2013

Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.

My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.

View 2 Replies View Related

Grouping Dates Not Working On Pivot Table - ERROR

May 28, 2014

I am trying to group some dates in a pivot table.

Unfortunately I receive an error saying it can't group the selection.

I have done "text to columns" and made the cells - (date values) and I have had no luck.

As I am at work I can't use the HTML genie because it won't install but I have uploaded a dummy file to my dropbox which available from here [URL] .......

I pull this data from an Oracle POWER PIVOT and always have trouble grouping the dates when they come from Power Pivot. Currently working as analyst so figuring this out would make life so much easier as I could group by months etc.

View 3 Replies View Related

Excel 2007 :: Pivot Table - Daily And Monthly Field Grouping

Oct 17, 2011

I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.

I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.

Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?

View 1 Replies View Related

Change The Date On One Of The Pivot Table And Pivot Table Match

Apr 29, 2006

I have data that develops 3 to 4 pivot table each day. I would like to know if there is a way to change the date on one of the pivot table and have the other pivot tables date change to match with the first pivot table. At this time I am going to all 3 or 4 pivot table to select the correct date. The date is in the page position of the pivot table. I have attached a small sample of the data and the pivot tables.

View 2 Replies View Related

Pivot Table Formatting With Vba

Feb 13, 2007

A report is written in a massive text file, and i've got my code to shrink it down, tidy it up and spit out a nice pivot table which my manager wants to be pretty colours (colors for US spelling). So far i've go this (which i recorded lazily):

ActiveSheet.PivotTables("PivotTable2").PivotSelect "Division[Numerical]", _
xlDataAndLabel
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With

Now - what happens if there are no entries for the "Numerical" division? ERROR!!!
How can i get some kind of "IF this division is present - color 35 please, if not, carry on" command? I have experimented til all the cows have come home, but i still end up with errors and freezing.

View 2 Replies View Related

Change Formatting Of Pivot Table?

Jun 19, 2014

Pivot Tables. I've created a very simple one where my Row Lables are Salesperson then date and the second column is Sales. It looks "backwards" to me, because each salesperson's total sales for all dates combined is ABOVE the data by date. To make it worse, there's a line below the name, which looks like it's separating the name from the next few lines! With the next salesperson's name in the same "block" as the previous salesperson's details, it's very confusing! It would make more sense to me to have each salesperson's total be at the BOTTOM of their section!

View 6 Replies View Related

Conditional Formatting Of Pivot Table Through VB

Jun 22, 2014

writing VB code to conditionally-format the pivot table shown here (I am not inclined to use the Excel Conditional formatting option as it loses the formatting when refreshing the pivot table)

Excel_screenshot1.png

I need a VB macro that reads each value in a Pivot table .. starting with the Col1, Row1 of the Pivot table .

It then matches the value in Col 1, Row1 to the Baseline value for Col 1 that is specified in (Yellow ) .

Note : The Baseline values are not part of the Pivot table area

If the value in in the Col 1, Row1 is less than the baseline value for that column at the top ,it formats the font color of that pivot table cell (say to red) It then moves to read the value in Col 1, Row2 and does the same check .

Once it completes the check on all values in Col 1, it does the same with Col 2 (where the values are compared to the baseline value for Col 2) ... and so on until all the colums of the PIvor table are validated in the same manner .

View 1 Replies View Related

How Can I RETAIN Formatting In A Pivot Table

Mar 21, 2007

My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.

I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.

I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?

I'm using Excel 2003 on Windows 2000 Professional.

View 9 Replies View Related

Pivot Table Formatting Of Month

May 8, 2007

I am having difficulty with the pivot table I created in that the name of the month is showing the serial number. In my spreadsheet, the data I have is:

cell B3 = 20070501
cell P3 = 05 { formula =MID(B3,5,2) } {}*not an array formula
cell Q3 =May {formula =DATE(2000,P3,1) }
custom formatting in cell Q3 with MMMM to retreive name of month

In my pivot table, I would like the name of the month "May" to be displayed but 5/1/2000 is appearing. What am I doing wrong? Must I always format my data in the pivot table to return the name of the month? Perhaps someone in the Excel spectrum knows of a better solution.

Thanks to anyone who can guide me to sanity.

View 14 Replies View Related

Pivot Table Number Formatting

Dec 5, 2009

Whenever I make one with numbers, I get formatting for a number as below

1234567.78(no commas and 2 decimal places)

I normally want the formatting as 1,234,568 (comma inserted, no decimals,), (the last digit is changed just becasue of round off, other wise number in both cases is same).

Now I can double click the field, goto number--> number and then apply this formatting.

My question is whether there is a setting in excel somewhere so that this formatting will come as default (after I create the pivot table each time)?

View 6 Replies View Related

Formatting Pivot Table Fields

Jul 20, 2009

I'm trying to loop through all pivot fields in a table and change the format to be 0 decimals and comma seperated.

Here's my

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved