Spreadsheet software is one of the most popular and powerful tools in data analysis. Millions of people use tools like Google Sheets or Microsoft Excel on a daily basis. Even the most experienced data scientists often started their careers with spreadsheets and still use it to test assumptions or to look at data for the first time. In this course, you will learn the basics of spreadsheets by working with rows, columns, addresses, and ranges. You will create your own formulas and learn how to use references.
Vincent Vankrunkelsven, Spreadsheet instructor at DataCamp
In this chapter, you’ll learn how to navigate within spreadsheets using concepts like rows, columns, cells, and ranges. Then you’ll practice using an essential part of spreadsheets: formulas. Finally, you'll learn how different data types are used in Google Sheets.
- Rows and Columns
- Cels, Ranges
- Formulas
- Data Types
In this chapter, you’ll learn how to use a powerful technique in Google Sheets: referencing. This chapter will cover concepts like absolute references, autofilling, and reactivity. After this chapter, your productivity with spreadsheets will have increased by a factor of n.
- Relative references: references to values in cells, e.g. A1. The value changes when the referred cell changes.
- Copying cells: dragging formulas to other cells will shift the relative references
- Calculations with references: references can be used in calculations as if they were the value they refer to
- Absolute references: references with columns and/or rows locked, e.g. $A$1. They don't change when copied.
This course will dig deeper into some of the core functionality of Google Sheets. There's a whole bunch of predefined functions we'll cover, like SUM() and AVERAGE(), and VLOOKUP(). We'll apply these techniques to do some analysis on your grades in school, look at performance statistics within a company, track monthly sales, and look at some real geographical information about the countries of the world.
This chapter introduces a very useful feature in Google Sheets: predefined functions. You'll use these functions to solve complex problems without having to worry about specific calculations. We’ll cover a lot of predefined functions, including functions for numbers, functions for strings, and functions for dates.
First function - ROUND
Function composition - SQRT
Functions and ranges - MIN, MAX
Selecting ranges - SUM, AVERAGE, MEDIAN
Multiple arguments - RANK
Even more arguments - RANK
String manipulation - LEFT, RIGHT
String information - LEN, SEARCH
Combining strings - CONCATENATE
Date functions - WEEKDAY
Comparing dates
Combining functions
In the last chapter of the course, you'll master more advanced functions like IF() and VLOOKUP(). Conditional and lookup functions won’t seem so scary after you completed this chapter.
Performance statistics
Flow control - IF
Nested logical functions - IF
Combining logical values - OR, WEEKDAY
Conditional counting - COUNTIF
Conditional aggregation - COUNTIF
Conditional sum - SUMIF
Conditional average - AVERAGEIF
Advanced conditions - AVERAGEIF
Filters - FILTER, DATEVALUE, MEDIAN
Grades in class
Automating the lookup - VLOOKUP
More about lookup - VLOOKUP
Horizontal lookup - HLOOKUP
Weighted average - SUMPRODUCT, HLOOKUP