Since 1993, the way to automate Excel workbooks was with a Visual Basic for Applications macro. I have been working with VBA since 1999, and have developed solutions for large companies including TD Bank and McDonald’s.
VBA allows for advanced customization on any spreadsheet; click a button, and let Excel do its magic! These customizations include:
- Formatting and importing worksheets and exporting to a specific format in order to provide to a 3rd party or to import into a database.
- Performing a large number of repetitive calculations, based on criteria you select!
Get and Transform Data
Get and Transform Data was introduced as Power Query in 2010, and is used to bring data from other sources, including databases, text files and even web tables! I have been developing solutions in Power Query since 2013.
This allows for complex data imports to be done without needing to use complex VBA code! These imports include:
- Importing Excel files distributed to multiple stakeholders into a single consolidated sheet.
- Importing from external databases to summarize in Power Pivot.
Power BI / Power Pivot
Power BI was initially released in 2014, based on the Power Query Excel add-in that was introduced in 2010. I have been developing solutions in Power Query since 2016, and have developed dashboards to support multiple real estate companies.
Benefits of this solution include:
- Accessing dashboards over mobile devices.
- Manipulation of data without needing highly customized VBA code.
Excel Formula Support
Sometimes, the best method of analyzing data doesn’t require a full BI dashboard or data model! Some formulas in Excel are very powerful at analyzing data and do not require custom coding to create fantastic looking dashboards!
These sorts of formulas include:
- Lookup functions (VLOOKUP, and the new XLOOKUP function!)
- Pivot tables, and lookups from pivot tables or data models.
User Guides / Instruction Manuals
After building your brand new process, you need to make sure that it can handle anything that you can throw at it!
This will include:
- Providing user manuals to provide support for the team using the new solution.
- Providing technical manuals for IT support teams, making sure that everything fits into your IT plans!
- Supporting the solution – I am always available by email, video call or in-person to keep your solution up and running!
Having been a developer of a course for a top e-learning site, DataCamp, I can develop custom video content to efficiently train any number of users at your company.
This content can include:
- Video content that you can view on your internal company site.
- Quiz content to make sure that your team knows how to use the solution!