
How to access the course files
Section 4 Summary
Filtering Data
Filtering data helps focus on specific information in a dataset, making it easier to identify patterns and narrow results. Key techniques include:
Filtering by Value:
Apply a filter to a dataset via the toolbar filter icon.
Use “Filter by values” to select or exclude specific entries.
Filtering by Condition:
Filter data based on criteria (e.g., scores below 50%).
Select “Filter by condition,” specify the condition, and enter the value.
Filtering by Colour:
Filter rows based on fill or text colour. Useful for status-based data.
Using Filter Views:
Create reusable, custom filters that don’t affect others’ views.
Save, rename, and duplicate Filter Views for convenience.
Sorting and Randomising Data
Sorting and randomising make datasets easier to organise and work with.
Sorting Data Without Headers:
Select data, sort by column, and choose ascending/descending order.
Sorting Data With Headers:
Include headers when selecting data and tick “Data has header row.”
Sort using header names for clarity.
Sorting by Multiple Criteria:
Apply multiple sort rules by adding extra rules, such as by category and then by value.
Randomising Data:
Shuffle rows using “Randomise range” to create random orders (e.g., student groups).
Conditional Formatting
Conditional formatting highlights data visually based on rules, enhancing readability and insights.
Basic Conditional Formatting:
Set rules (e.g., highlight scores ≤ 60%) and apply fill colours.
Multiple Formatting Rules:
Use multiple rules for different criteria, like red for fails and green for passes.
Colour Scales:
Gradually highlight data using a range (e.g., red to green based on value distribution).
Advanced Custom Formulas:
Use formulas to create more advanced rules (e.g., highlight rows based on a column value).
Pivot Tables
Pivot tables summarise and analyse data efficiently, offering powerful insights.
Creating a Pivot Table:
Use Insert > Pivot table and set the rows and values (e.g., total sales by person).
Adding Multiple Values:
Add multiple metrics (e.g., Units sold and Revenue) for comparison.
Sorting and Summarising:
Sort results (e.g., top-selling products) by column values.
Filtering Data:
Apply filters to narrow results (e.g., sales by a specific person or price range).
Grouping Data by Date:
Group data by month, year, or custom intervals for time-based analysis.
Breaking Down Data with Columns:
Analyse contributions by splitting data into rows and columns.
Slicers
Slicers create dynamic, interactive filters for datasets, pivot tables, or charts.
Adding Slicers:
Use Data > Add a slicer to create a slicer for specific columns.
Using Slicers:
Filter data by values or conditions (e.g., sales > 15,000).
Instantly update connected visuals like charts or pivot tables.
Customising Slicers:
Set default views, adjust filters, and modify appearance for a polished look.
Place slicers in frozen rows for visibility during scrolling.
Hello! Welcome to Google Sheets - From Novice to Ninja! This comprehensive course on Google Sheets equips you with the skills to effortlessly organize, analyze, and visualize your data. We’ll begin with the fundamentals, covering data input and formatting, before moving on to more advanced topics like data validation and automation. This course is fully up-to-date, incorporating the latest features in Google Sheets, including AI-powered tools like Gemini, structured data with tables, and time-saving features like Conditional Notifications.
The course consists of byte-size videos which are about 3 to 10 mins long. In each video, I’ll take you step-by-step through an area in Sheets, showing you practical examples of how to use the tools. At the end of each section, there will be a little quiz to check your understanding, plus, a real-life practical challenge to use your new-found skills on.
WHAT WILL YOU LEARN?
We’ll start with the very basics and look at the layout of Sheets, understand cells, rows, columns, and sheets.
We’ll then look at formatting data and the cells to make it easier to understand.
We’ll look beyond cells and look at rows, columns, and sheets, and see how we can work with them, for example, inserting, deleting, freezing, grouping, and merging.
Then we’ll introduce one of the most important areas in spreadsheets, which are formulas and functions. These allow us to manipulate data with ease, making calculation super easy. Plus, we’ll look at two types of cell references and when we should use them.
You’ll discover some time-saving techniques like how to quickly create sequential numbers and dates.
You’ll see how to use hyperlinks to connect specific cells, sheets, other Google files and external websites. Plus, how to connect spreadsheets so that if data in one is updated, it updates the other. All with a simple function.
You’ll learn how to manipulate data in different ways like, transposing, filtering, sorting, and slicing data. Plus, how to analyse data with tools like pivot tables.
You’ll learn how to create sparklines and charts to understand trends and patterns in your data.
We’ll look at how to format your data automatically using conditional formatting, to make it easier to read and understand, without the need to spend time manually formatting your cells.
Multiple people can use the same spreadsheet at the same time, and you’ll learn how to share the sheets, to leave notes, and how to use the in-built commenting system for effective teamwork.
You’ll see how you can go back to previous versions of both your document and in specific cells. Every change is recorded in Sheets!
Spreadsheet data can get messy, so you’ll learn how to clean data up, to use rules and drop-down menus to make data entry both easy and accurate.
Learn how to print out your data, formatting it so it looks professional.
We look at how to create timelines to visualize your time-based data.
Finally, we look at ways of automating your Sheets to save you time, using tools like macros, Apps Script, and using the power of Gemini.
Who am I? I’m Baz Roberts and I’ve been writing about Google Sheets and the rest of the suite of Google Apps since 2015. If you want a course that's straight to the point whilst taking you step-by-step, then you've found the right one!
How will this course help you? You’ll be able to use Google Sheets with confidence. You’ll save time and effort. You’ll impress your boss and colleagues! You’ll enhance professional and academic prospects and open doors to new opportunities.
None of this is difficult. Sometimes you just need to be guided in the right direction. Let's get started! :)