Hi: I am trying to create a graph with Date/Time together on the X-axis from large environmental data sets. I am running into problems with Excel not allowing me to specify the formatting of the data series.

Everytime I try to direct Excel to a specific data set, it automatically reverts to its original selection. This doesn't seem to affect the graph axes until I encounter a parameter (like pH) with no units. Then it messes up the date axis (why is that??).


I have been creating a guide for myself, which will probably explain my problem better: Please see below:


How to graph EcoWatch data in Excel and incorporate Date/Time format:
(Note: process begins AFTER importing data to Excel format from EcoWatch)

1. Clean data as necessary
2. Copy and paste Date Time series into a second column
3. Format one as A1 "Date" A2 "MM/DD/YY"; format the second as B1 "Time" B2 "hh:mm:ss"
4. Save spreadsheet as .txt file
5. Open new spreadsheet - import .txt file as tab delimited file (Do NOT make space delimited)
5. Save new spreadsheet
6. Insert new blank column called C1 "Date + Time" C2 "MM/DD/YY hh:mm"
7. formula (ex) =A3+B3 or wherever your first data point is located
(this gives you Date Time format looking identical to Ecowatch, but in Excel formatting)
8. Select columns to be graphed (ex --> Date + Time and Temp)
Insert - Scatter Plot with Straight Lines
9. Right click Horizontal axis --> Format Axis -->
Min: Auto
Max: Auto
Major Unit: Fixed 1.0
Minor Unit: Auto
10. Right Click line --> Select Data --> Check Correct Series should say (ex)
Chart data range = 'SeriesName'!$C$1:$D$2,'SeriesName"!$C$3:$D$2035
1st series selects Title and units, 2nd selects data
Note: For some reason Excel reverts to original format when I try to tell it this
Mine says --> Chart data range = 'SeriesName'!$C$1:$D$2035
**Need to decide if above formatting makes a difference**

NOTE: pH has no units! AND graph dates axis doesn't come out right unless you add in units.
I believe this is why #10 is important...
not sure how to force Excel to accept my specified formatting.