Microsoft Excel Drop-down List


Background

This portfolio artifact explains how I used a spreadsheet and three specific features in Microsoft Excel, macros, drop-down lists, and formulated cells. The screen capture shown below was created for a client who is an Administrative Assistant at a private elementary school.

The client was overwhelmed by her job and hired me to help. On average, the client worked between 60 and 65 hours weekly just to keep up. The client was exhausted, stressed, and afraid that she might lose her job.

Problem

I met with the client who explained all aspects of her job duties (recording tuition payments, sending out monthly statements to parents, late payment charges, after school care charges, and tuition discounts) along with others that do not relate to this example. The client juggled five spreadsheets, one for each job duty. Not only did her approach consume a significant amount of time, it also resulted in many errors.

Solution 

Spreadsheet with Macros

I created a macro that contained all of the essential categories including student name, address, medical conditions, payment received, amount paid to date, late charges, and discounts with drop-down lists, other macros, and formulated cells. Other macros generated weekly and monthly forms and reports sent to parents, teachers, the administration, and finance department.

Macros reduce the time needed to create spreadsheets and the weekly and monthly forms and reports. This increased efficiency and reduced the mistakes and oversights.

Spreadsheet with Drop-down Lists 

I created to help the client was a spreadsheet that performed all of the tasks and information as the client’s five spreadsheets. By creating drop-down lists, frequently used data such as academic program (grade level), percentage of tuition discount, and enrollment in the aftercare program can be quickly and easily selected.

Drop-down lists were another way to increase the client’s job efficiency and reduce mistakes. 

Spreadsheet with formula

The client knew how to use simple formulas, but the spreadsheet I created for her contained more and more complex formulas that automated some tasks that the client used to do using a calculator.

By creating several formulated cells, I significantly reduced the amount of time the client spent manually calculating figures. Formulated cells such as “Outstanding Balance” and “Amount Paid to Date” increased efficiency and accuracy. 

Creating drop-down lists and other functions in MS Excel

This significantly increased efficiency and reduced error rate

Results

The use of macros, drop-down lists, and formulated cells have profoundly changed the client’s work experience. Together these three functions helped the client in the following ways:

  • eliminate the need to work overtime.
  • significantly increased her job efficiency and profoundly reduced errors.
  • less job stress, and
  • job security.

 I published a how-to article explaining how to create drop-down menus using Microsoft Excel.

Click here to learn more about me.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s