Microsoft Excel User Plus Training Course
Objective: The aim of the 2-day Microsoft User Excel Plus Training Course is to provide the user with a comprehensive knowledge of the various features and facilities and how to apply them in practical situations. We are happy to tailor these sessions if onsite, and can use live data for maximum effect. The student will be led, via a series of in-depth examples on how to make the best use of Excel in their working environment and are invited to bring data with them for consultation.
| Review of Excel Basics / Short Cuts Advanced Formula
SUMIF / SUMIFS / COUNTIF / COUNTIFS
IF Statements / IF AND OR Statements
VLOOKUP / HLOOKUP
SUMPRODUCT / / INDEX / ARRAYS
Nested Formula
Formula In Linked Files
Date & Time Calculations
Using Named Ranges in Formula
Goal Seek / the Scenario Manager Data Cleansing / Creating Uniformity
TRIM /UPPER / LOWER / PROPER
CONCATENATE / LEFT / RIGHT / MID / LEN
Text to Columns / Fixed Width Entries /Delimited Working with Multiple Sheets / 3D Data Entry
Inserting / Deleting / Rearranging Worksheets
Linking the Sheets
3D Data Entry / 3D Formula
Using Multiple Windows / Linked Files
Link and Pasting to other Applications
Sorting a Table / Sort Keys / Sorting Left to Right Using AutoFilter / Advanced Filter
AutoFilter Criteria / Wildcards
Practical Users for Advanced Filter
Setting the ranges Process Automation
Creating Subtotals & Outlines Pivot Tables
Creating and Editing
Pivot Table Formula
Slicers / Charts / Reports
Pivot Table Drop Down Menu
Pivot Table Formula Setting Up Validation Rules
Restricting Numeric Entry
Error Prompts and Error Alerts
Dates and Time
Text Lengths
Restricting to Drop down lists | Custom Options
Preventing spaces
Budget limits
Ensuring Unique Entries Data Handling
Handling Large Volumes of Data
Optimising Large Files
Working with External Data Sources
Introduction to MSQuery
Setting Up Connections / Creating Live links
Choosing Fields / Applying Criteria
Returning the data to Excel
Refreshing the Download
Editing the Connection Advanced Charting
Creating your own
Speedometer Charts
Waterfall Charts
Thermometer Charts Data Entry Forms The Camera Tool The Development Ribbon
Working with Form Controls
Drop Down Lists
Check Boxes
Radio Buttons
Spinners
Scroll Bars Creating Scrolling tables
Introduction to Hyperlinks Introduction to Macros
The Macro Recorder
Using Relative References
Macro Types
Running a Keyboard Macro
Adding Embedded Macro Buttons
Adding to the Ribbon
Editing the VB Code
Joining Macros
Troubleshooting QUESTIONS & ANSWERS |
Please see our course schedule for the next course which will be held at our venue The Travelodge, Cumbernauld G689AT – located at junction 5 off M80.
