Working with Multiple Sheets - Excel Data Analysis

What is Working with Multiple Sheets?

In certain conditions, you might have to

  • Set up workbooks in which several worksheets have a comparable format or structure.
  • Get data for these worksheets from some other worksheet.
  • Summarize the consequences from these worksheets into a precis worksheet.

For example, you might need to track sales data region clever and month wise in separate worksheets. The price of each product is taken from the product catalog that is set up throughout the company in a separate worksheet. finally, you have to summarize the results across all the regions into a precis sheet.

In this chapter, you will learn how you can accomplish this in easy steps. you are going to summarize results from April 2015 to March 2016, i.e. financial year 2015-16.

The First Step

The first step is to installation a product catalog. follow the steps given below −

  • Start with a blank workbook.
  • Set up product catalog worksheet with products and prices.
  • Name the worksheet Product Catalog.
  • Assume the catalog is revised on the first of every month.
  • Provide a place holder for last updated on.

The price of a product at selling time is decided through the current price of the product.

Working with Multiple Sheets

Multiple Worksheets with same Structure

Next, you have to set up worksheets for the Regions – East, North, South, and West, in that order with same structure.

  • Add 4 blank worksheets.
  • Name the worksheets East, North, South and West.

Working with Multiple Sheets

These four worksheets should have the same structure.

  • Click the tab East. The East worksheet opens.
  • Press the shift key and click on the tab West. All the 4 tabs will be selected.

Working with Multiple Sheets

Now, something editing you do in the East worksheet will get automatically reflected within the other three selected worksheets.

In the East worksheet,

  • Add the column headers – S. No., Month, Product, price, No. of units, total amount.
  • Add the S. No., Month April and the four Product Names.
  • Format the table.

Working with Multiple Sheets

The same structure appears in the other worksheets North, South and West.

Creating a Formula across Multiple Worksheets

To create a formula across multiple worksheets −

  • Define names for the price values of the products in the product catalog worksheet.
  • Set the Scope as Workbook for all the names.

Working with Multiple Sheets

  • Over again choose all of the four worksheets – East, North, South and West.
  • In the East worksheet, for every product, in the price column, provide the formula because the price value name.

As we defined earlier, the price of a product is as per the product catalog that is updated on the first of every month.

Working with Multiple Sheets

  • Repeat the same steps for each Month

Working with Multiple Sheets

Therefore, for the worksheets for the regions East, North, South, and West, you have successfully set the same shape and placed the price data for every product based at the month from product catalog worksheet.

The Product Catalog can be in another Workbook too.

Calculations in the Worksheets

The next step is to fill in the data of No. of units offered for each Product in every Month and in every place. Therefore, you need to work separately on these worksheets.

For every region, for each product −

  • Fill No. of units sold.
  • Calculate the corresponding total amount as price*No. of units.

Working with Multiple Sheets

In each worksheet (East, North, South and West), calculate subtotals month-wise –

Working with Multiple Sheets

Note − you can use Subtotal on a single worksheet but not on multiple worksheets. Hence, you have to repeat this step for North, South and West worksheets.

Working with Multiple Sheets

Click the Outline Level 2. You will get all month-wise Totals.

Working with Multiple Sheets

Now, you are prepared to summarize the consequences from all the four worksheets – East, North, South and West.

Summarizing Data in Multiple Worksheets

The following steps will show you how to summarize data from multiple worksheets.

  • Upload a worksheet and name it summary.
  • Create the structure for precis worksheet.

Working with Multiple Sheets

In the column- Total Sales, in the cell C3, type =sum(

Working with Multiple Sheets

  • Select the worksheet East.
  • Select the cell G7.
  • With East tab pressed, click the tab West.
  • The tabs East to West get selected.
  • The formula in the formula bar appears as

=sum(‘East:West’!G7)

Working with Multiple Sheets

Note that you are still in the East worksheet. Press Enter.

You will be in the Summary worksheet. In the formula bar, you will see the formula as

=SUM(East:West!G7)

The calculated value appears in the cell C3.

Working with Multiple Sheets

  • Copy the formula to the cells C4 to C14.
  • Click Show Formulas in the Formula Auditing group under the FORMULAS tab.

All the formulas in the column Total Sales appear.

Working with Multiple Sheets

This is exactly how you wanted to summarize the results from each region.

  • Click in the cell C15.
  • Type =sum(C3:C14)

Working with Multiple Sheets

Your summarized results are ready in the Summary worksheet.

Working with Multiple Sheets

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel Data Analysis Topics