Example Excel Workbooks Without Macros
The following workbooks are all examples of my work, demonstrating a variety of diverse tasks that can be solved with Excel. None of the workbooks use macros, but still manage to perform complex tasks by the using a variety of functions and structured design.
| Project Planner |
By entering the start and end date of each task, a simple Gantt chart is displayed, including a calculation of resource requirements per day. |
| Cup Tournament |
Allows teams to be tracked through a cup tournament similar to the world cup. An initial group stage puts the teams on the group tables with their position dependant on win/lose/draw, and goal difference. 1st and 2nd place from each group progress to a knockout stage. |
| Invoice Creator |
Automatically creates invoices for customers. One sheet contains a list of products with product codes, and one contains customer addresses with customer codes. By entering a customer code and the product codes/ quantities of what was purchased, a formatted invoice with all necessary tax is created ready for printing. |
| Charts |
Demonstrates a variety of charts that require multiple techniques to graph complex data. Includes a scrolling chart, multi-stacked charts, geographical data plotted on a map, population pyramid, climate chart, gantt charts, colouring negative values, drawing an average line on a chart, displaying the rate of change of a value, complex stock chart, and others. |
| Employee Timesheet |
A monthly timesheet allowing employees to record their time against different projects, holiday, training etc. The formatting of the sheet changes depending on the month to allow quick data entry, and conditional formatting highlights errors and displays meaningful error messages. A summary is calculated and displayed, and the whole month fits neatly onto an A4 sheet. |
| Sales Analysis |
Sales for each person per day are recorded. An analysis sheet allows the entry of values required to reach a particular grade. The results grid is then couloured to highlight the different grades met per person per day. Analysis information is displayed such as the max/min/average per day and person, best and worst day of the month, and the ranking of each sales person. |
| Password Generator |
Creates random passwords following options provided by the user e.g. length, use upper/lower case, punctuation, numbers. Also provides details on the strength of the created password and its theoretical resistance to a brute force attack. |
| Postage Rates |
Calculates the cost of sending a parcel depending on weight/method/destination. Price data is stored on a data sheet and copied onto the main sheet dependant on method chosen. The calculated price is highlighted on the rates matrix. Error messages are displayed if any invalid data is entered. Rates/methods/zones are all easily edited. |
| Climate Chart |
Data is formatted and graphed on the main sheet, but is stored on seperate data sheets which are easy to expand. The user chooses what to display by way of a drop down list and scroll buttons. The graph combines bar and line, allowing simultaneous display of rain and temperature data. The axes are fixed to allow direct comparision between locations and years. |