Duration
1 day
Introduction
Microsoft Excel is a popular tool in the world of business accounting. Many companies have been taking advantage of Excel’s financial management capabilities, and there are a number of examples of companies that have opted for Excel over other business tools. Although many businesses believe they are familiar with Microsoft Excel's functions, there are a number of things that they may be missing out on if they aren’t staying up to date. Exploring some Excel tips for productivity will help businesses reap benefits from using Excel during this training. This training provides you with the knowledge of using formulas in your accounting work as well as the ability to expand your Microsoft Office Excel knowledge.
Audience
This course is intended for all Excel users.
Prerequisites
Before attending this course, students should have experience:
- A basic knowledge of Microsoft Excel.
Module 1: FORMULAS AND FUNCTIONS BASIC
This module explains how to create basic formulas. The concept of cell referencing is introduced, including relative, absolute, and mixed references. Other tips and tricks on creating formulas fast, such as copying formulas without changing cell references, transposing formulas, pasting formulas as values, and performing quick calculations, is also presented. Finally, students are exposed to techniques for creating nested functions in formulas.
Lessons
- Introduction Formula Basics
- Use Cell References
- Copy a Formula Without Changing Cell References
- Transpose a Formula
- Copy and Paste Values
- Perform a Quick Calculation
- Use Nesting Functions
After completing this module, students will be able to:
- Use cell references in formulas effectively.
- Create formulas and desired results by using quick tips and tricks.
Module 2: STATISTICAL AND LOGICAL FUNCTIONS
This module explains how to use statistical functions like COUNTIF, SUMIF, and AVERAGEA. This module also introduces logical functions such as IF and ISERROR, and how to use logical functions in nested conditions.
Lessons
- Use COUNTIF to Calculate Survey Results
- Use SUMIF to Add Data That Satisfy Conditions
- Use AVERAGEA to Include All Cases
- Use IF Function to Prevent Division by Zero
- Use ISERROR Function to Avoid Error Display
- Create Multiple Conditions Using Nested IF
After completing this module, students will be able to:
- Use statistical functions in getting answers quickly for calculating survey results, add data that satisfy conditions, and create averages that include all cases.
- Use logical functions to prevent division by zeros and avoid error display.
Module 4: TEXT FORMULAS
This module explains how to use text functions to manipulate text in Excel. This module will show how to use functions like UPPER, LOWER, and PROPER to change case of text. Students will learn how to use text operators to join text and formatted numbers. Students will also learn a variety of functions such as LEFT, RIGHT, and MID, and formula techniques to convert text format into numbers, break imported text into columns, and extract words into columns.
Lessons
- Change Case of Text
- Join Text and Formatted Numbers
- Convert Imported Text Format into Numbers
- Break Imported Date Field into Individual Columns
- Extract a Name Field into Three Columns
After completing this module, students will be able to:
- Use text functions to manipulate text in Excel.
Module 5: DATE and TIME FORMULAS
This module explains how to use date and time functions to accomplish a variety of tasks such as adding a number to a date to get new dates, calculate the difference between two dates, and perform time field calculations.
Lessons
- Add a Number to Current Date to Get New Dates
- Calculate a Period of Time Between Two Dates
- Perform Calculations with Time Fields
After completing this module, students will be able to:
- Get accustomed to a variety of date and time formulas.
Module 6: EFFICIENCY TIPS
This module explains how to use some tips and tricks in making the formulas you created in the previous modules better. Tips such as reducing calculation time, entering formulas quickly, and checking and reducing errors in formulas are presented.
Lessons
- Reduce Formula Recalculation Time
- Enter Formulas Quickly by Shortening Sheet Names
- Select and Protect Cells Containing Formulas
- Reduce Data Entry Errors by Using Data Validation
- Display and Print Formula Syntax
- Use Auditing Tools to Check for Errors
- Check Data by Using Trace Precedents
- Use Comments to Annotate Your Worksheet
After completing this module, students will be able to:
- Use a variety of tips to help make formulas better.
Programme scope, delivery arrangements and suitability are confirmed during enquiry.