Excel/Excel charts

Advertisement


Question
There are a number of tasks I that I cannot find answers for, to do with using Excel charts, and spreadsheets. I have three questions, so far.

I'm looking at the results I have from a soil analysis, with about 20 samples taken at increasing depths, down from the surface, from each of three different soils. I made a graph of percentage change in the soil composition, each analysis from a 1 cm sample, but take in just each 2 centimeters with a 1 cm gap between. My starting point is a table with the depths in the left column, and three further columns for results from the three different soils, to its right.

1)  The standard graph gives a fluctuating line to the right, for each of the three soils, showing its changing percentage with increasing depth. While this is the the right line,I need to change the orientation - I want a graph with the percent increasing to the right and the fluctuating line descending, so the surface results are at the top and going down the page is at increasing depth. However, when I click on the 'switch row/column' icon, I got a graph with  20 short lines going left-to-right, each being the percentage at one level, across the three different soils. How do I transpose the first graph to the desired orientation, instead?

2) As the central position of each sample is at position 0.5, I want the axis to state "0.5, 2.5, 4.5," and so on. While I have these down the left of my table, the automatic line chart on Excel will only state a position number - i.e. 1, 2, 3, or 1, 3, 5 if there is less space. I can 'manually' achieve this by removing all axis labels, then inserting a long thin text box, and typing each label in and fiddling around to get the positions roughly correct. But this seems not only crude, but is very time-consuming. Is there a better way of doing it?

3) I would also like to reverse a vertical column of numbers, so top to bottom becomes bottom to top. 'Paste special' will transpose top-down to left-to-right, only, and the data/sort tab will not simplyreverse the column, but only order it either way up, by size.

Thanks for this help.

Tim

Answer
1 - what version of Excel
2 - pls send a sample of some data to me at bobumlas@hotmail.com, use subject of "AllExpertsQ"
3 - if possible, DRAW (using shapes) what you'd sort of like to see onto the worksheet so I have a better idea of what you're looking for.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.