I am using bar chart to display top 5 units sold each year to my Excel file. In the data table, I have listed all the units and its number (quantity) sold. The problem is, the legend of the chart displays all the units instead of just displaying units that has values (top 5 units each year) which makes the legend hard to read (see attachment) How do I get Excel to select legends only if there is a value for it?
View 2 RepliesI have a standard bar graph with 2 sets of data: 1 dataset on the primary axis and 1 on the secondary axis. I have defined the legend entries fairly specifically so the descriptions are long. I have also inlcluded a 'rank' value into the graph which doesn't appear on the graph as the values on either axis aren't at a range that allows it to display. However, I want to display the rank value in a table below the graph. However, when I add the table, the legend entries are so long, the graph gets 'scrunched' and looks terrible.
Is there a way to either:
1.) Only show the rank data in the data table below the graph?
or
2.) Show only the data in the data table below the graph and not the legend entries?
or
3.) Another option to display the rank data on the graph?
I have 10 graphs with more than 20 legend entries. However, each graph only needs 3-4 elements out of the 20 legend entries in the graph. Is there any way to force Excel to only show those legend entries that have a value? (without deleting them manually)
View 12 Replies View RelatedIn this file i did paste special values so the links are no longer there, but in my original file alot of the values you see in the cells are pulled from other worksheets ( thats just so you know to lookin values of the cells and not the formula ).
I basically need row 18 starting from Column O to self populate.
Its based off of which has 1's in the Column. I have the completed here manually just to show you what i am looking for . I usually have to go to row 3 and Enter the data manually depending on which 1's are filled in for the column.
If i could have a method of having row 18 starting with Column O to self populate based off those 1's
I have a pareto for a manufacturing facility that has a list of nonconformists and their occurrences in columns K and L. My issue is since it is a running total for the year at certain times the values are 0 for the number of occurrences. This calculates fine, but the graph is very large and the failures are all located on one side instead of being evenly distributed. I am trying to get it to only plot for what has occurred and not have a bunch of blank values. To make it a little more complex I'm trying to avoid macros.. I can get them to show up blank just can't get the bar graph to resize itself to fill the chart.
View 3 Replies View RelatedI have a bar graph..and on top of each bar, i want the x value for that bar.
View 2 Replies View RelatedI have a line graph that charts certain data per day in a month.
During the weekend there is no data. The graph however considers the number as zero, and I want it to just "skip" that day and plot the line directly from the friday date to the monday, instead of the line touching zero on saturday's and sundays.
Is it possible to do this? Or am I forced to adapt my table to only include days of the week?
The company I work for has kept a list of the types of objects sold with their houses and how many were bought for each customer, via an Excel document. Here's a snapshot - You can see the name of each type of object on the left, with each column afterward representing an individual customer (Each vertical row is one customer) and how many of each item they bought.
[URL]
We're hoping to make a bar graph of how many of each type of item has been sold. I selected all the titles and columns of numbers and ended up with this, which is almost what we're looking for:
[URL]
The problem here is that each individual customer has their own line on the graph. The Recessed Can Light one I highlighted is a good example of a lot of people who bought varying amounts of that one item. What I'm looking to do is combine the individual sales for each item and make a single bar for each representing the total sales overall, so we can gauge what the best and worst-selling items are. Is there any way to do this?
I am creating a line graph to display changes in Cost Per Unit (CPU) of a given item over time.
The issue I have is that the CPU data on the line graph is displayed as 0 values where data is yet to be inputted. Instead I want the graph to remain blank where no data has yet been added. How can this be done?
find attached a simplified mock up of the spreadsheet.
Constant (fixed) values are:
- Date
- Hourly Rate
Values inputted on a daily basis are:
- Hours Worked
- Volume Picked
Calculated values are:
- Total Cost
- Cost Per Unit
- Mean [=average(CPU)]
- UWL [=mean+(2*stdev(CPU))]
- UAL [=mean+(3*stdev(CPU))]
I'm open to using VBA if necessary, however as I will not be the only user of this workbook when it is complete I would prefer to keep it a simple as possible.
I've two collumns, after some index the values of cells are zero
how i can draw a chart without selecting those zero cells?
one of my excel reports, i am using a dsum formula and plotting a chart against it. i do not want to show the zeros on the graph, is there anyway i can do that, i could not find anything in the tools -> options.
View 2 Replies View RelatedI am trying to create a line graph to show the trends (up or down) of I-Fund vs Gold. The trouble I am having is how to set the axis. The date one is an easy one, but the gold range is more or less 800-1000, and the I-Fund is 13-18. How can I have these both on the same graph to compare? I am attaching an Excel sheet as a reference. Obviously I need to delete the empty IFund cells.
View 2 Replies View RelatedI was asked to make a trend graph showing activation's and deactivation's over time. My boss wanted it to be done automatically when you placed values into a simple excel sheet like the attached image (the graph would be below this).
View 14 Replies View RelatedI have a dashboard which has dropdown boxes to pick out the data you want to see. From this data i then have graphs which are graphing 0s from the data making the graph look horrible.
I know I need to add NA() to the cells so that 0s dont graph, but all my data has formulas in and i don't know how to add the NA part to the existing formula.
my formula for each cell is
=IFERROR(VLOOKUP($A9,'DB data'!$A$1:$HH$2003,COLUMN(FP1),FALSE),"-")
This looks up name such as "sign on time", then goes to another sheet and retrieves the data.
Where can i add the NA part. I've tried instead of the "-" at the end but doesn't work. and ive tried instead of FALSE and this stops the formula from working.
The user will select a range (example, B4-Z4). The macro needs to test each cell to see if the number is in a certain range (example, is the number in the cell between 21-40, 41-60, 61-80, etc? - these ranges will not always be the same on each worksheet). If the cell is in that certain range, that cell is a 1 for that range. Example,
B4 = 23
21-40 = 1
C4 = 30
21-40 = 2
D4 = 45
41-60 = 1
After all ranges are tested, it will be graphed on a separate worksheet with the x-axis being the ranges (21-40, 41-60) and the y-axis will be the total number of cells that fit in the range. The above would be....................
if I can gray out the unused columns. I am creating a template that use only column A to J. I don't want user to be able to see or scroll to the unused columns. Is there any command in the setup that can gray out the unused columns? or maybe VBA codes?
View 2 Replies View RelatedI have some if statements for 300+ employees. Some rows are left blank b/c a specific type of employee didn't meet the if statement criteria...which is what I wanted. Now how can I get rid of those blank rows (that actually do contain formulas but are returnnig a "" value) to have all of the employees in a "nice" grouping?
View 9 Replies View RelatedApparently Excel can't delete unused rows, nor can it specify the sheet to be exactly X by Y large. My problem comes in any time I fill down a column of data by selecting the column header (A for example) and selecting fill down, rather than selecting the exact range of cels I need the formula in (which might be several thousand...) Excel winds up taking my sheet that was, let's say, 1000 rows long and filling down until now it's many many many thousands of rows long, with "error" fills in most of the rows. This now means that any kind of sorting, resizing of rows, etc. takes absolutely forever because it's dealing with 5 or 10 times more rows than I am actually using. Even clearing the contents of all the extra cels does not help. I can't in any way seem to get rid of these extra rows.
View 9 Replies View RelatedI have a page full of data and have some rows in between them which are empty. Is there any way that I can delete the unused rows through VBA code.
View 6 Replies View RelatedI am trying to create a Purchase Order Form that will automatically pull the next unused number from a summary Purchase Order log. I would like to create a formula that will look for the next blank cell in Column B and then pull the number from Column A in the same row.
View 4 Replies View RelatedUsing Excel 2003. I have a data range for a graph. The values in the cells are the results of a simple If function - If(m28>0,n28,0). The results are taken from a larger data input exercise. But, the graph line (a simple graph!) plots the FALSE value (0) when I would like there to really be no value & hence no plotted point if the result is FALSE.
View 4 Replies View Relatedtrying to create a button to hide unsed rows, can anyone help? basically the sample below is what i want to do but it hides all the rows listed.. i want it to hide only unused rows in the rows selected if yes is selected and unhide them when no is selected...
wannahide = MsgBox("hide rows", vbYesNo)
If wannahide = vbYes Then
Rows("5:28").Hidden = True
Else
Rows("5:28").Hidden = False
End If
Other than using the search on a 1 by 1 basis, is there an easier way to locate Declared variables that are unused in the code and/or Public variables that are only used in 1 sub/function?
View 1 Replies View RelatedI've just finished my project (with loads of help from you guys) and wondered if there was any way of making it look less like the spreadsheet it is when it's opened.
I've removed everything I can and I'm left with a vertical scroll bar (needed) and the top menu bar (not needed).
Is that about it or can I get rid of that, and maybe size the window accordingly when the workbook is opened (so the dead space on the right is not seen) and perhaps stop the vertical scrolling at the bottom of the sheet so it doesn't scroll down to 65555 and the dead space under the sheet isn't seen.
Is there a simple way to hide rows and columns? A lady in my office just asked me how to make only Columns A to J visible and only show 100 rows. She doesn't want the rest of the spreadsheet to show. I know how to manually hide columns and rows but it takes a while to drag down and select 64000 rows so I thoguht I would ask the question:
Does Excel have a feature that will automatically hide all unused rows and columns or is there a simple VBA code to do this?
Is it possible to use conditional formatting to set all the unused columns (S - ZZ) interior color to gray?
View 5 Replies View RelatedIs there a macro that will format unused rows or with no value in a given range. I have a sheet that I am using that some of the date is either added or deleted and would like to have a macro to do grey out unused rows. I am using this with a form control.
View 3 Replies View RelatedI have a spreadsheet ranging from A1 to AH602 Column AH contains row totals and Row 602 contains colunm totals. I am importing a CVS file starting at cell A4 - the number of rows contained in the import varies drastically. I would like to build a macro that would remove all the unused rows. I have tried to record a macro that would do this but the number of rows always ends up static and not variable.
What I'm doing below is Using an END+HOME to take me to the furthest point in the active range then 3 UP ARROWS to get to the desired row then an END+LEFT ARROW followed by END+UP ARROW and 2 DOWN ARROWS to get to the starting cell in Column A. At that point I use a SHIFT+END+DOWN ARROW to capture the range of Rows I need to delete.
Sub Macro1()
ActiveCell.SpecialCells(xlLastCell).Select
Range("AH599").Select
Selection.End(xlToLeft).Select
Selection.End(xlUp).Select
Range("A101").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
End Sub
This works except that the starting cell remains the static.
I like the look of when only the used columns and rows are shown. I like to hide all unused columns and rows, and have the background and a minimalist spreadsheet.
HOWEVER, is it just me, or does Excel move a lot slower when thousands of rows and columns are hidden? Particularly, opening files seems to be slower. I'd love to delete them entirely from existence, so Excel only has a few rows and columns to work with, but that doesn't seem to be an option.
I have a long grocery order list and only choose about 100 items out of 1000. I would like to send it to the store with only the items I need. There are several category headings throughout the list which I would like to keep...like Produce, Meats, etc. The list total dollar amount is automatically updated as I enter the amount of the items I need. I just have a lot of unused items (10) pages worth.
View 2 Replies View Related