Use Excel's statistical functions to analyze data across worksheets

by Mary Ann Richardson | Aug 08, 2006 7:00:00 AM

Tags: Microsoft Office, Use Excel, Mary Ann Richardson, Microsoft Excel, Department06, Department01, cell, Microsoft Office Suite Excel Tips Newsletter

2 comment(s)

Takeaway: You aren't confined to one Excel worksheet when you wish to compare or analyze data. Learn how to use Excel's statistical functions to get more out of your worksheets.

You can use Excel's statistical functions to analyze data in any worksheet, but they become even more powerful when used across worksheets. Excel lets you analyze data in the same cell or range of cells on multiple worksheets within a workbook.

For example, suppose you have five departments, and you use exactly the same worksheet layout and labels to track the average sales of each department over the previous quarter. Each sheet contains headings by department (such as Department01 through Department06), and in cell H15 on each sheet, the calculated total sales for the quarter appear. If you want to obtain the average sales for each of the departments, you would create a new sheet, called Summary. To find the average, enter the following formula in one of the cells in the Summary sheet:

=AVERAGE(Department01:Department06!H15)

Excel calculates the average for all values stored in H15 on all sheets in the range Department01: Department06. If you relocate the Department06 sheet between Department04 and Department05, Excel will adjust the calculation to include the sheets for Departments01, 02, 03, 04, and 06; Department 05 will be omitted. Likewise, if you delete or add a sheet anywhere between Department01 and Department06, Excel will include all the sheets located within the range in its calculation.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

2 comment(s)