Advanced Spreadsheet Skills
This website has been created with the intention of teaching you about some of the powerful features and tools that can be utilised when designing an advanced spreadsheet.
The site is presented in sections that cover a variety of skills and tools that are needed in the development of an advanced spreadsheet.
These sections include:
Spreadsheet Design
The main design of a spreadsheet is an important section of spreadsheet creation. The effectiveness of the spreadsheet design, depending on how well or badly it has been structured, will depend if a user can or cannot understand the information presented to them.
Conditional Formatting
Conditional formatting is a technique in Excel that enables us to apply a specific condition to cells and highlight the cells that meet that condition.
Conditional Formulas
Conditional formulas allow conditions to be applied to certain cells using the IF() function.
Symbolic Naming
In this section we will be looking at symbolic reference. It is important that all spreadsheets have reference to columns and rows so they can be clearly understood by those that use it. Symbolic reference is referring to a row or column type symbolically, which means by name, rather than literally meaning by number position. The name will either be a word or phrase assigned to a specific cell. By referring to a row or column symbolically allows users to understand the meaning of cell types more easily, thus making the spreadsheet more user friendly. This also means that users can pick up the meaning of the spreadsheet and how to use it quickly allowing for example, large businesses to draft different employees in to use the same spreadsheet.
What-If Analysis
What-If analysis is the term given to the process of considering what could happen to the data held within a spreadsheet, if you were to change certain inputs. There are various ways of doing this and there are some powerful tools built into most spreadsheet software that enables a user to make controlled changes to their spreadsheet or "mock-up" various situations.
Filtering
Advanced filtering is a filtering function which Excel provides. Advanced Filtering basically does the same job as AutoFilter, however there is a little more work required to set it up. Unlike AutoFiltering where you can input the criteria via the drop down menus in the column headers, you need to input your criteria onto the worksheet directly.
Quiz
Finally, you can see how much you have learnt by taking our multiple choice quiz.
To demonstrate how these various tools and skills can be used we will use a made up table of student data for examples. See below:
