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.
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"?
Using the table, draw a stacked chart that has both the project name (A, B etc) and Month on the x-axis (below all the projects). the stacked chart should contain the count for cat1, cat2, cat3 and cat 4 for each project (in different but consistent numbers for each month).
I need this for a presentation today,
OctoberNovemberDecemberProject Cat. 1 Cat. 2Cat. 3Cat. 4Cat. 1 Cat. 2Cat. 3Cat. 4Cat. 1 Cat. 2Cat. 3Cat. 4A 00236111110001B C D E F 0012230182600425G H I 02114001602210J K L
I want to create grouped labels on a chart x-axis of the type created automatically when creating a pivot chart. For example: I have projects, samples and sample yields. There are multiple samples in a project so I have a sheet that looks something like this below:-
I would like to create a chart that has Project and Sample as the x-axis where the samples are all grouped into their project. Pivot charts do this nicely, but how to do it without a pivot chart? As you will see from the attached file I can get both Project and Sample on the x-axis, but the Projects are overlapping.
I have a chart that feeds from dynamic ranges which contain whole rows of null enteries displayed as #N/A. The charts do not plot these enteries (as it shouldn't) however it does display the category label even if it too is #N/A. Is it possible to remove the category label if all data for that row is null. ie the label is also removed from the chart.
Has anyone ever succeeded in offsetting category labels in a chart by surpressing the first category label. I am plotting monthly financial data over several years and want to show category labels for the month ends which coincide with quarterly month ends (i.e Mar-06, Jun-06, Sep-06, Dec-06). If I select 2 tick marks between categories I get Jan-06, Apr-06, Jul-06, Oct-6) because my first month and category label is Jan-06. Is there a way to offset displaying the first category label by two tick marks and start with Mar-06 and then go every two tick marks between category labels?
Is it possible to hide some of the number labels on an axis; e.g. in on the chart below to hide the X-axis label 96? Or only start numbering from 97 while the chart displays from 96. Auto Merged Post;Well I have a solution to hiding the first value on the excel X axis values labels just using a custom number format;
[Black][>96]General;
but is there anyway of formating more than a couple of the number labels individually?
How can I add missing values to the axis in a pivot chart line chart? I have a numerical X-axis with values such as 0,1,5,8,14, etc. I have another set of Y values that correspond to the X values. If this was an XY-scatter plot I'd be able to plot X vs. Y and connect the dots for "gaps" in the X values. Since it's a pivot chart I cannot use an XY-scatter plot, I can only use a line chart. The line chart doesn't give me the ability to add the "missing" values, so it gives the impression that my data is more closely packed than it is. The data source is external to my spreadsheet, so I cannot add the values before creating the pivot table/chart.
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).
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?
I made a pivot chart. On the X axis are days of the year. I would like the axis to give the months rather than the days, because it is easier to read. But when I select the chart's axis, I don't have acces to any "number" sheet in the "Axis format" section. I could just change the scale.
ActiveChart.Axes(xlCategory).Select With Selection.TickLabels .Alignment = xlCenter . Offset = 100 .ReadingOrder = xlContext .Orientation = 45 .NumberFormat = "[$-41D]mmmm;@" 'I added this line End With ActiveChart.HasPivotFields = False
I had created a pivot table and pivot chart for the data, the pivot table shows the row label's as percentages which is correct, but it when comes to the pivot chart it shows it as decimal, for example a 10% shows 0.1, I tried of formatting the x axis and choose percentages but it doesn't want to change.
I have a pivot chart which has one Axis field (Cell Ref2) and one Sum of Values field (which is a table series of percentages calculated from formulas)
I see the graph like I'd expect, but I'm struggling to get it to show percentages rather than basic integer units on the y-axis? Usually I would imagine to just right click on the y-axis : Format Axis, and expect to see something to change the display units, but it is only for hundredths thousands etc rather than a percentage ? The units themselves in the table for that value series are in percentages so I don't see why it isnt showing the percentage units in the first place.
I would like to create a bar chart which is linked to a drop down box (from data validation) that allows me to choose of the 3 sets of data to show in the x axis.
So if i choose "HALF YEAR", the bar chart will have two bars (one is "jan-jul" the other is "jul-dec") with the green section data if i choose "QUARTER YEAR", the bar chart will have four bars ("jan-mar", "apr-jun", "jul-sep", "oct-dec") with the blue section data if i choose "INDIVIDUAL MONTHS" i have twelve bars....with the red section data.
The attached file has a scatter chart in which all of the data is properly displayed except the X axis. How can I get my X axis to reflect the data I have selected?
I have a set of data with values and dates (call the dates 'raw dates'). I have added 3 columns with formulae in them as follows:
Year =YEAR('raw date') Month =TEXT('raw date',"mmm") Day ='raw date', formatted as "d".
I have a pivot chart with three Axis Fields. In order they are: Year, Month and Day. This is so that the chart itself has three layers of x-axis names (year, month and day) rather than a whole load of dates which looks messy.
I would like to show the values as a running total "in" the raw dates of the data.
When I show the values as "Running Total in" 'raw dates', I get a load of #NAs in the pivot table because the raw dates are not in the Axis Fields. If I select Running totals "in" the Days (which is included in the pivot table setup), I get running totals over each month, starting at zero on the first date in each month. I need to have the running total run over the course of the whole data set, not mini running totals in each month.
When I choose only the 'raw dates' in the axis fields (i.e. taking out Year, Month and Day), running total works fine. The only thing is that I lose the nice looking x-axis. Adding the raw dates to the other 3 Axis Fields also doesn't work - it doesn't even try to give a running total at all, just the original values.
how to achieve a running total without sacrificing the x-axis?
Any way to change the font color of a chart axis when a checkbox is set to true.
let checkbox be checkbox5 and chart be chart5. What I would like to happen is that if checkbox5 is true then the font color of the horizontal axis changed to grey (RGB 166,166,166), if it is false then I would like the font color of the horizontal axis to be black (RGB 0,0,0). I also would like this macro to be run every time the checkbox changes, or the corresponding cell changes to TRUE or FALSE (it is in another sheet, say sheet2). This is the code I have so far but all it is doing is showing the chart as selected and not changing the font color. I would also like it to not physically select the chart, this is for a dashboard so the selecting of the chart throws off some of the visuals a bit, but not sure how to do change the chart values without selecting it.
I am wanting the colors of the bars in a stacked chart to change based on the date in the X Axis. As the current date arrives, the stacked bar needs to change to various blue shades. If the date is in the future, the stacked bar needs to be various grey shades.
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.
I have a spreadsheet with golfers handicaps, golfers names down the left in column A in rows 3-35. Row 2 has the event numbers titles 1 to 18 (18 events in a year). i need a graph that has the person as the vert axis and the horizontal axis needs to be the event numbers, so i can read down for the person and across to see how there handicaps change from each different event. i have attached the data below.
I have some numeric data that has to be put on a log chart. but there is no option for log charts. so i converted my data to log and then drew a chart but i am unable to edit the axis intervals and starting axis value. whenever i change the value it gets back to its default value,ie 1. can anyone tell me how to draw log chart or how to edit axis intervals and starting axis value?
How can I put another label under each bar (NOT outside end or inside end or any additional label. This is an actual label? For example, under the first blue bar labeled 56.7%, I also want to put N = 50 below the X-axis, above "Optimal." I also want to put N = 25 for the red bar immediately next to it labeled at 75.7%.
This is not a label, as it is below the X-axis.
An example of this can be found here (scroll down for second chart)
I'm trying to plot a function with specific values marked on the X axis (or at least distributed according to the said values). Namely, I have something like this:
And while it does plot the Y's as I want them to be plotted, X'es are just used as labels, which causes 0 and 100 to be equally apart as 933,9 and 944. How can I plot it, then, so that I simply select my desired Y values, their corresponding X values and get the plotted function?
how to incorporate another category and value axis to my existing chart.
1. I need to add a rank axis derived from my data column (C6-C15). This column should rank from highest to lowest and create a tie if any number is repeated. I have arranged the rank column in cells B38:L38 as they would appear. I would like this to appear on the top or bottom of the bar graphs.
2. Second I need to add the frequency of hits which = 3 derived from cell J1 This should appear on the chart were it is currently titled. This should create another bar graph colored green and labeled 3.
3. Lastly the numbers axis can remain the same as it appears on the chart or if it would be better to align as shown in cells B43:L43.
I am putting together a graph and my manager wants the companies with a zero value in the bar chart to stand out (0 = really good). The only thing I could think of was making the individual x-axis label's font turn green if value was zero. Is there any way to accomplish this? (Additional: This chart will be updated every month with new data so I do not want to change by hand every month).
