The goal of this course is to provide you with the knowledge required to use more advanced formulas and work with various tools to analyse data in spreadsheets, such as sorting, filtering, conditional formatting and auditing. You will also organise table data and present data as charts. You will discover how Excel spreadsheets are constructed and be given a clear path on how to create templates. The course emphasises hands-on experience, with a series of self-guided exercises integrated into the training.
Please note this course contains a taster session on PivotTables. Detailed training on this subject is covered in our Microsoft Excel 2013 Advanced course.
This course is aimed at end users who have taken the following QA course or have equivalent knowledge: Microsoft Excel 2013 Introduction.
Delegates will learn how to
- Calculate with advanced formulas
- Audit work using the auditing tools
- Work with Excel tables
- Organise worksheet data so that data can be sorted and filtered
- Create and modify charts
- Apply conditional formatting
- Create Excel templates
- Create basic spreadsheets
- Select and edit data
- Perform basic formatting
- Open, close and save files
- Create basic formulas - AutoSum
- Use Insert Function to create built-in functions
- Work with absolute references
- Use the AutoFill feature
Lesson 1: Calculating With Advanced Formulas
- Work with Range Names
- Calculate Data across Worksheets
- Use Specialised Functions
- Use Statistical Functions
- Use String Functions
- Use Financial Functions
- Analyse Data with Logical and Lookup Functions
Lesson 2: Auditing a Worksheet
Lesson 3: Working With Excel Tables
- Identify Table Elements
- Create an Excel Table
- Format an Excel Table
Lesson 4: Organising Worksheet Data
- Sort Data
- Filter a List
- Summarise Data with Subtotals
Lesson 5: Charts
- Identify Charts
- Create a Chart
- Modify and Format a Chart
- Create a Chart via the Quick Analysis Tool
- Save a Custom Chart as a Chart Template
- Analyse Data Using Sparklines
- Add a Trendline to a Chart
Lesson 6: Working with Templates
- Work with Templates
- Create a Hyperlink
- Add Comments
Lesson 7: Advanced Sorting
Lesson 8: Advanced Filters
- Advanced Filters
- Use Database Functions
- Use Outlines to Organise Data
Lesson 9: Apply Conditional Formatting
- Conditional Formatting
- Modify Existing Conditional Formats
- Sort and Filter by Conditional Formats
Lesson 10: Appendix - Inserting Illustrations
- Insert Images into a Spreadsheet
- Insert Shapes into a Spreadsheet
- Group and Layer Graphics
Lesson 11: Appendix - Excel Options
Lesson 12: Appendix - PivotTables
Taster - Recommended PivotTables
Attend From Anywhere
Extended Classroom: an innovative approach to learning
Our ‘Attend from Anywhere’ courses allow you to access award-winning classroom training without leaving your home or office. We use GoToMeeting video conferencing software by Citrix. Before you book you should:
- check to ensure you meet the GoToMeeting system requirements
- run a test meeting to ensure the software is compatible with your firewall settings (if this link doesn’t work you should adjust your settings or contact your IT department about permitting the website)
- make sure you have a compatible webcam if you are doing any ITIL or BCS Specialist exams, as visual ID checks will be required (click here to test if your hardware is compatible)
What will it be like?
- Just before the course begins you will be able to launch the software, dial-in to the classroom and familiarise yourself with the interface and how the virtual interactions work.
- The course will be split into multiple sessions, with short breaks in between so you can stay focussed and refreshed.
- Throughout the course the learning professional will use an electronic whiteboard, which will transmit all the notes directly to your screen.
- You can ask the learning professional a question at any time, either by simply speaking aloud through your microphone or by clicking the virtual ‘raise-a-hand’ button on the interface.
- Towards the end of the course there will be plenty of time for detailed Q&As with the learning professional, just as if you were physically in the classroom.
- Following the course you will be asked to complete a course evaluation form, which will allow you to give detailed feedback on your experience and help us to make future improvements.
- For four weeks after the course has finished you will have on-demand access to helpful videos on the subject matter, and we may send you useful emails reminding you of the ‘Key Learning Points’.
Frequently asked questions
You will need an internet-connected computer and a microphone to interact with the trainer. Two monitors are recommended; one to stream the video from the classroom and the other to display the interactive interface.
We use leading Citrix technology and our classrooms are specifically optimised to improve sound quality for remote attendees. We also send out the equipment you need in the post (headset and manuals) and contact you to test everything is working a week before the course starts.
Will the remote attendees feel excluded?
Our trainers are specially trained on how to interact with remote attendees and our technology allows them to take over remote PCs. Our remote labs ensure all participants can take part in hands-on class exercises wherever they are.
It is too expensive
Feedback we receive is that organisations and individuals actually save money by booking this type of course when they factor in the costs associated with in-person training such as travel costs and expenses such as hotels, food and childcare.
How do I take the exam remotely? You can take your exam via the Proctor U online exam body. You need to book it before the course begins and switch on a webcam to enable invigilation and show photo ID.
You can take your exam via the Proctor U online exam body. You need to book it before the course begins and switch on a webcam to enable invigilation and show photo ID.