In Excel, if I get a value that is less than alpha (i.e. I get a value near zero) for the CHISQ.TEST function, does this mean that the my observed data follows the expected distribution or is it the other way around?
CHISQ.TESTreturns the probability that a value of the $\chi^2$ statistic at least as high as the value calculated by the above formula could have happened by chance under the assumption of independence.
This means that
CHISQ.TEST is measuring the upper tail, and so a value near zero from
CHISQ.TEST means that the $\chi^2$ statistic is relatively large, with the observed values not close to the expected values. The older
CHITEST was similar.
chisq.test in Excel returns the p-value for a chi-squared test of independence.
The p-value carries its usual meaning -
In statistical significance testing, the p-value is the probability of obtaining a test statistic result at least as extreme as the one that was actually observed, assuming that the null hypothesis is true.
In the case of a chi-squared test of independence, it's the probability of obtaining a chi-squared statistic at least as large as the observed one, if the two categorical variables were independent.
Very low p-values therefore indicate that the data are inconsistent with independence in the population.