Microsoft Excel Onsites and Excel VBA Onsites

In our Excel onsites, we cover the little things as well as the big things that you need to know when constructing formulas and processing data. We cover the beginning worksheet functions like SUM as well as important worksheet functions like IF, INDIRECT and VLOOKUP. Our training also covers the subtle important things that are often overlooked like names, Excel tables and string concatenation ( i.e. &'s). We also cover how to lay things out on the worksheet and format it for rapid reporting and modeling. In the example worksheet above, a simple IF/VLOOKUP formula tracks the order # in an Excel table on another worksheet that is linked to a database while Conditional Formatting is used to switch the transit graphic status. Bing maps is used to display the current location. When manual Excel methods are not enough, our Excel VBA onsite training can show you how to automate things like formula construction, worksheet formatting, table lookups, database queries and picture/shape manipulation.

In our Excel onsites, we go way beyond normal Excel training, we can show you how to combine Excel features to produce stunning visual displays that help convey the state of your data. We will teach you how to use formulas, charts, Conditional Formatting, worksheet formatting, drawing shapes and pictures separately then combine them to produce a desired result. We have 22 years of experience designing software in Excel for customers like Lockheed Martin and Caterpillar. In the worksheet example above, pie charts and drawing shapes are used to build the gauges while Conditional Formatting is used to color the red/ green status. ActiveX controls are used to select the product, month and year to view in the charts. In our Excel VBA onsites, we will show you how to automate advanced displays using shapes, pictures, worksheet cell formatting, userforms, and ActiveX controls that allow high-end software to be developed like project management tools, dashboards, technical schematics, factory production...etc.

In our Excel onsites, we can show you how to use things like Data Validation and Conditional Formatting in unique ways to produce various management tools like coloring the cells red in the simple inventory tool above. Using Conditional Formatting, we can also show you how to use it to just flag your data. Oh yes, we also cover the logic. In the example worksheet above, VLOOKUP and IF formulas are used to distribute the inventory data from an Excel table throughout the dashboard while Conditional Formatting is used to color the short values red. In our Excel VBA onsites, we can show you how to build the data processing tools that analyze and organize data into usable tables that can then be used for reports. We can also show you how to automate the report above, output it to a different workbook, and email it to 20 people.

In our Excel onsites, we can show you how to create PivotTables and Pivot Charts. We can also show you how to shape and process your data using Power Query or using Microsoft Query and tying your data to external data sources like other workbooks, text files, databases. In the worksheet example above, a Pivot Charts and slicer are used to display product data by month and day. Formatting is used to break the days down. The Pivot Charts table is linked to an Access database and can be refreshed. In our Excel VBA onsites, we can show you how to automate PivotTables and Pivot Charts at the click of a button. We can also show you how to query workbooks, text files and databases using VBA and merge their data into tables that can then be used by PivotTables and Pivot Charts.

In our Excel onsite training, we also show you how to use Excel VBA to create powerful reporting, modeling and dashboard tools for a fraction of the cost of existing software. We teach you the programing strategies that you need to succeed like processing100 workbooks of data. Our training can take you from beginning to the most advanced VBA programming topics using our 22 years of Excel VBA software design experience. In the sample Excel VBA code above, AutoFilter is used to filter the data by shift then the data is copied and pasted to a new workbook and worksheet. The total hours is then summed.