Why don't you use the non-parametric Spearman's rank correlation coefficient? This would neatly circumvent the need to worry about data transformation. In essence it is almost the same as normal (Pearson's) correlation, but calculated on the ranks of the values, and not the values themselves. Since log is a monotonics function, the ranks will remain the same no matter whether or not you take logarithms of the values.
Since you are using a spreadsheet, look here.
As for your first question, the problem is I think more complex, as you would have to check whether a log-linear fit is significantly better than a linear fit. I have no idea whether this can be done simply in a spreadsheet. You could, of course, take logarithms of one of the variables and check whether the Pearson's correlation coefficent is better, but this is not really always a good idea.
I'm not an econometrician, so forgive any abuse of terminology.
From your question and further comments, you are not looking for classic correlation, you are looking for some kind of causal, or at least temporal correlation. At first, the data you have seems to be just a vector of two variables; Coke and Pepsi closing stock prices. Looking at the Pearson product-moment correlation coefficient , which is what CORREL calculates, will give you the linear relationship of one variable with the other, but no more.
If you are looking for a trigger or some other relationship, you need more information—you need some other variable or variables which affect both of your "top-level" outcomes which can explain some relationship.
You may actually already have some data, in that if you have closing prices, you probably have the closing dates as well. A first step may be to simply see if one closing price tends to lag the other, in which case some form of regression may be appropriate.
Also, and this is not based on anything authoritative outside of a hunch, so take it for what it is worth, you may wish to regress Coke on time ane Pepsi, and then Pepsi on time and Coke, and see if one or the other regressions have the company variable as significant as well as the time variable.
Your best bet, in my opinion, though would be to try and develop some database of statistics that may affect both (time, S&P close on same date, CPI on same date, spot/future price of corn (for corn syrup) on given date) and see if you can develop a model using those statistics.
Best Answer
first of all when you calculate correlations, don't just use the default correlation functions in any package, they all refer to pearson correlations. also calculate the kendall's tau and spearman's rho. (Use R).
Next for the case of stock prices, only correlation may be one property, but a more useful property is called cointegration which is used in a better manner if you consider things like pairs trading.
Next these properties in case of the financial data do not stay constant, so what you should do is divide that data into slices which could be monthly or yearly or quarterly and then calculate these values for each of these slices and then come to a conclusion as to how do the two stocks behave together.
Next calculate the cross correlation function for any two stocks but once again after dividing the prices into different windows.
Next, do not use wavelets as mentioned in one of the other answers if you do not understand them, first go through the theory before even thinking about it.
Next define the local maximum and local minimum of each of the stocks and then check out the behavior of the other stock around the point when one stock has formed the local maximum or minimum. this can be done by calculating the maximum value for a group of prices for the max and the minimum for the min. this will resemble your support and resistance analysis.
Next calculate the beta between the returns of the stocks versus one common index and then group the ones that are of similar beta.
All of this can be done very easily using R.