Online Course
Online MIS Course | Microsoft Certified Training
Welcome to India's #1 Online MIS Course Institute. Till now we have delivered 100+ batches successfully. Now, it's your turn to become an MIS expert with Microsoft Certifications. Register today for Online MIS Course along with Microsoft Certified Training. Google Rating 4.9 by 1000+ Professionals Globally.
Online MIS Course with Microsoft Certified Training by ACLM Institute is being provided with full duplex high end technology. Since the inception, ACLM meets the requirements of state of art and functionality. Our Online MIS Course, makes you feel like an instructor led training.
For the online MIS Course, ACLM invites Senior and Middle Level Manager, Team Leaders, Director and senior staffs to understand the entire technology and make it useful in their organization. Our training has settled lot of examples on MIS and Project Management.
ACLM also invites those candidate(s) who wish to make career in MIS and data management. The training provide In-depth Knowledge in MS-Excel, VBA with the combination of MS-ACCESS & SQL along with various techniques to prepare MIS Dashboard including Graphs, Charts and Power BI Tools.
Course Overview : Online MIS Course - Microsoft Certified Training
- Complete Advanced Excel along with 250+ formulas, 100+ Assignments, notes & E-books
- Use of ChatGPT & Microsoft Co-Pilot to handle complex formulas in few easy steps
- Advanced Dashboard Formatting & Data Validation
- Data Handling, Data Analysis with SQL & MS Access
- Dynamic Charting / Graphs & Maps
- MACRO & VBA Programming
- Automated Email & Charting in VBA
- Creating your own libraries and Add-ins
- Visualizing data over Tableau & PowerBI
- Assignments, Test & Microsoft Certification
Advanced Online MIS Course along with Microsoft Certification on Excel, SQL, PowerBI
- Advanced MIS Training Topics
- Basic Concept of VBA Macros Programming
- Use Data Type in Macros, Create a New VBA Formulas with Function
- Make a User-Interface with User Form
- Setting Data Source, Getting XML Data, Getting ACCESS Data, Getting Other Data
- Connect the Excel with SQL Procedure, Access, Using the SQL Procedure, Connecting to a DBMS Using the DSN
- ODBC
- Assignments
- E-Books
Microsoft Certifications
On completion of the training you’ll also be eligible for 04 international certifications including exclusive participation certification by ACLM.
- Microsoft Certified Data Analyst Associates
- MOS (Microsoft Office Specialist Exams Level 1, Level 2, Level 3 & Level 4) comprising of Beginner to Expert Level.
- Microsoft Power BI Certified Visualization Expert
- Microsoft Certified Database Developer (Exam Code: 70-761: Querying Data)
- Advanced MIS Experts by ACLM
Course Content - Online MIS Course with Microsoft Certification
- Focus on real scenarios of Reporting / Data Management / Automation
- Difference between basic and advance Excel, Excel Basics, Excel Settings
- Advanced Formulas including complete coverage of Logical functions, Text Functions, Date & Time Functions, Look up functions & Mathematical Functions.
- Complete Excel ribbons including Name Manager, Absolute and Relative references, field setting, Goal Seek / Scenario manager, Managing custom Lists, Cross addressing.
- Pivoting and Slicing along with Power Pivot and Power Slicing
- Graphs, Charts along with MIS reports generations
- Excel Setting, enabling and disabling Excel options including add ins, creating own tabs and menu bars
- Handling multiple files with the Excel File System (Exporting and Handling Files) + Database file connectivity, Remote file connectivity, Text file connectivity and many more…
- Conditional formatting, Data Validation, Consolidation, Grouping and Sub Grouping, User Settings for Excel Application Level
- Data Management and Analysis, Reporting Techniques, Macros, List box, Combo box, Buttons and many more…
- Geographical charts on global data sets
- More than 250+ formulas along with 100+ assignments, notes, e-books and many more ACLM’s branded videos to help you forever.
Set of Important Formulas and Related assignments & Notes
- Logical Function Sets – And, Or, Not, Nand, Nor, Xor, True, False, Iferror, Iserror, Iserr, Isna, Isblank, Istrue, IsFalse, IsNumber, IsText, IsEven, Isodd, IsLogical
- Usage of Operators – Logical Operator, Arithmetic Operator, Assignment Operator and Unary Operators
- Logical / Conditional Statement – If, If else, if else if (nested if), if with operator, if with the use of text functions, if with the use of numbers, if with date function, if with time functions, nested if with operators including text, numbers & date & time functions.
- Text Function Set – Upper, Lower, Proper, Left, Mid, Right, Concatenate, Char, Clean, Code, Find, Search, Replace, Substitute, trim, text value, Text set, T, BahtText, Unichar, Unicode. The array formulae including various other text functions.
- Date & Time Function Set – Date, Day, month, Year, time, today, now, Hour, minute, Second, Datedif, Datevalue, Time Value, Weeknum, Weekday, Age Calculator, Usage of scientific and moderate calculator, Days360, General Date Diff, EDate, EOmonth, Workdays, Workdays.intl, Networkdays, Networkdays.intl, now, Today, Yearfrac
- Lookup and References set – Address, Area, Choose, Vlookup, Hlookup, Lookup, Reverse Lookup, Index, Match, Row, Rows, Column, Columns, Get Pivot Data, Hyperlink, Indirect, offset, Transpose, RTD, Small, Large, Rank, Formula Text,
- Mathematical Function – General Use Function – Sum, sumif, Sumifs, Count, countif, Countifs, Average, Averageif, Averageifs, Product, Sumproduct, Sumproduct Array Formula, Round, Roundup, Rounddown, Floor, Ceiling, INT, Sqrt, Power, Subtotal, Fact, Even, Odd
- Advanced Formulas including Array Formulas and its applications, Sorting Formulas, Filtering Formulas, Query Formulas
- Differentiating Google Sheets and MS-Excel (Pros & Cons)
Data Filtering & Formatting
- Understanding Different Data Types, Using String / Text / Number / Long Data Types with examples, Managing Date Time Format, Handling Long Date time Format, Data Correction, Identifying bad data, Conversion of different data types, Importing and accessing different file types in excel
ADVANCE EXCEL SECURITY
- Protecting Excel File, Protecting worksheet, Workbook, Enable / Disable Range, Allow users to edit range, Protecting users to edit range, Field Controls using customized user access.
Section 1: Advance VBA Programming
Visual Basic Editor (VBE) in Excel Advance VBA Programming
- The Visual Basic Editor is the user friendly program that you will use to talk with Excel. In it you can create your VBA procedures (macros) and userforms. You will then be able to modify and test these components easily step by step in the VBE.
The Project Window in the Visual Basic Editor of Excel
- The Project Window lists all your open projects with their sheets, modules and forms. In the Project Window you will add modules and create forms. When you select one of the components its properties will show in the Properties Window and the VBA code that you have created for the selected component will appear in the Code Window.
The Properties Window in the Visual Basic Editor of Excel
- The Properties Window shows you the properties of the object that is selected in the Project Window (sheet, module) or the properties of the control (command button, text box, list box, etc…) that is selected on the forms. You will use this window often when you start developing forms (userforms).
The Code Window in the Visual Basic Editor of Excel
- All the action happens in the Code Window. In this large window you or the Macro Recorder will write macros. You will also test and fine tune all your macros in the Code Window.
Developing Macros in the VBE
- In this lesson you will learn how to create a new VBA procedure. You will organize sets of sentences to tell Excel what to do. You can key them in or copy/paste them from recorded macros, from one of your old macros or from the Internet where you will find millions of free macros.
Testing Macros in the VBE
- When you develop macros in Excel you spend 20% of your time analyzing the project, 15% writing your VBA procedures and 65% testing and fine tuning them. Split your screen, use the F8* key and you can see what your procedure does in Excel step by step. Back up, correct and re-test.
Excel Macro Recorder
- The Macro Recorder is the best teacher and it will be a great assistant (writing code for you) even when you become an expert at programming in VBA. In this lesson you will learn about the Macro Recorder and you will try it. You will also run and test the macro that you have recorded.
Macros Help and Assistance
- There is plenty of help and assistance available within Excel when you develop macros. As you have discovered in the previous lesson the Macro Recorder is a great teacher and assistant.
- In this lesson we investigate the two other sources of assistance within the Visual Basic Editor of Excel: the Help Files and the Object Browser.
VBA Events
- Once you have developed your macros you need to trigger them so that they start. The trigger is called an event.
- The most frequently used event is the click on a button. In this lesson you will learn how to add a button to your sheet and how to connect it to your macro. You will also learn how to start a macro by opening a workbook (also called spreadsheet or Excel file), by closing a workbook and even by changing the value of a cell.
VBA Security and Protection
- You cannot harm your computer with macros so be bold in experimenting with macros you will learn more and faster. In this lesson you will learn how to protect you code, your sheets and your workbooks.
Section 2: Advance VBA Programming
VBA Coding Tips Advance VBA Programming
- Assembling VBA words into sentences is called coding. Here are interesting tips to make things easier when you start coding.
Dealing with Errors
- VBA tells you immediately when the code that you have written is wrong. When the logic is wrong or when the user gives a wrong answer these errors need to be handled. In this lesson you will learn the necessary vocabulary to deal with errors.
Working with the Application
- The Application is EXCEL itself. Add 15 new VBA words to your vocabulary like Application.Quit, Application.ScreenUpdating = False, Application.CutCopyMode=False and others .
Working with the Workbooks
- Some people call them spreadsheets or Excel files VBA calls them workbooks. Here are other VBA words to add to your vocabulary. You will learn to work with ThisWorkbook (the workbook in which runs the macro) with many workbooks and even with all the workbooks in a directory.
Working with the Worksheets
- There can be 256 sheets in a single workbook. In this lesson you will discover the vocabulary to move from one to the other, to copy/paste from one to the other, to add and delete worksheets and even go from the first to the last sheet of a workbook to do something on all of them. You will also learn how to copy the sheets into another workbooks or to transform a single sheet into a workbook.
Moving Around the Worksheet
- In this lesson you will improve your VBA vocabulary with 40 some words to work within the worksheet. You will learn how to select a cell or a group of cells and how to count the rows and columns.
- You will learn how to change the value of a cell or insert a formula in it. The 5 VBA words that you will use the most moving around the sheets are Range, Select, Offset, Activecell and CurrentRegion. What you can do with these 5 words and tens of other important words is covered in this lesson.
Section 3: Advance VBA Programming
Working with Message and Input Boxes Advance VBA Programming
- You will use message boxes or input boxes to communicate with the user. Through these pop-ups the user can supply bits of data or say ” Yes, No, Ok, Cancel” and other short answers during the execution of a macro.
Excel VBA Vocabulary to Filter and Sort Data
- Excel offers you the most powerful database tools (sorting, filtering, etc…). With VBA these tools become even more powerful. You will learn how to use them with the data that you extract from large centralized databases (SAP, Oracle, EssBase, Access, etc..), from accounting and sales programs and with financial data that you can download from the Internet.
Working with Variables
- The variable is the concept that will launch your creativity and allow you to develop real programs in Excel. It will empower you to develop sophisticated programs and work extremely rapidly with very large sets of data. Before learning about variables you develop macros after you develop programs.
Working with Statements
- They are the KILL, the IF_THEN_ELSE_END IF, the SELECT-CASE, the DO_LOOP, the FOR_NEXT….
Working with Functions
- There are Excel functions and VBA functions. Three topics are covered in this lesson. You can use existing Excel functions within VBA or you can create brand new Excel functions with VBA and you can use VBA functions.
Working with external data and SQL
- When you connect to outside sources of data (large databases, text files, other Excel workbooks, Access, etc.) the computer is using SQL (Structured Query Language) a specialized language to work with data.
- You can use Excel’s functionalities to connect and extract data but you can also use directly the SQL language top extract data. It is the fastest way to access any external data.
Working with Windows and other Microsoft Programs FROM Excel
- With VBA for Excel you can develop VBA procedures (macros) to work within Excel while calling other Microsoft programs like Access, Notepad, Word, Project and even Windows.
Forms (Userforms) in VBA for Excel
- You have used message boxes and input boxes to communicate with the user while the macro was running. When these tools are no longer sufficient you need to develop useforms.
Userforms Properties and VBA Code
- In this lesson you will learn how to set the properties of the userform and you will develop code within the two important events that are ” On Activate” and ” On Close” .
Properties and VBA code for Command Buttons
- The command button is the control where most of the code resides and everything happens when you ” CLICK ” on it.
Properties and VBA code for Labels
- Labels are just labels. You use them to describe functions and to share information with the user.
Properties and VBA code for Text Boxes
- The user is now talking to you.There are very few userforms without text boxes. Text boxex having been created to handle text you need to discover how to use them with numbers, percentages, currencies, etc.
Properties and VBA code for Combo Boxes
- The combo box is the ultimate control. It is a drop-down list and you will learn how to develop sets of combo boxes where the choices offered in the second combo box depend on the choice made in the first one. They are called cascading combo boxes.
Properties and VBA code for List Boxes
- You will develop list boxes when you want to allow the user the possibility of multiple choices.
Properties and VBA code for Check Boxes, Option Buttons and Frames
- The ” True/False” controls to be used as a group within a frame
Properties and VBA code for Spin Buttons
- You can test different values increasing them step by step until you find the right one and you do it with a spin button.
Excel Image Controls
Basic Charting & Dashboard Preparation
How to Select Correct Chart, Basics of Chart Selection – Rationale, Common Messages for charts, Which Charts to use, Further help & Tips on Chart selection, Introduction to Excel Charts, Understanding Excel Charting Options, Bar Charts / Column Charts, Line Charts, Scatter Plots, Pie Charts, Other Charts in Excel, Chart Formatting – 10 Tips, Making titles dynamic, Reversing categories, Adjusting axis – (non-date), Adjusting axis – (date), Removing axis, grid lines, legend, Combining 2 chart types, Adding secondary axis, Error bars, adding and formatting them, Choosing right colors, Chart Templates, Bar & Column Chart Formatting, Overlapped Bar Charts, Understanding Series Gaps, Negative bars, Highlighting the max or minimum item automatically, Adding average line to the chart,
Advanced Charts/ Graphs & Maps in Dashboard
Advanced Charting Tutorials in Excel, Bullet chart – what is it and how to make, Incell charts – using REPT to make charts, Min-Max chart – showing range along with average, Waterfall charts, Time line charts, Thermo-meter charts, Gauge Chart, Dynamic Charts in Excel, Need for Dynamic Charts, Dynamic charts using Filters, Dynamic charts using camera tool, More info. & ideas on dynamic charts, Interactive Charts In Excel, Need for interactive charts, Introduction to form controls, Using Scrollbar control, using checkboxes, Example interactive charts (2),
Pivot Tables
Excel Pivot Tables, What Is A Pivot, Why You Should Use It, Creating Your First Pivot Report, Formatting Pivots, Summarizing Data, Playing With Pivots, Grouping Values, Calculated Fields, Calculated Items, Making A Pivot Chart, Copying Pivot Tables, Example Uses Of Pivots, Pivot Table Tricks, Guest lecture by Debra Dalgleish, Preparing your data, Pivot and chart, Group dates and data, Calculate, Focus with filters, sort
Data Filters
Data Filters & Sorting, Uses of Filters, Basic Filtering, Short-cuts for filtering, Visual Filters – Using Conditional Formats, Sorting, Sorting left to right
Validation
Data Validation, Uses of Data Validation, Simple Data Validation Examples, Using drop-down box, to validate, Using Formulas, Showing Error Messages, Advanced DV Tricks,
Working with Data
Importing External Data, What is the purpose of Excel Import?, Understanding various import options, Importing Text Files – Example: Dynamic Data Validation, Importing Web Pages – Example: Movie Tracker, Importing Access DBs – Example: Sales Pivot Report, Using Text Import Utility – Example: Converting Dates, Excel Access Integration, Excel to Access, Access to Excel, Integrating Excel & Access thru Automation, Working With Complex Worksheets, What is a complex sheet?, Tips for handling complexity, Designing a complex workbook,
MS-Access in Action
- MS Access SQL VBA Ribbon, File, Save a Database as Templates, Home, Create, External Data, Database Tools, Contextual Tabs, Quick Access Toolbar, Object Navigation Pane, Access Work Surface, Access Option Dialog Box, Object Overview, Tables Queries, Forms, Reports, Macros & Modules, Process Overview, Exercise: Getting Around the Access Environment, Create Database using a wizard
Fundamentals
- Approach to Database Design, Top down Database Design, Bottom up Database design, Reality, Gather Information, Data Mapping, Sample Data Map, Normalization, First Normal Form, Second Normal Form, Third Normal Form, Table Relationship
Table & their View
- Datasheet View, Selection Tools, Table Layout Tools, Sorting & Filtering Tools, Print Records, Record Navigation, Working in Datasheet View, Working with Table in Datasheet View, Design View, Open a table in Design View, Design View layout
Tables
- Creating an Access Database, Creating a New Database, Data Types, Determine Data Type, Create a Table in Design View, Delete a Fields, Rename a Table, Linking Table from External Source, Import table from other database, Tables and Templates and Application Parts
Queries
- Definition & Purpose, Types, Views, Select Queries with criteria, Logical Operators, Group & Total with Queries, Total Row with group By, Action Queries, Make Table Queries Append , Queries, Update Queries, Delete Queries Creating Crosstab Queries, Delete Queries, Rename Queries
Form
- Definition & Purpose, Layout Design & Views, Adding a Header & Footer, Adding Logo to the Header, Adding a Title to the header, Arranging Filed on a form, Spacing Objects o a form, Alter a Form, Remove form Control, Format Forms, Adding Data Record in Form View, Form Record Navigation, One Click Forms, Basic Data Entry Form, Datasheet Form, Split Form, Create a Form with Form Wizard
Reports
- Definition & Purpose, Views, Report Header, Page Header, Group Header, Details Group Footer, Page Footer, Report Footer, Adding a Report Header/Footer, Adding a logo to the Header, Adding a Title to the Header, Adding fields to Reports, Add Calculated Fields, Arranging Fields on a Report, Resizing the Details Sections, Grouping 7 Sorting, Setting Properties on a Report, Report Property Setting, Section Property Sheet, Object Property Sheet, Adding Records Counts, Special Report Fields, Dates, Page Numbering, Controls, Application Parts, Deleting a Report, Formatting a Reports, One Click Report, Formalize the Relationship between Tables
Relationship
- Data Type and Relationship, Views, One Click Report, Formalize the Relationship between Tables
Macro
- Open a Table, Form and Record, Run a Query, Print a Report, Running a Macro, Creating Simple Macros
Completing the Desktop Application
- The Navigation Form, Running Macros from a Navigation Form, Setting the Navigation Form as a Default Form, Creating a navigation Form, Splitting the database, Encrypt with password, Distributing the Front-End Database, Database Maintenance, Compact & Repair, Backup recovery a database, Maintain Backup Compatibility
Using Tableau
Part A: Basic Reports
Parameters • Grouping Example 1 • Grouping Example 2 • Edit Groups • Set • Combined Sets • Creating a First Report • Data Labels • Create Folders • Sorting Data • Add Totals, Sub Totals and Grand Totals to Report
Part B: Tableau Charts
Area Chart • Bar Chart • Box Plot • Bubble Chart • Bump Chart • Bullet Graph • Circle Views • Dual Combination Chart • Dual Lines Chart • Funnel Chart • Traditional Funnel Charts • Gantt Chart • Grouped Bar or Side by Side Bars Chart • Heatmap • Highlight Table • Histogram • Cumulative Histogram • Line Chart • Lollipop Chart • Pareto Chart • Pie Chart • Scatter Plot • Stacked Bar Chart • Text Label • Tree Map • Word Cloud • Waterfall Chart • Geographic map • Filled map • Crosstab • Combines axis • Motion chart • Reference lines
Part C: Advanced Reports
Dual Axis Reports • Blended Axis • Individual Axis • Add Reference Lines • Reference Bands • Reference Distributions • Basic Maps • Symbol Map • Use Google Maps • Mapbox Maps as a Background Map • WMS Server Map as a Background Map
Part D: Calculations and Filters
Calculated Fields • Basic Approach to Calculate Rank • Advanced Approach to Calculate Rank • Calculating Running Total • Filters Introduction • Quick Filters • Filters on Dimensions • Conditional Filters • Top and Bottom Filters • Filters on Measures • Context Filters • Slicing Filters • Data Source Filters • Extract Filters
Part E: Tableau Dashboard
Create a Dashboard • Format Dashboard Layout • Create a Device Preview of a Dashboard • Create Filters on Dashboard • Dashboard Objects • Create a Story
Part F: Tableau Data Server
Physical architecture overview • User access • Component functions & processes • Tableau server on-premises • Tableau reader • Tableau online v tableau server
Using Power BI
- About the Exams: MCSA 70-778, MCSA 70-779 Exam
- Types of Reports in Real-World
- Interactive & Paginated Reports
- Analytical & Mobile Reports
- Data Sources Types in Power BI
- Power BI Licensing Plans – Types
- Power BI Training : Lab Plan
- Power BI Dev & Prod Environments
- Understanding the Power BI Tools
- Installing Power BI & Connecting to Data
- The “Locale” used in the curriculum
- Working with the query Editor
- Working with the data model and creating a visualization
Who Should Attend
Want to change career into data analyst, data visualizor or a data scientists. Do you have need to prepare reports of any kind that require updating with new data on a regular basis (weekly, monthly….), if YES, this course is made for you. If you are working hard but no deriving the best result, this course is obviously for you. And if yo work with charts and, or, Work with large volumes of data and, or, Import data into Excel from another source e.g, another database, Access, Web, Text files etc. and, Often don’t know the best chart to use to display your data, you should quickly join this online mis course.
Pre-requisites
Familiar with Excel, little bit knowledge on gathering data and a slight knowledge on reports / MIS.
And, yes, obviously you should be aware about basic formulas in Excel, PivotTable and PivotChart. As the basic knowledge always helpful in understanding the things deeply.
What You Need To Bring
- Notepad, Pen/Pencil, Laptop
- A good internet connectivity with a working headphone
- A good configuration laptop (At least i3 with 4 GB RAM
- Pre-installed softwares (MS Excel, MS SQL (any database like, sql, mysql, ms access), Tableau Desktop & PowerBI Desktop
- And, please check your audio and video connection to zoom also
Key takeaways
- Strong exposure on Advanced excel, along with formulating complex formulas at your fingertips.
- Easily working with Google Sheets to work on live data with advanced google queries
- MS Excel Dashboard with Macros and VBA, Time saving automated reports, Employee Performance Plan, Great Visibility and insight, Ongoing Improvement trackers.
- Ability to identify and correct negative trends, Judge performance against our Plan, Choosing what metrics to track, Building your own Executive Dashboard and live it with Tableau & PowerBI for your clients in a smart way
- International certifications on Excel, Tableau & PowerBI
About Trainer
- Having 25+ Years of Experience in Data Analysis, Clinical Data Research, Data Mining and Presentation
- Delivered more than 200+ VBA Projects (onsite & offsite)
- Currently working on 30+ Projects Globally
- Live training providing since 2009
- Trained more than 10,000 candidates globally
- Google Ranking 4.9 from 1000+ candidates
