Best Advance Excel, VBA, Dashboard Training
Course Duration : 3 Month
Advance, Excel, VBA, Dashboard, training allow managers to get high-level overview of the business. Excel is an excellent tool to make powerful dashboards that can provide analysis, insight and alert managers in timely manner.It help them to make quick decisions. Dashboards are often called as Management Dashboards or Information Dashboards or Dashboard Reports. Dashboards help managers to monitor the contribution & performance of the various departments in their organization
ACLM is a full-service provider of Training & Development Programs for individuals, and organizations. We conduct cost-effective Training Programs, In-house Trainings, Workshops and Corporate Programs with Professionalism and Sincerity across all industry sectors. We can also customize training as per Training Need Identify by you. Our intervention span includes Front Level Employees, Middle Management, and Senior Leadership. Our panel has a database of experienced professionals for various sectors not only expert in topics but also in Training Techniques Areas from Ghaziabad (Vaishali, Indirapuram, Noida, Vasundhara) and Delhi NCR region.
Advance Excel, VBA, Dashboard – Do you know the Importance?
- Help in monitoring Visual performance
- Help in to identify and correct negative trends
- Help in Measure efficiencies/inefficiencies
- Help in to generate detailed reports showing new trends
- Help to make more informed decisions based on collected business intelligence
- Help in to align strategies and organizational goals
- Help in to saves time compared to running multiple reports
- Help in to gain total visibility of all systems instantly
- Help in to quick identification of data
Topics Covered Under Advance, Excel, VBA, Dashboard
Basic Formulas
Relative & Absolute Cell References in Formulas, Difference between relative and absolute references, using $ symbol while writing formulas, Switching between relative & absolute refs. With F4 key, Using IF Formula, Nesting IF Formulas, IF Formula, Nesting IF Formulas, CHOOSE Formula, Examples of Nested IFs and Discussion, COUNTIF, SUMIF Formulas, What is it and Syntax, SUMIFS examples, COUNTIFS examples, Excel Text Formulas, Text formulas are what you use to process, manipulate or find information about a give piece of text in some cell(s). In this lesson, you can learn about the most important text formulas in Excel. The following formulas are discussed, LEFT, TRIM, FIND, LEN, RIGHT, LOWER, SEARCH, TEXT, MID, UPPER, VALUE, PROPER, SUBSTITUTE, REPLACE, CONCATENATE and REPT, VLOOKUP formula, INDEX, MATCH Formulas, What is it and Syntax, VLOOKUP examples, VLOOKUP hacks VLOOKUP Errors & Fixing them, INDEX Formula, MATCH formula, SUMIFS Formula, COUNTIFS Formula, What is it and Syntax, SUMIFS examples, COUNTIFS examples, ROWS, COLUMNS Formulas, What is it and Syntax, Using Rows /columns to generate running numbers, Formula Editing Tips And Tricks, Using F2 key, Using F4 Key, Copying Formulas, Moving Formulas, Auto-filling formulas, Using Mouse to Edit Formula Ranges, More, More, Formula Debugging Using F9 Key, How to debug formulas using F9 key
Advanced Formulas Using Advance, Excel, VBA, Dashboard
Advanced VLOOKUP Tricks, Advanced VLOOKUP, Looking up on left, How to Get Unique Values from a List using Formulas, How to get unique values from a list using formulas alone, OFFSET Formula, What is it and Syntax, Offset examples, Uses of Offset formula, SUMPRODUCT Formula, What is it and Syntax, Sum product – Simple examples, Conditional Sum using Sum product, Sum product hacks, Alternatives to Sum product, SMALL, LARGE Formulas, What is it and Syntax, Examples of SMALL and LARGE, Using Small / large to fetch nth item of an un-sorted list, Advanced Formulas, Named Ranges, Using Formula Audit Tools, 3D References (example: consolidate sales data), Array Formulas – What are they? (example: MAXIF, MINIF), Circular References – What are they? (example: log), Various Calculation Modes and How to use them, Formula Errors and how to handle them,
Formatting Advance, Excel, VBA, Dashboard
Formatting Paste Special, Make phone numbers readable, Make joining date readable, Currency format sales and profits, Understand how formats work, examples, Conditional format codes – examples, Hide a value without really removing it, Make the entire table readable, Copy and paste formats, Column widths and row heights, Format Painter, Paste Special, Freezing the top row of table
Conditional Formatting On Advance, Excel, VBA, Dashboard
Conditional Formatting – 1, What is Conditional Formatting?, Basic CF with values, Using Formulas in CF, Examples of CF, Conditional Formatting – 2, Advanced CF Options, Using Icons, Using Data Bars, Using Heat-maps, Highlighting Top 5, above average etc., Conditional Formatting – 3, Conditional Formatting & Dates, Conditional Formatting and Duplicate Data, Complex CF Examples
Basic Charting in Advance, Excel, VBA, Dashboard
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,
Advance, Excel, VBA, Dashboard Charting
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),
Class Projects
Excel School Class Project # 1, Class project objective, building an interactive chart to compare our performance with competition, Excel School Class Project # 2, Class project objective, Building a dynamic dashboard using camera tool and macros,
Using Tables in Advance, Excel, VBA, Dashboard
Excel Tables, What Is A Table?, Format As Table, Understand Table Formatting Options, Filters, Can Get Multiple Filters In The Same Sheet, Total Row, Ability To Select Type Of Summary, Structured References – What Are They?, Write A Formula For Profit %, Write A Formula For Profitability Per Customer, Advantages Of Tables, Convert Table To Named Range, Compatibility Of Tables With Excel 2003 And Earlier,
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,
Using Advance- Advance, Excel, VBA, Dashboard
Export Excel to Other Formats, How to Export Excel Content to PPT or Word Documents?, How to Export Excel Content to Web Pages, How to Export Excel Content to PDF, Excel 2010 – A Short Introduction, New features in Excel 2010, Spark lines, Conditional Formatting Enhancements, Backstage view / File menu, etc.
Macros & VBA
Introduction To Macros, What is a Macro, Using Macro Recorder and Your First Macro, Editing Macros & VBA, Macros vs. UDFs, How to get more help on Macros & Advanced Excel
Advance, Excel, VBA, Dashboard
What is a Dashboard, 5 Dashboard Examples, Deriving our own dashboard definition, Looking at Dashboard definitions around web, Conclusions, Dashboard Creation Process, Why have a process – Learning from a Failed Dashboard, Defining the Dashboard Creation Process, Example Dashboard made with such process, Conclusions, Making Excel Dashboards – Part 1, Defining the purpose of the dashboard, Finding appropriate metrics for the dashboard, Making a mock up of the dashboard, Making Excel Dashboards – Part 2, Understanding the data, Writing Formulas, Making Charts, Adding Conditional Formats, Putting Everything Together, Conclusions & Homework, Dashboard Design Tips & Tricks, Making Website Dashboard, Making a Sales Dashboard, Making a KPI Dashboard
Who Should Attend
Want to change career or apply for a new job, Already prepare reports of any kind that require updating with new data on a regular basis (weekly, monthly….) and, 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.
Pre-requisites
Familiar with putting together any type of report in Excel to be able to apply the time saving and visualization techniques, Be able to build and apply basic formulas in Excel, PivotTable and PivotChart knowledge is helpful, but expertise is not necessary to take this course
What You Need To Bring
Notepad, Pen/Pencil, Laptop
Key Takeaways
MS Excel Dashboard Training with Advanced Macros and Formulas, Knowledge to create reports:- Save Time, Employee Performance Plan, Great Visibility and insight, Ongoing Improvement, Time Saving, Ability to identify and correct negative trends, Judge performance against our Plan, Choosing what metrics to track, Building your own Executive Dashboard
About Trainer
- Having 17+ Years of Experience in Data Analysis, Clinical Data Research, Data Mining and Presentation
- Delivered more than 50+ VBA Projects (onsite & offsite)
- Currently working on 10+ VBA Projects
