In certain conditions, you might have to
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 is to installation a product catalog. follow the steps given below −
The price of a product at selling time is decided through the current price of the product.
Next, you have to set up worksheets for the Regions – East, North, South, and West, in that order with same structure.
These four worksheets should have the same structure.
Now, something editing you do in the East worksheet will get automatically reflected within the other three selected worksheets.
In the East worksheet,
The same structure appears in the other worksheets North, South and West.
To create a formula across multiple worksheets −
As we defined earlier, the price of a product is as per the product catalog that is updated on the first of every month.
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.
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 −
In each worksheet (East, North, South and West), calculate subtotals month-wise –
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.
Click the Outline Level 2. You will get all month-wise Totals.
Now, you are prepared to summarize the consequences from all the four worksheets – East, North, South and West.
The following steps will show you how to summarize data from multiple worksheets.
In the column- Total Sales, in the cell C3, type =sum(
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
The calculated value appears in the cell C3.
All the formulas in the column Total Sales appear.
This is exactly how you wanted to summarize the results from each region.
Your summarized results are ready in the Summary worksheet.
Excel Data Analysis Related Tutorials
|VBA For Excel Tutorial||Microsoft Excel Tutorial|
|Microsoft Word Tutorial||Microsoft Powerpoint Tutorial|
|Microsoft Entity Framework Tutorial||Microsoft Azure Tutorial|
|Microsoft Project Tutorial||Advanced Excel Charts Tutorial|
|Advanced Excel Functions Tutorial||Excel Dashboards Tutorial|
|Excel pivot tables Tutorial||Excel Power View Tutorial|
Excel Data Analysis Related Interview Questions
|VBA For Excel Interview Questions||Microsoft Excel Interview Questions|
|Microsoft Word Interview Questions||Microsoft Powerpoint Interview Questions|
|Microsoft Entity Framework Interview Questions||Microsoft Azure Interview Questions|
|Microsoft Project Interview Questions||Excel Data Analysis Interview Questions|
|Excel pivot tables Interview Questions||Excel Power View Interview Questions|
|Microsoft Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.