Boost excel spreadsheet with programming

Definition

In daily, we always use excel spreadsheet to record the data, also analyze the data to get useful messages.

Data Analysis in excel can be operated in various way, from rearranged data tables to different kind of charts.

However, do we imagine doing these jobs by using code? It may be difficult but it’s the rocket engine to excel files.

A spreadsheet application is a spreadsheet file that is designed so that someone can perform useful work.

You may have dozens or hundreds of spreadsheet files, but obviously most of them aren’t designed as applications.

Creating spreadsheet applications for many different usage levels, from autofilling to complex GUI application.

A good spreadsheet application should:

1. Enable the end user to perform a task that humans are difficult to do well
2. Provide the appropriate solution to the problem
3. Produce accurate results and is free of bugs
4. Use appropriate and efficient methods and algorithms to accomplish its job
5. Trap errors before the user is forced to deal with them
6. Deny the user to delete or modify important components accidentally
7. Have a clear UI so that the user always knows how to proceed
8. Have well-documented formulas, macros, and user interface elements with help
9. Be portable and to run on any system that has the proper software

Procedure

To develop spreadsheet, the following activities are performed in sequence:

1. Requirement: Determine the needs of the users
2. Planning: Ideate an application that meets these needs
3. Design: Determine the most appropriate user interface
4. Implementation: Create the spreadsheet, formulas, macros, and user interface
5. Testing: Test and debug the application
6. Security: Make the application bulletproof
7. Makeup: Make the application appealing and intuitive
8. Changelog: Document the development progress
9. Readme: Develop user documentation and help systems
10. Publishing: Distribute the application to the user
11. Growth: Update the application when necessary

Data analysis

All spreading applications are surrounding one topic - Data Analysis to support decision-making.

The concern points of data analysis project can be broken down by Six Sigma DMAIC method like:

Define: Is a data analysis really worth the effort and how should you structure your project?

Measure: What special points do you need to consider when selecting the data and what is the obstacles?

Analyze: What aspects should you be aware of when you carry out a data analysis?

Improve: How can the results of your analysis be implemented in efficiency-enhancing solutions?

Control: How can you evalutate the efficiency of your solution and predict the potential benefits?