Course Code: excel082122
Duration: 14 hours
Course Outline:

Day 1

Introduction

  • Basic information about the program
  • Construction of the main window
  • Operations on workbooks (notebooks) and sheets

Entering and modifying data in spreadsheet cells

Formatting data

  • Determining how to display different types of data and calculation results (eg, dates, amounts, percentages or fractions)
  • Determining the edge and the background of the cells
  • Formatting row and column titles
  • Using Styles

Navigation

  • Moving up in large sheets
  • Parallel work on multiple spreadsheets / workbooks

Copying and moving data

  • Select areas (cell ranges)
  • Moving data
  • Paste Special 
  • Autofill cell strings (series) of data (for example, order numbers and dates of the next working day)

Excel Components

  • Format Cell
  • Sort & Filter
  • Fill
  • Data Validation
  • Conditional formatting
  • Freeze Pane
  • Connecting and splitting texts

Share & Print

  • Footer/Header/Watermarking
  • Repeat Titles
  • Printing options

Excel ShortCut Key

Formula, addresses and cell names

  • Create, modify and copy formulas
  • Circular references
  • Conditional formatting
  • The address types and their use
  • References to other worksheets and workbooks
  • Using cell names

Day 2

Functions

  • General rules for using the function
  • Frequently used functions
    • Simple formulas: addition, subtraction, multiplication, division, examples of formulas, percentage calculations
    • Relative formula and fixed formula, formula copying, cell fixation within a formula, links between sheets
    • Statistical formulas: AVERAGE/ COUNT/COUNTA/MIN/MAX/SUM
    • Text Function: CONTACT/ LEFT/RIGHT/MID/LEN/TRIM
    • Date&Time Function: Today/DATE/WEEKDAY/NETWORKDAYS/WORKDAY
  • Using the conditional
    • Logical function IF/OR/AND/SUMIF/AVERAGEIF/COUNTIF
  • The Function Wizard
  • Nesting functions
  • Advanced Function
    • Aggregate Function: SUMIF/COUNTIF /SUMIFS/ AVERAGEIFS/ COUNTIFS/ COUNTA/COUNTBLANK/ MAXIFS/MINIFS
    • Reference Function: VLOOKUP/HLOOKUP/XLOOKUP/FILTER/
    • Text Function: FIND/SEARCH/ SUBSTITUTE/ REPLACE
    • VLOOKUP + HLOOKUP Functions for searching and finding information and comparing tables(If there is an Excel 365 version, we will learn XLOOKUP)

Charts

  • The main chart types and their use
  • General principles for creating compelling charts
  • Creating Charts.
  • Components of graphs and their modification
  • Create your own chart types
  • Pivot table