Formulas and functions are at the core Microsoft Excel and learning to use them effectively is an essential step in becoming proficient in Excel. This course provides clear and easy-to-follow instructions on how to build formulas for analysis, projection and data cleansing.
The course starts by discussing all of the different components that can be used to create a formula. You will be given clear demonstrations of the use of relative, mixed and absolute references, the use of dollar signs and how to name cell ranges to add clarity to your formulas.
Naturally, Excel functions are discussed in depth and many examples of using functions to analyze your data, beginning with every day functions like SUM, AVERAGE, MIN and MAX.
We then examine the use of text functions to clean up data prior to creating reports; functions like TRIM, UPPER, LOWER, PROPER, LEFT, RIGHT, MID and CONCATENATE.
Next, we turn our attention to conditional functions like IF, IFERROR, AND and OR. These functions allow you to make the value displayed in a cell dependent on the results of logical tests.
We then see how conditional functions can be combined with mathematical functions; using COUNTIF and SUMIF to produce calculations which only include those cells which match certain criteria.
And, naturally, no discussion of Excel functions would be complete without lookup functions. We discuss the use of VLOOKUP and, also, how the INDEX and MATCH functions can be used to lookup up data whose format makes the use of VLOOKUP impossible.
When you have completed this course, you will feel more confident about creating your own Excel formulas and taking advantage of Excel's wide variety of powerful functions.
1. Excel Formula Essentials
Anatomy of an Excel Formula
Understanding Relative References
Using Absolute References
Using Named Ranges
2. Text functions
Flash Fill Revision
Using The TRIM Function
Changing The Case Of Text
The LEFT And RIGHT Functions
The MID Function
The CONCATENATE Function
3. Conditional functions
Using the IF Function
Using the IFERROR Function
Using Nested IF Statements
Using the OR Function
Using the AND Function
4. Conditional number crunching functions
Using the COUNTIF Function
Using the SUMIF Function
5. Lookup functions
Using VLOOKUP Exact Match
Using VLOOKUP Approximate Match
Using INDEX and MATCH
This course is for those who are comfortable with Excel basics and use Excel 2013 (or 2016).
Certificate of Attendance/Completion/Achievement (no assessment element)
Variable - to suit the learner
No time limit
£49 for single user sign up £255 for company sign up (unlimited users)
G Com Solutions design customised and effective training solutions in partnership with our clients; which equip their people with the computer skills necessary to work more productively and effectively. By delivering our training on-site, we are able to save our clients money and to personalise our courses in a manner which is not feasible with public training courses. We believe that the one-size-fits-all approach to IT training is unfair to our clients; instead we prefer to take a cooperative and collaborative approach to on-site training. True, our trainers know computer software inside-out; but we rely on you, our clients, to tell us what you need from that software. This insight enables us to focus on those features which will
Great! You've found a course. Still have a few questions? Here are the top three things you should ask the course provider before booking:
They'll also be able to help you with questions around payment plans, flexible learning, start dates and more.
Click the green 'GET COURSE INFO' button to get in touch with the them directly.