Our Excel "Swiss Army Knife" manual for business professionals.
Over 2200+ pages and hundreds of
examples of useful general Excel tips, features, formulas and strategies along with our complete Excel VBA write up, everything.
Questions? 1.805.498.7162 Customers >
Microsoft Excel Manual Specifics
| For: |
Business Professionals |
| Format: |
ADOBE PDF and Excel Files
as zip file download |
| Details: |
Covers beginning general Excel to advanced Excel VBA topics, everything from all of our manuals |
| Book: |
2200 + pages with examples |
| Platform: |
Windows 7 - Windows 10 |
| Sent: |
Sent within 3 hr after purchase Mon-Fri 8:30 AM - 5:00 PM PT |
| Versions: |
Excel for Windows 2007-2019, Office 365 |
Manual Introduction. This all in one Excel and Excel VBA self study manual contains all the topics found
in all of our other business manuals for Excel and Excel VBA. Topics include basic cell operation, worksheet formatting, formula design, sorting,
filtering, Pivot Tables, array formulas, external data access, modeling, chart creation and linking Excel with other programs
like Word and PowerPoint. It also covers dashboard topics including using ActiveX controls, charts and
drawing shapes. Also covered is our complete Beginning Excel VBA and Advanced Excel VBA
write-ups.
Price: $169.00
Add to Cart
Our Refund Policy >
Learning Module 1:
Creating, Opening, Saving Workbooks; Controlling Workbook Views; Using
the Ribbon and Using Help
- How to use the manual and terminology used in the manual
- Mouse terminology in the manual
- Introduction to Microsoft Excel
- Excel program interface review including how to use Excel on-line help
- How to create a new workbook
- How to open and close a workbook
- How to save a workbook
- How to use the Ribbon, shortcut menus, dialog boxes and hot keys
- How to arrange workbook windows
- How to freeze a worksheet row/column
- How to split a worksheet screen
- Overview of the main Excel elements that make up a workbook
Learning Module 2: How Can
Microsoft Excel Be Used in Business
- Discusses how Excel can be used in the business process in the areas of analysis, information
integration, VBA program development, dashboard development, rapid analysis, team integration and data processing.
Learning Module 3:
Worksheets, Formulas, Cell Naming, Functions, Formatting and Security,
Array Formulas, Data Tools, Pivot Tables and ActiveX Controls
- How to create and manage worksheets in Excel
- How to type data in a worksheet cell
and how data is really stored in Excel
- How to layout and arrange business problems on the worksheet
- How to create formulas, use different
cell references and operators, perform drag and fills
- How to name cells and ranges and use them in business formulas
- How to type, debug and use Excel worksheet functions like IF, VLOOKUP, SUMIF, COUNTIF, INDEX,
SUM, MAX, MIN, ...and much much more
- How to create and use dynamic names
in formulas
- How to create, debug and use powerful array formulas on the worksheet
- How to format worksheets including cell colors, fonts, borders, merging, text alignment...
- How to use the Conditional Formatting and Data Validation features in Excel to create interactive business applications on the worksheet
- How to track business data using the Conditional Formatting feature
- How to protect worksheets and workbooks
- How to create business forms on the worksheet
- How to analyze sort and filter data using Excel's Sort, Auto Filter and Advanced Filter tools
- How to import text files into Excel
- How to link Excel with Word and PowerPoint
- How to create, format and use drawing shapes on the worksheet
- How to create, format and use pictures on the worksheet
- How to transmit cell values dynamically to drawing shapes to create interactive displays
- How to create Excel tables for changing worksheet data and use their structured references in formulas
- How to link to external data using Excel tables
- How to use slicers with your Excel tables to create interactive worksheet displays
- How to create and edit Pivot Tables on the worksheet
- How to perform statistics on Pivot Tables
- How to use slicers and timelines with your Pivot Tables
- How to create Pivot Charts from your Pivot Tables
- How to create, edit and use ActiveX and Form controls on the worksheet
Learning Module 4: Creating Business Charts and Chart Reports in Excel
- Data visualization in Excel using charts
- How charts are created and work in Excel
- How to create and edit a Chart
- Editing and formatting a chart
- Creating and Managing Chart Trendlines
- How to create charts with a primary and secondary Y-axis
- Creating limit lines for a chart
- Printing a chart sheet
- How to layout a chart report on a worksheet and print it
Learning Module 5: Accessing Data Bases and Data Sources from Microsoft Excel
- Overview of relational databases
- Overview of ODBC and MS Query
- How to use MS Query to access a database
- How to use MS Query to query an Excel workbook
- How to use MS Query to query a text file
- Overview of how to use Power Query
- How to use Power Query to query an Access Database
- How to use Power Query to query a text file
- How to use Power Query to query an Excel file
Learning Module 6: Problem Optimization in Excel Using Solver
- Overview of problem optimization
- How to layout an optimization problem in Excel
- Command and control worksheet overview
- How to use Solver in Microsoft Excel
- How to drive multiple workbooks using Solver
- Other design optimization packages for Excel
Learning Module 7: Linking Teams Together in Excel
- Overview of external references
- How to link Excel workbooks
- How to manage Excel workbook links
- How to distribute linked Excel workbooks
Learning Module 8: Advanced Excel Formulas
- How to extract start and end points of data
- How to use the IF worksheet function in various advanced logic scenarios
- How to summarize data using advanced logic
- How to separate (parse) text
- How to determine specific items from worksheet data
Learning Module 9: Microsoft Excel Dashboard Design
- Excel dashboard formula design, debugging and
linking review
- Key logic formulas and functions that your Excel
dashboards will use
- Constructing, managing
and using range and cell names in your Excel dashboard
- How to create proper tables and
use Excel tables in your Excel dashboards
- How to create dynamic names that
automatically adjust to changing data and use them in various Excel
features
- Key worksheet functions and array
formulas used in Excel data processing
- Metric formula construction
techniques for Excel dashboards
- Learn about the different
dashboard types, benchmarks and KPI's
- Learn how to use ActiveX controls
and data validation to create Excel dashboard interfaces
- Learn how to link drawing shapes
with cells and pictures with ranges to create adaptive dashboard
displays
- Learn how to create and format key
Excel dashboard charts like sunburst, tree map, bubble and radar
- Learn how to create speedometer,
linear and doughnut gauge charts
- Learn how to create milestone
charts
- How to use conditional formatting
on the worksheet to build various status, project and timeline
displays
- How to create custom map displays
on the worksheet
- Learn how to use Microsoft Query,
Power Query and various OLEDB tools to automatically query and
filter external dashboard data
- Learn how to utilize pivot tables,
pivot charts, slicers, timelines and various pivot table
calculations to build dashboard displays
- Learn how to create custom VBA
worksheet functions that can perform amazing calculations
Learning Modules 10-11: Future Expansion
Learning Module 12: VBA Editor and Module Basics
- VBA overview, what is VBA, how is it used
- VBA project and module overview
- What is a procedure and its basic structure?
- How to use key procedure types used in VBA
- VBA statement, syntax and keyword overview
- VBA editor overview, operation and window review
- Macro protection review
- How to manage VBA modules and type in them
- How to turn off and on key VBA Editor features
- VBA procedure calling rules
- Referencing and integrating code from other VBA projects
- How to debug your code using the VBA Editor
Learning Module 13: VBA Language Review | Controlling Key Excel Elements with VBA
- VBA variables, constants and arrays (declaring, scope and data typing)
- How to create and use expressions in VBA that perform calculations
- How to use different operators in your expressions (logical, arithmetic, string, comparison...)
- How to run VBA and Excel functions in your procedures
- How to use VBA arrays in your procedures
- How to create and use logic in your procedures
- How to create and use For and Do loops in your procedures
- What is an object, a property and a method
- How to correctly build object expressions that identify and command Excel objects
- How to run properties and methods in your VBA procedures
- How to create and effectively use Set statements in your VBA code
- How to build For…Each Next loops to loop through objects scanning for items
- How to use Excel/VBA on-line help
- How to use the VBA macro recorder to record Excel business tasks in VBA
- How to use the Object Browser to look up Objects and their code
- How to provide error protection in your procedures
- Controlling Excel, Workbooks, Worksheets, Windows, Page Setup and Pivot Tables with VBA
- Controlling Ranges, Range Fonts, Range Borders, Range Colors with VBA
- Controlling Conditional Formatting, Data Validation, Sorting, Excel Tables and Queries with VBA
- How to lock on to changing worksheet data using VBA
- How to use VBA to control and build charts
- Basic data processing strategies for processing worksheet data using VBA
- How to build basic dashboards in Excel by automating charts, drawing shapes and worksheet cells
Learning Module 14: VBA Function Procedure Design
- Benefits of using Function procedures and User Defined Worksheet Functions (UDF's) for business tasks
- Function procedure and UDF differences
- How to declare Function procedures and UDF’s in VBA
- How to create arguments for Function procedures and UDF’s
- How to call UDF’s from a worksheet formula
- Calling Function procedures from other VBA procedures
- Function procedure and UDF procedure interface design
- How to pass worksheet ranges as arguments to UDF's
- Returning scalar and array values from UDF’s to worksheet formulas
- Using VBA and Excel Worksheet functions in your VBA procedures
Learning Module 15: VBA Sub Procedure Design
- Benefits of using Sub procedures for business tasks
- Declaring Sub procedures in VBA
- Creating arguments for Sub procedures
- Calling Sub procedures from other VBA procedures
- Executing Sub procedures via the Macro dialog box
- Executing Sub procedures via shortcut keys
- Executing Sub procedures via graphics
- How to run Sub Procedures by assigning them to drawing shapes and pictures
- How to assign Sub procedures to shapes and pictures using VBA
- How to tell what picture or shape button was clicked using VBA
- Sub procedure interface design
- Sub procedure design tips
- Property procedure overview
Learning Module 16: Using Events, ActiveX Controls, Userforms and VBA to Create Advanced Program Interfaces
- Developing business GUI's and applications in Excel
- Creating graphics that automatically display business data on the worksheet
- Assigning VBA Sub procedures to worksheet based graphics
- Creating event procedures that run automatically when something happens to Excel, a chart, a workbook or a worksheet
- Using ActiveX and Form controls to create GUI’s on the worksheet
- Creating event procedures for controls placed on a worksheet
- Review of creating userforms for business programs
- How to create and control userforms in Excel
- Creating controls and graphics on userforms
- Commanding userforms and their controls with VBA
- Userform design tips
- Creating toolbars from userforms to control your Excel tools
- Creating a tab and button in Excel's Ribbon
Learning Module 17: Commanding Databases and Applications Using VBA
- Automation overview
- How to command other applications from Excel using VBA
- How to use CreateObject to control other applications
- Using DDE with VBA
- How to use DAO to communicate with Microsoft Access
- How to use ADO to communicate with Databases
- How to use ADO to communicate with Microsoft Excel
- How to use ADO to communicate with Text Files
- How to use the FileSystemObject in VBA to control the Windows file system
- How to loop through 100's of workbooks in folders using the FileSystemObject
- How to control Window's Shell
- How to use VBA to control Microsoft Word for reporting purposes
_EMAGENIT_2019.gif)
