reference a different sheet in excel for mac 2011 nonnumerical data -hyperlink

#Reference a different sheet in excel for mac 2011 nonnumerical data hyperlink software#
#Reference a different sheet in excel for mac 2011 nonnumerical data hyperlink series#
#Reference a different sheet in excel for mac 2011 nonnumerical data hyperlink windows#

If you use a line chart (or bar chart) and you double click your x-axis values, or right click them, you can go to their format axis page where you will see that you have the choice of treating the x-axis as text or dates, but not numbers. Scatter chart in Excel assumes both axis are numbers and thus plots your numeric X-Axis in the expected manner. You should use the XY SCATTER chart instead. So, you cannot plot a number X-Axis on a line chart. Most people would expect the LINE chart to treat the X-Axis as a value, but MS is not most people so they decided to treat it like a category (unless it is a date more on that in a moment).

The problem is that to use charts in Excel, you have to know how each chart type treats the X-Axis. For example: how many days are there between jan-1-2001 and jan-10-2001? or What is the distance from the 10 yard line and the 20 yard line? Where as numbers and time have a concept of distance inherent in them. There is no 'distance between colors' (what is the distance between red and blue?). For example category would be something like color (RED/BLUE/GREEN). There are basically only two kinds of X-Axis: discreet/continuous (aka. How you treat the X-Axis determines what kind of chart you use. The issue at hand is how different chart types treat the X-Axis. One case in point is your problem.Įxcel Line charts are not line charts they are bar charts that just use lines instead of bars.

#Reference a different sheet in excel for mac 2011 nonnumerical data hyperlink software#

This is because about 10 years ago, Micro Soft went stupid and started trying to make software that is 'really helpful for the user' translate 'makes stupid decisions we don't want'. You have stumbled across what many also find, that Excel stinks as a graphing tool. To be honest, if you are going to plot a line chart which has one axis which appears to be dates, it may confuse users if those dates are not then in order. Reference i gave in comments which reminded me to transpose the data is scatter-chart-with-one-text-non-numerical-axis

#Reference a different sheet in excel for mac 2011 nonnumerical data hyperlink windows#

On an old Mac with Excel 2011, similar process for Windows and later Excel, removing the line would look like:Īnd you can select a line colour and add it back in:

#Reference a different sheet in excel for mac 2011 nonnumerical data hyperlink series#

transpose your original data set) and then plot this as a line chart and right click format data series > no line. You arrange your data horizontally with each data point in its own column (i.e. In the picture below, this is showing above B39, as I have transposed your original dataset, but the formula was pointing at your original vertical dataset. To see all data points, with data that are whole numbers, then 1 should do it, but may become very crowded, so a trade-off.Ģ) To stop the re-ordering of your dates: The trick is to convert your dates to text using =TEXT(A2,'dd/mm/yy') where A2 is a data point for the X axis etc. You would need to convert the dates to text and plot as a line chart without the line.ġ) Right click X axis and set the major interval to a balance between the amount of detail you want to see and that which is legible. 1) You can't see all your data labels on the X axis unless you format the X axis to have major interval of 1.Ģ) With a scatter plot, you cannot have your original labels retained on the X axis and, in your case, as your dates are recognised, they are ordered as such.

Reference a different sheet in excel for mac 2011 nonnumerical data -hyperlink