Introduction to EXCEL SPREADSHEET
- Data entry, Importing, sorting and coding
- Descriptive analysis – Frequency tables, Custom tables, Charts, Graphs, Cross tabulations
- Understanding the basic concepts of a spreadsheet, including: • the idea of templates • cells, rows and columns • cell coordinates • entering data into a spreadsheet cell • Setting up labels, including:
- Setting column widths • aligning data in cells • entering column and row labels •
- Creating and copying formulas
- Making changes in a spreadsheet • inserting rows and columns • deleting rows and columns
- Producing a printed copy of the contents of a spreadsheet file
- Saving a backup copy of your work
- Appreciating the power of spreadsheet templates
- Create a basic worksheet by entering text, values, and formulas.
- Change the appearance of worksheet data by using a variety of formatting techniques.
Intermediate EXCEL
- Advanced Excel Shortcuts
-
- Extensive exercise on excel shortcuts
- Doing away with the mouse
- Mastering the shortcut window
- Filter and sort Excel data.
- Create formulas by using some of Excel’s built-in functions.
-
- Upper, Lower, Proper and TRM
- Left, Mid, And Right
- CONCATENATE & (Ampersand)
- CONCATENATE Vs. & (Ampersand)
- Roundup, And ROUNDDOWN
- VLOOKUP And HLOOKUP
- IF
- Nested IF
- If and AND
- SUMIF
- Error Values in Excel…
- Rand and Rand between
- Pivot Tables
- Charts
-
- Ribbon Tour
- Creating a chart
- Chart Layout Options
- Multiple Series within a chart
- Modifying Gridlines
- Filters
-
- Ribbon Tour
- Creating a chart
- Chart Layout Options
- Multiple Series within a chart
- Modifying Gridlines
- Text to Columns (Data Parsing)
-
- UPPER, LOWER, PROPER AND TRIM
Advanced EXCEL
- Understanding References
-
- Relative, Absolute & Mixed reference
- Referencing different workbooks & Merging
- Consolidating data, linking cells in different work books
- Utilizing Dynamic Named Ranges for writing formulas and functions
- Data Analysis Report Pivot Tables & Pivot Charts
-
- Analyzing and generating reports of large data using PivotTables.
- Creating PivotTables, editing and updating changes in PivotTable.
- Using Slicers in Pivot Table
- Creating Pivot Chart
- Data Validations and Protection
- Financial Analysis and Formulas
-
- Using IRR
- Using NPV
- Budgeting under Scenarios
- Excel Macros and VBA for Programming
-
- Introduction to Excel Macros
- Recording and Running a Macro
- Absolute vs Relative
- Assigning Buttons to Macros
- Assigning Macros to an Auto Shape and to an icon on a Tool bar
- Sheet Controls and User Form Controls
- Deleting a Macro