Hi.using Excel 2010. I can not figure out how to have excel not plot a zero in my line graphs. I have a data range that goes to the year 2020, and the values for 2012-2020 are blank, but the lines drop to the zero value at 2012. In the 'hidden and empty cells' dialog withing the 'select data' box, I checked 'gaps'. I also have unchecked on the 'show a zero in cells that have zero value'. The referenced cells that the chart is plotting has a simple addition formula.
Jul 5, 2014 - Step 1: Open your spreadsheet in Excel for Mac 2011. Step 2: Click Excel at the top of the screen, then click Preferences. Step 3: Click the View button in the Authoring section of the window. Step 4: Click the check box to the left of Show zero values to remove the check mark. How to use the Microsoft Slider Control to implement a range filter input feature in Microsoft Excel. Almost every Excel workbook needs some way of user interaction (maybe except for the Excel models serving solely as the reporting front-end of a database). The users changes parameters, sets filters, triggers actions and so forth.
Not sure if that would affect anything. I just want the graph to stop at 2011, the last data point not a zero.
Hi Steve, I tried doing all that, understanding that formulas always return a number. I read several solutions and the one that seemed the simplest is to do a two column solution.
One column has the formula and the second column simply equals the first column. But that didn't solve my problem. Not sure if i'm doing the na correct. Here is the formula in the cells is =SUM('sheet1'!F22,Sheet2!F22) the cell is formatted Accounting, so it looks like this: $ 5000.00 if there is something to return and it shows: $ - if there is no data to add.
I tried to add this when i attempted the two column method. On the second column (which is the range for the chart data), i did this: so if column A1 had the above formula, I had B1: =IF(A1=','#N/A',A1). How should i do any of this different to get the chart to simply stop charting if the cell is zero?
On a side note.why would excel make this so difficult or unintuitive? It seems like a simple click should do it. It seems that's what the 'hidden and empty cell' button was to do, but it doesn't work.
Thanks for your patience with me. It seems that's what the 'hidden and empty cell' button was to do, but it doesn't work. Yes it DOES work. As I mentioned in my original response, if a cell has something in it, it is NOT empty. Even the null string (') in a cell makes the cell NOT empty.
So unless you HIDE those cells (by hiding the rows) you can check the 'hidden the empty' but since the cells you worry about are not hidden nor are they empty, they are charted (and text is plotted as a zero). The way around this is to use the #NA error which does not plot. I figured i had that wrong on the 'N/A#' part, but i tried that after i had already done what you suggested ( i saw it in another thread) I redid the IF statement the way you described but it didn't work. Here's what I have: REMINDER NOTE: I have two columns, the first has the formulas which pulls data from different sheets and adds them. The adjacent column has the IF(A1=',NA(),A1) formula. I'll call the first column the HOST column and the 2nd column the CHART DATA column, since the Chart's data source is the 2nd column. I set up the data columns to go to the year 2030.
However I only put the SUM formula through the year 2020. Years 2021-2030 are blank. This is how it looks: cell range from year 2005-2011- Has data since it could add up the filled cells and plots correctly on graph cell range from year 2012-2020- returns the Accounting version of zero '$ -', on both the HOST column and the CHART DATA column and not the expected '#N/A'.
This range plots as zero on the x axis.not what I want. Cell range from year 2021-2030-are blanks in the HOST column (since there are no formulas). The CHART DATA column returns the expected '#N/A'. This range is not plotted on the chart (which is what I want, but for years 2012-2020 too) So I have been able to make the cell range without a formula show '#N/A' which gives me the desired result, but how can i make a cell that has a formula do that?
Hi Steve, I attached a version of what I'm trying to do for you to look at and help. Based on what i have explained so far, the data should be self explanatory, but here is the overview: Column b contains the formula summing data found in sheet 2 & Sheet 3. Column C contains data from Sheet 1. Column D is equal to column B, but contains the IF clause in order to produce the desired #N/A to get the graph to not plot. Column E does the same thing except for column C You can see in the results and on the graph that through 2020 (which are the years I entered the formula) the graph is plotting zeros.