CMPS 163: Business Analytics

Introduction

In this course we will be using Excel and we will start with review basic skills, as well as a few more advanced ones. Excel is spreadsheet software developed by Microsoft and offers a lot of flexibility in terms of implementing formulas and other things, which is exactly what we need for this course. The book almost exclusively Excel, except for the last chapter which we will not cover, and Chapter 5 (Cluster Analysis Part II) where we will also look at a powerful tool for clustering called Gephi.

If you have a lot of experience with Excel this will be an easy start, but if you only Excel superficially (or not at all!) please make sure that you spend enough time on understanding all the topics covered in Chapter 1 of the book. In both cases, it should be emphasized that you have to learn to be very precise so try to develop an eye for detail; getting one letter or parenthesis wrong will result in error or incorrect output. Finally, while Excel is ‘just a spreadsheet program’ you will be surprised how powerful it can be for solving a wide variety of business analytics problems.

Module Objectives

• Locate basic Excel functionality
• Apply freezing rows, quick statistics, and filtering and sorting
• Explain the difference between absolute and relative references, and how to reference other sheets
• Apply a wide variety of functions, including MATCH, INDEX, OFFSET, SMALL/LARGE, and VLOOKUP

Excel Functionality

• Fixing column widths
• Freezing the top row
• Quick statistics: formulas
• Quick statistics: status bar
• Copying by dragging
• Relative vs. absolute references
• Formatting cells
• Conditional formatting
• Paste special
• Creating charts
• Find and replace
• MATCH: finding the index of a phrase
• INDEX: getting a value at a location
• OFFSET: getting the value at an offset
• SMALL/LARGE: getting the nth smallest/largest value
• Referencing other sheets
• VLOOKUP: search and return a corresponding value
• Filtering and sorting
• Advanced sorting

Learning Resources

• Module 1 Readings: First half of Chapter 1
• Module 1 Slides: First half of Chapter 1

Learning Activities

• Module 1 Assignment

Video

How to use paste special:

For Further Study

• Read more about Microsoft Excel on Wikipedia