Page 15 - Code & Click - 8

2 Databases in Excel

Pre-Processing
Pre-Processing
• Displaying Subtotals • Data Validation
• Goal Seek • Scenario Manager

A database is an organized collection of structured information, or data, typically stored electronically
in a computer system. Data within most common types of databases is typically organised in rows

and columns. The data can then be easily accessed, managed, modified, updated, controlled, and
organised. Excel provides tools for summarising data, maintaining integrity and consistency of data,
perform goal-oriented computations as well as perform What-if analysis on data to detrmine how
the data can be manipulated.

DISPLAYING SUBTOTALS
You can automatically calculate subtotals and grand totals in a list of data for a column by using the
Subtotal command.
If the workbook is set to automatically calculate formulas, the Subtotal command recalculates the
subtotal and grand total values automatically as you edit the detail data. You can choose the function
to apply to subtotals and grand total values from one of the functions – Sum, Count, Max, Min,
Average, and Product.

Subtotals feature works best on data with column headings. The data in the range must be sorted on
one or more columns so that the grouping of values is done correctly.

To apply subtotals on a list of data in Excel:

1. Create the worksheet as shown in the image.

1 2

2. Sort the data on the basis of the column(s) you want to apply subtotals to.

13