Penerangan
Microsoft Excel - Intermediate
Course Duration: 2 Days
Who is this course for?
This course is intended for participants who wish to gain more knowledge from the foundation level of Excel. For participants who are working with lots of formulas and creates report to understand the necessary techniques on how an electronic spreadsheet works.
Pre-requisites
Basic knowledge of Microsoft Excel is essential with the following pre-requisites:
- Able to switch between task applications
- Able to create a spreadsheet with simple formatting
- Able to create a basic chart
- Able to print a spreadsheet with headers and footers added
- Able to use Auto Filter command
- Able to apply Freeze Pane command
- Able to create basic formulas - Addition, Subtraction, Multiplication and Division
- Able to use basic functions - AutoSum, Count, Max, Min and Average functions
Benefits
Upon completion of this program, participants should be able to:
- Create simple to complex formulas and functions like:
- COUNTA & COUNTIFS Function
- AVERAGE A & AVERAGEIFS Function
- SUMIF & SUMIFS function
- IF Function
- Nested Function
- Database Function
- Validate data in a worksheet
- Filter data using Auto & Advanced Filtrers
- Advanced Chart Formatting
- Clean duplicate records
- Visualizing data as a graphics
Key Content
Unit 1: Performing Calculations
Topic A - Reuse Formulas
- Relative references
- Absolute references
- Mixed references
- Understanding mixed cell references
Unit 2: Creating Advanced Formulas
Topic A - Apply Range Names
- Range names
- Adding range names using the Name Box
- Adding a range names using the New Name dialog box
- Editing a range name and deleting a range name
- Using range names in formulas
Unit 3: Worksing with Functions
Topic A - Using Statistical Function
- COUNTA Function
- COUNTIFS Function
- AVERAGEA Function
- AVERAGEIFS Function
Topic B - Using Mathematical Function
Topic C - Using Logical Function
- IFERROR Function
- IF Function
- Nested IF Function
Topic D - Using Database Function
- DSUM Function
- DCOUNT Function
- DAVERAGE Function
- DMAX Function
- DMIN Function
Unit 4: Organizing Worksheet Data with Tables
Topic A - Create and Modify Tables
- Tables
- Table Components
- Create table dialog box
- Table tools design contextual tab
- Styles and Quick Styles sets
- Customizing row display
- Table modification options
Topic B - Sort and Filter Data
- Difference between Sorting and Filtering
- Sorting data
- Advanced filtering
- Removing duplicate values
Topic C - Use Subtotal and Database Functions to Calculate Data
- SubTotal Function
- Summary Function in Tables
Topic D - Data Validation
- Validating your data
- Data Validation using Lists
- Data Validation using Date
- Data Validation using Formulas
Unit 5: Visualizing Data as a Graphics
Topic A - Conditional Formatting
- Highlight Cell Rules
- Using Data Bars
- Using Icon Sets
- Creating new rules with Formula
Topic B - Create Charts
- Charts
- Chart Types
- Chart insertion methods
- Resizing and moving Chart
- Adding additional data
- Switching between rows and columns
Topic C: Modify and Format Charts
- Difference between modifying and formatting
- Chart elements
- Minimize extraneous chart elements
- The chart tools contextual tabs
- Formatting the chart with a Style
- Adding legend to the chart
Topic D: Adding Sparklines
- What are Sparklines?
- Adding Sparklines
- Sparkline Tools Tab
- Editing Sparkline Data
- Removing Sparkline
Topic E: Editing Sparklines
- Showing and hiding data
- Changing the style
- Changing Sparkline and Marker Color
- Setting Sparkline options
Unit 6: Managing Views
Topic A - Working with Different Views
- Using Custom View
- Using Page Layout View
- Express Header & Footer
Topic B - Managing Multiple Windows
- Arranging Workbooks
- Comparing Workbooks Side by Side
- Synchronous Scholling and Resetting Window
Methodology
Informative presentation, group discussion and presentation, handson practical session
Lebih maklumat tentang Iconic Training Solutions Sdn Bhd
