Advanced Excel

VBA training in Nepal

Advanced Excel Course Content

Working with Dates

  • Dates and time in Excel
  • Inserting and formatting dates
  • Entering date functions
  • Using dates in formulas

Working with Tables

  • What is a Table?
  • Creating Tables
  • Changing the Table Range
  • Inserting Table Columns
  • Inserting Table Rows
  • Deleting Rows or Columns
  • Creating a Table Total Row
  • Sorting and Filtering Tables
  • Sorting Data in a Table
  • What is an AutoFilter?
  • Custom AutoFilters
  • Removing Filters
  • Filtering with Wildcard Characters
  • Using an Advanced Filter
  • Creating Subtotals
  • FlashFill
  • Use Slicers to filter tables

Working With Charts

  • Creating a Chart
  • Manipulating a Chart
  • Moving and resizing a chart
  • Adding a chart title
  • Adding a chart Axis title
  • Changing the Type of Chart
  • Formatting a Chart
  • Recommended Charts
  • Quick Analysis Option
  • Chart formatting control

Conditional Formulas and Formatting

  • The IF function
  • Using the Function Library
  • Manually entering a function
  • Conditional Formatting
  • Applying Conditional Formatting
  • Creating a Conditional Formatting Rule
  • Removing Conditional Formatting
  • Using the Manage Rules Option
  • Adding Conditional Formatting using the Quick analysis tool

Using Logical Functions

  • Working with Names and Ranges
  • What Are Range Names?
  • Defining and Using Range Names
  • Using Logical Functions
  • Using the Function Library
  • Manually entering a function
  • The IF function
  • The AND Function
  • The OR Function
  • Working with Nested Functions
  • Nested IF functions
  • Applying Logical Functions

Working with Lookup Functions

  • Using VLOOKUP to Find Data
  • How to Find an Exact Match with VLOOKUP
  • Finding the Closest Match with VLOOKUP
  • How to use the MATCH Function
  • Index Function
  • Lookup using Match and Index Function

  Working with Multiple Worksheets and Workbooks

  • Using Multiple Worksheets
  • Navigating between sheets
  • Creating -D formulas
  • Consolidating data
  • Linking your workbooks
  • Linking Workbooks
  • Using the Paste Link option
  • Creating a formula to link workbooks
  • Controlling link updates
  • Manually update all of the links or none of the links in a workbook
  • Manually update only some of the links to other workbooks
  • Dealing with Broken Links

 Documenting and Auditing

  • Comments
  • Adding, editing and deleting comments
  • The Formula Auditing Group
  • Excel Workbook and Worksheet Protection
  • Protecting your Worksheets
  • Unlocking Cells

 Advanced List Management

  • Validating Your Data
  • Creating Data Validation Settings
  • Using Database Functions
  • Creating a Criteria Range
  • Entering the Database Function

PivotTables and PivotCharts

  • What is a PivotTable?
  • Preparing Data to Create a PivotTable
  • Creating a PivotTable
  • Quick Analysis
  • Adding Fields to the PivotTable
  • Creating a PivotTable Frame (Classic PivotTable Layout)
  • Rearranging PivotTable Data
  • Hiding and Showing Field Data
  • The PivotTable Tools Ribbon
  • Modifying Calculations and Data Area Display
  • Formatting a PivotTable
  • Refreshing a PivotTable
  • Time Grouping in PivotTables (Excel )
  • Using Slicers to Manipulate PivotTables
  • Use Timelines with Pivot Data
  • Creating a PivotChart

Using ‘What-If’ Analysis Tools

  • Using Scenarios in Excel
  • Creating a Scenario
  • Using Goal Seek
  • Using Solver
  • Checking Solver Installation
  • Setting Solver Parameters

Macros

  • What is a macro?
  • Creating a macro
  • The Developer Ribbon
  • Recording a macro
  • Playing a Macro
  • Macro Security
  • More Macro Tasks
  • Recording a Relative Reference Macro
  • Running a Relative Reference Macro
  • Assigning a Keystroke to a Macro
  • Copying a Macro from a Workbook or Template

