Advanced Excel Training Notes

Seite 1

Advanced Excel Training - August 27 & 28 2015

IF FUNCTION

Carries out a test

In cell type IF - Ctrl A - brings up function arguments

F4 - Inserts 2 $ signs in formula, this converts a relative figure to an absolute figure (number stays static)

Blank cell in formula - 2 speech marks "" - no information in cell

DIV/0 - use if statement to have a blank cell

Nested IF FUNCTION

More specific - up to 8 "IF" statements in a nested function

If more than 8 statements required - need to program

Average of Figures - Auto sum button & highlight all data

Colour Code - conditional formatting

Ctrl Z = Undo/clear

Validate data on workbook ie. data between 0-100

Data tab - Data validation (protect sheet & able some area of the sheet to be unprotected)

=TODAY() - populates current date in cell

Ctrl + : (semi-colon) = date stays the same

=NOW() - populates date & time

Goal Seek

Data Tab - Goal Seek - only change a single cell

Solver Solution

How to change multiple cells/input a range

Save as a scenario - Data

File - Options - Add ins

Go button (appears on data ribbon)

Max/Min = constraints

Save scenario to changed values, then restore original values

Answer report (through solver - answer/sensitivity/limits)

Pivot Tables

Try to manipulate data ie. cost of salary's per department

Ctl Shift 8 = highlight all data

Insert slicer - allows a filter

To update data - need to refresh

Button on analyse tool - refreshes automatically

Example

Stock V Repairs - pivot by years

Breakdown by Year (separate sheets)

Pivot table - LHS

Options

Show report, filter pages

Convert to % - Field settings

Show department detail - double click in data cell - NOT name

VLOOKUP

Look up value

Range

Answer

Range - true/false (logical field), if leave blank = defaults to TRUE, therefore use FALSE

Start with one cell - Type in

HLOOKUP

Horizontal Look up

Absolute values in range

Use comma, then column number then comma, false

Eg. VLOOKUP($f$23,$A$1:$E$14, HLOOKUP(E24,$A$1:$E$2,2,FALSE),FALSE)

If using text = "yes" eg. IF(F27="YES",F26*20%)

To input filter

Data Tab

Data Validation

Allow - list & select range = LIST button

Save original document - View & Custom view

New workbook - View & arrange all

Copy across to workbook = Ctrl & Drag sheet

Ctl Shift & Down arrow (selects area)

AND - Function with IF arguments - All statements have to be true

OR - Function with IF arguments - Only one condition has to be true

Advanced Filter - Criteria = exact match to headings

DCOUNT = Numbers

DCOUNTA = text/numbers - ie. anything

COUNTIF - same as database search

COUNTIFS - up to 127 criteria, same as database search

Format as Table - Total Row, allows drop down arrows

Sort by Custom list

Right click mouse - Ctrl & Enter, fills same data down ie: all MONDAY

Naming A Cell

Name box & type in name

plain english & fixed rate in function ie. fixed rate/bonus/discount

Formulas - name manage - change to 7% - associate name with reference ie. Bonus = 7% therefore can use in formula on new sheets

PC to do it automatically - Formula's create from selection

Name Range for Navigation Staff_Table_Replace

Absolute values in formula (replace $ & numbers)

Define Name - name cell only on one specific worksheet ie. scope - select sheet

Can rename the cell as "Rate" on a separate worksheet, therefore define reference to specific sheet

Same headings/labels on Multiple worksheets

Group Worksheets - all data in first sheet copies through to all sheets

Can also filter across worksheets - when already started the workbook

Circular Reference - adds totals together/duplication

3 D Calculation - SUM - Group worksheets

Trace Formula Precedents ( pre-ceeds the formula)

Double click black arrow head

See where data is feeding from

Trace Dependents

Remove arrows - takes them off

Grouping sheets to a Summary Tab

Arrange 4 workbooks on one screen - arrange all - Data - Data Tools - Consolidate

Add separate sheets - Consolidate - Add

Use Labels

If data will change highlight - Create links to Source data - If you do not want links but absolute values = DO NOT HIGHLIGHT

Select All Data - Data - Ungroup - Clear Data - to remove links & groups at LHS

Macros - recording/listing that carry out tasks

Record small steps and then create a programs

Record Macro - add new tab to worksheet/workbok

FILE - Options - Customize Ribbon - Developer

Developer

Name all worksheets

Record small steps (5 mins)

Record marco LHS

No spaces/hiphens

This will be on a Personal macro workbook.

Invisible - available all the time

Update to entire PC/Program

Description - can copy & paste to other macros

Data - Filter - Developer - Stop Recording = 1st Macro saved

Macros - add filter

Ctrl Shift 8 - Select all

Select new data - right click mouse - copy cells

Run a program with all macros enable - write in visual basic

Developer - visual basic

Modules

( ) - no arguments

Green Text is statement

FALSE = take off

Save to personal Macro workbook at beginning to allow Macro through all workbooks

Select new data - edit Macro, select visual basic button

Relative References - allows you to create a Macro in any cell

To carry out task - Record Marco - Stop Recording, therefore can input data into any cell

"formulaR1:C1" = Text in Macros

Create Personal Function

Visual Basic tab

Function DISCOUNT

Insert Function - User defined - function argument

Can use Macros to create/run - VLOOKUPS etc