Advanced Excel Formula Techniques, Time-Saving Functions and Charting Tips
-
Training TypeLive Training
-
CategoryMS Excel
-
Duration4 Hours
-
Rating4.7/5
Course Introduction
About the Course
To help ensure that everyone who works on the same workbook can understand what formulas and calculations are doing, it is important to use cell and range names. While cell references can be used to identify where formulas are getting information to calculate data, it is not always obvious. Excel allows you to give individual cells and cell ranges names, and then use those names in formulas and functions. Then, you can tell at just a glance what data is being used.
While the basic functions in Excel cover the majority of use cases, there are some situations where a specialized function is more appropriate. In order to find and use specialized functions, you must be familiar with their syntax and understand how they work on a fundamental level.
While pure data can be analyzed by those familiar with this data and the tools that Excel provides, sometimes you need graphical assistance to help interpret it. Additionally, charts are an enormous help when people do not have time to study the data and only need a brief overview of the relevant information. Over the course of this topic, you will learn how to create charts of different types, using different methods.
Course Objective
Apply range names
Use specialized functions
Use text functions
Use logical functions
Use lookup functions
Use date functions
Use financial functions
Create charts
Modify and format existing charts
Create a trendline
Create advanced charts
Who is the Target Audience?
Any Professional who would like to save time by learning more about advanced formulas, functions and visualizing data/charting
Basic Knowledge:
Foundational Excel knowledge would be helpful for this course.
Available Batches
| 30 Jun 2022 | Thu (1 Day) | 12:00 PM - 04:00 PM (Eastern Time) |
Pricing
-
Range Names
Adding Range Names Using the Name Box
Adding Range Names Using the New Name Dialog Box
Editing a Range Name and Deleting a Range Name
Using Range Names in Formulas
-
Function Categories
The Excel Function Reference
Function Syntax
Function Entry Dialog Boxes
Using Nested Functions
Automatic Workbook Calculations
Showing and Hiding Formulas
Enabling Iterative Calculations
-
Text Functions
The LEFT and RIGHT Functions
The MID Function
The LEN Function
The TRIM Function
The UPPER, LOWER, and PROPER Functions
The CONCATENATE Function
-
Logical Functions
Logical Operators
The AND Function
The OR Function
The IF Function
-
Lookup Functions
The LOOKUP Function
The VLOOKUP Function
The HLOOKUP Function
-
The TODAY Function
The NOW Function
Serializing Dates and Times with Functions
-
The PMT Function
The IPMT Function
The PPMT Function
The NPV Function
The FV Function
-
Charts
Chart Types
Chart Insertion Methods
Resizing and Moving the Chart
Adding Additional Data
Switching Between Rows and Columns
-
The Difference Between Modifying and Formatting
Chart Elements
Minimize Extraneous Chart Elements
The Chart Tools Contextual Tabs
Formatting the Chart with a Style
Adding a Legend to the Chart
-
Trendlines
Types of Trendlines
Adding a Trendline
The Format Trendline Task Pane
-
Dual Axis Charts
Creating Custom Chart Templates
Viewing Chart Animations