Subject

Advanced Excel

  • code 10772
  • course 2
  • term Semester 1
  • type op
  • credits 3

Main language of instruction: English

Teaching staff

Head instructor

Dr. Gabriel FERNÁNDEZ - gabi@uic.es

Office hours

Apart from classroom sessions, students may send questions, comments & requests via email to the teacher, or alternatively set a face-to-face appointment with him.

Gabriel Fernández: gabi@uic.es

Introduction

Spreadsheets are one of the most common tools used in managerial tasks. This implies that mastering them, taking profit of the advanced features they provide, constitutes a significant skill for being productive in any managerial position.

This course provides advanced training about spreadsheets oriented to managerial tasks, covering not only the step-by-step procedures to use the advanced features but also criteria and strategies all along the process of information treatment: retrieval, debugging, obtaining analytical information and preparing a synthesis conducing to conclusions.

Moreover, it also provides tools to automate repetitive tasks concerning information processing.

As has been perceived by the UIC staff who coordinates students internships in companies, mastering spreadsheets is one of the most valued and required competencies when companies search for candidates to cover a managerial position. This course prepares the student to become a "knowledge broker" of Microsoft Excel in his work environment. This way his employability is increased and once in a job his reputation as a highly efficient person is reinforced.

Pre-course requirements

The course requires basic/medium skills of Microsoft Excel. The skills acquired in the course "Information Systems 1", taught in the first year of UIC's curriculum (mandatory) are enough to cover this minimum.

The course also requires basic skills of computer use: surfing the internet and searching for information in the internet.

Moreover, as the course is taught in English, students need a minimum level of mastery of this language to follow the course without obstacles.

Objectives

After completing the course with success the student should be able to use spreadsheets to:

1) Introduce information using automatic or optimal procedures, importing it from a variety of sources and formats, debugging it and organizing it optimally for its further treatment.

2) Process information through optimal procedures to obtain analytical and synthetical information, oriented to decision-making process and to obtain conclusions.

3) Generate graphical objects to visualize analytical and synthetical information, to clarify the analytical process and establish conclusions.

4) Automate information treatment in all its phases: retrieval, debugging, aggregation, analysis, synthesis, graphical objects generation, sharing and establishing conclusions.

5) Test thoroughly any macro routine the student had generated, being aware of the extreme importance of the testing phase of any software development, and having criteria and methods to develop a systematic and exhaustive test.

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.
  • 50 - To acquire the ability to relate concepts, analyse and synthesise.
  • 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

Once having completed the course with success the student will be able to use spreadsheets for the integral treatment of information, in all its phases: retrieval, debugging, aggregation, analysis, synthesis, graphical object generation, sharing and arriving to conclusions.

He will also be able to automate repetitive tasks concerning information treatment.

Syllabus

Contents are structured in 5 modules:

  1. Organizational context and basic functions review (formatting, basic calculations, tables, formulas and references, search and reference functions, PivotTables and PivotCharts, etc.).
  2. Process automation: introduction to macros and visual basic.
  3. Data series treatment: data types management, import, debugging and re-coding,  sorting, matching and odd elements detection, anomalies detection. Error management and debugging. External data: import & export. Data Models and Databases. Criteria and methods for systematic and exhaustive testing of software routines.
  4. Other functions and tools: multi-user spreadsheets, data protection and forms management, scenarios analysis, goal seek, mini-charts, advanced PivotTable and PivotChart functions, Excel Add-ins, etc.
  5. Beyond Excel: the web (Google Apps (forms), Office Web Apps), report generation and automation, real-time financial information systems.

Teaching and learning activities

In person

Methodology is highly practical, based on cases. All face-to-face classes are in a PC lab, and student's autonomous work requires a computer with Microsot Office 2010+ or Office for Mac 2011+.

The instructional design includes 5 types of learning activities:

  1. Theoretical and conceptual explanations.
  2. Case resolution in the PC Lab under teacher's guidance.
  3. Autonomous case resolution in the PC lab with teacher's assistance if needed.
  4. Autonomous case resolution out of class. (Student can receive guidance and assistance of the teacher through e-mail or during next classes).
  5. Development of a "Directed project" througout the course. Topic will be suggested preferably by the student and approved by the teacher. The student will develop it under teacher's guidance and the resulting product will be a spreadsheet with all the obtained information (suitably treated), with graphical objects, and with a list of conclusions. This spreadsheet should use as many functionalities as possible, being compulsory to include at least one complex macro in VBA (visual basic for applications programming language).

Evaluation systems and criteria

In person

Final mark will be obtained aggregating these four components, with the specified weights:

  1. Final Exam (40%): it will take place in a PC Lab in the specified day and time which will be made public in advance. To pass the course is absolutely compulsory to pass the Final Exam.
  2. Attendance, participation in class and class exercises (10%).
  3. Home exercises (including review exercise) (20%).
  4. Directed project (30%).

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:
    1. Directed Project Proposal (10%). The student will suggest the topic of the project. 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.
    2. Finished Draft (20%). The teacher will provide feed-back on this submission and will suggest changes and improvements.
    3. Final Submission (70%). It 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, 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.

Bibliography and resources

Add-ins.com (Ed.), Macros Made Easy, Acquirable through https://www.add-ins.com/macros-made-easy.htm (RECOMMENDED)

Alexander, M., Kusleika, R., & Walkenbach, J., Excel 2016 Power Programming with VBA (Mr. Spreadsheet's Bookshelf), John Wiley & Sons, New Jersey 2016. (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 2016 (Manuales Avanzados), Anaya, Madrid 2016.

Chavez Castillo, A., Macros Grabadas en Excel 2016: Para principiantes en plataforma Windows (Aprende Excel), Consultia SC, Jalisco 2016. (GOOD AND SIMPLE INTRODUCTION TO MACRO'S RECORDING).

Flores Castillo, J. M., Macros con Excel 2016. Automatiza tu trabajo. Marcombo, Barcelona 2016.

Jelen, B., & Syrstad, T., Excel 2016 VBA and Macros (includes Content Update Program) (Mr.Excel Library), ALPHA BOOKS DIV OF PEARSON, New York 2015. (ADVANCED)

MEDIAactive (Ed.), Aprender a programar con Excel 2016 VBA con 100 ejercicios (APRENDER...CON 100 EJERCICIOS PRÁCTICOS), Marcombo, Barcelona 2016. (GOOD TO LEARN PROGRAMMING THROUGH A PRACTICAL METHOD)

Walkenbach, J., Excel 2016 Bible, Wiley, New Jersey 2016. (VERY COMPLETE)

All these books (except the first one) can be acquired through Amazon.com, and for some of them there is an electronic version (Kindle eBook).

Evaluation period

E: exam date | R: revision date | 1: first session | 2: second session:

  • E2 18/06/2019 15:00h b301

Teaching and learning material

      Websites
            Microsoft Office official website (spanish) http://office.microsoft.com/es-es/ 
            Microsoft Office official support website https://support.microsoft.com/en-us 
  © 2024 Universitat Internacional de Catalunya | Contact us | Privacy and data protection | Intellectual property
  Campus Barcelona. Tel.: 93 254 18 00 | Campus Sant Cugat. Tel.: 93 504 20 00