Have you already got the basics of Excel and want to raise your skills up a notch? Then this course progresses you to the next level of Excel knowledge to discover how else Excel can help you. We explore splitting (then linking) a spreadsheet problem over more than 1 spreadsheet, find out how Excel can help in decision making, and progress to making the spreadsheet secure and tamper proof.
Who is this for?
If you have completed the Everyday course or have equivalent skills / knowledge that's a great start; if you have not done any Excel training but you can construct formula using + - / * then that's fine. Ideally, you'll also be a regular Excel user so you can put your knowledge into practice immediately.
Once you have completed the course, you'll have the knowledge and ability to create secure spreadsheets for not only yourself, but to give to others, create formulas that make decisions, and a good grounding in the data analysis features.
What does it cover?
The course leads you into creating more complex spreadsheets, developing skills to create sheets for others to use. You'll learn how to link spreadsheets, protect them, apply logical formulas and create lookups. The latter part of the day covers initial data analysis skills such as filtering and sorting, sub totals and pivot tables
How long is it?
1 day or can be split into several sessions if required
Where is it run?
On your premises or via one of the training centres I use
Simplify your formulae
Understanding named ranges
Creating and modifying a range
Log and document your named ranges
Using & linking multiple worksheets
Adding, removing & renaming worksheets
Colour coding your sheets
Copying and moving sheets
Linking worksheets together
Point & click
Pasting a link
Grouping worksheets
Creating formula across worksheets
Spreadsheet decision making
Why and where to use this?
Using IF() function to determine a decision
Adding more criteria – introducing AND() & OR()
Lookup data from other sheets
Start simple – using LOOKUP()
Introducing VLOOKUP()
A little more error checking
Decoding Excel’s error messages
Using the IFERROR() function
Using IF() to suppress errors
Worksheets for others – techniques of robustness
Control the input with data validation
Keep your formulas safe - applying worksheet protection
Good worksheet design considerations
Adding a comment to a cell
Using Excel as a database
Rules for a good database
Sorting on single and multiple levels
Filtering with the simple filter
Specific text, date and number criteria
Using Advanced Filtering
Setting the criteria
Copying to another location
Creating a data table
Summarising data
Using SUMIF(), COUNTIF(), AVERAGEIF()
Using simple sub totals
Creating a pivot table
Updating the results
Creating a pivot chart
Some tips and tricks
Using the paste special features
Basic worksheet auditing tools
Moving columns and rows
