Course Details

Image

Course Introduction 課程簡介

免費網上課程 - Excel Tips (Formula and Names)(09:36)

This course is suitable for those who want to enhance their Excel operation skills. After finishing this course, the students will know many tips and tricks in using Excel and also expose themselves of the field of data analysis, which is a very important technique in management.

Course Duration 課程時數

6 hrs

Course Objective 課程目標

  • Learn the tips and tricks in using Excel.
  • Using Excel to manage, analyse, present, forcast data.

Entry Requirement 入學要求

People who have basic Office operation experience

Course Content 課程內容

1) Formula and Functions

- Common functions review: SUM, AVERAGE, COUNT ,MAX ,MIN
- Advanced functions for Text handling – LEFT, RIGHT, MID, LEN, UPPER, LOWER
- Advanced functions for Number handling – ROUND, RANDOM
- Advanced functions for Date calculations – DATEDIF, NETWORKDAYS, WEEKDAY
- Advanced functions for Logical decisions – IF, NOT, AND, OR
- Advanced functions for Data Lookup – VLOOKUP, HLOOKUP
- Database functions – DSUM, DMIN, DMAX, DCOUNT, DAVERAGE
- Formula Tracing and Error Checking

2) Range and Cells

- Absolute Addressing and Relative Addressing
- Assigning Names to Range or Cells and Name Management
- Cells Formatting (Border, Background, Text Wrapping)
- Conditional Formatting (Color Scales, Data Bar, Icon Sets)
- Convert to Tables and Tables Styling

3) Pivot Table and Data Analysis

- Create and understand Pivot Table
- Create various analysis report from different perspective
- Create Pivot Charts
- Auto summarize data by quarter, season or year.

4) What-if Data Analysis

- Goal Seek and Solver for finding conditions to meet the required target
- Scenario Manager for comparing analysis

5) Data Validation and Protection

- Validate Data input within required conditions, like range checking
- Protect worksheet by preventing changes to part of the worksheets
- Password protect the worksheet for read-only or for modification

6) Charting

- Create various charts like bar chart, pie chart and line charts
- Format various charts features like axis, legend, titles and background

7) Sorting and Filtering

- Basic Sorting and Filtering
- Text Filter, Number Filter and Range Filter
- Advanced Filtering with criteria range

8) Sharing and Tracking Changes

- Share workbooks with others
- Track Changes made by others

9) Grouping and Consolidation

- Data Grouping and Outlines setup
- Grouping and Subtotal of Data
- Combining multiple worksheets data into one

10) Macro Recorder

- To record and run a macro
- To edit a macro
- Macros available to other workbooks
- To run a macro from a toolbar button

Navigation

Social Media

IT Design