Microsoft Excel

Type				Team Size
Me, Myself and I	Group Collaboration	The Academic System	Software	1	2-10	11-30	30+

What, Why & When

This article is about using spreadsheets in general and Microsoft Excel in particular. We’ll show you how to conceptualize problems and use spreadsheets so solve them, as well as a host of other things. The article mostly revolves around a video tutorial, so there’s not so much to read.

The goal is to enable you to confidently be able to use spreadsheets for things from everyday tasks to work to scientific research.

Getting Started

To get started, you’ll need a spreadsheet software. Here, we’re using Microsoft Excel because it’s the most widely used and the richest in features. If you're a member of Leuphana University, you can order a cheap Microsoft Office license here: https://www.leuphana.de/services/miz/service-support/beschaffung/software/ms-office-365.html

Most of the things we’re showing are also doable in Google Sheets or LibreOffice Calc, so if you prefer those, don’t worry. The software will look and feel a bit different, but the underlying ideas are the same.

As stated above, this article mainly revolves around a set of videos, which we link to below. All the content and explanations can be found there. Here, will only tell you the contents of the video and give accompanying information and resources so that you can a) choose which one is interesting to you and b) follow along (which we recommend you do, because that’s the only way you’ll learn).

The Videos

Introduction

This video is an overview of the other four tutorials and discusses some caveats and hints when learning Excel.

1) The Flatshare Cost Calculator

In this introductory video, we’re building an Excel model that allows you to calculate how to split the rent in a flatshare based on room size. The final model can be downloaded here for reference. It is rather basic, so if you already have some solid Excel knowledge, you can probably skip it entirely or just skim through it if you’re interested in some particular thing. We provide the final file below for reference.

File:Flat Share Fair Cost Calculator Final.xlsx

Things that we cover:

• Navigating in Excel
• Basic formulas & calculations (including VLOOKUP/SVERWEIS)
• Basic formatting
• Custom formatting
• Creating of basic charts (we also recommend reading our Introduction to statistical figures

2) Data Analysis

This tutorial is supposed to put you in a position to import a dataset as a csv into Excel and efficiently build an analysis of the data. The dataset to follow along can be downloaded below.

File:Videogame Sales Dataset.csv

Things that we cover:

• CSV Import
• Making a table
• Format columns
• Filter & Sort
• Make a pivot table
• Make a pivot chart
• How to play around with data

3) Making Decisions

This video is about building a cost-benefit analysis in Excel that might help you to make reasoned decisions. Whilst it is primarily about how to use Excel to help with real-life problems, the method itself is also explained. You may download the final Excel file below.

File:Excel Decision Helper.xlsx

Things that we cover:

• Using tables
• Advanced formulas (weighted average, sum-if)
• Conditional formattin
• Advanced charts (point chart & stacked barchart)
• Using data across worksheets

4) Collaborating in Excel

In this last video, we talk about online collaboration in Excel via Microsoft OneDrive. We’ll show you how to setup everything, invite people and get going. The case we present is a common case for collaboratively tracking something, be it interview partners, tasks or anything else. We’re not going to cover many Excel functions, but just show you how you can use what you might have already learned to set this up. Again, you may download the final Excel file below.

File:Interviews Excel Collaboration.xlsx

Things that we cover:

• Setup
  • OneDrive
  • Create and share a table (directly, indirectly, online)
• Data validation
• Pivot Tables & Charts

Shortcuts

Here's some of the most useful shortcuts in Excel!

Shortcut	Keyboard Combination	Usage
Add row or column or cell	CTRL + '+'	Adds a row, column or cell above or before the selected row, cell or column.
Remove row or column or cell	CTRL + '-'	Removes the selected row, cell or column.
Fill downwards/rightwards	CTRL + U, CTRL + R	Fills the first the in a selection either downwards or rightwards, i.e. puts the same value into all the selected cells.
Undo / Redo	CTRL + Z, CTRL + Y	If you messed up, press CTRL + Z, if you then noticed you actually didn't, press CTRL + Y.
Format Window	CTRL + 1	This opens up the formatting window, which comes in handy surprisingly often, for example when you want borders (only in Excel, nowhere else).
Mark range of cells to copy/change	CTRL + Shift + arrow keys	Allows you to quickly, precisely and without using a mouse, select a range of cells.
Quickly format text	Ctrl + Shift + F (U, K)	Either boldens, underlines or italicizes your text.
Snap to grid while sizing and aligning elements	Hold down Alt	When you hold down alt while moving or resizing an object (such as a chart), the object will snap to Excel's gridlines, making everything look nice and orderly.

Links & Further reading

Of course, our own videos cover everything you need to know ;)

---

The author of this entry is Matteo Ramin.