About Our Microsoft Excel and Microsoft Excel VBA Consulting
EMAGENIT covers Excel consulting for Microsoft Excel for Windows
and Excel for Mac.
In our Excel consulting, we review an existing Excel project, recommend better ways to do things and then
upgrade the project with those
recommendations. We can upgrade both general Excel projects (projects containing formulas, charts, data validation, conditional formatting, logic...)
as well as the most advanced Excel VBA projects. In most of our consulting cases, our customers are trying to move away from a manual process to some
type of Excel automation. For example you keep making the same manual chart over and over again for multiple reports and you want to click a
button and have 50 of those charts made at once.
In addition to just modifying your existing project, we can also provide consulting
on those modifications showing you or your staff how we did it. The modifications can
also be developed into a formal class where the upgrades serve
as the core training. The modifications can also be discussed in
a less formal way with EMAGENIT just reviewing what we did with
you or your people and answering specific questions.
Just as a note, coupling training
with a project is a very good way to get a piece of
software designed for your group without involving a lot of red
tape. Just treat it like a class and the example is
the working piece of software you wanted. We have used this strategy to avoid a lot of red tape in companies
that make it challenging at best to get something designed.
EMAGENIT Excel Consulting and Your Project Size
EMAGENIT works on Excel
consulting projects of all size, difficulty and duration.
We provide Excel consulting for the most basic of tasks up to
projects that can take a few months or years to develop. We can develop simple
automation projects to the most advanced modeling, data processing and dashboard style
projects you can think of.
What We Can Use in Our Excel Consulting to Enhance Your Project
EMAGENIT can build a custom consulting solution in Excel using only Excel components if you desire.
We can also use any of the following to create a truly unique custom Excel solution.
Component usage of course depends on the operating system. Basically if
it can be tied into Excel in some manner, we use it. The list below is only a starting
point for what can be used.
- Visual Basic for Applications (VBA)
- Databases like SQL server, Access, Oracle,...
- Web based databases
- Visual Studio using VB.Net, CSharp
- Perl, MacScript,...
- The web using VB.NET, JavaScript, CSS, HTML, ASPX including web based charts,...
- Other Microsoft products like Word, PowerPoint, Outlook,...
- Text files
- Web pages including generating, reading and writing to them
We like to say all of the above is just kind of getting warmed up with what we actually do.
We call it hybrid software design, take the best of each tool
and using it according to its strengths.
And by the way, EMAGENIT does its own graphic design and builds its own website, like the one you are using right now is a good
example of our work.
Common Microsoft Excel Consulting Topics
(Non Automation)
As mentioned above, EMAGENIT
considers Excel consulting to be taking an existing project and
upgrading it or providing know how for a project through
examples. Listed below are some common topics that we cover when
consulting on general non-VBA Excel projects.
Models
- Implementation of worksheet functions like VLOOKUP, INDIRECT, ROW, COLUMN, INDEX, MATCH, COUNTIF, COUNTIFS, SUMIF, SUMIFS, COUNTA, TEXT, IF, AND, OR...in models
- Upgrades to modeling formulas utilizing advanced logic
- Implementation of advanced construction techniques in Excel models including organizational strategies, naming strategies and the use of array formulas
- Upgrades to various financial and accounting models
- Upgrades to various engineering and science models including numerical analysis techniques
- Designing models for use with the design optimization tool Solver
Dashboards and Visual interfaces
- We build all different types of
dashboards that track different KPIs using the worksheet,
ActiveX controls, formulas, data validation, conditional
formatting, drawing shapes and charts
- Upgrades to basic worksheet interfaces using ActiveX and Form controls
- Upgrades to worksheet dashboards utilizing ActiveX controls, data validation, conditional formatting, charts, pictures, Excel tables, names and advanced array formulas
- Upgrades to worksheet based calendars, timelines and schedules using advanced logic, data validation
and conditional formatting
- Upgrades to basic charts using advanced formatting techniques
and advanced formulas
Data Processing
- Upgrades to existing worksheet data layout using advanced table design strategies
- Using Excel tables to track and format data on a worksheet
- Implementation of adaptive data
processing formulas that grow with Excel data
- Implementation of adaptive names in
data processing formulas that grow with Excel data
- Implementation of adaptive names in
data processing formulas that find specific data ranges
- Implementation of advanced table lookup
formulas in Excel data processing projects
- Advanced data fusion of multiple text files, databases and other workbooks in Excel data processing projects
Report Generation
- Advanced design of worksheet based reports using charts, drawing shapes, conditional formatting and advanced formulas
- Integration of Excel based information with Microsoft Word and PowerPoint
- Advanced pivot table reports using multiple data sources and relationships
- Advanced pivot chart reports using slicers and timelines
- Implementation of SQL queries on Excel worksheets using Microsoft Query and Excel tables
- Rapid reporting using advanced worksheet templates
If you wish to learn more about these
consulting topics on your own, the following Excel workshops cover the same basic material:
Common Microsoft Excel VBA Consulting Topics
(Automation)
As mentioned above, EMAGENIT
considers Excel VBA consulting to be taking an existing project
and upgrading it or providing know how for a VBA project through
examples. Listed below are some common topics that we cover when consulting on Excel VBA projects.
Data Processing
- Analyzing data with Excel VBA macros that have shifting header positions, unknown cell
positions and variable row size
- Processing data in multiple workbooks and worksheets using Excel VBA macros
- Using Excel VBA to process data tables that are stacked on top of each other
or indented on worksheets
- Reading text files, cleaning their data, rearranging their data then processing it using VBA
- Using VBA macros to convert table configurations on the worksheet into other
table configurations on the worksheet
- Querying databases, worksheets and text files using VBA, SQL, ADO or DAO
- Excel VBA macros that integrate multiple data sources stored in databases, text files and workbooks
- VBA macros that clean data and perform table comparisons removing inconsistencies such as extra names, blanks,...
- Excel VBA macros that control the Windows folder and file system while processing data
- VBA macros that communicate with Google Gmail and Google spreadsheets downloading and uploading data
Report Generation
- Using VBA to generate various
accounting and financial type reports
- Building various worksheet summary tables using Excel VBA
- Using VBA to build various calendar
and timeline style reports on worksheets
- Using Excel VBA macros to build advanced pivot tables
- Integrating separate teams using
separate workbooks in various status reports using colored cells and fonts, can be 100's or 1000's of workbooks
- Using Excel VBA macros that process data and automatically generate
report tables on multiple worksheets in multiple workbooks
- Creating chart sheets and embedded charts on worksheets automatically with VBA
- Using Excel VBA to automatically format charts and place labels and drawing shapes at specific points on the chart
- Excel VBA macros that query databases, worksheets and text files using SQL, ADO or DAO
- Advanced report generation tools using Excel VBA macros that control Microsoft Excel, Microsoft Word, Microsoft Outlook and Microsoft PowerPoint
Dashboard and Visual Interfaces
- Automated technical drawings,
configuration layouts and process diagrams using Excel VBA
- Various advanced Excel dashboard designs using events, ActiveX controls, charts, drawing shapes and the worksheet itself
- Interactive Excel VBA userforms that display dashboard information and provide dashboard control
- Advanced worksheet displays showing
project status, productivity,...
- Advanced worksheet schedule displays showing project break down, scheduled work,...
- Advanced timeline displays showing project phases, work being performed, milestones,...
- Interactive VBA event driven maps showing productivity, various revenues and expenses,...
- Advanced Excel VBA driven charts displaying productivity, ratios, goals,...
- Advanced engineering and science model visualization using Excel VBA to illustrate processes and design configurations
Modeling
- Using VBA based User Defined Worksheet Functions (UDF's) in models to implement advanced lookups, finds and logic
- Using VBA based User Defined Worksheet Functions (UDF's) in models to combine Excel VBA and other
languages like FORTRAN, VB.NET and CSharp. This is to integrate existing verified code, to perform advanced calculations and for flat out calculation speed
- Using VBA macros as a wrapper to an existing formula based worksheet model managing its data, formulas, names and information flow
- Using Excel VBA to build an upload and download system for model information
- Using Excel VBA macros to build a batch processing system for a model and managing the batch results
- Automating Solver and Goal Seek and applying them to worksheet models using Excel VBA
If you wish to learn more about these
consulting topics on your own, the following Excel VBA workshops cover the same basic material:
