Universitat Internacional de Catalunya
Advanced Excel and Data Visualization
Other languages of instruction: Catalan, Spanish,
Teaching staff
Outside classroom sessions, students may send questions, comments and requests e-mailto lecturers via e-mail, or alternatively set up a face-to-face appointment with them.
- Joan Morató, jmorato@uic.es
Introduction
Today's business management requires advanced data manipulation skills. Obtaining relevant analytical information from the available data, and preparing it to efficiently support in decision-making is a task of the greatest relevance for managers in all areas and sectors. Skillful data preparation, with effective visualization, can make a difference when communicating the conclusions of a study, presenting a company project or participating in a funding round. In this sense, mastering the tools used for these task is a great added value that increases the employability of the student and enables him to function efficiently in a multitude of environments.
This course covers two of the tools most commonly used for these tasks. The first, the most popular spreadsheet software, Microsoft Excel. The second, one of the most valued data visualization programs, TABLEAU. Thus, the course is naturally divided into two parts: (1) Advanced Excel, (2) Data visualization.
The first part provides advanced spreadsheets skills, with a special emphasis on automating repetitive tasks by programming Macros. Therefore, the course includes an introduction to the Visual Basic programming language. This introduction starts from scratch and uses a methodology suitable for beginners. In this way, in addition to skills in using spreadsheets, the student acquires programming skills that can be used in other areas and with other tools. In the second part, specific skills of the data visualization software are combined with more general skills about summarization and results presentation.
This course responds to the growing demand of professionals with high competence in data management and data visualization. It prepares the student to become a future expert of these two tools within the organizations in which he will develop his professional career.
Pre-course requirements
The Excel part requires a medium / basic level of Microsoft Excel. The skills acquired in the "Information Systems" course (mandatory for first year students of the Degree in Business Administration) suffice this requirement.
For the data visualization part, it is recommended to have a basic knowledge of statistics.
Objectives
1) To learn to use spreadsheets to process information through optimized or automatic procedures, obtaining it from a wide variety of external sources and with varied formats, debugging and organizing it in an optimal way for further processing.
2) To learn to create data visualizations that provide relevant information and help decision-making in the context of organizations.
Competences/Learning outcomes of the degree programme
- 31 - To develop the ability to identify and interpret numerical data.
- 32 - To acquire problem solving skills based on quantitative and qualitative information.
- 33 - To be able to search for, interpret and convey information.
- 36 - To interpret quantitative and qualitative data and apply mathematical and statistical tools to business processes.
- 41 - To be able to descriptively summarise information.
- 50 - To acquire the ability to relate concepts, analyse and synthesise.
- 52 - To develop interpersonal skills and the ability to work as part of a team.
- 53 - To acquire the skills necessary to learn autonomously.
- 54 - To be able to express one’s ideas and formulate arguments in a logical and coherent way, both verbally and in writing.
- 59 - To skilfully use software and ICTs.
- 61 - To develop skills for adapting to new situations.
- 66 - To be able to retrieve and manage information.
Learning outcomes of the subject
Upon successful completion of the course, the student will be able to carry out information treatment processes using Excel and TABLEAU, covering all the phases of information management: data acquisition, debugging, aggregation, data analysis, data visualization and graphical presentation of conclusions.
He will also be able to program simple algorithms to automate repetitive tasks of information management.
Syllabus
Part 1 - Advanced Excel:
Unit 01 - ORGANIZATIONAL CONTEXT AND BASIC FUNCTIONS REVIEW: formatting, basic calculations, use of tables, formulas and references, search and reference functions, pivot tables and pivot charts, etc.
Unit 02 - PROCESS AUTOMATION: introduction to macros and visual basic.
Unit 03 - DATA SERIES TREATMENT: data types management, import, data debugging and transformation, sorting, correspondences generation and detection of missing elements, detection of anomalies. Error management and error debugging.
Unit 04 - EXTERNAL DATA SOURCES: data import and export. Data models and databases. Criteria and methods for comprehensive and systematic testing of software procedures.
Unit 05 - MORE ADVANCED FUNCTIONS AND TOOLS: multi-user sheet management, data protection and form management, scenario analysis, goal search, mini-charts, advanced pivot table and pivot chart functions, Excel Add-ins, etc.
Unit 06 - BEYOND EXCEL: the web (Google Apps (forms), Office Web Apps), reports generation and automation, real-time financial information retrieval and processing.
Part 2 - Data Visualization:
Unit 01 - INTRODUCTION: data visualization, software installation.
Unit 02 - LOG ON TABLEAU: Tableau software.
Unit 03 - SIMPLE VISUALIZATIONS: creating simple visualizations of a single variable.
Unit 04 - ADVANCED VISUALIZATIONS I: relating two variables.
Unit 05 - ADVANCED VISUALIZATIONS II: relating many variables in a visualization.
Unit 06 - MANIPULATING GRAPHICS: double axes, filters, pop-up descriptors.
Unit 07 - CREATING MAPS: maps and stories.
Unit 08 – PROJECT PRESENTATION.
Teaching and learning activities
In person
The two parts of the course are developed in parallel throughout the semester, using similar methodologies:
Part 1 - Advanced Excel:
The methodology is eminently practical, based on cases and using 5 different types of learning activities:
1. Presentation of the concepts by the teacher during class.
2. Resolution of exercises and practical cases in class under the direct guidance of the teacher.
3. Autonomous resolution of exercises and practical cases in class with the teacher's assistance if necessary.
4. Autonomous resolution of exercises and practical cases outside class. In this case, the student will be able to ask for teacher’s assistance during next class.
5. Elaboration of a "directed project" throughout the course. The topic will be preferably proposed by the student and approved by the teacher. The student will develop this work under the supervision and assistance of the teacher, and the final result will be a spreadsheet with all the information obtained and processed, the corresponding graphic elements and the conclusions. This spreadsheet should use as many functionalities as possible of the functionalities covered in the course, and it must include at least one complex macro in Visual Basic.
Part 2 - Data Visualization:
The methodology is eminently practical and is based on 3 different types of learning activities:
1. Theoretical presentation by the teacher to introduce the important aspects of the session.
2. Resolution of exercises and practical cases by the teacher as a practical demonstration of the concepts and skills to be developed.
3. Development of a personal project by the student, applying the features presented in the sessions, and under the guidance and supervision of the teacher.
Evaluation systems and criteria
In person
In order to pass the course is essential to pass each part (Excel & Data Visualization) separately. Therefore, the final mark will be calculated only if the mark of each part (Excel & Data Visualization) is higher or equal to 50 (out of 100).
In that case, the final mark will be calculated according to this formula:
50% mark of Excel part + 50% mark of Data Visualization part
Additionally, those students who participate in the E&S DAY organized by the Faculty of Economics and Social Sciences will receive an extra 5% (but never exceeding the limit of 100% overall mark for the course).
In case of failing only one of the two parts, the mark will be either the average (if it is below 40 out of 100) or 40 out of 100 otherwise.
In the repeat exams the final mark will be calculated according to the same rules.
The student will have to attend the repeat exam only of the part or parts he has failed in the first call.
Following there is a detailed description of the evaluation system for each part.
Part 1 - Advanced Excel:
The final mark for this part will be obtained aggregating the following 4 components, with the relative weights indicated between brackets:
1. Final exam (30%). To pass the course, it is essential to pass the final exam.
2. Attendance, class participation and class exercises (10%).
3. Home Exercises (20%).
4. Directed project (40%).
Remarks:
- To deliver the exercises and the directed project the student will upload the zipped file with his resolution to the online space of the course.
- The Directed Project will require three submissions: (A) Directed Project Proposal (10%), in which the student will suggest the topic of the project, but the student can alternatively write a mail to the teacher asking him to suggest a topic, but then the student will miss the 10% corresponding to this part; (B) Finished Draft (20%), for which the teacher will provide feed-back on this submission and will suggest changes and improvements; (C) Final Submission (70%), which must incorporate the changes and improvements suggested by the teacher to the Finished Draft. Last day of class the student will present his project to the rest of the class.
- Students can spontaneously group themselves into groups of 2 or 3 students for the Directed Project. In that case the complexity and scope of the project should be in accordance with the size of the group.
- The exercises delivered after the deadline will receive a penalty according to the following scale: 30 points (out of 100) if it is between 1 second and 48 hours late, and a mark of 0 if it is more than 48 hours late.
- Sending incomplete or bad-formatted exercises due to failing to follow the instructions with accuracy will imply a penalty.
- Any student involved (actively or passively) in any cheating or plagiarism activity will automatically fail de course with a final mark of zero.
- Obtaining a score below 50 (out of 100) on the final exam will imply failing the course.
- In case of taking the repeat exam, the student will choose if she/he prefers to be graded according to the continuous evaluation method or obtaining directly the mark of the final exam. In any case, in accordance with the school's regulations, the final mark for the repeat examination session will never be higher than 70 (out of 100).
- Each student repeating the course will be able to choose, in both the regular exam (in May) and the repeat exam (in June-July), if she/he prefers to be graded according to the continuous evaluation method or obtaining directly the mark of the final exam. In accordance with the school's regulations, the final mark for the repeat examination session will never be higher than 70 out of 100.
- Foreign and exchange students (Erasmus and others) will be subject to the same conditions as other students, especially for what regards to calendar, exams dates and evaluation system.
Part 2 - Data Visualization:
The final mark for this part will be obtained aggregating the following 2 components, with the relative weights indicated between brackets:
1. Attendance and class participation (30%).
2. Personal project (70%).
Remarks:
- Obtaining a score below 50 (out of 100) on the final exam will imply failing the course.
- In order to pass the course, class attendance rate must be greater than 70%.
- In case of taking the repeat exam, the maximum grade will be 70 out of 100, as specified by the school’s regulations.
- Foreign and exchange students (Erasmus and others) will be subject to the same conditions as other students, especially for what regards to calendar, exams dates and evaluation system.
Bibliography and resources
Add-ins.com (Ed.), Macros Made Easy, Adquirible a través de https://www.add-ins.com/macros-made-easy.htm (RECOMMENDED)
Alexander, M., & Kusleika, R. Excel 2019 Power Programming with VBA, John Wiley & Sons, New Jersey 2019. (RECOMMENDED)
Allen, A., Excel: Excel Mastering Book: Learn Excel, Macros, Shortcuts, and Accounting (Excel Beginners Guide, Excel Mastering, Excel Macros, Excel Shortcuts), CreateSpace Independent Publishing Platform, 2016.
Amelot, M. Vba excel 2016. Programacion en excel: macros y lenguaje vba, Eni-ediciones, Barcelona 2016.
Benton, C. J., Excel Macros & VBA For Business Users - A Beginners Guide, Self-published 2016. (RECOMMENDED)
Charte, F. Excel 2019 (Manual Imprescindible), Anaya, Madrid 2019.
Chavez Castillo, A., Macros Grabadas en Excel 2016: Para principiantes en plataforma Windows (Aprende Excel), Consultia SC, Jalisco 2016. (INTRODUCTION TO MACROS RECORDING).
Flores Castillo, J. M., Macros con Excel 2016. Automatiza tu trabajo. Marcombo, Barcelona 2016.
Jelen, B., & Syrstad, T., Microsoft Excel 2016 VBA and Macros (Business Skills), Pearson Education Inc., New York 2019. (ADVANCED)
MEDIAactive (Ed.), Aprender a programar con Excel 2016 VBA con 100 ejercicios (APRENDER...CON 100 EJERCICIOS PRÁCTICOS), Marcombo, Barcelona 2016. (TO LEARN PROGRAMMING IN A PRACTICAL WAY)
Alexander, M., Kusleika, R., & Walkenbach, J., Excel 2019 Bible, Wiley, New Jersey 2019. (VERY COMPLETE)
Some of these books have a very cheap electronic version.