User:Muffingg/Excel

Course Overview

 * Need to have 75% in each in order to pass the module

EXAM (50%)

 * consists of 11-12 questions
 * conditional formatting
 * formulas
 * Average: 85%
 * Open book (but most likely it won't help)
 * Probably just one question about charts
 * Questions will not be theoretical but all about actual actions within Excel

PROJECT (50%)

 * To be submitted by Friday evening
 * Average: 90%
 * Does have VBA in it
 * Big table with data from different tables
 * Algorithm based on which rule to separate people from different companies into different tables containing certain company's people
 * All formulas need to be structured
 * Otherwise we would lose 10% of the marks

General Excel Notes

 * Modellers and Forecasters often use a separate worksheet for input values (Inflation Rates etc)
 * List of all created cell and table names can be viewed in Formulas > Defined Names > Name Manager

Cell Referencing

 * CTRL + D to copy a formula down (D = down)
 * CTRL + R to copy a formula across (R = right)
 * Dynamic referencing is the one that changes as you drag a formula
 * Absolute referencing remains the same (with % sign)
 * Structured referencing works in tables
 * Ways in which to apply a formula to all cells:
 * CTRL + D and CTRL + R
 * Drag the bottom right corner
 * Select all cells, enter the formula and CTRL + ENTER
 * Across sheets references include ! after the sheet name
 * Never need to manually write these out, but simply opening the sheet and clicking the cells should do

Structured Referencing

 * Structured referencing is the name of the referencing in tables
 * Table Name Rules
 * It can't have special characters or spaces
 * Have some sort of convention, e.g. start with 'Tbl'
 * Using the Name field at the top left of the workbook, doesn't rename the table
 * If a column contains a space, the reference needs to have another square bracket around
 * e.g. [@[Hours Worked]]
 * Not having a @ deals with them as an array, so in some cases it will still give the same results
 * Absolute structured referencing:
 * [@[Hours Worked]:[Hours Worked]]*[Rate]
 * Hours worked does not shift, Rate does
 * Naming a cell can be helpful when a column or row uses a multiplier for example
 * It does not need to be absolute when this is used

Functions

 * Functions return one value
 * Some return an array of values, but that is an exception
 * Accept multiple inputs
 * In the input box, anything that doesn't have a square bracket ( [] ) around it, is a required input arguments
 * Squared brackets are optional arguments
 * '...' at the end means that infinite number of arguments can be entered

Example Functions

 * Count - number of cells that have a value
 * Counta - number of all cells
 * Countblank - number of blank cells
 * Sum - sum of all values in the selected cells
 * Subtotal - this only applies the particular formula on the visible cells