Course Code: excbasbesp
Duration: 14 hours
Prerequisites:
  • A basic understanding of computers

Audience

  • Data Analysts
Overview:

The course participants will be able to efficiently perform a variety of calculations in a transparent way to organize and present their results, use multiple mechanisms to facilitate and accelerate the creation of spreadsheets in Excel. 

This instructor-led, live training (online or onsite) is aimed at data analysts who wish to use Excel for data analysis.

By the end of this training, participants will be able to:

  • Create spreadsheets in Excel.
  • Visualize data with charts, graphs, etc.
  • Generate reports to share amongst team members.

Format of the Course

  • Interactive lecture and discussion.
  • Lots of exercises and practice.
  • Hands-on implementation in a live-lab environment.
Course Outline:

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)
  • Sorting, filtering
  • Protect worksheet and workbook
  • Create table, name table and format. Show when we name the table, how it makes it easier when writing functions, sorting data and doing conditional formatting
  • Name Manager and define name and the application when creating drop down list
  • Creating Drop down list from the Data Validation section
  • Remove duplicates

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
  • Common functions, math, time, date, vlookup
  • IF, AND, OR, Nested IF & NOT Logical operators
  • Import: csv, sql db files, xml into Excel

Functions

  • General rules for using the function
  • Frequently used functions
  • Nesting functions
  • Using the conditional
  • The Function Wizard
  • Isblank functions
  • SUMIF function
  • Pivot tables

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
  • Charts with sparklines