Family Tree

Family Tree

About Me

My photo
Kathmandu, Bagmati Zone, Nepal
I am Basan Shrestha from Kathmandu, Nepal. I use the term 'BASAN' as 'Balancing Actions for Sustainable Agriculture and Natural Resources'. I am a Design, Monitoring & Evaluation professional. I hold 1) MSc in Regional and Rural Development Planning, Asian Institute of Technology, Thailand, 2002; 2) MSc in Statistics, Tribhuvan University (TU), Kathmandu, Nepal, 1995; and 3) MA in Sociology, TU, 1997. I have more than 10 years of professional experience in socio-economic research, monitoring and documentation on agricultural and natural resource management. I had worked in Lumle Agricultural Research Centre, western Nepal from Nov. 1997 to Dec. 2000; CARE Nepal, mid-western Nepal from Mar. 2003 to June 2006 and WTLCP in far-western Nepal from June 2006 to Jan. 2011, Training Institute for Technical Instruction (TITI) from July to Sep 2011, UN Women Nepal from Sep to Dec 2011 and Mercy Corps Nepal from 24 Jan 2012 to 14 August 2016 and CAMRIS International in Nepal commencing 1 February 2017. I have published articles to my credit.

Friday, February 16, 2018

Database Management in Excel

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
visualise filtered values in different interactive charts.

No comments:

Post a Comment