Re: Pivot Tables: Pivot table layout
o.k. but the percentages shown are not correct - the figures are FAR too big.
I tried to format the cells but Excel wouldn't let me.
I tried double clicking on the Data title cell, but nothing happened.
Re: Pivot Tables: Pivot table layout
o.k. but the percentages shown are not correct - the figures are FAR too big.
I tried to format the cells but Excel wouldn't let me.
I tried double clicking on the Data title cell, but nothing happened.
Re: Pivot Tables: Pivot table layout
In the first column of my attachment (period 0) the total number of incidents was 4. The individual entries were all ones giving a percentage of 25% each. The other columns also seem to be showing the correct percentage calulations. What percentages are too large?
Re: Pivot Tables: Pivot table layout
I can see that all the percentages are without any decimal points, so 25% is shown as 2500% etc.
Re: Pivot Tables: Pivot table layout
Strange. On mycomputer (actually on two different ones. the decimal point is showing. Try formating the data differently and see what happens. It is (for me) currently formatted as a percentage with 2 decimal places.
Re: Pivot Tables: Pivot table layout
My apologies.
After my lats post, I had a funny feeling about this and so I zoomed in to 150% view and lo, the decimal point appeared! - it just didn't show up in normal view on my screen because its so tiny.
Thank you for what you have helped me with especially since I kinda hijacked the thread for my own problem. I can now continue with my project.
Best regards
Re: Pivot Tables: Pivot table layout
I have encountered another minor problem. One of my columns of data shows the month number using the MONTH operator. However, when I place it in the pivot table, I want to show the month number as the month name, i.e.Jan, Feb, etc not 1,2, etc.
I formatted the cell as Custom and then mmm, but it kept showing "Jan" whatever the month.
How can I get the date to show the month by name ?
(I have a field with a date in it, from which the MONTH command gets its data from).
Thanks for any help.
Re: Pivot Tables: Pivot table layout
To work with the Month number to get back the month name I think you will need a calulated field. However, instead why not use the date field and then group it by month. The pivot table has the date grouping capability built in (assuming the dates are Excel dates). Put the date field into the pivot table, right click, select group, and then select month (you can group on more than one of the choices if desired to get, say, quarter or year also).
Re: Pivot Tables: Pivot table layout
I've only put the date field into the pivot table and then selected the field name and then right clicked and chose group, but the computer beeped at me and said "cannot group that selection".
Also, I didn't see any grouping options, by month/quarter etc as you mentioned - where will they be shown ?
Re: Pivot Tables: Pivot table layout
The grouping will not work unless the dates are Excel dates rather than text dates. You will need to convert them to Excel dates in your database. If the data are in Excel, then just put a zero in some empty cell. Copy that cell, select the dates, and do a Paste Special with Values and Add. This will convert the text dates to Excel dates and you should then be able to group them.
Re: Pivot Tables: Pivot table layout
I don't know what you mean by 'text dates'.
The dates will be entered as day & month in numerals, so if the date was entered today (1st June), the date would be entered as 1/6. Excel would then fill in the year as well as the 2nd forward slash before the year.
The dates will ALWAYS be entered this way and I won't have any control to get the dates entered any other way.
Is this way of entering dates not o.k. for the grouping function in Excel ?
Re: Pivot Tables: Pivot table layout
I also wanted to ask about formatting Pivot Tables.
If I want to change typefaces, colours, column widths etc etc, this is all lost when for example I change the 'view' or I move data around.
Is there a way of keeping the formatting when changing a 'view' etc ?
Even a tiny change seems to lose some of the formats, like colours and cell formatting, even if the actual data underneath the lost formatting hasn't changed.
Re: Pivot Tables: Pivot table layout
If dates are entered as you say, then they should be Excel dates. I used the earlier sample workbook, replaced the Month field with the date field, and then right clicked on the dates and grouped by month quarter, and year. See the attached.
As to sticky formats, somethings do stay if your pivot table options have the "preserve formatting" entry checked (usually the default). You can also double click the tile of a numerical field and in the resulting dialog click the number button to set the number format for it.
Re: Pivot Tables: Pivot table layout
I did what you did and removed all other fields in the Row area except the Date field and then I highlighted the whole column and chose the Group function.
When I did this, all I got was a new empty column labelled as "Group 1" at the top, but without anything else being added or actually grouped - what's the point of that ?
How do you tell Excel that you want actual periods, like months, quarters, years, etc ?
Re: Pivot Tables: Pivot table layout
I didn't highlight the column. Just right-clicked in the date field and selected group. In the resulting dialog box I selected how I wanted it grouped.
Re: Pivot Tables: Pivot table layout
O.K. I just placed the cursor on one of the dates in the date field. Then I right clicked and saw the option called "Group and Show Detail", with sub-options, one of which is Group. I chose Group and, as before, the computer beeped and up came a warning box saying "Can't group that selection".
I played around with your sample worksheet (a.xls) Ungrouped and then Grouped the dates with the available grouping options. Why then do those options not appear for my data ? Is there perhaps something wrong with the actual dates themselves ?
Also, I have about 3600 events, each with a date, but only 500 or so dates appear in the Pivot Table ?
Re: Pivot Tables: Pivot table layout
Can you attach a workbook with a sample of your data? I suspect that one or more of the entries in your date field are not Excel dates. That would explain the problem you describe. It's not enough that most of the data are dates; they all must be.
Re: Pivot Tables: Pivot table layout
After I finished creating a cut-down version of the file (to enable it to be sent via this system), I just tried to create a pivot table with just the date field included - I was able to group the dates as I should be able to.
Therefore, the problem must be with one or more of the dates that I have deleted whilst getting the file down to size - even a file with just the dates in a single column is above the file limit of this forum.
Is there a way I can audit for date errors ?
I tried looking through the dates column, twice, in "formula edit mode" (Ctrl & the key next to the 1 on top of the keyboard) but found nothing unusual and all the dates are numbers, which is correct.
Re: Pivot Tables: Pivot table layout
All I can suggest is to systematically try different sections of the dates to see if that helps spot the prolem. It may be that there is some limitation in the number of didderent dates that a pivot table can handle, but I would then have expected an error message to show. You earlier said that only about 500 of the 3600 dates showed in a pivot table. Maybe there is a clue there. Do the dates that didn't show have something in common? If you copy all of the dates and maybe a few other columns of data and paste them into a new workbook, does the problem still occur?
Re: Pivot Tables: Pivot table layout
I have lots of unused rows in the data range (currently 12000) and when I exclude them from the Pivot Table data range, the problem disappears adn i can group as I should be able to.
The reason for these empty rows is that I have set the data range to allow for future data entered, the actual size of which is unknown to me.
I don't know any other way to set this range - someone in this forum did try to explain dynamic name ranges to me before, but I couldn't understand them.
Any ideas ?
Re: Pivot Tables: Pivot table layout
Well, you have identified the problem. Yes, dynamic ranges are the way to go here. See
http://www.ozgrid.com/Excel/DynamicRanges.htm
and try to follow the logic. It's worth investing the time to understand. Meanwhile, you can hardcode a defined name for your data that only includes the actual data and base your pivot table on that. Then as new data are added, redefine the name.
Don’t have an account yet? Register yourself now and be a part of our community!