How to Perform ztest Calculations in Excel
If you know the variance or standard deviation of the underlying population, you can calculate ztest values in Excel by using the Data Analysis addin. You might typically work with ztest values to calculate confidence levels and confidence intervals for normally distributed data. To do this, take these steps:

To select the ztest tool, click the Data tab’s Data Analysis command button.

When Excel displays the Data Analysis dialog box, select the zTest: Two Sample for Means tool and then click OK.
Excel then displays the zTest: Two Sample for Means dialog box.

In the Variable 1 Range and Variable 2 Range text boxes, identify the sample values by telling Excel in what worksheet ranges you’ve stored the two samples.
You can enter a range address into the text boxes here or you can click in the text box and then select a range by clicking and dragging. If the first cell in the variable range holds a label and you include the label in your range selection, select the Labels check box.

Use the Hypothesized Mean Difference text box to indicate whether you hypothesize that the means are equal.
If you think that the means of the samples are equal, enter 0 (zero) into this text box or leave the text box empty. If you hypothesize that the means are not equal, enter the difference.

Use the Variable 1 Variance (Known) and Variable 2 Variance (Known) text boxes to provide the population variance for the first and second samples.

In the Alpha text box, state the confidence level for your ztest calculation.
The confidence level is between 0 and 1. By default, the confidence level equals 0.05 (equivalent to a 5percent confidence level).

In the Output Options section, indicate where the ztest tool results should be stored.
To place the ztest results into a range in the existing worksheet, select the Output Range radio button and then identify the range address in the Output Range text box. If you want to place the ztest results someplace else, use one of the other options.

Click OK.
Excel calculates the ztest results. Here’s the ztest results for a Two Sample for Means test. The ztest results show the mean for each of the data sets, the variance, the number of observations, the hypothesized mean difference, the zvalue, and the probability values for onetail and twotail tests.