Database management is important
in all sectors. Database is a list of data in fields (variables) and records (cases).
Columns and rows in the Microsoft Excel worksheet are used as fields and records
respectively for record keeping, inventories, monitoring and evaluation to
convert data into information. There are many features on data input,
processing and outputs for managing database in Excel.
Input and Process
Table format in the worksheet
delineates the data entry range of cells bringing the cursor to the first field
of the following record after completion of the former record. Adding a new field
automatically comes under an array of data. The table is named such as “data”. The
name works as the source of data in the formula bar for processing and
analysis.
Dropdown menu lists the response options
that helps minimise data entry errors, example, district names are arranged in dropdown
menu in the district field. Input and alert messages can be added to inform to select
the appropriate value. Dependent dropdown menu in the next field limits the
response categories in a cell based on the value in the former field. For example,
once Kathmandu is selected from the dropdown list in the „district‟ field, only
the Village Development Committees (VDCs) or municipalities (MPs) within Kathmandu
district are listed in the “vdc” field.
Data from different sources are
connected to avoid multiple entries. For example, the data template has a field
to record district name and another field records the zone. A separate table of
district and zone is created and linked to the “zone” field that automatically
enters zone name once district is selected.
Cells can be customised to accept
only the numerical values between the assigned maximum and minimum values to avoid
Invalid data. Besides, there is a feature that identifies the already existing data
that are invalid given the criteria.
The unique identifier cells can be
customised to accept only the nonduplicating values. Besides, values from several fields can be added to
create the unique identifier. For example, the first and family names of staffs
in the list, district name and VDC name are combined to create a unique
identifier. Duplicate values can be highlighted and removed, and the unique identifiers
can be counted to identify the duplicate values.
Texts in a field can be broken
down into words and placed into several fields. For example, the full name of a
person in a field can be broken down into the first name, middle name if available
and family name. Separating family name will help analyse staffs by caste/
ethnicity. Serial numbers are auto filled to avoid typing error. Serial numbers
can be formatted to create unique identifiers. For example, if a staff dataset has
a “StaffID” field.
Serial numbers can be formatted
to appear “S1” as text on the first cell entered with number “1” and the same
format can be auto filled in remaining cells of that field. Directly writing “S1”
on a cell can also be auto filled in remaining cells, but the data value remains
as S1 not “1” as in cell format customised as above.
Date of joining an organisation, for
example, is used to calculate the duration of service as of certain time in
decimal number of years or number of years, months and days. Job duration can
be categorised as “New staff (less than 5 years)”, “Middle year staff (5 to less
than ten years)” and “Old staff (ten or more years)”.
Output
Data filtration criteria is defined.
Staffs aged 25 years or more can be filtered. Texts such as first names
beginning with “A” can be filtered. Excel calculates values that match the defined
criteria. For example, in the staff list one may be interested to know the average
age of female staffs from Kathmandu district. Here, “age” is calculated taking “female”
and “Kathmandu” as the criteria.
Frequency table counts the number
of records by response categories. The counts can be calculated in percentage. Frequency
tables can be created manually as well as using interactive features. Numeric
values can be grouped to create the frequency distribution by categories.
Data summaries and presentation can
be made in some other forms. Example, top three older and younger staffs by gender
can be tabulated to provide information for staff recruitment strategy.
Cross tables summarise data by categorical
fields, such as number of staffs by sex and ethnicity. One can choose either numeric
values or percentage figures for data summary in cross tables. The numeric values
may include number of counts, sum, average, maximum and minimum numbers. Column
percent or row percent is again a matter of choice. Cross table is updated when
the database is edited or updated with new records.
New field based on the existing fields
can be created just for tabulation purpose. For example, the staff list has age
of staff and salary fields. The salary age ratio field can be created for
tabulation purpose to know how different are the ratios for female and male
staffs. Charts are prepared to display data in graphical form using features to
highlight and graphically present data bars. Colorful icons are used to
visualise data and highlight top or bottom values.
Dashboard can be prepared using filter and connection features to link charts
on dashboard to
