Data Analysis in Excel – Working with Multiple Datasets
Melissa Esquibel began her career in audit and data security at a time when systems and hardware were kept in tightly secured boxes, and IT gatekeepers doled out functionality within a rigid systems architecture. Now, in the present, where end-users know how to access the power of technology independently and according to the agile timelines they require, this push and pull has created quite a mess in many organizations. With her unique experience as the conduit between the gatekeepers and the gate Stormers, she offers a unique perspective as “bridge-builder” in training programs designed to make sense of new productivity platforms, like Office 365 and G Suite and ensure effective implementations.
In the recent past, it was necessary to have some coding skills, an available data analyst or some very strong VLOOKUP skills in order to join related datasets to perform meaningful data analysis. With the new Get & Transform tools and Power Query (which you probably already have, but don’t know it!), you can do this sort of thing yourself with a few clicks. Over the past several versions of Excel, Microsoft has strived to put robust tools into the hands of the people that really need them by making them more intuitive and easier to use. In this session, you’ll learn how do connect related data by like fields, and how to create a larger dataset of similar information from smaller segments, such as being able to analyze all divisions sales performance from datasets presented in different workbooks, but formatted and arranged the same way. And, you’ll be able to do all of this without VLOOKUP formulas, SQL queries, or macros.
- Using Power Query
- Importing various types of data from different data sources into the same analysis
- How the Excel Data Model works
- The right and wrong way to establish relationships between data
- How to use PivotTable functionality with your queries
- How data refreshes (or doesn’t)
- Extracting data from websites to use in your analysis
- How these tools vary by Excel version
Course Level - Any
Who Should Attend
Data Analysts, Finance Professionals, Human Resources Professionals, Information Technology Managers, Operations Managers, Administrative Professionals, Managers, Help Desk, Compliance Managers, Economists, Auditors
Why Should Attend
If you are using any recent version of Microsoft Excel, you have the power to analyze multiple datasets, at the same time, and without writing macros or SQL queries. Rather than waiting in line behind the resident guru’s desk to create queries, you can do it yourself without knowing how to code. With the availability of Power Query and the new Get & Transform tools, you can make short work of analyzing multiple datasets that can be connected via a single field, such as payroll and employee database information connected by employee ID. You can also string together multiple similar datasets without copying and pasting, to analyze a whole year’s worth of data from separately stored monthly data. And you can do it in just a few clicks!