Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a large database of 20 or so assorted measurements covering a long period of time. Some measurements are available for the full range of dates while others are only available over some lesser range. I just have a list of dates in column one and the rest of the data placed according to the dates that they are available. I am looking to plot many graphs of measurements side by side looking for correlations and other relationships. Each time I make a graph I need to highlight that range of dates for which all the columns in the graph have data available. This seems like a rather agonizing process involving ridiculous amounts of scrolling hunting for the start and end points of the data- also quite often I forget which data belongs in which column since the column label is written on the first row only.

What I would really love to do is simply highlight the columns I'm interested and then have some clever macro/in-built-function/add-on to modify my selections so that they are restricted to only the date ranges where all the columns have values. Am I asking too much?

share|improve this question
up vote 0 down vote accepted

First, you might consider using defined names, sort and lookup functions.

Second, I'm going to suggest that perhaps you're using the wrong tool. This is the type of thing that R, S, SAS, etc. all excel (heh) at. They also have the added advantage of being much, much faster for performing transformations or computation intensive analyses. With all of these tools it's easy to pull out and plot/analyze specific ranges of data or plot everything on one big messy plot, perform multivariate regressions or just do your simple linear regression. I use R because that's the direction biological research is headed, it's free, and the community is great.

As @Wil suggests, using a database is probably a good idea if your dataset is huge, but up to garganutan I suggest SQLite over MS SQL server, MySQL or PostgreSQL. It's a flat file, it works nicely with R and it's not a nightmare to setup.

share|improve this answer
I don't understand how "sort" affects what is highlighted. – Mick Jan 29 '10 at 9:24
@Mick, sort would be more to do things in a manual manner. You can just sort your dates, then select all of the columns and plot them (missing values should just be missing especially if you use an xy scatter plot. but again, I strongly encourage you use the right tool for the job, and excel just isn't that tool in this instance. – Tyler Jan 29 '10 at 18:32

I think you may want to look at using a mixture of Sort and selection highlighting.

You can highlight everything then use the sort command to choose your date row, and selectively highlight single cells using the Ctrl key. (And use Shift to select large selections in combination with Ctrl).

Lastly, if you are going to be producing a lot of graphs or this is a long term project, you may want to look at using something like SQL server to store the data, then you will be able to manipulate it much better - but this is overkill for smaller projects.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.