Microsoft Excel

Type				Team Size
Collaborative Tools	Software	Personal Skills	Productivity Tools	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 interest in some particular thing.

Things that we cover:

• Navigating in Excel
• Basic formulas & calculations (including VLOOKUP/SVERWEIS)
• Basic formatting
• Custom formatting
• Creating of basic charts

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.

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.

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.

Things that we cover:

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

Shortcuts

Okay, here's some very useful shortcuts, descendingly ordered by subjective importance.

Shortcut	Keyboard Combination	Usage
Undo & Redo	CTRL + Z, CTRL + Y	The bread and butter shortcuts for everyone who messes up sometimes (which is everyone). This works in almost all software ever created by the way.
Copy Commands	CTRL + C, CTRL + X, CTRL + V	The C copies, the X cuts, and the V pastes. I hope you knew this already, but if you didn't, make use of it!
Fancy keyboard navigation	CTRL and Arrow Keys	Instead of trying to move your mouse exactly behind that word, try to get used to pushing your cursor around with the arrow keys. If you use CTRL and the arrow keys (to the ← left or the right →), you jump between words instead of letters. This works everywhere by the way.
Make heading	Alt + 1, Alt + 2, Alt + 3	This makes the selected piece of text a level 1 heading. Can be used with 2 and 3 as well. Very useful for quickly structuring your document!
Show paragraphs and stuff	CTRL + Shift + *	This shortcut shows you linebreaks, tabs, pagebreaks and formatting. Very useful when you're trying to figure out why your document is a mess.
Page Break	CTRL + Enter	Inserts a pagebreak, saves you the trouble of inserting to many line breaks.

Links & Further reading

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

---

The author of this entry is Matteo Ramin.