Effective analysis, dashboards and business intelligence tools in MS Excel 2400-ZEWW922
For years Excel has been the most widely used program for processing and analyzing data. Due to changing needs, there have been many changes to this program in recent years. Many functions and tools have been created that automate work and make the creation of interactive reports, which is now much simpler and often no longer requires programming in VBA. In addition, Excel has been equipped with Business Intelligence tools (the same as in Power BI, among others) for importing and processing data (Power Query), managing the data model - creating SQL relationships, using database functions or performance indicators, the so-called KPIs (Power Pivot), and analyzing regional data and creating visualizations on maps (Power Map). All this is now possible directly in Excel for databases with hundreds of millions of records (rows), plus the whole process can be fully automated. This is why the knowledge provided in the course is so important and valued on the job market.
The course consists of 2 parts.
Part 1 (Working effectively in MS Excel):
1. Preparing data for analysis (tables, text functions, advanced search functions)
2. Work automation (dynamic tables, dynamic ranges and reference functions)
3. Data analysis (advanced pivot tables and advanced conditional calculation functions)
4. Form creation (conditional formatting and data validation)
5. Advanced dynamic table functions (advanced dynamic table functions, performance and calculation optimization)
6. Form controls and application cases (form controls, business applications)
Part 2 (Dashboards and Business Intelligence tools in Excel):
1. Power Query (import, data processing, query parameterization)
2. Power Pivot (data model, actions, calculated fields and columns, DAX language)
3. Power Map (built-in geolocation, creation of custom maps, visualization of geographic data)
4. Interactive analytical reports
5. Business application classes
Note 1: hybrid form of classes - students can participate in classes in a stationary or remote form (to choose from).
Note 2: the course is part of the educational path "Analytical dashboards using Business Intelligence tools", upon completion of which participants receive a certificate. Details on the WNE UW website (Student> Educational Paths).
Note 3: classes are held in blocks of 4 didactic hours each. The entire course has 60 hours. Optionally, instead of this course, you can take two 30-hour courses: "Effective Data Analysis in MS Excel" and "Dashboards and Power Excel Tools".
Note 4: the course requires a lot of self-work on the part of students and is dedicated to those who want to learn to work well in Excel. The class is taught at an advanced level.
Note 5: The software version in the course is Microsoft Office 2021 or later (language version: Polish, operating system: Windows). It is not possible to use an earlier version (many functions are missing). The English language version is not a major problem, but the course a different operating system is (e.g. MacOS) due to the limitations of the Office suite (possible lack of necessary software components). The University of Warsaw does not provide participants with the necessary computer hardware or software.
Type of course
Prerequisites (description)
Course coordinators
Learning outcomes
The listener has gained the ability to efficiently and effectively use MS Excel and utilize its advanced tools and functions. The listener also gained the ability to create advanced interactive reports using Business Intelligence tools in the form of dashboards.
The course topics included the following: preparing data for analysis (text and search and address functions), working with databases (creating tables, dynamic references, defining dynamic ranges), analyzing data (counting and conditional calculation functions, database and table functions, advanced pivot tables), creating automatic reports (dynamic tables and Excel 365 novelties), creating user interface for reports (conditional formatting, validation, control form), creating dashboards (including presentation of data on different types of charts, using Power Query tools (importing different types of data formats, transforming data, adding custom fields, combining data), Power Pivot (creating a data model, as well as constructing pivot tables based on the data model) and Power Map (using geolocation to visualize data on maps, saving created maps in the traditional way and in the form of 3D visualization). Thanks to the course, the student knows the latest trends in working on data, creating reports and visualizing the obtained results, and is able to create professional business applications in MS Excel. The knowledge gained in the course allows, among other things, to create advanced reports and interpret the results obtained in them.
Assessment criteria
The final grade consists of:
- 2 cumulative homework (50 points each)
- additional activity on discussion forums
Punctation:
Score points
[0-50) 2
[50-60) 3
[60-70) 3.5
[70-80) 4
[80-90) 4.5
[90-100) 5
from 100 5!
Bibliography
own materials
Additional information
Additional information (registration calendar, class conductors, localization and schedules of classes), might be available in the USOSweb system: