MIS Reporting With VBA, Dashboard

• What is MIS Reporting?

• How it is work?

• Use of It?

• How  Dashboard Work?

• Text functions upper, lower, concatenate, proper, left, right, mid, search, search, find, len, trim....

• Date & time,  date, year, day, month, eomonth, today, time, hour, minute, datevalue, weekday, dateif....

• Financial, db, ddb, sin, ppmt, pmt, rate, nper, pv, fv, price.....

• Logical, if, and. Or, not, true, false, iferror....

• Lookup & references, lookup, hlookup. Vlookup, match, index, indirect, hyperlink....

• Mathematics, sum, sumifs, count, countifs, average, abs, fact, floor, cos, pi, round, power, product...

MODULE- I STREAMING WORKFLOW

• Create a macro

• Edit a macro

• Apply conditional formating

• Add data validation criteria

• Update a workbook properties

• Modifying excel default settiing

MODULE- II COLLABORATING WITH OTHERS

• Protect files

• Share a workbook

• Set revision tracking

• Merge workbook

• Administrator digital signature

   

MODULE- I CALCULATING DATA WITH ADVANCE FORMULA

• Manage cell & range name

• Calculating data across worksheets

• Use special functions

• Using formula to seperate data into seperate col.

• Linking files in excel workshhets by using button

MODULE- II Organizing Worksheet And Table Data

• Create A Chart

• Chart Element

• Changing A Chart Type

• Modifying A Chart

• Format A Chart

• Set Up Dynamic & Intreactive Chart

• Calculating In Pivot Table

MODULE- I Auditing WorkSheets

• Trace Cell

• Trouble Shoot Error In Formulas

• Troubleshoot invalid Data And Formula

• Watch And Evalute Formula

MODULE- II  Analyzing Data

• Create A Scenarios

• Perform What-if-analysis

• Perform Statistical Analysis

• Anlysis Toolpack

MODULE- I Organizing Sheet And Table Data

• Table

• Create Table Dialog Box

• Styles And Quick Style Set

• Formate Table

• Sort Or Filter Worksheet Or Table Data

• Database function…….Dsum(), Daverage(),Dcount()

MODULE- II Inserting Graphics Object

• Insert And Modify Picture And Clipart

• Draw And Modify Shapes

• Illustrate Workflow Using SmartArt graphics

• Layer And Group Graphics Object

• Customizing And Enahancing Workbook

• Customizing Workbook

• Manage Themes

• Create And Use Template(ex-bills)

• Exporting Excel Data

• Importing Delimited Text File

• Publish A Worksheet To The Web

• Importing Data From The Web

• Create A Web Query

• Define Objective, Start & End Points; Identifying Solution & Breaking it Into Sequential Steps Writing a Algorithm

• Step-by-Step Instructions, Flowcharts, Process Flow Diagrams. Excel Macros - an Introduction

• The Power of Macros - What, When, How to use Macros. Introduction to Object Oriented Programming

• Objects, Its Functions, Methods and Properties Introduction to Events

• Details of Events, How & When to use of Events, Preparing to 'Macro' Visual Basic Editor (VBE) Developer Tab, Security

• Introduction to the VBE, Properties window, Project Explorer, Password Protection of Code How to use the VBE - Features, Options, Intelligence Technology

• Debugging Mode, Bookmarks, Breakpoints, Watch Window, Immediate Window and Locals Window Inbuilt VBE Help Feature - Tips and Tricks.

• Terms like 'Compile', 'Debug' 'Keywords' etc. Recording Macro

• If and when to use Macro recorder with the code More On Excel Macros - I Structure of an Excel Workbook from VBA Point of View - Common Objects

• MS Excel Objects like Applications, Work sheets, Workbook, etc. Coding plate form , Variables and Constants

• What are Variables and Constants?

• Type of Variables; How and When to use Variables to Store Information.

• Typical Variable Naming Prototype

• When to use Constants or Variables More on Excel Macros - II Loops

• For-Next, For-Each, Do-While, For Each Loop Decision-Making and Code Branching

• If-Then-Else, Select-Case, And/or Conditions User Defined Functions (UDF)

• What are user’s Defined Functions? How to createÿ & use them. Form Controls vs. ActiveX Controls Getting into the Code Msg Box and Input Box Working with Data in Excel

• Data Types in Excel Worksheets, Like Dates, Texts, and Nulls etc. Working with Workbooks, Worksheets Ranges and Cells etc.

• Working with Dynamic Ranges. Protecting Worksheets, Cells and Ranges. Working with Multiple Files Opening & Saving Files Introduction to User Forms

• Working with User Forms & User Forms Events

• User form Controls

• How to use Data Validation in the Control

• Use of Text Box, Label, Drop down, List box, Image Box

• What is the Use of Message Box in VBA

• Searching Data into to the Database and finding records from the database

• Connecting VBA to Outlook

• Working with User Forms & User Forms Events

• Sending Email to Multiple Users Through VBA

• How to Establish Connection Between VBA and Internet Explorer to Open any Internet Website Through VBA

• Data Validation & Input Restrictions Effective Coding

• Testing and Debugging Your Code

• Effective Error Handling

• Automation Development Report & Live Project