Advanced Excel & Macros

Learn proven quantitative tools and methods of Microsoft Excel for analysing, interpreting, and utilizing data, so that more informed and reliable business decisions can be made.

Some tasks have to be performed repeatedly in Excel. With a macro, you can store these sets of procedures and avoid repeating the entire sequence of actions each time you need to perform them. These can then be easily activated with a keystroke combination, a toolbar button, or an on-screen pointer.

Benefits of this Advanced Excel Workshop:

• Perform complex calculations more efficiently, using various Excel functions.
• Create Dashboards and MIS reports.
• Consolidate and manage data from multiple workbooks.
• Save Time by Automating Repetitive Tasks with Simple-to-Use Macros

Course Content

• Advanced Formulas and Functions:
  • Logical – IF, OR, AND, SUMIF, COUNTIF, AVERAGEIF
  • Lookup and Reference Functions – Vlookup, Hlookup, Xlookup, Index, Match, Filter, Indirect, Chooseraw, Choosecol
  • Text Func tions
  • Date and Time Formulas

• What-If Analysis (Data Analysis)
  • Scenario Analysis
  • Goal Seek
  • Data Table
• Advanced Conditional Formatting
• Power of dynamic tables
• Advanced Filters
• Working on the large size files
• Sparklines, Slicers and Timelines
• Advanced Pivot Tables
  • PivotTable calculations
  • Using slicers in PivotTables
  • Conditional formatting in PivotTables
  • Creating PivotCharts
• Advanced Charting
• Importing data from different sources
• Avoid manual data entry – User restrictions with Data Validation
• Macros
  • Recognizing situations ideal for creating macros
  • How to create a macro by recording, and why this is usually the best way to create a macro
  • Four ways to run a macro: keystroke shortcut, toolbar button, command, or graphic
• Tips and Tricks