Formatting Row Labels In Pivot Table / Chart
Apr 30, 2014
Was working on this problem for a poster: [URL] ......
I can't seem to get the pivot table/chart to format exactly as I desire. It seems that as soon as I add 'group by hour and day' Excel forces the formatting to AM/PM and I want to keep it military. I want to group by hour, so that data that occurs at 6:00 and at 6:30 are grouped, and I had to group by day so that 6:00 on 1/1 was separated from 6:00 on 1/2.
Attached is a worksheet which shows the desired chart format (not a pivot chart), and the attempted pivot chart. I want the pivot chart to match the 'simple' chart in look and feel. Any attempts to change the formatting of the row labels to 'h' is promptly ignored by Excel.
Note the two tasks that occur at hour 18 (one at 18:00 and the other at 18:20 (you will need to see the formatting to truly see the minutes)). Those should be combined in the pivot table (and they are) and on my 'adjusted' table (where I used SUMIFS).
View 2 Replies
ADVERTISEMENT
Feb 7, 2014
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1
2
3
4
Here's what each of those "mean" (I have this in another table):
1 - Sick
2 - Overtime
3 - Court
4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
View 8 Replies
View Related
Jan 16, 2014
I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
View 1 Replies
View Related
May 15, 2014
I have a data set where a number of pieces of technology are listed in rows. For each piece of technology it can relate to a "Network" or a "Terminal" and the columns are yes/no as follows:
Tech Network? Terminal?
Tech 1 Yes No
Tech 2 Yes Yes
Tech 3 No No
Tech 4 No Yes
When I plot this on a pivot chart, I get the attached chart, where the x-axis relate to the hierarchies, i.e. Yes/Yes, Yes/No, No/Yes or No/No. As you can imagine, this is not very useful for the viewer as it's not clear what the yeses and nos refer to. It would be much better if I could rename the axis labels: "both"; "network only"; "terminal only"; or "neither".
Is this possible? And is there a solution that doesn't require me to change the source data? I'm happy to use a power pivot if that would make things easier.
View 1 Replies
View Related
Dec 27, 2012
Using Excel 2007: I have a column graph whose numbers/labels on the vertical axis go from ($300,000) to a positive $550,000.
Question: How do I get the labels on the vertical axis to appear in the "accounting format" with the "negative numbers in red" and the "positive numbers in black or blue"?
I have tried to find the answer online and it appears to need to find Format Data Series, which I have been unable to do.
View 3 Replies
View Related
Sep 18, 2009
Optionally, to customize the sort operation, on the Options tab, in the Sort group, click Sort. In the Sort <Field name> dialog box, select the type of sort that you want by doing one of the following:
To return items to their original order, click Data source order. This option is only available for OLAP source data. To drag and arrange items the way that you want, click Manual.To select a field to sort by in ascending sort order, select Ascending (A to Z) by, and then select the field from the drop-down list. To select a field to sort by in descending sort order, select Descending (A to Z) by, and then select the field from the drop-down list. Tip Read the Summary section at the bottom of the dialog box to verify your choices.
My problem is this: after following the above steps, then clicking Manual, then ok, I cannot drag and arrange the row labels in the PivotTable.
View 2 Replies
View Related
Apr 11, 2012
I have 5 pivot tables which feeds data from a single worksheet. The data from this sheet change every month. Meaning, old data out (deleted) and new one's inn.
The problem I have is the old data I used in the Row Label (such as Customer Name) are still showing in all the pivot tables even though the record is no longer there.
View 3 Replies
View Related
Feb 15, 2014
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
View 1 Replies
View Related
Jun 25, 2013
How can I Remove the word "Blank" in Row Labels or change it as Blank or no data.
View 1 Replies
View Related
Feb 15, 2014
I have a pivot table with slicers. I would like to collect into a cell all the item names that have been selected (filtered). (through VBA or Formula) For example if I select A, D, and E into the (A, B, C, D, E, F) list. I want the labels A, D, E to appear in a cell.
[URL]
View 3 Replies
View Related
Mar 27, 2007
I have a pivot table in Excel 2003 which summarises one field (subjective) then totals at the change of a second field (cost centre). I want to show the cost centre label on each line of subjective. In a standard pivot table it only shows the cost centre with the first subjective in each group.
View 2 Replies
View Related
Dec 19, 2008
My boss wants me to design a dynamic, updatable chart in Excel 2003. I initially made a Pivot Chart based on a Pivot Table which worked perfectly, but it doesn't look professional enough when printed (or viewed) and she wants me to approach it a different way.
So, I created a graph based on the data in a Pivot Table, and used dynamic ranges as the source for the graph series so that the chart updates when the criteria fields are changed for the Pivot Table. I then added two combo boxes (ie data validation lists) to the Chart sheet, and wrote VBA code so that whenever the combo box values are changed, the Criteria fields for the Pivot Table on the 2nd sheet are updated accordingly, and this in turn causes the graph to be updated as well.
This solution also worked perfectly, but now I've been told to create the graph without macros.
Does anyone have any suggestions? The requirements/details are as follows:
1. The Pivot Table is on sheet "PIVOT", and the graph is on sheet "GRAPH"
2. The Pivot Table has two criteria - School Name and Year Level
3. On sheet "GRAPH" there are two data-validated fields, School and Year, which only allow the selection of valid Schools and Year Levels
Is there any way to make the Pivot Table update when values are changed in the fields on the CHART sheet so that the chart also updates, but without using code nor a Pivot Chart?
View 9 Replies
View Related
Jan 24, 2014
I have column in my table that calculates time in hours between now and ticket open date (ticket open date in B1)
so, in C1 i have following formula:
=(now()-B1)*24
Results are in hours, such as 15, 25, 32, 65 etc
In pivot table, I would like to see how many tickets with values of less than 24 or more than 48, etc
My available columns are: Region, Ticket#, AgingOpenHr(this new column).
I tried it different ways, and I cannot get it Less Than and Greater Than work under Values, works if I select grater than or less than in Label, but label does not produce consistent numbers.
View 1 Replies
View Related
Feb 11, 2013
I am using Excel 2007. I have a population that I used to create a pivot table. I am currently double clicking on the value cells to create worksheets of only particular "row label" categories. I am then copying the "row label" information into the newly generated work sheet name tab. This works fine when I only have a few "row label" categories to do but it is tedious if there are many categories.
Is there any way to automate the creation of work sheets for all row label values and also naming each work sheet tab with it's respective row label information. Here are images of the pibot table and the type of work sheet I would like for wall row label values.
View 1 Replies
View Related
Jan 25, 2010
"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.
The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."
I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!
View 2 Replies
View Related
Jul 16, 2014
On a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates. I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.
I have attached the following code to a button on a different sheet.
The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.
I am having trouble with passing the start and end dates to my pivot table filter. I do not get any errors, the filter is simply blank.
Private Sub CommandButton3_Click()
Sheets("Details").Select
Dim pt As PivotTable
Dim Employee As String
Dim SDate As String
Dim EDate As String
[Code]...
View 6 Replies
View Related
Jun 11, 2014
I am trying to format the date in my x-axis which is currently 'month-day-year' but I would llike it to be 'month-year' I know it is because my data that was used for the date is 'month-day-year' but since I am using power pivot I can not change the data. Is there a way I can change it to my desired format so that my pivot chart will only have the 'month-year' format in the x-axis?
View 8 Replies
View Related
Sep 22, 2009
have come across the apparent inflexibility of a pivot chart retaining its user defined formatting whenever it is refreshed.
I understand I can save my chosen formatting as a 'user defined' custom chart, which can be applied post-refresh, but was wondering if a simple bit of code would do this automatically, saving the additional clicks!
how to deal with the attached. How could I adapt the code to call in other 'user defined' charts I may create in the future?
View 8 Replies
View Related
Jan 3, 2013
If I want to produce a pivot chart using VBA can I do it without producing the pivot table?
I am guessing that the following are a possibility:
Do in a virtual sheet that is not shownhide the sheet (can do already)
I am looking for the virtual sheet option or something similar.
View 2 Replies
View Related
Jun 21, 2006
Is it possible to use the chart wizard for a Pivot Table yet maintain that chart in the same worksheet and not have it in another? Every time I click on the wizard a chart is created on a separate page.
View 2 Replies
View Related
Sep 17, 2009
I have attached a portion of my Pivot Chart in Excel 2003 and have been trying to recreate this in 2007 with some difficulty. I am particularly interested in displaying the field list headings (specifically 'Count of Hit or Miss') in the pivot chart in 2007 but cannot make it work.
View 4 Replies
View Related
Mar 29, 2007
I have a data table and want to create six charts. In theory pivotcharts would be the way to go. However every time I refresh the charts they revert to excel's default chart and all my colour scheme and nice layouts are lost.
View 3 Replies
View Related
Apr 28, 2014
I have a list of customer satisfaction scores that are pulled in from a sharepoint list. The list is then used to create a pivot chart that is used in a web part on a dashboard in sharepoint. The chart is supposed to show average customer satisfaction scores per category per month.
The problem I have is that I can't filter the categories using a slicer, I can only filter the categories' values (i.e. the scores). I can filter by date though.
When I try to filter the various score categories/series (Support, Change Management etc) I only get the option to filter their values i.e. 1,2,3,4,5.
I want to be able to use a slicer or similar to be able to display either all of the series or just selected ones.pivottable.JPGpivotchart.JPG
View 14 Replies
View Related
Aug 14, 2014
Or at least with a formula?
This is the chart formula for the report:
=SERIE(Report!$B$10;Report!$A$11:$A$30;Report!$B$11:$B$30;1)
View 3 Replies
View Related
Mar 3, 2009
I have created some pivotcharts in Excel 2007, but I have a serious problem with them The labels (usernames actually) in the data table are impossible to read (pls see attachment) If I make the letters smaller it is unreadable, if I make them larger it is still the same. how I can make the data table "resized"?
View 4 Replies
View Related
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
Dec 4, 2012
My pivot table shows Towns as the Row headings and Month as the column headings. When I do a Chart, I get Towns on the X axis and Months as Y. I want the chart to show all my May data in a stacked column, then all my June data in another stacked column, etc. If I pick "Select Source Data" the options are greyed out. What's a mother to do?
View 1 Replies
View Related
Mar 17, 2008
I can not get my Pivot Chart to count, sort and categorized my data. I have included a sample file for your viewing. I basically want the data in cells C2:E18 counted, sorted and totaled by categories and locale. I thought the pivot charting was simple, but I'm having quite a challenge with this one. The pivot chart data should reflect as my sample indicates in cells E24:K27
View 3 Replies
View Related
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
Jul 30, 2012
Can Pivot Table chart add another data from another sheet? I attached the link for this file (Add data1), it is because the file consists of several sheets and I do not know how to show here.
I would like to add the data from the "Rate" sheet into the Pivot Table chart (Chart.PT). I made an example by using normal way (Chart.Case (9)), the column series in the chart is the one I added from the "Rate" sheet. I wonder if I could do the same in pivot table chart.
View 9 Replies
View Related
