Need to convert 3 columns of data to table to make surface chart

Posted on 2016-09-22
Last Modified: 2016-09-22
Hello experts, I have a large file of raw data... basically 3 columns of data - an X and a Y inputs, and a Z output... looking like this:

20	20	6
20	50	5.99
20	80	6.02
20	110	6.07
20	140	6.12
20	170	6.26
20	200	6.31
20	230	6.34
20	260	6.37
20	290	6.41
50	20	4.1
50	50	4.08
50	80	4.07
50	110	4.1
50	140	4.16
50	170	4.22
50	200	4.22
50	230	4.22
50	260	4.22
50	290	4.24
80	20	5.77
80	50	5.75
80	80	5.74
80	110	5.76
80	140	5.83
80	170	5.88
80	200	5.92
80	230	5.92
80	260	5.94
80	290	5.95
110	20	5.81

Open in new window

The 1st and 2nd columns of data both vary from 20 to 290 in increments of 30. The 3rd value is the corresponding "Z" value. My goal is to make a 3-D surface chart of these values.  Not knowing anything about Excel Pivottables, I started fooling with that and managed to get *very* close to what I want - see attachment "ExcelSheet.jpg". Only thing slightly messing up my nice chart is the fact that it blanks out the value where I have circled in red. As you can see from my raw data, that value should be 6. I can't figure out a way to "manually" put that value back in the table. Then my chart would be perfect.
   Also, crudely pointed at in green at the right of my screenshot, it notes one of the fields is "202". Where did it get that from? Lastly, circled in blue at the bottom right, it shows under "Values", "Sum of 6". I suspect this is why my one piece of data is missing in my final table... but I just don't understand why it did these several "weird" things.

Question by:shawn857
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 21

Accepted Solution

crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41811782
looking at the pivot table in your image, I see "Sum of 6" indicating that it is using the first row as labels, not data. Try adding a row above the data with labels to include in the pivot table range

Author Comment

ID: 41811798
That was exactly it, thank you! I notice there is a thing called a "PivotChart" too - would this save me a step?

LVL 21
ID: 41811801
you're welcome

> "PivotChart... would this save me a step?"
without knowing more about your process, all I can suggest is to try it ...
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


Author Comment

ID: 41811804
Well, I simply want to make the 3d surface chart from my columns of data. Would the PivotChart do everything all at once - make the table AND chart? I'll give it a try....

LVL 21
ID: 41811807
you're welcome, Shawn -- please let us know

Author Comment

ID: 41811817
Yeah, doesn't seem to do what I want, I will stick with the PivotTable, then making the chart from that... excellent!


Author Closing Comment

ID: 41811818
LVL 21
ID: 41811820
you're welcome, Shawn ~ happy to help

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question