Suppose I wanted to create a graph with the following parameters:
[ul][li]Temperature[/li][li]Wind speed[/li][li]Precipitation[/ul][/li]
The Y-axes would be the numerical values for each parameter. The X-axis would be the date with time. I know to make a scatter chart to pick up the values. I need help with the axes.
[ul][li]The X-axis is the date. But there will be multiple values from different times on each date. For example, I might gather data thrice per day. How do I graph the values such that they are not on top of each other (i.e., separated by time within the date)?[/li][li]Since time is cyclical, how do I make each day represent a 24-hour period? i.e., I may have data for 0600, 1400, and 2100 on Day 1, and the same for each succeeding day and beginning at 0600 each day?[/li]How do I make multiple Y-axes? i.e., 0 to 100 degrees Fahrenheit, 0 to 50 mph, and 0 to 2 inches, scaled over the height of the chart so that the graph is readable?[/ul]
Do you have the time as part of the date? That would allow separation.
Right click on the data on X axis. Select “format axis”. Try changing the Units, which should change how many points on the X axis are labeled. In my experience it takes some back and forth to get it right.
Right click on one of the lines of data. Select “Format Data Series”. It will be listed as Primary Axis. Change that to Secondary Axis. You can do this with multiple lines of data.
No, the time is not part of the date. I get data like that sometimes, and I don’t like it. I like to see mm/dd/yy format. In this case, I would like to see the time in a separate column.
Excel is good for relatively simple graphical display of linear data. Trying to plot more than two types of data on the same plot is stressing its capability, and trying to format it so you have a cluster of different times under each date is, well, problematic. By that, I mean I’m sure it can be done with enough effort but isn’t really worth it. I would suggest that you either combine date and time into datetime for plotting, and then have a label for each point that calls out the time and date separately, or just plot these on separate stacked figures.
I would personally use Python and Matplotlib or Bokeh for this but if that hasn’t occurred to you up front then I’m guessing you don’t have much experience with these tools. Although there is a learning curve, once you understand the basics of building plots in these tools you can build really sophisticated plots displaying information in a variety of ways that will be robust to changes in your data set and won’t require any manual formatting.
If you are just doing this exercise for one project and never again, perhaps it isn’t worth the effort, but if you have to do this kind of plotting regularly or are trying to build an application that will do this repeatedly without intervention, using Python and a plotting toolkit (and there are several others besides these two) will end up saving you so much time. Except that you’ll figure out how easy it is to do a bunch of asthetic and functional manipulations and waste all that saved time playing with the tool until your boss comes in and wonders why you haven’t just made the damned plot already, but that’s part of the fun.
If the time is in a separate column then right click data in the graph. Select “Select Data”. Edit the Horizontal Axis. Use the mouse to select the two columns which contain the date and times. Click “OK” to close the dialog box.
Data format should look like:
A B C D
Date1 Time1 Temp Speed
Date1 Time2 Temp Speed
Date1 Time3 Temp Speed
Date2 Time1 Temp Speed
Date2 Time2 Temp Speed
Date2 Time3 Temp Speed
And as Stranger says, there are better tools for these. All come with a learning curve. “R” is free and can do just about anything if you’re willing to put in the time. I don’t code so I use pre-existing tools.
And I see that the message board doesn’t like my attempt at formatting.
If column A contains the date and column B contains the time, you should be able to create a new column defined as A+B/24. The result should be a fractional date which will be plotted correctly.
Just an exercise with no need to do it on a regular basis. I have basically no time for learning a new programming language. (I’d like to learn Python, but… no time.)
Just have a colum that is combined datetime for plotting on the X axis and then put in custom labels that have the separate date and time for each entry. Or use a line plot and combine date and time cells into a single text field which serves as the X-axis labels unless the entries have to be spaced specifically according to time.
And not to ride my hobbyhorse too hard, but set aside the week or two it will take to learn Python, and the basic functions of NumPy and Matplotlib (or Bokah, or whatever graphing toolkit you prefer). If you ever have to deal with any kind of data analysis it will save you so much time over trying to make Excel do dancing bear tricks that it will absolutely be worth it. R is okay if you are doing a lot of statistical analysis (which is its core strength) and its built-in graphics library ggplot2 is quite powerful but can be a little clunky and slow for general data munging, while Python can give you almost C-like performance by using C-based function libraries and Cython code, and has far more tools available for use on anything from image processing to making web-based interfaces. You can also use R functionality from Python which means you’ll only need to use one development environment/interface, and for most things using the web-browser Jupyter notebook as a development/interface/documentation tool is quite easy, although for larger projects you’ll want to use a proper development GUI like those that come with the free Anaconda Python distribution unless you are already comfortable working from command line and text debuggers.
I still don’t know what you are trying to say. What format is the data you have to work with? If your data has date and time associated with each data point, in a format that Excel recognizes as a date & time, then the method I explained above should work.
A couple of work arounds.
If two of your parameters vary about the same order of magnitude you might be able to scale the values of one, plot both the same axis and add the “3rd axis” lables as text boxes.
Create the graph of one parameter separately using the same X-range. Remove as much of the plot & chart values as you can from one chart and make it’s background transparent. Then overlay one graph on the other and align them.
I agree with Stranger…Excel is terrible for plotting anything but the simplest data. And don’t get me started on people who try to use it as a database…
I personally use Python’s matplotlib as well as Matlab for my plotting. Scilab is an open-source (zero cost) clone of Matlab that includes many of its plotting abilities.
In grad school, I got to use a lovely graphing tool that is much more capable than Excel but doesn’t require the time investment that learning Python or Matlab would. It’s called Kaleidagraph. Kaleidagraph is very flexible about the format in which your data arrive, and it’s easy to make complex plots from those data. They offer a 75-day free trial, which is pretty generous. Kaleidagraph is available for both Windows and Mac machines.