Excel Advanced excel07

Topic

Content

Review

Exercises will be uploaded on each PC in order to review functions covered on past courses. This is a good way for the Tutor to get a feel for the class level.

Paste Special

Use to paste values not formula, transpose (turn around) data on the sheet, add, subtract and multiply figures by a set figure.

Conditional Sum

Analyse data in order to get a breakdown of facts and figures.

Functions

If, PMT, Min, Max & Ave, Mixed References.

Consolidate Data

Take four quarterly sheets of data and combine into one year end sheet

Work across Sheets

Create formula across several worksheets, traps to watch for.

Multiple If Statements

Nesting If statements up to nine times in order to test a cells result.

Iserror Function

When you want to prepare a Worksheet to send to someone, error messages appearing on the sheet can look very unprofessional.  Using the If and Iserror together can prevent this from happening.

Protect Worksheets

After designing a sheet for someone else to fill in the data, you need to protect your hard work by preventing anything from being deleted.

DataBase Functions

Naming Ranges to protect data from being entered incorrectly, outlining your Data

VLookup

Use the VLookup or HLookup functions to help get information from another hidden sheet.

Freeze Panes

Lock important information to be seen on the screen at all times

Today Function

Calculate a person’s age using this function together with Customised Formatting of the Columns.

Filter Data

Use the custom filter to filter your data using conditions, equal to, greater than, less than.

Subtotal

When asked to produce a report showing total figures from your Database, this feature is the most amazing and fast way to report your data.

PivotTables

When you have a very large Database and find you are constantly being asked to produce subsets of data from the data, using the PivotTable enables you to extract and analyse data with just the touch of a button.  If you don’t like what you see, you take it apart and redesign the table again until you get the picture you require.

Nesting If & And’s

You want to test the results in two columns at the same time in order to get a result, using the nested If, And, or Or statements can be extremely beneficial. More to the point, nesting several of these statements together, up to nine, can make getting results as easy as just asking a question.

Design Chart Template

Design your own colour scheme and chart type.

Simple Macro

Write a simple macro to create a Landscape Page, Include Header and Number Pages, Then assign a button to activate the macro onto the toolbar. A comprehensive knowledge of Visual Basic programming language is required to write more advanced macros - Not covered on this syllabus.