Course Contents on Excel & Macros Reporting

Course Contents on Excel & Macros Reporting

Course Contents on Excel & Macros Reporting

  1. Managing & Formatting Workbooks and Worksheets
  2. Renaming, Copying and Moving data between sheets
  3. Freezing the Titles
  4. Insert, Delete, Copy and Move sheets
  5. Modifying Page Setup options
  6. Preview and Printing the sheets
  7. Formatting cells, rows, columns and numbers
  8. Auto Formatting and Conditional Formatting on the basis of specific value, duplicate records & data bars
  9. Copy and Paste Special Options
  1. Working with Function’s, Referencing and Naming Convention
  2. Applying Naming Convention to range of cells & calling them in different functions
  3. Freezing a cell address in a Formula by $ sign(Absolute Referencing)
  4. Linking sheets and workbooks in a Formula(3D Referencing)
  5. Using Text Functions such as Len, Concatenate, Upper, Lower, Proper, Rept, Trimetc
  6. Using Logical Functions such as If, Multiple Ifs
  7. Using Mathematical Functions such as Sum, Abs, Round, Roundup andRounddown
  8. Using Data and Time Functions such as Today, Now, Date, Day, Month, Year, Workdays and Networkdays
  9. Using Statistical Functions such as Count, Counta, Countblank, Countif, Countifs, Sumif, Sumifs, Average, Max, Min, Small, & Large
  10. Using Lookup and Reference Functions such as Vlookup, Hlookup, Lookup, Index, and Match
  1. Smart Working

a)Linking the sheets through Hyperlinks

b)Using Keyboard Shortcuts to make work faster

  1. Working with Data
  2. Creating Custom Lists
  3. Arranging data in alphabetically order using Sort
  4. Arranging data in customized order using Sort
  5. Separating the data using Text to Columns
  6. Extracting the data using Filters and Advance Filters
  7. Importing data from external sources like Notepad, web etc. Creating a web query
  8. Removing duplicate records from database
  9. Analyzing data by applying multiple functions on database using Subtotals
  10. Designing and Protecting the Forms for the user using Data Validations
  11. Protecting and Sharing the Worksheets & Workbooks
  12. Consolidating the Data from different sheets using Data Consolidation
  1. Analyzing Data by Data Analytical Tools& What if Analysis
  2. Using Pivot Tables & Pivot Charts- Creating and Modifying a Pivot Table Layout, Creating Groups in Pivot table, Inserting a Calculated Field and Representing data through Pivot Charts
  3. Using Goal Seek and Scenarios Manager
  1. Representing Data Using Charts

a)Chart Terminology- Understanding the data and choosing the appropriate chart

b)Defining major chart types and plotting charts on 2 axis

c)Formatting the charts - Adding chart titles, Location and linking them with Powerpoint and word and Printing the charts

  1. Introducing Visual Basic for Applications
a)Introducing VBA Macros
b)Displaying the developer tab
c)About Macro Security
d)Saving workbooks that contain macros
e)VBA Sub Procedures and VBA Functions
f)Creating and Recording VBA Macros
g)Recording your actions to create VBA code
h)Absolute versus relative recording
i)Assigning macro to a button or toolbar
j)Writing VBA Code
k)Understanding Objects, Collection, properties, methods, variables and controlling execution
  1. Functions and Procedures in VBA
  2. An Introductory example
  3. A custom function and procedure
  4. Using the function in a worksheet
  5. Analyzing the custom function
  6. Executing function procedures
  7. Calling custom functions in procedures and Using custom functions in a worksheet formula, Function procedure arguments and Inserting custom functions
  1. Creating User Forms and Reports
  2. Creating Inputbox, Msgboxetc
  3. Working with user forms and reports
  4. Adding controls, Handling events, Creating the user form& quick reports and Creating an event handling procedure
  5. Adding multiple controls like command button, label control, option button control, etc.
  6. Linking controls to cells
  1. Working with Excel Events
  2. Understanding Events
  3. Entering event handled VBA Code
  4. Using events such as workbook level events, open event, sheet activate event, new sheet event, before save event, before close event, worksheet events, change event, selection change event, before right click event, etc.
  1. VBA Examples
  2. Copying a variable-size range
  3. Moving a range
  4. Looping through a range efficiently
  5. Prompting for a cell value
  6. Determining the type of selection
  7. Identifying a multiple selection
  8. Counting selected cells and sheets in workbook
  9. Working with workbooks
  10. Saving and closing all workbooks, and many more.
  1. Handling Errors
  2. Understanding Error Handling
  3. Using Debugging Tools
  4. Understanding VBA Error Trapping Options
  5. Trapping errors with the on error statement
  6. Understanding the err object
  7. Writing an error handling routine

Productivity Expertz

We make people productive

Top View