Online Advanced Microsoft Excel Course
Filtered
Summary
Overview
Microsoft Excel provides a range of advanced functions that greatly extend its capabilities. Powerful analysis, forecasting, automation and presentation techniques offer wide-ranging business benefits for members of staff that have learnt to harness them.
Our CPD Certified Advanced Excel course will take your staff’s Microsoft Excel abilities to the next level. It demystifies the most complex areas of Excel using written text, screenshots and 160 videos.
By the end of the course your team will be proficient in the more complex functions and formulae used in Excel. They'll be able to apply these to their work to deliver more in-depth analyses and become more efficient in their spreadsheeting. The course is available for PC versions 2007, 2010, 2013 & 2016 and the areas covered include Advanced Range Names, Array Formulae, Visual Basics and Macros, PivotTables and PowerPivots.
Description
Learning outcomes
- Use advanced graphics and presentation techniques to create impactful slides.
- Increase interactivity and automate your spreadsheet with macros and Visual Basic.
- Solve complex problems with superpower functions.
- Use PivotTables and PowerPivots to turn raw data into refined information to support key decisions.
Section 1: Efficiency and Risk
Unit 1.1 - Templates
Get a head start with templates. Set up an Excel template to save you an hour each time you create a new workbook. Create new default workbook and worksheet templates, learn how modular templates can make your spreadsheets more consistent and much quicker to set up. Your Excel, your way.
Unit 1.2 - Increasing Efficiency and Reducing Risk
Make spreadsheets more efficient and less risky by identifying how to build in checks and controls from the outset. Identify key techniques that reduce risk and increase automation and efficiency. Introduce standards to help your team and documentation and review tools.
Unit 1.3 - Data Validation
Understand extended uses of Data Validation, work with validation formulae. Understand lists and lists that depend on other list selections as well as other methods of tracking down invalid entries
Section 2: Advanced Techniques
Unit 2.1 - Functions with Superpowers
Finding the right functions: with hidden powers – MOD() for patterns, OFFSET() for simple choices, INDIRECT() to manipulate formulae and identify how references to ranges of cells adapt automatically for new data
Unit 2.2 - Array Formulae
One formula, one million calculations: SUMPRODUCT() - all the gain of an array formula with less of the pain
Unit 2.3 - Tables
Why Tables are so much more than just a new format. Make your spreadsheets more automatic with a single command. Understand table formulae and instant readability
Unit 2.4 - Advanced Range Names
Use the same name on different sheets using the Name Manager. Understand how to insert names in formulae – the efficient inclusion of Names, the use of intersections. Identify how to allocate a formula directly to a Range Name and why you might need to
Unit 2.5 - What If Analysis
Use Excel to help you make better decisions. Use Goal Seek to find where you need to start to get where you need to go. Use an Excel Data Table to calculate dozens of possible outcomes. Create and manage alternative scenarios. Make more profit or incur less expense by using Excel Solver to identify the best solution
Unit 2.6 - Problem Solving
Calculations that make decisions – understanding True and False. What Boolean Logic is and why it’s useful in practice, combining logic and arrays to solve complex problems
Section 3: Excel Interactivity
Unit 3.1 - Form Controls
Use Form controls to make life easier for users. Use a Spin Button to choose a value easily and an Option Button to choose with a single click. Also covers choosing from lists
Unit 3.2 - Visual Basic and Macros
Create macros by writing Visual Basic code. A macro that performs one or more actions on selected cells. Write your own Excel functions with VB code. Understanding volatile functions. Trigger a macro when a particular cell is changed. Handling errors elegantly. Exchanging information with VB code. Displaying a Message box. Asking for user input using an Input box. Create an Excel form with a List box containing values from a range of cells. Sample VB projects: an automatic index to sheets, printing selected ranges. Avoiding macros when they’re not really necessary
Section 4: Spreadsheet Impact
Unit 4.1 - Conditional Formatting
Beyond simple Conditional Formats. Basing conditions on a formula and deciding whether a batsman is out or not, choose currency symbols for a whole sheet by changing a single cell. Getting your rules in the right order and knowing when to stop. Graphical Conditional Formats – the detailed options. Using invisibility to your advantage.
Unit 4.2 - Charts that Inspire
What makes a good chart – is it really a 6.5 cucumbers? Simple steps to make your charts clearer. Why small can be better than large. Are pie charts evil? Why 3D charts can be 50% worse. Mixed chart types, trendlines and projections. Advanced chart techniques: break-even lines and waterfall charts. Pictures in chart columns
Unit 4.3 - Sparklines
In-cell charts – showing 12 times as much information in the same amount of space. Careful with that Axis. The different types of Sparkline: lines, columns and win loss. Sparklines based on a dynamic data range
Unit 4.4 - Graphics Tricks and Techniques
Further graphics tips and techniques. Taking dynamic pictures with the Excel camera. Use the Excel camera to combine areas from multiple sheets on the same sheet of paper. Formatting Excel Camera pictures. Power View – using the Excel 2013 data visualisation add-in including plotting values on maps and ‘playing’ bubble charts. Using 3D Map to create videos of animated visualisations of results by location
Section 5: Turning Data into Decisions
Unit 5.1 - Working with External Data
Identify the Get External Tools, understand relational databases in ten minutes to liberate your data, and create relationships within Excel using the Excel 2013 Data Model
Unit 5.2 - Advanced Uses of PivotTables
Use advanced PivotTable techniques to do more with your data, such as Calculated Fields and Calculated items. Use PivotTables as the calculation engine behind management reports, GETPIVOTDATA() and CUBE formulae to create flexible reports, as well as Excel 2016 slicers & timelines. Make your PivotTables more interactive, work with PivotCharts, and build interactive dashboards using PivotTables and Slicers.
Unit 5.3 - PowerPivot
Understand the Pivot add-in and PowerPivot data tools. Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX). Use DAX in table columns.,DAX to create new measures and calculated fields. Understanding advanced DAX expressions – functions that combine calculations and database technique, and the Time Intelligence DAX functions – why you need a table containing all possible dates
Unit 5.4 - Reporting with PowerPivot
Creating powerful reports with PowerPivot: Hierarchies, Perspectives and Sets, sdding Key Performance Indicators (KPIs) to a Power Pivot report. Creating CUBE formulae to report on your data in just the way you want to. Use all these techniques to turn millions of rows of data into a dashboard that supports better decisions
Section 6: Course Plenary
Unit 6.1 - Course Plenary
Revise what you have learned.
About the Author, Simon Hurst (BA ACA)
Simon Hurst is a Chartered Accountant and has been involved in computer software for 26 years during which he has provided a wide range of training services to professionals of all levels. His expertise lies in all the main Microsoft applications and he's been running hands-on Excel training courses for over 20 years, dealing with all aspects of Excel from basic competence to advanced data analysis. He is able to bring these years of practical experience to our Excel and Advanced Excel courses, ensuring that the courses explain advanced concepts clearly and concisely, and with real, practical applications and even the occasional joke.
Who is this course for?
- For expert Excel users looking to take their proficiency to the next level.
- Accountants, bookkeeppers, teachers, salespeople, managers. The list just doesn't end with Excel.
Requirements
- PC with a full version of Microsoft Excel (any version from 2007 to 2016 or Microsoft 365).
- Not a requirement but a benefit to have some work-related projects to which you can readily apply what you learn.
Questions and answers
do you have offices in Uganda?
Answer:Good afternoon. Thank you for contacting us. We are based in London UK, Unfortunately, we do not have offices in South Africa, The course is 100% online and although we recommend completing on a computer, it is available on any device as long as you have a good internet connection. Please let me know if you have any additional questions os we may assist. Happy learning!
This was helpful.Do you provide face to face classroom trainings to a group of individuals from the same company?
Answer:Good afternoon. Thank you for contacting us. The course is 100% online and although we recommend completing on a computer, it is available on any device as long as you have a good internet connection. Please let us know if you have any additional questions. Happy learning!
This was helpful.Does this course includ Vlookups as I note it invludes Pivot tables
Answer:Good afternoon. Thank you for contacting us, You will learn the following Use advanced graphs in minutes instead of wasting hours trying to figure them out Increase interactivity by automating your spreadsheets with macros and VBA Solve complex problems with superpower functions Turn raw data into ‘must make’ decisions using PivotTables and PowerPivots. And much more, please let us know if you have any additional questions so we may assist. Thank you.
This was helpful.
Reviews
Currently there are no reviews for this course. Be the first to leave a review.
Legal information
This course is advertised on reed.co.uk by the Course Provider, whose terms and conditions apply. Purchases are made directly from the Course Provider, and as such, content and materials are supplied by the Course Provider directly. Reed is acting as agent and not reseller in relation to this course. Reed's only responsibility is to facilitate your payment for the course. It is your responsibility to review and agree to the Course Provider's terms and conditions and satisfy yourself as to the suitability of the course you intend to purchase. Reed will not have any responsibility for the content of the course and/or associated materials.