Working with Large Worksheets

  • Viewing and Organising Spreadsheet Data
  • The Zoom Feature
  • Hide rows and columns
  • Using Automatic Outlining
  • Grouping Data Manually
  • Printing Large Worksheets
  • Print Titles
  • Page Break Preview
  • Inserting a Page Break
  • Printing Multiple Sheets

Using Templates

  • What is a Template?
  • Creating a Workbook from a Template
  • Creating a Template

SPSS Training in Lalitpur

Core Python

SPSS Training in Lalitpur

Python Programming Course Outline

1. General Introduction

1.1. Introduction: The Way of the Program

1.2. Algorithms

1.3. The Python Programming Language

1.4. Comments

1.5. Glossary

1.6. Chapter Assessment

2. Variables, Statements, and Expressions

2.1. Introduction

2.2. Values and Data Types

2.3. Operators and Operands

2.4. Data Types

2.5. Type conversion functions

2.6. Variables

2.7. Variable Names and Keywords

2.8. Choosing the Right Variable Name

2.9. Statements and Expressions

2.10. Order of Operations

2.11. Reassignment

2.12. Updating Variables

2.13. Hard-Coding

2.14. Input

  • Glossary
  • Exercises
  • Chapter Assessment

3. Python Modules

3.1. Introduction to Python Modules

3.2. Modules

  • Importing Modules
  • Syntax for Importing Modules and Functionality

3.3. The random module

3.4. Glossary

3.5. Exercises

4. Sequences

4.1. Introduction: Sequences

4.2. Strings and Lists

  • Strings
  • Lists
  • Tuples

4.3. Index Operator: Working with the Characters of a String

  • Index Operator: Accessing Elements of a List or Tuple

4.4. Introduction: Dictionaries

4.5. Dictionary operations

4.6. Dictionary methods

4.7. Length

4.8. The Slice Operator

  • List Slices
  • Tuple Slices

4.9. Concatenation and Repetition

4.10. Count and Index

  • Count
  • Index

4.11. Splitting and Joining Strings

4.12. Exercises

4.13. Chapter Assessment

5. Iteration

5.1. Introduction: Iteration

5.2. The for Loop

5.3. Flow of Execution of the for Loop

5.4. Strings and for loops

5.5. Lists and for loops

5.6. The Accumulator Pattern

5.7. Traversal and the for Loop: By Index

  • Glossary
  • Exercises
  • Chapter Assessment

6. Conditionals

6.1. Intro: Conditionals

6.2. Boolean Values and Boolean Expressions

6.3. Logical operators

6.4. The in and not in operators

6.5. Precedence of Operators

  • Glossary
  • Exercises
  • Chapter Assessment

7. Files

7.1. Introduction: Working with Data Files

7.2. Reading a File

7.3. Alternative File Reading Methods

7.4. Iterating over lines in a file

7.5. Using with for Files

7.6. Writing Text Files

7.7. CSV Format

7.8. Reading in data from a CSV File

7.9. Writing data to a CSV File

  • Glossary
  • Exercises
  • Chapter Assessment

8. Functions

8.1. Introduction to Functions

8.2. Function Definition

8.3. Function Invocation

8.4. Function Parameters

8.5. Returning a value from a function

8.6. A function that accumulates

8.7. Variables and parameters are local

8.8. Global Variables

8.9. Flow of Execution Summary

  • Glossary
  • Exercises
  • Chapter Assessment

9. Processing JSON

9.1. Processing JSON results

  • Understand
  • Extract
  • Repeat

9.2. Exercises

9.3. Chapter Assessment

10. Exceptions

10.1. What is an exception?

10.2. Exception Handling Flow-of-control

  • Raising and Catching Errors

10.3. When to use try/except

10.4. Standard Exceptions

10.5. Exercises

10.6. Chapter Assessment

11. Internet APIs

11.1. Requesting data from the Internet

11.2. Anatomy of URLs

11.3. The HTTP protocol

11.4. Using REST APIs

11.5. Fetching a page

  • Fetching in python with requests.get

11.6. Project – OMDB and TasteDive

VBA training in Nepal