Dr. Mark Gardener


Providing training for:

  • Ecology
  • Data analysis
  • Statistics
  • R The statistical programming language
  • Data management
  • Data mining

Tips and Tricks - for R and Excel

On this page you can find tips, tricks and hints for using both R and Excel. At the end of each tip there are links forwards and backwards as appropriate. There is also an index of R tips and an index of Excel tips.

For most analytical purposes the combination of Excel and R is unbeatable! Excel is great as a data management tool and for preparing data for analysis. You can also use it to get an overview of your data or to make simple (and not so simple) graphs. R is an analytical "swiss army knife" and can carry out a mind-boggling array of analytical routines as well as producing great graphics.

Tips & Tricks for R | Tips & Tricks for Excel | An Introduction to R | MonogRaphs | Writer's Bloc

Reversing the axis of an Excel chart


Reversing the axis of an Excel chart

Sometimes you want to make a plot that reflects the "real" situation rather than a plain "mathematical" one. An example might be temperature and depth of the ocean. You ought to plot the temperature on the y-axis and the depth on the x-axis but it would be nice to visualize the change in termperature with depth as if you were looking down a profile of the ocean. Your first attempt makes a scatter plot but the vertical is upside down with the deeper values at the top.

A Graph to be reversed

This is because the values are positive. You can easily reverse the axis rather than altering the values to negative. Click once on the chart and go to the Chart Tools > Design menu. Now you can use the Axes button to format the axis (you can also right-click on the axis and select Format Axis but you have to click in exactly the right place). When the Format Axis menu appears you can alter the appearence using the Axis Options section.

Format Axis menu

If you select the box that says Values in reverse order the values will... reverse. The x-axis will still be at the top so you might want to alter it using the Horizontal axis crosses: section. Set the x-axis to cross at the Maximum axis value and it will move to the bottom (the values are in reverse order now so the max. value is at the bottom of the y-axis).

Graph with reversed y-Axis

Now your chart is the way up you intended. Here I added smoothed joining lines to help visualize the pattern of temperature with depth. Richt-click on a data point and select Format Data Series... to bring up the options.

Top << Previous Tip: Adding data series to an Excel chart >> Next Tip: Roman numerals
Follow me...
Facebook Twitter Google+ Linkedin Amazon
Top Tips & Tricks Home Index of R Tips Index of Excel Tips  
More links:

An introduction to R

See my Publications about Excel, R, statistics and data analysis Courses in R, data analysis, data management and statistics Visit the R Project website

See my Publications about statistics and data analysis.

MonogRaphs: random topics in R

Writer's Bloc – my latest writing project includes R scripts

Courses in data analysis, data management and statistics.

Top Home
Data Analysis
Contact GardenersOwn Homepage