Creating Subtotal and Removing Subtotals from list Ms excel 2007
Creating Subtotal and Removing Subtotals from list Ms excel 2007: The subtotal feature is use to mathematically summarize data by a particular field.
The summary can be of sum. Average, count, maximum, minimum, product etc. the field used in subtotal must be sorted before using the subtotal feature.
The following procedure is use for subtotal feature:
- Click on the field by which you want to subtotal. For example, click on the product or region or salesperson.
- Click on the sort ascending or sort descending. Sorting is an important steps needed by the subtotal feature. Make sure that the column you are subtotaling is sorted before using the subtotal feature.
- Choose data subtotal from the menu bar. The subtotal dialog box appears for you to define the settings.
- At each change in text box, click on the drop down arrow so that you can select the region field.
- Function text box, click on the drop down arrow so that you can select the SUM function. You want excel to add the values of the sales for each region.
- At the add subtotal to check box option, check the sales field. Uncheck any field that is not numeric. Excel cannot sum words.
- At the bottom of subtotal dialog box, the additional options define what will happen each time you run the subtotal. Once all of your settings re correct, click on OK
Removing Subtotals from list:
At any time you can remove the subtotals and return the data list to its simple column and row format.
- Choose data subtotal from Data menu.
- Click on the remove all button on the right side of the subtotal dialog box. Your list is returned o its original format.
Pivot Tables are interactive worksheet tables used to summarize data. They display information in summary form. Pivot tables divided data in a list of categories according to the requirement. They are useful to look at different views of the same data. An organization may collect a large volume of data over time. A pivot table is a useful tool for spotting patterns and trends that emerge over time. The users can also easily create pivot charts to create different graphical layouts of data.
Creating a simple Pivot Report:
The following procedure is use to create a simple pivot report
- Click on the pointer on some cell of your data list. If the pointer is outside the list, excel connot determine the boundaries of the list.
- Choose Data > Pivot Table Report from the menu bar. Pivot table wizard appears.
- Make sure that excel list or database button is selected and click on next.
- Define the range of the list.
- Click on Next.
- Now format your layout that defines the fields to e displayed in the table.
- Drag the region field to page area.
- And drag the year field to the column area.
- Drag the sales field to the data area.
- Don’t use the product filed yet click on next.
- Specify where you want the pivot the table report to start on the worksheet. The new pivot table report is displayed on new sheet.