Home » Questions » Computers [ Ask a new question ]

higlighting ranges of rows in excel

higlighting ranges of rows in excel

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.

Asked by: Guest | Views: 415
Total answers/comments: 1
Guest [Entry]

"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."